XSL: selecting columns in Excel XML

Discussion in 'XML' started by Axial, Nov 8, 2004.

  1. Axial

    Axial Guest

    Question: How to select columns from Excel-generated XML when some cells
    are empty.

    I've found examples where rows are to be selected, but I can't seem to
    extrapolate from that to selecting columns when some cells are empty. Is
    there a way to use the ss:Index to account for the missing <Cell elements?

    Thank you for any suggestions.

    ====================
    XML input
    ====================

    <?xml version="1.0"?>
    <Workbook xmlns="urn:schemas-microsoft-com:eek:ffice:spreadsheet"
    xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
    xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"
    xmlns:ss="urn:schemas-microsoft-com:eek:ffice:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <DocumentProperties xmlns="urn:schemas-microsoft-com:eek:ffice:eek:ffice">
    <Author>Boss</Author>
    <LastAuthor>Boss</LastAuthor>
    <Created>2004-11-06T23:49:56Z</Created>
    <LastSaved>2004-11-06T23:50:53Z</LastSaved>
    <Version>10.6626</Version>
    </DocumentProperties>
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:eek:ffice:eek:ffice">
    <DownloadComponents/>
    <LocationOfComponents HRef="/"/>
    </OfficeDocumentSettings>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:eek:ffice:excel">
    <WindowHeight>13680</WindowHeight>
    <WindowWidth>17100</WindowWidth>
    <WindowTopX>240</WindowTopX>
    <WindowTopY>15</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
    <Alignment ss:Vertical="Bottom"/>
    <Borders/>
    <Font/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
    </Style>
    <Style ss:ID="s21">
    <Font x:Family="Swiss" ss:Bold="1"/>
    </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="4"
    x:FullColumns="1"
    x:FullRows="1">
    <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Rate</Data></Cell>
    <Cell><Data ss:Type="String">Price</Data></Cell>
    <Cell><Data ss:Type="String">Rounded</Data></Cell>
    <Cell><Data ss:Type="String">AOT adj.</Data></Cell>
    <Cell><Data ss:Type="String">Total</Data></Cell>
    <Cell><Data ss:Type="String">Florida</Data></Cell>
    <Cell><Data ss:Type="String">TX Retail</Data></Cell>
    </Row>
    <Row>
    <Cell><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="Number">66</Data></Cell>
    <Cell><Data ss:Type="Number">99</Data></Cell>
    <Cell ss:Index="5"><Data ss:Type="Number">5.125</Data></Cell>
    <Cell><Data ss:Type="Number">8.375</Data></Cell>
    <Cell><Data ss:Type="Number">8.625</Data></Cell>
    </Row>
    <Row>
    <Cell><Data ss:Type="Number">4.125</Data></Cell>
    <Cell><Data ss:Type="Number">77</Data></Cell>
    <Cell><Data ss:Type="Number">22</Data></Cell>
    <Cell ss:Index="5"><Data ss:Type="Number">5.125</Data></Cell>
    <Cell><Data ss:Type="Number">8.375</Data></Cell>
    <Cell><Data ss:Type="Number">8.625</Data></Cell>
    </Row>
    <Row>
    <Cell><Data ss:Type="Number">4.25</Data></Cell>
    <Cell><Data ss:Type="Number">88</Data></Cell>
    <Cell><Data ss:Type="Number">98</Data></Cell>
    <Cell><Data ss:Type="Number">0.25</Data></Cell>
    <Cell><Data ss:Type="Number">1.75</Data></Cell>
    <Cell><Data ss:Type="Number">5</Data></Cell>
    <Cell><Data ss:Type="Number">5.25</Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:eek:ffice:excel">
    <Selected/>
    <Panes>
    <Pane>
    <Number>3</Number>
    <ActiveRow>10</ActiveRow>
    <ActiveCol>2</ActiveCol>
    </Pane>
    </Panes>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    </Workbook>

    =====================
    XSL stylesheet
    =====================
    <?xml version='1.0'?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0"
    xmlns:ss="urn:schemas-microsoft-com:eek:ffice:spreadsheet"
    xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
    xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"

    exclude-result-prefixes=" ss x o"
    >

    <xsl:eek:utput encoding="ISO-8859-1" indent="yes" />

    <xsl:variable name="root" select="/" />
    <!-- -->
    <!-- ======================================================= -->
    <!-- ======================================================= -->
    <xsl:template match="/">
    <xsl:apply-templates />

    </xsl:template>

    <xsl:variable name="TFM">${TFM}</xsl:variable>
    <xsl:variable name="DAT">${DAT}\</xsl:variable>
    <xsl:variable name="GRA">${GRA}</xsl:variable>

    <!-- ======================================================= -->

    <xsl:template match="Workbook">
    <xsl:apply-templates />
    </xsl:template>

    <xsl:template match="Worksheet">
    <xsl:apply-templates />
    </xsl:template>

    <!-- ======================================================= -->

    <xsl:template match="ss:Table">
    <Table>

    <xsl:for-each select="./ss:Row[position() = 1]/ss:Cell">
    <xsl:variable name="vColID" select="position()"/>
    <xsl:choose>
    <xsl:when test="position() &gt; 5">

    <Branch>
    <xsl:attribute name="Name"><xsl:value-of select="."/></xsl:attribute>
    <xsl:attribute name="ColID"><xsl:value-of select="$vColID"/></xsl:attribute>

    <Rates>
    <xsl:for-each
    select="//ss:Workbook/ss:Worksheet[@ss:Name='Sheet1']/ss:Table/ss:Row[position()
    &gt; 1]">


    <Rate>
    <xsl:attribute name="vRateID"><xsl:value-of select="position()"/>
    </xsl:attribute>
    <xsl:attribute name="vRate"><xsl:value-of
    select="./ss:Cell/ss:Data"/></xsl:attribute>

    <xsl:attribute name="vPrice"><xsl:value-of
    select="./ss:Cell[$vColID]/ss:Data"/></xsl:attribute>

    </Rate>
    <xsl:variable name="vRateID" select="position()"/>
    </xsl:for-each>

    </Rates>

    </Branch></xsl:when>
    </xsl:choose>
    </xsl:for-each>
    </Table>
    </xsl:template>
    <!-- ======================================================= -->
    <!-- ======================================================= -->
    <xsl:template match="text()" />

    </xsl:stylesheet>

    =======================
    Output generated
    =======================

    <?xml version="1.0" encoding="ISO-8859-1"?>
    <Table>
    <Branch Name="Florida" ColID="6">
    <Rates>
    <Rate vRateID="1" vRate="4" vPrice="8.625"/>
    <Rate vRateID="2" vRate="4.125" vPrice="8.625"/>
    <Rate vRateID="3" vRate="4.25" vPrice="5"/>
    </Rates>
    </Branch>
    <Branch Name="TX Retail" ColID="7">
    <Rates>
    <Rate vRateID="1" vRate="4" vPrice=""/>
    <Rate vRateID="2" vRate="4.125" vPrice=""/>
    <Rate vRateID="3" vRate="4.25" vPrice="5.25"/>
    </Rates>
    </Branch>
    </Table>

    =======================
    Output intended
    =======================
    <?xml version="1.0" encoding="ISO-8859-1"?>
    <Table>
    <Branch Name="Florida" ColID="6">
    <Rates>
    <Rate vRateID="1" vRate="4" vPrice="8.375"/>
    <Rate vRateID="2" vRate="4.125" vPrice="8.375"/>
    <Rate vRateID="3" vRate="4.25" vPrice="5"/>
    </Rates>
    </Branch>
    <Branch Name="TX Retail" ColID="7">
    <Rates>
    <Rate vRateID="1" vRate="4" vPrice="8.625"/>
    <Rate vRateID="2" vRate="4.125" vPrice="8.625"/>
    <Rate vRateID="3" vRate="4.25" vPrice="5.25"/>
    </Rates>
    </Branch>
    </Table>
     
    Axial, Nov 8, 2004
    #1
    1. Advertising

  2. Axial

    Axial Guest

    No suggestions? Or is my question confusing, in which case I can try to
    elaborate a little more.

    Thx

    Axial wrote:
    > Question: How to select columns from Excel-generated XML when some cells
    > are empty.
    >
    > I've found examples where rows are to be selected, but I can't seem to
    > extrapolate from that to selecting columns when some cells are empty. Is
    > there a way to use the ss:Index to account for the missing <Cell elements?
    >
    > Thank you for any suggestions.
    >
     
    Axial, Nov 9, 2004
    #2
    1. Advertising

  3. Axial

    Joris Gillis Guest

    > No suggestions? Or is my question confusing, in which case I can try to
    > elaborate a little more.


    Hi,

    Your question was cristal-clear. The solution only proved to be quite difficult.
    I have here one type of solution. It uses result tree fragments, so you'd have to declare xsl version 1.1 (which is actually depricated). Tested with Saxon.

    use this to recall the price out of column $vColID:

    <xsl:attribute name="vPrice">
    <xsl:call-template name="FetchRow">
    <xsl:with-param name="index" select="$vColID"/>
    <xsl:with-param name="row"><xsl:copy-of select="*"/></xsl:with-param>
    </xsl:call-template>
    </xsl:attribute>

    This template must be included:

    <xsl:template name="FetchRow">
    <xsl:param name="index"/>
    <xsl:param name="row"/>
    <xsl:param name="offset" select="0"/>
    <xsl:variable name="count">
    <xsl:choose>
    <xsl:when test="$row/ss:Cell[1]/@ss:Index">
    <xsl:value-of select="$row/ss:Cell[1]/@ss:Index"/>
    </xsl:when>
    <xsl:eek:therwise>
    <xsl:value-of select="$offset + 1"/>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:variable>
    <xsl:if test="$count = $index"><xsl:value-of select="$row/ss:Cell[1]/ss:Data"/></xsl:if>
    <xsl:if test="$count &lt; $index">
    <xsl:call-template name="FetchRow">
    <xsl:with-param name="index" select="$index"/>
    <xsl:with-param name="row"><xsl:copy-of select="$row/ss:Cell[position() != 1]"/></xsl:with-param>
    <xsl:with-param name="offset"><xsl:copy-of select="$count"/></xsl:with-param>
    </xsl:call-template>
    </xsl:if>
    </xsl:template>

    regards,
    --
    Joris Gillis (http://www.ticalc.org/cgi-bin/acct-view.cgi?userid=38041)
    Ceterum censeo XML omnibus esse utendum
     
    Joris Gillis, Nov 9, 2004
    #3
  4. Axial

    Joris Gillis Guest

    Joris Gillis, Nov 9, 2004
    #4
  5. Axial

    Axial Guest

    Joris, thank you for your solution. Under any template name ;-) it would
    do exactly what I need. Unfortunately I have to use 1.0 and there isn't
    a node-set() or nodeset() extension function. After looking at your
    example I can certainly see how nodesets are so valuable a concept.

    Joris Gillis wrote:
    > It would make more sense if that template 'FetchRow' was called
    > 'FetchColumn' :)
    >
     
    Axial, Nov 9, 2004
    #5
  6. Axial

    Axial Guest

    I'll run two separate transforms, so that I can use a 1.1 parser for the
    first pass, and go back to the 1.0 for the rest of the job.

    Thank you again for your assistance.

    Joris Gillis wrote:
    > It would make more sense if that template 'FetchRow' was called
    > 'FetchColumn' :)
    >
     
    Axial, Nov 10, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Johannes Koch
    Replies:
    0
    Views:
    850
    Johannes Koch
    Jul 2, 2003
  2. Marrow
    Replies:
    0
    Views:
    4,143
    Marrow
    Jul 2, 2003
  3. Replies:
    1
    Views:
    3,611
    A. Bolmarcich
    May 27, 2005
  4. =?iso-8859-1?q?Tobias_M=FCller?=

    converting xml to table width multiple columns by xsl

    =?iso-8859-1?q?Tobias_M=FCller?=, Sep 6, 2005, in forum: XML
    Replies:
    4
    Views:
    3,413
    Andy Dingley
    Sep 6, 2005
  5. enggr
    Replies:
    0
    Views:
    1,641
    enggr
    Jun 30, 2010
Loading...

Share This Page