I have the xml from an Excel worksheet as given below. I would like to have a xslt to parse and extract the Excel rows in asimple xml format as given below based on the data picked from the named cells. I was able to collect data for each row. But I would like to have the data for all the rows together with separate element nodes for rows. Can someone please help me. Thanks - Ram
XML output should be like -
<row>
<Name>....</Name>
<Price>.....</Price>
<Unit>....</Unit>
<Total>....</Total>
</row>
<row>
<Name>....</Name>
<Price>.....</Price>
<Unit>....</Unit>
<Total>....</Total>
</row>
The Excel XML data is as given below.
<?xml version="1.0" encoding="UTF-8"?>
- <Worksheet p1:Name="Sheet1" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:p1="urn:schemas-microsoft-com:office:spreadsheet">
- <Table p1:DefaultColumnWidth="" p1:DefaultRowHeight="15" p1:ExpandedColumnCount="4" p1:ExpandedRowCount="5" p1:StyleID="" p2:FullColumns="1" p2:FullRows="1" xmlns:p2="urn:schemas-microsoft-com:office:excel">
- <Row p1:AutoFitHeight="" p1:Height="" p1:Index="" p1:StyleID="">
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="String">Name</Data>
<NamedCell p1:Name="Name"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="String">Price</Data>
<NamedCell p1:Name="Price"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="String">Unit</Data>
<NamedCell p1:Name="Unit"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="String">Total</Data>
<NamedCell p1:Name="Total"/>
</Cell>
</Row>
- <Row p1:AutoFitHeight="" p1:Height="" p1:Index="" p1:StyleID="">
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="String">qweq</Data>
<NamedCell p1:Name="Name"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">454</Data>
<NamedCell p1:Name="Price"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">2</Data>
<NamedCell p1:Name="Unit"/>
</Cell>
- <Cell p1:Formula="=RC[-2]*RC[-1]" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">908</Data>
<NamedCell p1:Name="Total"/>
</Cell>
</Row>
- <Row p1:AutoFitHeight="" p1:Height="" p1:Index="" p1:StyleID="">
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="String">aadd</Data>
<NamedCell p1:Name="Name"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">4542</Data>
<NamedCell p1:Name="Price"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">3</Data>
<NamedCell p1:Name="Unit"/>
</Cell>
- <Cell p1:Formula="=RC[-2]*RC[-1]" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">13626</Data>
<NamedCell p1:Name="Total"/>
</Cell>
</Row>
- <Row p1:AutoFitHeight="" p1:Height="" p1:Index="" p1:StyleID="">
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="String">Xsad</Data>
<NamedCell p1:Name="Name"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">45</Data>
<NamedCell p1:Name="Price"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">4</Data>
<NamedCell p1:Name="Unit"/>
</Cell>
- <Cell p1:Formula="=RC[-2]*RC[-1]" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">180</Data>
<NamedCell p1:Name="Total"/>
</Cell>
</Row>
- <Row p1:AutoFitHeight="" p1:Height="" p1:Index="" p1:StyleID="">
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="String">dsd</Data>
<NamedCell p1:Name="Name"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">78</Data>
<NamedCell p1:Name="Price"/>
</Cell>
- <Cell p1:Formula="" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">54</Data>
<NamedCell p1:Name="Unit"/>
</Cell>
- <Cell p1:Formula="=RC[-2]*RC[-1]" p1:Index="" p1:MergeAcross="" p1:MergeDown="" p1:StyleID="">
<Data p1:Type="Number">4212</Data>
<NamedCell p1:Name="Total"/>
</Cell>
</Row>
</Table>
- <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
- <PageSetup>
<Header p2:Margin="0.3" xmlns:p2="urn:schemas-microsoft-com:office:excel"/>
<Footer p2:Margin="0.3" xmlns:p2="urn:schemas-microsoft-com:office:excel"/>
<PageMargins p2:Bottom="0.75" p2:Left="0.7" p2:Right="0.7" p2:Top="0.75" xmlns:p2="urn:schemas-microsoft-com:office:excel"/>
</PageSetup>
<Zoom/>
<PageBreakZoom/>
<Selected/>
<DoNotDisplayGridlines/>
<TopRowVisible/>
- <Panes>
- <Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
<ActiveCol>3</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
