XSL: selecting columns in Excel XML

[Axial]

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:sche mas-microsoft-com:office:spre adsheet"
xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
xmlns:x="urn:sc hemas-microsoft-com:office:exce l"
xmlns:ss="urn:s chemas-microsoft-com:office:spre adsheet"
xmlns:html="htt p://www.w3.org/TR/REC-html40">
<DocumentProper ties xmlns="urn:sche mas-microsoft-com:office:offi ce">
<Author>Boss</Author>
<LastAuthor>Bos s</LastAuthor>
<Created>2004-11-06T23:49:56Z</Created>
<LastSaved>20 04-11-06T23:50:53Z</LastSaved>
<Version>10.662 6</Version>
</DocumentPropert ies>
<OfficeDocument Settings xmlns="urn:sche mas-microsoft-com:office:offi ce">
<DownloadCompon ents/>
<LocationOfComp onents HRef="/"/>
</OfficeDocumentS ettings>
<ExcelWorkboo k xmlns="urn:sche mas-microsoft-com:office:exce l">
<WindowHeight>1 3680</WindowHeight>
<WindowWidth>17 100</WindowWidth>
<WindowTopX>240 </WindowTopX>
<WindowTopY>1 5</WindowTopY>
<ProtectStructu re>False</ProtectStructur e>
<ProtectWindows >False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal ">
<Alignment ss:Vertical="Bo ttom"/>
<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:ExpandedColu mnCount="7" ss:ExpandedRowC ount="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"><D ata 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"><D ata 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>
<WorksheetOptio ns xmlns="urn:sche mas-microsoft-com:office:exce l">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>10 </ActiveRow>
<ActiveCol>2</ActiveCol>
</Pane>
</Panes>
<ProtectObjects >False</ProtectObjects>
<ProtectScenari os>False</ProtectScenario s>
</WorksheetOption s>
</Worksheet>
</Workbook>

=============== ======
XSL stylesheet
=============== ======
<?xml version='1.0'?>
<xsl:styleshe et xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns:ss="urn:s chemas-microsoft-com:office:spre adsheet"
xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
xmlns:x="urn:sc hemas-microsoft-com:office:exce l"

exclude-result-prefixes=" ss x o"

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

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

</xsl:template>

<xsl:variable name="TFM">${TF M}</xsl:variable>
<xsl:variable name="DAT">${DA T}\</xsl:variable>
<xsl:variable name="GRA">${GR A}</xsl:variable>

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

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

<xsl:template match="Workshee t">
<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="positio n()"/>
<xsl:choose>
<xsl:when test="position( ) &gt; 5">

<Branch>
<xsl:attribut e name="Name"><xs l:value-of select="."/></xsl:attribute>
<xsl:attribut e name="ColID"><x sl:value-of select="$vColID "/></xsl:attribute>

<Rates>
<xsl:for-each
select="//ss:Workbook/ss:Worksheet[@ss:Name='Sheet 1']/ss:Table/ss:Row[position()
&gt; 1]">
<Rate>
<xsl:attribut e name="vRateID"> <xsl:value-of select="positio n()"/>
</xsl:attribute>
<xsl:attribut e name="vRate"><x sl:value-of
select="./ss:Cell/ss:Data"/></xsl:attribute>

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

</Rate>
<xsl:variable name="vRateID" select="positio n()"/>
</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]

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.

[Joris Gillis]

> 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:attribut e 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:otherwis e>
<xsl:value-of select="$offset + 1"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:if test="$count = $index"><xsl:va lue-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-v...i?userid=38041)
Ceterum censeo XML omnibus esse utendum

[Joris Gillis]

It would make more sense if that template 'FetchRow' was called 'FetchColumn' :)

--
Joris Gillis (http://www.ticalc.org/cgi-bin/acct-v...i?userid=38041)
Ceterum censeo XML omnibus esse utendum

[Axial]

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]

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' :)