asp将table生成excel文件(xls)

时间:2022-09-29 20:20:16
代码如下:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%> 
<% 
if request("action")=1 then 
Response.ContentType="application/ms-excel" 
Response.AddHeader "content-disposition","attachment;filename=www.xls" 
end if 
%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" /> 
<title>无标题文档</title> 
<style type="text/css"> 
table { 
border-top:1px solid #003399; 
border-left:1px solid #003399; 

td { 
border-right:1px solid #003399; 
border-bottom:1px solid #003399; 

thead { 
background-color:#000066; 
font-weight:bold; 
padding:5px; 
color:#FFFFFF; 

</style> 
<script language="javascript"> 
function tableToExcel(){ 
location.href='?action=1'; 

</script> 
</head> 

<body> 
<input type="button" value="导出数据" onclick="tableToExcel()" /> 
<% 
ConnStr="..." 
Set conn = Server.CreateObject("ADODB.Connection") 
conn.Open connstr 

set rs = server.CreateObject("adodb.recordset") 
rs.open "select top 10 * from [你的表名]",conn,1,1 
if not (rs.eof and rs.bof) then 
column = rs.fields.count 
response.Write("<table cellpadding='0' cellspacing='0'>") 
response.Write("<thead><td>序号</td>") 
for each f in rs.fields 
response.Write("<td>" & f.name & "</td>") 
next 
response.Write("</thead>") 
for j = 1 to rs.recordcount 
if j > 5 then '在第五条的时候隐藏数据,经过测试如果是display为none的数据是不会导出来的 
response.Write("<tr style='display:none'>") 
else 
response.Write("<tr>") 
end if 
response.Write("<td>" & j & "</td>") 
for i = 0 to column - 1 
response.Write("<td>" & rs(i) & "</td>") 
next 
response.Write("</tr>") 
rs.movenext 
next 
response.Write("</table>") 
end if 
rs.close 
set rs = nothing 
conn.close 
set conn = nothing 
%> 
</body> 
</html>