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