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暂时没有发现这个问题