Excel Interop - 效率和性能

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

这里有一些我发现自己:

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

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

减少对Worksheet.Cells.Item(row, col)和的调用Worksheet.Range- 我不得不轮询数百个单元格以找到我需要的单元格.实现一些单元格位置的缓存,将执行时间从大约40秒减少到大约5秒.

什么样的互操作会对性能造成严重影响,应该避免？你还能做些什么来避免不必要的处理？

1> Anonymous Ty..：

---

当使用C#或VB.Net来获取或设置范围时,找出范围的总大小,然后得到一个大的二维对象数组......

//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存储的数据类型(文本或数字),因为当您从对象数组转换回来时,它不会自动为您执行此操作.如果您无法事先确定数据类型,则在必要时添加测试以验证数据.

---

@Will Marcouiller:是的但使用UsedRange属性的问题在于它会对性能产生负面影响.我不确定它甚至比使用cell/offset方法快得多.

2> 小智..：

---

这适用于任何想知道从db结果集填充excel表的最佳方法.这并不是一个完整的列表,但它确实列出了一些选项.

在尝试使用旧的Pentium 4 3GHz盒子上的155列和4200条记录填充excel表时的一些性能数据,包括从最慢到最快的顺序,从不超过10秒的数据检索时间如下......

一次一个牢房 - 不到11分钟

通过转换为html +将html保存到磁盘来填充数据集+将html加载到excel并将工作表保存为xls/xlsx - 5分钟

一次一列--4分钟

使用SQL 2005中不推荐使用的sp_makewebtask过程创建HTML文件 - 9秒+然后在excel中加载html文件并另存为XLS/XLSX - 大约2分钟.

将.Net数据集转换为ADO RecordSet并使用WorkSheet.Range [].CopyFromRecordset函数填充excel - 45秒!

我最终使用选项5.希望这有帮助.

3> Treb..：

---

尽可能使用excels内置功能,例如:不使用整列查找给定字符串,而是使用findCtrl-F在GUI中提供的命令:

Set Found = Cells.Find(What:=SearchString, LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not Found Is Nothing Then
    Found.Activate
    (...)
EndIf

如果要对某些列表进行排序,请使用excel sort命令,不要在VBA中手动执行:

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

4> Jon Fournier..：

---

如果您正在轮询许多单元格的值,则可以一次性获取存储在变量数组中的范围内的所有单元格值:

Dim CellVals() as Variant
CellVals = Range("A1:B1000").Value

这里有一个权衡,就你所获得的范围大小而言.我想如果你需要一千个或更多的单元格值,这可能比循环遍历不同的单元格并轮询值更快.

---

-1,对Jon没有冒犯,但是这个怎么这么多？问题是谈论Excel Interop而不是VBA/VB6.非常确定Variant甚至不作为COM interop(使用对象)的类型存在.