[xsl] Problem Transforming Spreadsheet ML

Subject: [xsl] Problem Transforming Spreadsheet ML
From: Andy Carr1 <CARRA@xxxxxxxxxx>
Date: Mon, 12 Feb 2007 13:22:35 +0000
Hi Everyone

I have a problem transforming and Excel spreadsheet into more readable 
XML.

Some example XML from the spreadsheet follows:

<Workbook>
 <DocumentProperties/>
 <ExcelWorkbook/>
 <Styles/>
 <Worksheet ss:Name="Release A">
  <Table>
   <Row>
    <Cell><Data ss:Type="String">HeaderFooter</Data></Cell>
    <Cell ss:Index="3" ss:StyleID="s23"><Data 
ss:Type="String">Font</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">Times New 
Roman</Data></Cell>
    <Cell ss:Index="6" ss:StyleID="s23"><Data 
ss:Type="String">Font</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">Trebuchet 
MS</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="3" ss:StyleID="s25"><Data 
ss:Type="String">Size</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">10</Data></Cell>
    <Cell ss:Index="6" ss:StyleID="s25"><Data 
ss:Type="String">Size</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">16</Data></Cell>
   </Row>
   <Row ss:Height="15">
    <Cell ss:Index="3" ss:StyleID="s25"><Data 
ss:Type="String">Colour</Data></Cell>
    <Cell ss:StyleID="s26"><Data ss:Type="String">0, 0, 0</Data></Cell>
    <Cell ss:Index="6" ss:StyleID="s25"><Data 
ss:Type="String">Colour</Data></Cell>
    <Cell ss:StyleID="s26"><Data ss:Type="String">255, 0, 0</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">ProductLine</Data></Cell>
    <Cell ss:Index="3" ss:StyleID="s23"><Data 
ss:Type="String">Font</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">Times New 
Roman</Data></Cell>
    <Cell ss:Index="6" ss:StyleID="s23"><Data 
ss:Type="String">Font</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">Trebuchet 
MS</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="3" ss:StyleID="s25"><Data 
ss:Type="String">Size</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">18</Data></Cell>
    <Cell ss:Index="6" ss:StyleID="s25"><Data 
ss:Type="String">Size</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">20</Data></Cell>
   </Row>
   <Row ss:Height="45">
    <Cell ss:Index="3" ss:StyleID="s25"><Data 
ss:Type="String">Colour</Data></Cell>
    <Cell ss:StyleID="s26"><Data ss:Type="String">0, 128, 0</Data></Cell>
    <Cell ss:Index="6" ss:StyleID="s35"><Data 
ss:Type="String">Colour</Data></Cell>
    <Cell ss:StyleID="s36"><Data ss:Type="String">128, 0, 
128</Data></Cell>
   </Row>
  </Table>
 <Worksheet>
<Workbook>

The spreadsheet contains data for 2 report styles, referenced by the 
SS:index of 3 and 6 found in each row.

The xml output I require is this:

<Styles>
  <Report1>
    <HeaderFooter> 
      <Font>Times New Roman</Font>
      <Size>10</Size>
      <Colour>0, 0, 0</Colour>
    <HeaderFooter>
    <ProductLine> 
      <Font>Times New Roman</Font>
      <Size>18</Size>
      <Colour>0, 128, 0</Colour>
    <ProductLine>
  </Report1>
  <Report2>
    <HeaderFooter> 
      <Font>Trebuchet MS</Font>
      <Size>16</Size>
      <Colour>255, 0, 0</Colour>
    <HeaderFooter>
    <ProductLine> 
      <Font>Trebuchet MS</Font>
      <Size>20</Size>
      <Colour>128, 0, 128</Colour>
    <ProductLine>
  </Report2
</Styles>

I've had some success using for-each on the Rows and then for-each-ing the 
Cells but I can't figure out how to generate the higher level tags only 
once as the for-each-ing produces the tags each time round the loop.

Any help would be greatly appreciated.

Regards
Andy

Andy Carr
IT Specialist
Tel: Internal - 298037 External - 01252 558037
Mail Point  M1C IBM Application Services
Meudon House, Meudon Avenue, Farnborough, GU14 7NB
(Notes) Andy Carr1/UK/IBM@IBMGB 
(Internet)CARRA@xxxxxxxxxx


Unless stated otherwise above:
IBM United Kingdom Limited - Registered in England and Wales with number 
741598. 
Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU

Current Thread