[xsl] Converting excel columns/rows to XML using XSL

Subject: [xsl] Converting excel columns/rows to XML using XSL
From: Irina Simpson <irina@xxxxxxxxxxxx>
Date: Thu, 30 Jan 2003 11:46:31 -0800
I am looking for help converting some Excel XP data from a saved Excel’s XML file to another XML file via XSL. I’m fairly new to this, so your assistance will be much appreciated.

There’re two columns worth of data:
yearMonth Quantity
2002-01 10
2002-02 8
2002-03 3
2002-04 5
2002-05 3
2002-06 4
2002-07 4

The way I know I reached the last row is that yearMonth column is blank. A non-blank yearMonth with blank Quantity is ok.

I would like to convert the data into the following XML piece:
<timeQuantity>
<yearMonth> 2002-01 </yearMonth>
<quantity> 10 </quantity>
</timeQuantity>

<timeQuantity>
<yearMonth> 2002-02 </yearMonth>
<quantity> 8 </quantity>
</timeQuantity>

Below is a piece of a saved Excel file and the xsl I have so far. I’m using named cells to get to various values. I’m not sure how to apply that approach here. I currently have a name for yearMonths cells and the quantity cells. I’m not sure if it would be better to name the entire block.

=== Excel piece: ===
<Names>
<NamedRange ss:Name="name" ss:RefersTo="=Sheet1!R2C1"/>
<NamedRange ss:Name="quantity" ss:RefersTo="=Sheet1!R2C4:R10C4"/>
<NamedRange ss:Name="yearMonth" ss:RefersTo="=Sheet1!R2C3:R10C3"/>
</Names>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="10" x:FullColumns="1"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="67.5"/>
<Row>
<Cell ss:Index="3"><Data ss:Type="String">yearMonth</Data></Cell>
<Cell><Data ss:Type="String">quantity</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s25"><Data ss:Type="String">Forecast Name</Data><NamedCell
ss:Name="name"/></Cell>
<Cell ss:Index="3" ss:StyleID="s21"><Data ss:Type="String">2002-01</Data><NamedCell
ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s22"><Data ss:Type="Number">10</Data><NamedCell
ss:Name="quantity"/></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s21"><Data ss:Type="String">2002-02</Data><NamedCell
ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="Number">8.29</Data><NamedCell
ss:Name="quantity"/></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s21"><Data ss:Type="String">2002-03</Data><NamedCell
ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="Number">3</Data><NamedCell
ss:Name="quantity"/></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s21"><Data ss:Type="String">2002-04</Data><NamedCell
ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="Number">4.9</Data><NamedCell
ss:Name="quantity"/></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s21"><Data ss:Type="String">2002-05</Data><NamedCell
ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="Number">3</Data><NamedCell
ss:Name="quantity"/></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s21"><NamedCell ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s23"><NamedCell ss:Name="quantity"/></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s21"><NamedCell ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s24"><NamedCell ss:Name="quantity"/></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s21"><NamedCell ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s23"><NamedCell ss:Name="quantity"/></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s21"><NamedCell ss:Name="yearMonth"/></Cell>
<Cell ss:StyleID="s24"><NamedCell ss:Name="quantity"/></Cell>
</Row>
</Table>


===xsl===
<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>
<xsl:template match="/">
<forecast
xmlns="http://www.vivecon.com";
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >


<name> <xsl:value-of select="ss:Workbook/ss:Worksheet/ss:Table/ss:Row/ss:Cell[ss:NamedCell[@ss:Name='name']]"/> </name>

</forecast>
</xsl:template>
</xsl:stylesheet>

Thank you,
-Irina.


XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list



Current Thread