title: Fast Export of Large Datatable to Excel Spreadsheet in VB.Net
body: <p>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:</p>
  
  <pre><code>    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 &amp; lblReportName.Text &amp; ".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 &amp; lblReportName.Text &amp; ".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 " &amp; lblReportName.Text &amp; " 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: " &amp; 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
  </code></pre>