Excel Spreadsheet Dynamic cell colouring using XML

Question

I got an XML source file (from other source) and its respective XSL file to get the XML transformed into Excel Spreadsheet.

What i need is to dynamically change background color of cell in spreadsheet based on XML attribute for a node using XSL

Example: Source.XML content

<workbooks>
  <Wrkbook
    <table Id="My table 1">
      <Colnames>
        <ColumnName>Student ID</ColumnName>
        <ColumnName>Student Name</ColumnName>
        <ColumnName>Subject 1</ColumnName>
        <ColumnName>Subject 2</ColumnName>
        <ColumnName>Subject 3</ColumnName>
        <ColumnName>Subject 4</ColumnName>
        <ColumnName>Subject 5</ColumnName>
        <ColumnName>Subject 6</ColumnName>
      </Colnames>
      <Rows>
        <CI>534</CI>
        <CI>Ramu</CI>
        <CI>67</CI>
        <CI Colour="Green">67</CI>
        <CI Colour="#e8e9e8">48</CI>
        <CI>66</CI>
        <CI Colour="#B3C389">39</CI>
        <CI>67</CI>
      </Rows>
      <Rows>
        <CI>534</CI>
        <CI>Raul</CI>
        <CI Colour="Green">63</CI>
        <CI>89</CI>
        <CI Colour="#007788">67</CI>
        <CI>57</CI>
        <CI>75</CI>
        <CI Colour="#AABBCC">92</CI>
      </Rows>
    </table>
  </Wrkbook>
</workbooks>

What i used to do earlier is i used to have a specific style for green and Yelow in XSL sheet for the attributes in their respective XML tags, hence i could manage it. But now the source file contains color codes and i need to highlight based on color code. XSL File :

<xsl:template match='/'>
    <Workbook>
      <Styles>
         <Style ss:ID="Green">
          <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="Calibri" x:Family="Swiss" ss:Size="11"/>
          <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
        </Style>
      </Styles>
      <xsl:for-each select='workbooks'>
        <xsl:for-each select='Wrkbook'>
          <Worksheet ss:Name='Scores'>
            <Table x:FullColumns='1' x:FullRows='1'>                 
             <xsl:for-each select='table'>
                <Row>
                  <xsl:for-each select='Colnames/ColumnName'>
                    <Cell ss:StyleID='s41'>
                      <Data ss:Type='String'>
                        <xsl:value-of select='.' disable-output-escaping='yes'/>
                      </Data>
                    </Cell>
                  </xsl:for-each>
                </Row>
                <xsl:for-each select='Rows'>
                  <Row>
                    <xsl:for-each select='CI'>
                      <xsl:choose>
                        <xsl:when test="@Colour = 'Green'">
                          <Cell ss:StyleID='Green'>
                            <Data ss:Type='String'>
                              <xsl:value-of select='.' disable-output-escaping='yes'/>
                            </Data>
                          </Cell>
                        </xsl:when>
                        <xsl:otherwise>
                          <Cell ss:StyleID='s38'>
                            <Data ss:Type='String'>
                              <xsl:value-of select='.' disable-output-escaping='yes'/>
                            </Data>
                          </Cell>
                        </xsl:otherwise>
                      </xsl:choose>
                    </xsl:for-each>
                  </Row>
                </xsl:for-each>
                <Row>
                  <Cell></Cell>
                </Row>
              </xsl:for-each>
            </Table>
          </Worksheet>
        </xsl:for-each>
      </xsl:for-each>
    </Workbook>
  </xsl:template>

Please ignore if there are syntax errors in XSL, just i am looking for highlighting the spreadsheet color based on XML attribute dynamically.

Thank you!!

Answer 1

It's much easier to work with individual templates instead of many nested for-each elements. So I would suggest reorganizing the entire stylesheet so each template can deal with a part of the source tree. Also, since you have to generate additional data which doesn't follow the structure of the source tree, it would be appropriate to have named templates that you could call. I organized this answer in two parts. The second part will attempt to answer your question.

Part 1: refactoring the stylesheet

In the template that matches root (/) you would place only the invariant basic structure of the file. Inside it you can use <apply-templates select="*/*/table"/> so select the templates that will match the data in your table (skipping workbooks and Wrkbook). Since the header involves the creation of styles based on colours, we will deal with it later. The required <?mso-application progid="Excel.Sheet" ?> processing instruction is also generated:

