IDC资讯中心
<!–#include file=../conn.asp–>
<%
数据库导出记录代码
作者海娃,haiwa#blueidea.com，http://www.51windows.net
用法：
exportdb.asp?sql=select语句&tablename=表名(可选)&filetype=导出格式(xml,htm,csv,sql)&pid=自动编号字段名(仅当导出sql类型时有用)
dim tablename,filetype,fieldpid
sql = request(“sql”)
tablename = request(“tablename”)
filetype = lcase(request(“filetype”))
fieldpid = request(“pid”)
if fieldpid = “” then
fieldpid = “id”
end if
fieldpid = lcase(fieldpid)
if lcase(left(sql,6))<>”select” then
response.write “sql语句必须为select * from [table] where …….”
response.end
end if
if tablename = “” then
tablename = “数据导出结果”
end if
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
function myreplace(str)
if not isnull(str) then
fstring = replace(fstring,””””, “”””””)
myreplace = str
else
myreplace = “”
end if
end function
function myreplace2(str)
if not isnull(str) then
fstring = replace(fstring,””, “”)
myreplace2 = str
else
myreplace2 = “”
end if
end function
dim def_export_sep,def_export_val
def_export_sep = “,”
def_export_val = “”””
set rs = conn.execute(sql)
导出xml文件
if filetype=”xml” then
response.contenttype=”text/xml”
response.charset = “gb2312”
response.addheader “content-disposition”, “attachment;filename=”&tablename&”.xml”
response.write “<?xml version=””1.0″” encoding=””gb2312″”?>” & vbnewline
response.write “<root>”
strline=””
dim thefield(50)
i = 0
for each x in rs.fields
thefield(i)=x.name
i=i+1
next
while rs.eof =false
strline= vbnewline&chr(9)&”<row>”
k=0
for each x in rs.fields
strline= strline & vbnewline&chr(9)&chr(9)&”<“&thefield(k)&”>”
if instr(x.value,”<“)>0 or instr(x.value,”>”)>0 or instr(x.value,”&”)>0 or len(x.value)>255 then
strline= strline &”<![cdata[“& x.value &”]]>”
else
strline= strline & x.value
end if
strline= strline &”</”&thefield(k)&”>”
k=k+1
next
rs.movenext
response.write strline &vbnewline& chr(9)&”</row>”
wend
response.write vbnewline&”</root>”
导出sql文件
elseif filetype=”sql” then
response.contenttype=”text/sql”
response.addheader “content-disposition”, “attachment;filename=”&tablename&”.sql”
strline=””
dim sql_insert
for each x in rs.fields
if lcase(x.name)<>fieldpid then 如果是自动编号
strline= strline & def_export_val & x.name & def_export_val & def_export_sep
end if
next
strline = replace(left(strline,len(strline)-1),””””,””)
strline = “insert into [“&tablename&”] (” & strline & “) values “
sql_insert = strline
response.write strline & vbnewline
response.end
while rs.eof =false
strline= “”
def_export_val = “”
for each x in rs.fields
if lcase(x.name)<>fieldpid then
2004-8-11更新 null值时无法导出的bug。
x_value = x.value
if isnull(x_value) or len(x_value) = 0 then
x_value = “”
else
x_value = replace(x_value,””,””)
end if
strline= strline & def_export_val & x_value & def_export_val & def_export_sep
end if
next
rs.movenext
strline = left(strline,len(strline)-1)
response.write sql_insert & “(“& strline &”)*” & vbnewline
wend
elseif filetype=”csv” then
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 & myreplace(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>导出数据 － www.51windows.net</title>
<style>
<!–
body,input,select { font-family: tahoma; font-size: 8pt }
th { font-family: tahoma; font-size: 8pt;padding:3px;color:#ffffff;background-color:#999999;}
td { font-family: tahoma; font-size: 8pt;padding:3px;background-color:#efefef;}
–>
</style>
</head>
<body style=”overflow:auto;” topmargin=2 bgcolor=buttonface>
<form method=”post” name=myform>
sql:<input type=”text” name=”sql” value=”<% = sql%>”>
表名:<input type=”text” name=”tablename” value=”<% = tablename %>” size=”8″>
导出格式:<select size=”1″ name=”filetype”>
<option value=””>请选择</option>
<option <%if filetype = “htm” then response.write “selected “%>value=”htm”>htm</option>
<option <%if filetype = “xml” then response.write “selected “%>value=”xml”>xml</option>
<option <%if filetype = “csv” then response.write “selected “%>value=”csv”>csv</option>
<option <%if filetype = “sql” then response.write “selected “%>value=”sql”>sql</option>
</select>
自动编号字段名:<input type=”text” name=”pid” value=”<% = fieldpid%>” size=”8″><input type=”submit” value=”确定”>
</form>
<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<>”sql” then
response.write “<p style=line-height:160%;>”&i&”条记录 <a href=?tablename=”& tablename &”&pid=”& fieldpid &”&filetype=htm&sql=”&server.urlencode(sql)&”>导出html</a>”
response.write “|<a href=?tablename=”& tablename &”&pid=”& fieldpid &”&filetype=csv&sql=”&server.urlencode(sql)&”>导出excel</a>”
response.write “|<a href=?tablename=”& tablename &”&pid=”& fieldpid &”&filetype=xml&sql=”&server.urlencode(sql)&”>导出xml</a>”
response.write “|<a href=?tablename=”& tablename &”&pid=”& fieldpid &”&filetype=sql&sql=”&server.urlencode(sql)&”>导出sql</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
%>
