Microsoft.Office.Interop.Excel is too slow

Question

我正在使用 Excel 互操作，我有一个对象列表。当我循环在Excel单元格中设置列表中对象的属性时，花费的时间太长：

workSheet = (Excel.Worksheet)wbook.ActiveSheet;
var row = 1;
foreach (var shiftStop in shiftStops)
{
row++;
workSheet.Cells[row, "A"] = shiftStop.MachineName;
workSheet.Cells[row, "B"] = shiftStop.MachineType;
workSheet.Cells[row, "C"] = shiftStop.UnitName;
}

shiftStops列表有大约 4000 个对象。完成退货需要4-5分钟。有没有办法使这个过程更快？

Answer 1

执行代码时，通常可以关闭以下功能：

• 应用程序.屏幕更新

  关闭屏幕更新。如果Application.ScreenUpdating设置为 False，Excel 不会重绘屏幕。代码运行时，屏幕会快速更新，用户通常不需要看到每个更新。在代码执行后更新屏幕一次可提高性能。

• 应用程序.显示状态栏

  关闭状态栏。如果Application.DisplayStatusBar设置为 False，Excel 不会显示状态栏。状态栏设置与屏幕更新设置是分开的，因此即使屏幕未更新，您仍然可以显示当前操作的状态。但是，如果不需要显示每个操作的状态，则在代码运行时关闭状态栏也会提高性能。

• 应用.计算

  切换到手动计算。如果Application.Calculation设置为xlCalculationManual，Excel 仅在用户显式启动计算时计算工作簿。在自动计算模式下，Excel 确定何时进行计算。例如，每次与公式相关的单元格值发生更改时，Excel 都会重新计算公式。如果将计算模式切换为手动，则可以等到与公式关联的所有单元格都更新完毕后再重新计算工作簿。通过仅在必要时在代码运行时重新计算工作簿，可以提高性能。

• 应用程序.启用事件

  关闭事件。如果Application.EnableEvents设置为 False，Excel 不会引发事件。如果有侦听 Excel 事件的外接程序，则这些外接程序在记录事件时会消耗计算机上的资源。如果外接程序不需要记录代码运行时发生的事件，则关闭事件可提高性能。

• ActiveSheet.DisplayPageBreak

  关闭分页符。如果ActiveSheet.DisplayPageBreaks设置为 False，Excel 不显示分页符。在代码运行时不必重新计算分页符，并且在代码执行后计算分页符可以提高性能。

有关此内容的详细信息，请参阅 Excel 性能：优化性能障碍的提示一文。

此外，我建议释放基础 COM 对象。当不再需要所有 Excel 加载项时，都应系统地释放其对 Excel 对象的引用。未能系统地释放对 Excel 对象的引用可能会阻止 Office Excel Microsoft正常关闭。

使用System.Runtime.InteropServices.Marshal.ReleaseComObject 在完成使用 Excel 对象后释放该对象。然后在 Visual Basic 中将变量设置为 Nothing(在 C# 中为 null(以释放对该对象的引用。

Answer 2

每次调用 Office 应用程序中的对象都需要时间 - 尤其是通过互操作。这在您显示的循环操作类型中尤其明显。

相反，请考虑创建值的数组，然后将数组写入 Excel 范围。Excel 应将数组的每个元素写入单元格。这是将大量数据写入 Excel 以加快该过程时的标准做法。

假设 shiftStops 是一个数组，代码可能如下所示：

workSheet = (Excel.Worksheet)wbook.ActiveSheet;
var row = 1;
Excel.Range rng = worksheet.Cells[row, "A"];
rng = rng.Resize(1, shiftStops.Length);
rng.Value = shiftStops;