<xsl:template match='/'>
    <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
    <Workbook>
        <Styles>
            <!-- make Style elements for each color here -->
        </Styles>
        <Worksheet ss:Name='Scores'>
            <Table x:FullColumns='1' x:FullRows='1'>    
                <xsl:apply-templates select="*/*/table"/> <!-- will process the rest of the tree -->
            </Table>
        </Worksheet>
    </Workbook>
</xsl:template>

The template for table is also very simple. Based on the stylesheet you provided, it consists on one <Row> with data from the Colnames/ColumnName elements in your source XML, several <Row> elements from each <Rows> element, and one empty <Row> containing an empty <Cell>. This can be rewritten like this:

<xsl:template match="table">             
    <Row><xsl:apply-templates select='Colnames/ColumnName'/></Row>
    <xsl:apply-templates select='Rows'/>
    <Row><Cell/></Row>
</xsl:template>

There are two <xsl:apply-templates/>. The first one will call a template to build the first <Row> containing several ColumnNames. The second will build the individual data rows. This is the template which will be called for each ColumnName:

<xsl:template match="ColumnName">
    <Cell ss:StyleID='s41'>
        <Data ss:Type='String'>
            <xsl:value-of select='.' disable-output-escaping='yes'/>
        </Data>
    </Cell>
</xsl:template>

And this one will be called for each Rows:

<xsl:template match="Rows">
    <Row><xsl:apply-templates select='CI'/></Row>
</xsl:template>

Again another <xsl:aply-templates/>. This one will process each CI element which will contain the Cell data. It will have to read the Colour attribute from each element and decide how to render it. Currently you only need to distinguish Green and nothing. We could rewrite it as:

<xsl:template match="CI">
    <xsl:choose>
        <xsl:when test="@Colour = 'Green'">
            <Cell ss:StyleID='Green'>
                <xsl:call-template name="print-cell"/>
            </Cell>
        </xsl:when>
        <xsl:otherwise>
            <Cell ss:StyleID='s38'>
                <xsl:call-template name="print-cell"/>
            </Cell>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

removing duplication and placing the cell printing in a named template, which we call with <xsl:call-template>:

<xsl:template name="print-cell">
    <Data ss:Type='String'>
        <xsl:value-of select='.' disable-output-escaping='yes'/>
    </Data>
</xsl:template>

Part 2: Adding colour information

Now that the stylesheet is reorganized in smaller templates, we can proceed to adapt it so it will read colour information correctly.

First we should make a key containing all the colours that are avaliable:

<xsl:key name="colours" match="CI/@Colour" use="."/>

This will match all Colour attributes in all CI elements. We can use the contents of the Colour attribute to select it.

In the root template, we can build the <Styles> block by building a Style element for each colour. We will do that in a separate template, but first we need to iterate among the unique colours in your document. This can be done by using the Muenchian grouping technique in XSLT 1.0 (if you are using XSLT 2.0 you can handle this in a simpler way using for-each-group):

<Styles>
    <xsl:for-each select="//CI/@Colour[count(. | key('colours', .)[1]) = 1]">
        <xsl:call-template name="make-style">
            <xsl:with-param name="colour" select="."/>
        </xsl:call-template>
    </xsl:for-each>
</Styles>

This will call the make-style template for each unique colour found in your source document. The template is being called with a parameter colour which contains the current colour.

We need IDs for each colour. The colour name could be used but the ID can't start with a hash, so one way to solve the problem is use the colour code removing the #. You can use the XPath translate() function for that: translate($colour,'#','').

Since your source data still uses the Green colour, and it doesn't follow that pattern, we have to deal with it separately. To set the Style ss:ID attribute we need to do use the contents of the original attribute if the $colour is Green and strip the # otherwise:

<xsl:choose>
    <xsl:when test="$colour = 'Green'">
       <xsl:value-of select="$colour"/>
    </xsl:when>
    <xsl:otherwise>
        <xsl:value-of select="translate($colour,'#','')"/>
    </xsl:otherwise>
</xsl:choose>

We also have to do that to build the Interior element:

<xsl:choose>
    <xsl:when test="$colour = 'Green'">
        <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
    </xsl:when>
    <xsl:otherwise>
        <Interior ss:Color="{$colour}" ss:Pattern="Solid"/>
    </xsl:otherwise>
</xsl:choose>

This is the final make-style template:

