# 05. Finding and Formatting

#### Chris Bailiss

#### 2021-06-26

## In This Vignette

- Finding and Formatting
- Example Table
- Getting Cells by Row and/or Column Numbers
- Finding Cells
- Conditional Formatting
- Further Reading

## Finding and Formatting

This vignette explains how to find parts of a table, i.e. one or more cells in the body of the table.

This is often useful to retrieve either a specific value/values, or to change the appearance of specific headings/cells - similar to the conditional formatting capabilities of many off-the-shelf tools.

## Example Table

The following table is used as the basis of the examples in the rest of this vignette:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table and render
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)
    tbl$renderTable()

## Getting Cells By Row and/or Column Numbers

The `getCells()` function can be used to retrieve one or more cells by row/column number in the body of the table. This function has the following parameters:

- `specifyCellsAsList` - a logical value, usage and default value described below.
- `rowNumbers` - an integer vector specifying row numbers.
- `columnNumbers` - an integer vector specifying column numbers.
- `cellCoordinates` - a list of two-element vectors that specify the coordinates of cells to retrieve. Ignored when `specifyCellsAsList=FALSE`.
- `excludeEmptyCells` - a logical value that specifies whether cells with no value should be excluded, default `TRUE`.
- `matchMode` - either “simple” (default) or “combinations”.
  - “simple” specifies that row and column arguments are considered separately (logical OR), e.g. `rowNumbers=1` and `columnNumbers=2` will match all cells in row 1 and all cells in column 2.
  - “combinations” specifies that row and column arguments are considered together (logical AND), e.g. `rowNumbers=1` and `columnNumbers=2` will match only the cell single at location (1, 2).
  - Arguments `rowNumbers` and `columnNumbers` are affected by the match mode. All other arguments are not.

The arguments can be specified in two different ways depending on the value of the `specifyCellsAsList` argument. The `specifyCellsAsList` can have the values `TRUE` or `FALSE`, `TRUE` is the default value.

The `getCells()` function returns a list of cell objects.

### Getting cells when `specifyCellsAsList=TRUE`

To get cells when `specifyCellsAsList=TRUE`:

- Get one or more rows by specifying the row numbers as a vector as the `rowNumbers` argument and leaving the `columnNumbers` argument set to the default value of NULL, or
- Get one or more columns by specifying the column numbers as a vector as the `columnNumbers` argument and leaving the `rowNumbers` argument set to the default value of NULL, or
- Get one or more individual cells by specifying the `cellCoordinates` argument as a list of vectors of length 2, where each element in the list is the row and column number of one cell, e.g. `list(c(1, 2), c(3, 4))` specifies two cells, the first located at row 1, column 2 and the second located at row 3, column 4.

Examples of the above are given below. The retrieved cells are highlighted in orange by specifying a different style.

#### Retrieving whole rows of cells when `specifyCellsAsList=TRUE`

Retrieving the first and third rows:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(rowNumbers=c(1, 3))
    tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
    tbl$renderTable()

#### Retrieving whole columns of cells when `specifyCellsAsList=TRUE`

Retrieving the second column:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(columnNumbers=2)
    tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
    tbl$renderTable()

#### Retrieving specific cells when `specifyCellsAsList=TRUE`

Retrieving the raw/formatted values of the cell in the third column on the second row:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(cellCoordinates=list(c(2, 3)))
    tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
    cat("The raw value of the cell is", cells[[1]]$rawValue, "and the formatted value is", cells[[1]]$formattedValue, ".")

    ## The raw value of the cell is 2348 and the formatted value is 2,348 .

    tbl$renderTable()

Retrieving multiple cells (2nd row-3rd column, 3rd row-4th column and 5th row-6th column):

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(cellCoordinates=list(c(2, 3), c(3, 4), c(5, 6)))
    tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
    tbl$renderTable()

#### Retrieving a mixture of rows, columns and cells when `specifyCellsAsList=TRUE`

Retrieving the 2nd row, 4th column and 5th row-6th column cell:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(rowNumbers=2, columnNumbers=4, cellCoordinates=list(c(5, 6)))
    tbl$setStyling(cells=cells, declarations=list("background-color"="#FFCC66"))
    tbl$renderTable()

### Getting cells when `specifyCellsAsList=FALSE`

