xsl-list
[Top] [All Lists]

Re: Normalizing/Simplify MS-Excel XML

2005-08-16 13:12:29
So it seems to be the basic axis question: "When within my siblings was
the last time a attribute of type x appeared and what was the value. The
what was the value question seems rather simple: select="
preceding-sibling::e:Cell/@ss:Index"
The "how many nodes ago" question is where I'm stuck....

To calculate the "index" of the current "Cell" node use:

 count(preceding-sibling::*) 
- 
 count(preceding-sibling::*[(_at_)ss:Index][1]/preceding-sibling::*) 
+ 
 preceding-sibling::*[(_at_)ss:Index][1]/@ss:Index


Cheers,
Dimitre Novatchev


On 8/15/05, stephan(_at_)wissel(_dot_)net <stephan(_at_)wissel(_dot_)net> wrote:
Hi there,

cracking my head on this...
I try to convert an Excel XML Spreadsheet into a simpler format. My first
attempt works fine (my Excel sheet name is called "Sheet 1") but only when
all cells are filled (see code below). When cells in Excel are not filled
Excel XML simply adds a column reference to the next following cell like
this:

<Row>
<Cell><Data ss:Type="String">Peter Pan</Data></Cell>
<Cell ss:Index="4"><Data ss:Type="String">Toon</Data></Cell>
<Cell><Data ss:Type="String">Disney</Data></Cell>
<Cell><Data ss:Type="String">USA</Data></Cell>
<Cell ss:Index="11"><Data ss:Type="String">7</Data></Cell>
<Cell><Data ss:Type="String">GA</Data></Cell>
</Row>

So type of movie is in column 4, the Studio in column 5 and the country in
column 6.
(Column 2 would have the Male Star and Column 3 the Female, which is empty
for Toons or Animee, Column 7-10 some more statistical data)

I need:
<movie>
<title>Peter
Pan</title><genre>Toon</genre><studio>Disney</studio><country>USA</country><rating>7</rating><audience>GA</audience>
</movie>

The field names are in the first row....
What I wonder: how do I get an XPath expression (I guess
preceding-sibling:: comes into play) that can give me the column name
based on the last ss:Index and the "distance to it", so I get column 6 for
Disney, column 7 for USA etc.

So it seems to be the basic axis question: "When within my siblings was
the last time a attribute of type x appeared and what was the value. The
what was the value question seems rather simple: select="
preceding-sibling::e:Cell/@ss:Index"
The "how many nodes ago" question is where I'm stuck....

Help is greatly appreciated!!!
:-) stw

My template that works for full rows:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="
http://www.w3.org/1999/XSL/Transform"; xmlns:e="
urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="
urn:schemas-microsoft-com:office:spreadsheet" exclude-result-prefixes="e
ss">
       <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="
yes"/>
       <xsl:param name="sheetname">Sheet 1</xsl:param>
       <xsl:template match="e:Workbook">
               <xsl:element name="importdata">
                       <xsl:attribute name="type"><xsl:value-of select="
$sheetname"/></xsl:attribute>
                       <xsl:apply-templates select="
e:Worksheet[(_at_)ss:Name=$sheetname]"/>
               </xsl:element>
       </xsl:template>
       <xsl:template match="e:Worksheet">
               <xsl:apply-templates select="e:Table"/>
       </xsl:template>
       <xsl:template match="e:Table">
               <xsl:apply-templates select="e:Row"/>
       </xsl:template>
       <xsl:template match="e:Row">
               <xsl:element name="record">
                       <xsl:attribute name="position"><xsl:value-of
select="position()-1"/></xsl:attribute>
                       <xsl:apply-templates select="e:Cell"/>
               </xsl:element>
       </xsl:template>
       <xsl:template match="e:Cell[e:Data!='']">
               <xsl:variable name="pos" select="position()"/>
               <xsl:element name="field">
                       <xsl:attribute name="name"><xsl:value-of select="
normalize-space(ancestor::e:Table/e:Row[position()=1]/e:Cell[position()=$pos])
"/></xsl:attribute>
                       <xsl:value-of select="e:Data"/>
               </xsl:element>
       </xsl:template>
       <!-- elements we do not need -->
       <xsl:template match="e:Row[position()=1]"/>
       <!-- First row contains field names -->
       <xsl:template match="*"/>
</xsl:stylesheet>


--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: 
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--




-- 
Cheers,
Dimitre Novatchev
---------------------------------------
Harry did not ask how Dumbledore knew; ...but Harry had long since
learned that bangs and smoke were more often the marks of ineptitude
than expertise.

--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--



<Prev in Thread] Current Thread [Next in Thread>