缓慢导出从访问excel [英] slow exporting from access to excel
问题描述
我必须从访问数据库生成约800个excel文件。
I have to generate about 800 excel files from an access database.
对于前10-15个,它工作不错,几秒/ excel文件,它的持续时间越来越长,第150个excel文件需要10分钟。
For the first 10-15 of them it's working nice, a few seconds/excel file but it's constantly taking longer, at the 150th excel file it's taking 10 minutes.
这是我的代码:
它正在为访问表中的每个nrliste(其中约800个)执行此操作。
It's doing this for each nrliste in the access table (about 800 of them)
Dim lista = From ls In Liste _
Where ls!Concatenare = nrliste(i) _
Select ls
Dim table = lista.CopyToDataTable
Dim DataArr(table.Rows.Count, 30)
For x = 0 To table.Rows.Count - 1
For y = 0 To 30
DataArr(x, y) = table.Rows(x).Item(y)
Next
Next
Dim filetocopy As String
Dim newcopy As String
Dim tempname As String = nrliste(i).ToString
Dim filename As String = "LISTA INV OBI(MF) LA 30.09.2009_" & tempname.Replace("#", "_")
filetocopy = Environment.CurrentDirectory & "\MACHETA.xls"
newcopy = FolderBD.SelectedPath & "\" & filename & ".xls"
If System.IO.File.Exists(newcopy) = True Then
System.IO.File.Delete(newcopy)
System.IO.File.Copy(filetocopy, newcopy)
Else
System.IO.File.Copy(filetocopy, newcopy)
End If
'excel file
Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Open(newcopy)
Dim xlSheet As Excel.Worksheet = CType(xlWBook.Worksheets("Lista inventar OBI de natura MF"), Excel.Worksheet)
'insereaza liniile necesare
For n = 11 To ((lista.Count - 1) + 11)
With xlSheet
.Rows(n).Insert(Excel.XlDirection.xlDown, 1)
End With
Next
'copiaza datele
With xlSheet
.Range(.Cells(11, 1), .Cells(table.Rows.Count + 11, 31)).Value = DataArr
End With
推荐答案
而不是行插入我会尝试CopyFromRecordset做一个整个记录集。当然,您必须大幅度地修改逻辑。
Instead of Rows Insert I'd try CopyFromRecordset to do an entire recordset. You'd have to substantially rework your logic of course.
但更重要的是,一旦完成了Excel电子表格对象,您将在哪里关闭?
But more importantly where are you closing the Excel spreadsheet object once you're done with it?
这篇关于缓慢导出从访问excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!