C# 高效能 txt 轉 Excel 完整實戰：Range 批量寫入、記憶體優化與百萬筆資料處理

大量文字資料匯入 Excel 是常見需求，逐儲存格寫入效能極差。本文深度解析 `Range.Value2` 批量寫入技術、二維陣列資料結構、記憶體管理與百萬筆資料處理的完整方案。

效能對比：逐筆 vs 批量

核心技術：Range.Value2 二維陣列批量寫入

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

public class FastExcelImporter : IDisposable {
    private Excel.Application _app;
    private Excel.Workbook _wb;
    private Excel.Worksheet _ws;
    private bool _disposed;
    
    public FastExcelImporter(bool visible = false) {
        _app = new Excel.Application {
            Visible = visible,
            ScreenUpdating = false,      // 關鍵：關閉螢幕更新
            DisplayAlerts = false,       // 關閉警示
            EnableEvents = false,        // 關閉事件
            Calculation = Excel.XlCalculation.xlCalculationManual // 手動計算
        };
        _wb = _app.Workbooks.Add();
        _ws = (Excel.Worksheet)_wb.ActiveSheet;
    }
    
    /// 

    /// 高效能匯入：文字檔 -> 二維陣列 -> Range.Value2
    /// 
    public void ImportTextFile(string filePath, string delimiter = ",", 
        int maxRows = 1000000, int maxCols = 50) {
        
        // 1. 讀取所有行到記憶體 (大檔案建議分批)
        string[] lines = File.ReadAllLines(filePath, Encoding.UTF8);
        int rowCount = Math.Min(lines.Length, maxRows);
        
        // 2. 預先配置二維陣列 (1-based 索引)
        object[,] data = new object[rowCount, maxCols];
        
        // 3. 解析填充陣列
        for (int i = 0; i < rowCount; i++) {
            string[] fields = lines[i].Split(new[] { delimiter }, 
                StringSplitOptions.None);
            int colCount = Math.Min(fields.Length, maxCols);
            for (int j = 0; j < colCount; j++) {
                data[i, j] = fields[j];
            }
        }
        
        // 4. 一次性寫入 Range
        Excel.Range range = _ws.Range[_ws.Cells[1, 1], _ws.Cells[rowCount, maxCols]];
        range.Value2 = data;  // 關鍵：單次 COM 呼叫
        
        // 5. 自動調整欄寬 (可選，大資料量建議關閉)
        // range.EntireColumn.AutoFit();
        
        // 6. 恢復 Excel 設定
        _app.ScreenUpdating = true;
        _app.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
    }
    
    /// 

    /// 分批處理超大檔案 (避免記憶體壓力)
    /// 
    public void ImportLargeFileBatched(string filePath, int batchSize = 50000) {
        using (var reader = new StreamReader(filePath, Encoding.UTF8)) {
            int batch = 0;
            string line;
            var buffer = new List();
            
            while ((line = reader.ReadLine()) != null) {
                buffer.Add(line.Split(','));
                
                if (buffer.Count >= batchSize) {
                    WriteBatch(buffer, batch * batchSize + 1);
                    buffer.Clear();
                    batch++;
                }
            }
            
            // 寫入剩餘
            if (buffer.Count > 0) {
                WriteBatch(buffer, batch * batchSize + 1);
            }
        }
    }
    
    private void WriteBatch(List batch, int startRow) {
        int rows = batch.Count;
        int cols = batch.Max(r => r.Length);
        object[,] data = new object[rows, cols];
        
        for (int i = 0; i < rows; i++) {
            for (int j = 0; j < batch[i].Length; j++) {
                data[i, j] = batch[i][j];
            }
        }
        
        Excel.Range range = _ws.Range[_ws.Cells[startRow, 1], _ws.Cells[startRow + rows - 1, cols]];
        range.Value2 = data;
    }
    
    public void SaveAs(string path) {
        _wb.SaveAs(path);
    }
    
    public void Dispose() {
        if (!_disposed) {
            try {
                _wb?.Close(false);
                _app?.Quit();
            } catch { }
            finally {
                ReleaseComObjects();
            }
            _disposed = true;
        }
    }
    
