See the other 2 documents at the same time:
Http://community.csdn.net/Expert/topic/4760/4760677.xml? Temp =. 7889063.
Http://community.csdn.net/Expert/topic/4787/4787630.xml? Temp =. 7903253.
========================================================== ======================
Public String datatabletoexcel (datatable DT, string excelpath)
{
If (Dt = NULL)
{
Return "datatable cannot be blank ";
}
Int rows = DT. Rows. count;
Int Cols = DT. Columns. count;
Stringbuilder Sb;
String connstring;
If (rows = 0)
{
Return "no data ";
}
SB = new stringbuilder ();
Connstring = string. Format (connectionstring, excelpath );
// Generate the table creation script
SB. append ("create table ");
SB. append (Dt. tablename + "(");
For (INT I = 0; I
{
If (I <Cols-1)
SB. append (string. Format ("{0} varchar,", DT. Columns [I]. columnname ));
Else
SB. append (string. Format ("{0} varchar)", DT. Columns [I]. columnname ));
}
Using (oledbconnection objconn = new oledbconnection (connstring ))
{
Oledbcommand objcmd = new oledbcommand ();
Objcmd. Connection = objconn;
Objcmd. commandtext = sb. tostring ();
Try
{
Objconn. open ();
Objcmd. executenonquery ();
}
Catch (exception E)
{
Return "failed to create table in Excel, error message:" + E. message;
}
Generate the insert data script # region generate the insert data script
SB. Remove (0, SB. Length );
SB. append ("insert ");
SB. append (Dt. tablename + "(");
For (INT I = 0; I
{
If (I <Cols-1)
SB. append (Dt. Columns [I]. columnname + ",");
Else
SB. append (Dt. Columns [I]. columnname + ") values (");
}
For (INT I = 0; I
{
If (I <Cols-1)
SB. append ("@" + dt. Columns [I]. columnname + ",");
Else
SB. append ("@" + dt. Columns [I]. columnname + ")");
}
# Endregion
// Create the INSERT command
Objcmd. commandtext = sb. tostring ();
Oledbparametercollection Param = objcmd. parameters;
For (INT I = 0; I
{
Param. Add (New oledbparameter ("@" + dt. Columns [I]. columnname, oledbtype. varchar ));
}
// Traverse datatable to insert data into the new Excel File
Foreach (datarow row in DT. Rows)
{
For (INT I = 0; I {
Param [I]. value = row [I];
}
Objcmd. executenonquery ();
}
Return "data has been imported into excel ";
} // End using
}