zyddamon 2005年03月25日
ycted(长城万里今犹在,不见当年秦始皇!) 大哥请进
导出数据到Excel,我们可以导出CSV格式,再另存为

用法:
--------------

下载上面代码后改名,如:ExportDB.asp

用的时候,是

http://xx/ExportDB.asp?t=文件类型&sql=sql语句

文件类型可以是htm,csv,默认是htm


sql语句:select * from table where .....
'================================================================

<!--#include file='conn.asp'-->
<%
dim tablename
sql2 = request("sql")
tablename = request("tablename")
if tablename = "" then
tablename = "数据导出结果"
end if
sql = replace(sql2,";","")
filetype = lcase(request("t"))
dim dbid,IndexPageName
function HTMLEncode(fString)
if not isnull(fString) then

fString = Server.HTMLEncode(fString)
fString = Replace(fString, CHR(10) & CHR(10), "</P><P> ")
fString = Replace(fString, CHR(10), "<BR> ")
fString = Replace(fString, CHR(9), "  ")

HTMLEncode = fString
end if
end function
dim def_export_val
def_export_sep = ","
def_export_val = """"
if lcase(left(sql,6))<>"select" then
response.write "出错了。。。"
response.end
end if
Set rs = Conn.Execute(sql)

if filetype="csv" then
'response.contenttype="application/vnd.ms-excel"
response.contenttype="text/csv"
response.AddHeader "Content-Disposition", "attachment;filename="&tablename&".csv"
strLine=""
For each x in rs.fields
strLine= strLine & def_export_val & x.name & def_export_val & def_export_sep
Next
response.write strLine & vbnewline
While rs.EOF =false
strLine= ""
For each x in rs.fields
strLine= strLine & def_export_val & replace(x.value,"""","""""") & def_export_val & def_export_sep
Next
rs.MoveNext
response.write strLine & vbnewline
Wend
else
if filetype="htm" then'弹出下载html的对话框
response.contenttype="application/ms-download"
response.AddHeader "Content-Disposition", "attachment;filename="&tablename&".htm"
end if
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<META NAME="Author" CONTENT="51windows,海娃,haiwa">
<META NAME="Description" CONTENT="Power by 51windows.Net">
<title>导出数据。。</title>
<style>
<!--
body { font-family: Verdana; font-size: 11px }
th { font-family: Verdana; font-size: 11px;padding:3px;color:#FFFFFF;background-color:#999999;}
td { font-family: Verdana; font-size: 11px;padding:3px;background-color:#EFEFEF;}
-->
</style>
</head>
<BODY style="overflow:auto;" topmargin=2 bgcolor=buttonface>
<div align="center">
<table border="0" cellpadding="0" cellspacing="1" bgcolor="#000000">
<tr>
<%
i=0
For each x in rs.fields
strLine= strLine &chr(9)&chr(9)&"<th align=""center"">"& x.name &"</th>"& vbnewline
Next
response.write strLine&chr(9)&"</tr>"& vbnewline & vbnewline
While rs.EOF =false
i=i+1
response.write chr(9)&"<tr>"& vbnewline
strLine= ""
For each x in rs.fields
strLine= strLine &chr(9)&chr(9)&"<td>"& HTMLEncode(x.value) &"</td>"& vbnewline
Next
rs.MoveNext
response.write strLine
response.write chr(9)&"</tr>"& vbnewline & vbnewline
Wend
response.write "</table>"& vbnewline
if filetype<>"htm" and filetype<>"xls" and filetype<>"txt" then
response.write "<p style='line-height:160%;'>"&i&"条记录 <a href='?tablename="&request("tablename")&"&t=htm&sql="&server.urlencode(sql2)&"'>导出HTML</a>|<a href='?tablename="&request("tablename")&"&t=csv&sql="&server.urlencode(sql2)&"'>导出EXCEL</a>"& vbnewline
end if
response.write "<p>Power by <A HREF=""http://www.51windows.Net"" target=""_blank"">51windows.Net</A>"& vbnewline
response.write "</div>"& vbnewline
response.write "</BODY>"& vbnewline
response.write "</HTML>"& vbnewline
%>

<%
end if

rs.close
conn.close
Set rs=nothing
Set conn=nothing
%>

当我传
sql=select * from paper_more where 1=1 and paper_more_time between #2005-1-1# and #2005-12-31# order by id desc
时候 报错为
Microsoft VBScript 编译器错误 错误 '800a03f6'

缺少 'End'

/iisHelp/common/500-100.asp,行242

Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14'

[Microsoft][ODBC Microsoft Access Driver] 日期的语法错误 在查询表达式 '1=1 and paper_more_time between #2005-1-1 and 2005-12-31' 中。

/paper/admin/ExportDB.asp,行31
不知道ycted怎么修改一下啊 先谢谢拉
...全文
47 点赞 收藏 2
写回复
2 条回复

还没有回复,快来抢沙发~

发动态
发帖子
ASP
创建于2007-09-28

1.9w+

社区成员

35.7w+

社区内容

ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
社区公告
暂无公告