转自：http://www.cnblogs.com/tanpeng/p/6155749.html
系统中经常会使用导出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 | 失败 | 失败 |