How to save DataSet into Excel files

[yaya via DotNetMonster.com]

Hi, I'm using OleDb to read an excel files and display the data in a datagrid (using dataset as data source), after modifying the data on datagird, how can I save the changes back into Excel files.

Thankz....

 

[Nicholas Paldino [.NET/C# MVP]]

yaya,

There are a few ways that you can go about doing this. The most obvious
way I would think of doing this would be to use the OLE DB provider for
Excel and try to use an OleDbDataAdapter, calling Update.

If you are using a version of Excel that supports XML, then you can just
take the dataset that the grid is bound to and then save the XML contents.

If that doesn't work, then you could take the data set, and write out to
a CSV file (cycling through the rows and columns). If you want the native
Excel format, then you will have to access Excel through automation and
populate the sheet yourself.

Hope this helps.

 

[yaya via DotNetMonster.com]

Yes, just as you mentioned, I'm trying to call OleDbDataAdapter's Update but I get this error
---------------------------------------------------------------------
System.InvalidOperationException: Update unable to find TableMapping['Table'] or DataTable 'Table'.
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at TPApplication.frm_main.ReadExcelFile(String fileName) in c:\...
---------------------------------------------------------------------

Here is my code to read the data from Excel
---------------------------------------------------------------------using System.Data.OleDb;

string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\Test.xls;Extended Properties=Excel 8.0";

OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] ";
myConn.Open();

OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom , myConn);
DataSet inputDataSet = new DataSet();
myCommand.Fill (inputDataSet, "[Sheet1$]");
myConn.Close();

dtg_input.DataMember= "[Sheet1$]";
dtg_input.DataSource = inputDataSet;

// Some changes made in the datagrid, and I wana save the changes

**************Error*********************
myCommand.Update(inputDataSet);
****************************************

Thankz again...

 

[yaya via DotNetMonster.com]

Is that any other way to save the Dataset into Excel files?
Thankz...