asp.net里导出excel表方法汇总
1、由dataset生成
public void createexcel(dataset ds,string typeid,string filename)
{
httpresponse resp;
resp = page.response;
resp.contentencoding = system.text.encoding.getencoding("gb2312");
resp.appendheader("content-disposition", "attachment;filename=" + filename);
string colheaders= "", ls_item="";
int i=0;
//定义表对象与行对像，同时用dataset对其值进行初始化
datatable dt=ds.tables;
datarow.caption.tostring()+"/t";
colheaders +=dt.columns.caption.tostring() +"/n";
//向http输出流中写入取得的数据信息
resp.write(colheaders);
//逐行处理数据
foreach(datarow row in myrow)
{
//在当前行中，逐列获得数据，数据之间以/t分割，结束时加回车符/n
for(i=0;i ls_item +=row.tostring() + "/t";
ls_item += row.tostring() +"/n";
//当前行数据写入http输出流，并且置空ls_item以便下行数据
resp.write(ls_item);
ls_item="";
}
}
else
{
if(typeid=="2")
{
//从dataset中直接导出xml数据并且写到http输出流中
resp.write(ds.getxml());
}
}
//写缓冲区中的数据到http头文件中
resp.end();
}
2、使用微软的c＋＋写的activex控件：http://download.microsoft.com/download/officexpdev/sample/1.0/win98mexp/en-us/dsoframerctl.exe
3、由datagrid生成：
public void toexcel(system.web.ui.control ctl)
{
httpcontext.current.response.appendheader("content-disposition","attachment;filename=excel.xls");
httpcontext.current.response.charset ="utf-8";
httpcontext.current.response.contentencoding =system.text.encoding.default;
httpcontext.current.response.contenttype ="application/ms-excel";//image/jpeg;text/html;image/gif;vnd.ms-excel/msword
ctl.page.enableviewstate =false;
system.io.stringwriter tw = new system.io.stringwriter() ;
system.web.ui.htmltextwriter hw = new system.web.ui.htmltextwriter (tw);
ctl.rendercontrol(hw);
httpcontext.current.response.write(tw.tostring());
httpcontext.current.response.end();
}
用法：toexcel(datagrid1);
4、这个用dataview ，代码好长
public void outputexcel(dataview dv,string str)
{
//
// todo: 在此处添加构造函数逻辑
//
//dv为要输出到excel的数据，str为标题名称
gc.collect();
application excel;// = new application();
int rowindex=4;
int colindex=1;
_workbook xbk;
_worksheet xst;
excel= new applicationclass();
xbk = excel.workbooks.add(true);
xst = (_worksheet)xbk.activesheet;
//
//取得标题
//
foreach(datacolumn col in dv.table.columns)
{
colindex++;
excel.cells = col.columnname;
xst.get_range(excel.cells,excel.cells).horizontalalignment = xlvalign.xlvaligncenter;//设置标题格式为居中对齐
}
//
//取得表格中的数据
//
foreach(datarowview row in dv)
{
rowindex ++;
colindex = 1;
foreach(datacolumn col in dv.table.columns)
{
colindex ++;
if(col.datatype == system.type.gettype("system.datetime"))
{
excel.cells = (convert.todatetime(row.tostring())).tostring("yyyy-mm-dd");
xst.get_range(excel.cells,excel.cells).horizontalalignment = xlvalign.xlvaligncenter;//设置日期型的字段格式为居中对齐
}
else
if(col.datatype == system.type.gettype("system.string"))
{
excel.cells = """+row.tostring();
xst.get_range(excel.cells,excel.cells).horizontalalignment = xlvalign.xlvaligncenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.cells = row.tostring();
}
}
}
//
//加载一个合计行
//
int rowsum = rowindex + 1;
int colsum = 2;
excel.cells = "合计";
xst.get_range(excel.cells,excel.cells).horizontalalignment = xlhalign.xlhaligncenter;
//
//设置选中的部分的颜色
//
xst.get_range(excel.cells,excel.cells).select();
xst.get_range(excel.cells,excel.cells).interior.colorindex = 19;//设置为浅黄色，共计有56种
//
//取得整个报表的标题
//
excel.cells = str;
//
//设置整个报表的标题格式
//
xst.get_range(excel.cells,excel.cells).font.bold = true;
xst.get_range(excel.cells,excel.cells).font.size = 22;
//
//设置报表表格为最适应宽度
//
xst.get_range(excel.cells,excel.cells).select();
xst.get_range(excel.cells,excel.cells).columns.autofit();
//
//设置整个报表的标题为跨列居中
//
xst.get_range(excel.cells,excel.cells).select();
xst.get_range(excel.cells,excel.cells).horizontalalignment = xlhalign.xlhaligncenteracrossselection;
//
//绘制边框
//
xst.get_range(excel.cells,excel.cells).borders.linestyle = 1;
xst.get_range(excel.cells,excel.cells).borders.weight = xlborderweight.xlthick;//设置左边线加粗
xst.get_range(excel.cells,excel.cells).borders.weight = xlborderweight.xlthick;//设置上边线加粗
xst.get_range(excel.cells,excel.cells).borders.weight = xlborderweight.xlthick;//设置右边线加粗
xst.get_range(excel.cells,excel.cells).borders.weight = xlborderweight.xlthick;//设置下边线加粗
//
//显示效果
//
excel.visible=true;
//xst.export(server.mappath(".")+"//"+this.xlfile.text+".xls",sheetexportactionenum.ssexportactionnone,microsoft.office.interop.owc.sheetexportformat.ssexporthtml);
xbk.savecopyas(server.mappath(".")+"//"+this.xlfile.text+".xls");
ds = null;
xbk.close(false, null,null);
excel.quit();
system.runtime.interopservices.marshal.releasecomobject(xbk);
system.runtime.interopservices.marshal.releasecomobject(excel);
system.runtime.interopservices.marshal.releasecomobject(xst);
xbk = null;
excel = null;
xst = null;
gc.collect();
string path = server.mappath(this.xlfile.text+".xls");
system.io.fileinfo file = new system.io.fileinfo(path);
response.clear();
response.charset="gb2312";
response.contentencoding=system.text.encoding.utf8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
response.addheader("content-disposition", "attachment; filename=" + server.urlencode(file.name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
response.addheader("content-length", file.length.tostring());
// 指定返回的是一个不能被客户端读取的流，必须被下载
response.contenttype = "application/ms-excel";
// 把文件流发送到客户端
response.writefile(file.fullname);
// 停止页面的执行
response.end();
}