To get cells when `specifyCellsAsList=FALSE`:

- Get one or more rows by specifying the row numbers as a vector as the `rowNumbers` argument and leaving the `columnNumbers` argument set to the default value of NULL, or
- Get one or more columns by specifying the column numbers as a vector as the `columnNumbers` argument and leaving the `rowNumbers` argument set to the default value of NULL, or
- Get one or more cells by specifying the row and column numbers as vectors for the `rowNumbers` and `columnNumbers` arguments, or
- a mixture of the above, where for entire rows/columns the element in the other vector is set to `NA`, e.g. to retrieve whole rows, specify the row numbers as the `rowNumbers` but set the corresponding elements in the `columnNumbers` vector to `NA`.

Examples of the above are given below. The retrieved cells are highlighted in green by specifying a different style.

#### Retrieving whole rows of cells when `specifyCellsAsList=FALSE`

When retrieving just rows, the rowNumbers argument is specified the same irrespective of whether `specifyCellsAsList` is `TRUE` or `FALSE`.

Retrieving the first and third rows:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(specifyCellsAsList=FALSE, rowNumbers=c(1, 3))
    tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
    tbl$renderTable()

#### Retrieving whole columns of cells when `specifyCellsAsList=FALSE`

When retrieving just columns, the columnNumbers argument is specified the same irrespective of whether `specifyCellsAsList` is `TRUE` or `FALSE`.

Retrieving the second column:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(specifyCellsAsList=FALSE, columnNumbers=2)
    tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
    tbl$renderTable()

#### Retrieving specific cells when `specifyCellsAsList=FALSE`

When retrieving cells, the rowNumbers and columnNumbers arguments are specified differently depending on whether `specifyCellsAsList` is `TRUE` or `FALSE`.

Retrieving the raw/formatted values of the cell in the third column on the second row:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(specifyCellsAsList=FALSE, rowNumbers=2, columnNumbers=3)
    tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
    cat("The raw value of the cell is", cells[[1]]$rawValue, "and the formatted value is", cells[[1]]$formattedValue, ".")

    ## The raw value of the cell is 2348 and the formatted value is 2,348 .

    tbl$renderTable()

#### Retrieving multiple cells (2nd row-3rd column, 3rd row-4th column and 5th row-6th column):

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(specifyCellsAsList=FALSE, rowNumbers=c(2, 3, 5), columnNumbers=c(3, 4, 6))
    tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
    tbl$renderTable()

#### Retrieving a mixture of rows, columns and cells when `specifyCellsAsList=FALSE`

When retrieving cells, the rowNumbers and columnNumbers arguments are specified differently depending on whether `specifyCellsAsList` is `TRUE` or `FALSE`.

Retrieving the 2nd row, 4th column and 5th row-6th column cell:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # get the cells and apply styling
    cells <- tbl$getCells(specifyCellsAsList=FALSE, rowNumbers=c(2, NA, 5), columnNumbers=c(NA, 4, 6))
    tbl$setStyling(cells=cells, declarations=list("background-color"="#00FF00"))
    tbl$renderTable()

## Finding Cells

The `findCells()` function is used to search for cells within the body of the table matching one or more criteria. The function returns a list of cell objects. This function has the following parameters:

- `minValue` - a numerical value specifying a minimum value threshold when matching cells.
- `maxValue` - a numerical value specifying a maximum value threshold when matching cells.
- `exactValues` - vector specifying a set of allowed values when matching cells.
- `valueRanges` - a vector specifying one or more value range expressions to use when matching cells. The expression(s) can be 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)"`.
- `includeNA` - specify TRUE to include NA in the matched cells, FALSE to exclude NA values.
- `includeNull` - specify TRUE to include cells with no value, FALSE to exclude cells with no value.
- `emptyCells` - a word that specifies how empty cells are matched - must be one of:
  - `include` to match empty and non-empty cells.
  - `exclude` to match only non-empty cells.
  - `only` to match only empty cells.

In addition, the following parameters can also be used with `pt$findCells()`:

