C#导出EXCEL(DataTable导出EXCEL)
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.IO; using System.Web; using Microsoft.Office.Interop.Excel; using System.Reflection;
/* * 开发人员：Hisen * 时间：2008年11月24日 * 功能：将数据导出Excel * */ namespace XT.LiTree.Logic { public class ExcelExport { private ExcelExport() { } private static ExcelExport _instance = null;
public static ExcelExport Instance { get { if (_instance == null) _instance = new ExcelExport(); return _instance; } }
/// <summary> /// DataTable直接导出Excel,此方法会把DataTable的数据用Excel打开,再自己手动去保存到确切的位置 /// </summary> /// <param>要导出Excel的DataTable</param> /// <returns></returns> public bool DoExport(DataTable dt) { Application app = new ApplicationClass(); if (app == null) { throw new Exception("Excel无法启动"); } app.Visible = true; Workbooks wbs = app.Workbooks; Workbook wb = wbs.Add(Missing.Value); Worksheet ws = (Worksheet)wb.Worksheets[1];
int cnt = dt.Rows.Count; int columncnt = dt.Columns.Count;
// *****************获取数据******************** object[,] objData = new Object[cnt + 1, columncnt]; // 创建缓存数据 // 获取列标题 for (int i = 0; i < columncnt; i++) { objData[0, i] = dt.Columns[i].ColumnName; } // 获取具体数据 for (int i = 0; i < cnt; i++) { System.Data.DataRow dr = dt.Rows[i]; for (int j = 0; j < columncnt; j++) { objData[i + 1, j] = dr[j]; } }
//********************* 写入Excel****************** Range r = ws.get_Range(app.Cells[1, 1], app.Cells[cnt + 1, columncnt]); r.NumberFormat = "@"; //r = r.get_Resize(cnt+1, columncnt); r.Value2 = objData; r.EntireColumn.AutoFit();
app = null; return true; }
/// <summary> /// DataTable通过流导出Excel /// </summary> /// <param>数据源DataSet</param> /// <param>DataTable中列对应的列名(可以是中文),若为null则取DataTable中的字段名</param> /// <param>保存文件名(例如：a.xls)</param> /// <returns></returns> public bool StreamExport(DataTable dt, string[] columns, string fileName, System.Web.UI.Page pages) { if (dt.Rows.Count > 65535) //总行数大于Excel的行数 { throw new Exception("预导出的数据总行数大于excel的行数"); } if (string.IsNullOrEmpty(fileName)) return false; StringBuilder content = new StringBuilder(); StringBuilder strtitle = new StringBuilder(); content.Append("<html xmlns:o=‘urn:schemas-microsoft-com:office:office‘ xmlns:x=‘urn:schemas-microsoft-com:office:excel‘ xmlns=‘‘>"); content.Append("<head><title></title><meta http-equiv=‘Content-Type‘ content=\"text/html; charset=gb2312\">"); //注意：[if gte mso 9]到[endif]之间的代码，用于显示Excel的网格线，若不想显示Excel的网格线，，可以去掉此代码 content.Append("<!--[if gte mso 9]>"); content.Append("<xml>"); content.Append(" <x:ExcelWorkbook>"); content.Append(" <x:ExcelWorksheets>"); content.Append(" <x:ExcelWorksheet>"); content.Append(" <x:Name>Sheet1</x:Name>"); content.Append(" <x:WorksheetOptions>"); content.Append(" <x:Print>"); content.Append(" <x:ValidPrinterInfo />"); content.Append(" </x:Print>"); content.Append(" </x:WorksheetOptions>"); content.Append(" </x:ExcelWorksheet>"); content.Append(" </x:ExcelWorksheets>"); content.Append("</x:ExcelWorkbook>"); content.Append("</xml>"); content.Append("<![endif]-->"); content.Append("</head><body><table style=‘border-collapse:collapse;table-layout:fixed;‘><tr>");
温馨提示: 本文由Jm博客推荐，转载请保留链接: https://www.jmwww.net/file/70402.html
- 上一篇：【读书笔记】WebApi 和 SPA(单页应用)
- 下一篇：C# 实现MD5加密
