#

6. Styling

#### Chris Bailiss

#### 2017-10-02

## In This Vignette

- Styles and Themes
- Styles and CSS
- XL Styles
- Included Themes
- Simple Themes
- Creating a New Theme
- Changing the appearance of individual cells
- Further Reading

## 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.

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

The `pivottabler` package includes the `getSimpleColoredTheme()` function that helps define basic new themes quickly. Several examples of using this function are shown below.

    # define the colours
    blue1Colors <- list(
      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
    theme <- getSimpleColoredTheme(parentPivot=pt, colors=blue1Colors, fontName="Verdana, Arial")
    pt$theme <- theme
    pt$renderPivot(styleNamePrefix="t3")

    # define the colours
    grayColors <- list(
      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
    theme <- getSimpleColoredTheme(parentPivot=pt, colors=grayColors, fontName="Courier New, Courier")
    pt$theme <- theme
    pt$renderPivot(styleNamePrefix="t4")

    # define the colours
    orangeColors <- list(
      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()")
    # define the theme
    theme <- getSimpleColoredTheme(parentPivot=pt, colors=orangeColors, fontName="Garamond, arial")
    pt$theme <- theme
    pt$renderPivot(styleNamePrefix="t5")

    # define the colours
    greenColors <- list(
      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
    theme <- getSimpleColoredTheme(parentPivot=pt, colors=greenColors, fontName="Helvetica, arial")
    pt$theme <- theme
    pt$renderPivot(styleNamePrefix="t6")

    # define the colours
    yellowColors <- list(
      headerBackgroundColor = "rgb(255, 192, 0)",
      headerColor = "rgb(255, 255, 255)",
      cellBackgroundColor="rgb(255, 255, 255)",
      cellColor="rgb(0, 0, 0)",
      totalBackgroundColor = "rgb(255, 242, 204)",
      totalColor="rgb(0, 0, 0)",
      borderColor = "rgb(255, 192, 0)"
    )
    # 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
    theme <- getSimpleColoredTheme(parentPivot=pt, colors=yellowColors, fontName="Verdana")
    pt$theme <- theme
    pt$renderPivot(styleNamePrefix="t7")

## 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/colHeaderStyle - the name of the style applied to the row/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(
          "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")

## Changing the appearance of individual cells

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. highlighting a cell:

    # define the colours
    orangeColors <- list(
      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()")
    # define the theme
    theme <- getSimpleColoredTheme(parentPivot=pt, colors=orangeColors, fontName="Garamond, arial")
    pt$theme <- theme
    # evaluate the pivot to generate the cells
    pt$evaluatePivot()
    # apply an additional highlight to one cell (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.

For many more examples of styling parts of a pivot table, see the Finding and Formatting 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