How to Export Dataset contents to Excel File

If you have come across the need to export the contents of your dataset in VB.net to an excel file then this code will help you. With this code snippet you will be able to dump the entire contents of the dataset to an excel file.

Public Sub ExportDatasetToExcel(ByVal ds As DataSet, ByVal strExcelFile As String)

        Dim conn As New OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", strExcelFile))
        conn.Open()

        Dim strTableQ(ds.Tables.Count) As String

        Dim i As Integer = 0

        'making table query
        For i = 0 To ds.Tables.Count - 1

            strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("

            Dim j As Integer = 0
            For j = 0 To ds.Tables(i).Columns.Count - 1
                Dim dCol As DataColumn
                dCol = ds.Tables(i).Columns(j)
                strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255) , "
            Next
            strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
            strTableQ(i) &= ")"

            Dim cmd As New OleDbCommand(strTableQ(i), conn)
            cmd.ExecuteNonQuery()

        Next

        'making insert query
        Dim strInsertQ(ds.Tables.Count - 1) As String
        For i = 0 To ds.Tables.Count - 1
            strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
            For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & " , "
            Next
            strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
            strInsertQ(i) &= ")"
        Next

        'Now inserting data
        For i = 0 To ds.Tables.Count - 1
            For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
                Dim cmd As New OleDbCommand(strInsertQ(i), conn)
                For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                    cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(), ds.Tables(i).Rows(j)(k).ToString())
                Next
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()

            Next
        Next
    End Sub
 

And here's how you use the code:

ExportDatasetToExcel(dsFinal, "d:\\my.xls")