467,151 Members | 1,094 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,151 developers. It's quick & easy.

Problem to convert xml to excel sheet using xslt

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
Jul 26 '07 #1
  • viewed: 8473
Share:
2 Replies
jkmyoung
Expert 2GB
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?
Jul 26 '07 #2
inspireuk
Just an idea - Excel allows html tables as a valid input. it maybe easier to format your data into that.

Josh
Aug 9 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
9 posts views Thread by Connull | last post: by
2 posts views Thread by solex | last post: by
2 posts views Thread by noopathan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.