vsto excel 工作簿项目：如何快速地将巨大的数据表写入 Excel 工作表

Question

我有一个复杂的对象（树结构），我将其展平到数据表中以将其显示在 Excel 工作表上。数据表很大，大约有 20000 行和 10000 列。

将数据一次写入 Excel 单元格需要很长时间。因此，我将复杂对象转换为数据表，然后使用下面的代码将其写入 Excel 工作表。

是否可以在一分钟之内相当快地将 20K 行 x 10K 列数据写入 Excel 工作表或 << 5分钟？快速完成这项任务的最佳技术是什么？

环境：Visual studio 2010、VSTO excel 工作簿项目、.net Framework 4.0、excel 2010/2007

编辑：

原始数据源是 json 格式的休息服务响应。然后，我将 json 响应反序列化为 C# 对象，最后将其展平为数据表。

使用此代码将数据表写入 Excel 工作表：

Excel.Range oRange;
                var oSheet = Globals.Sheet3;
                int rowCount = 1;
                foreach (DataRow dr in resultsDataTable.Rows)
                {
                    rowCount += 1;
                    for (int i = 1; i < resultsDataTable.Columns.Count + 1; i++)
                    {
                        // Add the header the first time through 
                        if (rowCount == 2)
                        {
                            oSheet.Cells[1, i] = resultsDataTable.Columns[i - 1].ColumnName;
                        }
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }

                // Resize the columns 
                oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                                oSheet.Cells[rowCount, resultsDataTable.Columns.Count]);
                oRange.EntireColumn.AutoFit();

最终解决方案： 使用 2D 对象数组而不是数据表并将其写入范围。

Answer 1

除了冻结 Excel 的动画之外，在给定数据源的情况下，您还可以通过 Excel.Range 对象（这必然会成为瓶颈）进行循环，而不是写入Datatable，写入string[,]，Excel 可使用该字符串立即写入Range。循环遍历 string[,] 比循环遍历 Excel 单元格要快得多。

string[,] importString = new string[yourJsonSource.Rows.Count, yourJsonSource.Columns.Count];
//populate the string[,] however you can
for (int r = 0; r < yourJsonSource.Rows.Count; r++)
{
    for (int c = 0; c < yourJsonSource.Columns.Count; c++)
    {
        importString[r, c] = yourJsonSource[r][c].ToString();
    }
}

var oSheet = Globals.Sheet3;
Excel.Range oRange = oSheet.get_Range(oSheet.Cells[1, 1],
            oSheet.Cells[yourJsonSource.Rows.Count, yourJsonSource.Columns.Count]);
oRange.Value = importString;

Answer 2

我不能谈论使用数据表来完成这项工作，但如果您想使用 Interop，您肯定希望避免逐个单元地编写。相反，创建一个二维数组，并立即将其写入一个范围，这会给你带来非常显着的性能提升。

您应该考虑的另一个选择是完全避免互操作，并使用 OpenXML 。如果您使用的是 Excel 2007 或更高版本，这通常是处理文件的更好方法。

Answer 3

VSTO 总是需要时间，我可以与您分享的最佳技巧是在填充数据时禁用工作表刷新，一种方法是弹出一个“模态”进度对话框并在后台刷新工作表，这将给出您的性能提高 50-70%。您可以做的另一件事是将 VS 更新到 sp1，这会有所帮助。