OWC 生成 Excel 效能優化 (二) 大量資料寫入實戰

優化成果

優化後從 DataTable 匯入 60,000 筆資料，耗時不到兩分鐘。

核心優化代碼

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

    if (withCaption)
    {
        foreach (DataColumn dc in dt.Columns) { sheet.Cells[row, col] = dc.Caption; col++; }
        row++; totalRow++; col = startCol;
    }

    // 建立欄位名稱陣列 (A..Z, AA..ZZ)
    string[] A2Z = { "A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z" };
    var A2ZZ = new List(A2Z);
    foreach (var c1 in A2Z) foreach (var c2 in A2Z) A2ZZ.Add(c1 + c2);
    string lastColName = A2ZZ[dt.Columns.Count - 1];

    var dataList = new List();
    Range range = sheet.get_Range("A" + row, lastColName + row);
    int dtRows = dt.Rows.Count, dtCols = dt.Columns.Count;

    if (dtRows > 0)
    {
        // 將每列資料轉為二維陣列加入清單
        for (int i = 0; i < dtRows; i++)
        {
            object[,] objData = new object[1, dtCols];
            for (int j = 0; j < dtCols; j++)
            {
                if (dt.Rows[i][j] != DBNull.Value)
                {
                    if (Array.IndexOf(textColumns, j) > -1)
                        objData[0, j] = "'" + dt.Rows[i][j].ToString();
                    else
                        objData[0, j] = dt.Rows[i][j];
                }
                else objData[0, j] = "";
            }
            dataList.Add(objData);
        }
    }

    object missing = Type.Missing;
    for (int i = 0; i < dtRows; i++)
    {
        // 關鍵優化：直接對 Range 區間賦值二維陣列
        Range r2 = range.get_Range("A" + (rowCount + 1), lastColName + (rowCount + 1));
        r2.set_Value(missing, dataList[i]);

        rowCount++; totalRow++;
        if (totalRow >= maxRow) break;
        if (limited > 0 && rowCount >= limited) break;
        row++; col = startCol;
    }
}

關鍵優化點

• 預先將所有資料轉為 object[,] 二維陣列清單，避免逐儲存格寫入。
• 使用 Range.set_Value 一次將整列陣列寫入工作表區塊，大幅減少 COM 呼叫開銷。
• 支援指定欄位以文字格式寫入（前置單引號）。
• 程式碼為專案實戰提煉，仍有優化空間，歡迎交流更好做法。