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=Micr osoft.Jet.OLEDB .4.0;Data
Source=C:\temp\ crash data districtwide-2002.xls;Extend ed Properties=""Ex cel
8.0;HDR=YES;""" ;
string srcQuery = "Select * from [Sheet1$]";
OleDbConnection srcConn = new OleDbConnection ( srcConnString);
srcConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);
// OleDbDataAdapte r da = new OleDbDataAdapte r( srcQuery, srcConn);
// DataSet ds = new DataSet();
// da.Fill( ds);
OleDbDataReader reader = objCmdSelect.Ex ecuteReader(
CommandBehavior .CloseConnectio n);
//Target
string targetConnStrin g = @"Provider=Micr osoft.Jet.OLEDB .4.0;Data
Source=c:\temp\ 100test.mdb"; //;User Id=admin;Passwo rd=;"
OleDbConnection targetConn = new OleDbConnection ( targetConnStrin g);
targetConn.Open ();
OleDbCommand targetCmd = new OleDbCommand();
targetCmd.Conne ction = targetConn;
targetCmd.Comma ndType = CommandType.Tex t;
targetCmd.Comma ndText = "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.V alue )
continue;
targetCmd.Param eters.Add( "@IDNUMBER" , OleDbType.VarCh ar).Value = reader[0]
;
targetCmd.Param eters.Add( "@MILEPOST" , OleDbType.Integ er).Value = reader[8]
;
targetCmd.Param eters.Add( "@KEYNUMBER ", OleDbType.Integ er).Value =
1;//reader[2] ;
targetCmd.Param eters.Add( "@DATE", OleDbType.DBTim eStamp).Value = reader[1]
;
targetCmd.Param eters.Add( "@DAYOFWEEK ", OleDbType.VarCh ar).Value =
((DateTime)read er[3]).DayOfWeek.ToS tring().Substri ng(0,2);
targetCmd.Param eters.Add( "@TIME", OleDbType.DBTim eStamp).Value = reader[2]
;
targetCmd.Param eters.Add( "@NUMBERCAR S", OleDbType.Integ er).Value =
reader[78] ;
targetCmd.Param eters.Add( "@NUMBERTRK S", OleDbType.Integ er).Value =
0;//reader[7] ;
targetCmd.Param eters.Add( "@ACCTYPE", OleDbType.VarCh ar).Value = reader[8] ;
*************** *************** ****
targetCmd.Param eters.Add( "@SEVERITY" , OleDbType.VarCh ar).Value = reader[9]
;
targetCmd.Param eters.Add( "@TRCNTRL03 ", OleDbType.Integ er).Value =
reader[10] ;
targetCmd.Param eters.Add( "@LITCOND04 ", OleDbType.Integ er).Value =
reader[11] ;
targetCmd.Param eters.Add( "@CHARCTR05 ", OleDbType.Integ er).Value =
reader[12] ;
targetCmd.Param eters.Add( "@SURFACE06 ", OleDbType.Integ er).Value =
reader[13] ;
targetCmd.Param eters.Add( "@WEATHER07 ", OleDbType.Integ er).Value =
reader[14] ;
targetCmd.Param eters.Add( "@CONTRIB19 ", OleDbType.Integ er).Value =
reader[15] ;
targetCmd.Param eters.Add( "@CONTRIB20 ", OleDbType.Integ er).Value =
reader[16] ;
targetCmd.Param eters.Add( "@CONTRIB21 ", OleDbType.Integ er).Value =
reader[17] ;
targetCmd.Param eters.Add( "@CONTRIB22 ", OleDbType.Integ er).Value =
reader[18] ;
targetCmd.Param eters.Add( "@DIRECTN23 ", OleDbType.Integ er).Value =
reader[19] ;
targetCmd.Param eters.Add( "@DIRECTN24 ", OleDbType.Integ er).Value =
reader[20] ;
targetCmd.Param eters.Add( "@DIRECTVEH 3", OleDbType.Integ er).Value =
reader[21] ;
targetCmd.Param eters.Add( "@DIRECTVEH 4", OleDbType.Integ er).Value =
reader[22] ;
targetCmd.Param eters.Add( "@DESCRIPTN ", OleDbType.VarCh ar).Value =
reader[23] ;
targetCmd.Param eters.Add( "@SYMBOL", OleDbType.VarCh ar).Value = reader[24] ;
targetCmd.Execu teNonQuery();
}
targetConn.Clos e();
reader.Close();
srcConn.Close() ;
}
catch(Exception e1)
{
MessageBox.Show ( e1.Message);
}
"Hans [DiaGraphIT]" <ha********@ssh f.no> wrote in message
news:18******** *************** ***********@mic rosoft.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********@ssh f.no> wrote in message
news:89******** *************** ***********@mic rosoft.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.Applicati onClass excel = new ApplicationClas s();
>
> excel.Applicati on.Workbooks.Ad d(true);
> DataTable table = DATASETNAME.Tab les[0];
> int ColumnIndex=0;
> foreach(Datacol umn col in table.Columns)
> {
> ColumnIndex++;
> excel.Cells[1,ColumnIndex]=col.ColumnName ;
> }
> int rowIndex=0;
> foreach(DataRow row in table.Row)
> {
> rowIndex++;
> ColumnIndex=0;
> foreach(DataCol umn col in table.Columns)
> {
> ColumnIndex++;
>
> excel.Cells[rowIndex+1,Colu mnIndex]=row.Cells[col.ColumnName].Text;
>
> }
> }
> excel.Visible = true;
> Worksheet worksheet = (Worksheet)exce l.ActiveSheet;
> worksheet.Activ ate();
> )
>
>
> I asume that this code will work just fine for me, but how do I include
> an
> applicationclas s 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#)
