Data Cell Colour Rules

To improve the visualisation of data within table widgets, different cell shading can be applied based on the data value. For example, cells with values above 50 could be shaded red and cells with values below 50 in green.  This style of data presentation has been referred to as “tartan rug”.  It can be achieved using the following advanced settings for the table widget Borders, Colours & Styles setting:

Data Cell Colour Rules – a comma-delimited string where each delimited value has the form <value>|<rule>|<style>.

<rule> is one of:

  • eq (equal)
  • neq (not equal)
  • gt (greater than)
  • gte (greater than or equal to)
  • lt (less than)
  • lte (less than or equal to)

 <style> is either of form:

<color>;<font-weight>;<font-style>;<font-size>

OR

class:<css-class-name>

This is used in conjunction with:

Cell Rule Behaviour – Text or Background. If Text, then the above colour rules will apply to text colour.  If Background, the background of the cell will be shaded instead.

For example, to shade cells red where the percentage of benefit claimants is above 50 can be achieved under the Advanced Settings by giving Data Cell Colour Rules a value of ‘50|gt|#ff0000’ (Figure 1).  Note that there are many web pages that list colour codes, such as http://www.w3schools.com/colors/colors_picker.asp.

Figure 1

tablecellcolourrulesfig1

Figure 2 shows the resulting table.

Figure 2

tablecellcolourrulesfig2

To display cells that have values above 50 shaded red and those with values equal to or less than 50 shaded green, the setting rule would be ‘50|gt|#ff0000,50|lte|#00ff00’ (Figure 3).

Figure 3

tablecellcolourrulesfig3

To assign more than two colours e.g. cells with values above 50 shaded red, cells with values between 50 and 20 amber and those with values equal to or less than 20 shaded green, the setting rule would be ‘50|gt|#ff0000,20|lte|#00ff00,50|lt|#ffbf00’ (Figure 4).

Figure 4

tablecellcolourrulesfig4

Please note that the order of the rules is important. The string is read from left to right and as soon as the table widget finds a rule that is true, it applies the respective style and then ignores all further rules. So giving the Rule a value of ‘50|gt|#ff0000,50|lte|#ffbf00,20|lte|#00ff00’ will not shade any cells green.

To shade the values rather than the cells, change the Cell Rule Behaviour to ‘Text’. The table widget in 5 is using a Data Cell Color Rules value of ‘50|gt|#ff0000,50|lte|#00ff00’ (the same as for Figure 3).

Figure 5

tablecellcolourrulesfig5

To display values above 50 as red, bold, italic and 1.2 times the base font size and those equal to or less than 50 to be green and bold give the Data Cell Colour Rules a value of ‘50|gt|#ff0000;bold;italic;1.2em,50|lte|#00ff00;bold’ (Figure 6).

Figure 6

tablecellcolourrulesfig6

Note that 1em is equal to the current font size.  2em means 2 times the size of the current font.  The ’em’ is a very useful unit in CSS, since it can adapt automatically to the font that the reader uses.

It is also possible to substitute text values on the basis of colour rules as well as making use of the styling functionality. The same code could be adapted to display the table as in Figure 6 but amended by using a further | character after the style rules.

‘50|gt|#ff0000;bold;italic;1.2em|High,50|lte|#00ff00;bold|Low’

This would display High or Low values as text (Figure 7 and Figure 8), rather that displaying the number (Figure 6)

Figure 7

Cell Rule Behaviour set to ‘Text’

tablecellcolourrulesfig7

 

Figure 8

Cell Rule Behaviour set to ‘Background’

tablecellcolourrulesfig8