Problem to convert xml to excel sheet using xslt

[Ch Pravin]

Hi All:

I am having the following xml which i need to convert to excel using xslt.
Please help me out.

Afghanistan.xml

<?xml version="1.0" encoding="utf-16"?>
<Languages BuildVersion="1,5,0815,0 " CountryName="Afghanistan" >
<Language locale="English">
<Office languageSKU="Arabic">
<Product name="Office Professional 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
<Product name="Office Enterprise 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
<Product name="Office Small Business 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
</Office>
<Office languageSKU="English">
<Product name="Office Professional 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
<Product name="Office Enterprise 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
<Product name="Office Small Business 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
</Office>
</Language>
<Language locale="Arabic">
<Office languageSKU="Arabic">
<Product name="Office Professional 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
<Product name="Office Enterprise 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
<Product name="Office Small Business 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
</Office>
<Office languageSKU="English">
<Product name="Office Professional 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
<Product name="Office Enterprise 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
<Product name="Office Small Business 2007" Result="PASS" ErrorsIfAny="NONE">
</Product>
</Office>
</Language>
</Languages>






I am using the following xslt code:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<xsl:template match="/">

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="StyleBorder">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="StyleTableHeader">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"/>
</Borders>
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font ss:Bold="1"/>
</Style>
<Style ss:ID="StyleRightBorder">
<Alignment ss:Wra<?xml version="1.0" encoding="UTF-8" ?>
pText="1"/>
<Borders>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>
</Borders>
</Style>
<Style ss:ID="StyleWrapText" >
<Alignment ss:WrapText="1"/>
</Style>
<Style ss:ID="StyleRightAlign">
<Alignment ss:Horizontal="Right" />
</Style>
</Styles>
<Worksheet ss:Name="UrlCorpus">
<Table>
<Column ss:AutoFitWidth="0" ss:Width="100" />
<Column ss:AutoFitWidth="0" ss:Width="100" />
<Column ss:AutoFitWidth="0" ss:Width="100" />
<Column ss:AutoFitWidth="0" ss:Width="200" />
<Column ss:AutoFitWidth="0" ss:Width="100" />
<Column ss:AutoFitWidth="0" ss:Width="100" />

<Row ss:Height="20">
<Cell ss:StyleID="StyleTableHeader">
<Data ss:Type="String">CountryName</Data>
</Cell>
<Cell ss:StyleID="StyleTableHeader">
<Data ss:Type="String">Toggle Language</Data>
</Cell>

<Cell ss:StyleID="StyleTableHeader">
<Data ss:Type="String">Language Name</Data>
</Cell>

<Cell ss:StyleID="StyleTableHeader">
<Data ss:Type="String">Product Name</Data>
</Cell>
<Cell ss:StyleID="StyleTableHeader">
<Data ss:Type="String">Result</Data>
</Cell>
<Cell ss:StyleID="StyleTableHeader">
<Data ss:Type="String">ErrorsIfAny</Data>
</Cell>
</Row>

<xsl:apply-templates/>
<xsl:apply-templates select="Language/Office/Product"/>

<!-- Original Code for producing output using original xml
<Row ss:Height="15">
<xsl:for-each select="/*">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="@CountryName"/>
</Data>
</Cell>
</xsl:for-each>
<xsl:for-each select="/*/*">
<Cell>

<Data ss:Type="String">
<xsl:value-of select="@locale"/>
</Data>
</Cell>
</xsl:for-each>
<xsl:for-each select="/*/*/*">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="@languageSKU"/>
</Data>
</Cell>
</xsl:for-each>
<xsl:for-each select="/*/*/*/*">

<Cell>
<Data ss:Type="String">
<xsl:value-of select="@name"/>
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="@Result"/>
</Data>
</Cell>

<Cell>
<Data ss:Type="String">
<xsl:value-of select="@ErrorsIfAny"/>
</Data>
</Cell>

</xsl:for-each>
</Row>

-->
</Table>


</Worksheet>
</Workbook>
</xsl:template>



<xsl:template match="Language/Office/Product">
<Row ss:Height="15">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="./././@CountryName"/>
</Data>
</Cell>

<Cell>
<Data ss:Type="String">
<xsl:value-of select="././@LanguageLocale"/>
</Data>
</Cell>


<Cell>
<Data ss:Type="String">
<xsl:value-of select="./@OfficeLanguageSKU"/>
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="@ProductName"/>
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="@Result"/>
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="@ErrorsIfAny"/>
</Data>
</Cell>

</Row>
</xsl:template>

</xsl:stylesheet>


I require the excel sheet in the following format:


Country Name| LangugeLocale | OfficeLanguageSKU | Product Name | Result | ErrorsIfAny

Afg | English | Arabic | Office Pro 2007 | Pass | None
Afg | English | Arabic | Office Enterprise 2007 | Pass | None
Afg | English | Arabic | Office Small Biz 2007 | Pass | None
Afg | English | English | Office Pro 2007 | Pass | None
Afg | English | English | Office Enterprise 2007 | Pass | None
Afg | English | English | Office Small Biz 2007 | Pass | None
Afg | Arabic | Arabic | Office Pro 2007 | Pass | None
Afg | Arabic | Arabic | Office Enterprise 2007 | Pass | None
Afg | Arabic | Arabic | Office Small Biz 2007 | Pass | None
Afg | English | Arabic | Office Pro 2007 | Pass | None
Afg | English | Arabic | Office Enterprise 2007 | Pass | None
Afg | English | Arabic | Office Small Biz 2007 | Pass | None


The Problem i am facing with the current code is that all the attribute values are not coming in their proper rows.Please help me out as soon as possible.Its urgent.

Thanks and Regards,

Rahul

[jkmyoung]

What do you mean by that? Your Product template looks alright.
The last 3 data rows in your result are suspicious.
Afg | English | Arabic | Office Pro 2007 | Pass | None
Afg | English | Arabic | Office Enterprise 2007 | Pass | None
Afg | English | Arabic | Office Small Biz 2007 | Pass | None
As Arabic should probably be replaced with English and vice-versa.

How are you expecting your data to appear?

[inspireuk]

Just an idea - Excel allows html tables as a valid input. it maybe easier to format your data into that.

Josh