locked
Help with XSL? RRS feed

  • Question

  • User-1021925404 posted

    Hi Guys,

    I was hoping you could help me with my XSL file that i want to convert XML to excel please?

    Below is my XML:

    <?xml version="1.0" encoding="utf-8"?>
    <
    Report>
     <
    Table1/>
     <
    Table2>
      <
    Data>
       <
    Detail JobId="123" Title="Developer" Description="This is the decription" />
       <
    Detail JobId="456" Title="Tester" Description="My test second decription" />
      </
    Data>
     </
    Table2>
    </
    Report>

    Below is my XSL file:

    <xsl:stylesheet version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:user="urn:my-scripts"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >

    <
    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">
    <
    xsl:apply-templates/>
    </
    Workbook>
    </
    xsl:template>

    <
    xsl:template match="/*">
     <
    Worksheet>
      <
    xsl:attribute name="ss:Name">
       <
    xsl:value-of select="local-name(/*/*)"/>
      </
    xsl:attribute>
    <
    Table>
    <
    Row>
    <
    xsl:for-each select="*[position() = 1]/*/*/*">
    <
    Cell>
    <
    Data ss:Type="String">
    <xsl:value-of select="@JobId"/>
    </Data>
    </
    Cell>
    <
    Cell>
    <
    Data ss:Type="String">
    <xsl:value-of select="@Title"/>
    </
    SPAN>
    </
    Data>
    </
    Cell>
    <
    Cell>
    <
    Data ss:Type="String">
    <
    xsl:value-of select="@Description"/>
    </
    Data>
    </
    Cell>
    </xsl:for-each>
    </
    Row>
    <
    xsl:apply-templates/>
    </
    Table>
    </
    Worksheet>
    </
    xsl:template>
    <
    xsl:template match="/*/*">
    <
    Row>
    <
    xsl:apply-templates/>
    </
    Row>
    </
    xsl:template>
    <
    xsl:template match="/*/*/*">
    <
    Cell>
    <Data ss:Type="String">
    <
    xsl:value-of select="."/>
    </
    Data>
    </
    Cell>
    </
    xsl:template>
    </
    xsl:stylesheet>

    At the moment when i convert the XML all the data is entered on 1 row, i was hoping someone might be able to tell me how to:

    1) Get the data to appear on seperate rows per Detail node in my xml?
    2) How to format the excel sheet so that i can change the font size and type?
    3) How to add titles to the excel file as per the attributes in the Detail node, i.e JobId, Title and Description?

    Your help will be greatly appreciated!

    Kind Regards,
    Chloe ~X~ 

    Saturday, April 11, 2009 8:20 AM

Answers

  • User1835330922 posted

    With XSLT 1.0 if you want to eliminate duplicates then you need to use Muenchian grouping and process only the first node in each group.

    So assuming you have e.g.

     

    <root>
      <table>
        <row Title="Test" Location="UK" Colour="Blue" Make="Audi" Model="A4"/>
        <row Title="Test" Location="UK" Colour="Blue" Make="Audi" Model="A4"/>
        <row Title="Test" Location="UK" Colour="Red" Make="BMW" Model="Z4"/>
      </table>
    </root>

     then we can process only distinct row element as follows:

    <xsl:stylesheet
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      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"
      version="1.0">
      
      <xsl:output method="xml" indent="yes"/>
      
      <xsl:key name="group" match="row"
        use="concat(@Title, '|', @Location, '|', @Colour, '|', @Make, '|', @Model)"/>
      
      <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        <Workbook>
          <xsl:apply-templates select="root/table"/>
        </Workbook>
      </xsl:template>
      
      <xsl:template match="table">
        <Worksheet ss:Name="{name()}">
          <Table>
            <xsl:apply-templates select="row[generate-id() = generate-id(key('group', concat(@Title, '|', @Location, '|', @Colour, '|', @Make, '|', @Model))[1])]"/>
          </Table>
        </Worksheet>
      </xsl:template>
      
      <xsl:template match="row">
        <Row>
          <xsl:apply-templates select="@*"/>
        </Row>
      </xsl:template>
      
      <xsl:template match="row/@*">
        <Cell><Data ss:Type="String"><xsl:value-of select="."/></Data></Cell>
      </xsl:template>
    
    </xsl:stylesheet>
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 16, 2009 8:24 AM

