[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

Converting excel columns/rows to XML using XSL

Subject: Converting excel columns/rows to XML using XSL
From: Irina Simpson <irina@xxxxxxxxxxxx>
Date: Thu, 30 Jan 2003 11:46:31 -0800
converting excel to xml
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

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.