【工具】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參考資料：