471,592 Members | 1,230 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,592 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 33214
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Kevin Blakeley | last post: by
4 posts views Thread by John Z. | last post: by
3 posts views Thread by Sachin Salgarkar | last post: by
4 posts views Thread by =?Utf-8?B?anAybXNmdA==?= | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

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.