    private void ReleaseComObjects() {
        if (_ws != null) Marshal.ReleaseComObject(_ws);
        if (_wb != null) Marshal.ReleaseComObject(_wb);
        if (_app != null) Marshal.ReleaseComObject(_app);
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

進階優化技巧

1. 資料類型優化：避免物件裝箱

// 根據資料類型選擇陣列類型
// 純數值：double[,] -> 轉 object[,] 時會裝箱
// 混合類型：object[,] (必要)
// 大量字串：string[,] -> 同樣需裝箱

// 進階：使用 Array.CreateInstance 優化
Array data = Array.CreateInstance(typeof(object), rows, cols);
// 但 Range.Value2 仍需 object[,]，效果有限

2. 記憶體管理：大檔案分塊處理

public void ImportHugeFile(string filePath, int chunkRows = 100000) {
    using (var reader = new StreamReader(filePath)) {
        string header = reader.ReadLine(); // 假設有標題
        var headers = header.Split(',');
        int colCount = headers.Length;
        
        // 寫入標題
        object[,] headerData = new object[1, colCount];
        for (int j = 0; j < colCount; j++) headerData[0, j] = headers[j];
        _ws.Range[_ws.Cells[1, 1], _ws.Cells[1, colCount]].Value2 = headerData;
        
        int currentRow = 2;
        var buffer = new List();
        
        string line;
        while ((line = reader.ReadLine()) != null) {
            buffer.Add(line.Split(','));
            
            if (buffer.Count >= chunkRows) {
                FlushBuffer(buffer, currentRow, colCount);
                currentRow += buffer.Count;
                buffer.Clear();
                
                // 定期釋放記憶體
                GC.Collect(0, GCCollectionMode.Optimized);
            }
        }
        
        if (buffer.Count > 0) {
            FlushBuffer(buffer, currentRow, colCount);
        }
    }
}

private void FlushBuffer(List buffer, int startRow, int colCount) {
    object[,] data = new object[buffer.Count, colCount];
    for (int i = 0; i < buffer.Count; i++) {
        var fields = buffer[i];
        for (int j = 0; j < Math.Min(fields.Length, colCount); j++) {
            data[i, j] = fields[j];
        }
    }
    _ws.Range[_ws.Cells[startRow, 1], _ws.Cells[startRow + buffer.Count - 1, colCount]].Value2 = data;
}

3. 進度回報與取消支援

public async Task ImportWithProgressAsync(string filePath, 
    IProgress progress, CancellationToken token) {
    
    await Task.Run(() => {
        long totalLines = File.ReadLines(filePath).Count();
        long processed = 0;
        
        using (var reader = new StreamReader(filePath)) {
            var buffer = new List();
            const int batchSize = 50000;
            int currentRow = 1;
            
            string line;
            while ((line = reader.ReadLine()) != null) {
                token.ThrowIfCancellationRequested();
                
                buffer.Add(line.Split(','));
                
                if (buffer.Count >= batchSize) {
                    WriteBatch(buffer, currentRow);
                    currentRow += buffer.Count;
                    processed += buffer.Count;
                    buffer.Clear();
                    
                    progress.Report(new ImportProgress {
                        ProcessedRows = processed,
                        TotalRows = totalLines,
                        Percent = (int)(processed * 100 / totalLines)
                    });
                }
            }
            
            if (buffer.Count > 0) {
                WriteBatch(buffer, currentRow);
            }
        }
    }, token);
}

public class ImportProgress {
    public long ProcessedRows { get; set; }
    public long TotalRows { get; set; }
    public int Percent { get; set; }
}

OpenXML 替代方案：無 Office 環境首選

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public class OpenXmlExcelImporter {
    public void ImportCsvToXlsx(string csvPath, string xlsxPath, char delimiter = ',') {
        using (var doc = SpreadsheetDocument.Create(xlsxPath, 
            SpreadsheetDocumentType.Workbook)) {
            
            var wbPart = doc.AddWorkbookPart();
            wbPart.Workbook = new Workbook();
            
            var wsPart = wbPart.AddNewPart();
            wsPart.Worksheet = new Worksheet(new SheetData());
            
            var sheets = wbPart.Workbook.AppendChild(new Sheets());
            sheets.Append(new Sheet {
                Id = wbPart.GetIdOfPart(wsPart),
                SheetId = 1,
                Name = "Sheet1"
            });
            
            var sheetData = wsPart.Worksheet.GetFirstChild();
            uint rowIndex = 1;
            
            using (var reader = new StreamReader(csvPath)) {
                string line;
                while ((line = reader.ReadLine()) != null) {
                    var row = new Row { RowIndex = rowIndex++ };
                    var fields = line.Split(delimiter);
                    
                    foreach (var field in fields) {
                        row.Append(new Cell {
                            CellValue = new CellValue(field),
                            DataType = CellValues.String
                        });
                    }
                    sheetData.Append(row);
                }
            }
            
            wsPart.Worksheet.Save();
        }
    }
}

方案選擇決策樹

if (環境是伺服器/容器/Linux 或 需高併發/大量生成) {
    使用 OpenXML SDK (或 ClosedXML 封裝庫)
} else if (需複雜格式/圖表/樞紐分析/PDF匯出 或 客戶端桌面應用) {
    使用 Office Interop + Range.Value2 批量寫入
} else if (簡單匯入/匯出、無格式需求) {
    使用 CSV 直接輸出 (Excel 可直接開啟)
}

常見陷阱排查表

掌握 `Range.Value2` 批量寫入核心技術，配合分批處理、記憶體管理與非同步進度回報，能輕鬆應對百萬筆資料的高效能 Excel 匯入需求。