|
[XSL-LIST Mailing List Archive Home]
[By Thread]
[By Date]
[Recent Entries]
[Reply To This Message]
Re: Converting excel columns/rows to XML using XSL
Subject: Re: [xsl] Converting excel columns/rows to XML using XSL
From: Irina Simpson <irina@xxxxxxxxxxxx>
Date: Fri, 31 Jan 2003 16:11:41 -0800
|
Bryan,
Thanks for you suggestion. It didn't work for me even with minor
modifications, but it gave me some ideas. Here is what did work.
<?xml version='1.0'?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" />
<xsl:template match="/">
<forecast xmlns="http://www.vivecon.com"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >
<xsl:variable name="name"
select="ss:Workbook/ss:Worksheet/ss:Table/ss:Row/ss:Cell[ss:NamedCell[@ss:Name='name']]">
</xsl:variable>
<xsl:if test="string-length($name)>0">
<name> <xsl:value-of select="$name"/> </name>
</xsl:if>
<!-- iterate over all rows, you might include the check for
non-spaced values -->
<xsl:for-each select="ss:Workbook/ss:Worksheet/ss:Table/ss:Row">
<xsl:variable name="date"
select="ss:Cell[ss:NamedCell/@ss:Name='yearMonth']"> </xsl:variable>
<xsl:if test="string-length($date)>0">
<yearMonth> <xsl:value-of select="$date"/> </yearMonth>
<quantity><xsl:value-of
select="ss:Cell[ss:NamedCell/@ss:Name='quantity']"/></quantity>
</xsl:if>
</xsl:for-each>
</forecast>
</xsl:template>
</xsl:stylesheet>
-Irina.
bryan wrote:
Okay, I haven't tested this but it should be pretty close to what you
wanted.
=== 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"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:template match="/">
<forecast
xmlns="http://www.vivecon.com">
<name> <xsl:apply-templates
select="ss:Workbook/ss:Worksheet/ss:Table"/>
</name>
</forecast>
</xsl:template>
<xsl:template match="ss:Table">
<xsl:apply-templates select="Row[position() > 1]"/>
</xsl:template>
<xsl:template match="Row">
<timeQuantity>
<xsl:apply-templates/>
</timeQuantity>
</xsl:template>
<xsl:template match="Cell[Data]">
<xsl:variable name="NamedCellValue" select="NamedCell/@ss:Name"/>
<xsl:element name="{$NamedCellValue}"><xsl:value-of
select="Data"/></xsl:element>
</xsl:template>
<xsl:template match="*"/>
</xsl:stylesheet>
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
 |
PURCHASE STYLUS STUDIO ONLINE TODAY!
Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!
Download The World's Best XML IDE!
Accelerate XML development with our award-winning XML IDE - Download a free trial today!
Subscribe in XML format
| RSS 2.0 |
|
| Atom 0.3 |
|
|
Cart
