Problem to convert xml to excel sheet using xslt

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Ch Pravin
    New Member
    • Jul 2007
    • 1
    #1

    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="Af ghanistan" >
    <Language locale="English ">
    <Office languageSKU="Ar abic">
    <Product name="Office Professional 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    <Product name="Office Enterprise 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    <Product name="Office Small Business 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    </Office>
    <Office languageSKU="En glish">
    <Product name="Office Professional 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    <Product name="Office Enterprise 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    <Product name="Office Small Business 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    </Office>
    </Language>
    <Language locale="Arabic" >
    <Office languageSKU="Ar abic">
    <Product name="Office Professional 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    <Product name="Office Enterprise 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    <Product name="Office Small Business 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    </Office>
    <Office languageSKU="En glish">
    <Product name="Office Professional 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    <Product name="Office Enterprise 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    <Product name="Office Small Business 2007" Result="PASS" ErrorsIfAny="NO NE">
    </Product>
    </Office>
    </Language>
    </Languages>






    I am using the following xslt code:

    <xsl:styleshe et version="1.0" xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="ur n:schemas-microsoft-com:xslt"
    xmlns="urn:sche mas-microsoft-com:office:spre adsheet"
    xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
    xmlns:x="urn:sc hemas-microsoft-com:office:exce l"
    xmlns:ss="urn:s chemas-microsoft-com:office:spre adsheet"
    xmlns:html="htt p://www.w3.org/TR/REC-html40">
    <xsl:template match="/">

    <Workbook xmlns="urn:sche mas-microsoft-com:office:spre adsheet" xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
    xmlns:x="urn:sc hemas-microsoft-com:office:exce l" xmlns:ss="urn:s chemas-microsoft-com:office:spre adsheet"
    xmlns:html="htt p://www.w3.org/TR/REC-html40">
    <Styles>
    <Style ss:ID="StyleBor der">
    <Borders>
    <Border ss:Position="Bo ttom" ss:LineStyle="C ontinuous" ss:Weight="1"/>
    <Border ss:Position="Le ft" ss:LineStyle="C ontinuous" ss:Weight="1"/>
    <Border ss:Position="Ri ght" ss:LineStyle="C ontinuous" ss:Weight="1"/>
    <Border ss:Position="To p" ss:LineStyle="C ontinuous" ss:Weight="1"/>
    </Borders>
    </Style>
    <Style ss:ID="StyleTab leHeader">
    <Borders>
    <Border ss:Position="Bo ttom" ss:LineStyle="C ontinuous" ss:Weight="2"/>
    <Border ss:Position="Le ft" ss:LineStyle="C ontinuous" ss:Weight="2"/>
    <Border ss:Position="Ri ght" ss:LineStyle="C ontinuous" ss:Weight="2"/>
    <Border ss:Position="To p" ss:LineStyle="C ontinuous" ss:Weight="2"/>
    </Borders>
    <Alignment ss:Horizontal=" Center" ss:Vertical="Bo ttom"/>
    <Font ss:Bold="1"/>
    </Style>
    <Style ss:ID="StyleRig htBorder">
    <Alignment ss:Wra<?xml version="1.0" encoding="UTF-8" ?>
    pText="1"/>
    <Borders>
    <Border ss:Position="Ri ght" ss:LineStyle="C ontinuous" ss:Weight="2"/>
    </Borders>
    </Style>
    <Style ss:ID="StyleWra pText" >
    <Alignment ss:WrapText="1"/>
    </Style>
    <Style ss:ID="StyleRig htAlign">
    <Alignment ss:Horizontal=" Right" />
    </Style>
    </Styles>
    <Worksheet ss:Name="UrlCor pus">
    <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="Sty leTableHeader">
    <Data ss:Type="String ">CountryNa me</Data>
    </Cell>
    <Cell ss:StyleID="Sty leTableHeader">
    <Data ss:Type="String ">Toggle Language</Data>
    </Cell>

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

    <Cell ss:StyleID="Sty leTableHeader">
    <Data ss:Type="String ">Product Name</Data>
    </Cell>
    <Cell ss:StyleID="Sty leTableHeader">
    <Data ss:Type="String ">Result</Data>
    </Cell>
    <Cell ss:StyleID="Sty leTableHeader">
    <Data ss:Type="String ">ErrorsIfA ny</Data>
    </Cell>
    </Row>

    <xsl:apply-templates/>
    <xsl:apply-templates select="Languag e/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="@Countr yName"/>
    </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="@langua geSKU"/>
    </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="@Errors IfAny"/>
    </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="./@OfficeLanguage SKU"/>
    </Data>
    </Cell>
    <Cell>
    <Data ss:Type="String ">
    <xsl:value-of select="@Produc tName"/>
    </Data>
    </Cell>
    <Cell>
    <Data ss:Type="String ">
    <xsl:value-of select="@Result "/>
    </Data>
    </Cell>
    <Cell>
    <Data ss:Type="String ">
    <xsl:value-of select="@Errors IfAny"/>
    </Data>
    </Cell>

    </Row>
    </xsl:template>

    </xsl:stylesheet>


    I require the excel sheet in the following format:


    Country Name| LangugeLocale | OfficeLanguageS KU | 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
    Tags: None
    • jkmyoung
      Recognized Expert Top Contributor
      • Mar 2006
      • 2057
      #2
      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?

        Comment

        • inspireuk
          New Member
          • Jul 2007
          • 12
          #3
          Just an idea - Excel allows html tables as a valid input. it maybe easier to format your data into that.

          Josh

            Comment

            Previous template Next
            Working...