大家好,我要将内存中一个数组的数据写入到Excel文件中,用了如下方法,
现在的问题是 数据量非常大,可能有2万条. 所以效率非常低!
请问应该怎样改进呢? 有没有类似 MSFlexGrid的 addrow 的方法,一次加一行啊? 或是代码如下(省略部分):
Dim objExcelFile As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim sheet As Excel.Worksheet
Set objExcelFile = CreateObject("Excel.Application")
objExcelFile.SheetsInNewWorkbook = 1
Set objWorkBook = objExcelFile.Workbooks.Add
objExcelFile.Sheets(objExcelFile.Sheets.count).Name = "***"
Set sheet = objWorkBook.Sheets("***")***For X = 1 To GetRecordsSum(resultFileNum)
Get #resultFileNum, X, dataRecord
col = 1
sheet.Cells(X + 1, col) = CStr(X): col = col + 1
sheet.Cells(X + 1, col) = dataRecord.shipname: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.saddr1: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.saddr2: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.saddr3: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.consname: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.caddr1: col = col + 1
Next X谢谢
现在的问题是 数据量非常大,可能有2万条. 所以效率非常低!
请问应该怎样改进呢? 有没有类似 MSFlexGrid的 addrow 的方法,一次加一行啊? 或是代码如下(省略部分):
Dim objExcelFile As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim sheet As Excel.Worksheet
Set objExcelFile = CreateObject("Excel.Application")
objExcelFile.SheetsInNewWorkbook = 1
Set objWorkBook = objExcelFile.Workbooks.Add
objExcelFile.Sheets(objExcelFile.Sheets.count).Name = "***"
Set sheet = objWorkBook.Sheets("***")***For X = 1 To GetRecordsSum(resultFileNum)
Get #resultFileNum, X, dataRecord
col = 1
sheet.Cells(X + 1, col) = CStr(X): col = col + 1
sheet.Cells(X + 1, col) = dataRecord.shipname: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.saddr1: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.saddr2: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.saddr3: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.consname: col = col + 1
sheet.Cells(X + 1, col) = dataRecord.caddr1: col = col + 1
Next X谢谢
解决方案 »
- 如何编程开启和关闭windows的桌面扩展功能?
- 怎么在ShockwaveFlash控件里切一张正在播放的Flash图片啊
- 本人想组建开发团队,有兴趣的朋友可加开发爱好者群ID号:13945311
- VB结合asp+access 登陆程序 +效验码 +检测网络连接 +win2000 xp 下窗体透明
- 怎么知道是谁调用了这个窗体呢?
- 如何实现NT的重启?
- 帮忙看看,为什么正常提交后,数据库中却没有写入这个记录?在提交前还需要rst.Update语句吗?
- 数据库匹配问题
- 什么是外接程序?
- 在vb中创建vb应用后,怎么判断这个应用还存在,并没有退出来.
- 怎么让控件大小随窗体的变化而相应的变化
- 求一函數﹐要求返回數組
dim a(11,1) as single
range("a1:b10")=a
a = range("a1:b10")
'* Function Name: ToExcel */
'* Input Arguments: */
'* Out Arguments : */
'* : */
'* Description : */
'* Author : by yarno QQ:84115357 */
'* Date : 2005-11-25 */
'***********************************************************************/
Public Function ToExcel()On Error GoTo ErrorHandler Dim exlapp As Excel.Application
Dim exlbook As Excel.Workbook
Set exlapp = CreateObject("Excel.Application")
Set exlbook = exlapp.Workbooks.Add
exlapp.Caption = "数据正在导出......"
exlapp.Visible = True
exlapp.DisplayAlerts = False
Dim exlsheet As Excel.Worksheet
Set exlsheet = exlbook.Worksheets.Add
exlsheet.Activate
Set exlsheet = exlsheet
exlsheet.Name = "我导出的数据"
'设置列宽
exlapp.ActiveSheet.Columns(1).ColumnWidth = 10
exlapp.ActiveSheet.Columns(2).ColumnWidth = 20
StrSql = "你的SQL语句"
Set exl_rs = PubSysCn.Execute(StrSql)
exlsheet.Range("A2").CopyFromRecordset exl_rs
exl_rs.Close
Set exl_rs = Nothing
exlapp.Worksheets("sheet1").Delete
exlapp.Worksheets("sheet2").Delete
exlapp.Worksheets("sheet3").Delete
exlapp.DisplayAlerts = True
exlapp.Caption = "数据导出完毕!!"
exlapp.Visible = True
Set exlapp = Nothing
Set exlbook = Nothing
Set exlsheet = Nothing
Exit Function
ErrorHandler:
MsgBox "EXCEL : " & err.Number & " : " & err.Description
End Function