这是一篇老文了,整理下发出来,希望能对大家有些帮助一般我们在程序中操作Excel时,逐单元格填充时,速度非常之慢。其实Excel的Range可以直接绑定数组,速度极快。在下面的 VB6 示例中,填充一个1000*10的区块,逐单元格方式要20~25秒,而数组方式瞬间内即可完成。Private Sub Command2_Click()
Dim a(1000, 10) As Integer
Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim oXLSAPP As New Excel.Application
Dim oWSheet As Worksheet
Command2.Enabled = False '创建一个新的Excel文件
oXLSAPP.Workbooks.Add
oXLSAPP.Workbooks(1).Activate
Set oWSheet = oXLSAPP.Workbooks(1).Worksheets(1)
oWSheet.Activate
'准备一个数组
For i = 1 To 1000
For j = 1 To 10
Randomize
a(i, j) = CInt(1000 * Rnd)
Randomize
z = z + 1
DoEvents
Next
Next
'利用这个数组填充
Dim xrange As Range
Set xrange = oWSheet.Range("A1:J1000")
Label9.Caption = "正在进行逐格填充"
DoEvents
Label2(0).Caption = Now()
For i = 1 To 1000
For j = 1 To 10
xrange(i, j).Value = a(i, j)
'DoEvents
Next
'DoEvents
Next
Label3(0).Caption = Now()
Label9.Caption = "逐格填充完毕"
Label5(0).Caption = DateDiff("s", CDate(Label2(0).Caption), CDate(Label3(0).Caption)) & " 秒"
DoEvents
'-------------------------------------------------------------
'直接填充
Label9.Caption = "正在进行数组填充"
DoEvents
Label2(1).Caption = Now()
'##################################
'直接把数据给区块
'需要注意的是,这个区块接受数组是从序号0开始的.用的时候注意边界
oWSheet.Range("L1:U1000") = a
'##################################
Label3(1).Caption = Now()
Label9.Caption = "数组填充完毕"
Label5(1).Caption = DateDiff("s", CDate(Label2(1).Caption), CDate(Label3(1).Caption)) & " 秒"
DoEvents
Dim fname As String
fname = App.Path & "\" & Format(Now, "yyyymmddhhMMss") & ".xls"
oXLSAPP.Workbooks(1).SaveAs fname
Label9.Caption = "文件保存到 " & fname
DoEvents
Set oXLSAPP = Nothing
End Sub示例代码下载:http://files.cnblogs.com/babyt/APP20090208EXCEL.rar另外特别推荐:
特别推荐:纯VB.NET代码直接生成Excel文件(不需要Excel)
Dim a(1000, 10) As Integer
Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim oXLSAPP As New Excel.Application
Dim oWSheet As Worksheet
Command2.Enabled = False '创建一个新的Excel文件
oXLSAPP.Workbooks.Add
oXLSAPP.Workbooks(1).Activate
Set oWSheet = oXLSAPP.Workbooks(1).Worksheets(1)
oWSheet.Activate
'准备一个数组
For i = 1 To 1000
For j = 1 To 10
Randomize
a(i, j) = CInt(1000 * Rnd)
Randomize
z = z + 1
DoEvents
Next
Next
'利用这个数组填充
Dim xrange As Range
Set xrange = oWSheet.Range("A1:J1000")
Label9.Caption = "正在进行逐格填充"
DoEvents
Label2(0).Caption = Now()
For i = 1 To 1000
For j = 1 To 10
xrange(i, j).Value = a(i, j)
'DoEvents
Next
'DoEvents
Next
Label3(0).Caption = Now()
Label9.Caption = "逐格填充完毕"
Label5(0).Caption = DateDiff("s", CDate(Label2(0).Caption), CDate(Label3(0).Caption)) & " 秒"
DoEvents
'-------------------------------------------------------------
'直接填充
Label9.Caption = "正在进行数组填充"
DoEvents
Label2(1).Caption = Now()
'##################################
'直接把数据给区块
'需要注意的是,这个区块接受数组是从序号0开始的.用的时候注意边界
oWSheet.Range("L1:U1000") = a
'##################################
Label3(1).Caption = Now()
Label9.Caption = "数组填充完毕"
Label5(1).Caption = DateDiff("s", CDate(Label2(1).Caption), CDate(Label3(1).Caption)) & " 秒"
DoEvents
Dim fname As String
fname = App.Path & "\" & Format(Now, "yyyymmddhhMMss") & ".xls"
oXLSAPP.Workbooks(1).SaveAs fname
Label9.Caption = "文件保存到 " & fname
DoEvents
Set oXLSAPP = Nothing
End Sub示例代码下载:http://files.cnblogs.com/babyt/APP20090208EXCEL.rar另外特别推荐:
特别推荐:纯VB.NET代码直接生成Excel文件(不需要Excel)
特别推荐:纯VB.NET代码直接生成Excel文件(不需要Excel)
http://www.cnblogs.com/unruledboy/archive/2004/07/07/22093.html
=====================
这种数组直接赋值的时候,需要检查下数组中每个元素字符串的长度,长度过长时Excel会抛异常出来0x800A03EC
长度过长时Excel会抛异常出来0x800A03EC特殊情况就需要特殊处理,呵呵。
“注意:操作过程中不可再操作Excel文件,运行前后请杀掉Excel进程。”这个你不必啊 在 Set oXLSAPP = Nothing 前面加上下面的 Quit 即可
oXLSAPP.Quit