Fast Way To Export DataSet To Excel in VB.Net

Question

I need to export a lot (nearly a million) of data everyday from SQLServer to Excel. The data is being processed through a stored procedure then I put them on the DataSet and tried to export using this code:

` Private Sub ExportToExcel(ByVal dtTemp As System.Data.DataTable, ByVal filepath As String) Dim strFileName As String = filepath

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

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

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

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

    For Each dr In dt.Rows
        rowIndex = rowIndex + 1
        colIndex = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            wSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
        Next
    Next
    wSheet.Columns.AutoFit()
    wBook.SaveAs(strFileName)

    ReleaseObject(wSheet)
    wBook.Close(False)
    ReleaseObject(wBook)
    _excel.Quit()
    ReleaseObject(_excel)
    GC.Collect()
End Sub`

Is there any faster way for this? How about DataSet to Clipboard then paste it to excel?

To Export the result of a SQL query to Excel, take a look at this SO Post: stackoverflow.com/a/7390928/1339616 — mrrodd
I guess it won't work on stored proc. I get my data on stored proc result. — Kris Edison
Create a 2d array from your data - you can place that on the sheet in a single operation. See the accepted answer here stackoverflow.com/questions/536636/write-array-to-excel-range — Tim Williams

Bjørn-Roger Kringsjå Bjørn-Roger Kringsjå · Accepted Answer · 2014-02-06T05:31:47

2

votes

One way is to save the DataSet as an XML file:

myDataSet.WriteXml("c:\file.xml")

Fast Way To Export DataSet To Excel in VB.Net

2 Answers