一次性将大量数据写入到EXCEL中
之前的帖子http://bbs.csdn.net/topics/390310163
实际的运算远不止求6个数的和，有很多的判断在里边
算出结果大概花费1分钟（这个我能接受），所有可能的组合都被存在一个一维数组temp（）里，现在要把这些组合输出到EXCEL里，大概有30W组，每组6个数，也就是说有30W行数据，每行6个数
（之前也想用二维数组来保存的，但是因为行数不确定，二维动态数组也不熟悉，就改用一维了）
Dim index As Long
For index = 1 To total ’total为计算出来的总组合数，即行数
xlSheet.Cells(index, 1) = temp((index - 1) * 6)
xlSheet.Cells(index, 2) = temp((index - 1) * 6 + 1)
xlSheet.Cells(index, 3) = temp((index - 1) * 6 + 2)
xlSheet.Cells(index, 4) = temp((index - 1) * 6 + 3)
xlSheet.Cells(index, 5) = temp((index - 1) * 6 + 4)
xlSheet.Cells(index, 6) = temp((index - 1) * 6 + 5)
Next
Option Explicit
Dim Data() As String
Dim m As Long, n As Long
Private Sub Command1_Click()
m = 200: n = 10
ReDim Data(1 To m, 1 To n) As String
m = 400: n = 20
ReDim Data(1 To m, 1 To n) As String
m = 800: n = 40
ReDim Data(1 To m, 1 To n) As String
End Sub
'引用对象库:Microsoft Excel 11.0 Object Library
Option Explicit
Dim xlExcel As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Private Sub Command1_Click()
Dim Data(1 To 200, 1 To 10) As String
Dim i As Long, j As Long
For i = 1 To 200
For j = 1 To 10
Data(i, j) = j
Next
Next
On Error GoTo Errhandler
xlExcel.Application.Visible = True
Me.MousePointer = vbHourglass
xlExcel.Workbooks.Add
xlExcel.Workbooks(1).Activate
Set xlSheet = xlExcel.Workbooks(1).Worksheets(1)
xlSheet.Activate
xlSheet.Columns("A:J").NumberFormatLocal = "@" '设置A-J列为文本格式。
'或者xlSheet.Range("A:J").NumberFormatLocal = "@"
xlSheet.Range("A1:J200 ") = Data '填充数组到区域A1到J200
xlSheet.Columns.EntireColumn.AutoFit '列自适应
Me.MousePointer = vbDefault
Errhandler:
Exit Sub
End Sub
Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
xlBook.Close
xlExcel.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlExcel = Nothing
End Sub
