### [ASP.NET WebForm] NPOI2.0產出EXCEL檔案

NPOI 2.0 Templete Example

Work is confortable !

1.首先我們會先將EXCEL檔案調整至程式可以處理的樣式(公式我們處理完資料後補上並計算)

• 將舊有資料刪除 (之後由程式填入)
• 標題列置中對齊
2.將EXCEL範本存檔並放入Visual Studio 專案中 (個人習慣將它放在Templete 底下做管理)

3.撰寫程式碼新增資料並補上公式與重新運算
``````protected void export_btn_Click(object sender, EventArgs e)
{
//開啟我們的範例檔案
using (FileStream file = new FileStream(Server.MapPath(@"~/Templete/FixedDemoTemplete.xlsx"), FileMode.Open, FileAccess.Read))
{
//輸出檔名(我習慣加上日期時間)
string fileName = "學生成績單資料_" + DateTime.Now.ToString();
//開啟workbook檔案
IWorkbook workbook = new XSSFWorkbook(file);
//指定為第一個頁籤
ISheet sheet = workbook.GetSheetAt(0);

var lst = CreateExportData();

//從第二列到第三列，往下移動lst.Count列
sheet.ShiftRows(1, 2, lst.Count);

for (int i = 0; i < lst.Count; i++)
{
//5.依列逐格填入資料
var l = lst.ElementAt(i);
sheet.CreateRow(i + 1);
sheet.GetRow(i + 1).CreateCell(0).SetCellValue(l.StuName);
sheet.GetRow(i + 1).CreateCell(1).SetCellValue(l.Chinese);
sheet.GetRow(i + 1).CreateCell(2).SetCellValue(l.English);
sheet.GetRow(i + 1).CreateCell(3).SetCellValue(l.Math);
}

//資料總比數
var dataCount = lst.Count;

//科目總分計算行
string[] subjectTotalColumns = new string[] { "B", "C", "D", "E" };
if (dataCount > 0)
{
IRow subjectTotalRow = sheet.GetRow(dataCount + 1);
IRow subjectAverageRow = sheet.GetRow(dataCount + 2);
for (int i = 0; i < subjectTotalColumns.Count(); i++)
{
subjectTotalRow.CreateCell(i + 1).CellFormula = string.Format("SUM({0}2:{0}{1})", subjectTotalColumns[i], dataCount + 1);
subjectAverageRow.CreateCell(i + 1).CellFormula = string.Format("AVERAGE({0}2:{0}{1})", subjectTotalColumns[i], dataCount + 1);
}

//個人總分計算
string startPersonColunn = "B";
string endPersonColumn = "D";

for (int i = 0; i < dataCount; i++)
{
IRow personTotalRow = sheet.GetRow(i + 1);
personTotalRow.CreateCell(4).CellFormula = string.Format("SUM({0}{1}:{2}{1})", startPersonColunn, i + 2, endPersonColumn);
}
for (int i = 0; i < dataCount + 2; i++)
{
IRow personTotalRow = sheet.GetRow(i + 1);
personTotalRow.CreateCell(5).CellFormula = string.Format("AVERAGE({0}{1}:{2}{1})", startPersonColunn, i + 2, endPersonColumn);
}
}
//重新計算公式內的值
sheet.ForceFormulaRecalculation = true;
#region 輸出
MemoryStream ms = new MemoryStream();
workbook.Write(ms);

//設定檔名, IE 要特殊處理
if (HttpContext.Current.Request.Browser.Browser == "IE" || HttpContext.Current.Request.Browser.Browser == "InternetExplorer") fileName = HttpContext.Current.Server.UrlPathEncode(fileName);

HttpContext.Current.Response.Clear();

//此列針對xlsx錯誤做修正處理，若使用hssfworkbook則不需要此行

HttpContext.Current.Response.Cache.SetNoStore();
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.BinaryWrite(ms.ToArray());

workbook = null;
ms.Close();
ms.Dispose();

HttpContext.Current.Response.End();
#endregion
}
}``````

Summary

1. 開啟你要處理的範例檔案
2. 填入整理好的資料
3. 開始填寫公式
4. 重新計算公式
5. 輸出檔案
6. 關閉資源

https://github.com/hatedinny1/ExcelDemoProject