ASP导出数据到excel

来源：互联网发布：网络喷子经典语录编辑：程序博客网时间：2024/05/24 15:38

最近做一个用asp做的网站后台的数据导出功能时,google,baidu了很多,但是多数都是转载,代码大概都一样,但是就是报错,最后有人说通过设置MIME类型和建立模板,最后整理,测试问题终于解决.关键的地方有以下几点:
1):MIME类型要设置正确,如要把DB中表(或表中指定字段)的数据导出成exel格式时,头部的MEMI类型的设置是:

<%
Response.Expires = 0
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "content-disposition","inline; filename=exportDataName.xls"
%>

尾部的MIME类型的设置:
<%
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "content-disposition","attachment filename=exportDataName.xls"
rs.close
set rs=nothing
%>
注::exportDataName为导出exel时要保存的文件名,自己可以自定义,比如以yyyy-MM-dd形式保存,以上头部信息的设置,即MIME类型的设置和JAVA代码中的文件下载设置一样,其它的类型如法炮制.
2)模板的字段设置一定要和从表中读出的要迭代的一致.

xls.asp代码如下：


<%
function times_gs(times)
dim years,months,days
years=year(times)
months=month(times)
if len(months)=1 then
months="0"&months
end if
days=day(times)
if len(days)=1 then
days="0"&days
end if
times_gs=years&"-"&months&"-"&days
end function

'用于查询记过的导出，当然可以通过各种方式传递值到这个页面，我是通过button的onlick：表单名.action='xls.asp?action=srh<%=strpage%>，strpage用于搜索时的项

源码如下：
riqi = trim(request("riqi"))
riqi2 = trim(request("riqi2"))
danhao = trim(request("danhao"))
wuliu = trim(request("wuliu"))
kehu = trim(request("kehu"))
jbr = trim(request("jingbanren"))

if riqi<>"" and riqi2<>"" then
sql_riqi = "and riqi between #"&riqi&"# and #"&riqi2&"#"
end if
if danhao<>"" then
sql_danhao = " and danhao='"&danhao&"'"
end if
if wuliu<>"" then
sql_wuliu = " and wuliu like '%"&wuliu&"%'"
end if
if kehu<>"" then
sql_kehu = " and kehu like '%"&kehu&"%'"
end if
if jbr<>"" then
sql_jbr = " and jingbanren like '%"&jbr&"%'"
end if

sql = "select * from t_info where 1=1 "&sql_riqi&sql_danhao&sql_wuliu&sql_kehu&sql_jbr&" order by id desc"

rs.open sql,conn,1,1
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<%
Response.Expires = 0
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "content-disposition","inline; filename="&times_gs(now())&"-result.xls"
%>
<title>全部导出到EXCEL</title>
</head>

<body>

<tr>
<td>销售单号</td>
<td>销售日期</td>
<td>客户名称</td>
<td>物流名称</td>
<td>发货地</td>
<td>经办人</td>
<td>货物状态</td>
<td>添加时间</td>
</tr>
<%
if rs.recordcount > 0 then
do while not rs.eof
%>
<tr>
<td><%=rs("danhao")%></td>
<td><%=rs("riqi")%></td>
<td><%=rs("kehu")%></td>
<td><%=rs("wuliu")%></td>
<td><%=rs("fahuodi")%></td>
<td><%=rs("jingbanren")%></td>
<td><%=rs("zhuangtai")%></td>
<td><%=rs("addtime")%></td>
</tr>
<%
rs.movenext
loop
end if
%>
</table>

</body>
</html>
<%
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "content-disposition","attachment filename="&times_gs(now())&"-result.xls"
rs.close
set rs=nothing
%>