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

Subject: RE: [xsl] Problem transforming Excel XML Workbook (or how to implement one-more-counter-yet in XSLT)
From: "Nathan Young -X \(natyoung - Artizen at Cisco\)" <natyoung@xxxxxxxxx>
Date: Wed, 2 Nov 2005 10:47:45 -0800
I have a possible avenue that's not totally on topic for the list, but
excel lets you define XML maps that can give you a bit more flexibility
with the output XML...  You can't always get what you want with it but
you could probably get a lot closer than excel's XML output as shown


> -----Original Message-----
> From: Nacho Jimenez [mailto:nacho.jimenez@xxxxxxxxx]
> Sent: Wednesday, November 02, 2005 3:12 AM
> 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.

Current Thread