Write into excel using oledb Connection

[deva.v]

Hi,

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.

[debasisdas]

what is the code that you are working on ?

[sandeepparekh9]

this will help you: http://www.codeproject.com/KB/office/excel_using_oledb.aspx

and this too: http://stackoverflow.com/questions/57987/writing-into-excel-file-with-oledb

[deva.v]

what is the code that you are working on ?

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

[deva.v]

this will help you: http://www.codeproject.com/KB/office/excel_using_oledb.aspx

and this too: http://stackoverflow.com/questions/57987/writing-into-excel-file-with-oledb

I already tried with that.. I need to copy the dataset into excel sheet using oledb connections only.

[sandeepparekh9]

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

[deva.v]

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

[sandeepparekh9]

hi..

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

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

[sandeepparekh9]

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

find the attachment

[deva.v]

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

find the attachment

Thanks Sandeep....

Got it :)

[sandeepparekh9]

if solved then mark the thread solved.. :)