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电话（行，列）和 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要么获取或设置一个范围，弄清楚范围的总规模是什么，然后得到一个大的2维对象数组...

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屋！