最近在做一个项目，涉及到将数据导出到excel，因此花了一天多时间，从网上查询了一些相关方法：下面做一下总结： 下面这个项目里面的步骤是本人已经成功应用到项目中的。 第一步骤：将NPOI DLL文件引用到项目中： 然后在项目引中会出现
using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel;第二步：打开保存文件夹
SaveFileDialog sfd = new SaveFileDialog(); //设置文件类型 sfd.Filter = "Excel文件（*.xls）|*.xls"; //设置默认文件类型显示顺序 sfd.FilterIndex = 1; //保存对话框是否记忆上次打开的目录 sfd.RestoreDirectory = true; //设置默认的文件名 DateTime dataNow = DateTime.Now; string fileName = "XXXXXx" + dataNow.ToString("yyyy-MM-dd-HH-mm-ss"); sfd.FileName = fileName; string localFilePath = null; //点了保存按钮进入 if (sfd.ShowDialog() == DialogResult.OK) { localFilePath = sfd.FileName.ToString(); //获得文件路径 //string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径 } else { return; }第三步：
IWorkbook wb = new HSSFWorkbook();//建立一个excel文件 //创建一个工作簿 ISheet sh = wb.CreateSheet(captionName); #region 表头(第1行) //合并单元格 sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, propDictionary.Count - 1)); //创建第1行 IRow row0 = sh.CreateRow(0); //设置第1行高度 row0.Height = 20 * 20; //创建第1行第1列 ICell icell1top0 = row0.CreateCell(0); //设置第1行第1列格式 icell1top0.CellStyle = Getcellstyle(wb, "head"); //设置第1行第1列内容 icell1top0.SetCellValue(captionName); #endregion #region 抬头(第2行) //创建第2行 IRow row1 = sh.CreateRow(1); //设置高度 row1.Height = 20 * 20; //columnt_index是列的索引 int columnt_index = 0; ICellStyle style = Getcellstyle(wb, ""); foreach (var itemtmp in propDictionary) { //获取T的字段名称 string name = itemtmp.Key.ToString(); //创建第2行的第columnt_index列 ICell icell1top = row1.CreateCell(columnt_index); //设置第2行的第columnt_index列的格式 icell1top.CellStyle = style; //设置第2行的第columnt_index列的内容 icell1top.SetCellValue(name); //设置第2行的第columnt_index列的宽度 sh.SetColumnWidth(columnt_index, (int)((15 + 0.72) * 256)); columnt_index++; } #endregion #region 这里是data具体内容 //创建第3行 int rownums = 2; foreach (var item in resultToXLS.Items) { IRow row_zs = sh.CreateRow(rownums); //创建第1列、格式、字段值 ICell icell1top = row_zs.CreateCell(0); icell1top.CellStyle = style; icell1top.SetCellValue(item.ID.ToString()); ICell icell1top1 = row_zs.CreateCell(1); icell1top1.CellStyle = style; icell1top1.SetCellValue(item.PlateNum); ICell icell1top2 = row_zs.CreateCell(2); icell1top2.CellStyle = style; icell1top2.SetCellValue(item.PlateColor); ICell icell1top3 = row_zs.CreateCell(3); icell1top3.CellStyle = style; icell1top3.SetCellValue(item.PassTime.ToString("yyyy-MM-dd HH:mm:ss")); ICell icell1top4 = row_zs.CreateCell(4); icell1top4.CellStyle = style; icell1top4.SetCellValue(item.Result); ICell icell1top5 = row_zs.CreateCell(5); icell1top5.CellStyle = style; icell1top5.SetCellValue(item.CO.ToString()); ICell icell1top6 = row_zs.CreateCell(6); icell1top6.CellStyle = style; icell1top6.SetCellValue(item.CO2.ToString()); ICell icell1top7 = row_zs.CreateCell(7); icell1top7.CellStyle = style; icell1top7.SetCellValue(item.NO.ToString()); ICell icell1top8 = row_zs.CreateCell(8); icell1top8.CellStyle = style; icell1top8.SetCellValue(item.HC.ToString()); ICell icell1top9 = row_zs.CreateCell(9); icell1top9.CellStyle = style; icell1top9.SetCellValue(item.Opacity.ToString()); ICell icell1top10 = row_zs.CreateCell(10); icell1top10.CellStyle = style; icell1top10.SetCellValue(item.K.ToString()); ICell icell1top11 = row_zs.CreateCell(11); icell1top11.CellStyle = style; icell1top11.SetCellValue(item.SF.ToString()); ICell icell1top12 = row_zs.CreateCell(12); icell1top12.CellStyle = style; icell1top12.SetCellValue(item.Speed.ToString()); ICell icell1top13 = row_zs.CreateCell(13); icell1top13.CellStyle = style; icell1top13.SetCellValue(item.Acc.ToString()); ICell icell1top14 = row_zs.CreateCell(14); icell1top14.CellStyle = style; icell1top14.SetCellValue(item.Temperature.ToString()); ICell icell1top15 = row_zs.CreateCell(15); icell1top15.CellStyle = style; icell1top15.SetCellValue(item.WindSpeed.ToString()); ICell icell1top16 = row_zs.CreateCell(16); icell1top16.CellStyle = style; icell1top16.SetCellValue(item.WindDirection.ToString()); ICell icell1top17 = row_zs.CreateCell(17); icell1top17.CellStyle = style; icell1top17.SetCellValue(item.Pressure.ToString()); ICell icell1top18 = row_zs.CreateCell(18); icell1top18.CellStyle = style; icell1top18.SetCellValue(item.EntireBelieve.ToString()); ICell icell1top19 = row_zs.CreateCell(19); icell1top19.CellStyle = style; icell1top19.SetCellValue(item.Humidity.ToString()); ICell icell1top20 = row_zs.CreateCell(20); icell1top20.CellStyle = style; icell1top20.SetCellValue(item.IsUploaded.ToString()); rownums++; } #endregion //输出内容 if (string.IsNullOrEmpty(localFilePath)) { return; } using (FileStream stm = File.OpenWrite(@localFilePath)) { wb.Write(stm); MessageBox.Show("导出Excle完成！"); }第四步：
/// 格式设置 /// </summary> static ICellStyle Getcellstyle(IWorkbook wb, string type) { ICellStyle cellStyle = wb.CreateCellStyle(); //定义字体 IFont font = wb.CreateFont(); font.FontName = "微软雅黑"; //水平对齐 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //垂直对齐 cellStyle.VerticalAlignment = VerticalAlignment.Center; //自动换行 cellStyle.WrapText = true; //缩进 cellStyle.Indention = 0; switch (type) { case "head": cellStyle.SetFont(font); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; break; default: cellStyle.SetFont(font); break; } return cellStyle; }以上就是成功应用的步骤。
下面是我统计一些较为有用的方法 1、使用NPOI处理大数据量EXCEl2007 excel2003只能存储65536行数据，而excel2007能存储100多万行数据。所以针对日后的数据的一个可预见性增长，所以采用excel2007来处理数据，但是C#默认的excel
的处理方式只能处理excel2003，所以为了处理大数据的excel2007，我们使用NPOI来进行处理 需要引入相应的dll文件，具体引入方法就不再多说，具体的实现方式见下方代码。
*生成导出EXCEl文件对话框 *同时将导出文件类型定义为Excel *保存时只需指明对应的文件名即可 */ SaveFileDialog sdfexport = new SaveFileDialog(); sdfexport.Filter = "Excel文件|*.xlsx"; if (sdfexport.ShowDialog() == DialogResult.No) { return; } string filename = sdfexport.FileName; //创建Excel2007工作簿 IWorkbook book = new XSSFWorkbook(); //创建Excel2007工作表 ISheet sheet = book.CreateSheet("standard_template"); //创建Excel行 IRow row = sheet.CreateRow(0); //给单元格赋值 row.CreateCell(0).SetCellValue("序号"); row.CreateCell(1).SetCellValue("大区(区域)"); row.CreateCell(2).SetCellValue("省(简)"); row.CreateCell(3).SetCellValue("说明"); /* * 将Excel文件写入相应的Excel文件中 */ FileStream fs = File.Create(filename); book.Write(fs); fs.Close();2、NPOI导入导出EXCEL通用类，供参考，可直接使用在WinForm项目中
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Data; using System.Windows.Forms; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel; using Zwj.TEMS.Common; namespace TEMS.Service { public static class ExcelHelperForCs { #region 私有方法 /// <summary> /// 获取要保存的文件名称（含完整路径） /// </summary> /// <returns></returns> private static string GetSaveFilePath() { SaveFileDialog saveFileDig = new SaveFileDialog(); saveFileDig.Filter = "Excel Office97-2003(*.xls)|.xls|Excel Office2007及以上(*.xlsx)|*.xlsx"; saveFileDig.FilterIndex = 0; saveFileDig.OverwritePrompt = true; saveFileDig.InitialDirectory = Common.DesktopDirectory; string filePath = null; if (saveFileDig.ShowDialog() == DialogResult.OK) { filePath = saveFileDig.FileName; } return filePath; } /// <summary> /// 判断是否为兼容模式 /// </summary> /// <param name="filePath"></param> /// <returns></returns> private static bool GetIsCompatible(string filePath) { return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase); } /// <summary> /// 创建工作薄 /// </summary> /// <param name="isCompatible"></param> /// <returns></returns> private static IWorkbook CreateWorkbook(bool isCompatible) { if (isCompatible) { return new HSSFWorkbook(); } else { return new XSSFWorkbook(); } } /// <summary> /// 创建工作薄(依据文件流) /// </summary> /// <param name="isCompatible"></param> /// <param name="stream"></param> /// <returns></returns> private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream) { if (isCompatible) { return new HSSFWorkbook(stream); } else { return new XSSFWorkbook(stream); } } /// <summary> /// 创建表格头单元格 /// </summary> /// <param name="sheet"></param> /// <returns></returns> private static ICellStyle GetCellStyle(IWorkbook workbook) { ICellStyle style = workbook.CreateCellStyle(); style.FillPattern = FillPattern.SolidForeground; style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; return style; } /// <summary> /// 从工作表中生成DataTable /// </summary> /// <param name="sheet"></param> /// <param name="headerRowIndex"></param> /// <returns></returns> private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex) { DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(headerRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "") { // 如果遇到第一个空列，则不再继续向后读取 cellCount = i + 1; break; } DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue)) { DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } table.Rows.Add(dataRow); } } return table; } #endregion #region 公共导出方法 /// <summary> /// 由DataSet导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <returns>Excel工作表</returns> public static string ExportToExcel(DataSet sourceDs, string filePath = null) { if (string.IsNullOrEmpty(filePath)) { filePath = GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; bool isCompatible = GetIsCompatible(filePath); IWorkbook workbook = CreateWorkbook(isCompatible); ICellStyle cellStyle = GetCellStyle(workbook); for (int i = 0; i < sourceDs.Tables.Count; i++) { DataTable table = sourceDs.Tables[i]; string sheetName = "result" + i.ToString(); ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in table.Columns) { ICell cell = headerRow.CreateCell(column.Ordinal); cell.SetCellValue(column.ColumnName); cell.CellStyle = cellStyle; } // handling value. int rowIndex = 1; foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString()); } rowIndex++; } } FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook.Write(fs); fs.Dispose(); workbook = null; return filePath; } /// <summary> /// 由DataTable导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <returns>Excel工作表</returns> public static string ExportToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null) { if (sourceTable.Rows.Count <= 0) return null; if (string.IsNullOrEmpty(filePath)) { filePath = GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; bool isCompatible = GetIsCompatible(filePath); IWorkbook workbook = CreateWorkbook(isCompatible); ICellStyle cellStyle = GetCellStyle(workbook); ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in sourceTable.Columns) { ICell headerCell = headerRow.CreateCell(column.Ordinal); headerCell.SetCellValue(column.ColumnName); headerCell.CellStyle = cellStyle; } // handling value. int rowIndex = 1; foreach (DataRow row in sourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue((row[column]??"").ToString()); } rowIndex++; } FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook.Write(fs); fs.Dispose(); sheet = null; headerRow = null; workbook = null; return filePath; } /// <summary> /// 由List导出Excel /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="data">在导出的List</param> /// <param name="sheetName">sheet名称</param> /// <returns></returns> public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "result", string filePath = null) where T : class { if (data.Count <= 0) return null; if (string.IsNullOrEmpty(filePath)) { filePath = GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; bool isCompatible = GetIsCompatible(filePath); IWorkbook workbook = CreateWorkbook(isCompatible); ICellStyle cellStyle = GetCellStyle(workbook); ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < headerNameList.Count; i++) { ICell cell = headerRow.CreateCell(i); cell.SetCellValue(headerNameList[i].Value); cell.CellStyle = cellStyle; } Type t = typeof(T); int rowIndex = 1; foreach (T item in data) { IRow dataRow = sheet.CreateRow(rowIndex); for (int n = 0; n < headerNameList.Count; n++) { object pValue = t.GetProperty(headerNameList[n].Key).GetValue(item, null); dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString()); } rowIndex++; } FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook.Write(fs); fs.Dispose(); sheet = null; headerRow = null; workbook = null; return filePath; } /// <summary> /// 由DataGridView导出 /// </summary> /// <param name="grid"></param> /// <param name="sheetName"></param> /// <param name="filePath"></param> /// <returns></returns> public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null) { if (grid.Rows.Count <= 0) return null; if (string.IsNullOrEmpty(filePath)) { filePath = GetSaveFilePath(); } if (string.IsNullOrEmpty(filePath)) return null; bool isCompatible = GetIsCompatible(filePath); IWorkbook workbook = CreateWorkbook(isCompatible); ICellStyle cellStyle = GetCellStyle(workbook); ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < grid.Columns.Count; i++) { ICell cell = headerRow.CreateCell(i); cell.SetCellValue(grid.Columns[i].Name); cell.CellStyle = cellStyle; } int rowIndex = 1; foreach (DataGridViewRow row in grid.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); for (int n = 0; n < grid.Columns.Count; n++) { dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString()); } rowIndex++; } FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook.Write(fs); fs.Dispose(); sheet = null; headerRow = null; workbook = null; return filePath; } #endregion #region 公共导入方法 /// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFileStream">Excel文件流</param> /// <param name="sheetName">Excel工作表名称</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <param name="isCompatible">是否为兼容模式</param> /// <returns>DataTable</returns> public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible) { IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream); ISheet sheet = null; int sheetIndex = -1; if (int.TryParse(sheetName, out sheetIndex)) { sheet = workbook.GetSheetAt(sheetIndex); } else { sheet = workbook.GetSheet(sheetName); } DataTable table = GetDataTableFromSheet(sheet, headerRowIndex); excelFileStream.Close(); workbook = null; sheet = null; return table; } /// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径，为物理路径。</param> /// <param name="sheetName">Excel工作表名称</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataTable</returns> public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex) { using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { bool isCompatible = GetIsCompatible(excelFilePath); return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible); } } /// <summary> /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable /// </summary> /// <param name="excelFileStream">Excel文件流</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <param name="isCompatible">是否为兼容模式</param> /// <returns>DataSet</returns> public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible) { DataSet ds = new DataSet(); IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream); for (int i = 0; i < workbook.NumberOfSheets; i++) { ISheet sheet = workbook.GetSheetAt(i); DataTable table = GetDataTableFromSheet(sheet, headerRowIndex); ds.Tables.Add(table); } excelFileStream.Close(); workbook = null; return ds; } /// <summary> /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径，为物理路径。</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataSet</returns> public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex) { using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { bool isCompatible = GetIsCompatible(excelFilePath); return ImportFromExcel(stream, headerRowIndex, isCompatible); } } #endregion #region 公共转换方法 /// <summary> /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B... /// </summary> /// <param name="index">列索引</param> /// <returns>列名，如第0列为A，第1列为B...</returns> public static string ConvertColumnIndexToColumnName(int index) { index = index + 1; int system = 26; char[] digArray = new char[100]; int i = 0; while (index > 0) { int mod = index % system; if (mod == 0) mod = system; digArray[i++] = (char)(mod - 1 + 'A'); index = (index - 1) / 26; } StringBuilder sb = new StringBuilder(i); for (int j = i - 1; j >= 0; j--) { sb.Append(digArray[j]); } return sb.ToString(); } /// <summary> /// 转化日期 /// </summary> /// <param name="date">日期</param> /// <returns></returns> public static DateTime ConvertDate(object date) { string dtStr = (date ?? "").ToString(); DateTime dt = new DateTime(); if (DateTime.TryParse(dtStr, out dt)) { return dt; } try { string spStr = ""; if (dtStr.Contains("-")) { spStr = "-"; } else if (dtStr.Contains("/")) { spStr = "/"; } string[] time = dtStr.Split(spStr.ToCharArray()); int year = Convert.ToInt32(time[2]); int month = Convert.ToInt32(time[0]); int day = Convert.ToInt32(time[1]); string years = Convert.ToString(year); string months = Convert.ToString(month); string days = Convert.ToString(day); if (months.Length == 4) { dt = Convert.ToDateTime(date); } else { string rq = ""; if (years.Length == 1) { years = "0" + years; } if (months.Length == 1) { months = "0" + months; } if (days.Length == 1) { days = "0" + days; } rq = "20" + years + "-" + months + "-" + days; dt = Convert.ToDateTime(rq); } } catch { throw new Exception("日期格式不正确，转换日期失败！"); } return dt; } /// <summary> /// 转化数字 /// </summary> /// <param name="d">数字字符串</param> /// <returns></returns> public static decimal ConvertDecimal(object d) { string dStr = (d ?? "").ToString(); decimal result = 0; if (decimal.TryParse(dStr, out result)) { return result; } else { throw new Exception("数字格式不正确，转换数字失败！"); } } #endregion } }3、最近工作导数据失败，找问题原因：ORALCE导出数据，超出5w多条数据，由于数据量过大（加上列数多），无法导出。 PLSQL工具导出EXCEL失败，卡死白屏。 使用NPOI第三方类导出EXCEL失败，超出100+M多的数据就抛出异常提示内存溢出。 最后找到方法，使用OLEDB方式，数据导出到EXCEL文件，数据量大的时候，运行占用内存明显比NPOI和微软组件小（不超过100M）。
/// <summary> /// 使用OLEDB导出Excel /// </summary> /// <param name="dt">数据集</param> /// <param name="filepath">文件目录和文件名</param> /// <param name="tablename">SHEET页名称</param> /// <param name="pagecount">每页记录数</param> public static void Export(DataTable dt, string filepath, string tablename, int pagecount) { //excel 2003格式 string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; //Excel 2007格式 //string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;"; try { using (OleDbConnection con = new OleDbConnection(connString)) { con.Open(); //开始分页 if (dt.Rows.Count > pagecount) { int page = dt.Rows.Count / pagecount + 1; //总页数 for (int i = 0; i < page; i++) { //建新sheet和表头 StringBuilder strSQL = new StringBuilder(); string tabname = tablename + i.ToString(); strSQL.Append("CREATE TABLE ").Append("[" + tabname + "]"); //每60000项建一页 strSQL.Append("("); for (int j = 0; j < dt.Columns.Count; j++) { strSQL.Append("[" + dt.Columns[j].ColumnName + "] text,"); } strSQL = strSQL.Remove(strSQL.Length - 1, 1); strSQL.Append(")"); OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con); cmd.ExecuteNonQuery(); //准备逐条插入数据 for (int j = i * pagecount; j < (i + 1) * pagecount; j++) { if (i == 0 || j < dt.Rows.Count) { StringBuilder tmp = new StringBuilder(); StringBuilder strfield = new StringBuilder(); StringBuilder strvalue = new StringBuilder(); for (int z = 0; z < dt.Columns.Count; z++) { strfield.Append("[" + dt.Columns[z].ColumnName + "]"); strvalue.Append("'" + dt.Rows[j][z].ToString() + "'"); if (z != dt.Columns.Count - 1) { strfield.Append(","); strvalue.Append(","); } else { } } cmd.CommandText = tmp.Append(" insert into [" + tabname + "]( ") .Append(strfield.ToString()) .Append(") values (").Append(strvalue).Append(")").ToString(); cmd.ExecuteNonQuery(); } } } } con.Close(); no = count; } Console.WriteLine("OK"); } catch (Exception ex) { Console.WriteLine(ex.Message); } GC.Collect(); }4、NPOI导出Excel和EPPlus导出Excel比较 系统中经常会使用导出Excel的功能。
之前使用的是NPOI，但是导出数据行数多就报内存溢出。
最近看到EPPlus可以用来导出Excel，就自己测了下两者导出上的差异。
NPIO官网地址：http://npoi.codeplex.com/
EPPlus官网地址：http://epplus.codeplex.com/
添加NPOI、EPPlus类库dll使用的是NuGet添加。
在类库References右键Manage NuGet Packages…，之后选择添加对应的dll。
测试结果显示，相同数据结构的数据，EPPlus的导出能力比NPOI强。
20列，NPOI能导出4万数据，导出5万数据时报内存溢出。
EPPlus能导出20万以上数据，导出23万测试时内存溢出。 NPOI导出：
1 private static MemoryStream ExportXlsx(DataTable dt) 2 { 3 XSSFWorkbook workbook = new XSSFWorkbook(); 4 ISheet sheet = null; 5 6 int headRowIndex = 0; 7 string sheetName = "Sheet1"; 8 if (!string.IsNullOrEmpty(dt.TableName)) 9 { 10 sheetName = dt.TableName; 11 } 12 sheet = workbook.CreateSheet(sheetName); 13 int rowIndex = 0; 14 15 #region 列头及样式 16 { 17 XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex); 18 19 ICellStyle headStyle = workbook.CreateCellStyle(); 20 headStyle.Alignment = HorizontalAlignment.Center; 21 IFont font = workbook.CreateFont(); 22 font.FontHeightInPoints = 10; 23 font.Boldweight = 700; 24 headStyle.SetFont(font); 25 26 foreach (DataColumn column in dt.Columns) 27 { 28 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 29 headerRow.GetCell(column.Ordinal).CellStyle = headStyle; 30 } 31 } 32 #endregion 33 34 #region 填充内容 35 36 foreach (DataRow row in dt.Rows) 37 { 38 rowIndex++; 39 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); 40 foreach (DataColumn column in dt.Columns) 41 { 42 string drValue = row[column].ToString(); 43 dataRow.CreateCell(column.Ordinal).SetCellValue(drValue); 44 } 45 } 46 #endregion 47 48 49 MemoryStream ms = new MemoryStream(); 50 51 workbook.Write(ms); 52 ms.Flush(); 53 54 return ms; 55 } 56 57 public static void ExportXlsxByWeb(DataTable dt, string strFileName) 58 { 59 60 HttpContext curContext = HttpContext.Current; 61 62 MemoryStream ms = ExportXlsx(dt); 63 64 curContext.Response.AppendHeader("Content-Disposition", 65 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx"); 66 curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString()); 67 curContext.Response.ContentEncoding = Encoding.UTF8; 68 69 curContext.Response.BinaryWrite(ms.ToArray()); 70 ms.Close(); 71 ms.Dispose(); 72 curContext.Response.End(); 73 74 }EPPlus导出：
1 /// <summary> 2 /// 使用EPPlus导出Excel(xlsx) 3 /// </summary> 4 /// <param name="sourceTable">数据源</param> 5 /// <param name="strFileName">xlsx文件名(不含后缀名)</param> 6 public static void ExportByEPPlus(DataTable sourceTable, string strFileName) 7 { 8 using (ExcelPackage pck = new ExcelPackage()) 9 { 10 //Create the worksheet 11 string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName; 12 ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName); 13 14 //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 15 ws.Cells["A1"].LoadFromDataTable(sourceTable, true); 16 17 //Format the row 18 ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin; 19 Color borderColor = Color.FromArgb(155, 155, 155); 20 21 using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count]) 22 { 23 rng.Style.Font.Name = "宋体"; 24 rng.Style.Font.Size = 10; 25 rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid 26 rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255)); 27 28 rng.Style.Border.Top.Style = borderStyle; 29 rng.Style.Border.Top.Color.SetColor(borderColor); 30 31 rng.Style.Border.Bottom.Style = borderStyle; 32 rng.Style.Border.Bottom.Color.SetColor(borderColor); 33 34 rng.Style.Border.Right.Style = borderStyle; 35 rng.Style.Border.Right.Color.SetColor(borderColor); 36 } 37 38 //Format the header row 39 using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count]) 40 { 41 rng.Style.Font.Bold = true; 42 rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; 43 rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246)); //Set color to dark blue 44 rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51)); 45 } 46 47 //Write it back to the client 48 HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; 49 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8))); 50 HttpContext.Current.Response.ContentEncoding = Encoding.UTF8; 51 52 HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray()); 53 HttpContext.Current.Response.End(); 54 } 55 }5导出数据到Excel（百万级）保存为csv格式
private void DataTableToCsv(DataTable table, string file) { StringBuilder titleBuilder = new StringBuilder(); StringBuilder lineBuilder = new StringBuilder(); FileStream fs = new FileStream(file, FileMode.OpenOrCreate); StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default); for (int i = 0; i < table.Columns.Count; i++) { titleBuilder.Append(table.Columns[i].ColumnName + "\t"); //栏位：自动跳到下一单元格 } string title = titleBuilder.ToString(); title = title.Substring(0, title.Length - 1) + "\n"; sw.Write(title); string line = string.Empty; foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { lineBuilder.Append(row[i].ToString().Trim() + "\t"); //内容：自动跳到下一单元格 } line = lineBuilder.ToString(); line = line.Substring(0, line.Length - 1) + "\n"; sw.Write(line); lineBuilder.Clear(); } sw.Close(); fs.Close(); }