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(_at_)IBMGB
(Internet)CARRA(_at_)uk(_dot_)ibm(_dot_)com
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
--~------------------------------------------------------------------
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>
--~--