Convert DataSet to Excel Sheet

Question

Hi
I am Using .NET 3.5 with VB2008.
Now wht i want to do is i have a dataset which has more than one datatables inside it. Now I want that Each table to be exported to a Excel file .
Means If there are 4 datatables inside that dataset then there should be 4 sheets , one for each datatable included in that sheet.

Please provide me a sollution how can i do this.

Answer 1

Dim dt1 As New DataTable
          Dim I1, J1 As Integer
          For I1 = 0 To dsmas1.Tables(0).Columns.Count - 1
                dt1.Columns.Add(dsmas1.Tables(0).Columns(I1).ColumnName)
           Next
           For I1 = 0 To dsmas1.Tables(0).Rows.Count - 1
                Dim DR As DataRow = Nothing
                DR = dt1.NewRow
                For J1 = 0 To dsmas1.Tables(0).Columns.Count - 1
                    DR.Item(J1) = dsmas1.Tables(0).Rows(I1).ItemArray(J1)
                Next
                dt1.Rows.Add(DR)
           Next
           rel_ds.Tables.Add(dt1)
           Dim dt As New DataTable
           Dim I, J As Integer
           For I = 0 To dschd1.Tables(0).Columns.Count - 1
                dt.Columns.Add(dschd1.Tables(0).Columns(I).ColumnName)
           Next
           For I = 0 To dschd1.Tables(0).Rows.Count - 1
                Dim DR As DataRow = Nothing
                DR = dt.NewRow
                For J = 0 To dschd1.Tables(0).Columns.Count - 1
                    DR.Item(J) = dschd1.Tables(0).Rows(I).ItemArray(J)
                Next
                dt.Rows.Add(DR)
           Next
            rel_ds.Tables.Add(dt)


'verfying the datagridview having data or not
        If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
            Exit Sub
        End If

        'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To DataGridView1.ColumnCount - 1
            dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DataGridView1.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                dr1(j) = DataGridView1.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dt As System.Data.DataTable = dset.Tables(0)
        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
            excel.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
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
        Next

        wSheet.Columns.AutoFit()
        Dim strFileName As String = "D:\ss.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = True

For more info

http://www.codeproject.com/KB/vb/Senthil_S__Software_Eng_.aspx

---

Thanks, A.m.a.L

Answer 2

Dim dt1 As New DataTable

          Dim I1, J1 As Integer

          For I1 = 0 To dsmas1.Tables(0).Columns.Count - 1

                dt1.Columns.Add(dsmas1.Tables(0).Columns(I1).ColumnName)

           Next

           For I1 = 0 To dsmas1.Tables(0).Rows.Count - 1

                Dim DR As DataRow = Nothing

                DR = dt1.NewRow

                For J1 = 0 To dsmas1.Tables(0).Columns.Count - 1

                    DR.Item(J1) = dsmas1.Tables(0).Rows(I1).ItemArray(J1)

                Next

                dt1.Rows.Add(DR)

           Next

           rel_ds.Tables.Add(dt1)

           Dim dt As New DataTable

           Dim I, J As Integer

           For I = 0 To dschd1.Tables(0).Columns.Count - 1

                dt.Columns.Add(dschd1.Tables(0).Columns(I).ColumnName)

           Next

           For I = 0 To dschd1.Tables(0).Rows.Count - 1

                Dim DR As DataRow = Nothing

                DR = dt.NewRow

                For J = 0 To dschd1.Tables(0).Columns.Count - 1

                    DR.Item(J) = dschd1.Tables(0).Rows(I).ItemArray(J)

                Next

                dt.Rows.Add(DR)

           Next

            rel_ds.Tables.Add(dt)





'verfying the datagridview having data or not

        If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then

            Exit Sub

        End If



        'Creating dataset to export

        Dim dset As New DataSet

        'add table to dataset

        dset.Tables.Add()

        'add column to that table

        For i As Integer = 0 To DataGridView1.ColumnCount - 1

            dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)

        Next

        'add rows to the table

        Dim dr1 As DataRow

        For i As Integer = 0 To DataGridView1.RowCount - 1

            dr1 = dset.Tables(0).NewRow

            For j As Integer = 0 To DataGridView1.Columns.Count - 1

                dr1(j) = DataGridView1.Rows(i).Cells(j).Value

            Next

            dset.Tables(0).Rows.Add(dr1)

        Next



        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass

        Dim wBook As Microsoft.Office.Interop.Excel.Workbook

        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet



        wBook = excel.Workbooks.Add()

        wSheet = wBook.ActiveSheet()



        Dim dt As System.Data.DataTable = dset.Tables(0)

        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

            excel.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

                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)



            Next

        Next



        wSheet.Columns.AutoFit()

        Dim strFileName As String = "D:\ss.xls"

        Dim blnFileOpen As Boolean = False

        Try

            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)

            fileTemp.Close()

        Catch ex As Exception

            blnFileOpen = False

        End Try



        If System.IO.File.Exists(strFileName) Then

            System.IO.File.Delete(strFileName)

        End If



        wBook.SaveAs(strFileName)

        excel.Workbooks.Open(strFileName)

        excel.Visible = True

For more info

http://www.codeproject.com/KB/vb/Senthil_S__Software_Eng_.aspx

---

Thanks, A.m.a.L

Thanks A.M for ur response. But wht i want if there are 4 datatales in the dataset then , there will be only on work book with 4 excell sheets, one for each datatable

Answer 3

For each datatable in dataset, you can use the same procedure. Rite?

---

Thanks, A.m.a.L

Answer 4

For each datatable in dataset, you can use the same procedure. Rite?

---

Thanks, A.m.a.L

Ok but will it create a new sheet in that Exscel file for each WorkBook

Answer 5

Go through that link.

---

Thanks, A.m.a.L

Answer 6

Thanks A.M for ur response. But wht i want if there are 4 datatales in the dataset then , there will be only on work book with 4 excell sheets, one for each datatable

Here is another ariticle with source code which may help you. I have a glance with that project, you can set the export style to "Sheet Wise" to export the datatables to separate work sheets of the work book.

Hope it helps.

Best regards,
Bruce Zhou

---

Please mark the replies as answers if they help and unmark if they don't.

Answer 7

can you provide this code in c#...

i have a requirement that either datagridview or dataset have to export to excel.

saving the datagridview to created excel(already created excel file)

(or)

i have to create random numberd excel and random numberd sheet(i have to create new file)

any one is ok for me...

thanks in advance...

Answer 8

Hi,

I think this Excel C# / VB.NET component would suit your needs.

Here is a sample Excel VB.NET code how to export DataSet to Excel:

Dim bindingSource = DirectCast(Me.peopleDataGridView.DataSource, BindingSource)

Dim dataSet = DirectCast(bindingSource.List, DataView).DataViewManager.DataSet

Dim ef = New ExcelFile()

For Each dataTable As DataTable In dataSet.Tables
	ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, True)
Next

ef.SaveXls(Convert.ToString(dataSet.DataSetName) & ".xls")