By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,002 Members | 2,137 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,002 IT Pros & Developers. It's quick & easy.

Exporting Data Set to Excel

P: n/a
Could someone please provide me an effective means of
exporting data from a data set (or data grid) to Excel?
Jul 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
-----Original Message-----
Could someone please provide me an effective means of
exporting data from a data set (or data grid) to Excel?
.

Jul 21 '05 #2

P: n/a
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?

Jul 21 '05 #3

P: n/a
This is very helpful only thing is how to I add to
multiple spreadsheets and name those tabs?
-----Original Message-----
The DataAdapter is designed for this sort of task.
If you obtained your DataSet from an OLEDB database, you may have used aSystem.Data.Oledb.OledbDataAdapter and the DataAdapter.Fill() method to getit.
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-Excelfile, 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 beforeinserting.

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 theOLEDB 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;IntegratedSecurity=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 fromProducts 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) alreadyexist...");
}
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 differentDataAdapter --
// for example you want to fill from a
System.Data.SqlClient.DataAdapter and
// then Update via a System.Data.Oledb.OledbDataAdapter -- then youcould 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?

.

Jul 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.