XSLT XML to Excel in cell text formatting

Question

I'm creating an excel document where certain text within a cell will be formatted in a different colour or bold.

for example my spreadsheet should be like this: .

The text I want to format has got tags round it in my XML e.g. <strong>. I have written a function that picks up these tags and replaced them with the colour or bold formatting e.g. <Font html:color="#0000FF"> text </Font> when creating the xml output to open in excel.

The problem is that when the spreadsheet is opened the text isn't coloured but instead surround by the formatting command <Font html:color="#0000FF">: .

Here is my input XML

<?xml version="1.0" encoding="UTF-8"?>
<analysis>
   <datasetList>
      <dataset>
         <datasetLabel>Subject Level Analysis</datasetLabel>
         <datasetName>ADSL</datasetName>
         <datasetOrdinal>1</datasetOrdinal>
         <datasetStructure>One record per subject</datasetStructure>
         <datasetContext>
            <datasetClass>
               <datasetClass>ADSL</datasetClass>
            </datasetClass>
         </datasetContext>
         <columnList>            
            <column>
               <columnLabel>Analysis Visit</columnLabel>               
               <columnName>AVISIT</columnName>               
               <columnDerivationList>
                  <columnDerivation>
                     <columnDerivationDescription>Set to collected visit name [EG.VISIT] &lt;keepordrop>Set to 'POST-BASELINE MINIMUM'&lt;/keepordrop></columnDerivationDescription>                     
                  </columnDerivation>
                  <columnDerivation>                     
                     <columnDerivationDescription>Set to a re-defined visit range based on user-defined input. &lt;strong>Set to 'POST-BASELINE MINIMUM'&lt;/strong></columnDerivationDescription>
                  </columnDerivation>
               </columnDerivationList>
            </column>
            <column>
               <columnLabel>Analysis Visit (N)</columnLabel>              
               <columnName>AVISITN</columnName>               
               <columnDerivationList>
                  <columnDerivation>                     
                     <columnDerivationDescription>Set to collected &lt;edit>Set to 9997&lt;/edit></columnDerivationDescription>
                  </columnDerivation>
                  <columnDerivation>                     
                     <columnDerivationDescription>Set to a user defined numeric value &lt;select>Set to 9997 wih the analysis visit &lt;/select></columnDerivationDescription> 
                  </columnDerivation>
               </columnDerivationList>
            </column>           
         </columnList>         
      </dataset>
   </datasetList>
</analysis>

Here is my complete XSLT

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<xsl:stylesheet version="2.0" 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" xmlns:gdsr="http://somethinghere.com" >
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>


    <!--
***************************************************************************************
                                  START OF WORKBOOK LAYOUT 
