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: http://stackoverflow.com/a/7390928/1339616 — mrrodd, Feb 06 '14 at 05:35
I guess it won't work on stored proc. I get my data on stored proc result. — Kris Edison, Feb 06 '14 at 05:40
Create a 2d array from your data - you can place that on the sheet in a single operation. See the accepted answer here http://stackoverflow.com/questions/536636/write-array-to-excel-range — Tim Williams, Feb 06 '14 at 05:51

score 2 · Answer 1 · answered Feb 06 '14 at 05:31

2

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

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

answered Feb 06 '14 at 05:31

Bjørn-Roger Kringsjå

9,849
6
36
64

score 0 · Answer 2 · answered Feb 25 '15 at 07:06

There is a much faster way involving SQL Data Pump (Import Export DTS). You can save a DTS Package (that exports data from SQL to Excel about 1000 rows per second) then run that package using SQL Server Agent. This way, you don't have to iterate to all the rows and columns one by one and you don't need to have a VB code.

score 0 · Answer 3 · answered Jan 28 '23 at 07:26

Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Linq
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Core
Imports Microsoft.VisualBasic.DateAndTime


Private Sub Btn_Add_Click(sender As Object, e As EventArgs) Handles btn_Add.Click

        DataSetToExcel(ReturnDS("Select * from Membership"), "Test")

    End Sub



Public Shared Function DataSetToExcel(myDS As DataSet, fileName As String)
    Try
        Dim Excel As Object = CreateObject("Excel.Application")
        With Excel
            .SheetsInNewWorkbook = 1
            .Workbooks.Add()
            .Worksheets(1).Select()
            Dim i As Integer = 1
            For col = 0 To myDS.Tables(0).Columns.Count - 1
                .Cells(1, i).value = myDS.Tables(0).Columns(col).ColumnName
                .Cells(1, i).EntireRow.Font.Bold = True
                i += 1
            Next
            i = 2
            Dim j As Integer = 1
            For col = 0 To myDS.Tables(0).Columns.Count - 1
                i = 2
                For row = 0 To myDS.Tables(0).Rows.Count - 1
                    .Cells(i, j).Value = myDS.Tables(0).Rows(row).ItemArray(col)
                    i += 1
                Next
                j += 1
            Next

            Dim MyTime As String
            MyTime = System.DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss")

            .Application.DisplayAlerts = False
            .ActiveCell.Worksheet.SaveAs(fileName & "_" & MyTime & ".xlsx")
            .Workbooks.Close()
        End With
    Catch ex As Exception
    End Try
    Return ""

End Function

Public Shared Function ReturnDS(Query As String) As DataSet
    Dim con = New SqlConnection(Crypto.ConString)
    Dim cmd As New SqlCommand()
    Dim myDA As New SqlDataAdapter()
    Dim myDS As New DataSet 'The DataSet you created.

    cmd.Connection = con
    cmd.CommandText = Query
    cmd.CommandType = CommandType.Text
    myDA.SelectCommand = cmd
    myDA.Fill(myDS)
    Return myDS
End Function

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jan 30 '23 at 17:48

Fast Way To Export DataSet To Excel in VB.Net

3 Answers3