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

RE: Problem transforming Excel XML Workbook (or how to

Subject: RE: Problem transforming Excel XML Workbook (or how to implement one-more-counter-yet in XSLT)
From: "Michael Kay" <mike@xxxxxxxxxxxx>
Date: Wed, 2 Nov 2005 12:44:45 -0000
excel get row
Yes, Microsoft didn't design this to make your life easy. I think it needs a
sibling recursion something like

<xsl:function name="excel:get-cell" as="element(Cell)?">
  <xsl:param name="row" as="element(Row)"/>
  <xsl:param name="cellNr" as="xs:integer"/>
  <xsl:sequence select="excel:get-sibling-cell(Cell[1], (Cell[1]/@ss:Index,
1)[1], cellNr)"/> 
</xsl:function>

<xsl:function name="excel:get-sibling-cell" as="element(Cell)?">
  <xsl:param name="current-cell" as="element(Cell)?"/>
  <xsl:param name="current-index" as="xs:integer"/>
  <xsl:param name="required-index" as="xs:integer"/>
  <xsl:sequence select="
     if (exists($current-cell)) then
        if ($current-index eq $required-index) then
           $current-cell
        else
           excel:get-sibling-cell($current-cell/following-sibling::Cell,
                    ($current-cell/following-sibling::Cell/@ss:index,
$current-index + 1),
                     $required-index)
    else ()"/>
</xsl:function>

You can do the same thing using recursive templates in XSLT 1.0 if you need
to.

Michael Kay
http://www.saxonica.com/


> -----Original Message-----
> From: Nacho Jimenez [mailto:nacho.jimenez@xxxxxxxxx] 
> Sent: 02 November 2005 11:12
> To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> Subject:  Problem transforming Excel XML Workbook (or 
> how to implement one-more-counter-yet in XSLT)
> 
> Dear all,
> 
> I'm stuck like glue trying to get data out of an XML Workbook in
> Excel, and can't find a solution without recursing to a
> procedural-style counter.. Let me explain the situation i'm stuck in:
> 
> Excel produces an XML file with lots of spurious content, but in the
> middle of it, i get the following nodes:
> [....]
>    <Row>
>     <Cell><Data ss:Type="String">E-TF-010-16425</Data></Cell>
>     <Cell><Data ss:Type="String">B38379715</Data></Cell>
>     <Cell><Data ss:Type="String">X</Data></Cell>
>     <Cell><Data ss:Type="String">S</Data></Cell>
>     <Cell><Data ss:Type="Number">11144.55</Data></Cell>
>     <Cell ss:Index="16"><Data ss:Type="String">AGP</Data></Cell>
>     <Cell><Data ss:Type="String">Po</Data></Cell>
>    </Row>
>    <Row>
>     <Cell><Data ss:Type="String">E-TF-012-09930</Data></Cell>
>     <Cell><Data ss:Type="String">B38037511</Data></Cell>
>     <Cell><Data ss:Type="String">X</Data></Cell>
>     <Cell><Data ss:Type="String">S</Data></Cell>
>     <Cell ss:StyleID="s45"><Data 
> ss:Type="Number">15734.4</Data></Cell>
>     <Cell><Data ss:Type="String">X</Data></Cell>
>     <Cell ss:Index="16"><Data ss:Type="String">AGP</Data></Cell>
>     <Cell><Data ss:Type="String">Po</Data></Cell>
>    </Row>
>   [...]
> 
> The idea behind this file seems to be that you dont need to declare a
> cell if it's empty, instead, the next full cell is declared with an
> Index attribute so you can see where you are.
> 
> So in the example, column 1 in row 1 has value "E-TF-010-16425",
> coulmn 2 "B38379715", and so on. But then, columns 6 to 15 are empty,
> column 16 has value "AGP" and column 17, "Po".
> 
> The second row is slightly different, as there's another filled column
> in the middle. I do not now from beforehand how many cells are going
> to be empty or not.
> 
> I need to produce something like:
> 
> <programas>
>    <name>E-TF-012-09930</name>
>    <line>Po</line>
>    <company>AGP</company>
> </programas>
> 
> Trouble is, I know the data I want is stored in Cell 17 of the
> spreadsheet, but I'm not able to see where is Cell 17 from an xpath
> expression, so it's getting really difficult.
> 
> I thought about doing two transformations to the file, and having an
> intermediate XML file like this:
> <item>
>  <col number="1">E-TF-012-09930</number>
>  <col number="2">B35467946</number>
>  <col number="3">X</number>
>  <col number="4">S</number>
>  <col number="5">1144.55</number>
>  <col number="6"/>
>  <col number="7"/>
>  <col number="8"/>
>  <col number="9"/>
>  <col number="10"/>
>  <col number="11"/>
>  <col number="12"/>
>  <col number="13"/>
>  <col number="14"/>
>  <col number="15"/>
>  <col number="16">AGP</number>
>  <col number="17">Po</number>
> </item>
> 
> But for that i'll need procedural variables in XSLT, or a named
> template which remembers values from previous calls, and both are
> imposible. Any idea.. I'm in a BIG hurry here.
> 
> 
> Thanks to all.

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.