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

export dataset to excel in winform

P: n/a
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
Share this Question
Share on Google+
4 Replies


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

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

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

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