title: XSLT XML to Excel in cell text formatting
body: <p>I'm creating an excel document where certain text within a cell will be formatted in a different colour or bold.</p>
  
  <p>for example my spreadsheet should be like this:
  .</p>
  
  <p>The text I want to format has got tags round it in my XML e.g. <code>&amp;lt;strong&gt;</code>. I have written a function that picks up these tags and replaced them with
  the colour or bold formatting e.g. <code>&lt;Font html:color="#0000FF"&gt; text &lt;/Font&gt;</code> when creating the xml output to open in excel.</p>
  
  <p>The problem is that when the spreadsheet is opened the text isn't coloured but instead surround by the formatting command <code>&lt;Font html:color="#0000FF"&gt;</code>:
  .</p>
  
  <p>Here is my input XML</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;analysis&gt;
     &lt;datasetList&gt;
        &lt;dataset&gt;
           &lt;datasetLabel&gt;Subject Level Analysis&lt;/datasetLabel&gt;
           &lt;datasetName&gt;ADSL&lt;/datasetName&gt;
           &lt;datasetOrdinal&gt;1&lt;/datasetOrdinal&gt;
           &lt;datasetStructure&gt;One record per subject&lt;/datasetStructure&gt;
           &lt;datasetContext&gt;
              &lt;datasetClass&gt;
                 &lt;datasetClass&gt;ADSL&lt;/datasetClass&gt;
              &lt;/datasetClass&gt;
           &lt;/datasetContext&gt;
           &lt;columnList&gt;            
              &lt;column&gt;
                 &lt;columnLabel&gt;Analysis Visit&lt;/columnLabel&gt;               
                 &lt;columnName&gt;AVISIT&lt;/columnName&gt;               
                 &lt;columnDerivationList&gt;
                    &lt;columnDerivation&gt;
                       &lt;columnDerivationDescription&gt;Set to collected visit name [EG.VISIT] &amp;lt;keepordrop&gt;Set to 'POST-BASELINE MINIMUM'&amp;lt;/keepordrop&gt;&lt;/columnDerivationDescription&gt;                     
                    &lt;/columnDerivation&gt;
                    &lt;columnDerivation&gt;                     
                       &lt;columnDerivationDescription&gt;Set to a re-defined visit range based on user-defined input. &amp;lt;strong&gt;Set to 'POST-BASELINE MINIMUM'&amp;lt;/strong&gt;&lt;/columnDerivationDescription&gt;
                    &lt;/columnDerivation&gt;
                 &lt;/columnDerivationList&gt;
              &lt;/column&gt;
              &lt;column&gt;
                 &lt;columnLabel&gt;Analysis Visit (N)&lt;/columnLabel&gt;              
                 &lt;columnName&gt;AVISITN&lt;/columnName&gt;               
                 &lt;columnDerivationList&gt;
                    &lt;columnDerivation&gt;                     
                       &lt;columnDerivationDescription&gt;Set to collected &amp;lt;edit&gt;Set to 9997&amp;lt;/edit&gt;&lt;/columnDerivationDescription&gt;
                    &lt;/columnDerivation&gt;
                    &lt;columnDerivation&gt;                     
                       &lt;columnDerivationDescription&gt;Set to a user defined numeric value &amp;lt;select&gt;Set to 9997 wih the analysis visit &amp;lt;/select&gt;&lt;/columnDerivationDescription&gt; 
                    &lt;/columnDerivation&gt;
                 &lt;/columnDerivationList&gt;
              &lt;/column&gt;           
           &lt;/columnList&gt;         
        &lt;/dataset&gt;
     &lt;/datasetList&gt;
  &lt;/analysis&gt;
  </code></pre>
  
  <p>Here is my complete XSLT</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;?mso-application progid="Excel.Sheet"?&gt;
  &lt;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" &gt;
      &lt;xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/&gt;
  
  
      &lt;!--
  ***************************************************************************************
                                    START OF WORKBOOK LAYOUT 
  ***************************************************************************************
  --&gt;
      &lt;xsl:template match="/" &gt;
  
          &lt;xsl:processing-instruction name="mso-application"&gt;progid="Excel.Sheet"&lt;/xsl:processing-instruction&gt;
          &lt;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"&gt;
  
              &lt;xsl:call-template name="document-properties"/&gt;
              &lt;xsl:call-template name="document-styles"/&gt;
  
  
  
              &lt;!-- ****************************************************************************************** --&gt;
              &lt;!-- CREATE THE TABS IN THE WORKBOOK --&gt;
              &lt;!-- ****************************************************************************************** --&gt;         
              &lt;xsl:call-template name="getDset"/&gt;
  
  
              &lt;!-- ****************************************************************************************** --&gt;
  
          &lt;/ss:Workbook&gt;
      &lt;/xsl:template&gt;
  
  
      &lt;!-- TEMPLATE FOR DATASET --&gt;
  
  
      &lt;xsl:template name="getDset"&gt;
          &lt;xsl:for-each select="//dataset"&gt;
              &lt;ss:Worksheet ss:Name="{.//datasetName}"&gt;&lt;!-- Add filters to the columns --&gt;
                  &lt;Names&gt;
                      &lt;NamedRange ss:Name="_FilterDatabase" ss:RefersTo="={.//datasetName}!R1C1:R1C2" ss:Hidden="1"/&gt;
                  &lt;/Names&gt;
                  &lt;ss:Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="200" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="30"&gt;       
                      &lt;Column ss:Width="70"/&gt; 
                      &lt;Column ss:Width="70"/&gt;
  
  
                      &lt;Row&gt;
                          &lt;Cell ss:StyleID="sHead"&gt;
                              &lt;Data ss:Type="String"&gt;
                                  Header1
                              &lt;/Data&gt;
                              &lt;NamedCell ss:Name="_FilterDatabase"/&gt;
                          &lt;/Cell&gt;
                          &lt;Cell ss:StyleID="sHead"&gt;
                              &lt;Data ss:Type="String"&gt;
                                  Header2
                              &lt;/Data&gt;
                              &lt;NamedCell ss:Name="_FilterDatabase"/&gt;
                          &lt;/Cell&gt;        
                      &lt;/Row&gt;  
  
  
                      &lt;xsl:for-each select="columnList/column"&gt;
                          &lt;xsl:if test="columnName !=' ' "&gt;
  
                              &lt;Row&gt;
                                  &lt;Cell ss:StyleID="sBody"  &gt; 
                                      &lt;Data ss:Type="String"&gt;
                                          &lt;xsl:value-of select="columnName"/&gt; 
                                      &lt;/Data&gt;
                                  &lt;/Cell&gt;
  
   &lt;!-- SET COLOR OF TEXT WHEN TAGGED --&gt;
                                  &lt;xsl:variable name="columnDerivationDescription"&gt;
                                      &lt;xsl:value-of select="columnDerivationList/columnDerivation/columnDerivationDescription"/&gt;  
                                  &lt;/xsl:variable&gt; 
                                  &lt;xsl:variable name="columnDerivationDescription1"&gt;
                                      &lt;xsl:value-of select="gdsr:set-font($columnDerivationDescription)"/&gt;  
                                  &lt;/xsl:variable&gt; 
  
                                  &lt;xsl:choose&gt;
                                      &lt;xsl:when test="contains($columnDerivationDescription1, 'Font')"&gt;    
                                  &lt;Cell ss:StyleID="sBody"&gt;
                                      &lt;ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40"&gt;
                                          &lt;xsl:for-each select="columnDerivationList/columnDerivation"&gt;                           
                                              &lt;xsl:if test="columnDerivationDescription !=' ' "&gt;
                                                  &lt;xsl:call-template name="LFsToBRs"&gt;
                                                      &lt;xsl:with-param name="input" select="gdsr:set-font(columnDerivationDescription)"/&gt; 
                                                  &lt;/xsl:call-template&gt;
                                              &lt;/xsl:if&gt;                                           
                                          &lt;/xsl:for-each&gt; 
                                      &lt;/ss:Data&gt;
                                  &lt;/Cell&gt; 
                                  &lt;/xsl:when&gt; 
  
                                  &lt;xsl:otherwise&gt;
                                      &lt;Cell ss:StyleID="sBody"&gt;
                                          &lt;Data ss:Type="String"&gt;
                                              &lt;xsl:for-each select="columnDerivationList/columnDerivation"&gt;                                                
                                                  &lt;xsl:if test="columnDerivationDescription !=' ' "&gt;
                                                      &lt;xsl:call-template name="LFsToBRs"&gt;
                                                          &lt;xsl:with-param name="input" select="columnDerivationDescription"/&gt; 
                                                      &lt;/xsl:call-template&gt;
                                                  &lt;/xsl:if&gt;                                               
                                              &lt;/xsl:for-each&gt; 
                                          &lt;/Data&gt;
                                      &lt;/Cell&gt;   
                                  &lt;/xsl:otherwise&gt;    
                                  &lt;/xsl:choose&gt;    
   &lt;!-- COLOUR TAGGED. NICELY DONE --&gt;
                              &lt;/Row&gt;
  
                          &lt;/xsl:if&gt;
                      &lt;/xsl:for-each&gt;
                  &lt;/ss:Table&gt;
                  &lt;xsl:call-template name="worksheet-options"/&gt;
  
                  &lt;AutoFilter x:Range="R1C1:R1C2" xmlns="urn:schemas-microsoft-com:office:excel"/&gt;
              &lt;/ss:Worksheet&gt;
          &lt;/xsl:for-each&gt;
      &lt;/xsl:template&gt;
  
  
      &lt;!--FUNCTIONS--&gt;
  
      &lt;xsl:function name="gdsr:set-font"&gt;
          &lt;xsl:param name="text"/&gt;
  
          &lt;xsl:variable name="blue-font"&gt;&amp;lt;Font  html:color="#0000FF"&amp;gt;&lt;/xsl:variable&gt;
          &lt;xsl:variable name="blue-font-end"&gt;&lt;xsl:text disable-output-escaping="no"&gt;&amp;lt;/Font&amp;gt;&lt;/xsl:text&gt;&lt;/xsl:variable&gt;
  
  
          &lt;xsl:variable name="pink-font"&gt;&amp;lt;Font  html:color="#7030A0"&amp;gt;&lt;/xsl:variable&gt;
          &lt;xsl:variable name="pink-font-end"&gt;&lt;xsl:text disable-output-escaping="no"&gt;&amp;lt;/Font&amp;gt;&lt;/xsl:text&gt;&lt;/xsl:variable&gt;
  
          &lt;xsl:variable name="var-1" select="replace($text,'&amp;lt;select&amp;gt;',$blue-font)"/&gt;
          &lt;xsl:variable name="var-2" select="replace($var-1,'&amp;lt;/select&amp;gt;',$blue-font-end)"/&gt;
  
          &lt;xsl:variable name="var-3" select="replace($var-2,'&amp;lt;strong&amp;gt;','&amp;lt;b&amp;gt;')"/&gt;
          &lt;xsl:variable name="var-4" select="replace($var-3,'&amp;lt;/strong&amp;gt;','&amp;lt;/b&amp;gt;')"/&gt;
  
          &lt;xsl:variable name="var-5" select="replace($var-4,'&amp;lt;keepordrop&amp;gt;',$pink-font)"/&gt;
          &lt;xsl:variable name="var-6" select="replace($var-5,'&amp;lt;/keepordrop&amp;gt;',$pink-font-end)"/&gt;
  
          &lt;xsl:variable name="var-7" select="replace($var-6,'&amp;lt;edit&amp;gt;',$blue-font)"/&gt;
          &lt;xsl:variable name="var-8" select="replace($var-7,'&amp;lt;/edit&amp;gt;',$blue-font-end)"/&gt;
  
          &lt;xsl:variable name="var-81" select="replace($var-8,'&amp;lt;select&amp;gt;&amp;lt;option&amp;gt;',$blue-font)"/&gt;
          &lt;xsl:variable name="var-82" select="replace($var-81,'&amp;lt;/option&amp;gt;&amp;lt;option&amp;gt;','BLUEFONTENDOP1 | BLUEFONTSTARTOP2')"/&gt;
          &lt;xsl:variable name="var-83" select="replace($var-82,'BLUEFONTENDOP1',$blue-font-end)"/&gt;
          &lt;xsl:variable name="var-84" select="replace($var-83,'BLUEFONTSTARTOP2',$blue-font)"/&gt;
          &lt;xsl:variable name="var-85" select="replace($var-84,'&amp;lt;/option&amp;gt;&amp;lt;/select&amp;gt;',$blue-font-end)"/&gt;
  
          &lt;xsl:variable name="var-9"&gt;
              &lt;xsl:analyze-string select="$var-85"
                  regex="({$blue-font})(.*?)({$blue-font-end}|{$blue-font})"&gt;
                  &lt;xsl:matching-substring&gt;
                      &lt;xsl:choose&gt;
                          &lt;xsl:when test="regex-group(3)=$blue-font-end"&gt;
                              &lt;xsl:value-of select="regex-group(0)"/&gt;
                          &lt;/xsl:when&gt;
                          &lt;xsl:otherwise&gt;
                              &lt;xsl:value-of select="regex-group(1)"/&gt;
                              &lt;xsl:value-of select="regex-group(2)"/&gt;
                          &lt;/xsl:otherwise&gt;
                      &lt;/xsl:choose&gt;
                  &lt;/xsl:matching-substring&gt;
                  &lt;xsl:non-matching-substring&gt;
                      &lt;xsl:value-of select="."/&gt;
                  &lt;/xsl:non-matching-substring&gt;
              &lt;/xsl:analyze-string&gt;
          &lt;/xsl:variable&gt;
  
          &lt;xsl:variable name="var-10"&gt;
              &lt;xsl:analyze-string select="$var-9"
                  regex="({$blue-font-end})(.*?)({$blue-font-end}|{$blue-font})"&gt;
                  &lt;xsl:matching-substring&gt;
                      &lt;xsl:choose&gt;
                          &lt;xsl:when test="regex-group(3)=$blue-font"&gt;
                              &lt;xsl:value-of select="regex-group(0)"/&gt;
                          &lt;/xsl:when&gt;
                          &lt;xsl:otherwise&gt;
                              &lt;xsl:value-of select="regex-group(2)"/&gt;
                              &lt;xsl:value-of select="regex-group(3)"/&gt;
                          &lt;/xsl:otherwise&gt;
                      &lt;/xsl:choose&gt;
                  &lt;/xsl:matching-substring&gt;
                  &lt;xsl:non-matching-substring&gt;
                      &lt;xsl:value-of select="."/&gt;
                  &lt;/xsl:non-matching-substring&gt;
              &lt;/xsl:analyze-string&gt;
          &lt;/xsl:variable&gt;
  
          &lt;xsl:value-of select="$var-10"/&gt;
      &lt;/xsl:function&gt;    
  
  
      &lt;!-- 
      *************************************GENERAL TEMPLATES**************************************************
      --&gt;
  
      &lt;xsl:template name="LFsToBRs"&gt;
          &lt;xsl:param name="input"/&gt;
          &lt;xsl:choose&gt;
              &lt;xsl:when test="contains($input, '&amp;#10;')"&gt;
                  &lt;xsl:value-of select="substring-before($input, '&amp;#10;')"/&gt; 
                  &lt;xsl:text disable-output-escaping="yes"&gt;&amp;amp;#10;&lt;/xsl:text&gt;      
                  &lt;xsl:call-template name="LFsToBRs"&gt;
                      &lt;xsl:with-param name="input" select="substring-after($input, '&amp;#10;')"/&gt;
                  &lt;/xsl:call-template&gt;
              &lt;/xsl:when&gt;
              &lt;xsl:otherwise&gt;
  
                  &lt;xsl:value-of select="$input"/&gt;
              &lt;/xsl:otherwise&gt;
          &lt;/xsl:choose&gt;
      &lt;/xsl:template&gt;
  
      &lt;!-- EXCEL WORKSHEET OPTIONS --&gt;        
      &lt;xsl:template name="worksheet-options"&gt;
          &lt;WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"&gt;
              &lt;PageSetup&gt;
                  &lt;Header x:Margin="0.3"/&gt;
                  &lt;Footer x:Margin="0.3"/&gt;
                  &lt;PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/&gt;
              &lt;/PageSetup&gt;
              &lt;Selected/&gt;
              &lt;FreezePanes/&gt;
              &lt;FrozenNoSplit/&gt;
              &lt;SplitHorizontal&gt;1&lt;/SplitHorizontal&gt;
              &lt;TopRowBottomPane&gt;1&lt;/TopRowBottomPane&gt;
              &lt;ActivePane&gt;2&lt;/ActivePane&gt;
              &lt;Panes&gt;
                  &lt;Pane&gt;
                      &lt;Number&gt;3&lt;/Number&gt;
                      &lt;ActiveRow&gt;18&lt;/ActiveRow&gt;
                      &lt;ActiveCol&gt;2&lt;/ActiveCol&gt;
                  &lt;/Pane&gt;
              &lt;/Panes&gt;
              &lt;ProtectObjects&gt;False&lt;/ProtectObjects&gt;
              &lt;ProtectScenarios&gt;False&lt;/ProtectScenarios&gt;
          &lt;/WorksheetOptions&gt;
      &lt;/xsl:template&gt;
  
      &lt;!-- EXCEL DOCUMENT PROPERTIES --&gt;  
      &lt;xsl:template name="document-properties"&gt;
          &lt;DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"&gt;
              &lt;Author&gt;Mason, Huw {MDBZ~Basel}&lt;/Author&gt;
              &lt;LastAuthor&gt;Mason, Huw {MDBZ~Basel}&lt;/LastAuthor&gt;
              &lt;Created&gt;&lt;/Created&gt;
              &lt;Company&gt;&lt;/Company&gt;
              &lt;Version&gt;1&lt;/Version&gt;
          &lt;/DocumentProperties&gt;
          &lt;ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"&gt;
              &lt;WindowHeight&gt;14370&lt;/WindowHeight&gt;
              &lt;WindowWidth&gt;27795&lt;/WindowWidth&gt;
              &lt;WindowTopX&gt;480&lt;/WindowTopX&gt;
              &lt;WindowTopY&gt;120&lt;/WindowTopY&gt;
              &lt;ProtectStructure&gt;False&lt;/ProtectStructure&gt;
              &lt;ProtectWindows&gt;False&lt;/ProtectWindows&gt;
          &lt;/ExcelWorkbook&gt;
      &lt;/xsl:template&gt;
  
      &lt;!-- EXCEL DOCUMENT STYLES --&gt;  
      &lt;xsl:template name="document-styles"&gt;
          &lt;ss:Styles&gt;
              &lt;ss:Style ss:ID="Default" ss:Name="Normal"&gt;
                  &lt;ss:Alignment ss:Vertical="Bottom" ss:WrapText="1"/&gt;
                  &lt;ss:Borders/&gt;
                  &lt;ss:Font ss:FontName="Arial" x:Family="Swiss" ss:Size="10" ss:Color="#000000"/&gt;
                  &lt;ss:Interior/&gt;
                  &lt;ss:NumberFormat/&gt;
                  &lt;ss:Protection/&gt;
              &lt;/ss:Style&gt;&lt;!-- **STYLES CREATED TO REF LATER ON e.g. STYLE FOR COL HEADER**--&gt;
              &lt;ss:Style ss:ID="sHead"&gt;
                  &lt;Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/&gt;
                  &lt;ss:Borders&gt;
                      &lt;ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                  &lt;/ss:Borders&gt;
                  &lt;Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#FFFFFF"/&gt;
                  &lt;ss:Interior ss:Color="#538DD5" ss:Pattern="Solid"/&gt;
              &lt;/ss:Style&gt;
              &lt;ss:Style ss:ID="sHeadAC"&gt;
                  &lt;Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/&gt;
                  &lt;ss:Borders&gt;
                      &lt;ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                  &lt;/ss:Borders&gt;
                  &lt;Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#000000"/&gt;
                  &lt;ss:Interior ss:Color="#FFFF66" ss:Pattern="Solid"/&gt;
              &lt;/ss:Style&gt;
              &lt;ss:Style ss:ID="sHeadCT"&gt;
                  &lt;Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/&gt;
                  &lt;ss:Borders&gt;
                      &lt;ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                  &lt;/ss:Borders&gt;
                  &lt;Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#FFFFFF"/&gt;
                  &lt;ss:Interior ss:Color="#FF9900" ss:Pattern="Solid"/&gt;
              &lt;/ss:Style&gt;
              &lt;ss:Style ss:ID="sHeadGD"&gt;
                  &lt;Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/&gt;
                  &lt;ss:Borders&gt;
                      &lt;ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                  &lt;/ss:Borders&gt;
                  &lt;Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#DA9694"/&gt;
                  &lt;ss:Interior ss:Color="#000000" ss:Pattern="Solid"/&gt;
              &lt;/ss:Style&gt;
              &lt;ss:Style ss:ID="sHeadPRM"&gt;
                  &lt;Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/&gt;
                  &lt;ss:Borders&gt;
                      &lt;ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                  &lt;/ss:Borders&gt;
                  &lt;Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#FFFFFF"/&gt;
                  &lt;ss:Interior ss:Color="#92D050" ss:Pattern="Solid"/&gt;
              &lt;/ss:Style&gt;        
              &lt;ss:Style ss:ID="sHeadALG"&gt;
                  &lt;Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/&gt;
                  &lt;ss:Borders&gt;
                      &lt;ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                  &lt;/ss:Borders&gt;
                  &lt;Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#FFFFFF"/&gt;
                  &lt;ss:Interior ss:Color="#92D050" ss:Pattern="Solid"/&gt;
              &lt;/ss:Style&gt;         
              &lt;ss:Style ss:ID="sBody"&gt;
                  &lt;Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/&gt;
                  &lt;ss:Borders&gt;
                      &lt;ss:Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;ss:Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                  &lt;/ss:Borders&gt;
                  &lt;ss:Interior ss:Pattern="Solid"/&gt;
              &lt;/ss:Style&gt;
              &lt;Style ss:ID="SHyperlink" ss:Name="Hyperlink"&gt;
                  &lt;Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/&gt;
                  &lt;Borders&gt;
                      &lt;Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                      &lt;Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/&gt;
                  &lt;/Borders&gt;
                  &lt;Font ss:FontName="Arial" x:Family="Swiss" ss:Size="10" ss:Color="#0000FF" ss:Underline="Single"/&gt;
              &lt;/Style&gt;
          &lt;/ss:Styles&gt;
      &lt;/xsl:template&gt;
  
  
  &lt;/xsl:stylesheet&gt;
  </code></pre>
  
  <p>Here is a sample from my output XML</p>
  
  <pre><code>&lt;Row&gt;
              &lt;Cell ss:StyleID="sBody"&gt;
                 &lt;Data ss:Type="String"&gt;AVISIT&lt;/Data&gt;
              &lt;/Cell&gt;
              &lt;Cell ss:StyleID="sBody"&gt;
                 &lt;ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String"&gt;Set to collected visit name [EG.VISIT] &amp;lt;Font  html:color="#7030A0"&amp;gt;Set to 'POST-BASELINE MINIMUM'&amp;lt;/Font&amp;gt;Set to a re-defined visit range based on user-defined input. &amp;lt;b&amp;gt;Set to 'POST-BASELINE MINIMUM'&amp;lt;/b&amp;gt;&lt;/ss:Data&gt;
              &lt;/Cell&gt;
           &lt;/Row&gt;
           &lt;Row&gt;
              &lt;Cell ss:StyleID="sBody"&gt;
                 &lt;Data ss:Type="String"&gt;AVISITN&lt;/Data&gt;
              &lt;/Cell&gt;
              &lt;Cell ss:StyleID="sBody"&gt;
                 &lt;ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String"&gt;Set to collected &amp;lt;Font  html:color="#0000FF"&amp;gt;Set to 9997&amp;lt;/Font&amp;gt;Set to a user defined numeric value &amp;lt;Font  html:color="#0000FF"&amp;gt;Set to 9997 wih the analysis visit &amp;lt;/Font&amp;gt;&lt;/ss:Data&gt;
              &lt;/Cell&gt;
           &lt;/Row&gt;
  </code></pre>
  
  <p>I've created an xsl and saved as XML spreadsheet 2003 to check the code and is as expected. When I replace &lt; > with <code>&amp;lt;</code> and <code>&amp;gt;</code> I get the issue I am experiencing with my output</p>
  
  <p>I don't know if is related to using <code>&amp;lt;</code> rather than &lt; when creating the xml. Has anybody had a similar problem and solved it? Or can see where my problem lies?</p>
  
  <p>EDIT:</p>
  
  <p>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.</p>
  
  <p>The problem now is that when opened in excel the formatting doesn't display, I get plain black text.</p>
  
  <p><a href="http://i.stack.imgur.com/VeuJB.png" rel="nofollow">Output without formatting</a></p>
  
  <p>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.</p>
  
  <p><a href="http://i.stack.imgur.com/YAjJc.png" rel="nofollow">Excel created example</a></p>
  
  <p>The XML code of the excel created value</p>
  
  <pre><code>   &lt;Row ss:AutoFitHeight="0"&gt;
      &lt;Cell ss:StyleID="s64"&gt;&lt;Data ss:Type="String"&gt;AVISIT&lt;/Data&gt;&lt;/Cell&gt;
      &lt;Cell ss:StyleID="s65"&gt;&lt;ss:Data ss:Type="String"
        xmlns="http://www.w3.org/TR/REC-html40"&gt;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;&lt;/ss:Data&gt;&lt;/Cell&gt;
     &lt;/Row&gt;
     &lt;Row ss:AutoFitHeight="0"&gt;
      &lt;Cell ss:StyleID="s64"&gt;&lt;Data ss:Type="String"&gt;AVISITN&lt;/Data&gt;&lt;/Cell&gt;
      &lt;Cell ss:StyleID="s65"&gt;&lt;ss:Data ss:Type="String"
        xmlns="http://www.w3.org/TR/REC-html40"&gt;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;&lt;/ss:Data&gt;&lt;/Cell&gt;
     &lt;/Row&gt;
  </code></pre>
  
  <p>This is how the xml created by my XSLT looks:</p>
  
  <pre><code>&lt;Row&gt;
      &lt;Cell ss:StyleID="sBody"&gt;
         &lt;Data ss:Type="String"&gt;AVISIT&lt;/Data&gt;
      &lt;/Cell&gt;
      &lt;Cell ss:StyleID="sBody"&gt;
         &lt;ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String"&gt;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;
         &lt;/ss:Data&gt;
      &lt;/Cell&gt;
   &lt;/Row&gt;
   &lt;Row&gt;
      &lt;Cell ss:StyleID="sBody"&gt;
         &lt;Data ss:Type="String"&gt;AVISITN&lt;/Data&gt;
      &lt;/Cell&gt;
      &lt;Cell ss:StyleID="sBody"&gt;
         &lt;ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String"&gt;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;
         &lt;/ss:Data&gt;
      &lt;/Cell&gt;
   &lt;/Row&gt;
  </code></pre>
  
  <p>So despite the fact that the xml code is the same, depending on how it was created defines whether it works or not.</p>
  
  <p>Question:
  Could there be some encoding differences that would explain this behavior? </p>