***************************************************************************************
-->
    <xsl:template match="/" >

        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        <ss: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:call-template name="document-properties"/>
            <xsl:call-template name="document-styles"/>



            <!-- ****************************************************************************************** -->
            <!-- CREATE THE TABS IN THE WORKBOOK -->
            <!-- ****************************************************************************************** -->         
            <xsl:call-template name="getDset"/>


            <!-- ****************************************************************************************** -->

        </ss:Workbook>
    </xsl:template>


    <!-- TEMPLATE FOR DATASET -->


    <xsl:template name="getDset">
        <xsl:for-each select="//dataset">
            <ss:Worksheet ss:Name="{.//datasetName}"><!-- Add filters to the columns -->
                <Names>
                    <NamedRange ss:Name="_FilterDatabase" ss:RefersTo="={.//datasetName}!R1C1:R1C2" ss:Hidden="1"/>
                </Names>
                <ss:Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="200" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="30">       
                    <Column ss:Width="70"/> 
                    <Column ss:Width="70"/>


                    <Row>
                        <Cell ss:StyleID="sHead">
                            <Data ss:Type="String">
                                Header1
                            </Data>
                            <NamedCell ss:Name="_FilterDatabase"/>
                        </Cell>
                        <Cell ss:StyleID="sHead">
                            <Data ss:Type="String">
                                Header2
                            </Data>
                            <NamedCell ss:Name="_FilterDatabase"/>
                        </Cell>        
                    </Row>  


                    <xsl:for-each select="columnList/column">
                        <xsl:if test="columnName !=' ' ">

                            <Row>
                                <Cell ss:StyleID="sBody"  > 
                                    <Data ss:Type="String">
                                        <xsl:value-of select="columnName"/> 
                                    </Data>
                                </Cell>

 <!-- SET COLOR OF TEXT WHEN TAGGED -->
                                <xsl:variable name="columnDerivationDescription">
                                    <xsl:value-of select="columnDerivationList/columnDerivation/columnDerivationDescription"/>  
                                </xsl:variable> 
                                <xsl:variable name="columnDerivationDescription1">
                                    <xsl:value-of select="gdsr:set-font($columnDerivationDescription)"/>  
                                </xsl:variable> 

                                <xsl:choose>
                                    <xsl:when test="contains($columnDerivationDescription1, 'Font')">    
                                <Cell ss:StyleID="sBody">
                                    <ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">
                                        <xsl:for-each select="columnDerivationList/columnDerivation">                           
                                            <xsl:if test="columnDerivationDescription !=' ' ">
                                                <xsl:call-template name="LFsToBRs">
                                                    <xsl:with-param name="input" select="gdsr:set-font(columnDerivationDescription)"/> 
                                                </xsl:call-template>
                                            </xsl:if>                                           
                                        </xsl:for-each> 
                                    </ss:Data>
                                </Cell> 
                                </xsl:when> 

                                <xsl:otherwise>
                                    <Cell ss:StyleID="sBody">
                                        <Data ss:Type="String">
                                            <xsl:for-each select="columnDerivationList/columnDerivation">                                                
                                                <xsl:if test="columnDerivationDescription !=' ' ">
                                                    <xsl:call-template name="LFsToBRs">
                                                        <xsl:with-param name="input" select="columnDerivationDescription"/> 
                                                    </xsl:call-template>
                                                </xsl:if>                                               
                                            </xsl:for-each> 
                                        </Data>
                                    </Cell>   
                                </xsl:otherwise>    
                                </xsl:choose>    
 <!-- COLOUR TAGGED. NICELY DONE -->
                            </Row>

                        </xsl:if>
                    </xsl:for-each>
                </ss:Table>
                <xsl:call-template name="worksheet-options"/>

                <AutoFilter x:Range="R1C1:R1C2" xmlns="urn:schemas-microsoft-com:office:excel"/>
            </ss:Worksheet>
        </xsl:for-each>
    </xsl:template>


    <!--FUNCTIONS-->

    <xsl:function name="gdsr:set-font">
        <xsl:param name="text"/>

        <xsl:variable name="blue-font">&lt;Font  html:color="#0000FF"&gt;</xsl:variable>
        <xsl:variable name="blue-font-end"><xsl:text disable-output-escaping="no">&lt;/Font&gt;</xsl:text></xsl:variable>


        <xsl:variable name="pink-font">&lt;Font  html:color="#7030A0"&gt;</xsl:variable>
        <xsl:variable name="pink-font-end"><xsl:text disable-output-escaping="no">&lt;/Font&gt;</xsl:text></xsl:variable>

        <xsl:variable name="var-1" select="replace($text,'&lt;select&gt;',$blue-font)"/>
        <xsl:variable name="var-2" select="replace($var-1,'&lt;/select&gt;',$blue-font-end)"/>

        <xsl:variable name="var-3" select="replace($var-2,'&lt;strong&gt;','&lt;b&gt;')"/>
        <xsl:variable name="var-4" select="replace($var-3,'&lt;/strong&gt;','&lt;/b&gt;')"/>

        <xsl:variable name="var-5" select="replace($var-4,'&lt;keepordrop&gt;',$pink-font)"/>
        <xsl:variable name="var-6" select="replace($var-5,'&lt;/keepordrop&gt;',$pink-font-end)"/>

        <xsl:variable name="var-7" select="replace($var-6,'&lt;edit&gt;',$blue-font)"/>
        <xsl:variable name="var-8" select="replace($var-7,'&lt;/edit&gt;',$blue-font-end)"/>

        <xsl:variable name="var-81" select="replace($var-8,'&lt;select&gt;&lt;option&gt;',$blue-font)"/>
        <xsl:variable name="var-82" select="replace($var-81,'&lt;/option&gt;&lt;option&gt;','BLUEFONTENDOP1 | BLUEFONTSTARTOP2')"/>
        <xsl:variable name="var-83" select="replace($var-82,'BLUEFONTENDOP1',$blue-font-end)"/>
        <xsl:variable name="var-84" select="replace($var-83,'BLUEFONTSTARTOP2',$blue-font)"/>
        <xsl:variable name="var-85" select="replace($var-84,'&lt;/option&gt;&lt;/select&gt;',$blue-font-end)"/>

        <xsl:variable name="var-9">
            <xsl:analyze-string select="$var-85"
                regex="({$blue-font})(.*?)({$blue-font-end}|{$blue-font})">
                <xsl:matching-substring>
                    <xsl:choose>
                        <xsl:when test="regex-group(3)=$blue-font-end">
                            <xsl:value-of select="regex-group(0)"/>
                        </xsl:when>
                        <xsl:otherwise>
                            <xsl:value-of select="regex-group(1)"/>
                            <xsl:value-of select="regex-group(2)"/>
                        </xsl:otherwise>
                    </xsl:choose>
                </xsl:matching-substring>
                <xsl:non-matching-substring>
                    <xsl:value-of select="."/>
                </xsl:non-matching-substring>
            </xsl:analyze-string>
        </xsl:variable>

        <xsl:variable name="var-10">
            <xsl:analyze-string select="$var-9"
                regex="({$blue-font-end})(.*?)({$blue-font-end}|{$blue-font})">
                <xsl:matching-substring>
                    <xsl:choose>
                        <xsl:when test="regex-group(3)=$blue-font">
                            <xsl:value-of select="regex-group(0)"/>
                        </xsl:when>
                        <xsl:otherwise>
                            <xsl:value-of select="regex-group(2)"/>
                            <xsl:value-of select="regex-group(3)"/>
                        </xsl:otherwise>
                    </xsl:choose>
                </xsl:matching-substring>
                <xsl:non-matching-substring>
                    <xsl:value-of select="."/>
                </xsl:non-matching-substring>
            </xsl:analyze-string>
        </xsl:variable>

        <xsl:value-of select="$var-10"/>
    </xsl:function>    


    <!-- 
    *************************************GENERAL TEMPLATES**************************************************
    -->

    <xsl:template name="LFsToBRs">
        <xsl:param name="input"/>
        <xsl:choose>
            <xsl:when test="contains($input, '&#10;')">
                <xsl:value-of select="substring-before($input, '&#10;')"/> 
                <xsl:text disable-output-escaping="yes">&amp;#10;</xsl:text>      
                <xsl:call-template name="LFsToBRs">
                    <xsl:with-param name="input" select="substring-after($input, '&#10;')"/>
                </xsl:call-template>
            </xsl:when>
            <xsl:otherwise>

                <xsl:value-of select="$input"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <!-- EXCEL WORKSHEET OPTIONS -->        
    <xsl:template name="worksheet-options">
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
            <PageSetup>
                <Header x:Margin="0.3"/>
                <Footer x:Margin="0.3"/>
                <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
            </PageSetup>
            <Selected/>
            <FreezePanes/>
            <FrozenNoSplit/>
            <SplitHorizontal>1</SplitHorizontal>
            <TopRowBottomPane>1</TopRowBottomPane>
            <ActivePane>2</ActivePane>
            <Panes>
                <Pane>
                    <Number>3</Number>
                    <ActiveRow>18</ActiveRow>
                    <ActiveCol>2</ActiveCol>
                </Pane>
            </Panes>
            <ProtectObjects>False</ProtectObjects>
            <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
    </xsl:template>

    <!-- EXCEL DOCUMENT PROPERTIES -->  
    <xsl:template name="document-properties">
        <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
            <Author>Mason, Huw {MDBZ~Basel}</Author>
            <LastAuthor>Mason, Huw {MDBZ~Basel}</LastAuthor>
            <Created></Created>
            <Company></Company>
            <Version>1</Version>
        </DocumentProperties>
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>14370</WindowHeight>
            <WindowWidth>27795</WindowWidth>
            <WindowTopX>480</WindowTopX>
            <WindowTopY>120</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
    </xsl:template>

    <!-- EXCEL DOCUMENT STYLES -->  
    <xsl:template name="document-styles">
        <ss:Styles>
            <ss:Style ss:ID="Default" ss:Name="Normal">
                <ss:Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
                <ss:Borders/>
                <ss:Font ss:FontName="Arial" x:Family="Swiss" ss:Size="10" ss:Color="#000000"/>
                <ss:Interior/>
                <ss:NumberFormat/>
                <ss:Protection/>
            </ss:Style><!-- **STYLES CREATED TO REF LATER ON e.g. STYLE FOR COL HEADER**-->
            <ss:Style ss:ID="sHead">
                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
                <ss:Borders>
                    <ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </ss:Borders>
                <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#FFFFFF"/>
                <ss:Interior ss:Color="#538DD5" ss:Pattern="Solid"/>
            </ss:Style>
            <ss:Style ss:ID="sHeadAC">
                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
                <ss:Borders>
                    <ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </ss:Borders>
                <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#000000"/>
                <ss:Interior ss:Color="#FFFF66" ss:Pattern="Solid"/>
            </ss:Style>
            <ss:Style ss:ID="sHeadCT">
                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
                <ss:Borders>
                    <ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </ss:Borders>
                <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#FFFFFF"/>
                <ss:Interior ss:Color="#FF9900" ss:Pattern="Solid"/>
            </ss:Style>
            <ss:Style ss:ID="sHeadGD">
                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
                <ss:Borders>
                    <ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </ss:Borders>
                <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#DA9694"/>
                <ss:Interior ss:Color="#000000" ss:Pattern="Solid"/>
            </ss:Style>
            <ss:Style ss:ID="sHeadPRM">
                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
                <ss:Borders>
                    <ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </ss:Borders>
                <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#FFFFFF"/>
                <ss:Interior ss:Color="#92D050" ss:Pattern="Solid"/>
            </ss:Style>        
            <ss:Style ss:ID="sHeadALG">
                <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
                <ss:Borders>
                    <ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </ss:Borders>
                <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#FFFFFF"/>
                <ss:Interior ss:Color="#92D050" ss:Pattern="Solid"/>
            </ss:Style>         
            <ss:Style ss:ID="sBody">
                <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
                <ss:Borders>
                    <ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </ss:Borders>
                <ss:Interior ss:Pattern="Solid"/>
            </ss:Style>
            <Style ss:ID="SHyperlink" ss:Name="Hyperlink">
                <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
                <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>
                <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="10" ss:Color="#0000FF" ss:Underline="Single"/>
            </Style>
        </ss:Styles>
    </xsl:template>


