[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message] RE: Normalizing/Simplify MS-Excel XML
Hi Kaila, thanks so mutch, works like a charm! My stylesheet takes the name of a worksheet as a parameter and pulls the values into a easier structure for further import (I asume that the first row does have the field names and no "holes"). For future readers I include my sheet below: <?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/e:Row"/> </xsl:template> <xsl:template match="ss:Row"> <xsl:if test="not(ss:Cell[1]/@ss:Index)"> <!-- We do NOT process records where the first cell is empty --> <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:if> </xsl:template> <xsl:template match="ss:Cell"> <xsl:variable name="curCol"> <xsl:choose> <xsl:when test="@ss:Index"> <xsl:value-of select="@ss:Index"/> </xsl:when> <xsl:otherwise> <xsl:call-template name=" cell-index"> <xsl:with-param name="idx" select="1"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:variable> <xsl:element name="field"> <xsl:attribute name="col"><xsl:value-of select=" $curCol"/></xsl:attribute> <xsl:attribute name="name"><xsl:value-of select=" normalize-space(ancestor::e:Table/e:Row[position()=1]/e:Cell[position()=$curCol]) "/></xsl:attribute> <xsl:value-of select="ss:Data"/> </xsl:element> </xsl:template> <xsl:template name="cell-index"> <xsl:param name="idx"/> <xsl:if test="$idx <= position()"> <xsl:choose> <xsl:when test=" preceding-sibling::ss:Cell[position()=$idx]/@ss:Index"> <xsl:value-of select=" preceding-sibling::ss:Cell[position()=$idx]/@ss:Index +$idx"/> </xsl:when> <xsl:when test="$idx = position()"> <xsl:value-of select="$idx"/> </xsl:when> <xsl:otherwise> <xsl:call-template name=" cell-index"> <xsl:with-param name="idx" select="$idx+1"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:if> </xsl:template> </xsl:stylesheet> To be very honest: I do not fully understand how the recursive call to cell-index is actually working. If a kind soul could enlighten me on that one, that would be great. :-) stw "Kaila Kaarle" <Kaarle.Kaila@xxxxxxx> wrote on 15/08/2005 20:28:47: > hi, > > I had the same task a while ago and you can find my solution at: > > http://www.kk-software.fi/kalle/xslt/fromExcel.xsl > > It fills all empty cells but doesn't do anything with empty rows > as I'm not interested in them. > > regards > Kaarle > > -----Original Message----- > From: stephan@xxxxxxxxxx [mailto:stephan@xxxxxxxxxx] > Sent: 15. elokuuta 2005 14:36 > To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx > Subject: Normalizing/Simplify MS-Excel XML > > > 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[@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>
|
PURCHASE STYLUS STUDIO ONLINE TODAY!Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced! Download The World's Best XML IDE!Accelerate XML development with our award-winning XML IDE - Download a free trial today! Subscribe in XML format
|