vb.net 在VB.Net中快速导出大数据表到Excel电子表格

Fast Export of Large Datatable to Excel Spreadsheet in VB.Net

提问by Jeff Beese

I have an interesting conundrum here, how do I quickly (under 1 minute) export a large datatable (filled from SQL, 35,000 rows) into an Excel spreadsheet for users. I have code in place that can handle the export, and while nothing is "wrong" with the code per se, it is infuriatingly slow taking 4 minutes to export the entire file (sometimes longer if a user has less RAM or is running more on their system). Sadly, this is an improvement over the 10+ minutes it used to take using our old method. Simply put, can this be made any faster, without using 3rd party components? If so, how? My code is as follows, the slow down occurs between messageboxes 6 and 7 where each row is written. Thank you all for taking the time to take a look at this:

我在这里有一个有趣的难题，我如何快速（不到 1 分钟）将大型数据表（从 SQL 填充，35,000 行）导出到 Excel 电子表格中供用户使用。我有可以处理导出的代码，虽然代码本身没有任何“错误”，但导出整个文件需要 4 分钟的时间（如果用户的 RAM 较少或运行更多，有时会更长）他们的系统）。遗憾的是，这比使用我们的旧方法花费的 10 多分钟有所改进。简而言之，在不使用 3rd 方组件的情况下，这可以做得更快吗？如果是这样，如何？我的代码如下，在写入每一行的消息框 6 和 7 之间发生减速。感谢大家抽出时间来看看这个：

    Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnJeffTest.Click
           Test(MySPtoExport)
    End Sub

