asp查询结果保存为excel文件 100

asp页面的查询记录怎么样保存为excel文件,我做的是一页查询,输出的是表格形式的查询结果,现在想实现,点击一个按钮保存所有的查询结果到excel,这个应该怎么做啊?我... asp页面的查询记录怎么样保存为excel文件,我做的是一页查询,输出的是表格形式的查询结果,现在想实现,点击一个按钮保存所有的查询结果到excel,这个应该怎么做啊?我是新手,谁能帮帮我,help!!!!给实例或者在线指导啊,都可以,我的qq305561447。各位大哥大姐,各位高手,会的都来指导我啊 展开
 我来答
OruA
2006-11-19 · 超过54用户采纳过TA的回答
知道小有建树答主
回答量:115
采纳率:0%
帮助的人:158万
展开全部
我是这样实现的:按照XLS的格式,利用FSO在服务器上生成一个XLS文件,再进行下载
我贴一个实例吧,你自己看,修改几个变量,
----------------------------------导出EXCEL的文件-----
<!--#include file="../include/conn.asp"-->
<!--#include file="../include/admin_function.asp"-->
<%
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">
<!--[if gte mso 9]><xml>
<DocumentProperties>
<Author>OruA</Author>
<LastAuthor>OruA</LastAuthor>
<Created>2002-05-27T17:51:00Z</Created>
<LastSaved>2002-06-22T10:03:03Z</LastSaved>
<Company>zydn</Company>
<Version>9.2812</Version>
</DocumentProperties>
<OfficeDocumentSettings>
<DownloadComponents/>
<LocationOfComponents HRef=""/>
</OfficeDocumentSettings>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:常规;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
border:.5pt solid windowtext;}
.xl25
{mso-style-parent:style0;
mso-number-format:"Long Date";
text-align:left;
border:.5pt solid windowtext;}
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-char-type:none;
display:none;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>225</x:DefaultRowHeight>
<x:print>
<x:ValidPrinterInfo/>
<x:paperSizeIndex>9</x:paperSizeIndex>
<x:HorizontalResolution>-3</x:HorizontalResolution>
<x:VerticalResolution>0</x:VerticalResolution>
</x:print>
<x:Selected/>
<x:panes>
<x:pane>
<x:Number>3</x:Number>
<x:ActiveRow>24</x:ActiveRow>
<x:ActiveCol>5</x:ActiveCol>
</x:pane>
</x:panes>
<x:protectContents>False</x:protectContents>
<x:protectObjects>False</x:protectObjects>
<x:protectScenarios>False</x:protectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Sheet2</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>225</x:DefaultRowHeight>
<x:protectContents>False</x:protectContents>
<x:protectObjects>False</x:protectObjects>
<x:protectScenarios>False</x:protectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Sheet3</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>225</x:DefaultRowHeight>
<x:protectContents>False</x:protectContents>
<x:protectObjects>False</x:protectObjects>
<x:protectScenarios>False</x:protectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>6600</x:WindowHeight>
<x:WindowWidth>12000</x:WindowWidth>
<x:WindowTopX>0</x:WindowTopX>
<x:WindowTopY>1395</x:WindowTopY>
<x:protectStructure>False</x:protectStructure>
<x:protectWindows>False</x:protectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</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>
百度网友a14540ab0
2006-11-19 · TA获得超过240个赞
知道小有建树答主
回答量:454
采纳率:0%
帮助的人:338万
展开全部
<%
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
%>
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
小小机器龟
2006-11-19 · 超过26用户采纳过TA的回答
知道答主
回答量:116
采纳率:0%
帮助的人:32.3万
展开全部
保存成CSV格式咯!然后发给客户端保存~
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
收起 更多回答(1)
推荐律师服务: 若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询

为你推荐:

下载百度知道APP,抢鲜体验
使用百度知道APP,立即抢鲜体验。你的手机镜头里或许有别人想知道的答案。
扫描二维码下载
×

类别

我们会通过消息、邮箱等方式尽快将举报结果通知您。

说明

0/200

提交
取消

辅 助

模 式