C# 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导出：

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();

        }

EPPlus导出：

/// <summary>
        /// 使用EPPlus导出Excel(xlsx)
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="strFileName">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();
            }
        }

程序生成DataTable，20列，内容如下图

测试结果

条数	NPOI	EPPlus
10000	成功生成	成功生成
20000	成功生成	成功生成
30000	成功生成	成功生成
40000	成功生成	成功生成
50000	失败	成功生成
100000	失败	成功生成
200000	失败	成功生成
230000	失败	失败