系统中经常会使用导出Excel的功能。
之前使用的是NPOI，但是导出数据行数多就报内存溢出。
最近看到EPPlus可以用来导出Excel，就自己测了下两者导出上的差异。
NPIO官网地址：
EPPlus官网地址：
添加NPOI、EPPlus类库dll使用的是NuGet添加。
在类库References右键Manage NuGet Packages...，之后选择添加对应的dll。
测试结果显示，相同数据结构的数据，EPPlus的导出能力比NPOI强。
20列，，NPOI能导出4万数据，导出5万数据时报内存溢出。
EPPlus能导出20万以上数据，导出23万测试时内存溢出。
NPOI导出：
private static MemoryStream ExportXlsx(DataTable dt) { XSSFWorkbook workbook = new XSSFWorkbook(); ISheet sheet = null; int headRowIndex = 0; string sheetName = "Sheet1"; if (!string.IsNullOrEmpty(dt.TableName)) { sheetName = dt.TableName; } sheet = workbook.CreateSheet(sheetName); int rowIndex = 0; #region 列头及样式 { XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dt.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; } } #endregion #region 填充内容 foreach (DataRow row in dt.Rows) { rowIndex++; XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { string drValue = row[column].ToString(); dataRow.CreateCell(column.Ordinal).SetCellValue(drValue); } } #endregion MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); return ms; } public static void ExportXlsxByWeb(DataTable dt, string strFileName) { HttpContext curContext = HttpContext.Current; MemoryStream ms = ExportXlsx(dt); curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx"); curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString()); curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.BinaryWrite(ms.ToArray()); ms.Close(); ms.Dispose(); curContext.Response.End(); }
View CodeEPPlus导出：
/// <summary> /// 使用EPPlus导出Excel(xlsx) /// </summary> /// <param>数据源</param> /// <param>xlsx文件名(不含后缀名)</param> public static void ExportByEPPlus(DataTable sourceTable, string strFileName) { using (ExcelPackage pck = new ExcelPackage()) { //Create the worksheet string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName; ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName); //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 ws.Cells["A1"].LoadFromDataTable(sourceTable, true); //Format the row ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin; Color borderColor = Color.FromArgb(155, 155, 155); using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count]) { rng.Style.Font.Name = "宋体"; rng.Style.Font.Size = 10; rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255)); rng.Style.Border.Top.Style = borderStyle; rng.Style.Border.Top.Color.SetColor(borderColor); rng.Style.Border.Bottom.Style = borderStyle; rng.Style.Border.Bottom.Color.SetColor(borderColor); rng.Style.Border.Right.Style = borderStyle; rng.Style.Border.Right.Color.SetColor(borderColor); } //Format the header row using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count]) { rng.Style.Font.Bold = true; rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246)); //Set color to dark blue rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51)); } //Write it back to the client HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8))); HttpContext.Current.Response.ContentEncoding = Encoding.UTF8; HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray()); HttpContext.Current.Response.End(); } }
View Code