开发者

xml to excel style sheet to cater for nested tables

问答 作者：

I have an XML file which has the following format

<Parent>
    <Id>1</Id>
    <Value>23</Value>
    <Child>
        <Id>1</Id>
        <SubValue1>2</SubValue1>
        <SubValue1>3</SubValue1>
    </Child>
    <Child>
        <Id>1</Id>
        <SubValue1>45</SubValue1>
        <SubValue1>45</SubValue1>
    </Child>
</Parent>

This XML is basically off a hierarchical grid where each Parent is a row in a grid and when the 开发者_如何学Cparent id is clicked, a nested grid is rendered with each child being displayed as a row in the sub grid. The idea is to represent the hierarchical grid in an Excel spread sheet. Any help with the XSLT file would be greatly appreciated

The solution:

These is my xml:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<Master>
  <Table>
    <Alias>900798</Alias>
    <Header>
      <Column>Column 1</Column>
      <Column>Column 2</Column>
      <Column>Column 3</Column>
      <Column>Column 4</Column>
      <Column>Column 5</Column>
      <Column>Column 6</Column>
      <Column>Column 8</Column>
      <Column>Column 9</Column>
      <Column>Column 10</Column>
    </Header>
    <Body>
      <Row>
        <Celle>data 1</Celle>
        <Celle>data 2</Celle>
        <Celle>data 3</Celle>
        <Celle>data 4</Celle>
        <Celle>10/21/2010 11:25:11 AM</Celle>
        <Celle>data 6</Celle>
        <Celle>data 7</Celle>
        <Celle>other data</Celle>
        <Celle>1</Celle>
      </Row>
      <Row>
        <Celle>data 1</Celle>
        <Celle>data 2</Celle>
        <Celle>data 3</Celle>
        <Celle>data 4</Celle>
        <Celle>date</Celle>
        <Celle>data 6</Celle>
        <Celle>data 7</Celle>
        <Celle>other data</Celle>
        <Celle>1</Celle>
      </Row>
    </Body>
  </Table>
</Master>

and the xsl:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                exclude-result-prefixes="msxsl"
                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:msxsl="urn:schemas-microsoft-com:xslt">

  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">
      <xsl:text>progid="Excel.Sheet"</xsl:text>
    </xsl:processing-instruction>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet">

      <xsl:call-template name="DocumentPropertis" />
      <xsl:call-template name="ExcelWorkbook"></xsl:call-template>
      <xsl:call-template name="Styles"></xsl:call-template>
      <xsl:apply-templates select="*" mode="Worksheet" />
    </Workbook>
  </xsl:template>

  <xsl:template name="DocumentPropertis">
    <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <LastAuthor>Harold</LastAuthor>
      <Created>2010-11-09T09:41:05Z</Created>
      <LastSaved>2010-11-09T09:41:05Z</LastSaved>
      <Version>11.5606</Version>
    </DocumentProperties>
  </xsl:template>

  <xsl:template name ="ExcelWorkbook">
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <WindowHeight>12780</WindowHeight>
      <WindowWidth>18795</WindowWidth>
      <WindowTopX>240</WindowTopX>
      <WindowTopY>315</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
  </xsl:template>

  <xsl:template name ="Styles">
    <Styles >
      <Style ss:ID="Default" ss:Name="Normal">
        <Alignment ss:Vertical="Bottom"/>
        <Borders/>
        <Font/>
        <Interior/>
        <NumberFormat/>
        <Protection/>
      </Style>
      <Style ss:ID="m20452808">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20452818">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20452828">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20452838">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20452640">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20452650">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20452660">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20452670">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20452476">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20452486">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20452496">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20452506">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20452324">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20452334">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20452344">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20452354">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20464136">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20464146">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="m20464156">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
      </Style>
      <Style ss:ID="m20464166">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="s33">
        <Borders>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="s34">
        <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
      </Style>
      <Style ss:ID="s43">
        <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
        <Borders>
          <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
          <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
           ss:Color="#000000"/>
        </Borders>
        <Font ss:Bold="1"/>
        <Interior ss:Color="#FFFF99" ss:Pattern="Solid"/>
      </Style>
    </Styles>
  </xsl:template>

  <xsl:template match="*" mode="Worksheet">
    <Worksheet ss:Name="Sheet 1">
      <Table x:FullColumns="1" x:FullRows="1">
        <!--<Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="64" x:FullColumns="1"
    x:FullRows="1">-->
        <Column ss:Width="74.25"/>
        <Column ss:Width="107.25"/>
        <Column ss:Width="70.5"/>
        <Column ss:Width="444.75"/>
        <Column ss:Width="84.75" ss:Span="1"/>
        <Column ss:Index="7" ss:Width="80.25"/>
        <Column ss:Width="89.25"/>
        <Column ss:Width="67.5"/>
        <Column ss:AutoFitWidth="0" ss:Width="70.75"/>
        <Column ss:AutoFitWidth="0" ss:Width="70.25"/>
        <Column ss:AutoFitWidth="0" ss:Width="70.25"/>
        <xsl:apply-templates select="Table" mode="Table"></xsl:apply-templates>
      </Table>
      <xsl:call-template name="WorksheetOptions" />
    </Worksheet>
  </xsl:template>
  <xsl:template match="*" mode ="Table">
    <Row>
      <Cell ss:MergeAcross="10" ss:StyleID="m20464136">
        <Data ss:Type="String">
          <xsl:value-of select="Alias/."/>
        </Data>
      </Cell>
    </Row>
    <Row>
      <xsl:apply-templates select="Header/*" mode="Header"></xsl:apply-templates>
      <Cell ss:Index="11" ss:StyleID="s33"/>
    </Row>
    <xsl:apply-templates select="Body/*" mode="Body"></xsl:apply-templates>
  </xsl:template>
  <xsl:template match="*" mode ="Header">
    <Cell ss:StyleID="s43">
      <Data ss:Type="String">
        <xsl:value-of select="."/>
      </Data>
    </Cell>
  </xsl:template>
  <xsl:template match ="*" mode="Body">
    <Row>
      <xsl:apply-templates select="./*" mode="Row"></xsl:apply-templates>
      <Cell ss:Index="11" ss:StyleID="s33"/>
    </Row>
  </xsl:template>
  <xsl:template match="*" mode ="Row">
    <Cell ss:StyleID="s34"><Data ss:Type="String"><xsl:value-of select="."/></Data></Cell>
  </xsl:template>
  <xsl:template name ="WorksheetOptions">
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <Selected/>
      <DoNotDisplayGridlines/>
      <Panes>
        <Pane>
          <Number>3</Number>
          <ActiveRow>1</ActiveRow>
          <ActiveCol>1</ActiveCol>
        </Pane>
      </Panes>
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
  </xsl:template>

</xsl:stylesheet>

You can adapt the xsl, the xml format data is similar.

继续阅读：excelxmlxslt

更多精彩内容

0

上一篇:

下一篇:

精彩评论

暂无评论...
登录 注册
Qedev.com
验证码 换一张
取 消

最新问答

问答排行榜

法律声明：本站内容均为网友上传，网站举办方负责审核和监督，

如存在版权或非法内容，欢迎举报，我们将尽快予以删除。

Copyright © 2018-2020 开发者. All rights reserved.

Powered by 开发者

ICP备案号： 京ICP备10032868号-9