Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > XML > XSL: selecting columns in Excel XML

Reply
Thread Tools

XSL: selecting columns in Excel XML

 
 
Axial
Guest
Posts: n/a
 
      11-08-2004
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="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 xmlnssl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="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>

 
Reply With Quote
 
 
 
 
Axial
Guest
Posts: n/a
 
      11-09-2004
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.
>


 
Reply With Quote
 
 
 
 
Joris Gillis
Guest
Posts: n/a
 
      11-09-2004
> 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 (http://www.ticalc.org/cgi-bin/acct-v...i?userid=38041)
Ceterum censeo XML omnibus esse utendum
 
Reply With Quote
 
Joris Gillis
Guest
Posts: n/a
 
      11-09-2004
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
 
Reply With Quote
 
Axial
Guest
Posts: n/a
 
      11-09-2004
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'
>


 
Reply With Quote
 
Axial
Guest
Posts: n/a
 
      11-10-2004
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'
>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
selecting all the columns in a table based on the column headersvalue yawnmoth XML 1 05-25-2009 05:37 PM
Active-Record Sql: selecting only few columns Rajat Garg Ruby 2 03-12-2008 12:57 PM
Highlighting / Selecting columns in an HTML table karflips33@fastmail.fm Javascript 0 10-10-2006 08:40 AM
DataGrid: Selecting multiple rows/columns PontiMax ASP .Net Datagrid Control 0 01-12-2005 12:38 PM
selecting images in datagrid - template columns? Paul Baker ASP .Net Web Controls 0 11-23-2003 08:38 PM



Advertisments