关于通过COM自动化调用Excel的效率问题

如果笔者告诉你,往Excel表格中写几十万条数据不会超过10秒钟,而不是通常设想的数分钟甚至数十分钟,你是否会相信,但实际情况就是这样。在Office开发中,最为常用的对象就是Range对象,而最被误用的对象也是Range对象。下面笔者举一个简单的例子说明如何有效的使用Range对象,以导出数据到Excel。

在实践中,通过COM自动化调用Excel,不管是通过VB还是通过VSTO在.net下调用Excel,最为耗时的操作就是对Range的操作,而Excel对象、WorkBook和WorkSheet对象的创建通常会在1-3秒内完成(笔者计算机上测试最多不会超过5秒)。如果我们需要向Excel中导出大量数据,一般的做法是(使用晚绑定):

 

 1Sub test1()
 2      Dim objExcel As Object
 3      Dim objBook As Object
 4      Dim objSheet As Object
 5      Dim objRng As Object
 6      Dim i As Long, j As Long
 7      
 8      Set objExcel = CreateObject("Excel.Application")
 9      Set objBook = objExcel.Workbooks.Add()
10      Set objSheet = objBook.Worksheets(1)
11      Set objRng = objSheet.Range("A" & 1 & ":" & "J" & 2000)
12      
13      With objRng
14            For j = 1 To 2000 Step 1
15                  For i = 1 To 10 Step 1
16                        .Cells(j, i).Value = CStr(j)
17                  Next i
18            Next j
19      End With
20      
21      objExcel.Visible = True
22      
23      Set objBook = Nothing
24      Set objSheet = Nothing
25      Set objExcel = Nothing
26End Sub
27
28


这段程序大概需要执行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倍):

 1Sub Test2()
 2    
 3    Dim objExcel As Object
 4    Dim objBook As Object
 5    Dim objSheet As Object
 6    Dim objRng As Object
 7    Dim i As Long, j As Long
 8
 9    Set objExcel = CreateObject("Excel.Application")
10    Set objBook = objExcel.Workbooks.Add()
11    Set objSheet = objBook.Worksheets(1)
12    Set objRng = objSheet.Range("A" & 1 & ":" & "J" & 20000)
13        
14    Dim a(1 to 200001 to 10As Variant
15    
16    For j = 1 To 20000 Step 1
17        
18        For i = 1 To 10 Step 1
19            a(j, i) = CStr(j)
20        Next i
21        
22    Next j
23    
24    objRng.Value = a
25    
26    objExcel.Visible = True
27    
28    Set objBook = Nothing
29    Set objSheet = Nothing
30    Set objExcel = Nothing
31    
32End Sub
33

这段程序在笔者机子上运行时间为2-4秒,一般为2.x秒,效率差别可见一斑。

一般来说,我们向Excel中写数据,很多数据块应该都是可以使用数组代替的,这种情况下,先生成数组,再将数组赋给Excel的Range对象,即可大大提高效率(而且,数据量越大,效率差别越大)。


 

posted on 2005-06-28 11:04  马维峰  阅读(7450)  评论(37编辑  收藏  举报