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.Oledb.OledbDataAdapter and the DataAdapter.Fill() 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.SqlClient.DataAdapter), 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.Fill(dataSet1);
dataAdapter2.Update(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 ConnStringSource= "Provider=sqloledb;Data
Source=dinoch-1\\vsdotnet;Initial Catalog=Northwind;Integrated
Security=SSPI;" ;
const string OutputFilename= "ExtractToExcel.xls";
const string ConnStringDest=
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + OutputFilename + ";" +
"Extended Properties=\"Excel 8.0;HDR=yes;\""; //
FIRSTROWHASNAMES=1;READONLY=false\"
private System.Data.OleDb.OleDbConnection ConnSource= null;
private System.Data.OleDb.OleDbConnection ConnDest= null;
const string sqlSelect="SELECT top 10 ProductId, ProductName,
QuantityPerUnit, UnitPrice, UnitsInStock, GETDATE() as Extracted from
Products order by UnitPrice";
const string sqlInsert="INSERT INTO Extracto (ProductId, ProductName,
QuantityPerUnit, UnitPrice, UnitsInStock, Extracted) VALUES (@ProductId,
@ProductName, @QuantityPerUnit, @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.OleDb.OleDbDataAdapter da ;
System.Data.DataSet ds;
public void CreateTable() {
System.Console.WriteLine("Creating table in Excel file...");
ConnDest= new System.Data.OleDb.OleDbConnection(ConnStringDest);
System.Data.OleDb.OleDbCommand cmd= new
System.Data.OleDb.OleDbCommand(sqlCreate, ConnDest);
try {
ConnDest.Open();
cmd.ExecuteNonQuery();
}
catch (System.Exception e2){
if (!e2.Message.Trim().EndsWith("already exists."))
System.Console.WriteLine("Error while creating. " + e2);
else
System.Console.WriteLine("File and Table (worksheet) already
exist...");
}
finally {
ConnDest.Close();
}
}
private void Read() {
System.Console.WriteLine("Reading from SQL...");
ConnSource= new System.Data.OleDb.OleDbConnection(ConnStringSource );
da= new System.Data.OleDb.OleDbDataAdapter();
da.SelectCommand= new System.Data.OleDb.OleDbCommand(sqlSelect);
da.SelectCommand.Connection= ConnSource;
ds= new System.Data.DataSet();
da.Fill(ds, "Extracto");
//System.Console.WriteLine("data: \n" + ds.GetXml());
}
private void InsertIntoExcel() {
System.Console.WriteLine("Inserting data into Excel...");
// need to update the row so the DA does the insert...
foreach (System.Data.DataRow r in ds.Tables[0].Rows) {
r["Extracted"]= System.DateTime.Now; // update the row!
}
da.UpdateCommand= new System.Data.OleDb.OleDbCommand(sqlInsert);
da.UpdateCommand.Connection= ConnDest;
da.UpdateCommand.Parameters.Add("@ProductId",
System.Data.OleDb.OleDbType.Integer, 4, "ProductId");
da.UpdateCommand.Parameters.Add("@ProductName",
System.Data.OleDb.OleDbType.VarWChar, 40, "ProductName");
da.UpdateCommand.Parameters.Add("@QuantityPerUnit" ,
System.Data.OleDb.OleDbType.VarWChar, 20, "QuantityPerUnit");
da.UpdateCommand.Parameters.Add("@UnitPrice",
System.Data.OleDb.OleDbType.Currency, 8, "UnitPrice");
da.UpdateCommand.Parameters.Add("@UnitsInStock",
System.Data.OleDb.OleDbType.SmallInt, 2, "UnitsInStock");
da.UpdateCommand.Parameters.Add("@Extracted",
System.Data.OleDb.OleDbType.Date, 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.SqlClient.DataAdapter and
// then Update via a System.Data.Oledb.OledbDataAdapter -- then you
could define
// two distinct DataAdapters. Fill the DataSet with the first DA,
then Update
// with the second DA.
}
private void OpenResultInExcel() {
System.Console.WriteLine("Starting MS-Excel...");
System.Diagnostics.Process.Start(OutputFilename);
}
public void Run() {
try {
Read();
CreateTable();
InsertIntoExcel();
OpenResultInExcel();
}
catch (System.Exception e1) {
System.Console.WriteLine("Exception: " + e1 );
}
}
}
}
"Chris" <ch*********@tbgamericas.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?
