Export data from DataSet to Excel

// Read temporary files
GYYW. DA. Common. Base_SqlDataBase daBZDM = new GYYW. DA. Common. Base_SqlDataBase ();
DataSet dsBZDM = daBZDM. GetDataSetBySql ("select QCDM, MC, GG from WG_BZDM where QCDM like '200 '");

// Use the Data in the virtual directory as the temporary file directory.
String urlPath = HttpContext. Current. Request. ApplicationPath + "/Data /";
String physicPath = HttpContext. Current. Server. MapPath (urlPath );
// String fileName = Guid. NewGuid () + ". Xls ";
String fileName = "DownLoad. Xls ";
String connString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + physicPath + fileName + "; Extended Properties = Excel 8.0 ;";

OleDbConnection objConn = new OleDbConnection (connString );
OleDbCommand objCmd = new OleDbCommand ();
ObjCmd. Connection = objConn;
ObjCmd. Connection. Open ();

// Create a table structure
ObjCmd. CommandText = @ "create table Sheet1 (device code varchar, name varchar, specification varchar )";
ObjCmd. ExecuteNonQuery ();

// Create the INSERT Command

ObjCmd. CommandText = "insert into Sheet1 (device code, name, specification) VALUES (@ QCDM, @ MC, @ GG )";

ObjCmd. Parameters. Clear ();

ObjCmd. Parameters. Add (new OleDbParameter ("@ QCDM", OleDbType. VarChar ));
ObjCmd. Parameters. Add (new OleDbParameter ("@ MC", OleDbType. VarChar ));
ObjCmd. Parameters. Add (new OleDbParameter ("@ GG", OleDbType. VarChar ));
 
// Traverse DataSet and insert data into the new Excel File
Foreach (DataRow row in dsBZDM. Tables [0]. Rows)
{
For (int I = 0; I <objCmd. Parameters. Count; I ++)
{
ObjCmd. Parameters [I]. Value = row [I];
}
ObjCmd. ExecuteNonQuery ();
}
ObjCmd. Connection. Close ();

// Download
// Clear temporary files
HttpResponse response = HttpContext. Current. Response;
Response. Clear ();
// Prepare for output
Response. WriteFile (urlPath + fileName );
String httpHeader = "attachment; filename = KCMX. Xls ";
Response. AppendHeader ("Content-Disposition", httpHeader );
Response. Flush ();
// Clear temporary files after output
String strSaveDir = "../Data /";
String strFile = Server. MapPath (strSaveDir + fileName). ToString ();
// String sss = urlPath + fileName;
System. IO. File. Delete (strFile); // Delete a temporary File
Response. End ();