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 said:
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 ) said:
Hi,
Take a look at this code, I'm using it and works great,
http://support.microsoft.com/default.aspx?scid=kb;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 said:
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?
