C
Chris
Could someone please provide me an effective means of
exporting data from a data set (or data grid) to Excel?
exporting data from a data set (or data grid) to Excel?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
MTchar
You might want to try ReportDepot for .NET
(www.semurg.ca) Since v. 02.00.00 it can sav report
output to Excel file
HTH
(www.semurg.ca) Since v. 02.00.00 it can sav report
output to Excel file
HTH
D
Dino Chiesa [Microsoft]
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 );
}
}
}
}
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 );
}
}
}
}
C
Chris
This is very helpful only thing is how to I add to
multiple spreadsheets and name those tabs?
multiple spreadsheets and name those tabs?
DataAdapter.Fill() method to get-----Original Message-----
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
System.Data.Oledb.OledbDataAdapter -- then youit.
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