C# Office 自動化實戰：Word 範本填充與 Excel 匯入匯出 (OLEDB/Interop/OpenXML)

前言

C# 操作 Office 有三大主流技術棧：COM Interop (需安裝 Office、適合複雜排版/列印)、OLEDB/ACE (僅資料存取、無需 Office)、Open XML SDK (純 .NET、高效能、跨平台、適合伺服器端大量產出)。本文三者並陳，依場景選型。

一、Word 範本填充 (書籤/內容控制項/郵件合併)

1.1 COM Interop 方案 (WinForms/WPF 桌面、需列印)

using Word = Microsoft.Office.Interop.Word;
using System.Runtime.InteropServices;

public class WordTemplateFiller : IDisposable
{
    private readonly Word.Application _app;
    private Word.Document _doc;

    public WordTemplateFiller(string templatePath, bool visible = false)
    {
        _app = new Word.Application { Visible = visible, DisplayAlerts = Word.WdAlertLevel.wdAlertsNone };
        object file = templatePath;
        _doc = _app.Documents.Open(ref file, ReadOnly: false, Visible: visible);
    }

    /// 
依書籤名稱填入文字
    public void FillBookmark(string bookmarkName, string text)
    {
        if (_doc.Bookmarks.Exists(bookmarkName))
        {
            var range = _doc.Bookmarks[bookmarkName].Range;
            range.Text = text;
            // 重建書籤以便再次填寫
            _doc.Bookmarks.Add(bookmarkName, range);
        }
    }

    /// 
依內容控制項 Tag 填入文字
    public void FillContentControl(string tag, string text)
    {
        foreach (Word.ContentControl cc in _doc.ContentControls)
            if (cc.Tag == tag) { cc.Range.Text = text; break; }
    }

    /// 
表格填充：指定書籤在表格儲存格內，向右/向下移動填入
    public void FillTableFromBookmark(string bookmarkName, string[,] data)
    {
        if (!_doc.Bookmarks.Exists(bookmarkName)) return;
        var start = _doc.Bookmarks[bookmarkName].Range;
        var table = start.Tables[1]; // 假設書籤在表格第一儲存格
        for (int r = 0; r < data.GetLength(0); r++)
        {
            for (int c = 0; c < data.GetLength(1); c++)
            {
                var cell = table.Cell(r + 1, c + 1);
                cell.Range.Text = data[r, c];
            }
        }
    }

    public void SaveAs(string path, Word.WdSaveFormat format = Word.WdSaveFormat.wdFormatDocumentDefault)
    {
        object p = path; object f = format;
        _doc.SaveAs2(ref p, ref f);
    }

    public void Print() => _doc.PrintOut();

    public void Dispose()
    {
        _doc?.Close(SaveChanges: false);
        _app?.Quit();
        if (_doc != null) Marshal.ReleaseComObject(_doc);
        if (_app != null) Marshal.ReleaseComObject(_app);
        GC.Collect(); GC.WaitForPendingFinalizers(); // 強制釋放 WINWORD.EXE
    }
}

// 使用
using (var filler = new WordTemplateFiller(@"C:\Templates\Report.dotx"))
{
    filler.FillBookmark("CustomerName", "台積電");
    filler.FillBookmark("Date", DateTime.Today.ToString("yyyy年MM月dd日"));
    filler.FillContentControl("TotalAmount", "1,234,567");
    string[,] items = new string[3,4] { {"1","項目A","100","1000"},{"2","項目B","200","2000"},{"3","項目C","300","3000"} };
    filler.FillTableFromBookmark("DetailTable", items);
    filler.SaveAs(@"C:\Output\Report_20240615.docx");
    filler.Print();
}

Interop 避坑指南：
1. 一定要 Marshal.ReleaseComObject + GC.Collect，否則殘留 WINWORD.EXE。
2. 伺服器端 (IIS/Windows Service) **嚴禁** 使用 Interop (授權、穩定性、執行緒模型皆不支援)。
3. 參考組件嵌入 Interop Types (Embed Interop Types = True) 避免版本綁定。

1.2 Open XML SDK 方案 (伺服器端、高效能、無 Office)

// NuGet: DocumentFormat.OpenXml
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using System.Linq;

public class OpenXmlWordFiller
{
    public void FillAndSave(string templatePath, string outputPath, Dictionary bookmarks)
    {
        File.Copy(templatePath, outputPath, true);
        using (var doc = WordprocessingDocument.Open(outputPath, true))
        {
            var body = doc.MainDocumentPart.Document.Body;
            
            // 1. 書籤文字替換
            foreach (var bm in body.Descendants())
            {
                if (bookmarks.TryGetValue(bm.Name, out var text))
                {
                    var run = bm.NextSibling();
                    if (run != null) run.GetFirstChild().Text = text;
                }
            }

            // 2. 內容控制項 (SdtBlock) 替換
            foreach (var sdt in body.Descendants())
            {
                var tag = sdt.SdtProperties?.GetFirstChild()?.Val?.Value;
                if (tag != null && bookmarks.TryGetValue(tag, out var text))
                {
                    var run = sdt.Descendants().FirstOrDefault();
                    if (run != null) run.GetFirstChild().Text = text;
                }
            }

            doc.MainDocumentPart.Document.Save();
        }
    }
}

二、Excel 匯入匯出

2.1 OLEDB/ACE (純資料、無 Office、高效能)

// NuGet: System.Data.OleDb (Core) / 系統內建 (Framework)
// 需安裝 Microsoft Access Database Engine (ACE) 64-bit
public class ExcelOleDbHelper
{
    private static string GetConnString(string path, bool hasHeader = true)
        => $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Extended Properties='Excel 12.0;HDR={(hasHeader?"YES":"NO")};IMEX=1'";

