Asp. net method of exporting to Excel with DataSet _ Practical Tips

Copy Code code 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 ' 2% '");

The data in the virtual directory is also used 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 (Equipment code varchar, name varchar, specification varchar)";

Objcmd.executenonquery ();

Create command for Insert action

objCmd.CommandText = "INSERT into Sheet1 (equipment 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));

Traversing the dataset inserts data into a 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 ();

Provide 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 ();

Clears temporary files when output is complete

String Strsavedir = ".. /data/";

String strfile = Server.MapPath (Strsavedir + fileName). ToString ();

String sss = URLPath + fileName;

System.IO.File.Delete (strfile);//Delete temporary files

Response. End ();