How to write into excel sheet using oledb connection or how to export dataset into excel sheet using oledb connection.

Iam using console application in vb.net. If there are any alternate ways to write into the excel files from dataset.

what is the code that you are working on ?

In vb.net.. I m trying to export dataset into excel using oledb

you want to copy all the datatabel from dataset to respective sheet in an excel file??

ya.. even if not all the tables atleast one table from dataset into one sheet into excel. I want this to be done by oledb connection


i have made the function;

here is the code:

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))

        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) , "
            strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
            strTableQ(i) &= ")"

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


        '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 & " , "
            strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
            strInsertQ(i) &= ")"

        '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())

    End Sub

the strExcelFile parameter stores the address of your excel file on the disk.
just make an empty excel file in windows and give its path to here.

For Example Lets say my dataset name is ds .

then you can call it like:

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

if you dont get it.. here i am giving a complete demo project ..

find the attachment

Thanks Sandeep....

Got it :)

This question has already been answered. Start a new discussion instead.