Converting Dataset to Excel

sandeepparekh9 0 1K Views

Function to Convert Dataset to Excel.

It will export all the table of the given dataset to the given excel.


Example:

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

where dsFinal is my Datase and second parameter is the excel file location. Make sure the excel file exist.

Fa3hed commented: HELLO , I AM Fa3hed +1 
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

Dani AI

Generated

Useful practical notes and improvements for 's OLEDB-based export (and thanks to for the praise). The approach is fine for small, ad-hoc .xls exports but has a few common pitfalls that surface later: 32/64-bit compatibility, mismatched provider vs file format (.xls vs .xlsx), type coercion (everything declared as text can lose numeric/date typing), and slow row-by-row inserts. Below are focused, actionable suggestions.

Prefer ACE for .xlsx and use positional parameters and transactions for performance and reliability. Do not rely on named parameters with OleDb; add parameters in the same order as the placeholders. Also explicitly list columns in your INSERT to avoid column-order bugs. Example skeleton (new content, not a replacement of your function):

' ACE provider for .xlsx
Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\out.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';"

Using conn As New OleDbConnection(connStr)
    conn.Open()
    Dim tr = conn.BeginTransaction()
    Using cmd As New OleDbCommand("INSERT INTO [Sheet1$] (ColA,ColB) VALUES (?,?)", conn, tr)
        cmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar))
        cmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar))
        ' set parameter values and ExecuteNonQuery() inside loop
    End Using
    tr.Commit()
End Using

If this runs on a server or you need robust .xlsx output, prefer libraries that write Open XML directly (ClosedXML / Open XML SDK) — they preserve cell types, formatting, and avoid depending on installed Access drivers. Other quick tips: sanitize sheet/column names (Excel limits and forbidden characters), ship or create a blank template if the provider complains about a missing file, and watch for the common "installable ISAM" or format-mismatch errors which usually indicate a wrong connection string or missing ACE driver.

Member Avatar for Fa3hed
Fa3hed 0 Newbie Poster

The best code from the best member

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview