NPOI vs EPPlus 導出Excel簡單對比

1.引用NPOI.dll和EPPlus.dll

http://epplus.codeplex.com/ 

http://npoi.codeplex.com/   ---- 首先應去下載下傳需要引用的dll并引用進項目

2.EPPlus的導出實驗

/// <summary>
        /// EPPlus V4.1 for .NET3.5 24.8萬行資料，總用時14秒,EXCEL共6.24MB
        /// </summary>
        protected void ToExcel_epp(DataTable dt)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("sheet1");   //建立sheet
                //worksheet.Cells[1, 1, 3, 6].Merge = true;        //合并單元格 worksheet.Cells[int fromRow, fromCol, int toRow,int toCol]
                //worksheet.Cells["A1"].Value = "LOUV資料彙總";  //标題
                worksheet.Cells.Style.Font.Name = "華文細黑";  //字型設定
                worksheet.Cells.Style.Font.Size = 12;
                worksheet.Column(0).Style.Font.Name = "微軟雅黑"; //根據列設定樣式
                worksheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;  //邊框樣式
                worksheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                worksheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                worksheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;  //水準對齊方式
                worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;  //垂直對齊方式
                //worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.DarkGray);  //背景色
                //worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.LightUp;
                //worksheet.Cells.Style.WrapText = true;  //折行顯示
                worksheet.Cells.Style.ShrinkToFit = true;  //單元格自适應大小

                //寫入data
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    worksheet.Cells[i + 3, 1].Value = dt.Rows[i]["VouID"];
                    worksheet.Cells[i + 3, 2].Value = dt.Rows[i]["SampleName"];
                    worksheet.Cells[i + 3, 3].Value = dt.Rows[i]["Mat"];
                    worksheet.Cells[i + 3, 4].Value = dt.Rows[i]["LINE"];
                    worksheet.Cells[i + 3, 5].Value = dt.Rows[i]["UserID"];
                    worksheet.Cells[i + 3, 6].Value = dt.Rows[i]["Grade"];
                }

                //Write it back to the client
                var data = package.GetAsByteArray();
                Response.Clear();
                Response.AddHeader("content-disposition", "attachment; filename=Lvou.xlsx");
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.BinaryWrite(data);
                Response.Flush();
                Response.End();
            }
        }
           

3.NPOI的導出實驗

/// <summary>
        /// NPOI V2.0 for .NET3.5 24.8萬行資料，總用時13秒，EXCEL共32MB
        /// </summary>
        protected void ToExcel_npoi(DataTable dt)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();//建立sheet
            //ISheet sheet = workbook.GetSheet("sheet1"); //擷取到有名字的sheet
            //ISheet sheet = workbook.GetSheetAt(0);//擷取索引處的sheet
            //workbook.SetSheetName(0, "Lvou表"); //設定索引為0的sheet的名字

            #region 右擊檔案 屬性資訊
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author = "檔案作者資訊"; //填加xls檔案作者資訊
                si.ApplicationName = "建立程式資訊"; //填加xls檔案建立程式資訊
                si.LastAuthor = "最後儲存者資訊"; //填加xls檔案最後儲存者資訊
                si.Comments = "作者資訊"; //填加xls檔案作者資訊
                si.Title = "标題資訊"; //填加xls檔案标題資訊
                si.Subject = "主題資訊";//填加檔案主題資訊
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle cellstyle = workbook.CreateCellStyle(); //樣式
            //IDataFormat format = workbook.CreateDataFormat();
            //cellstyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            sheet.SetColumnWidth(0,5000); //設定列寬
            sheet.SetColumnWidth(1,3000);
            IFont font = workbook.CreateFont();
            font.FontName = "微軟雅黑";
            //font.Boldweight = 700;
            cellstyle.SetFont(font);

            #region 列頭及樣式
            //{
            //    HSSFRow headerRow = sheet.CreateRow(1);
            //    HSSFCellStyle headStyle = workbook.CreateCellStyle();
            //    headStyle.Alignment = NPOI.HSSF.UserModel.HSSFCellStyle.ALIGN_CENTER;
            //    HSSFFont font = workbook.CreateFont();
            //    font.FontHeightInPoints = 10;
            //    font.Boldweight = 700;
            //    headStyle.SetFont(font);
            //    foreach (DataColumn column in dtSource.Columns)
            //    {
            //        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            //        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

            //        //設定列寬
            //        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
            //    }
            //    // headerRow.Dispose();
            //}
            #endregion

            #region 取得列寬
            //int[] arrColWidth = new int[dt.Columns.Count];
            //foreach (DataColumn item in dt.Columns)
            //{
            //    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            //}
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    for (int j = 0; j < dt.Columns.Count; j++)
            //    {
            //        int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
            //        if (intTemp > arrColWidth[j])
            //        {
            //            arrColWidth[j] = intTemp;
            //        }
            //    }
            //}
            #endregion

            //寫入資料
            int k = 1;
            int q = 1;
            foreach (DataRow dr in dt.Rows)
            {
                if (k == 60000)
                {
                    sheet = workbook.CreateSheet("sheet" + q);
                    k = 1;
                    q++;
                }
                IRow row = sheet.CreateRow(k+2);
                
                row.CreateCell(0).SetCellValue(dr["VouID"].ToString());
                row.GetCell(0).CellStyle = cellstyle;
                row.CreateCell(1).SetCellValue(dr["SampleName"].ToString());
                row.GetCell(1).CellStyle = cellstyle;
                row.CreateCell(2).SetCellValue(dr["Mat"].ToString());
                row.GetCell(2).CellStyle = cellstyle;
                row.CreateCell(3).SetCellValue(dr["LINE"].ToString());
                row.GetCell(3).CellStyle = cellstyle;
                row.CreateCell(4).SetCellValue(dr["UserID"].ToString());
                row.GetCell(4).CellStyle = cellstyle;
                row.CreateCell(5).SetCellValue(dr["Grade"].ToString());
                row.GetCell(5).CellStyle = cellstyle;
                k++;
            }

            HttpContext curContext = HttpContext.Current;
            // 設定編碼和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("Lvou.xls", Encoding.UTF8));
            Response.BinaryWrite(WriteToStream(workbook).GetBuffer());
            Response.End();
        }
        
        /// 導出到檔案流
        /// <summary>
        /// 導出到檔案流
        /// </summary>
        /// <param name="hssfworkbook"></param>
        /// <returns></returns>
        private MemoryStream WriteToStream(HSSFWorkbook hssfWorkbook)
        {
            MemoryStream file = new MemoryStream();
            hssfWorkbook.Write(file);
            return file;
        }
           

4.結論

速度：相差不大（EPPlus：14s NPOI：13s） 導出檔案大小：EPPlus完勝（EPPlus：6.24MB NPOI：32MB） 操作難度：EPPlus感覺上略為人性化一點 其他：NPOI一個sheet最多容納65536行資料，故資料量大于65536時需要分多個sheet，較為麻煩，EPPlus暫時沒有發現這個問題