XSLT for reading Excell Spreadsheet xml

[rsundare]

I would appreciate if someone can help me in reading the data from an xml of an Excell spreadsheet. I would like to know how I can traverse through the multiple element Cell for the element Row based on the value of the attribute for the element NamedCell. Given below is a sample of the xml of an Excel spreadsheet. Thanks. -Ram <?xml version="1.0" encoding="UTF-8"?> <Table p1:DefaultColumnWidth="" p1:DefaultRowHeight="15" p1:ExpandedColumnCount="4" p1:ExpandedRowCount="5" p1:StyleID="" p2:FullColumns="1" p2:FullRows="1" xmlns="urn:schemasmicrosoftcom:office:spreadsheet" xmlns:p1="urn:schemasmicrosoftcom:office:spreadsheet" xmlns:p2="urn:schemasmicrosoftcom: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> </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> </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> </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> </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> </Row> </Table>

[Martin Honnen]

If you want to access "Cell" elements based on their NamedCell child element you could define a key:

<xsl:stylesheet  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  xmlns:ss="urn:schemasmicrosoftcom:office:spreadsheet"  version="1.0">    <xsl:param name="cell-name" select="'Price'"/>    <xsl:output method="text"/>  <xsl:strip-space elements="*"/>    <xsl:key name="k1" match="ss:Cell" use="ss:NamedCell/@ss:Name"/>    <xsl:template match="/">	<xsl:for-each select="key('k1', $cell-name)">	  <xsl:value-of select="ss:Data"/>	  <xsl:text></xsl:text>	</xsl:for-each>     </xsl:template></xsl:stylesheet>

That sample stylesheet, when run against your sample input, produces the following output:

Price45445424578

[rsundare]

Thank you Martin. I would like to recursively read the repeated cell NamedCell and Cell and get the result as given below. I guess I can use the key once I am able to read the repeated nodes Cell and NamedCell recursively. I would appreciate if you could help me with this. Thanks. - Ram<row> <name>Name</name> <price>Price</price></row><row> <name>qweq</name> <price>454</price></row><row> <name>aadd</name> <price>4542</price></row><row> <name>dsd</name> <price>78</price></row>

[Martin Honnen]

I am not sure why you insist on "recursively" reading stuff but the following stylesheet produces the output you have shown, with the exception of the case of element names:

<xsl:stylesheet  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  xmlns:ss="urn:schemasmicrosoftcom:office:spreadsheet"  exclude-result-prefixes="ss"  version="1.0">    <xsl:output method="xml" indent="yes"/>  <xsl:strip-space elements="*"/>    <xsl:variable name="row1" select="descendant::ss:Row[1]"/>    <xsl:template match="/">	<xsl:apply-templates select="descendant::ss:Row"/>    </xsl:template>    <xsl:template match="ss:Row">	<row>	  <xsl:apply-templates select="ss:Cell"/>	</row>  </xsl:template>    <xsl:template match="ss:Cell">	<xsl:element name="{$row1/ss:Cell[ss:NamedCell/@ss:Name = current()/ss:NamedCell/@ss:Name]/ss:NamedCell/@ss:Name}">	  <xsl:value-of select="ss:Data"/>	</xsl:element>  </xsl:template></xsl:stylesheet>

[rsundare]

Thank you again, Martin. I tweaked the code you had given to get the element names based on the attribute value of the element NamedCell as given below. However, the result comes out unformatted in 1 line instead of each element in a new line. I would appreciate if you could help me with having a new line. I tried using the tag text, but it didn't work.<?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" exclude-result-prefixes="ss"><xsl:output method="xml" indent="yes"/> <!--<xsl:strip-space elements="*"/>--> <!--<xsl:variable name="row1" select="descendant::ss:Row[1]"/>--> <xsl:template match="/"> <xsl:apply-templates select="descendant::ss:Row"/> </xsl:template> <xsl:template match="ss:Row"><result><resultSets><resultSet> <row> <xsl:apply-templates select="//ss:NamedCell"/> </row></resultSet></resultSets></result> </xsl:template> <xsl:template match="//ss:NamedCell"> <xsl:element name="{@ss:Name}"> <xsl:value-of select="../ss:Data"/> </xsl:element> </xsl:template></xsl:stylesheet>

[Martin Honnen]

Which XSLT processor exactly do you use? How exactly do you use it? The indent="yes" on the xsl: output element should take care of indenting the result when it is serialized.

[rsundare]

Thanks again, Martin. There was a mistake in my earlier code. I thought, for the benefit of someone looking at this post later on I will post the code that works fine. The code results in having the results of each row in a Excel Spreadsheet based on the NamedCell.<?xml version='1.0'?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" exclude-result-prefixes="ss"><xsl:output method="xml" media-type="string" indent="yes"/><!--<xsl:output method="xml" indent="yes"/>--> <xsl:strip-space elements="*"/> <!--<xsl:variable name="row1" select="descendant::ss:Row[1]"/>--> <xsl:template match="/"> <xsl:apply-templates select="//ss:Row"/> </xsl:template> <xsl:template match="//ss:Row"> <xsl:if test="position() > 1"> <xsl:text></xsl:text> <xsl:element name="row"> <xsl:text> </xsl:text> <xsl:apply-templates select="ss:Cell"/> <xsl:text> </xsl:text> </xsl:element> <xsl:text> </xsl:text> </xsl:if> </xsl:template> <xsl:template match="//ss:Cell"> <xsl:element name="{ss:NamedCell[position()]/@ss:Name}"> <xsl:value-of select="ss:Data"/> </xsl:element> <xsl:text> </xsl:text> </xsl:template></xsl:stylesheet>