[ASP.NET]Use NPOI library to generate a excel from template excel
需求：
從預先定義好的Excel檔中，透過程式的方式填充部份cell的資料，且Excel內容有定義
公式的Cell必須要能自動計算處理完成，並產生新的Excel下載至使用者端
實作：
- 利用NPOI Library來幫我們操控有關Excel的部份
- 透過Http直接輸出資料流提供使用者端download file
範例演示：
(1)目前NPOI最新版為1.2.5版，可直接至官方網站download
(2)加入參考，有.net2.0及.net4.0二個版本可以選擇
(3)Using
(4)假設有個Template.xls如下，其中時薪及工時須由資料庫找出資料填充，而實領薪資
則是已經定義好在Cell裡的公式
(5)程式碼
protected void GetXls_Click(object sender, EventArgs e)
{
string path = Server.MapPath(@"\template.xls");
HSSFWorkbook xlsfile = new HSSFWorkbook();
xlsfile = AutoXls(path);
using (MemoryStream menorystream = new MemoryStream())
{
//xls資料寫入MemoryStream
xlsfile.Write(menorystream);
DownLoadFile(menorystream);
}
}
private HSSFWorkbook AutoXls(string path)
{
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook xlsfile = new HSSFWorkbook(file);
ISheet sheet = xlsfile.GetSheetAt(0);
if (sheet != null)
{
//填充時薪
sheet.GetRow(1).GetCell(1).SetCellValue(120);
sheet.GetRow(2).GetCell(1).SetCellValue(175);
sheet.GetRow(3).GetCell(1).SetCellValue(135);
//填充工時
sheet.GetRow(1).GetCell(2).SetCellValue(30);
sheet.GetRow(2).GetCell(2).SetCellValue(55);
sheet.GetRow(3).GetCell(2).SetCellValue(60);
//公式重新計算
sheet.ForceFormulaRecalculation = true;
}
return xlsfile;
}
}
private void DownLoadFile(MemoryStream stream)
{
Response.ClearHeaders();
Response.Clear();
Response.Expires = 0;
Response.Buffer = false;
Response.ContentType = "Application/save-as";
Response.Charset = "utf-8";
Response.AddHeader("Content-Disposition", "Attachment; filename=" + HttpUtility.UrlEncode(Path.GetTempFileName() + ".xls"));
Response.BinaryWrite(stream.GetBuffer());
Response.End();
}
(6)結果
注意事項：
-
目前NPOI版本只支援97-2003的excel檔，根據官方資料說明，是有計劃今年會推
出支援2010 excel
-
在Template檔案中，要由程式填充資料的Cell建議先給預設值，不然在抓取cell時
很容易出現nullreferenceexception的錯誤，這個部份花了點時間測試才發現給預
設值是比較ok的做法
若本文對您有所幫助，歡迎轉貼，但請在加註【轉貼】及來源出處，並在附上本篇的超連結，感恩您的配合囉。
By No.18