By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,501 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,501 IT Pros & Developers. It's quick & easy.

Problem to convert xml to excel sheet using xslt

P: 1
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
Share this Question
Share on Google+
2 Replies


jkmyoung
Expert 100+
P: 2,057
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
P: 12
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.