Brian's Develop Club

正文

我們先模擬出一份簡單的基本加總Excel 檔案，

迷之音:由於範例會需要做一些小調整，所以還是跟自身EXCEL操作能力有很大的相關 QAQ

我們可以看到上圖是一份基本的成績單檔案，

正常來說我們的學生與分數會用抓取資料庫並將總分與分數統計計算出來，那我們該如何處理呢 ?

以下只做了兩項調整

• 將舊有資料刪除 (之後由程式填入)
• 標題列置中對齊

 

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.ClearHeaders();
        HttpContext.Current.Response.Clear();

        //此列針對xlsx錯誤做修正處理，若使用hssfworkbook則不需要此行
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        HttpContext.Current.Response.Cache.SetNoStore();
        HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
        HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + fileName + ".xlsx"));
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());

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

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

Summary

程式碼雖然看起來很長，可是其實歸納起來只有幾項需要注意的

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

下面是程式產出來的範例檔 :

結語

謝謝各位的收看，

上面的作法是我目前工作上面處理EXCEL常見的做法，

並不敢保證這樣是最好的辦法，

若有謬誤再麻煩各位指正，謝謝 。

完整程式碼範例

https://github.com/hatedinny1/ExcelDemoProject