473,219 Members | 1,784 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,219 software developers and data experts.

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
2 8874
jkmyoung
2,057 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
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

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

Similar topics

13
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet...
9
by: Connull | last post by:
I have extracted data from a SQL database and need to convert it to a csv. I have transformed the data to xml and then save it with .xls extension which is viewable in Excel. As soon as I try save...
2
by: solex | last post by:
Hello, Is there a way to develop a style sheet that runs on the client that will open the file in excel? Thanks, Dan
1
by: RickH | last post by:
..Cells(1,y).GetType ownly shows instead of .Value, .Copy, etc. The code below is derived from samples, it should work, but I've messed up somewhere... Imports System.Windows.Forms Imports...
1
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns...
2
by: noopathan | last post by:
Hi experts , I have an excel file in the below format --------------------------------------------------------------------- CodeID CodeName Market Name Date...
4
by: sadc1986 | last post by:
How does one introduce multiple worksheets in a excel using Xslt Transforms my code... Please suggest <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"...
11
by: poolboi | last post by:
hi guys, below is a script that i need yr help on i got this to print out a csv to excel i'm trying to modify it to read a .txt file to convert an excel hm...doesn't seem to work still any idea...
6
by: Gilbert Tordeur | last post by:
Hello ! My web application generates an Excel sheet on the server. Then I would like to generate a pdf file from this Excel sheet. How can I do ? Thank you for your help, Gilbert
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.