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(_dot_)jimenez(_at_)gmail(_dot_)com]
Sent: 02 November 2005 11:12
To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject: [xsl] 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.
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail:
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--