Simply Measured Engineering Blog

Simply Measured Report Themes

Report Themes

The first project assigned to me at Simply Measured was simply called “themes” or “themeing”. Themes would offer our customers the ability to change the colors of their reports. At the time reports were offered in only one dark style – predominately grays and blues on a black background. Users wanted the ability to customize reports in their brand colors or custom theme colors. They had also requested a lighter and brighter version of the standard theme.

Simply Measured Reports on the web

Some background information

When viewing a Simply Measured report on the web a few things are happening behind the scenes.

Excel and online versions of the same report

Excel File

Your Simply Measured report is an Excel .XLSX file. A .XLSX is essentially many directories and XML files compressed into a ZIP archive. Take a .xlsx file and change the extension to .zip. The structure of the uncompressed files is defined by the Office Open XML File Formats Standard (ECMA-376).

Magic

The Simply Measured application is performing some magic to parse all of the XML files, read them according to the rules defined in ECMA-376 and extract the necessary information needed to display our pretty charts in your browser. Docx4j is just one of the open source libraries helping us to do this.

JSON

The data extracted from the XLSX is then serialized to JSON, a format that the front end of the application can understand. The result is pretty charts in your browser just as you would see them in Excel.

Themes – How does it all work?

Well, it’s complicated.

The ECMA-376 specification defines the theme of a document as “the color scheme, fonts, and effects, which can be referenced by parts of the document—such as text, drawings, charts and diagrams”.

Great! We can just swap out the theme files specified in the spec and there you have it: report themes! Not so fast. It turns out that Excel only supports a limited number of colors within a theme and that limit is far less than the number of colors found throughout our reports. So if Excel only supports a limited number of colors, how is it supporting the range we’re using now? It turns out that colors are strewn through almost every XML file within the structure of a XLSX. They’re in themes, theme overrides, styles, charts, and drawings. This makes it impossible to switch out the theme and call it a “themed report”.

In the end it was decided that a one-to-one color mapping was necessary to transform a report from the dark theme to the light theme (or any other theme). This mapping would then be used to replace any XML file that contained RGB values. On top of that, in some cases a line width in a dark report may be too garish in a light report. A gradient in a dark report may need a larger range of colors in a lighter theme. This definitely wasn’t going to be as easy as replacing the theme.

The technology to solve the problem.

Programmatically re-themeing an Excel spreadsheet wasn’t something you could Google or find the solution to on stackoverflow.com. Believe me, I tried. This was a new and interesting problem. When it came to our solution we didn’t use anything that I would consider “cool” or “cutting edge” in development of this feature. We used the right tools for the job; we did cool things with “common” tools.

XSLT and XPath

XSLT (Extensible Stylesheet Language Transformations) is a transformation tool which gives you the ability to transform any XML file into another format. The XSLT processor takes an XML source file, applies a XSLT stylesheet to that file and the result is the requested format. XSLT helped us to do a transformation on the XML files to change one RGB color (or any other attribute) to another value. XSLT stylesheets use XPath (XML Path Language) to do pattern matching and define replacements for those patterns.

Example XSLT stylesheet:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" >

    <xsl:template match="@*|node()">
     <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
     </xsl:copy>
    </xsl:template>

    <xsl:template match="/c:chartSpace//a:srgbClr[@val=&apos;000000&apos;]">
     <xsl:copy>
         <xsl:attribute name="val">
            <xsl:value-of select="111111"/>
         </xsl:attribute>
     </xsl:copy>
    </xsl:template>
</xsl:stylesheet>

The resulting XML file will contain only nodes that matched one of the xsl:template matches. The first xsl:template will simply copy all nodes to the result. The second xls:template uses the XPath expression to find all a:srgbClr elements that are children of the c:chartSpace element with a @val attribute equal to ‘000000’. The xsl:copy of that template will replace black (000000) with white (FFFFFF).

For example:

<c:spPr>
      <a:ln w="9525">
            <a:solidFill>
    <a:srgbClr val="000000"/>
           </a:solidFill>
      </a:ln>
</c:spPr>

Will become:

<c:spPr>
      <a:ln w="9525">
            <a:solidFill>
    <a:srgbClr val="111111"/>
           </a:solidFill>
      </a:ln>
</c:spPr>

With Simply Measured report themes, once the stylesheet is applied to the appropriate XML files, the entire structure is compressed back into a XLSX file that is then viewable in Excel or in our web-based report viewer.

Testing with XMLUnit

Our XSLT Stylesheets were huge – sometimes thousands of lines long. How do we make sure all of the expected transformations were made to the resulting file? We needed a way to compare the results of each XPath expression on the original file the new themed file. We’re using about 30 distinct XPath expressions which are applied hundreds of times to dynamic color combinations. This is where XMLUnit comes into our JUnit tests. XMLUnit allows you to evaluate XPath expressions on XML and provides a list of nodes that match that expression.

To test the previous example you would need to write a test class that extends org.custommonkey.xmlunit.XMLTestCase. Here is an example of what a test method could look like to test the change from RGB 000000 to 111111.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
/**
 * 
 * @param originalDoc original document
 * @param transformedDoc document transformed by XSLT
 * @param beforeRGB original RGB color
 * @param expectedRGB theme RGB color
 * @throws XpathException
 */
public void testColorReplacement(Document originalDoc, Document transformedDoc, String beforeRGB, 
    String expectedRGB) throws XpathException
{ 
  XpathEngine engine = createXpathEngine();
  String xpathExpression = "/c:chartSpace//a:srgbClr";
  
  NodeList originalNodes = engine.getMatchingNodes(xpathExpression, originalDoc);
  NodeList transformedNodes = engine.getMatchingNodes(xpathExpression, transformedDoc);

    Assert.assertEquals(originalNodes.getLength(), transformedNodes.getLength());

    for(int i = 0; i < originalNodes.getLength(); i++){
      if(originalNodes.item(i).getAttributes().getNamedItem("val").getNodeValue().equals(beforeRGB))
      {
          Assert.assertEquals("RGB color replacement failure", expectedRGB, 
              transformedNodes.item(i).getAttributes().getNamedItem("val").getNodeValue());
      }
    }
}

private XpathEngine createXpathEngine(){
  HashMap<String, String> m = new HashMap<String, String>();
  m.put("xsl", "http://www.w3.org/1999/XSL/Transform");
  m.put("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
  m.put("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
    NamespaceContext ctx = new SimpleNamespaceContext(m);
    XpathEngine engine = XMLUnit.newXpathEngine();
    engine.setNamespaceContext(ctx);
    
    return engine;
}