    public DataTable ReadSheet(string path, string sheetName = "Sheet1")
    {
        using (var conn = new System.Data.OleDb.OleDbConnection(GetConnString(path)))
        {
            conn.Open();
            var cmd = new System.Data.OleDb.OleDbCommand($"SELECT * FROM [{sheetName}$]", conn);
            var adapter = new System.Data.OleDb.OleDbDataAdapter(cmd);
            var dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
    }

    public void WriteSheet(string path, DataTable data, string sheetName = "Sheet1")
    {
        using (var conn = new System.Data.OleDb.OleDbConnection(GetConnString(path)))
        {
            conn.Open();
            // 建立工作表
            var createCmd = new System.Data.OleDb.OleDbCommand(
                $"CREATE TABLE [{sheetName}$] ({string.Join(",", data.Columns.Cast().Select(c => $"[{c.ColumnName}] {GetOleDbType(c.DataType)}"))})", conn);
            createCmd.ExecuteNonQuery();

            // 批次插入
            using (var tx = conn.BeginTransaction())
            {
                var insertSql = $"INSERT INTO [{sheetName}$] ({string.Join(",", data.Columns.Cast().Select(c => $"[{c.ColumnName}]"))}) VALUES ({string.Join(",", data.Columns.Cast().Select((c,i) => "@p"+i))})";
                foreach (DataRow row in data.Rows)
                {
                    var cmd = new System.Data.OleDb.OleDbCommand(insertSql, conn, tx);
                    for (int i = 0; i < data.Columns.Count; i++)
                        cmd.Parameters.AddWithValue("@p"+i, row[i]);
                    cmd.ExecuteNonQuery();
                }
                tx.Commit();
            }
        }
    }

    private string GetOleDbType(Type t)
    {
        if (t == typeof(string)) return "TEXT(255)";
        if (t == typeof(int) || t == typeof(long)) return "INTEGER";
        if (t == typeof(double) || t == typeof(decimal)) return "DOUBLE";
        if (t == typeof(DateTime)) return "DATETIME";
        return "TEXT(255)";
    }
}

2.2 Open XML SDK (樣式、公式、大數據、跨平台)

// NuGet: DocumentFormat.OpenXml
// 適合：需儲存格樣式、公式、條件格式、百萬列效能
// 程式碼較長，核心概念：
// 1. SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook)
// 2. WorkbookPart → WorksheetPart → SheetData → Row → Cell
// 3. Cell.DataType = CellValues.String/Number/Date/SharedString/Formula
// 4. SharedStringTablePart 存放重複字串
// 5. Stylesheet 定義字型、填滿、邊框、數字格式
// 推薦使用 ClosedXML (Open XML 封裝) 大幅簡化 API

2.3 ClosedXML (強烈推薦：Open XML 的人性化封裝)

// NuGet: ClosedXML
using ClosedXML.Excel;

public class ClosedXmlHelper
{
    public void Export(DataTable dt, string path, string sheetName = "Data")
    {
        using (var wb = new XLWorkbook())
        {
            var ws = wb.Worksheets.Add(sheetName);
            ws.Cell(1, 1).InsertTable(dt.AsEnumerable()); // 自動建立標題、資料
            ws.Columns().AdjustToContents();
            wb.SaveAs(path);
        }
    }

    public DataTable Import(string path, string sheetName = "Sheet1")
    {
        using (var wb = new XLWorkbook(path))
        {
            var ws = wb.Worksheet(sheetName);
            var table = ws.RangeUsed().AsTable();
            var dt = new DataTable();
            foreach (var col in table.Fields) dt.Columns.Add(col.Name);
            foreach (var row in table.Rows) dt.Rows.Add(row.Fields.Select(f => f.Value).ToArray());
            return dt;
        }
    }

    // 進階樣式
    public void StyledExport(string path)
    {
        using (var wb = new XLWorkbook())
        {
            var ws = wb.Worksheets.Add("Styled");
            var header = ws.Row(1);
            header.Style.Font.Bold = true;
            header.Style.Fill.BackgroundColor = XLColor.DarkBlue;
            header.Style.Font.FontColor = XLColor.White;
            ws.Cell(1, 1).Value = "ID";
            ws.Cell(1, 2).Value = "Name";
            ws.Cell(2, 1).Value = 1;
            ws.Cell(2, 2).Value = "Sample";
            ws.Columns().AdjustToContents();
            wb.SaveAs(path);
        }
    }
}

三、技術選型決策表

四、常見坑點彙整

• Interop 殘留進程：一定要 `Marshal.ReleaseComObject` 所有 COM 物件 + `GC.Collect()`。
• OLEDB 64 位元：必須安裝 64-bit ACE Driver，否則 "Provider not registered"。
• Excel 數字儲存為文字：OLEDB `IMEX=1` 強制混合模式；Open XML 設定 `Cell.DataType = CellValues.String`。
• 大檔案記憶體：Open XML `OpenXmlReader` / `Sax` 流式讀寫，避免載入完整 DOM。
• 授權：伺服器端自動化 Office 違反 Microsoft EULA，務必用 Open XML/ClosedXML。

總結

現代 C# 專案：伺服器端一律 ClosedXML/Open XML，桌面端需列印/複雜 Word 才用 Interop，純資料匯入匯出可考慮 OLEDB。建立統一介面 `IExcelExporter`/`IWordGenerator`，實作細節交給 DI 容器切換，即可兼顧開發效能、部署彈性與授權合規。