ASP导出数据到excel
最近做一個用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代碼如下：
<!--#include file="../Inc/Conn.asp"-->
<!--#include file="../Inc/Function.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="×_gs(now())&"-result.xls"
%>
<title>全部導出到EXCEL</title>
</head>
<body>
<table width="100%" border="1" cellpadding="2" cellspacing="1">
<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="×_gs(now())&"-result.xls"
rs.close
set rs=nothing
%>
總結
以上是生活随笔為你收集整理的ASP导出数据到excel的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： asp格式化日期函数
- 下一篇： ASP excel导出/导入Access