以 Excel 模板 + 宏 生成複雜格式報表 — 避開逐儲存格寫入的效能與維護地獄

前言

傳統用程式碼逐儲存格寫入 Excel (Cells[row, col] = value) 既慢又難維護：格式、合併儲存格、公式、圖表、分頁列印… 每一項都要寫代碼控制。本篇介紹 「模板 + 宏」 架構：先在 Excel 設計好完整樣板 (含格式、公式、條件格式、樞紐分析表…)，程式只負責「填資料、觸發宏」、最後另存新檔。開發效率提升 10 倍，報表維護由業務/財務人員直接在 Excel 調整，無需改程式碼重新部署。

架構概覽

┌──────────────┐     填入基礎參數      ┌──────────────┐
│  程式端       │ ──────────────────→ │  Excel 模板   │
│  (C# / VB)   │  (Sheet2: 日期/部門) │  (三個工作表) │
└──────────────┘                     └──────┬───────┘
                                            │
                                            ▼ 觸發巨集 (Macro)
                                   ┌────────────────────┐
                                   │  Macro: 複製樣板列、│
                                   │  填入明細資料、    │
                                   │  自動擴展公式區域  │
                                   └────────┬───────────┘
                                            │
                                            ▼
                                   ┌────────────────────┐
                                   │  另存為 .xlsx →    │
                                   │  交付使用者/列印   │
                                   └────────────────────┘

Excel 模板設計三工作表法

Sheet1：報表輸出頁 (最終呈現)

• 標題列：固定文字
• 動態參數區 (第 3 列)：
  • 日期 ← 參考 `Sheet2!A2`
  • 部門 ← 參考 `Sheet2!B2`
  • 考勤員 ← 參考 `Sheet2!C2`
• 欄位標題列 (第 5 列)
• 資料區 (第 8-9 列預留 2 列樣板)：
  • 第 8、9 列設定好所有格式、邊框、條件格式
  • 第 10 列為彙總列，公式如 =SUM(C8:C9)
  • 關鍵：巨集在 8/9 列之間插入新列 → Excel 自動擴展公式範圍 (SUM(C8:C10))、格式自動繼承
• 底部簽核欄、浮水印等固定元素

Sheet2：參數輸入頁 (單一列)

程式寫入單列資料：`A2=2024/01/15`, `B2=資訊部`, `C2=王小明`

Sheet3：明細資料來源頁 (多列)

程式一次性寫入所有明細資料 (DataTable → 陣列 → Range.Value2)

核心 VBA 宏程式碼 (標準模組 Module1)

Option Explicit

Sub GenerateReport()
    ' 關閉螢幕更新、事件、自動計算 → 极速執行
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    On Error GoTo ErrHandler

    Dim wsRpt As Worksheet: Set wsRpt = ThisWorkbook.Sheets("Sheet1")
    Dim wsData As Worksheet: Set wsData = ThisWorkbook.Sheets("Sheet3")
    
    ' 1. 取得明細資料筆數 (假設 A 欄有資料即為有效列)
    Dim lastRow As Long
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then GoTo ExitSub ' 無資料
    
    Dim dataRows As Long: dataRows = lastRow - 1 ' 扣除標題列
    
    ' 2. 在樣板列 (第 8 列) 下方插入 (dataRows - 1) 列
    '    因已有 2 列樣板 (8,9)，需再插 dataRows-2 列
    If dataRows > 2 Then
        wsRpt.Rows("9:9").Resize(dataRows - 2).Insert Shift:=xlDown, _
            CopyOrigin:=xlFormatFromLeftOrAbove
    End If
    
    ' 3. 將明細資料一次性寫入報表資料區 (第 8 列起)
    Dim srcRange As Range
    Set srcRange = wsData.Range("A2").Resize(dataRows, 20) ' 假設 20 欄
    Dim destRange As Range
    Set destRange = wsRpt.Range("A8").Resize(dataRows, 20)
    destRange.Value2 = srcRange.Value2 ' 陣列賦值，極快
    
    ' 4. 強制重新計算 (更新彙總公式、條件格式)
    Application.Calculation = xlCalculationAutomatic
    wsRpt.Calculate
    
    ' 5. 可選：列印預覽、另存 PDF、Email 發送等
    ' wsRpt.ExportAsFixedFormat xlTypePDF, "C:\Reports\Monthly.pdf"

ExitSub:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

ErrHandler:
    MsgBox "巨集執行錯誤: " & Err.Description, vbCritical
    Resume ExitSub
End Sub

C# 程式端整合 (使用 Microsoft.Office.Interop.Excel)

using System;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

public class ExcelReportGenerator
{
    private readonly string _templatePath = @"C:\Templates\AttendanceReport.xlsm"; // 巨集啟用活頁簿
    private readonly string _outputDir = @"C:\Reports\";

    public string Generate(DateTime reportDate, string deptId, string operatorName)
    {
        Excel.Application xlApp = null;
        Excel.Workbook wb = null;
        try
        {
            xlApp = new Excel.Application { Visible = false, DisplayAlerts = false };
            wb = xlApp.Workbooks.Open(_templatePath, ReadOnly: false); // 必須 ReadOnly=false 才能寫入/儲存
            
            // 1. 寫入參數到 Sheet2
            var wsParam = (Excel.Worksheet)wb.Sheets["Sheet2"];
            wsParam.Range["A2"].Value2 = reportDate.ToString("yyyy/MM/dd");
            wsParam.Range["B2"].Value2 = deptId;
            wsParam.Range["C2"].Value2 = operatorName;

            // 2. 寫入明細資料到 Sheet3 (高效能陣列寫入)
            var wsData = (Excel.Worksheet)wb.Sheets["Sheet3"];
            DataTable dt = GetAttendanceDetail(reportDate, deptId); // 從 DB 取資料
            if (dt.Rows.Count > 0)
            {
                object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
                for (int r = 0; r < dt.Rows.Count; r++)
                    for (int c = 0; c < dt.Columns.Count; c++)
                        arr[r, c] = dt.Rows[r][c];
                
                var dest = wsData.Range["A2"].Resize[dt.Rows.Count, dt.Columns.Count];
                dest.Value2 = arr; // 一次性寫入
            }

            // 3. 執行巨集 (產生報表格式、擴展公式)
            xlApp.Run("GenerateReport"); // 宏名稱

            // 4. 另存為 .xlsx (移除巨集、壓縮體積)
            string outputFile = $"{_outputDir}Attendance_{deptId}_{reportDate:yyyyMMdd}.xlsx";
            wb.SaveAs(outputFile, Excel.XlFileFormat.xlOpenXMLWorkbook); // .xlsx 非 .xlsm
            return outputFile;
        }
        finally
        {
            wb?.Close(false);
            xlApp?.Quit();
            // 釋放 COM 物件
            if (wb != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
            if (xlApp != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
            GC.Collect(); GC.WaitForPendingFinalizers();
        }
    }

    private DataTable GetAttendanceDetail(DateTime date, string deptId)
    {
        // 實際從 SQL Server 取資料
        var dt = new DataTable();
        using (var conn = new SqlConnection("YourConnectionString"))
        using (var cmd = new SqlCommand("sp_GetAttendance", conn) { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue("@Date", date);
            cmd.Parameters.AddWithValue("@DeptId", deptId);
            using (var da = new SqlDataAdapter(cmd)) da.Fill(dt);
        }
        return dt;
    }
}

關鍵效能優化技巧

常見坑點與解決

1. 伺服器端執行 Interop.Excel 失敗：需安裝 Office、設定 DCOM 權限 (dcomcnfg → Microsoft Excel Application → 啟動權限/存取權限加入 IIS 執行身分)、建立 Desktop 資料夾 (C:\Windows\System32\config\systemprofile\Desktop 及 C:\Windows\SysWOW64\config\systemprofile\Desktop)。
2. 巨集未執行/安全性阻擋：模板需儲存為 .xlsm，且信任中心 → 巨集設定 → 勾選「信任 VBA 專案物件模型存取」。
3. 中文路徑/檔名亂碼：確保專案編碼 UTF-8，C# 字串正確傳遞。
4. 記憶體洩漏：務必 `Marshal.ReleaseComObject` + `GC.Collect()`，否則 Excel 進程殘留。

替代方案：無 Office 環境 (如 Linux Docker、雲端)

• EPPlus / ClosedXML / NPOI：純 .NET 程式庫，支援範本讀取、寫入、公式計算 (EPPlus 5+ 需商業授權)。
• 同樣採用「模板 + 程式碼填資料」架構：先用 Excel 設計好 .xlsx 模板，程式用 EPPlus 開啟 → 填資料 → 另存。
• 缺點：無法執行 VBA 宏，複雜格式邏輯 (如動態插入列擴展公式) 需自行用程式碼實現。

總結：為何選「模板 + 宏」？

1. 分工明確：程式管資料、Excel 管呈現，業務調整格式無需找 RD。
2. 功能完整：公式、條件格式、圖表、樞紐、分頁列印、資料驗證… 全支援。
3. 效能最佳：陣列寫入 + 巨集批次處理，萬筆資料秒級產出。
4. 維護成本低：修改模板即時生效，無需重新編譯部署。