C# Excel 報表開發 (八)：資料繫結模板法 - 以欄位名稱定位儲存格

痛點

傳統 Excel 報表寫法：C# 代碼**硬編碼**儲存格座標 (如 `Cells[3,5] = value`)，一旦模板調整版面，代碼就得跟著改，維護極度痛苦。

解決方案：模板標記法

在 Excel 模板中，將**資料填充目標儲存格**預先填入 $欄位名 (如 `$name`、`$age`)，程式碼掃描整張工作表，依**欄位名稱**比對 DataTable 欄位，自動填入資料。**C# 完全不需知道具體座標**。

模板設計範例

• 欄位名稱需與 DataTable.Columns 完全一致 (區分大小寫)。
• 前綴 `$` 避免與一般文字衝突。
• 可佈置多筆資料區塊 (明細表) 或單筆 (主檔)。

完整實作 (WinForms + Microsoft.Office.Interop.Excel)

using System;
using System.Data;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

public partial class MainForm : Form
{
    private void btnCreateXml_Click(object sender, EventArgs e)
    {
        // 建立測試資料並輸出 XML (供按鈕 2 讀取)
        var dt = new DataTable();
        dt.Columns.Add("name", typeof(string));
        dt.Columns.Add("age", typeof(int));
        dt.Columns.Add("memo", typeof(string));
        var dr = dt.NewRow();
        dr["name"] = "dahuzizyd";
        dr["age"] = 20;
        dr["memo"] = "dahuzizyd.cnblogs.com";
        dt.Rows.Add(dr);
        dt.AcceptChanges();

        var ds = new DataSet();
        ds.Tables.Add(dt);
        ds.WriteXml(Path.Combine(Application.StartupPath, "ExcelBindingXml.xml"));
        MessageBox.Show("測試 XML 已產生");
    }

    private void btnFillTemplate_Click(object sender, EventArgs e)
    {
        string xmlPath = Path.Combine(Application.StartupPath, "ExcelBindingXml.xml");
        string templatePath = Path.Combine(Application.StartupPath, "ExcelTemplate.xls");
        string outputPath = Path.Combine(Application.StartupPath, "ExcelBindingXml.xls");

        if (!File.Exists(xmlPath) || !File.Exists(templatePath))
        {
            MessageBox.Show("請先點擊按鈕 1 產生 XML，並確認模板檔案存在");
            return;
        }

        var ds = new DataSet();
        ds.ReadXml(xmlPath);
        DataTable dt = ds.Tables[0];

        Excel.Application xlApp = null;
        Excel.Workbook wb = null;
        Excel.Worksheet ws = null;
        object miss = Missing.Value;

        try
        {
            xlApp = new Excel.Application { DisplayAlerts = false, Visible = false };
            wb = xlApp.Workbooks.Open(templatePath, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            ws = (Excel.Worksheet)wb.Worksheets[1];

            // 掃描前 7 欄 x 4 列 (可依實際模板調整)
            foreach (DataRow row in dt.Rows)
            {
                for (int colIdx = 0; colIdx < dt.Columns.Count; colIdx++)
                {
                    string fieldName = dt.Columns[colIdx].ColumnName;
                    for (int excelCol = 1; excelCol <= 7; excelCol++)
                    {
                        for (int excelRow = 1; excelRow <= 4; excelRow++)
                        {
                            string addr = ColNumToLetter(excelCol) + excelRow;
                            Excel.Range rng = ws.get_Range(addr, miss);
                            if (rng.Text?.ToString().Replace("$", "") == fieldName)
                            {
                                rng.Value2 = row[colIdx];
                                goto NextField; // 找到即跳出雙層迴圈
                            }
                        }
                    }
                    NextField: ; // 標籤
                }
            }

            wb.SaveAs(outputPath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
            MessageBox.Show($"報表已產生：{outputPath}");
        }
        catch (Exception ex)
        {
            MessageBox.Show("錯誤：" + ex.Message);
        }
        finally
        {
            // 正確釋放 COM 物件 (逆序)
            if (wb != null) { wb.Close(false, miss, miss); Marshal.ReleaseComObject(wb); }
            if (xlApp != null) { xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }
            wb = null; xlApp = null; ws = null;
            GC.Collect(); GC.WaitForPendingFinalizers();
        }
    }

    /// 
數字欄號轉 Excel 字母欄名 (支援至 ZZ, 即 702 欄)
    private string ColNumToLetter(int colNum)
    {
        string result = "";
        while (colNum > 0)
        {
            int rem = (colNum - 1) % 26;
            result = (char)('A' + rem) + result;
            colNum = (colNum - 1) / 26;
        }
        return result;
    }
}

關鍵技術點

進階擴充建議

1. 支援明細表重複列：偵測 `$` 標記所在列，對每筆資料 Insert 新列並複製格式。
2. 改用 OpenXML SDK / EPPlus / NPOI：免安裝 Office、效能更好、支援 .xlsx、適合伺服器端批次產報表。
3. 模板驗證：產報表前掃描一次，列出「模板有但 DataTable 無」與「DataTable 有但模板無」的欄位警告。
4. 條件格式/圖表：模板預設條件格式、樞紐分析、圖表，資料填入後自動更新。

輸出結果