</xsl:stylesheet>

Here is a sample from my output XML

<Row>
            <Cell ss:StyleID="sBody">
               <Data ss:Type="String">AVISIT</Data>
            </Cell>
            <Cell ss:StyleID="sBody">
               <ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String">Set to collected visit name [EG.VISIT] &lt;Font  html:color="#7030A0"&gt;Set to 'POST-BASELINE MINIMUM'&lt;/Font&gt;Set to a re-defined visit range based on user-defined input. &lt;b&gt;Set to 'POST-BASELINE MINIMUM'&lt;/b&gt;</ss:Data>
            </Cell>
         </Row>
         <Row>
            <Cell ss:StyleID="sBody">
               <Data ss:Type="String">AVISITN</Data>
            </Cell>
            <Cell ss:StyleID="sBody">
               <ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String">Set to collected &lt;Font  html:color="#0000FF"&gt;Set to 9997&lt;/Font&gt;Set to a user defined numeric value &lt;Font  html:color="#0000FF"&gt;Set to 9997 wih the analysis visit &lt;/Font&gt;</ss:Data>
            </Cell>
         </Row>

I've created an xsl and saved as XML spreadsheet 2003 to check the code and is as expected. When I replace < > with < and > I get the issue I am experiencing with my output

I don't know if is related to using < rather than < when creating the xml. Has anybody had a similar problem and solved it? Or can see where my problem lies?

