05. Finding and Formatting

The hardware and bandwidth for this mirror is donated by METANET, the Webhosting and Full Service-Cloud Provider.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]metanet.ch.

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()

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

Getting cells when specifyCellsAsList=TRUE

Retrieving whole rows of cells when specifyCellsAsList=TRUE

Retrieving whole columns of cells when specifyCellsAsList=TRUE

Retrieving specific cells when specifyCellsAsList=TRUE

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

Getting cells when specifyCellsAsList=FALSE

Retrieving whole rows of cells when specifyCellsAsList=FALSE

Retrieving whole columns of cells when specifyCellsAsList=FALSE

Retrieving specific cells when specifyCellsAsList=FALSE

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

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

Finding Cells

Conditional Formatting

Further Reading

Getting cells when `specifyCellsAsList=TRUE`

Retrieving whole rows of cells when `specifyCellsAsList=TRUE`

Retrieving whole columns of cells when `specifyCellsAsList=TRUE`

Retrieving specific cells when `specifyCellsAsList=TRUE`

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

Getting cells when `specifyCellsAsList=FALSE`

Retrieving whole rows of cells when `specifyCellsAsList=FALSE`

Retrieving whole columns of cells when `specifyCellsAsList=FALSE`

Retrieving specific cells when `specifyCellsAsList=FALSE`

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