2012-04-21

【推薦套件】Excel利器 NPOI VS EPPLUS

44626
0
推薦套件
2015-11-29

【工具】Excel利器—NPOI VS EPPLUS

由於最近客戶端不停抱怨，查詢會當機。

其實，這算是老問題了，主要原因是：

Query效能不佳
由於該查詢會使用到的Table交易量很大，容易會有Wait的現象到最後就TimeOut了。
查詢資料量太大，目前是放到DataSet之後直接用GridView綁定。

其實，以上種種造成因素太多了。

再加上，查詢出來的結果使用端整批下載或是自訂下載成Excel。

最一開始的版本的使用Office原生的套件寫到Excel，在網頁TimeOut前的極限大約是6000~9000筆。

而，我接手以後我把它改成直接用Html Render出來，在網頁TimeOut前的極限大約是10000~14000筆。

但是，這樣還是有不少問題。

最後，最近開會主管提出一種方案，讓使用者需要大量資料時，用離線的方式製作。

這樣的好處有：

SQL Command TimeOut 可以拉長。
不用再受限網頁TimeOut的問題點。
可以避開尖峰時間，讓這些又臭又長的Query在深夜裡享受效能。

好啦....既然決定要做總要改變用更好的工具吧！！

NPOI應該已經有很多人聽聞過了，主管在前幾天也剛好提到有一套第三方原件EPPlus。

網路上其實已經有人寫過類似評測：皮尼網前走

上面那篇文章，看起來主要是網頁版的時間比較，看起來EPPlus根本就是讓NPOI看不到車尾燈。

但是，我要的是Console版的比較，就自己來學啦！！

我對NPOI和EPPlus其實都不熟，靠著範例&部分教學拼湊出來了以下結論。

NPOI

我是使用官方最新版本1.2.4，NPOI只能存取Office 2003的版本，我的目的是有一個Templete，我們只把資料塞到各列中。

步驟一：將下載的DLL，加入至參考(此部分不另說明)。

步驟二：

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;

步驟三：

//將WorkBook指到我們原本設計好的Templete Book1.xls

using (IWorkbook wb = new HSSFWorkbook(new FileStream("D:/Book1.xls", FileMode.Open)))
{
    try
    {
        //設定要使用的Sheet為第0個Sheet
        ISheet TempSheet = wb.GetSheetAt(0);
        int StartRow = 4;

        //tDS為Query帶回來的資料
        for (int i = 0; i < tDS.Tables[0].Rows.Count; i++)
        {
            //第一個Row要用Create的
            TempSheet.CreateRow(StartRow + i).CreateCell(0).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][0]));

            //第二個Row之後直接用Get的
            TempSheet.GetRow(StartRow + i).CreateCell(1).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][1]));
            TempSheet.GetRow(StartRow + i).CreateCell(2).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][2]));
            TempSheet.GetRow(StartRow + i).CreateCell(3).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][3]));
            TempSheet.GetRow(StartRow + i).CreateCell(4).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][4]));
            TempSheet.GetRow(StartRow + i).CreateCell(5).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][5]));
            TempSheet.GetRow(StartRow + i).CreateCell(6).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][6]));
            TempSheet.GetRow(StartRow + i).CreateCell(7).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][7]));
            TempSheet.GetRow(StartRow + i).CreateCell(8).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][8]));
            TempSheet.GetRow(StartRow + i).CreateCell(9).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][9]));
            TempSheet.GetRow(StartRow + i).CreateCell(10).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][10]));
            TempSheet.GetRow(StartRow + i).CreateCell(11).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][11]));
            TempSheet.GetRow(StartRow + i).CreateCell(12).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][12]));
        }
		
        //將檔案寫到指定位置
        using (FileStream file = new FileStream("H:/Test_NPOI4.xls", FileMode.Create))
        {
            wb.Write(file);
            file.Close();
            file.Dispose();
        }
    }
    catch (Exception e)
    {
        string a = e.ToString();
    }
}

NPOI的程式撰寫部分大概就是上方的方式。

EPPLUS

EPPlus我也是用官方最新版本2.6.0.1，EPPluse適用Office 2007&2010，但要注意，只支援.net 3.5以上，至於程式目的跟NPOI是一樣的。

步驟一：也是把下載的DLL加入到參考。

步驟二：

