In cbailiss/pivottabler: Create Pivot Tables

In This Vignette

• Quick Styling
• Styles and Themes
• Styles and CSS
• XL Styles
• Included Themes
• Simple Themes
• Styling with the quick pivot functions
• Styling Data Groups
• Styling Calculations
• Changing styling/formatting using pt$setStyling()
• Formatting cell borders for specific cells
• Changing styling/formatting using pt$mapStyling()
• Inheritance of Styles
• Creating a New Theme
• Summary of Styling Rules
• Styling Output
• Styling Reference
• Further Reading

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, rowNumbers, columnNumbers, 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, use either:

pt$setStyling(2, 1, 3, 5, declarations=list("font-weight"="bold", "text-align"="center")
or
pt$setStyling(rowNumbers=2:3, columnNumbers=1: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.

To apply styles based on the value of each cell, use the mapStyling() function described later in this vignette.

The above examples set CSS style attributes on the individual cells. This is quick and easy, though is verbose in the generated HTML. 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:

• A style defines the appearance of a cell in the pivot table.
  • A cell here is a general term that covers row/column headings, values in the body of the pivot table and totals.
• A style has a name and a set of style settings.
• Each pivot table has a collection of styles.
• For each cell in the pivot table, a style name can be set.
• A theme is a collection of styles that can easily be applied to a pivot table.

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 four basic themes:

Default 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")

Standard Table Theme

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC", header="Train Company")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$theme <- "standardtable"
pt$renderPivot(styleNamePrefix="t2", showRowGroupHeaders=TRUE)

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",
  fontSize="0.75em",
  headerBackgroundColor = "rgb(68, 114, 196)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor = "rgb(255, 255, 255)",
  cellColor = "rgb(0, 0, 0)",
  outlineCellBackgroundColor = "rgb(186, 202, 233)",
  outlineCellColor = "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",
  fontSize="0.75em",
  headerBackgroundColor = "rgb(128, 128, 128)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor = "rgb(255, 255, 255)",
  cellColor = "rgb(0, 0, 0)",
  outlineCellBackgroundColor = "rgb(192, 192, 192)",
  outlineCellColor = "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",
  fontSize="0.75em",
  headerBackgroundColor = "rgb(112, 173, 71)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor="rgb(255, 255, 255)",
  cellColor="rgb(0, 0, 0)",
  outlineCellBackgroundColor = "rgb(182, 216, 158)",
  outlineCellColor = "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",
  fontSize="0.75em",
  headerBackgroundColor = "rgb(68, 114, 196)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor = "rgb(255, 255, 255)",
  cellColor = "rgb(0, 0, 0)",
  outlineCellBackgroundColor = "rgb(186, 202, 233)",
  outlineCellColor = "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 specific 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 overridden 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 overridden 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 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",
  fontSize="0.75em",
  headerBackgroundColor = "rgb(237, 125, 49)",
  headerColor = "rgb(255, 255, 255)",
  cellBackgroundColor = "rgb(255, 255, 255)",
  cellColor = "rgb(0, 0, 0)",
  outlineCellBackgroundColor = "rgb(248, 198, 165)",
  outlineCellColor = "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 (preferred) - 3rd row, 1st column
pt$setStyling(3, 1, declarations=list("background-color"="#00FFFF"))
# applying a highlight - method 2 (legacy) - 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:

• rFrom and cFrom are the coordinates of the cell where the styling is to be applied, excluding the row and column headers, i.e. (1, 1) is the first data cell. If the styling is being applied to multiple cells, this is the top-left cell in the rectangular cell range where the styling is to be applied.
• rTo and cTo are the coordinates of the bottom right cell when styling is being applied to a rectangular range of cells. If styling is only being applied to a single cell, these arguments are not needed.
• rowNumbers and columnNumbers are integer vectors of row and/or column numbers specifying where the styling is to be applied. If both rowNumbers and columnNumbers are specified, then the cells at the intersection of the specified row numbers and column numbers are styled.
• groups is a list of data groups to apply styling to. Either rFrom/cFrom, groups or cells is typically specified.
• cells is a list of cells to apply styling to. Either rFrom/cFrom, groups or cells is typically specified.
• baseStyleName is the name of a style in the table theme to apply to the specified cells. Set this explicitly to NULL to revert to the default style specified in the table theme.
• style is a TableStyle override object to apply to the specified cells. This is created by calling tbl$createInlineStyle() as shown in the example above.
• declarations is a list of style override declarations to apply to the specified cells.
• applyBorderToAdjacentCells is a logical value - specify TRUE to override the border in neighbouring cells, e.g. the left border of the current cell becomes the right border of the cell to the left. Does not apply to row/column groups.

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.

pt$setStyling() Example

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addColumnDataGroups("Status")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$setStyling(groups=pt$leafColumnGroups[[4]], declarations=list("background-color"="peachpuff"))
pt$setStyling(columnNumbers=2:3, declarations=list("background-color"="yellow"))
pt$setStyling(rowNumbers=c(2, 4), columnNumbers=c(5, 7), declarations=list("background-color"="pink"))
pt$setStyling(rFrom=1, rTo=2, cFrom=8, cTo=9, declarations=list("background-color"="lightgreen"))
pt$setStyling(cells=pt$getCell(5, 9), declarations=list("background-color"="lightblue"))
pt$renderPivot()

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

Formatting cell borders for specific cells

CSS rendering rules mean that the first encountered border setting is used. When table cells have no separation, this means the left/top borders of a cell are actually defined by the right/bottom borders of the previous cells (i.e. to the left and above). This means attempting to set the border of a specific cell often doesn't produce the desired effect, e.g. attempting to change the border of a specific cell to red:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$setStyling(rowNumbers=3, columnNumbers=2, 
               declarations=list("border"="1px solid red"))
pt$renderPivot()

This can be resolved by specifying applyBorderToAdjacentCells=TRUE (only for cells in the body of the pivot table - not row/column headings):

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$setStyling(rowNumbers=3, columnNumbers=2, 
               declarations=list("border"="1px solid red"),
               applyBorderToAdjacentCells=TRUE)
pt$renderPivot()

Changing styling/formatting using pt$mapStyling()

Sometimes it is desirable to set the styling more dynamically, i.e. the styling of each cell should vary according to the value of the each cell, e.g. increasingly large numbers have an increasingly intense red cell background colour. This can be done with the mapStyling() method, which has the following arguments:

• styleProperty - the name of the style property to set e.g. "background-color".
• cells - a list specifying the PivotCell objects to be styled.
• valueType - the type of style value to be set - one of the following values: text/character, number/numeric, or color/colour
• mapType - the type of mapping to be performed - the following mapping types are supported:
  • value - a 1:1 mapping which maps each specified "from" value to the corresponding "to" value, e.g. 100 -> "green".
  • logic - each from value is logical criteria - see more details below.
  • range - values between each pair of "from" values are mapped to the corresponding "to" value, e.g. values in the range 80-100 -> "green".
  • continuous - rescales values between each pair of "from" values into the range of the corresponding pair of "to" values, e.g. if the "from" range is 80-100 and the corresponding "to" range is 0.8-1, then 90 -> 0.9.
• mappings - the mappings to be applied, specified in one of the following three forms:
  • a list containing pairs of values, e.g. list(0, "red", 0.4, "yellow", 0.8, "green")
  • a list containing "from" and "to" vectors/lists, e.g. list(from=c(0, 0.4, 0.8), to=c("red", "yellow", "green"))
  • a custom mapping function that will be invoked once per cell, e.g. function(v, cell) { if(isTRUE(v>0.8)) return("green") }.
• styleLowerValues - a logical value, default FALSE, that specifies whether values less than the lowest specified "from" value should be styled using the style specified for the lowest "from" value.
• styleHigherValues - a logical value, default TRUE, that specifies whether values greater than the highest specified "from" value should be styled using the style specified for the highest "from" value.

mapType="logical" is a generic way of specifying mapping rules, which can be of any of the following forms:

• a specific value, e.g. 12.
• a specific value equality condition, e.g. "v==12", where v represents the cell value.
• a value range expression using the following abbreviated form: "value1<=v<value2", e.g. "10<=v<15".
  • Only "<" or "<=" can be used in these abbreviated value range expressions.
• a standard R logical expression, e.g. "10<=v && v<15".
  • Basic R functions that test the value can also be used, e.g. "is.na(v)".

Further Notes:

• mapType="continuous" cannot be used with valueType="text"/"character".
• A range specified as list(0, "red", 1000) is equivalent to "0<=v<1000", i.e. the upper value is excluded from the range.
• styleLowerValues and styleHigherValues only apply when valueType is "range" or "continuous".
• Mappings must be supplied in ascending order when valueType is "range" or "continuous".

Style mapping example with mapType="logic"

The example pivot table below maps cell colours from cell values using conditional/logical expressions:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$mapStyling(cells=pt$allCells, styleProperty="background-color", 
              valueType="color", mapType="logic",
              mappings=list("v==3079", "skyblue", "v<1000", "orange", 
                            "5000<=v<15000", "yellow", "v>50000", "green"))
pt$mapStyling(cells=pt$allCells, styleProperty="color", valueType="color", 
              mapType="logic", mappings=list("v>50000", "white"))
pt$mapStyling(cells=pt$allCells, styleProperty="background-color", 
              valueType="text", mapType="logic",
              mappings=list("is.null(v)", "red"))
pt$renderPivot()

Style mapping example with mapType="range"

The example pivot table below maps cell colours from cell values using ranges, i.e. 0-1000 -> "red" and 1000-15000 -> "orange":

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
pt$mapStyling(cells=pt$allCells, styleProperty="background-color", valueType="color", mapType="range",
              mappings=list(0, "red", 1000, "orange", 15000), styleHigherValues=FALSE)
pt$renderPivot()

Style mapping example with mapType="continuous"

An example of styling using the "continuous" map type can be found in the Finding and Formatting vignette.

Style mapping example with mapType="function"

The example pivot table below maps cell colours using a custom function:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
fx <- function(v, cell) {
  if(isTRUE(0<=v && v<1000)) return("red")
  if(isTRUE(1000<=v && v<15000)) return("orange")
}
pt$mapStyling(cells=pt$allCells, styleProperty="background-color", mappings=fx)
pt$renderPivot()

The cell argument passed to the function is the current cell being styled, e.g. to format based on the column number:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
fx <- function(v, cell) {
  clr <- 255-floor(140*cell$columnNumber/3)
  return(paste0("#",
                format(as.hexmode(255), width=2),
                format(as.hexmode(clr), width=2),
                format(as.hexmode(clr), width=2)))
}
pt$mapStyling(cells=pt$allCells, styleProperty="background-color", mappings=fx)
pt$renderPivot()

Or even styling using random colours:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
pt$addColumnDataGroups("TrainCategory")
pt$addRowDataGroups("TOC")
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()")
pt$evaluatePivot()
fx <- function(v, cell) {
  return(paste0("#",
                format(as.hexmode(sample(160:255, 1)), width=2),
                format(as.hexmode(sample(160:255, 1)), width=2),
                format(as.hexmode(sample(160:255, 1)), width=2)))
}
pt$mapStyling(cells=pt$allCells, styleProperty="background-color", mappings=fx)
pt$renderPivot()

Inheritance of Styles

Both named styles and additional style declarations can be specified for:

• Row data groups
• Column data groups
• Calculations
• Cells

Data groups have style properties for both the data group itself (baseStyleName and style) and the related cells (cellBaseStyleName and cellStyle).

Calculations have style properties for the calculation headings (headingBaseStyleName and headingStyleDeclarations) and the related cells (cellBaseStyleName and cellStyle).

This means there are several different ways styles can be specified for the cells within a pivot table. A set of precedence rules are used to determine how cell styles are inherited.

Style Names

Style names apply to a cell in the following precedence order:

1. baseStyleName set on the cell.
2. cellBaseStyleName set on the calculation.
3. cellBaseStyleName set on the column group related to the cell.
4. cellBaseStyleName set on the row group related to the cell.

As soon as a cell style name is encountered, stop, e.g. if a cell style name is not set on the cell, but is set in a calculation, then the calculation style name will be used, irrespective of the style names that may be set on the row or column groups.

Style declarations

Style declarations apply in the same precedence order as described above, with the additional note the the precedence rules apply for each style property individually, e.g. it is possible for the colour to be set directly on the cell, while the font is inherited from the column data group.

The following example illustrates different cell style settings being set and inherited from row/column groups, calculations and cells:

library(pivottabler)
pt <- PivotTable$new()
pt$addData(bhmtrains)
# add column groups
pt$addColumnDataGroups("TrainCategory")
cgrps <- pt$addColumnDataGroups("PowerType")

# text colour comes from the column data groups
colorText <- function(grp) {
  if(isTRUE(grp$values=="DMU")) grp$cellStyle <- list(color="blue")
  else if(isTRUE(grp$values=="EMU")) grp$cellStyle <- list(color="green")
  else if(isTRUE(grp$values=="HST")) grp$cellStyle <- list(color="red")
  else if(isTRUE(grp$isTotal)) grp$cellStyle <- list(color="brown")
}
invisible(lapply(cgrps, colorText))

# add row groups
rgrps <- pt$addRowDataGroups("TOC", atLevel=1, addTotal=FALSE)

# background colour comes from the row data groups
colorText <- function(grp) {
  if(isTRUE(grp$values=="Arriva Trains Wales")) grp$cellStyle <- list("background-color"="aliceblue")
  else if(isTRUE(grp$values=="CrossCountry")) grp$cellStyle <- list("background-color"="lavenderblush")
  else if(isTRUE(grp$values=="London Midland")) grp$cellStyle <- list("background-color"="lightgreen")
  else if(isTRUE(grp$values=="Virgin Trains")) grp$cellStyle <- list("background-color"="cornsilk")
}
invisible(lapply(rgrps, colorText))

# manually added total (see "Irregular Layout" vignette for details)
pt$addRowGroup(caption="Total", isOutline=TRUE, isTotal=TRUE, isLevelTotal=TRUE,
               styleDeclarations=list(color="blue"), 
               cellStyleDeclarations=list("background-color"="plum"))

# add calculations - each calculation styles the font differently
pt$defineCalculation(calculationName="TotalTrains", summariseExpression="n()",
                     caption="Train Count", format = "%.0f", 
                     cellStyleDeclarations=list("font-family"="serif", "font-style"="italic"))
pt$defineCalculation(calculationName="AvgSchSpeed", 
                     summariseExpression="mean(SchedSpeedMPH, na.rm=TRUE)",
                     caption="Avg Speed", format = "%.0f", 
                     cellStyleDeclarations=list("font-family"="Lucida Console", 
                                                "xl-value-format"="#,##0"))
# set the calculations on the rows axis
pt$addRowCalculationGroups()

# evaluate the pivot the generate the cells and cell values
pt$evaluatePivot()

# set styling on some specific cells
pt$setStyling(rFrom=4, rTo=5, cFrom=6, cTo=8, 
              declarations=list("color"="black", "background-color"="yellow", "font-weight"="bold"))

# render pivot - the resulting styling is all of the above combined
pt$renderPivot()

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:

• tableStyle - the name of the style applied to the table (i.e. the HTML <table> element).
• rootStyle - the name of the style applied to the empty cell at the top left of the pivot table.
• rowHeaderStyle - the name of the style applied to the row header cells.
• colHeaderStyle - the name of the style applied to the column header cells.
• cellStyle - the name of the style applied to the cells in the body of the pivot table.
• totalStyle - the name of the style applied to the total cells in the 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(
      "display"="table",
      "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$outlineRowHeaderStyle <- "RowHeader"
  pivotStyles$outlineColHeaderStyle <- "ColumnHeader"
  pivotStyles$outlineCellStyle <- "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:

• Each cell in a table has an associated cell type (root / rowHeader / columnHeader / cell / total). This controls the default styling applied to a cell. The cell type is set by the pivottabler package automatically.
• The styling theme applied to a pivot table defines a set of styles and the default style for each cell type.
  • Create a new simple theme as a list: pt$theme <- list(...) (see above for supported list elements).
  • Create a new simple theme a list and assigning this to the tbl$theme field.
  • Create a new custom theme by writing a function createCustomTheme().
  • Assign a theme to the table theme property: pt$theme <- createCustomTheme(pt).
• Each individual cell can also have a different style, specified by settings the baseStyleName for the cell.
  • Single cell example: pt$setStyling(3, 2, baseStyleName="AltCellStyle").
  • Single cell example: pt$cells$getCell(3, 2)$baseStyleName <- "AltCellStyle".
  • Cell range example: pt$setStyling(3, 2, 3, 6, baseStyleName="AltCellStyle").
  • Cell range example: pt$setStyling(rowNumbers=3, columnNumbers=2:6, baseStyleName="AltCellStyle").
• It is also possible to declare individual style overrides (e.g. a different text colour). These override specific style settings from the named styles.
  • Single cell example: pt$setStyling(3, 2, declarations=list("text-align"="left", "background-color"="yellow")).
  • Cell range example: pt$setStyling(rowNumbers=3, columnNumbers=2:6, declarations=list("text-align"="left", "background-color"="yellow")).
• It is possible to specify a new base style and overrides together. The declarations override the settings in the base style.
  • Single cell example: pt$setStyling(3, 2, baseStyleName="AltCellStyle", declarations=list("text-align"="left", "background-color"="yellow")).
  • Cell range example: pt$setStyling(rowNumbers=3, columnNumbers=2:6, baseStyleName="AltCellStyle", declarations=list("text-align"="left", "background-color"="yellow")).
• Instead of specifying the declarations as a list, an inline style can be declared. This is an unnamed style object that is used for one or multiple cells and is not part of the table theme.
  • See the "Changing cell appearance after cells have been created" section in this vignette for an example.
  • See the "Finding and Formatting" vignette for more examples.

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" >

• The cell$baseStyleName is used as the HTML class name.
• If no cell$baseStyleName has been explicitly set for a cell, then the base style name (for the relevant cell type) specified in the table theme is used.
• The class names can be prefixed if required - useful when using several tables on the same HTML page.
• It is also possible to generate class names that refer to styles not defined in the table - useful when the styles are defined in an external CSS file.

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. Regular Layout
5. Outputs
6. Latex Output
7. Styling
8. Finding and Formatting
9. Cell Context
10. Navigating a Pivot Table
11. Irregular Layout
12. Performance
13. Excel Export
14. Shiny
15. Appendix: Details
16. Appendix: Calculations
17. Appendix: Class Overview

cbailiss/pivottabler documentation built on Oct. 14, 2023, 9:38 a.m.