資料庫匯出xml、htm、csv、sql

來源:互聯網
上載者:User
xml|匯出xml|資料|資料庫

<!--#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&"



相關文章

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 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。