XSL: selecting columns in Excel XML

[Axial (2)]

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-comffice:spreadsheet" xmlns="urn:schemas-microsoft-comfficeffice" xmlns:x="urn:schemas-microsoft-comffice:excel" xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-comfficeffice"> <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-comfficeffice"> <DownloadComponents/> <LocationOfComponents HRef="/"/> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-comffice: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-comffice: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-comffice:spreadsheet" xmlns="urn:schemas-microsoft-comfficeffice" xmlns:x="urn:schemas-microsoft-comffice:excel"

exclude-result-prefixes=" ss x o"<xslutput 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/ssata"/></xsl:attribute>

<xsl:attribute name="vPrice"><xsl:value-of
select="./ss:Cell[$vColID]/ssata"/></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 (2)]

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

Thx

 

[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: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">
<xslaram name="index"/>
<xslaram name="row"/>
<xslaram 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>
<xsltherwise>
<xsl:value-of select="$offset + 1"/>
</xsltherwise>
</xsl:choose>
</xsl:variable>
<xsl:if test="$count = $index"><xsl:value-of select="$row/ss:Cell[1]/ssata"/></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]

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

 

[Axial (2)]

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.

 

[Axial (2)]

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.