none
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.
    Saturday, March 28, 2009 5:58 AM

Answers

  • 
     
    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
    • Edited by A.m.a.L Hashim Saturday, March 28, 2009 6:30 AM code change
    • Marked as answer by Bruce.Zhou Thursday, April 02, 2009 2:48 AM
    Saturday, March 28, 2009 6:27 AM
  • 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.
    • Marked as answer by Bruce.Zhou Thursday, April 02, 2009 2:48 AM
    Monday, March 30, 2009 7:37 AM

All replies

  • 
     
    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
    • Edited by A.m.a.L Hashim Saturday, March 28, 2009 6:30 AM code change
    • Marked as answer by Bruce.Zhou Thursday, April 02, 2009 2:48 AM
    Saturday, March 28, 2009 6:27 AM
  • 
     
    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
    Saturday, March 28, 2009 9:12 AM
  • For each datatable in dataset, you can use the same procedure. Rite?
    Thanks, A.m.a.L
    Saturday, March 28, 2009 9:22 AM
  • 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
    Saturday, March 28, 2009 9:56 AM
  • Go through that link.
    Thanks, A.m.a.L
    Saturday, March 28, 2009 9:59 AM
  • 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.
    • Marked as answer by Bruce.Zhou Thursday, April 02, 2009 2:48 AM
    Monday, March 30, 2009 7:37 AM
  • 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...
    Friday, February 12, 2010 5:09 PM
  • 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")
    

    Thursday, April 07, 2011 9:29 AM