[XSL-LIST Mailing List Archive Home] [By Thread] [By Date]

RE: [xsl] Problem transforming Excel XML Workbook (or how to implement one-more-counter-yet in XSLT)

---

---

Hi,

I had same problem earlier.

You can check My solution at

http://www.biglist.com/cgi-bin/wilma/wilma_hiliter/xsl-list/200508/msg00
501.html?line=11#hilite

Regards
Kaarle



-----Original Message-----
From: Nacho Jimenez [mailto:nacho.jimenez@xxxxxxxxx]
Sent: 2. marraskuuta 2005 13:12
To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
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.

---