none
Export DataSet to Existing Excel Spreadsheet RRS feed

  • Question

  • I am looking for an example to export the data from a dataset to an existing excel speadsheet in vb.net 2005

    Or could someone show me me the easiest way to achieve this please.

     

    Cheers, Darren.

    Friday, November 30, 2007 1:03 AM

Answers

  •  

    Something like the following, simply iterating through the dataset and updating the worksheet.  This should get you going.

     

    Code Block

     

    Imports Microsoft.Office.Interop

     

    Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    'Set up a DataSet

    Dim ds As New DataSet

    ds.Tables.Add("Table")

    ds.Tables(0).Columns.Add("Col1")

    ds.Tables(0).Columns.Add("Col2")

    ds.Tables(0).Rows.Add(1, 2)

    ds.Tables(0).Rows.Add(3, 4)

    ds.Tables(0).Rows.Add(5, 6)

     

     

    Try

    Dim oXL As Excel.Application

    Dim oWB As Excel.Workbook

    Dim oSheet As Excel.Worksheet

    Dim oRng As Excel.Range

     

    'On Error GoTo Err_Handler

    ' Start Excel and get Application object.

    oXL = New Excel.Application

    oXL.Visible = True

    ' Get a new workbook.

    oWB = oXL.Workbooks.Add

    oSheet = oWB.ActiveSheet

    '//Write Dataset to Excel Sheet

    Dim irow As Integer = 1

    For Each dr As DataRow In ds.Tables(0).Rows

    irow += 1

    Dim icol As Integer = 1

    For Each c As String In dr.ItemArray

    icol += 1

    'Determine cell to write

    oSheet.Cells(irow, icol).Value = c

    Next

    Next

     

    ' Make sure Excel is visible and give the user control

    ' of Microsoft Excel's lifetime.

    oXL.Visible = True

    oXL.UserControl = True

    ' Make sure you release object references.

    oRng = Nothing

    oSheet = Nothing

    oWB = Nothing

    oXL = Nothing

    Catch ex As Exception

    MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)

    End Try

     

     

     

     

     

    End Sub

    End Class

     

     

    Friday, November 30, 2007 1:19 AM
  •  

     

    All you need to do is instead of creating a new spreadsheet.  Open an existing one using the

     

    Workbooks Open Method.

     

     

    oWB = oXL.Workbooks.Open("C:\test1.xls").

     

    Instead of the  WorkBooks.Add method

     

    Friday, November 30, 2007 6:47 PM

