[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

RE: Normalizing/Simplify MS-Excel XML

Subject: RE: Normalizing/Simplify MS-Excel XML
From: stephan@xxxxxxxxxx
Date: Tue, 16 Aug 2005 19:07:34 +0800
excel xml encoding
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 &lt;= 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>

Current Thread

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.