<xsl:template name="make-style">
    <xsl:param name="colour"/>
    <Style ss:ID="Green">
        <xsl:attribute name="ss:ID">
            <xsl:choose>
                <xsl:when test="$colour = 'Green'">
                   <xsl:value-of select="$colour"></xsl:value-of>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:value-of select="translate($colour,'#','')"/>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:attribute>
        <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="Calibri" x:Family="Swiss" ss:Size="11"/>
        <xsl:choose>
            <xsl:when test="$colour = 'Green'">
                <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
            </xsl:when>
            <xsl:otherwise>
                <Interior ss:Color="{$colour}" ss:Pattern="Solid"/>
            </xsl:otherwise>
        </xsl:choose>
    </Style>
</xsl:template>

Now we can refactor the CI template adding a xsl:when block to generate a cell with a StyleID for the appropriate colour:

    <xsl:template match="CI">
    <xsl:choose>
        <xsl:when test="@Colour = 'Green'">
            <Cell ss:StyleID='Green'>
                <xsl:call-template name="print-cell"/>
            </Cell>
        </xsl:when>
        <xsl:when test="starts-with(@Colour, '#')">
            <Cell ss:StyleID="{translate(@Colour,'#','')}">
                <xsl:call-template name="print-cell"/>
            </Cell>
        </xsl:when>
        <xsl:otherwise>
            <Cell ss:StyleID='s38'>
                <xsl:call-template name="print-cell"/>
            </Cell>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

This is the final stylesheet.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="xs"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    version="1.0">
    <xsl:output indent="yes"/>

    <xsl:key name="colours" match="CI/@Colour" use="."/>

    <xsl:template match='/'>
        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        <Workbook>
            <Styles>
                <xsl:for-each select="//CI/@Colour[count(. | key('colours', .)[1]) = 1]">
                    <xsl:call-template name="make-style">
                        <xsl:with-param name="colour" select="."/>
                    </xsl:call-template>
                </xsl:for-each>
            </Styles>
            <Worksheet ss:Name='Scores'>
                <Table x:FullColumns='1' x:FullRows='1'>    
                    <xsl:apply-templates select="*/*/table"/>
                </Table>
            </Worksheet>
        </Workbook>
    </xsl:template>

    <xsl:template match="table">             
        <Row><xsl:apply-templates select='Colnames/ColumnName'/></Row>
        <xsl:apply-templates select='Rows'/>
        <Row><Cell/></Row>
    </xsl:template>

    <xsl:template match="ColumnName">
        <Cell ss:StyleID='s41'>
            <Data ss:Type='String'>
                <xsl:value-of select='.' disable-output-escaping='yes'/>
            </Data>
        </Cell>
    </xsl:template>

    <xsl:template match="Rows">
        <Row><xsl:apply-templates select='CI'/></Row>
    </xsl:template>

    <xsl:template name="make-style">
        <xsl:param name="colour"/>
        <Style ss:ID="Green">
            <xsl:attribute name="ss:ID">
                <xsl:choose>
                    <xsl:when test="$colour = 'Green'">
                       <xsl:value-of select="$colour"></xsl:value-of>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:value-of select="translate($colour,'#','')"/>
                    </xsl:otherwise>
                </xsl:choose>
            </xsl:attribute>
            <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="Calibri" x:Family="Swiss" ss:Size="11"/>
            <xsl:choose>
                <xsl:when test="$colour = 'Green'">
                    <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
                </xsl:when>
                <xsl:otherwise>
                    <Interior ss:Color="{$colour}" ss:Pattern="Solid"/>
                </xsl:otherwise>
            </xsl:choose>
        </Style>
    </xsl:template>

    <xsl:template match="CI">
        <xsl:choose>
            <xsl:when test="@Colour = 'Green'">
                <Cell ss:StyleID='Green'>
                    <xsl:call-template name="print-cell"/>
                </Cell>
            </xsl:when>
            <xsl:when test="starts-with(@Colour, '#')">
                <Cell ss:StyleID="{translate(@Colour,'#','')}">
                    <xsl:call-template name="print-cell"/>
                </Cell>
            </xsl:when>
            <xsl:otherwise>
                <Cell ss:StyleID='s38'>
                    <xsl:call-template name="print-cell"/>
                </Cell>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>

    <xsl:template name="print-cell">
        <Data ss:Type='String'>
            <xsl:value-of select='.' disable-output-escaping='yes'/>
        </Data>
    </xsl:template>

</xsl:stylesheet>

You can see the results and make adjustments in this XSLT fiddle.