关于通过COM自动化调用Excel的效率问题
如果笔者告诉你,往Excel表格中写几十万条数据不会超过10秒钟,而不是通常设想的数分钟甚至数十分钟,你是否会相信,但实际情况就是这样。在Office开发中,最为常用的对象就是Range对象,而最被误用的对象也是Range对象。下面笔者举一个简单的例子说明如何有效的使用Range对象,以导出数据到Excel。
在实践中,通过COM自动化调用Excel,不管是通过VB还是通过VSTO在.net下调用Excel,最为耗时的操作就是对Range的操作,而Excel对象、WorkBook和WorkSheet对象的创建通常会在1-3秒内完成(笔者计算机上测试最多不会超过5秒)。如果我们需要向Excel中导出大量数据,一般的做法是(使用晚绑定):
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
这段程序大概需要执行2分钟左右(注意,数据一共是2000行;笔者的机器是Dell i8500笔记本,P4 2.0,512内存)甚至更长。使用早期绑定和晚期绑定对于这样的程序,差别很小。
如果测试一下,可以发现时间主要花在Range的操作上。查一下Excel的文档,可以看到,Range对象可以和数组进行很好的互操作。在笔者正在进行的《Excel与VBA程序设计》中关于效率一节(7.7)对Range对象的使用,特别是大数据量频繁引用的情况下,需要使用数组代替,但没有展开讨论。
例如将Range赋给数组:vData = ActiveSheet.Range("A1:B10").Value
vData 就是一个数组。反过来,使用:
ActiveSheet.Range("D1:E10").Value = vData
就可以将数组vData的值赋给Range,如果Range的范围较小,则自动截断。
下面,我们将上面的例子使用此思路修改为以下的样子(这次数据一共为20000行,是上边的10倍):
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
这段程序在笔者机子上运行时间为2-4秒,一般为2.x秒,效率差别可见一斑。
一般来说,我们向Excel中写数据,很多数据块应该都是可以使用数组代替的,这种情况下,先生成数组,再将数组赋给Excel的Range对象,即可大大提高效率(而且,数据量越大,效率差别越大)。