高效率导入数据到Excel.docVIP

关于通过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?objRng14????????????For?j?=?1?To?2000?Step?115??????????????????For?i?=?1?To?10?Step?116????????????????????????.Cells(j,?i).Value?=?CStr(j)17??????????????????Next?i18????????????Next?j19??????End?With20??????21??????objExcel.Visible?=?True22??????23??????Set?objBook?=?Nothing24??????Set?objSheet?=?Nothing25??????Set?objExcel?=?Nothing26End?Sub2728 这段程序大概需要执行2分钟左右（注意，数据一共是2000行；笔者的机器是Dell i8500笔记本，P4 2.0，512内存）甚至更长。使用早期绑定和晚期绑定对于这样的程序，差别很小。 如果测试一下，可以发现时间主要花在Range的操作上。查一下Excel的文档，可以看到，Range对象可以和数组进行很好的互操作。在笔者正在进行的《Excel与VBA程序设计》中关于效率一节（7.7）对Range对象的使用，特别是大数据量频繁引用的情况下，需要使用数组代替，但没有展开讨论。 例如将Range赋给数组：vData = ActiveSheet.Range(A1:B10).ValuevData 就是一个数组。反过来，使用：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?20000,?1?to?10)?As?Variant15????16????For?j?=?1?To