473,238 Members | 1,644 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,238 software developers and data experts.

export dataset to excel in winform

Hi!

I want to export a dataset to an excel file. I found following code on the
net...
( http://www.codeproject.com/csharp/Export.asp )

Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
DataTable table = DATASETNAME.Tables[0];
int ColumnIndex=0;
foreach(Datacolumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow row in table.Row)
{
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex+1,ColumnIndex]=row.Cells[col.ColumnName].Text;

}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
)
I asume that this code will work just fine for me, but how do I include an
applicationclass for Excel?

Do I need to include an excel.dll file for this occasion, and if so where do
i find this dll fie?
---------------
Best regards
- Hans -
---------------
(Have fun programming with ... C#)
Nov 17 '05 #1
4 33407
After further research I think I need the

Microsoft Excel 11.0 Object Library.

Where do I find this
---------------
Best regards
- Hans -
---------------
(Have fun programming with ... C#)
"Hans [DiaGraphIT]" wrote:
Hi!

I want to export a dataset to an excel file. I found following code on the
net...
( http://www.codeproject.com/csharp/Export.asp )

Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
DataTable table = DATASETNAME.Tables[0];
int ColumnIndex=0;
foreach(Datacolumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow row in table.Row)
{
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex+1,ColumnIndex]=row.Cells[col.ColumnName].Text;

}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
)
I asume that this code will work just fine for me, but how do I include an
applicationclass for Excel?

Do I need to include an excel.dll file for this occasion, and if so where do
i find this dll fie?
---------------
Best regards
- Hans -
---------------
(Have fun programming with ... C#)

Nov 17 '05 #2
Hi,

Take a look at this code, I'm using it and works great,
http://support.microsoft.com/default...b;EN-US;316934

Now, I do not need to create an excel table, I have embedded in my .EXE an
empty excel file with the format I need

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Hans [DiaGraphIT]" <ha********@sshf.no> wrote in message
news:89**********************************@microsof t.com...
Hi!

I want to export a dataset to an excel file. I found following code on
the
net...
( http://www.codeproject.com/csharp/Export.asp )

Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
DataTable table = DATASETNAME.Tables[0];
int ColumnIndex=0;
foreach(Datacolumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow row in table.Row)
{
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex+1,ColumnIndex]=row.Cells[col.ColumnName].Text;

}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
)
I asume that this code will work just fine for me, but how do I include an
applicationclass for Excel?

Do I need to include an excel.dll file for this occasion, and if so where
do
i find this dll fie?
---------------
Best regards
- Hans -
---------------
(Have fun programming with ... C#)

Nov 17 '05 #3
Thank you for a good advice.

To write to My first thought was that the worksheet should be generic, and
build along the way, but writing to an already excisting excel file is
captivating idea.

I will consider this. The translation from VB to C# is noe problem but do
you have that code in c# already?

---------------
Best regards
- Hans -
---------------
(Have fun programming with ... C#)
"Ignacio Machin ( .NET/ C# MVP )" wrote:
Hi,

Take a look at this code, I'm using it and works great,
http://support.microsoft.com/default...b;EN-US;316934

Now, I do not need to create an excel table, I have embedded in my .EXE an
empty excel file with the format I need

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Hans [DiaGraphIT]" <ha********@sshf.no> wrote in message
news:89**********************************@microsof t.com...
Hi!

I want to export a dataset to an excel file. I found following code on
the
net...
( http://www.codeproject.com/csharp/Export.asp )

Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
DataTable table = DATASETNAME.Tables[0];
int ColumnIndex=0;
foreach(Datacolumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow row in table.Row)
{
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex+1,ColumnIndex]=row.Cells[col.ColumnName].Text;

}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
)
I asume that this code will work just fine for me, but how do I include an
applicationclass for Excel?

Do I need to include an excel.dll file for this occasion, and if so where
do
i find this dll fie?
---------------
Best regards
- Hans -
---------------
(Have fun programming with ... C#)


Nov 17 '05 #4
Hi,

Hey, what about the lazyness? :)
I had to rewrite it myself too ;)

here is the piece of code I'm using to read from excel and writing to
access, it should be easy to modify to write in excel, just change the query

As I'm lazy too this morning I did not edited nothing, it goes straight from
my .cs

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\temp\crash data districtwide-2002.xls;Extended Properties=""Excel
8.0;HDR=YES;""";

string srcQuery = "Select * from [Sheet1$]";

OleDbConnection srcConn = new OleDbConnection( srcConnString);

srcConn.Open();

OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);

// OleDbDataAdapter da = new OleDbDataAdapter( srcQuery, srcConn);

// DataSet ds = new DataSet();

// da.Fill( ds);

OleDbDataReader reader = objCmdSelect.ExecuteReader(
CommandBehavior.CloseConnection);

//Target

string targetConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\temp\100test.mdb"; //;User Id=admin;Password=;"

OleDbConnection targetConn = new OleDbConnection( targetConnString);

targetConn.Open();

OleDbCommand targetCmd = new OleDbCommand();

targetCmd.Connection = targetConn;

targetCmd.CommandType = CommandType.Text;

targetCmd.CommandText = "INSERT INTO AllData " +

"( IDNUMBER , MILEPOST , KEYNUMBER , DATE , DAYOFWEEK , TIME , NUMBERCARS ,
NUMBERTRKS , " +

"ACCTYPE , SEVERITY , TRCNTRL03 , LITCOND04 , CHARCTR05 , SURFACE06 ,
WEATHER07 , CONTRIB19,"+

"CONTRIB20 , CONTRIB21 , CONTRIB22 , DIRECTN23 , DIRECTN24 , DIRECTVEH3 ,
DIRECTVEH4 , DESCRIPTN , SYMBOL ) " +

"VALUES ( "+

"@IDNUMBER , @MILEPOST , @KEYNUMBER , @DATE , @DAYOFWEEK , @TIME ,
@NUMBERCARS , @NUMBERTRKS , @ACCTYPE , " +

"@SEVERITY , @TRCNTRL03 , @LITCOND04 , @CHARCTR05 , @SURFACE06 , @WEATHER07
, @CONTRIB19 , @CONTRIB20 , "+

"@CONTRIB21 , @CONTRIB22 , @DIRECTN23 , @DIRECTN24 , @DIRECTVEH3 ,
@DIRECTVEH4 , @DESCRIPTN , @SYMBOL " +

")";
while( reader.Read() )

{

if ( reader[0] == System.DBNull.Value )

continue;

targetCmd.Parameters.Add( "@IDNUMBER", OleDbType.VarChar).Value = reader[0]
;

targetCmd.Parameters.Add( "@MILEPOST", OleDbType.Integer).Value = reader[8]
;

targetCmd.Parameters.Add( "@KEYNUMBER", OleDbType.Integer).Value =
1;//reader[2] ;

targetCmd.Parameters.Add( "@DATE", OleDbType.DBTimeStamp).Value = reader[1]
;

targetCmd.Parameters.Add( "@DAYOFWEEK", OleDbType.VarChar).Value =
((DateTime)reader[3]).DayOfWeek.ToString().Substring(0,2);

targetCmd.Parameters.Add( "@TIME", OleDbType.DBTimeStamp).Value = reader[2]
;

targetCmd.Parameters.Add( "@NUMBERCARS", OleDbType.Integer).Value =
reader[78] ;

targetCmd.Parameters.Add( "@NUMBERTRKS", OleDbType.Integer).Value =
0;//reader[7] ;

targetCmd.Parameters.Add( "@ACCTYPE", OleDbType.VarChar).Value = reader[8] ;
**********************************

targetCmd.Parameters.Add( "@SEVERITY", OleDbType.VarChar).Value = reader[9]
;

targetCmd.Parameters.Add( "@TRCNTRL03", OleDbType.Integer).Value =
reader[10] ;

targetCmd.Parameters.Add( "@LITCOND04", OleDbType.Integer).Value =
reader[11] ;

targetCmd.Parameters.Add( "@CHARCTR05", OleDbType.Integer).Value =
reader[12] ;

targetCmd.Parameters.Add( "@SURFACE06", OleDbType.Integer).Value =
reader[13] ;

targetCmd.Parameters.Add( "@WEATHER07", OleDbType.Integer).Value =
reader[14] ;

targetCmd.Parameters.Add( "@CONTRIB19", OleDbType.Integer).Value =
reader[15] ;

targetCmd.Parameters.Add( "@CONTRIB20", OleDbType.Integer).Value =
reader[16] ;

targetCmd.Parameters.Add( "@CONTRIB21", OleDbType.Integer).Value =
reader[17] ;

targetCmd.Parameters.Add( "@CONTRIB22", OleDbType.Integer).Value =
reader[18] ;

targetCmd.Parameters.Add( "@DIRECTN23", OleDbType.Integer).Value =
reader[19] ;

targetCmd.Parameters.Add( "@DIRECTN24", OleDbType.Integer).Value =
reader[20] ;

targetCmd.Parameters.Add( "@DIRECTVEH3", OleDbType.Integer).Value =
reader[21] ;

targetCmd.Parameters.Add( "@DIRECTVEH4", OleDbType.Integer).Value =
reader[22] ;

targetCmd.Parameters.Add( "@DESCRIPTN", OleDbType.VarChar).Value =
reader[23] ;

targetCmd.Parameters.Add( "@SYMBOL", OleDbType.VarChar).Value = reader[24] ;

targetCmd.ExecuteNonQuery();

}

targetConn.Close();

reader.Close();

srcConn.Close();

}

catch(Exception e1)

{

MessageBox.Show( e1.Message);

}
"Hans [DiaGraphIT]" <ha********@sshf.no> wrote in message
news:18**********************************@microsof t.com...
Thank you for a good advice.

To write to My first thought was that the worksheet should be generic, and
build along the way, but writing to an already excisting excel file is
captivating idea.

I will consider this. The translation from VB to C# is noe problem but do
you have that code in c# already?

---------------
Best regards
- Hans -
---------------
(Have fun programming with ... C#)
"Ignacio Machin ( .NET/ C# MVP )" wrote:
Hi,

Take a look at this code, I'm using it and works great,
http://support.microsoft.com/default...b;EN-US;316934

Now, I do not need to create an excel table, I have embedded in my .EXE
an
empty excel file with the format I need

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Hans [DiaGraphIT]" <ha********@sshf.no> wrote in message
news:89**********************************@microsof t.com...
> Hi!
>
> I want to export a dataset to an excel file. I found following code on
> the
> net...
> ( http://www.codeproject.com/csharp/Export.asp )
>
> Excel.ApplicationClass excel = new ApplicationClass();
>
> excel.Application.Workbooks.Add(true);
> DataTable table = DATASETNAME.Tables[0];
> int ColumnIndex=0;
> foreach(Datacolumn col in table.Columns)
> {
> ColumnIndex++;
> excel.Cells[1,ColumnIndex]=col.ColumnName;
> }
> int rowIndex=0;
> foreach(DataRow row in table.Row)
> {
> rowIndex++;
> ColumnIndex=0;
> foreach(DataColumn col in table.Columns)
> {
> ColumnIndex++;
>
> excel.Cells[rowIndex+1,ColumnIndex]=row.Cells[col.ColumnName].Text;
>
> }
> }
> excel.Visible = true;
> Worksheet worksheet = (Worksheet)excel.ActiveSheet;
> worksheet.Activate();
> )
>
>
> I asume that this code will work just fine for me, but how do I include
> an
> applicationclass for Excel?
>
> Do I need to include an excel.dll file for this occasion, and if so
> where
> do
> i find this dll fie?
>
>
> ---------------
> Best regards
> - Hans -
> ---------------
> (Have fun programming with ... C#)


Nov 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Maria L. | last post by:
Hi, I need to export the content of a DataGrid (in Windows application in C#), into an Excel spreadsheet. Anyone knows how to do this? Any code snippets would help! thanks a lot, Maria
1
by: Kevin Blakeley | last post by:
I know this was just posted but I did not want this message to get lost in the other thread as it's slightly different. Yes I want to export my dataset to excel for my clients, but I don't want...
1
by: ad | last post by:
I used the code below to export a table in a dataset to Excel. It can export only on table at a time. Can export more than on table in a dataset to Excel public static void...
4
by: John Z. | last post by:
I have read numerous articles and postings on various approaches to exporting a DataSet to excel while avoiding the use of automation. I found the most performant approach to be assigning a...
3
by: Scott M. Lyon | last post by:
I'm trying to figure out a way to export data (actually the result of a Stored Procedure call from SQL Server) into a specified Excel spreadsheet format. Currently, I have the data read into a...
3
by: Sachin Salgarkar | last post by:
I have a DataSet that I need to export to Excel. The dataset has multiple tables. I need a way to export the complete dataset to a single Excel Workbook with sheets for each table in the dataset....
1
by: DennisBetten | last post by:
First of all, I need to give some credit to Mahesh Chand for providing me with an excellent basis to export data to excel. What does this code do: As the title says, this code is capable of...
4
by: =?Utf-8?B?anAybXNmdA==?= | last post by:
I've got a DataSet that I save as XML using the DataSet DataTable's WriteXml method. If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can not reopen the file in my...
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.