The DataAdapter is designed for this sort of task.
If you obtained your DataSet from an OLEDB database, you may have used a
System.Data.Ole db.OledbDataAda pter and the DataAdapter.Fil l() method to get
it.
And you know there is a Update() method, as well, on the DataAdapter.
You can set the UpdateCommand on the DataAdapter to refer to an MS-Excel
file, via the OLEDB provider for MS Excel.
You also need to set the Connection on the UpdateCommand.
And you will need to "create the table" in the MS-Excel datasource before
inserting.
A working example follows.
If you obtained your dataset from a non-OLEDB DataAdapter (say, for example,
the System.Data.Sql Client.DataAdap ter), then you can use the same technique,
but with 2 distinct DataAdapters. The MS-Excel is accessible only via the
OLEDB DataAdapter, as far as I know. So you would do something like:
dataAdapter1.Fi ll(dataSet1);
dataAdapter2.Up date(dataSet1);
--
Dino Chiesa
Microsoft Developer Division
d i n o c h @ O N L I N E . m i c r o s o f t . c o m
// ExtractToExcel. cs
//
// uses a single DataSet and DataAdapter to copy data from one database
(SQL)
// to another (MS Excel, via Jet Driver)
//
// Wed, 01 Oct 2003 19:32
//
namespace Ionic {
public class ExtractToExcel {
public static void Main(string[] args) {
ExtractToExcel e= new ExtractToExcel( );
e.Run();
}
const string ConnStringSourc e= "Provider=sqlol edb;Data
Source=dinoch-1\\vsdotnet;Ini tial Catalog=Northwi nd;Integrated
Security=SSPI;" ;
const string OutputFilename= "ExtractToExcel .xls";
const string ConnStringDest=
"Provider=Micro soft.Jet.OLEDB. 4.0;" +
"Data Source=" + OutputFilename + ";" +
"Extended Properties=\"Ex cel 8.0;HDR=yes;\"" ; //
FIRSTROWHASNAME S=1;READONLY=fa lse\"
private System.Data.Ole Db.OleDbConnect ion ConnSource= null;
private System.Data.Ole Db.OleDbConnect ion ConnDest= null;
const string sqlSelect="SELE CT top 10 ProductId, ProductName,
QuantityPerUnit , UnitPrice, UnitsInStock, GETDATE() as Extracted from
Products order by UnitPrice";
const string sqlInsert="INSE RT INTO Extracto (ProductId, ProductName,
QuantityPerUnit , UnitPrice, UnitsInStock, Extracted) VALUES (@ProductId,
@ProductName, @QuantityPerUni t, @UnitPrice, @UnitsInStock, @Extracted)";
const string sqlCreate = "CREATE TABLE Extracto ( ProductId NUMBER,
ProductName char(40), QuantityPerUnit char(20), UnitPrice NUMBER,
UnitsInStock NUMBER, Extracted DATETIME )";
System.Data.Ole Db.OleDbDataAda pter da ;
System.Data.Dat aSet ds;
public void CreateTable() {
System.Console. WriteLine("Crea ting table in Excel file...");
ConnDest= new System.Data.Ole Db.OleDbConnect ion(ConnStringD est);
System.Data.Ole Db.OleDbCommand cmd= new
System.Data.Ole Db.OleDbCommand (sqlCreate, ConnDest);
try {
ConnDest.Open() ;
cmd.ExecuteNonQ uery();
}
catch (System.Excepti on e2){
if (!e2.Message.Tr im().EndsWith(" already exists."))
System.Console. WriteLine("Erro r while creating. " + e2);
else
System.Console. WriteLine("File and Table (worksheet) already
exist...");
}
finally {
ConnDest.Close( );
}
}
private void Read() {
System.Console. WriteLine("Read ing from SQL...");
ConnSource= new System.Data.Ole Db.OleDbConnect ion(ConnStringS ource);
da= new System.Data.Ole Db.OleDbDataAda pter();
da.SelectComman d= new System.Data.Ole Db.OleDbCommand (sqlSelect);
da.SelectComman d.Connection= ConnSource;
ds= new System.Data.Dat aSet();
da.Fill(ds, "Extracto") ;
//System.Console. WriteLine("data : \n" + ds.GetXml());
}
private void InsertIntoExcel () {
System.Console. WriteLine("Inse rting data into Excel...");
// need to update the row so the DA does the insert...
foreach (System.Data.Da taRow r in ds.Tables[0].Rows) {
r["Extracted"]= System.DateTime .Now; // update the row!
}
da.UpdateComman d= new System.Data.Ole Db.OleDbCommand (sqlInsert);
da.UpdateComman d.Connection= ConnDest;
da.UpdateComman d.Parameters.Ad d("@ProductId ",
System.Data.Ole Db.OleDbType.In teger, 4, "ProductId" );
da.UpdateComman d.Parameters.Ad d("@ProductName ",
System.Data.Ole Db.OleDbType.Va rWChar, 40, "ProductNam e");
da.UpdateComman d.Parameters.Ad d("@QuantityPer Unit",
System.Data.Ole Db.OleDbType.Va rWChar, 20, "QuantityPerUni t");
da.UpdateComman d.Parameters.Ad d("@UnitPrice ",
System.Data.Ole Db.OleDbType.Cu rrency, 8, "UnitPrice" );
da.UpdateComman d.Parameters.Ad d("@UnitsInStoc k",
System.Data.Ole Db.OleDbType.Sm allInt, 2, "UnitsInStock") ;
da.UpdateComman d.Parameters.Ad d("@Extracted ",
System.Data.Ole Db.OleDbType.Da te, 8, "Extracted" );
da.Update(ds, "Extracto") ;
// in the event you want to update a datasource via a different
DataAdapter --
// for example you want to fill from a
System.Data.Sql Client.DataAdap ter and
// then Update via a System.Data.Ole db.OledbDataAda pter -- then you
could define
// two distinct DataAdapters. Fill the DataSet with the first DA,
then Update
// with the second DA.
}
private void OpenResultInExc el() {
System.Console. WriteLine("Star ting MS-Excel...");
System.Diagnost ics.Process.Sta rt(OutputFilena me);
}
public void Run() {
try {
Read();
CreateTable();
InsertIntoExcel ();
OpenResultInExc el();
}
catch (System.Excepti on e1) {
System.Console. WriteLine("Exce ption: " + e1 );
}
}
}
}
"Chris" <ch*********@tb gamericas.com> wrote in message
news:00******** *************** *****@phx.gbl.. .
Could someone please provide me an effective means of
exporting data from a data set (or data grid) to Excel?
