缓慢导出从访问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屋！