- `rowNumbers` - an integer vector specifying row numbers.
- `columnNumbers` - an integer vector specifying column numbers.
- `cellCoordinates` - a list of two-element vectors that specify the coordinates of cells to retrieve. Ignored when `specifyCellsAsList=FALSE`.
- `cells` - a `PivotCell` object or a list of `PivotCell` objects to restrict the cells that are matched.
- `rowColumnMatchMode` - either “simple” (default) or “combinations”.
  - “simple” specifies that row and column arguments are considered separately (logical OR), e.g. `rowNumbers=1` and `columnNumbers=2` will match all cells in row 1 and all cells in column 2.
  - “combinations” specifies that row and column arguments are considered together (logical AND), e.g. `rowNumbers=1` and `columnNumbers=2` will match only the cell single at location (1, 2).
  - Arguments `rowNumbers`, `columnNumbers`, `rowGroups` and `columnGroups` are affected by the match mode. All other arguments are not.

`findCells()` is typically used for conditional formatting, as illustrated in the next section.

## Conditional Formatting

The `findCells()` and `getCells()` functions can be used to help conditionally format a table.

For example, to highlight in red those cells in the basic example table that have a percentage value between 0% and 40%:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # apply the formatting
    cells <- tbl$findCells(columnNumbers=5:6, minValue=0, maxValue=40, includeNull=FALSE, includeNA=FALSE)
    tbl$setStyling(cells=cells, declarations=list("background-color"="#FFC7CE", "color"="#9C0006"))
    tbl$renderTable()

Extending the above example so that on-time arrival/departure percentages are coloured as follows:

- 0% to 40% - red
- 40% to 60% - yellow
- 60% to 100% - green

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # apply the red formatting
    cells <- tbl$findCells(columnNumbers=5:6, minValue=0, maxValue=40, includeNull=FALSE, includeNA=FALSE)
    tbl$setStyling(cells=cells, declarations=list("background-color"="#FFC7CE", "color"="#9C0006"))
    # apply the yellow formatting
    cells <- tbl$findCells(columnNumbers=5:6, minValue=40, maxValue=60, includeNull=FALSE, includeNA=FALSE)
    tbl$setStyling(cells=cells, declarations=list("background-color"="#FFEB9C", "color"="#9C5700"))
    # apply the green formatting
    cells <- tbl$findCells(columnNumbers=5:6, minValue=60, maxValue=100, includeNull=FALSE, includeNA=FALSE)
    tbl$setStyling(cells=cells, declarations=list("background-color"="#C6EFCE", "color"="#006100"))
    tbl$renderTable()

The `mapStyling()` function can be used to map the colours using a continuous colour range instead of fixed colours:

    # aggregate the sample data to make a small data frame
    library(basictabler)
    library(dplyr)
    tocsummary <- bhmsummary %>%
      group_by(TOC) %>%
      summarise(OnTimeArrivals=sum(OnTimeArrivals),
                OnTimeDepartures=sum(OnTimeDepartures),
                TotalTrains=sum(TrainCount)) %>%
      ungroup() %>%
      mutate(OnTimeArrivalPercent=OnTimeArrivals/TotalTrains*100,
             OnTimeDeparturePercent=OnTimeDepartures/TotalTrains*100) %>%
      arrange(TOC)

    # formatting values (explained in the introduction vignette)
    columnFormats=list(NULL, list(big.mark=","), list(big.mark=","), list(big.mark=","), "%.1f", "%.1f")

    # create the table
    tbl <- BasicTable$new()
    tbl$addData(tocsummary, firstColumnAsRowHeaders=TRUE,
                explicitColumnHeaders=c("TOC", "On-Time Arrivals", "On-Time Departures",
                                        "Total Trains", "On-Time Arrival %", "On-Time Departure %"),
                columnFormats=columnFormats)

    # apply the conditional formatting
    cells <- tbl$getCells(rowNumbers=2:5, columnNumbers=5:6, matchMode="combinations")
    tbl$mapStyling(cells=cells, styleProperty="background-color", valueType="color", mapType="continuous",
                   mappings=list(25, "#FFC7CE", 40, "#FFEB9C", 60, "#C6EFCE", 100))
    tbl$mapStyling(cells=cells, styleProperty="color", valueType="color", mapType="continuous",
                   mappings=list(25, "#9C0006", 40, "#9C5700", 60, "#006100", 100))

    # render
    tbl$renderTable()

## Further Reading

The full set of vignettes is:

1. Introduction
2. Working with Cells
3. Outputs
4. Styling
5. Finding and Formatting
6. Shiny
7. Excel Export