[ASP.NET] Export Excel with NPOI and EPPlus

NPOI：使用於(Excel2003及之前的版本)，可寫出Excel2003(xls)，可讀Excel2003 (xls)/ Excel2007(xlsx)

官網:

• http://npoi.codeplex.com/ (~1.2.3)
• http://code.google.com/p/npoi/ (1.2.4)

System Requirement:

• VS2005 or VS2008 with .NET 2.0 Runtime (SP1)
• vs2003 with .NET 1.1
• medium trust environment in ASP.NET

EPPlus：EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

官網：http://epplus.codeplex.com/
System Requirements:

• .Net Framwork 3.5 or higher

NPOI vs. EPPlus：比較匯出大量資料的時間



NPOI：

private void ExportNPOI()
{
    logger.Info("begin npoi");

    HSSFWorkbook workbook = new HSSFWorkbook();
    MemoryStream ms = new MemoryStream();
    HSSFSheet sheet = workbook.CreateSheet();

    for (int i = 0; i < 65535; i++)
    {
        HSSFRow row = sheet.CreateRow(i);
        row.CreateCell(0).SetCellValue("npoiformats");
    }

    workbook.Write(ms);

    logger.Info("end npoi");

    Response.ContentType = "application/download";
    Response.AddHeader("Content-Disposition", "attachment; filename=file.xls");
    Response.Clear();
    Response.BinaryWrite(ms.GetBuffer());

    ms.Close();
    ms.Dispose();
}

EPPlus：

private void ExportEPPlus()
{
    logger.Info("begin epplus");
    
    ExcelPackage pck = new ExcelPackage();
    var ws = pck.Workbook.Worksheets.Add("Sample2");

    for (int i = 1; i < 65999; i++)
    {
        ws.Cells[i, 1].Value = "openxmlformats";	//Set the value of cell A1 to 1
        ws.Cells[i, 2].Value = "openxmlformats";
        ws.Cells[i, 3].Value = "openxmlformats";
        ws.Cells[i, 4].Value = "openxmlformats";
        ws.Cells[i, 5].Value = "openxmlformats";
    }

    logger.Info("end epplus");

    Response.Clear();
    Response.AddHeader("content-disposition", "attachment; filename=file.xlsx");
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.BinaryWrite(pck.GetAsByteArray());
    Response.End();            
}

Excel2007可會出超過65535筆資料。
使用NPOI產出65535筆資料花費41 sec，我測試的最佳紀錄為25秒。
使用EPPlus產出65999筆花費2 sec。

Log File：

2011-07-21 15:09:18.8851|INFO|ExcelCompare._Default|begin npoi
2011-07-21 15:09:59.2133|INFO|ExcelCompare._Default|end npoi

2011-07-21 15:10:02.4633|INFO|ExcelCompare._Default|begin epplus
2011-07-21 15:10:04.7758|INFO|ExcelCompare._Default|end epplus