Ado.net exports data to excel and provides download

Ado.net exports data to excel and provides download

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 }