Private Sub Test(ByVal SQL As String)
    'Declare variables used to execute the VUE Export stored procedure
    MsgBox("start stop watch")
    Dim ConnectionString As New SqlConnection(CType(ConfigurationManager.AppSettings("ConnString"), String))
    Dim cmdSP As New SqlClient.SqlCommand
    Dim MyParam As New SqlClient.SqlParameter
    Dim MyDataAdapter As New SqlClient.SqlDataAdapter
    Dim ExportDataSet As New DataTable
    Dim FilePath As String

    MsgBox("stop 1 - end of declare")

    Try

        ' open the connection
        ConnectionString.Open()

        ' Use the connection for this sql command
        cmdSP.Connection = ConnectionString

        'set this command as a stored procedure command
        cmdSP.CommandType = CommandType.StoredProcedure

        'get the stored procedure name and plug it in
        cmdSP.CommandText = SQL

        'Add the Start Date parameter if required
        Select Case StDt
            Case Nothing
                ' there's no parameter to add
            Case Is = 0
                ' there's no parameter to add
            Case Else
                'add the parameter name, it's direction and its value
                MyParam = cmdSP.Parameters.Add("@StartDate", SqlDbType.VarChar)
                MyParam.Direction = ParameterDirection.Input
                MyParam.Value = Me.txtStartDate.Text
        End Select
        MsgBox("stop 2 - sql ready")
        'Add the End Date parameter if required
        Select Case EdDt
            Case Nothing
                ' there's no parameter to add
            Case Is = 0
                ' there's no parameter to add
            Case Else
                'add the parameter name, it's direction and its value

                MyParam = cmdSP.Parameters.Add("@EndDate", SqlDbType.VarChar)
                MyParam.Direction = ParameterDirection.Input
                MyParam.Value = Me.txtEndDate.Text
        End Select

        'Add the single parameter 1 parameter if required
        Select Case SPar1
            Case Is = Nothing
                ' there's no parameter to add
            Case Is = ""
                ' there's no parameter to add
            Case Else
                'add the parameter name, it's direction and its value
                MyParam = cmdSP.Parameters.Add(SPar1, SqlDbType.VarChar)
                MyParam.Direction = ParameterDirection.Input
                MyParam.Value = Me.txtSingleReportCrt1.Text
        End Select

        'Add the single parameter 2 parameter if required
        Select Case Spar2
            Case Is = Nothing
                ' there's no parameter to add
            Case Is = ""
                ' there's no parameter to add
            Case Else
                'add the parameter name, it's direction and its value
                MyParam = cmdSP.Parameters.Add(Spar2, SqlDbType.VarChar)
                MyParam.Direction = ParameterDirection.Input
                MyParam.Value = Me.txtSingleReportCrt2.Text
        End Select

        MsgBox("stop 3 - params ready")

        'Prepare the data adapter with the selected command 
        MyDataAdapter.SelectCommand = cmdSP

        ' Set the accept changes during fill to false for the NYPDA export
        MyDataAdapter.AcceptChangesDuringFill = False

        'Fill the Dataset tables (Table 0 = Exam Eligibilities, Table 1  = Candidates Demographics)
        MyDataAdapter.Fill(ExportDataSet)

        'Close the connection
        ConnectionString.Close()

        'refresh the destination path in case they changed it
        SPDestination = txtPDFDestination.Text

        MsgBox("stop 4 - procedure ran, datatable filled")

        Select Case ExcelFile
            Case True

                FilePath = SPDestination & lblReportName.Text & ".xls"

                Dim _excel As New Microsoft.Office.Interop.Excel.Application
                Dim wBook As Microsoft.Office.Interop.Excel.Workbook
                Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

                wBook = _excel.Workbooks.Add()
                wSheet = wBook.ActiveSheet()

                Dim dt As System.Data.DataTable = ExportDataSet
                Dim dc As System.Data.DataColumn
                Dim dr As System.Data.DataRow
                Dim colIndex As Integer = 0
                Dim rowIndex As Integer = 0

                MsgBox("stop 5 - excel stuff declared")

                For Each dc In dt.Columns
                    colIndex = colIndex + 1
                    _excel.Cells(1, colIndex) = dc.ColumnName
                Next

                MsgBox("stop 6 - Header written")

                For Each dr In dt.Rows
                    rowIndex = rowIndex + 1
                    colIndex = 0
                    For Each dc In dt.Columns
                        colIndex = colIndex + 1
                        _excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                    Next
                Next

                MsgBox("stop 7 - rows written")

                wSheet.Columns.AutoFit()

                MsgBox("stop 8 - autofit complete")

                Dim strFileName = SPDestination & lblReportName.Text & ".xls"

                If System.IO.File.Exists(strFileName) Then
                    System.IO.File.Delete(strFileName)
                End If

                MsgBox("stop 9 - file checked")

                wBook.SaveAs(strFileName)
                wBook.Close()
                _excel.Quit()
        End Select

        MsgBox("File " & lblReportName.Text & " Exported Successfully!")


        'Dispose of unneeded objects
        MyDataAdapter.Dispose()
        ExportDataSet.Dispose()
        StDt = Nothing
        EdDt = Nothing
        SPar1 = Nothing
        Spar2 = Nothing
        MyParam = Nothing
        cmdSP.Dispose()
        cmdSP = Nothing
        MyDataAdapter = Nothing
        ExportDataSet = Nothing

    Catch ex As Exception
        '  Something went terribly wrong.  Warn user.
        MessageBox.Show("Error: " & ex.Message, "Stored Procedure Running Process ", _
       MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally
        'close the connection in case is still open
        If Not ConnectionString.State = ConnectionState.Closed Then
            ConnectionString.Close()
            ConnectionString = Nothing
        End If

        ' reset the fields
        ResetFields()

    End Try
End Sub

回答by Tim Williams

As when using VBA to automate Excel, you can assign an array directly to the value of a Range object: this is done as a single operation, so you remove the overhead associated with making multiple calls across the process boundaries between your .Net code and the Excel instance.

与使用 VBA 自动化 Excel 时一样，您可以将数组直接分配给 Range 对象的值：这是作为单个操作完成的，因此您可以消除与跨 .Net 代码和Excel 实例。

Eg, see the accepted answer here: Write Array to Excel Range

例如，请参阅此处接受的答案：将数组写入 Excel 范围

回答by Gilles5678

Here is a piece of my own code that performs a very fastexport of data from a DataTable to an Excel sheet (use the "Stopwatch" object to compare the speed and let me a comment):

这是我自己的一段代码，它可以非常快速地将数据从 DataTable 导出到 Excel 工作表（使用“秒表”对象来比较速度并让我发表评论）：

Dim _excel As New Excel.Application
Dim wBook As Excel.Workbook
Dim wSheet As Excel.Worksheet

wBook = _excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()


Dim dc As System.Data.DataColumn
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
'Nombre de mesures
Dim Nbligne As Integer = DtMesures.Rows.Count

'Ecriture des entêtes de colonne et des mesures
'(Write column headers and data)

For Each dc In DtMesures.Columns
  colIndex = colIndex + 1
  'Entête de colonnes (column headers)
  wSheet.Cells(1, colIndex) = dc.ColumnName
  'Données(data)
  'You can use CDbl instead of Cobj If your data is of type Double
  wSheet.Cells(2, colIndex).Resize(Nbligne, ).Value = _excel.Application.transpose(DtMesures.Rows.OfType(Of DataRow)().[Select](Function(k) CObj(k(dc.ColumnName))).ToArray())
Next

回答by CPRouse

Even though the question was asked several years ago, I thought I would add my solution since the question was posed in VB and the "best answer" is in C#. This solution writes 22,000+ rows (1.9MB) in 4 seconds on an i7 System w/ 16GB RAM.

尽管这个问题是几年前提出的，但我认为我会添加我的解决方案，因为这个问题是在 VB 中提出的，而“最佳答案”是在 C# 中。此解决方案在具有 16GB RAM 的 i7 系统上在 4 秒内写入 22,000 多行 (1.9MB)。

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Main
    Private Sub btnExportToExcel(sender As Object, e As EventArgs) Handles btnExpToExcel.Click
        'Needed for the Excel Workbook/WorkSheet(s)
        Dim app As New Excel.Application
        Dim wb As Excel.Workbook = app.Workbooks.Add()
        Dim ws As Excel.Worksheet
        Dim strFN as String = "MyFileName.xlsx"    'must have ".xlsx" extension

        'Standard code for filling a DataTable from SQL Server
        Dim strSQL As String = "My SQL Statement for the DataTable"
        Dim conn As New SqlConnection With {.ConnectionString = "My Connection"}
        Dim MyTable As New DataTable
        Dim cmd As New SqlCommand(strSQL, conn)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(MyTable)

        'Add a sheet to the workbook and fill it with data from MyTable
        'You could create multiple tables and add additional sheets in a loop
        ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
        DataTableToExcel(MyTable, ws, strSym)

        wb.SaveAs(strFN)    'save and close the WorkBook
        wb.Close()

        MsgBox("Export complete.")
    End Sub

    Private Sub DataTableToExcel(dt As DataTable, ws As Excel.Worksheet, TabName As String)
        Dim arr(dt.Rows.Count, dt.Columns.Count) As Object
        Dim r As Int32, c As Int32
        'copy the datatable to an array
        For r = 0 To dt.Rows.Count - 1
            For c = 0 To dt.Columns.Count - 1
                arr(r, c) = dt.Rows(r).Item(c)
            Next
        Next

        ws.Name = TabName   'name the worksheet
        'add the column headers starting in A1
        c = 0
        For Each column As DataColumn In dt.Columns
            ws.Cells(1, c + 1) = column.ColumnName
            c += 1
        Next
        'add the data starting in cell A2
        ws.Range(ws.Cells(2, 1), ws.Cells(dt.Rows.Count, dt.Columns.Count)).Value = arr
    End Sub
End Class

Hope it helps.

希望能帮助到你。

回答by adam

We had a VB.NET app that did exactly this, and took even longer for our users who were on slow PC's... sometimes 15 minutes.

我们有一个 VB.NET 应用程序可以做到这一点，对于使用慢速 PC 的用户来说，花费的时间甚至更长……有时需要 15 分钟。

The app is now an ASP/VB.NET app which simply builds an HTML table and outputs the result as an .xls extension... excel is able to read the HTML table and parse it into a grid format. You can still pass in XML for formatting and options, horizontal pane locking, etc.

该应用程序现在是一个 ASP/VB.NET 应用程序，它只是构建一个 HTML 表并将结果输出为 .xls 扩展名...... excel 能够读取 HTML 表并将其解析为网格格式。您仍然可以传入 XML 以进行格式设置和选项、水平窗格锁定等。

If you don't have the option of using ASP.NET... try looking into a way to build an HTML table string and have excel parse & populate for you... much faster! I'm sure excel can parse other types as well.... XML, Arrays, HTML, etc... all would be quicker than manually building each row through VB.NET objects.

如果您没有使用 ASP.NET 的选项...尝试寻找一种方法来构建 HTML 表格字符串并为您进行 excel 解析和填充...速度要快得多！我确信 excel 也可以解析其他类型...... XML、数组、HTML 等......所有这些都比通过 VB.NET 对象手动构建每一行要快。