By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,375 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,375 IT Pros & Developers. It's quick & easy.

XSL: selecting columns in Excel XML

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

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">${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>

Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.


Jul 20 '05 #2

P: n/a
> 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:otherwise>
<xsl:value-of select="$offset + 1"/>
</xsl:otherwise>
</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-v...i?userid=38041)
Ceterum censeo XML omnibus esse utendum
Jul 20 '05 #3

P: n/a
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
Jul 20 '05 #4

P: n/a
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' :)


Jul 20 '05 #5

P: n/a
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' :)


Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.