FileInfo newFile = new FileInfo("D:" + @"\Test.xlsx");
//開啟
using (ExcelPackage pck = new ExcelPackage(newFile))
{
    try
    {
        //設定ExcelWorkBook
        ExcelWorkbook workBook = pck.Workbook;
        if (workBook != null)
        {
            if (workBook.Worksheets.Count > 0)
            {
                //複製Temp這個Sheet同時命名為《清單》
                ExcelWorksheet currentWorksheet = workBook.Worksheets.Copy("Temp", "清單");
				
                //可以設定保護Sheet的密碼
                //currentWorksheet.Protection.SetPassword("1234");
				
                int StartRow = 4;

                for (int i = 0; i < tDS.Tables[0].Rows.Count; i++)
                {
                    //Cells[RowIndex,CellIndex]
                    currentWorksheet.Cells[StartRow + i, 1].Value = Convert.ToString(tDS.Tables[0].Rows[i][0]);
                    currentWorksheet.Cells[StartRow + i, 2].Value = Convert.ToString(tDS.Tables[0].Rows[i][1]);
                    currentWorksheet.Cells[StartRow + i, 3].Value = Convert.ToString(tDS.Tables[0].Rows[i][2]);
                    currentWorksheet.Cells[StartRow + i, 4].Value = Convert.ToString(tDS.Tables[0].Rows[i][3]);
                    currentWorksheet.Cells[StartRow + i, 5].Value = Convert.ToString(tDS.Tables[0].Rows[i][4]);
                    currentWorksheet.Cells[StartRow + i, 6].Value = Convert.ToString(tDS.Tables[0].Rows[i][5]);
                    currentWorksheet.Cells[StartRow + i, 7].Value = Convert.ToString(tDS.Tables[0].Rows[i][6]);
                    currentWorksheet.Cells[StartRow + i, 8].Value = Convert.ToString(tDS.Tables[0].Rows[i][7]);
                    currentWorksheet.Cells[StartRow + i, 9].Value = Convert.ToString(tDS.Tables[0].Rows[i][8]);
                    currentWorksheet.Cells[StartRow + i, 10].Value = Convert.ToString(tDS.Tables[0].Rows[i][9]);
                    currentWorksheet.Cells[StartRow + i, 11].Value = Convert.ToString(tDS.Tables[0].Rows[i][10]);
                    currentWorksheet.Cells[StartRow + i, 12].Value = Convert.ToString(tDS.Tables[0].Rows[i][11]);
                    currentWorksheet.Cells[StartRow + i, 13].Value = Convert.ToString(tDS.Tables[0].Rows[i][12]);
                }
				
                //將Temp 這個Sheet刪除
                workBook.Worksheets.Delete("Temp");
            }
        }
		
        //存檔至Text4.xlsx
        pck.SaveAs(new FileInfo("H:" + @"\Test4.xlsx"));
    }
    catch (Exception e)
    {
        oLogger.Fatal(e.ToString());
    }
}

EPPlus程式撰寫方是大概就如上方。

目前不確定我兩種元件的寫法，有沒有可能會造成資源被Lock住，或著是沒有釋放掉，

這部份如果有高人願意指教非常之歡迎喔！！

寫法說明完，要開始評測啦！

首先我們先來看執行速度，兩種元件我都分別跑了10000~40000左右的筆數。

以下是我整理的列表，開始到結束時間是從算出資料筆數後開始計算，因為這次的目的是寫檔案的速度，要扣除掉Query執行時間。

NOPI

開始到結束：3秒，檔案大小：7.25MB 2012-04-21 00:23:07.6900 BathExcel.Program INFO 開始 2012-04-21 00:23:12.2333 BathExcel.Program INFO 資料筆數 9849 2012-04-21 00:23:15.4474 BathExcel.Program INFO 結束開始到結束：2秒，檔案大小：12.8MB 2012-04-21 00:24:12.9077 BathExcel.Program INFO 開始 2012-04-21 00:24:22.5753 BathExcel.Program INFO 資料筆數 17681 2012-04-21 00:24:24.3514 BathExcel.Program INFO 結束開始到結束：3秒，檔案大小：21.4MB 2012-04-21 00:24:41.5194 BathExcel.Program INFO 開始 2012-04-21 00:24:56.1772 BathExcel.Program INFO 資料筆數 29368 2012-04-21 00:24:59.4544 BathExcel.Program INFO 結束開始到結束：8秒，檔案大小：31.6MB 2012-04-21 00:25:17.5354 BathExcel.Program INFO 開始 2012-04-21 00:25:38.9126 BathExcel.Program INFO 資料筆數 43178 2012-04-21 00:25:44.1159 BathExcel.Program INFO 結束

EEPLUS

開始到結束：2秒，檔案大小：2.88MB 2012-04-21 00:26:51.8548 BathExcel.Program INFO 開始 2012-04-21 00:26:56.2361 BathExcel.Program INFO 資料筆數 9849 2012-04-21 00:26:58.7312 BathExcel.Program INFO 結束開始到結束：4秒，檔案大小：5.12MB 2012-04-21 00:27:16.2712 BathExcel.Program INFO 開始 2012-04-21 00:27:24.2037 BathExcel.Program INFO 資料筆數 17681 2012-04-21 00:27:28.8359 BathExcel.Program INFO 結束開始到結束：8秒，檔案大小：8.6MB 2012-04-21 00:27:45.5839 BathExcel.Program INFO 開始 2012-04-21 00:27:57.7826 BathExcel.Program INFO 資料筆數 29368 2012-04-21 00:28:05.7360 BathExcel.Program INFO 結束開始到結束：13秒，檔案大小：12.6MB 2012-04-21 00:28:23.6411 BathExcel.Program INFO 開始 2012-04-21 00:28:41.8771 BathExcel.Program INFO 資料筆數 43178 2012-04-21 00:28:54.2988 BathExcel.Program INFO 結束

看完這數據，我發現這跟網頁上那篇評論有很大的落差，不知道是哪裡造成就是，但是這個數據符合我的實際情況。

總結：

在執行速度方面：NPOI小贏一點。
在檔案大小方面：EEPLus大勝。
在撰寫感受方面：可能都是剛接觸不熟，但現階段我個人認為EEPlus小勝一籌。
在資源支援方面：NPOI勝，因EEPlus目前大多還是英文較多，但NPOI已有中文的完整文件。

最後，你問我我會選擇哪一套，答案是，我會選擇EEPlus，因為我喜歡他的寫作方式。

希望各位看完以後，有甚麼問題或可以指教我的地方盡量提出來，謝謝！

NPOI參考資料：

EPPlus參考資料：

回首頁

小小工程師

努力生活的螃蟹

【推薦套件】Excel利器 NPOI VS EPPLUS

NPOI

EPPLUS

NOPI

EEPLUS

標籤雲

系列文章