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. Advertisements

  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. Advertisements

  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. Advertisements

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. Marrow
    Replies:
    0
    Views:
    4,352
    Marrow
    Jul 2, 2003
  2. Replies:
    1
    Views:
    3,928
    A. Bolmarcich
    May 27, 2005
  3. =?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,647
    Andy Dingley
    Sep 6, 2005
  4. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,991
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  5. yawnmoth
    Replies:
    1
    Views:
    792
    Martin Honnen
    May 25, 2009
  6. enggr
    Replies:
    0
    Views:
    1,781
    enggr
    Jun 30, 2010
  7. PontiMax

    DataGrid: Selecting multiple rows/columns

    PontiMax, Jan 12, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    207
    PontiMax
    Jan 12, 2005
  8. Paul Baker

    selecting images in datagrid - template columns?

    Paul Baker, Nov 23, 2003, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    164
    Paul Baker
    Nov 23, 2003
Loading...