RE: Problem Transforming Spreadsheet ML

From: Andy Carr1 <CARRA@xxxxxxxxxx>
Reply-To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
Subject:  Problem Transforming Spreadsheet ML
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