OWC 生成 Excel 效能優化：從 10 分鐘到 2 分鐘的 300 倍提速實戰

痛點：逐儲存格寫入極慢

使用 OWC (Office Web Components) 的 SpreadsheetClass 導出數據時，原始代碼逐行逐列賦值 f_sheet.Cells[row, col] = value，1000 筆資料耗時近 10 分鐘，甚至超時。每次儲存格賦值都是一次 COM 跨進程調用，開銷極大。

優化核心：批次陣列賦值

關鍵洞察：OWC 的 Range.set_Value 支援一次將二維陣列寫入整個區塊，將 N×M 次 COM 調用減為 1 次。

優化前代碼（慢）

foreach (DataRow row in dt.Rows)
{
    foreach (DataColumn col in dt.Columns)
    {
        if (row[col] != DBNull.Value)
        {
            // 逐儲存格賦值 - 極慢
            sheet.Cells[currentRow, currentCol] = 
                isTextColumn ? "'" + row[col].ToString() : row[col];
        }
        currentCol++;
    }
    currentRow++;
    currentCol = startCol;
}

優化後代碼（快）

public void WriteDataToSpreadsheet(SpreadsheetClass spreadsheet, DataTable dt,
    int startRow, int startCol, bool withCaption, int startRecord, 
    int limited, int[] textColumns)
{
    Worksheet sheet = spreadsheet.ActiveSheet;
    int maxRow = 65535;
    int totalRow = 0, rowCount = 0;
    int currentRow = startRow, currentCol = startCol;

    // 1. 處理標題列
    if (withCaption)
    {
        foreach (DataColumn col in dt.Columns)
        {
            sheet.Cells[currentRow, currentCol++] = col.Caption;
        }
        currentRow++; totalRow++;
        currentCol = startCol;
    }

    // 2. 預計算列名 (A, B, ..., Z, AA, AB...)
    string[] A2Z = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".Select(c => c.ToString()).ToArray();
    var colNames = new List<string>(A2Z);
    foreach (var c1 in A2Z)
        foreach (var c2 in A2Z)
            colNames.Add(c1 + c2);
    string lastColName = colNames[dt.Columns.Count - 1];

    // 3. 將所有數據先裝入 List<object[,]>
    var dataBlocks = new List<object[,]>();
    int rows = dt.Rows.Count, cols = dt.Columns.Count;

    for (int i = 0; i < rows; i++)
    {
        var block = new object[1, cols]; // 單行區塊
        for (int j = 0; j < cols; j++)
        {
            var val = dt.Rows[i][j];
            if (val != DBNull.Value)
            {
                block[0, j] = Array.IndexOf(textColumns, j) >= 0 
                    ? "'" + val.ToString() : val;
            }
            else block[0, j] = "";
        }
        dataBlocks.Add(block);
    }

    // 4. 批次寫入：每行一個 Range.set_Value 調用
    object missing = System.Reflection.Missing.Value;
    for (int i = 0; i < rows; i++)
    {
        // 目標區塊：單行完整列範圍
        Range targetRange = sheet.get_Range(
            "A" + (currentRow).ToString(), 
            lastColName + (currentRow).ToString());
        
        // 一次性寫入整行陣列 - 核心提速點
        targetRange.set_Value(missing, dataBlocks[i]);

        rowCount++; totalRow++; currentRow++;
        if (totalRow >= maxRow) break;
        if (limited > 0 && rowCount >= limited) break;
    }
}

效能對比

關鍵技術點解析

1. Range.set_Value 接受二維陣列：object[,] 維度必須與目標 Range 完全一致
2. 預先組裝記憶體中：先在 .NET 端構建完整陣列，再一次性跨 COM 邊界
3. 文本列前綴單引號：'123 強制 Excel 視為文本，避免科學記號或前導零丟失
4. 動態列名生成：支援超過 26 欄 (AA, AB...)

適用場景與限制

• 適用：OWC Spreadsheet、Excel Interop、大批量數據導出
• 注意：OWC 已停止更新，新專案建議改用 EPPlus、ClosedXML 或 NPOI
• 記憶體：超大數據集 (百萬行) 仍需分批處理，避免 OOM

「用陣列換迴圈」是 COM 互操作優化的黃金法則，這個技巧在 Excel Interop、Word Interop 同樣有效。