ASP.NET用DataSet导出到Excel的方法

 /读取临时文件

   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 '02%'");

   //同时将虚拟目录下的data作为临时文件目录。

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

   //建立表结构

   objcmd.commandtext = @"create table sheet1(器材代码 varchar,名称 varchar, 规格 varchar)";

   objcmd.executenonquery();

   //建立插入动作的command
 

   objcmd.commandtext = "insert into sheet1(器材代码, 名称,规格) 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));
   

   //遍历dataset将数据插入新建的excel文件中

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

  
   //提供下载

   //清除临时文件

   httpresponse response = httpcontext.current.response;

   response.clear();

   //为输出作准备

   response.writefile(urlpath + filename);

   string httpheader="attachment;filename=kcmx.xls";

   response.appendheader("content-disposition", httpheader);

   response.flush();

   //输出完毕后清除临时文件

   string strsavedir = "../data/";

   string strfile = server.mappath(strsavedir + filename).tostring();

   //string sss = urlpath + filename;

   system.io.file.delete(strfile);//删除临时文件

   response.end();