如何将asp查询结果输出为excel表格
本人有一个小的asp商城系统,由于各种原因只能使用asp+access做。请问asp可以将订单生成excel表格吗?如果可以,能否给个简单的列子。...
本人有一个小的asp商城系统,由于各种原因只能使用asp+access做。 请问asp可以将订单生成excel表格吗? 如果可以,能否给个简单的列子。
展开
展开全部
<!--#include file="include/conn.asp"-->
<HTML>
<HEAD>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<TITLE>生成EXCEL文件</TITLE>
</HEAD>
<body>
<hr size=1 align=left width=300px>
<%
Response.Expires =-1
Response.ExpiresAbsolute = Now() - 1
Response.cachecontrol = "no-cache"
Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
filename="Regist_Exhi"&year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)&".xls"
filename1="EXECL/"&filename
filename=Server.MapPath(filename1)
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)
dim strLine,responsestr
strLine=""
'得到表的列名
strLine=strLine & "公司名称"& chr(9)
strLine=strLine & "姓名"& chr(9)
strLine=strLine & "工作职务"& chr(9)
strLine=strLine & "所在国家"& chr(9)
strLine=strLine & "所在城市"& chr(9)
strLine=strLine & "地址"& chr(9)
strLine=strLine & "邮政编码"& chr(9)
strLine=strLine & "电话"& chr(9)
strLine=strLine & "传真"& chr(9)
strLine=strLine & "手机"& chr(9)
strLine=strLine & "电子邮箱"& chr(9)
strLine=strLine & "网址"& chr(9)
strLine=strLine & "行业性质"& chr(9)
strLine=strLine & " "& chr(9)
strLine=strLine & "渠道获得"& chr(9)
strLine=strLine & "报名流水号"& chr(9)
strLine=strLine & "报名时间"& chr(9)
'--将表的列名先写入EXCEL
myfile.writeline strLine
Set rs = Server.CreateObject("ADODB.Recordset")
'--从数据库中把你想放到EXCEL中的数据查出来
sql="Select * from Regist_Exhi where Area="&Request("Pid")&" order by id Desc"
rs.Open sql,conn
if Not rs.EOF then
Do while Not rs.EOF
strLine = Rs("company") & chr(9)
strLine =strLine & Rs("gname") & Rs("sex")& chr(9)
strLine =strLine & Rs("jobtitle") & chr(9)
strLine =strLine & Rs("country") & chr(9)
strLine =strLine & Rs("city")& chr(9)
strLine =strLine & Rs("address") & chr(9)
strLine =strLine & Rs("postal") & chr(9)
strLine =strLine & Rs("phone") & chr(9)
strLine =strLine & Rs("fax") & chr(9)
strLine =strLine & Rs("mobile") & chr(9)
strLine =strLine & Rs("email")& chr(9)
strLine =strLine & Rs("http") & chr(9)
strLine =strLine & Rs("trade")&rs("tradeElse")& chr(9)
strLine =strLine & Rs("auspice") & Rs("show")& chr(9)
strLine =strLine & Rs("modes")&rs("modeElse")& chr(9)
strLine =strLine & "*"&Rs("SystemNO") & chr(9)
strLine =strLine & Rs("addtime") & chr(9)
'--将表的数据写入EXCEL
myfile.writeline strLine
rs.MoveNext
loop
end if
rs.Close
set rs=nothing
set conn = nothing
set myfile = nothing
Set fs=Nothing
Response.Redirect filename1
%>
</BODY>
</HTML>
<HTML>
<HEAD>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<TITLE>生成EXCEL文件</TITLE>
</HEAD>
<body>
<hr size=1 align=left width=300px>
<%
Response.Expires =-1
Response.ExpiresAbsolute = Now() - 1
Response.cachecontrol = "no-cache"
Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
filename="Regist_Exhi"&year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)&".xls"
filename1="EXECL/"&filename
filename=Server.MapPath(filename1)
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)
dim strLine,responsestr
strLine=""
'得到表的列名
strLine=strLine & "公司名称"& chr(9)
strLine=strLine & "姓名"& chr(9)
strLine=strLine & "工作职务"& chr(9)
strLine=strLine & "所在国家"& chr(9)
strLine=strLine & "所在城市"& chr(9)
strLine=strLine & "地址"& chr(9)
strLine=strLine & "邮政编码"& chr(9)
strLine=strLine & "电话"& chr(9)
strLine=strLine & "传真"& chr(9)
strLine=strLine & "手机"& chr(9)
strLine=strLine & "电子邮箱"& chr(9)
strLine=strLine & "网址"& chr(9)
strLine=strLine & "行业性质"& chr(9)
strLine=strLine & " "& chr(9)
strLine=strLine & "渠道获得"& chr(9)
strLine=strLine & "报名流水号"& chr(9)
strLine=strLine & "报名时间"& chr(9)
'--将表的列名先写入EXCEL
myfile.writeline strLine
Set rs = Server.CreateObject("ADODB.Recordset")
'--从数据库中把你想放到EXCEL中的数据查出来
sql="Select * from Regist_Exhi where Area="&Request("Pid")&" order by id Desc"
rs.Open sql,conn
if Not rs.EOF then
Do while Not rs.EOF
strLine = Rs("company") & chr(9)
strLine =strLine & Rs("gname") & Rs("sex")& chr(9)
strLine =strLine & Rs("jobtitle") & chr(9)
strLine =strLine & Rs("country") & chr(9)
strLine =strLine & Rs("city")& chr(9)
strLine =strLine & Rs("address") & chr(9)
strLine =strLine & Rs("postal") & chr(9)
strLine =strLine & Rs("phone") & chr(9)
strLine =strLine & Rs("fax") & chr(9)
strLine =strLine & Rs("mobile") & chr(9)
strLine =strLine & Rs("email")& chr(9)
strLine =strLine & Rs("http") & chr(9)
strLine =strLine & Rs("trade")&rs("tradeElse")& chr(9)
strLine =strLine & Rs("auspice") & Rs("show")& chr(9)
strLine =strLine & Rs("modes")&rs("modeElse")& chr(9)
strLine =strLine & "*"&Rs("SystemNO") & chr(9)
strLine =strLine & Rs("addtime") & chr(9)
'--将表的数据写入EXCEL
myfile.writeline strLine
rs.MoveNext
loop
end if
rs.Close
set rs=nothing
set conn = nothing
set myfile = nothing
Set fs=Nothing
Response.Redirect filename1
%>
</BODY>
</HTML>
展开全部
<%
dim excelstring
dim excelstr
dim excelstr2
dim head
excelstring = ""
head = "Sn" & "," & "Reason" & "," & "Empid" & "," & "Empname" & "," & "Ext" & "," & "Depname" excel="creat"
If excel<>""
Then
Set rs2 = Server.CreateObject ("ADODB.Recordset")
sql2 = "Select * from value_table "
rs2.open sql2,conn,1,3 do while not rs2.eof excelstr =" " & rs2(0) & """,""" & rs2(1) & """,""" & rs2(2) & """,""" & rs2(3) & """,""" & rs2(4) & """,""" & rs2(5) & """" excelstr2 = excelstr2 & Chr(13) & excelstr rs2.movenext loop excelstring = head &Chr(13)& excelstr2 'response.Write(excelstring) 'rs2.close
Set rs2=Nothing
set fs = CreateObject("scripting.FileSystemObject")
set exc = fs.OpenTextFile(server.MapPath("excel.csv"),2,True) exc.write(excelstring) if fs.FileExists(Server.MapPath("excel.csv"))
then Response.Write("<a href=excel.csv>报表已经生成,点击查看</a>") 'Response.write(excelstring) else Response.Write("生成报表失败!") end if End if%>
dim excelstring
dim excelstr
dim excelstr2
dim head
excelstring = ""
head = "Sn" & "," & "Reason" & "," & "Empid" & "," & "Empname" & "," & "Ext" & "," & "Depname" excel="creat"
If excel<>""
Then
Set rs2 = Server.CreateObject ("ADODB.Recordset")
sql2 = "Select * from value_table "
rs2.open sql2,conn,1,3 do while not rs2.eof excelstr =" " & rs2(0) & """,""" & rs2(1) & """,""" & rs2(2) & """,""" & rs2(3) & """,""" & rs2(4) & """,""" & rs2(5) & """" excelstr2 = excelstr2 & Chr(13) & excelstr rs2.movenext loop excelstring = head &Chr(13)& excelstr2 'response.Write(excelstring) 'rs2.close
Set rs2=Nothing
set fs = CreateObject("scripting.FileSystemObject")
set exc = fs.OpenTextFile(server.MapPath("excel.csv"),2,True) exc.write(excelstring) if fs.FileExists(Server.MapPath("excel.csv"))
then Response.Write("<a href=excel.csv>报表已经生成,点击查看</a>") 'Response.write(excelstring) else Response.Write("生成报表失败!") end if End if%>
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
简单的
<%
'连接数据库代码省略
response.contentType = "application/vnd.ms-excel" '如输出word文件将excel改为word
'读取数据库表格数据并输出数据(全部输出数据或输出指定页数据根据要求自己定)代码省略
response.addheader "Content-Disposition", "attachment; filename=工作表名.xls" '如输出word文件将xls改为doc
'关闭数据连接代码省略
%>
<%
'连接数据库代码省略
response.contentType = "application/vnd.ms-excel" '如输出word文件将excel改为word
'读取数据库表格数据并输出数据(全部输出数据或输出指定页数据根据要求自己定)代码省略
response.addheader "Content-Disposition", "attachment; filename=工作表名.xls" '如输出word文件将xls改为doc
'关闭数据连接代码省略
%>
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
给你一个最简单的方法
<html>
<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=gb2312">
<title> </title>
<%
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "inline; filename = 文件名.xls"
%>
</head>
<body>
内容
最好是用表格做出你要在EXCEL中的样子
</body>
</html>
<html>
<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=gb2312">
<title> </title>
<%
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "inline; filename = 文件名.xls"
%>
</head>
<body>
内容
最好是用表格做出你要在EXCEL中的样子
</body>
</html>
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询