中文XML论坛 - 专业的XML技术讨论区--显示贴子

以文本方式查看主题

-  中文XML论坛 - 专业的XML技术讨论区  (http://bbs.xml.org.cn/index.asp)
--  『 WORD to XML, HTML to XML 』  (http://bbs.xml.org.cn/list.asp?boardid=13)
----  求助：如何利用XSL把XML文档在EXCEL中显示？  (http://bbs.xml.org.cn/dispbbs.asp?boardid=13&rootid=&id=20754)

--  作者：alonefish
--  发布时间：7/29/2005 1:30:00 AM

--  求助：如何利用XSL把XML文档在EXCEL中显示？
<%
    Dim oXML
    Dim oXSL
    '将 XSL（使用 XSL 指令的工作簿模板）加载到 DOMDocument 中。
    Set oXML = Server.CreateObject("Microsoft.XMLDOM")
    oXML.async=False
    oXML.Load Server.MapPath("result.xml")
    Set oXSL = CreateObject("Microsoft.XMLDOM")
    oXSL.async=False
    oXSL.Load Server.MapPath("Orders.xsl")

    Dim oResults
    Set oResults = CreateObject("Microsoft.XMLDOM")
    oXML.transformNodeToObject oXSL, oResults

    Response.Buffer = True
    nRecords=rs.RecordCount
    If oXSL.parseError.errorCode <> 0 Then
       Response.Write "Parse Error: " & oResults.parseError.reason
    Else
       '修改 XSL 中 <table> 节点的 ss:ExpandedRowCount 属性。
        Dim oTable
        Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
        oTable.setAttribute "ss:ExpandedRowCount", nRecords+2
        '返回得到的 XML 电子表格，以便在 Excel 中进行显示。
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = "ISO-8859-1"
        Response.Write oResults.XML
        Response.Flush
    End If
%>
这是MSDN里的文章这样来实现转换的，不过我在执行时，在oTable.setAttribute "ss:ExpandedRowCount", nRecords+2
这行出现如下错误：
Microsoft VBScript 编译器错误错误 '800a03f6'
缺少 'End'
/iisHelp/common/500-100.asp，行242
Microsoft VBScript 运行时错误错误 '800a01a8'
缺少对象
/xmlexcel.asp，行57 ，就是上面那行。

--  作者：alonefish
--  发布时间：7/29/2005 1:31:00 AM

--
样式单是在EXCEL中创建的，如下：
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/XSL/Transform/1.0">
<xsl:template match="/">
<xsl:pi name="xml">version="1.0"</xsl:pi>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>yzw</Author>
  <LastAuthor>yzw</LastAuthor>
  <Created>2005-05-27T11:39:40Z</Created>
  <Company>alone</Company>
  <Version>11.5606</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>9225</WindowHeight>
  <WindowWidth>11700</WindowWidth>
  <WindowTopX>240</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Center"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s22">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="3"/>
   </Borders>
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s23">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
  </Style>
  <Style ss:ID="s24">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="3"/>
   </Borders>
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
   <NumberFormat ss:Format="0%"/>
  </Style>
  <Style ss:ID="s25">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <NumberFormat ss:Format="0%"/>
  </Style>
  <Style ss:ID="s26">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="3"/>
   </Borders>
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
   <NumberFormat
    ss:Format="_ "￥"* #,##0.00_ ;_ "￥"* \-#,##0.00_ ;_ "￥"* "-"??_ ;_ @_ "/>
  </Style>
  <Style ss:ID="s27">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <NumberFormat
    ss:Format="_ "￥"* #,##0.00_ ;_ "￥"* \-#,##0.00_ ;_ "￥"* "-"??_ ;_ @_ "/>
  </Style>
  <Style ss:ID="s31">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
  </Style>
  <Style ss:ID="s32">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <NumberFormat ss:Format="0%"/>
  </Style>
  <Style ss:ID="s33">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
   <Borders>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <NumberFormat
    ss:Format="_ "￥"* #,##0.00_ ;_ "￥"* \-#,##0.00_ ;_ "￥"* "-"??_ ;_ @_ "/>
  </Style>
</Styles>
<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="3" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
   <Column ss:StyleID="s23" ss:AutoFitWidth="0" ss:Width="93.75" ss:Span="3"/>
   <Column ss:Index="5" ss:StyleID="s25" ss:AutoFitWidth="0" ss:Width="93.75"/>
   <Column ss:StyleID="s27" ss:AutoFitWidth="0" ss:Width="93.75"/>
   <Row ss:Height="15">
    <Cell ss:StyleID="s22"><Data ss:Type="String">订单 ID</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="String">产品 ID</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="String">单价</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="String">数量</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">折扣</Data></Cell>
    <Cell ss:StyleID="s26"><Data ss:Type="String">总数</Data></Cell>
   </Row>
   <xsl:for-each select="xml/rs:data/z:row">
<Row ss:AutoFitHeight="0" ss:Height="13.5">
    <Cell><Data ss:Type="String"><xsl:value-of select="@OrderID"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@ProductID"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@UnitPrice"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@Quantity"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@Discount"/></Data></Cell>
    <Cell ss:Formula="=RC[-3]*RC[-2]*(1-RC[-1])"><Data ss:Type="Number">0</Data></Cell>
</Row>
</xsl:for-each>

   <Row ss:AutoFitHeight="0" ss:Height="24.9375">
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s31"/>
    <Cell ss:StyleID="s32"/>
    <Cell ss:StyleID="s33" ss:Formula="=SUM(R2C:R[-1]C)"><Data ss:Type="Number">73926</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <DoNotDisplayGridlines/>
   <FreezePanes/>
   <FrozenNoSplit/>
   <SplitHorizontal>1</SplitHorizontal>
   <TopRowBottomPane>1</TopRowBottomPane>
   <ActivePane>2</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
    </Pane>
    <Pane>
     <Number>2</Number>
     <ActiveRow>0</ActiveRow>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>

--  作者：alonefish
--  发布时间：7/29/2005 1:32:00 AM

--
管理员，斑竹，高手们请帮忙指教下，如果有好的方法也请告之！谢谢！

W 3 C h i n a ( since 2003 ) 旗下站点
苏ICP备05006046号《全国人大常委会关于维护互联网安全的决定》《计算机信息网络国际联网安全保护管理办法》

78.125ms