C# 合併多個相同結構 Excel：Microsoft.Office.Interop.Excel Range 複製法

適用場景

多個欄位相同、含複雜表頭的 Excel 檔案合併為一檔，自動略過重複表頭。

核心邏輯

1. 建立目標活頁簿與工作表。
2. 迴圈來源檔案：
3. 第一份檔案：複製表頭（A1 到指定末欄/表頭行數）與資料區。
4. 後續檔案：僅複製資料區（表頭行數+1 到已使用範圍末列）。
5. 追加寫入目標工作表下一可用列。
6. 儲存並關閉 Excel 進程。

完整程式碼

using System;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

public class ExcelMerger
{
    private readonly Excel.Application _app = new Excel.Application();
    private Excel.Workbook _destBook;
    private Excel.Worksheet _destSheet;
    private readonly string[] _srcFiles;
    private readonly string _destFile;
    private readonly string _endCol;       // 如 "M"
    private readonly int _headerRows;      // 表頭列數
    private int _nextRow = 1;              // 目標下一寫入列（1-based）

    public ExcelMerger(string[] srcFiles, string destFile, string endCol, int headerRows)
    {
        _srcFiles = srcFiles; _destFile = destFile; _endCol = endCol; _headerRows = headerRows;
        _destBook = (Excel.Workbook)_app.Workbooks.Add(Missing.Value);
        _destSheet = (Excel.Worksheet)_destBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        _destSheet.Name = "Merged";
    }

    public void Merge()
    {
        bool first = true;
        foreach (string file in _srcFiles)
        {
            Excel.Workbook srcBook = _app.Workbooks.Open(file, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Excel.Worksheet srcSheet = (Excel.Worksheet)srcBook.Worksheets[1];

            if (first)
            {
                CopyRange(srcSheet, $"A1:{_endCol}{_headerRows}", "A1");
                _nextRow += _headerRows;
                first = false;
            }

            int srcLastRow = srcSheet.UsedRange.Rows.Count;
            if (srcLastRow > _headerRows)
            {
                string srcRange = $"A{_headerRows + 1}:{_endCol}{srcLastRow}";
                CopyRange(srcSheet, srcRange, $"A{_nextRow}");
                _nextRow += srcLastRow - _headerRows;
            }

            srcBook.Close(false, Missing.Value, Missing.Value);
        }

        _destBook.SaveAs(_destFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        _destBook.Close(false, Missing.Value, Missing.Value);
        _app.Quit();
    }

    private void CopyRange(Excel.Worksheet src, string srcAddr, string destAddr)
    {
        Excel.Range srcRange = src.get_Range(srcAddr, Missing.Value);
        Excel.Range destRange = _destSheet.get_Range(destAddr, Missing.Value);
        srcRange.Copy(destRange);
    }
}

// 呼叫範例
class Program
{
    static void Main()
    {
        new ExcelMerger(
            new[] { @"E:\excel\TypeA\CoA.xls", @"E:\excel\TypeA\CoB.xls" },
            @"E:\excel\TypeA\Merged.xls", "M", 3).Merge();

        new ExcelMerger(
            new[] { @"E:\excel\TypeB\StatA.xls", @"E:\excel\TypeB\StatB.xls" },
            @"E:\excel\TypeB\Merged.xls", "I", 4).Merge();
    }
}

部署注意事項

• 需安裝 Microsoft Excel（或至少 Primary Interop Assemblies）。
• 伺服器端非互動式執行需設定 DCOM 權限（dcomcnfg → Microsoft Excel Application → 身分識別 → 互動使用者）。
• 大量檔案建議改用 OpenXML SDK (EPPlus/ClosedXML) 免裝 Office、效能更佳。
• 合併後檢查 _destSheet.UsedRange 確認資料完整。