xsl-list
[Top] [All Lists]

Re: Converting excel columns/rows to XML using XSL

2003-01-31 17:11:41
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[(_at_)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() &gt; 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



<Prev in Thread] Current Thread [Next in Thread>