EDIT:

I,ve made the updates to the XSLT inc. the html namespace, as suggested by Martin. This creates the XML as I would expect and looks fine.

The problem now is that when opened in excel the formatting doesn't display, I get plain black text.

Output without formatting

I've created an example of what it should look and saved as XML spreadsheet 2003, to compare the two and they are the same. Interestingly: If I copy the code from the excel created example and paste it in to the xslt created XML then open it in excel, the formatting works. If I copy the code from the xslt created xml and paste it in to my excel created example (that did work fine) the formatting now no longer works.

Excel created example

The XML code of the excel created value

   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s64"><Data ss:Type="String">AVISIT</Data></Cell>
    <Cell ss:StyleID="s65"><ss:Data ss:Type="String"
      xmlns="http://www.w3.org/TR/REC-html40">Set to collected visit name [EG.VISIT] <Font
       html:Color="#7030A0">Set to 'POST-BASELINE MINIMUM'</Font>Set to a re-defined visit range based on user-defined input. <B>Set to 'POST-BASELINE MINIMUM'</B></ss:Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s64"><Data ss:Type="String">AVISITN</Data></Cell>
    <Cell ss:StyleID="s65"><ss:Data ss:Type="String"
      xmlns="http://www.w3.org/TR/REC-html40">Set to collected <Font
       html:Color="#0000FF">Set to 9997</Font>Set to a user defined numeric value<Font
       html:Color="#0000FF">Set to 9997 wih the analysis visit</Font></ss:Data></Cell>
   </Row>

