06. Styling

Chris Bailiss

2019-11-01

In This Vignette

Quick Styling

This ‘Quick Start’ describes one easy method of styling table cells. A fuller explanation is given in the other sections of this vignette.

Cells in a table are styled using CSS. The quickest way to apply styles to individual cells in a table is using:

pt$setStyling(rFrom, cFrom, rTo, cTo, groups, cells, baseStyleName, style, declarations)

To apply styles to a single cell, e.g. at row 1, column 2 (in the body of the table, i.e. excluding the headings):

pt$setStyling(1, 2, declarations=list("font-weight"="bold", "text-align"="center")

To apply styles to a range of cells, e.g. rows 2 to 3 and columns 1 to 5:

pt$setStyling(2, 1, 3, 5, declarations=list("font-weight"="bold", "text-align"="center")

The above examples specify the cells to be styled by their row and column numbers in the body of the table. It is also possible to specify a list of cells using the cells argument or a list of data groups (i.e. row or column headings) using the groups argument. Many examples of these can be found in the Finding and Formatting vignette.

The above examples also set CSS style attributes on the individual cells. This is quick and easy, though very verbose. Named styles are more efficient for large pivot tables (hundreds/thousands of cells) and are described in the following sections.

Styles and Themes

pivottabler can render pivot tables in HTML and Latex. The focus of this vignette is styling when rendering to HTML.

pivottabler offers only limited styling options when rendering to Latex. Please see the Latex Output vignette for details.

When rendered to HTML, the appearance of pivottabler pivot tables can be changed using styles and themes:

The pivottabler package is supplied with some basic themes. Additional themes can be defined.

In addition, for each cell in the table, a set of style overrides can be set. These are style declarations that apply to individual cells.

Styles and CSS

A style is a collection of name-value pairs. The name-value pairs used to specify styles are simply Cascading Style Sheet properties and values. A full introduction and reference for CSS can be found on the w3schools website.

An example of defining a pivottabler style is:

pivotStyles$addStyle(styleName="ColumnHeader", list(
    "font-family"="arial",
    "font-size"="0.75em",
    padding="2px",
    border="1px solid blue",
    "vertical-align"="middle",
    "text-align"="center",
    "font-weight"="bold",
    color="blue",
    "background-color"="#FFFFFF",
    "xl-wrap-text"="wrap"
  ))

When viewing pivot tables in an IDE such as R Studio, then typically only one pivot table is viewed at a time.

If multiple pivot tables are being output together (e.g. into a single HTML page) then specifying a styleNamePrefix in the pt$renderPivot() call is needed. The CSS declarations generated by the pivottabler package for each pivot table will then not overlap with each other. This can be seen in all of the examples in this vignette.

XL Styles

The pivottabler package also supports outputting to Excel files. The Excel output can use CSS styling and/or Excel specific styling. The Excel specific styles are defined alongside the CSS styles and start with “xl-”, e.g. see the example above. The Excel specific styles are ignored by the HTML output. For more details about the Excel export, see the Excel Export vignette.

Included Themes

The pivottabler package includes three basic themes:

# basic theme
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$theme <- "default"  # this theme is already the default, so this line isn't really needed
pt$renderPivot(styleNamePrefix="t0")
# compact theme
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$theme <- "compact"
pt$renderPivot(styleNamePrefix="t1")
# large plain theme
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$theme <- "largeplain"
pt$renderPivot(styleNamePrefix="t2")

Simple Themes

Simple themes can be defined using a list. Several examples are shown below.

# define the font and colours
simpleBlueTheme <- list(
  fontName="Verdana, Arial",
  headerBackgroundColor = "rgb(68, 114, 196)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor = "rgb(255, 255, 255)",
  cellColor = "rgb(0, 0, 0)",
  totalBackgroundColor = "rgb(186, 202, 233)",
  totalColor = "rgb(0, 0, 0)",
  borderColor = "rgb(48, 84, 150)"
)
# create the pivot table
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
# define the theme
pt$theme <- simpleBlueTheme
pt$renderPivot(styleNamePrefix="t3")
# define the font and colours
simpleGrayTheme <- list(
  fontName="Courier New, Courier",
  headerBackgroundColor = "rgb(128, 128, 128)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor = "rgb(255, 255, 255)",
  cellColor = "rgb(0, 0, 0)",
  totalBackgroundColor = "rgb(192, 192, 192)",
  totalColor = "rgb(0, 0, 0)",
  borderColor = "rgb(64, 64, 64)"
)
# create the pivot table
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
# define the theme
pt$theme <- simpleGrayTheme
pt$renderPivot(styleNamePrefix="t4")
# define the font and colours
simpleGreenTheme <- list(
  fontName="Helvetica, arial",
  headerBackgroundColor = "rgb(112, 173, 71)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor="rgb(255, 255, 255)",
  cellColor="rgb(0, 0, 0)",
  totalBackgroundColor = "rgb(182, 216, 158)",
  totalColor="rgb(0, 0, 0)",
  borderColor = "rgb(84, 130, 53)"
)
# create the pivot table
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
# define the theme
pt$theme <- simpleGreenTheme
pt$renderPivot(styleNamePrefix="t6")

Styling with the quick pivot functions

The following example defines a simple theme and supplies this to the quick pivot function to specify styling:

# define the font and colours
simpleBlueTheme <- list(
  fontName="Verdana, Arial",
  headerBackgroundColor = "rgb(68, 114, 196)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor = "rgb(255, 255, 255)",
  cellColor = "rgb(0, 0, 0)",
  totalBackgroundColor = "rgb(186, 202, 233)",
  totalColor = "rgb(0, 0, 0)",
  borderColor = "rgb(48, 84, 150)"
)
# create the pivot table
library(pivottabler)
qhpvt(bhmtrains, "TOC", "TrainCategory", "n()", 
      theme=simpleBlueTheme, styleNamePrefix="qt1")

The next example demonstrates specifying more granular style settings when using a quick pivot function:

# create the pivot table
library(pivottabler)
qhpvt(bhmtrains, "TOC", "TrainCategory", "n()", 
      tableStyle=list("border-color"="maroon"),
      headingStyle=list("color"="cornsilk", "background-color"="maroon", 
                        "font-style"="italic", "border-color"="maroon"), 
      cellStyle=list("color"="maroon", "background-color"="cornsilk", 
                     "border-color"="maroon"),
      totalStyle=list("color"="maroon", "background-color"="cornsilk", 
                     "border-color"="maroon", "font-weight"="bold"),
      styleNamePrefix="qt2")

The quick pivot functions specify the same styling/formatting for all headers and all calculation cells. To specify different styling/formatting for specific data groups and/or specifc calculation cells, use the more verbose syntax for creating the pivot table as shown in the examples below.

Styling Data Groups

The appearance of sets of data groups can be specified when adding the data groups to the pivot table either by specifying a baseStyleName or by specifying styleDeclarations. If specifying a baseStyleName, the full style must be defined. Alternatively specifying a set of style declarations allows just the style settings of interest to be overriden which tends to be simpler, e.g.

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory", styleDeclarations=list("color"="red", "font-weight"="bold", "background-color"="yellow"))
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$renderPivot(styleNamePrefix="tdg")

Styling Calculations

The appearance of calculation headings or cell values can be specified when defining the calculations by specifying any combination of headingBaseStyleName, headingStyleDeclarations, cellBaseStyleName and/or cellStyleDeclarations. If specifying a base style name, the full style must be defined. Alternatively specifying a set of style declarations allows just the style settings of interest to be overriden which tends to be simpler. The example below shows both approaches.

library(pivottabler)
pt <- PivotTable$new()
pt$styles$addStyle(styleName="NewHeadingStyle1", list(
      "font-family"="Arial",
      "font-size"="0.75em",
      padding="2px",
      border="1px solid lightgray",
      "vertical-align"="middle",
      "text-align"="center",
      "font-weight"="bold",
      "background-color"="Gold",
      "xl-wrap-text"="wrap"
    ))
pt$styles$addStyle(styleName="CellStyle1", list(
      "font-family"="Arial",
      "font-size"="0.75em",
      padding="2px 2px 2px 8px",
      border="1px solid lightgray",
      "vertical-align"="middle",
      "background-color"="Yellow",
      "text-align"="right"
    ))
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains1", summariseExpression="n()",
                     headingBaseStyleName="NewHeadingStyle1", cellBaseStyleName="CellStyle1")
pt$defineCalculation(calculationName="TotalTrains2", summariseExpression="n()",
                     headingStyleDeclarations=list("color"="red", "font-weight"="bold"),
                     cellStyleDeclarations=list("color"="blue"))
pt$renderPivot(styleNamePrefix="tcn")

Changing styling/formatting after table creation using pt$setStyling()

The individual cells in the pivot table can have their appearance changed by retrieving the cell then applying a style to the cell. These styles typically apply in addition to the styles coming from the underlying theme, e.g. two different ways for highlighting a cell:

# define the theme
orangeTheme <- list(
  fontName="Garamond, arial",
  headerBackgroundColor = "rgb(237, 125, 49)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor = "rgb(255, 255, 255)",
  cellColor = "rgb(0, 0, 0)",
  totalBackgroundColor = "rgb(248, 198, 165)",
  totalColor = "rgb(0, 0, 0)",
  borderColor = "rgb(198, 89, 17)"
)
# create the pivot table
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
# use the theme
pt$theme <- orangeTheme
# evaluate the pivot to generate the cells
pt$evaluatePivot()
# applying a highlight - method 1 - 3rd row, 1st column
pt$setStyling(3, 1, declarations=list("background-color"="#00FFFF"))
# applying a highlight - method 2 - 3rd row, 2nd column
cellHighlight <- pt$createInlineStyle(declarations=list("background-color"="#FFFF00"))
cells <- pt$getCells(specifyCellsAsList=TRUE, cellCoordinates=list(c(3, 2)))
cells[[1]]$style <- cellHighlight
# draw the pivot table
pt$renderPivot(styleNamePrefix="t9")

In the example above, the default cell style from the theme is still applied. The highlight is applied in addition to the default styling. To stop the base styling being applied, create a new named style and set the baseStyleName property on the cell to the name of the new style.

The pt$setStyling() function is used in the example above. This function has the following arguments:

The pt$createInlineStyle() function is also used in the example above. This function creates a new unnamed style (that is not part of the table theme) and takes two arguments: baseStyleName specifies the name of the style that the new style will be based on (i.e. will have it’s style properties copied from) and declarations which specifies new/overriding style properties for the new style.

For many more examples of styling parts of a pivot table, see the Finding and Formatting vignette.

Creating a New Theme

Since a theme is a collection of styles, creating themes is relatively straightforward. The names of the styles are then assigned to the different types of cells within a pivot table:

E.g. creating a custom theme that displays a mixture of bright colours (a rather ugly bubble-gum theme):

# define the theme and styles
createCustomTheme <- function(parentPivot=NULL, themeName="myCustomTheme") {
  pivotStyles <- PivotStyles$new(parentPivot=parentPivot, themeName=themeName)
  # borders in purple
  pivotStyles$addStyle(styleName="Table", list(
      "border-collapse"="collapse",
      "border"="2px solid #B28DFF"
    ))
  # column headings in pink
  pivotStyles$addStyle(styleName="ColumnHeader", list(
      "font-family"="\"Courier New\", Courier, monospace",
      "font-size"="0.75em",
      "font-weight"="bold",
      padding="2px",
      "border"="2px solid #B28DFF",
      "vertical-align"="middle",
      "text-align"="center",
      "font-weight"="bold",
      color="#DB49AC",
      "background-color"="#FFCCF9",
      "xl-wrap-text"="wrap"
    ))
  # row headings in blue
  pivotStyles$addStyle(styleName="RowHeader", list(
      "font-family"="\"Courier New\", Courier, monospace",
      "font-size"="0.75em",
      "font-weight"="bold",
      padding="2px 8px 2px 2px",
      "border"="1px solid #B28DFF",
      "vertical-align"="middle",
      "text-align"="left",
      "font-weight"="bold",
      color="#438EC8",
      "background-color"="#ACE7FF",
      "xl-wrap-text"="wrap"
    ))
  # cells in yellow
  pivotStyles$addStyle(styleName="Cell", list(
      "font-family"="\"Courier New\", Courier, monospace",
      "font-size"="0.75em",
      padding="2px 2px 2px 8px",
      "border"="1px solid #B28DFF",
      "text-align"="right",
      color="#FF800D",
      "background-color"="#FFFFD1"
    ))
  # totals in orange
  pivotStyles$addStyle(styleName="Total", list(
      "font-family"="\"Courier New\", Courier, monospace",
      "font-size"="0.75em",
      "font-weight"="bold",
      padding="2px 2px 2px 8px",
      "border"="1px solid rgb(84, 130, 53)",
      "text-align"="right",
      color="#3BC6B6",
      "background-color"="#BFFCC6"
    ))
  pivotStyles$tableStyle <- "Table"
  pivotStyles$rootStyle <- "ColumnHeader"
  pivotStyles$rowHeaderStyle <- "RowHeader"
  pivotStyles$colHeaderStyle <- "ColumnHeader"
  pivotStyles$cellStyle <- "Cell"
  pivotStyles$totalStyle <- "Total"
  return(invisible(pivotStyles))
}
# create the pivot table
library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$theme <- createCustomTheme(pt)
pt$renderPivot(styleNamePrefix="t8")

Summary of Styling Rules

pivottabler styles cells using the following rules:

Styling Output

When rendering to HTML, two styles settings are written by pivottabler in the HTML for each table cell:

HTML class attribute

Example: < td class=“StyleName” >

HTML style attribute

Example: < td style=“text-align:left;background-color:yellow;” >

The cell style override declarations (= the declarations of the inline style) are used as the value of the HTML style attribute for each cell.

Styling Reference

A reference table of the supported styling declarations can be found in the Appendix: Details vignette.

Further Reading

The full set of vignettes is:

  1. Introduction
  2. Data Groups
  3. Calculations
  4. Outputs
  5. Latex Output
  6. Styling
  7. Finding and Formatting
  8. Cell Context
  9. Irregular Layout
  10. Performance
  11. Shiny
  12. Excel Export
  13. Appendix: Details
  14. Appendix: Calculations
  15. Appendix: Class Overview