Excel 互操作 - 效率和性能 [英] Excel Interop - Efficiency and performance

问题描述

我想知道我可以做些什么来提高 Excel 自动化的性能，因为如果工作表中有很多事情要做，它可能会很慢......

I was wondering what I could do to improve the performance of Excel automation, as it can be quite slow if you have a lot going on in the worksheet...

以下是我自己发现的一些:

Here's a few I found myself:

• ExcelApp.ScreenUpdating = false -- 关闭屏幕重绘

ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual -- 关闭计算引擎，以便 Excel 在单元格值更改时不会自动重新计算(在您更改后重新打开)完成)

ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual -- turning off the calculation engine so Excel doesn't automatically recalculate when a cell value changes (turn it back on after you're done)

减少对 Worksheet.Cells.Item(row, col) 和 Worksheet.Range 的调用——我必须轮询数百个单元格才能找到该单元格我需要.实现了一些单元格位置的缓存，将执行时间从约 40 秒减少到约 5 秒.

Reduce calls to Worksheet.Cells.Item(row, col) and Worksheet.Range -- I had to poll hundreds of cells to find the cell I needed. Implementing some caching of cell locations, reduced the execution time from ~40 to ~5 seconds.

什么样的互操作调用会严重影响性能并且应该避免?您还能做些什么来避免进行不必要的处理?

What kind of interop calls take a heavy toll on performance and should be avoided? What else can you do to avoid unnecessary processing being done?

推荐答案

在使用 C# 或 VB.Net 获取或设置范围时，先弄清楚范围的总大小是多少，然后得到一个大的二维对象数组...

When using C# or VB.Net to either get or set a range, figure out what the total size of the range is, and then get one large 2 dimensional object array...

//get values
object[,] objectArray = shtName.get_Range("A1:Z100").Value2;
iFace = Convert.ToInt32(objectArray[1,1]);

//set values
object[,] objectArray = new object[3,1] {{"A"}{"B"}{"C"}};
rngName.Value2 = objectArray;

请注意，了解 Excel 存储的数据类型(文本或数字)很重要，因为当您将类型从对象数组转换回时，它不会自动为您执行此操作.如果您不能事先确定数据的类型，则在必要时添加测试以验证数据.

Note that its important you know what datatype Excel is storing (text or numbers) as it won't automatically do this for you when you are converting the type back from the object array. Add tests if necessary to validate the data if you can't be sure beforehand of the type of data.

这篇关于Excel 互操作 - 效率和性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！