This is how the xml created by my XSLT looks:

<Row>
    <Cell ss:StyleID="sBody">
       <Data ss:Type="String">AVISIT</Data>
    </Cell>
    <Cell ss:StyleID="sBody">
       <ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String">Set to collected visit name [EG.VISIT] <Font html:Color="#7030A0">Set to 'POST-BASELINE MINIMUM'</Font>Set to a re-defined visit range based on user-defined input. <B>Set to 'POST-BASELINE MINIMUM'</B>
       </ss:Data>
    </Cell>
 </Row>
 <Row>
    <Cell ss:StyleID="sBody">
       <Data ss:Type="String">AVISITN</Data>
    </Cell>
    <Cell ss:StyleID="sBody">
       <ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String">Set to collected <Font html:Color="#0000FF">Set to 9997</Font>Set to a user defined numeric value <Font html:Color="#0000FF">Set to 9997 wih the analysis visit </Font>
       </ss:Data>
    </Cell>
 </Row>

So despite the fact that the xml code is the same, depending on how it was created defines whether it works or not.

Question: Could there be some encoding differences that would explain this behavior?

I would start by manually creating what you want to see in Excel, then exporting as XML to see what Excel needs, then creating that in your XSLT. — Jim Garrison
That is a lot of code, but a function returning a text node with xsl:value-of will never create a font element, that is for sure. As for the whole approach, as you use XSLT 2.0 already, which XSLT processor exactly do you use? Using the 9.7 release of Saxon 9 (even the HE edition) you might be able to switch to XSLT 3.0 and then use w3.org/TR/xpath-functions-31/#func-parse-xml-fragment on the contents of columnDerivationDescription to simply push the contents to proper templates transforming the select element as needed. That works much better than trying to parse the data. — Martin Honnen
Thanks @MartinHonnen , I'm using Saxon EE 9.6 currently. I'll try and grab 9.7 and give your solution a try. Thanks again. — Huw
I have tried to reproduce the problem using Excel 2013, first it was refusing to load the generated XML at all, complaining (in a log file well hidden) about things like that sBody value on StyleID. But once I corrected all those values to values taken from an Excel saved sheet the generated snippet of Rows and Cells shows up with the color or bold for those text parts that have gotten some special formatting. — Martin Honnen

