How to export data from ASP. NET to Excel using DataSet

Copy codeThe Code is as follows:
/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 ();