28,390
社区成员
发帖
与我相关
我的任务
分享
Sub SaveToFile(str, path)
With CreateObject("ADODB.Stream")
.Mode = 3
.Type = 2
.Open
.Charset = "UTF-8"
.WriteText str
.SaveToFile path, 2
.Close
End With
End Sub
Function LoadXML(path)
Set LoadXML = CreateObject("MSXML2.DOMDocument")
LoadXML.async = False
LoadXML.load path
End Function
Dim rs, path, savePath, xsltPath
savePath = Server.MapPath("/export/xls/myExcelFile.xls")
xsltPath = Server.MapPath("xsl.xslt")
path = Server.MapPath(Session.SessionId & ".xml")
Set rs = conn.Execute("SELECT * FROM [tab]")
rs.Save path, 1
rs.Close
Set rs = Nothing
SaveToFile LoadXML(path).transformNode(LoadXML(xsltPath)), savePath
CreateObject("Scripting.FileSystemObject").DeleteFile path
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fn='http://www.w3.org/2005/02/xpath-functions'
xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<xsl:output method="html" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<meta name="ProgId" content="Excel.Sheet" />
<meta name="Generator" content="Microsoft Excel 11" />
<xsl:value-of select="'<!--[if gte mso 9]>'" disable-output-escaping="yes"/><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>工作薄</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>285</x:DefaultRowHeight>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveRow>2</x:ActiveRow>
<x:ActiveCol>1</x:ActiveCol>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>9090</x:WindowHeight>
<x:WindowWidth>11715</x:WindowWidth>
<x:WindowTopX>240</x:WindowTopX>
<x:WindowTopY>90</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><xsl:value-of select="'<![endif]-->'" disable-output-escaping="yes"/>
<style type="text/css">td,th{font-size:12px; font-family:Arial,新宋体;}</style>
</head>
<body>
<table>
<tr>
<xsl:for-each select="//s:Schema//s:ElementType/s:AttributeType">
<th><xsl:value-of select="./@name"/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="/xml/rs:data/z:row">
<xsl:variable name="row" select="."/>
<tr>
<xsl:for-each select="//s:Schema//s:ElementType/s:AttributeType">
<xsl:variable name="th" select="." />
<td>
<xsl:for-each select="$row/@*">
<xsl:if test="local-name(.) = $th/@name">
<xsl:value-of select="."/>
</xsl:if>
</xsl:for-each>
</td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body></html>
</xsl:template>
</xsl:stylesheet>
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!--#include file="conn.asp"-->
<%
DIM infoid
infoid=Trim(Request("infoid"))
on error resume next'如果有错误继续执行下面的代码
Server.ScriptTimeOut=360000'防止超时
set conn = server.createobject("ADODB.Connection")
conn.open StrConn
set rs=server.createobject("adodb.recordset")
'sql="select * from PE_Commonmodel where ItemID in ("&infoid&")"'根据此SQL语句导出至Excel
sql="select PE_CommonModel.title as 标题,PE_U_Article.Author as 作者,PE_U_Article.Content as 内容 from PE_CommonModel,PE_U_Article WHERE PE_CommonModel.ItemID = PE_U_Article.ID and PE_CommonModel.Status=99 and PE_CommonModel.ItemID in ("&infoid&")"'根据此SQL语句导出至Excel
rs.Open sql,conn,3,3
for Createtablei=0 to rs.Fields.Count-1
Createtable=Createtable&rs.fields(Createtablei).name&" text ,"
next
Createtablesql="Create table Sheet1("&left(Createtable,len(Createtable)-1)&")"
ExcelFile="Excel/Excel.xls"
set fso=Server.CreateObject ("Scripting.FileSystemObject")
fpath=Server.MapPath(ExcelFile)
if fso.FileExists(fpath) then
whichfile=Server.MapPath(ExcelFile)
Set fs = CreateObject("Scripting.FileSystemObject")
Set thisfile = fs.GetFile(whichfile)
thisfile.delete true
dim excelfile,tbname
end if
Dim Driver,DBPath
Set conn = Server.CreateObject("ADODB.Connection")
Driver = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
DBPath = "Data Source="&Server.MapPath(ExcelFile)
conn.Open Driver & DBPath
conn.Execute(Createtablesql)
for ii=0 to rs.recordcount-1
for i=0 to rs.Fields.Count-1
Inserttablename=Inserttablename&rs.fields(i).name&","
Inserttable=Inserttable&"'"&Rs(i)&"',"
Next
Insertintosql="Insert into Sheet1("&left(Inserttablename,len(Inserttablename)-1)&")values("&left(Inserttable,len(Inserttable)-1)&")"
'显示错误信息开始
if err.number<>0 then
response.write "<br><br><br><br><br><br><br>"
response.write " <div align='center'><font color='red'>"&err.description&"</font><a href='javascript:history.go(-1)'>退回上一步!</a></div>"
response.write "<br><br><br><br><br><br>"
response.end
end if
'显示错误信息结束
conn.Execute(Insertintosql)
'显示错误信息开始
if err.number<>0 then
response.write "<br><br><br><br><br><br><br>"
response.write " <div align='center'><font color='red'>"&err.description&"</font><a href='javascript:history.go(-1)'>退回上一步!</a></div>"
response.write "<br><br><br><br><br><br>"
response.end
end if
'显示错误信息结束
Insertintosql =""
Inserttable=""
Inserttablename=""
rs.MoveNext
Next
'显示错误信息开始
if err.number<>0 then
response.write "<br><br><br><br><br><br><br>"
response.write " <div align='center'><font color='red'>"&err.description&"</font><a href='javascript:history.go(-1)'>退回上一步!</a></div>"
response.write "<br><br><br><br><br><br>"
response.end
end if
'显示错误信息结束
Response.Redirect (ExcelFile)
%>