All replies

  •  

    Something like the following, simply iterating through the dataset and updating the worksheet.  This should get you going.

     

    Code Block

     

    Imports Microsoft.Office.Interop

     

    Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    'Set up a DataSet

    Dim ds As New DataSet

    ds.Tables.Add("Table")

    ds.Tables(0).Columns.Add("Col1")

    ds.Tables(0).Columns.Add("Col2")

    ds.Tables(0).Rows.Add(1, 2)

    ds.Tables(0).Rows.Add(3, 4)

    ds.Tables(0).Rows.Add(5, 6)

     

     

    Try

    Dim oXL As Excel.Application

    Dim oWB As Excel.Workbook

    Dim oSheet As Excel.Worksheet

    Dim oRng As Excel.Range

     

    'On Error GoTo Err_Handler

    ' Start Excel and get Application object.

    oXL = New Excel.Application

    oXL.Visible = True

    ' Get a new workbook.

    oWB = oXL.Workbooks.Add

    oSheet = oWB.ActiveSheet

    '//Write Dataset to Excel Sheet

    Dim irow As Integer = 1

    For Each dr As DataRow In ds.Tables(0).Rows

    irow += 1

    Dim icol As Integer = 1

    For Each c As String In dr.ItemArray

    icol += 1

    'Determine cell to write

    oSheet.Cells(irow, icol).Value = c

    Next

    Next

     

    ' Make sure Excel is visible and give the user control

    ' of Microsoft Excel's lifetime.

    oXL.Visible = True

    oXL.UserControl = True

    ' Make sure you release object references.

    oRng = Nothing

    oSheet = Nothing

    oWB = Nothing

    oXL = Nothing

    Catch ex As Exception

    MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)

    End Try

     

     

     

     

     

    End Sub

    End Class

     

     

    Friday, November 30, 2007 1:19 AM
  • Cheers for that. I am also wondering how i can save this to an existing spreadsheet though.

     

    Darren.

    Friday, November 30, 2007 5:03 AM
  •  

     

    All you need to do is instead of creating a new spreadsheet.  Open an existing one using the

     

    Workbooks Open Method.

     

     

    oWB = oXL.Workbooks.Open("C:\test1.xls").

     

    Instead of the  WorkBooks.Add method

     

    Friday, November 30, 2007 6:47 PM
  • OK got that.
    I am now having trouble referencing "Microsoft.Office.Interop"
    I have tried adding the Object Libraries, "Microsoft Excel 11" and "Microsoft Office 11" and all i come up with is "Microsoft.Office.Core"

    Am i missing something here?

    Cheers.


    Monday, December 3, 2007 3:09 AM
  •  

    Hi Darren,

                     Good Day!

     Here i give the URL export the data from a dataset to an existing excel speadsheet in ASP.NET and C#

     

    http://balanagaraj.wordpress.com/2007/12/07/export-excel-sheet-data-from-dataset-in-aspnet/

     

    I hope it will helpful for you..

     

    Bala

    http://balanagaraj.wordpress.com

    Wednesday, December 12, 2007 4:59 AM
  • Hi,

    you can easily export DataSet to Excel (existing or new spreadsheet) with this C# / VB.NET Excel component.

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

    ' Create new Excel file.
    Dim excelFile = New ExcelFile()
    
    ' Load Excel file from XLS format.
    excelFile.LoadXls(filePath)
    
    ' Append each DataTable to a worksheet with the same name.
    For Each dataTable As DataTable In dataSet.Tables
    	Dim worksheet = excelFile.Worksheets(dataTable.TableName)
    	worksheet.InsertDataTable(dataTable, worksheet.Rows.Count, 0, False)
    Next
    
    ' Save Excel file to XLS format.
    excelFile.SaveXls(filePath)

    Thursday, April 26, 2012 9:04 AM
  • i know i am about two years late

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'Set up a DataSet
            Me.TblDatabaseTableAdapter.Fill(Me.RecordsDataSet.tblDatabase)

            Try
                Dim oXL As Excel.Application
                Dim oWB As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                Dim oRng As Excel.Range
                'On Error GoTo Err_Handler
                ' Start Excel and get Application object.
                oXL = New Excel.Application
                oXL.Visible = True
                ' Get a new workbook.
                oWB = oXL.Workbooks.Add
                oSheet = oWB.ActiveSheet
                '//Write Dataset to Excel Sheet
                Dim coll As Integer = 1
                Dim irow As Integer = 1
                For Each dr As DataRow In RecordsDataSet.Tables(0).Rows
                    irow += 1
                    Dim icol As Integer = 1
                    For Each c As String In dr.ItemArray
                        icol += 1
                        'Determine cell to write
                        oSheet.Cells(irow, icol).Value = c
                    Next
                Next
                ' Make sure Excel is visible and give the user control
                ' of Microsoft Excel's lifetime.
                oXL.Visible = True
                oXL.UserControl = True
                ' Make sure you release object references.
                oRng = Nothing
                oSheet = Nothing
                oWB = Nothing
                oXL = Nothing
            Catch ex As Exception
                MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
            End Try
        End Sub

    i have use your method and it work great the only problem is i cant get the column name/header

    Sunday, January 5, 2014 3:45 PM
  • Dazza, did you get this working, or not?  If not, see the examples here.

    http://vb.net-informations.com/

    Scroll down until you find the section titled 'VB.NET Excel 2007 Tutorials'

    There are MANY great samples there.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, January 9, 2014 3:30 PM