none
Export Dataset to ExcelSheet RRS feed

  • Question

  • Hi,

     

    I have a Excel sheet ABC.xls.

     

    I have requirement to read ABC.xls file--> Read it in Dataset ---> make some changes in Dataset (Change 2 or more)-------> update the changes made in dataset to Excel.

     

    I am able to read Excesheet into Dataset and Could make come changes in datase (add row , delete row,make some changes in  it etc.) . but I don't know How to reflect changes made in dataset to Excelsheet.

     

     

    Dows any one knows hoe to export Dataset to ExcelSheet?????

     

     

    Any kind of help will be appreciated .

     

     

     

    Thanks,

    Vinod Sa.

     

     

     

    Thursday, August 16, 2007 1:56 PM

All replies

  • Hi,

     

    You can export to excel using ADO.NET, the DataAdapter could do it for example. Excel worksheets are just like database tables in that you can run SQL based SELECT and INSERT commands against them. Named ranges in the workbook also act like tables also.

     

    Here is a short code snippet on how to read from Excel using ADO.NET. To write you'd call INSERT commands. UPDATE commands might not work though. There are some considerations... the sheet really should have a header row (a row containing the column headers) not totally required but easier, using sheets as table names need $, name ranges don't need this.

     

            Dim cn As System.Data.OleDb.OleDbConnection
            Dim cmd As System.Data.OleDb.OleDbDataAdapter
            Dim ds As New System.Data.DataSet()

            cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
            "data source=C:\test.xls;Extended Properties=Excel 8.0;")

            cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)
            'cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$A1:A1]", cn)

            cn.Open()
            cmd.Fill(ds)
            cn.Close()

            For Each row As DataRow In ds.Tables(0).Rows
                For Each col As DataColumn In ds.Tables(0).Columns
                    Console.Write("{0}    ", row(col.Ordinal))
                Next
                Console.WriteLine()
            Next
            Console.ReadLine()

     

    Here is a link to a search page containing lots of information on doing this with ADO, the process transfers over to ADO.NET fine as long as you use a OLEDB connection type. Reading on how to do it with ADO will make it easier to do it in ADO.NET.

    http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=ADO+and+Excel

     

    The other option is to write out SpreadsheetML which is an XML based file that Excel understands. Depends on what version of Excel your users have though. It's a nice way to do it though, just like writing a text file but you need some background in XML to do this. Let me know if you want to go this way and I'll give you more information. Figure if your used to accessing databases the ADO.NET approach would involve the smallest learning curve.

    Thursday, August 16, 2007 2:16 PM