系統中經常會使用導出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 }
程序生成DataTable，20列，內容如下圖
電腦配置：
測試結果：
條數 NPOI EPPlus 10000 成功生成 成功生成 20000 成功生成 成功生成 30000 成功生成 成功生成 40000 成功生成 成功生成 50000 失敗 成功生成 100000 失敗 成功生成 200000 失敗 成功生成 230000 失敗 失敗
