C# 使用 Range.Value2 批量寫入大幅提升 DataTable 匯出 Excel 效能

效能痛點

傳統逐儲存格寫入（Cells[row, col] = value）涉及大量 COM 呼叫，匯出萬筆資料可能耗時數十秒。透過 Range.Value2 一次寫入二維陣列，速度可提升 欄位數倍數。

核心輔助函數：欄位索引轉 Excel 欄位字母

private string NumToCol(int num) {
    if (num <= 0) return "";
    if (num <= 26) return ((char)('A' + num - 1)).ToString();
    return NumToCol((num - 1) / 26) + NumToCol((num - 1) % 26 + 1);
}

private string GetExcelRangeAddress(int colCount) {
    return "A1:" + NumToCol(colCount) + "1"; // 如 A1:Z1
}

完整匯出方法

public void DataTableToExcelFast(DataTable dt, string filePath) {
    if (dt == null || dt.Rows.Count == 0) return;

    int rowCount = dt.Rows.Count;
    int colCount = dt.Columns.Count;

    // 1. 準備標題列陣列
    object[] headers = new object[colCount];
    for (int i = 0; i < colCount; i++) headers[i] = dt.Columns[i].ColumnName;

    // 2. 準備資料陣列 (二維)
    object[,] data = new object[rowCount, colCount];
    for (int r = 0; r < rowCount; r++) {
        for (int c = 0; c < colCount; c++) {
            data[r, c] = dt.Rows[r][c].ToString();
        }
    }

    // 3. 啟動 Excel 並寫入
    Excel.Application xlApp = new Excel.ApplicationClass();
    xlApp.DisplayAlerts = false;
    xlApp.ScreenUpdating = false;

    Excel.Workbook wb = xlApp.Workbooks.Add(true);
    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

    // 寫入標題
    Excel.Range headerRange = ws.get_Range("A1", NumToCol(colCount) + "1");
    headerRange.Value2 = headers;

    // 寫入資料
    Excel.Range dataRange = ws.get_Range("A2", NumToCol(colCount) + (rowCount + 1));
    dataRange.Value2 = data;

    // 自動調整欄寬
    dataRange.EntireColumn.AutoFit();

    // 儲存並關閉
    wb.SaveCopyAs(filePath);
    wb.Close(false);
    xlApp.Quit();

    // 釋放 COM 物件 (略，建議使用 Marshal.ReleaseComObject)
}

效能實測

關鍵優化點

• 一次 COM 呼叫： Range.Value2 = object[,] 只跨越一次 Managed/Unmanaged 邊界。
• 關閉 UI 更新： ScreenUpdating = false、DisplayAlerts = false。
• 釋放資源： 循環釋放所有 COM 參考，避免 Excel 殭屍進程。