.NET Core中替代Excel Interop CopyFromRecordset批量注入数据方案

由于.NET Core不支持ADODB，且逐单元格写入效率无法满足十万级数据需求，推荐使用二维数组批量赋值的方式替代CopyFromRecordset，这是Excel Interop中效率最高的批量写入方案之一，且能保证Excel自动刷新图表和透视表的需求。

核心思路 #

Excel Interop允许直接将二维object数组赋值给Range对象，这是单次COM调用操作，底层会一次性传输所有数据，效率与CopyFromRecordset接近，完全适配十万级数据量的场景。

实现步骤与代码示例 #

1. 将自定义数据集转换为二维数组 #

先把List<MyData>转换成与Excel列对应的二维数组，确保数据类型与Excel单元格兼容：

internal static object[,] ToExcelBatchArray(this List<MyData> dataList)
{
    // 数组维度：行数=数据条数，列数=字段数量（此处为3列）
    var batchArray = new object[dataList.Count, 3];

    for (int i = 0; i < dataList.Count; i++)
    {
        var item = dataList[i];
        batchArray[i, 0] = item.Date;    // 对应Excel的Date列
        batchArray[i, 1] = item.Hour;    // 对应Excel的Hour列
        batchArray[i, 2] = item.Code;    // 对应Excel的Code列
    }

    return batchArray;
}

2. 批量写入Excel表格 #

定位到模板中的ListObject（表格），计算目标Range范围后直接赋值数组，再刷新相关元素：

// 假设已获取到目标sheet和workbook对象
var dataTable = sheet.ListObjects["CodesTable"];
var data = GetProcessedData(); // 你的数据处理逻辑，返回List<MyData>

// 计算数据写入的起始位置：表头行的下一行，表格的起始列
int startRow = dataTable.HeaderRowRange.Row + 1;
int startCol = dataTable.Range.Column;

// 计算目标Range的结束位置：起始行+数据行数-1，起始列+列数-1
var targetRange = sheet.Range[
    sheet.Cells[startRow, startCol],
    sheet.Cells[startRow + data.Count - 1, startCol + 2]
];

// 批量写入数据（用Value2比Value性能更优，避免额外格式转换）
targetRange.Value2 = data.ToExcelBatchArray();

// 刷新表格（确保表格范围自动扩展）
dataTable.Refresh();

// 刷新所有透视表
foreach (Excel.PivotTable pivotTable in sheet.PivotTables)
{
    pivotTable.RefreshTable();
}

// 刷新所有图表
foreach (Excel.ChartObject chartObj in sheet.ChartObjects())
{
    chartObj.Chart.Refresh();
}

// 保存文件
workbook.Save();

关键注意事项 #

• 性能优化：使用Value2而非Value，前者直接操作原始数据，避免Excel自动格式转换带来的开销。
• 维度匹配：确保二维数组的行数、列数与目标Range完全一致，否则会导致数据错位或写入失败。
• COM对象释放：操作完成后需正确释放Excel相关COM对象，避免内存泄漏（可通过Marshal.ReleaseComObject或使用try-finally块清理）。
• 兼容性：该方案依赖Excel COM组件，需确保运行环境安装了对应版本的Excel，且.NET Core项目启用了COM互操作支持（Windows环境下默认支持）。

内容的提问来源于stack exchange，提问作者mmix