473,320 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Exporting Data Set to Excel

Could someone please provide me an effective means of
exporting data from a data set (or data grid) to Excel?
Jul 21 '05 #1
3 7958
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works...
4
by: D | last post by:
I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc. Only one problem: one of the fields is a "SchoolYear"...
2
by: G | last post by:
When I export data from access to excel by with "export" or "Analyze with" I seem to loose parts of some fields (long text strings). Is there a way to export it all to excel? Thanks G
2
by: pmud | last post by:
Hi, I am exporting data from an EDITABLE DATA GRID EXCEL. But the 1st column in data grid is Edit Column. I want to display all columns in Excel except for the Edit column. The following...
3
by: Chris | last post by:
Could someone please provide me an effective means of exporting data from a data set (or data grid) to Excel?
2
by: bienwell | last post by:
Hi, I have a question about exporting data from datagrid control into Excel file in ASP.NET. On my Web page, I have a linkbutton "Export data". This link will call a Sub Function to perform...
2
by: Snozz | last post by:
The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...
1
by: 333sridhar333 | last post by:
Hi, I am having a problem in exporting a data from jsp to excel. I am getting the values from a servlet and populating it to a JSP. And form there i export them to Excel. The functionality...
2
by: 333sridhar333 | last post by:
Hi, I am having a problem in exporting a data from jsp to excel. I am getting the values from a servlet and populating it to a JSP. And form there i export them to Excel. The functionality...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.