Martin Honnen Martin Honnen · Accepted Answer · 2016-08-10T19:09:09

Here is how you could approach the problem using parse-xml-fragment (https://www.w3.org/TR/xpath-functions-31/#func-parse-xml-fragment, available in Saxon 9.7 (all editions) when using version="3.0" in your XSLT) and then templates to transform the elements you have inside of the escaped markup in the columnDerivationDescription elements:

    <xsl:template name="getDset">
        <xsl:for-each select="//dataset">
            <ss:Worksheet ss:Name="{.//datasetName}"><!-- Add filters to the columns -->
                <Names>
                    <NamedRange ss:Name="_FilterDatabase" ss:RefersTo="={.//datasetName}!R1C1:R1C2" ss:Hidden="1"/>
                </Names>
                <ss:Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="200" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="30">       
                    <Column ss:Width="70"/> 
                    <Column ss:Width="70"/>


                    <Row>
                        <Cell ss:StyleID="sHead">
                            <Data ss:Type="String">
                                Header1
                            </Data>
                            <NamedCell ss:Name="_FilterDatabase"/>
                        </Cell>
                        <Cell ss:StyleID="sHead">
                            <Data ss:Type="String">
                                Header2
                            </Data>
                            <NamedCell ss:Name="_FilterDatabase"/>
                        </Cell>        
                    </Row>  


                    <xsl:for-each select="columnList/column">
                        <xsl:if test="columnName !=' ' ">

                            <Row>
                                <Cell ss:StyleID="sBody"  > 
                                    <Data ss:Type="String">
                                        <xsl:value-of select="columnName"/> 
                                    </Data>
                                </Cell>
 <!-- SET COLOR OF TEXT WHEN TAGGED -->
                                <Cell ss:StyleID="sBody">
                                    <Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">
                                        <xsl:apply-templates select="columnDerivationList/columnDerivation/columnDerivationDescription"/>
                                    </Data>
                                </Cell>  
 <!-- COLOUR TAGGED. NICELY DONE -->
                            </Row>

                        </xsl:if>
                    </xsl:for-each>
                </ss:Table>
                <xsl:call-template name="worksheet-options"/>

                <AutoFilter x:Range="R1C1:R1C2" xmlns="urn:schemas-microsoft-com:office:excel"/>
            </ss:Worksheet>
        </xsl:for-each>
    </xsl:template>

    <xsl:template match="columnDerivationDescription">
        <xsl:apply-templates select="parse-xml-fragment(.)/node()"/>
    </xsl:template>

    <xsl:template match="strong">
        <b>
            <xsl:apply-templates/>
        </b>
    </xsl:template>

    <xsl:template match="edit">
        <Font html:color="#0000FF">
            <xsl:apply-templates/>
        </Font>
    </xsl:template>

    <!-- add further templates here for the other elements and transformations you need -->

XSLT XML to Excel in cell text formatting

1 Answers