ycted(长城万里今犹在，不见当年秦始皇！) 大哥请进

zyddamon 2005-03-25 12:15:42

导出数据到Excel，我们可以导出CSV格式，再另存为

用法：
－－－－－－－－－－－－－－

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

用的时候，是

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

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

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


<%
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>

</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怎么修改一下啊先谢谢拉

...全文

115 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

笨笨兔兔兔兔兔 2005-03-25

打赏
举报

长城好久不来了

你好象好了一个end if

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

zyddamon 2005-03-25