All replies

  • User1835330922 posted

    I am not very familiar with the Excel XML format so I simply worked from a sample spreadsheet saved with Excel 2003 in XML format. Based on that and assuming you want a Worksheet for your Table2 element where each Detail element is transformed into a row and each attribute of the Detail element forms a cell in that row here is an XSLT 1.0 stylesheet that creates one worksheet with one table with two rows from your XML sample document:

     

    <xsl:stylesheet
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      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"
      version="1.0">
      
      <xsl:output method="xml" indent="yes"/>
      
      <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        <Workbook>
          <xsl:apply-templates select="Report/Table2"/>
        </Workbook>
      </xsl:template>
      
      <xsl:template match="Table2">
        <Worksheet ss:Name="{name()}">
          <Table>
            <xsl:apply-templates select="Data/Detail"/>
          </Table>
        </Worksheet>
      </xsl:template>
      
      <xsl:template match="Detail">
        <Row>
          <xsl:apply-templates select="@*"/>
        </Row>
      </xsl:template>
      
      <xsl:template match="Detail/@*">
        <Cell><Data ss:Type="String"><xsl:value-of select="."/></Data></Cell>
      </xsl:template>
    
    </xsl:stylesheet>
      

    That should solve your first question. As for the other two questions, consider to create an Excel spreadsheet with the format (e.g. font size, type) you are looking for and with title information, then save as XML, then look at the resulting XML document with a text editor. From that you should be able to find the elements and/or attributes the Excel XML format uses to mark up such information, then you only need to integrate that in the stylesheet.

     

    Tuesday, April 14, 2009 8:19 AM
  • User-1021925404 posted

    Hi Martin!

    Thank you so much for help. I tried saving an excel file as a xml spreadsheet and the viewing it in notepad and that worked a treated.

    I have one more question now i was hoping you could help me with please?

    My xml data is now displayed correctly within excel. I want to be able to remove rows that have 3 columns that have the same value as the row above.

    For example in the below example we can see that the second row is a duplicate of the top row so it can be removed.

       Title     Location     Colour     Make   Model
    1.Test      UK           Blue       Audi      A4
    2.Test      UK           Blue       Audi      A4
    3.Test      UK           Red        BMW    Z4

    Currently in excel i use the below forumla to highlight if a row is a duplicate by checking three cells with the cells entered above them and then entering the word Dupe in another cell in that row:

    =IF(B2&C2&G2=B1&C1&G1,"Dupe","")

    How can i de-duplicate the data when transforming it into excel?

    Kind regards,
    Chole ~X~

    Wednesday, April 15, 2009 3:29 PM
  • User-1021925404 posted

    Would i use soemthing like this to achieve what i want to do?

    <xsl:choose>
      <xsl:when test="expression">
        ... some output ...
      </xsl:when>
      <xsl:otherwise>
        ... some output ....
      </xsl:otherwise>
    </xsl:choose>

    Thanks,
    Chloe ~X~

    Thursday, April 16, 2009 7:11 AM
  • User1835330922 posted

    With XSLT 1.0 if you want to eliminate duplicates then you need to use Muenchian grouping and process only the first node in each group.

    So assuming you have e.g.

     

    <root>
      <table>
        <row Title="Test" Location="UK" Colour="Blue" Make="Audi" Model="A4"/>
        <row Title="Test" Location="UK" Colour="Blue" Make="Audi" Model="A4"/>
        <row Title="Test" Location="UK" Colour="Red" Make="BMW" Model="Z4"/>
      </table>
    </root>

     then we can process only distinct row element as follows:

    <xsl:stylesheet
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      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"
      version="1.0">
      
      <xsl:output method="xml" indent="yes"/>
      
      <xsl:key name="group" match="row"
        use="concat(@Title, '|', @Location, '|', @Colour, '|', @Make, '|', @Model)"/>
      
      <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        <Workbook>
          <xsl:apply-templates select="root/table"/>
        </Workbook>
      </xsl:template>
      
      <xsl:template match="table">
        <Worksheet ss:Name="{name()}">
          <Table>
            <xsl:apply-templates select="row[generate-id() = generate-id(key('group', concat(@Title, '|', @Location, '|', @Colour, '|', @Make, '|', @Model))[1])]"/>
          </Table>
        </Worksheet>
      </xsl:template>
      
      <xsl:template match="row">
        <Row>
          <xsl:apply-templates select="@*"/>
        </Row>
      </xsl:template>
      
      <xsl:template match="row/@*">
        <Cell><Data ss:Type="String"><xsl:value-of select="."/></Data></Cell>
      </xsl:template>
    
    </xsl:stylesheet>
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 16, 2009 8:24 AM
  • User-1021925404 posted

    Hi Martin!

    Thank you so much, you are fantastic!

    I was thinking about the duplicates and i reckon it would be better if maybe the duplicate were not removed completely form the Excel file it creates, but hidden in the output excel.

    For example on the end of the table in my previous post we enter a column called Duplicate and then if the 1 row has the same value entered in three of it's cells as the row above, then we will say this is a duplicate row and so in the new column we have created we will enter the value 'Dupe' (As per the excel formula i posted previously)

    Then when the excel file is generated the output will have an auto filter on the headers and the auto filter will have a selection on the duplicate column that will displays 'Blanks' and so as it displays Blanks that means that it will only display rows that have nothing entered in that column meaning it is not a duplicate, therefore displaying only unique records. (I hope you follow me?!)

    This is abit confusing but if we can achieve this that would be absolutely fantastic!

    Thank you as always!!
    Chloe ~X~

    P.S

    So here's my table, you can see i have added the Duplicate column in and hte second row has Dupe entered because it has 3 fields that have the same value as the row above. Therefore when the file is tranformed to excel it has an auto filter on the Header columns and it has blanks selected on the Duplicate column so it only displays the 1st and 3rd row?

       Title     Location     Colour     Make   Model    Duplicate
    1.Test      UK           Blue       Audi      A4
    2.Test      UK           Blue       Audi      A4          Dupe
    3.Test      UK           Red        BMW    Z4

    Thursday, April 16, 2009 5:57 PM