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#)

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#)

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]" <(E-Mail Removed)> wrote in message
news:89CBF363-6655-417D-B698-(E-Mail Removed)...
> 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#)

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]" <(E-Mail Removed)> wrote in message
> news:89CBF363-6655-417D-B698-(E-Mail Removed)...
> > 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#)
>
>
>

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]" <(E-Mail Removed)> wrote in message
news:188DBCFA-0ED8-4DB5-8D48-(E-Mail Removed)...
> 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]" <(E-Mail Removed)> wrote in message
>> news:89CBF363-6655-417D-B698-(E-Mail Removed)...
>> > 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#)
>>
>>
>>

[pfgrid]

You could use our grid which has full excel-export included. Excel must not be installed on your computer to export the data in Excel-format:
Excel export PFGrid.NET

HTH and kind regards,
Matthias

Originally Posted by =?Utf-8?B?SGFucyBbRGlhR3JhcGhJVF0=?=

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#)