asp查询结果保存为excel文件

发布网友发布时间：2022-04-20 21:16

共3个回答

热心网友时间：2023-09-09 07:26

我是这样实现的:按照XLS的格式,利用FSO在服务器上生成一个XLS文件,再进行下载
我贴一个实例吧,你自己看,修改几个变量,
----------------------------------导出EXCEL的文件-----


<%
response.Buffer=false
call checkadmin()
dim sql,filename,rs1,c,tablename
if request("class")<>"0" then
filename="../temp/名单"&request("class")&"班.xls" ' 要输出的EXCEL文件的文件名, 你只要改以上两句就行了,其它的都不要改.
tablename=request("class")&"班名单"
else
filename="../temp/全年段名单.xls"
tablename="全年段名单"
end if
%>
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>导出名单到EXCEL</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<BODY>
<p> </p>

<div align="center"><table width="350" border="0" cellspacing="1" cellpadding="0">
<tr class="title">
<td height="20" align="center">导出<%=tablename%></td>
</tr>
<tr>
<td height="80" align="center"><p>正在生成导出的EXCEL文件,请稍候...<br>
[<a href="javascript:window.close()">关闭窗口</a>]<br>
</p>
</td>
</tr>
</table></div>
<%
sql="select "
for each c in request("fileds")
select case c
case "class"
sql=sql&"class as 班级,"
case "num"
sql=sql&"num as 座号,"
case "name"
sql=sql&"name as 姓名,"
case "tel"
sql=sql&"tel as 电话,"
case "address"
sql=sql&"address as 地址,"
case "ptel"
sql=sql&"ptel as 家长手机,"
case "memo"
sql=sql&"memo as 备注,"
case "birthday"
sql=sql&"birthday as 生日,"
case "idcardnum"
sql=sql&"idcardnum as 学号,"
end select
next
sql=left(sql,len(sql)-1)
sql=sql&" from student"
if request("class")<>"0" then sql=sql&" where class="&request("class")
sql=sql&" order by class,num" '这里是要输出EXCEL的SQL
'response.write sql
'response.end

function ReadText(FileName) '这是一个用于读出文件的函数
dim adf,ads
set adf=server.CreateObject("Adodb.Stream")
with adf
.Type=2
.LineSeparator=10
.Open
.LoadFromFile (server.MapPath(FileName))
.Charset="GB2312"
.Position=2
ReadText=.ReadText
.Cancel()
.Close()
end with
set ads=nothing
end function

sub SaveText(FileName,Data) '这是一个用于写文件的函数
dim fs,ts
set fs= createobject("scripting.filesystemobject")
set ts=fs.createtextfile(server.MapPath(FileName),true)
ts.writeline(data)
ts.close
set ts=nothing
set fs=nothing
end sub

sub toexcel(filename,sql) '这是一个根据SQL语句和FILENAME生成EXCEL文件
dim rs,toexcellr,myfield,i,datename,tou,di
Set rs=Server.CreateObject("ADODB.RecordSet")
rs.Open sql,conn,1,3
set myfield=rs.fields
dim fieldname(50)
TOEXCELLR="<table width='100%'><tr><td colspan="&myfield.count&"><p align='center'>"&tablename&"</p></td></tr><tr>"
for i=0 to myfield.count-1
toexcellr=toexcellr&"<td class=xl24><p align='center'>"&MYFIELD(I).NAME&"</p></td>"
fieldname(i)=myfield(i).name
if myfield(i).type=135 then datename=datename&myfield(i).name&","
next
toexcellr=toexcellr&"</tr>"
do while not rs.eof
toexcellr=toexcellr&"<tr height=20>"
for i=0 to myfield.count-1
if instr(datename,fieldname(i)&",")<>0 then
if not isnull(rs(fieldname(i))) then
TOEXCELLR=TOEXCELLR&"<td class=xl25><p align='center'>"&formatdatetime(rs(fieldname(i)),2)&"</p></td>"
else
TOEXCELLR=TOEXCELLR&"<td class=xl25><p align='center'> </p></td>"
end if
else
TOEXCELLR=TOEXCELLR&"<td class=xl24><p align='center'>"&rs(fieldname(i))&"</p></td>"
end if
next
toexcellr=toexcellr&"</tr>"
rs.movenext
loop
toexcellr=toexcellr&"</table>"
tou=readtext("../include/xls_head.txt")
di=readtext("../include/xls_foot.txt")
toexcellr=tou&toexcellr&di
call savetext(filename,toexcellr)
end sub
call toexcel(FILENAME,sql)

conn.close
set conn=nothing
%>
<script>this.location="<%=filename%>";</script>
</BODY>
</HTML>
----------------------XLS的头文件,存为xls_head.txt,放在include的相应路径
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=GB2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">

<style>

</style>

</head>

<body link=blue vlink=purple>
-----------------------XLS尾文件,存为xls_foot.txt,放在include的相应路径

<table x:str border=0 cellpadding=0 cellspacing=0 width=288 style='border-collapse:
collapse;table-layout:fixed;width:216pt'>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
</tr>
<![endif]>
</table>

热心网友时间：2023-09-09 07:26

<%
conn="driver={SQL SERVER};Server=(local);UID=sa;PWD=11111111;database=table_name"
Set rs = Server.CreateObject("ADODB.RecordSet")
sqlstr="你的查询语句"
rs.open sqlstr,conn,1,3
%>
导出文件如下：
<%Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
filename="log.xls"
temp=filename
filename=Request.ServerVariables("APPL_PHYSICAL_PATH")&"\"+filename
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)
dim strLine,responsestr
strLine=""
For each x in rs.fields
strLine= strLine & x.name & chr(9)
Next
'--将表的列名先写入EXCEL
myfile.writeline strLine
while not rs.eof
strLine=""
for each x in rs.Fields
strLine= strLine & x.value & chr(9)
next
'--将表的数据写入EXCEL
myfile.writeline strLine
rs.movenext
wend
link="<A HREF=\" & temp & ">请点击下载excel文件</a>"
Response.write link
set myfile = nothing
Set fs=Nothing
rs.close
%>

热心网友时间：2023-09-09 07:27

保存成CSV格式咯！然后发给客户端保存~

全部频道

asp查询结果保存为excel文件