RE: [xsl] Problem Transforming Spreadsheet ML

["Joe Fawcett" <joefawcett@xxxxxxxxxxx>]

Andy

Well without seeing the XSLT I can only guesss but in general you have to 
move the creation of the one-off elements outside of the xsl:for-each. It 
sounds like you might be better using xsl:apply-templates rather than 
xsl:for-each, then you can wrap the xsl:apply-templates with the code to 
create the on-off elements.

Joe


> From: Andy Carr1 <CARRA@xxxxxxxxxx>
> Reply-To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [xsl] 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