Use oledb in. Net to export dataset data to an Excel file

Use oledb in. Net to export dataset data to an Excel file
Function Description:
// DT is the data source (data table)
// Excelfilename is the excle file to be exported.
// Modelfile is a template file, which is consistent with the table in the data source. Otherwise, the Data fails to be exported.
// In the modelfile file, you must have a table consistent with DT. tablename and the fields must be consistent.
// Note: If modelfile is not used, you can use a blank Excel file. However, you need to remove the comment for creating the table below and let oledb create a blank table on its own.

Public static string tabletoexcelfile (datatable DT, string excelfilename, string modelfile)
{
File. Copy (modelfile, excelfilename); // copy an empty file for Data Writing.

If (file. exists (excelfilename) = false)
{
Return "0 | An error occurred while creating the temporary file. Contact the system administrator! ";
}

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 = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + excelfilename + "; extended properties = Excel 8.0 ;";

// Generate the table creation script
// ---- Sb. append ("Drop table" + dt. tablename );

/*
SB. append ("create table ");
SB. append (Dt. tablename + "(");
For (INT I = 0; I <Cols; 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 ));
}

*/

// Return sb. tostring ();

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;
}

SB. Remove (0, SB. Length );

SB. append ("insert ");
SB. append (Dt. tablename + "(");

For (INT I = 0; I <Cols; I ++)
{
If (I <Cols-1)
SB. append (Dt. Columns [I]. columnname + ",");
Else
SB. append (Dt. Columns [I]. columnname + ") values (");
}

For (INT I = 0; I <Cols; I ++)
{
If (I <Cols-1)
SB. append ("@" + dt. Columns [I]. columnname + ",");
Else
SB. append ("@" + dt. Columns [I]. columnname + ")");
}

// Create the INSERT command

Objcmd. commandtext = sb. tostring ();
Oledbparametercollection Param = objcmd. parameters;

For (INT I = 0; I <Cols; 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. Count; I ++)
{
Param [I]. value = row [I];
}

Objcmd. executenonquery ();
}

Return "data has been imported into excel ";
}
Add the following references
Using system;
Using system. IO;
Using system. text;
Using system. Data;
Using system. Data. oledb;