A
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: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() > 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()
> 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>
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() > 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()
> 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>