Subject: Re: [xsl] Normalizing/Simplify MS-Excel XML From: Dimitre Novatchev <dnovatchev@xxxxxxxxx> Date: Wed, 17 Aug 2005 06:12:29 +1000 |
> 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.
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
[xsl] Normalizing/Simplify MS-Excel, stephan | Thread | RE: [xsl] Normalizing/Simplify MS-E, Kaila Kaarle |
RE: [xsl] Problem with sum function, cknell | Date | RE: [xsl] Problem with sum function, Michael Kay |
Month |