Excel Interop - 效率和性能

Question

我想知道我可以做些什么来提高Excel自动化的性能，因为如果你在工作表中有很多事情，它可能会很慢......这里有一些我发现自己：ExcelApp.ScreenUpdating = false - 关闭重绘屏幕ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual - 关闭计算引擎，以便Excel在单元格值更改时不会自动重新计算（在完成后将其重新打开）减少对Worksheet.Cells.Item(row, col)和的调用Worksheet.Range- 我不得不轮询数百个单元格以找到我需要的单元格。实现一些单元格位置的缓存，将执行时间从大约40秒减少到大约5秒。什么样的互操作会对性能造成严重影响，应该避免？你还能做些什么来避免不必要的处理？

Answer 1

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

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

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

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

3. 一次一列--4分钟

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

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

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

Answer 2

尽可能使用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