資料庫匯出xml、htm、csv、sql通用代碼

來源:互聯網
上載者:User
xml|匯出xml|資料|資料庫|通用代碼 資料庫匯出xml、htm、csv、sql代碼

用法:ExportDB.asp?sql=select語句&table=表名(可選)&filetype=匯出格式(xml,htm,csv,sql)&pid=自動編號欄位名(僅當匯出sql類型時有用)

2004-8-11 更新

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



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。