Re: Normalizing/Simplify MS-Excel XML

> 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::*[@ss:Index][1]/preceding-sibling::*)
+
 preceding-sibling::*[@ss:Index][1]/@ss:Index


Cheers,
Dimitre Novatchev


On 8/15/05, stephan@xxxxxxxxxx <stephan@xxxxxxxxxx> 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><r
ating>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[@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>
>
>


--
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.