02. Working with Cells

Chris Bailiss

2020-03-07

In This Vignette

Table Cells
Properties of a Table Cell
Creating a Table Cell-by-Cell
Creating a Table Column-by-Column
Creating a Table Row-by-Row
Retrieving Cell values
Retrieving Cells
Merging Cells
Manipulating Tables
Summary of Table Cells Functions
Further Reading

Table Cells

Every basictabler table contains a TableCells object, which contains all of the cells in the table. Each cell is a TableCell object, e.g. a table of 3 rows and 5 columns will have 15 TableCell objects in the cells collection.

The cells collection offers a set of functions that make working with individual or groups of cells easier. These are described in this vignette and summarised at the end of the vignette.

The cells collection is accessed via tbl$cells.

Properties of a Table Cell

Each TableCell object has the following basic properties:

rowNumber - the number of the row where the cell exists in the table.
columnNumber - the number of the column where the cell exists in the table.
cellType - one of: root, rowHeader, columnHeader, cell, total. Controls the type of default styling applied to the cell when the table is rendered/exported.
visible - TRUE/FALSE, controls whether the cell is output during rendering/exporting.
rawValue - the unformatted value of the cell.
formattedValue - the formatted value of the cell used during most rendering/exporting.
baseStyleName - allows a different named style to be applied to the cell when rendering/exporting (if not specified, the default style based on the cellType will be used).
style - a TableStyle object that contains specific style rules just for this cell (which take precedence over the baseStyleName).

Most the above properties are rarely accessed directly. Instead, helper functions are used to manipulate them. Nonetheless, understanding what each cell is composed of helps to understand tables and make working with tables easier.

Creating a Table Cell-by-Cell

Instead of constructing a table in one-line from a data frame or matrix, a table can be constructed cell-by-cell, though this requires many lines of code:

library(basictabler)
tbl <- BasicTable$new()
tbl$cells$setCell(1, 1, cellType="root", rawValue="Sale ID")
tbl$cells$setCell(1, 2, cellType="columnHeader", rawValue="Item")
tbl$cells$setCell(1, 3, cellType="columnHeader", rawValue="Quantity")
tbl$cells$setCell(1, 4, cellType="columnHeader", rawValue="Price")
tbl$cells$setCell(2, 1, cellType="rowHeader", rawValue=5334)
tbl$cells$setCell(2, 2, cellType="cell", rawValue="Apple")
tbl$cells$setCell(2, 3, cellType="cell", rawValue=5)
tbl$cells$setCell(2, 4, cellType="cell", rawValue=0.34)
tbl$cells$setCell(3, 1, cellType="rowHeader", rawValue=5336)
tbl$cells$setCell(3, 2, cellType="cell", rawValue="Orange")
tbl$cells$setCell(3, 3, cellType="cell", rawValue=8)
tbl$cells$setCell(3, 4, cellType="cell", rawValue=0.47)
tbl$cells$setCell(4, 1, cellType="rowHeader", rawValue=5338)
tbl$cells$setCell(4, 2, cellType="cell", rawValue="Banana")
tbl$cells$setCell(4, 3, cellType="cell", rawValue=6)
tbl$cells$setCell(4, 4, cellType="cell", rawValue=1.34)
tbl$renderTable()

Creating a Table Column-by-Column

Less code is required to construct a table column-by-column from vectors:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$cells$setCell(1, 1, cellType="root", rawValue="Sale ID")
tbl$cells$setCell(1, 2, cellType="columnHeader", rawValue="Item")
tbl$cells$setCell(1, 3, cellType="columnHeader", rawValue="Quantity")
tbl$cells$setCell(1, 4, cellType="columnHeader", rawValue="Price")
tbl$cells$setColumn(1, cellTypes="rowHeader", rawValues=saleIds)
tbl$cells$setColumn(2, cellTypes="cell", rawValues=items)
tbl$cells$setColumn(3, cellTypes="cell", rawValues=quantities)
tbl$cells$setColumn(4, cellTypes="cell", rawValues=prices,
                    formats=list("%.2f"))
tbl$renderTable()

By default the tbl$cells$setColumn() function starts from row 2, i.e. it assumes there is one row of column headings in the table. This behaviour can be changed by setting the value of the startAtRowNumber parameter.

Creating a Table Row-by-Row

Similarly, it is possible to construct a table row-by-row, though typically this requires lists to be used, since rows often contain multiple types of data:

# cell types for the cells in each row
cellTypes <- c("rowHeader", "cell", "cell", "cell")

# formats for the values in each row
# (only the value in the fourth column needs formatting)
formats <- list(NULL, NULL, NULL, "%.2f")

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$cells$setCell(1, 1, cellType="root", rawValue="Sale ID")
tbl$cells$setCell(1, 2, cellType="columnHeader", rawValue="Item")
tbl$cells$setCell(1, 3, cellType="columnHeader", rawValue="Quantity")
tbl$cells$setCell(1, 4, cellType="columnHeader", rawValue="Price")
tbl$cells$setRow(2, cellTypes=cellTypes, formats=formats,
                 rawValues=list(5334, "Apple", 5, 0.34452354))
tbl$cells$setRow(3, cellTypes=cellTypes, formats=formats,
                 rawValues=list(5336, "Orange", 8, 0.4732543))
tbl$cells$setRow(4, cellTypes=cellTypes, formats=formats,
                 rawValues=list(5338, "Banana", 6, 1.3443243))
tbl$renderTable()

NB: It is possible to use vectors or lists for the rawValues argument for both the tbl$cells$setColumn() and tbl$cells$setRow() functions.

Retrieving Cell Values

It is possible to retrieve the values of individual cells or ranges of cells:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))
tbl$renderTable()

# get the raw value of the cell in row 2 column 4
cat(tbl$cells$getValue(2, 4))

0.3445235

# get the formatted value of the cell in row 2 column 4
cat(tbl$cells$getValue(2, 4, formattedValue=TRUE))

0.34

# get the values in row 2 (as a list due to different data types)
rowValues <- tbl$cells$getRowValues(2, asList=TRUE)

# convert to character values for outputting
rowValues <- lapply(rowValues, as.character)
cat(class(rowValues), ": ", paste(rowValues, collapse=", "))

list :  5334, Apple, 5, 0.34452354

# get the values in column 3
columnValues <- tbl$cells$getColumnValues(3)
cat(class(columnValues), ": ", paste(columnValues, collapse=", "))

numeric :  5, 8, 6

Retrieving Cells

Table Cell objects can be retrieved using the tbl$cells$getCell() function:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))
tbl$renderTable()

# get the cell in row 2 column 4
cell <- tbl$cells$getCell(2, 4)

# output the cell properties
summary <- list(rowNumber=cell$rowNumber, columnNumber=cell$columnNumber, cellType=cell$cellType,
                visible=cell$visible, rawValue=cell$rawValue, formattedValue=cell$formattedValue,
                baseStyleName=ifelse(is.null(cell$baseStyleName), "", cell$baseStyleName), 
                style=ifelse(is.null(cell$style), "", cell$style$asCSSRule()))
description <- paste(paste(names(summary), unlist(summary), sep="="), collapse="\n")
cat(description)

rowNumber=2
columnNumber=4
cellType=cell
visible=TRUE
rawValue=0.34452354
formattedValue=0.34
baseStyleName=
style=

Merging Cells

Cells can be merged. The range of cells is specified using the row and column of the top-left cell (rFrom and cFrom), then specifying either the number of rows and columns that the merge spans (rSpan and cSpan) or the row and column of the bottom-right cell (rTo and cTo).

E.g. merging the headings of the 2nd and 3rd columns into a single cell:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))

# merge the cells and specify new heading
tbl$mergeCells(rFrom=1, cFrom=2, rSpan=1, cSpan=2)
cell <- tbl$cells$getCell(1, 2)
cell$rawValue <- "Item & Qty"
cell$formattedValue <- "Item & Qty"

tbl$renderTable()

Manipulating Tables

It is also possible to insert and delete rows/columns:

# data for the table
saleIds <- c(5334, 5336, 5338)
items <- c("Apple", "Orange", "Banana")
quantities <- c(5, 8, 6)
prices <- c(0.34452354, 0.4732543, 1.3443243)

# row formats
formats <- list(NULL, NULL, NULL, NULL, "%.2f")

# construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(data.frame(saleIds, items, quantities, prices), 
            firstColumnAsRowHeaders=TRUE,
            explicitColumnHeaders=c("Sale ID", "Item", "Quantity", "Price"),
            columnFormats=list(NULL, NULL, NULL, "%.2f"))
tbl$renderTable()

tbl$cells$insertColumn(3)
tbl$cells$setCell(1, 3, cellType="columnHeader", rawValue="Variety")
tbl$cells$setColumn(3, rawValues=c("Gala", "Jaffa", "Yellow"))
tbl$renderTable()

tbl$cells$insertRow(3)
tbl$cells$setRow(3, cellTypes=c("rowHeader", "cell", "cell", "cell", "cell"),
                 rawValues=list(5335, "Pear", "Marit", 2, 0.89),
                 formats=formats)
tbl$renderTable()

tbl$cells$insertRow(5)
tbl$cells$setRow(5, cellTypes=c("rowHeader", "cell", "cell", "cell", "cell"),
                 rawValues=list(5337, "Plum", "Sweet", 5, 1.59),
                 formats=formats)
tbl$renderTable()

tbl$cells$setCell(1, 6, cellType="columnHeader", rawValue="Total")
qty <- tbl$cells$getColumnValues(4)
price <- tbl$cells$getColumnValues(5)
total <- qty * price
tbl$cells$setColumn(6, cellType="cell", rawValues=total, formats="%.2f")
tbl$renderTable()

tbl$cells$deleteRow(3)
tbl$renderTable()

tbl$cells$deleteColumn(3)
tbl$renderTable()

tbl$cells$deleteCell(3, 2)
tbl$renderTable()

Summary of Table Cells Functions

The following functions exist on the table cells (tbl$cells) collection:

reset()
getCell(r, c)
getValue(r, c, formattedValue=FALSE)
getRowValues(rowNumber, columnNumbers, formattedValue=FALSE, asList=FALSE, rebase=TRUE)
getColumnValues(columnNumber, rowNumbers, formattedValue=FALSE, asList=FALSE, rebase=TRUE)
setCell(r, c, cellType="cell", rawValue, formattedValue=NULL, visible=TRUE, baseStyleName=NULL, styleDeclarations=NULL, rowSpan=NULL, colSpan=NULL)
setRow(rowNumber, startAtColumnNumber=1, cellTypes="cell", rawValues, formattedValues=NULL, formats=NULL, visiblity=TRUE, baseStyleNames=NULL)
setColumn(columnNumber, startAtRowNumber=2, cellTypes="cell", rawValues, formattedValues=NULL, formats=NULL, visiblity=TRUE, baseStyleNames=NULL)
setValue(r, c, rawValue, formattedValue=NULL)
setBlankCell(r, c, cellType="cell", visible=TRUE, baseStyleName=NULL, styleDeclarations=NULL, rowSpan=NULL, colSpan=NULL)
deleteCell(r, c)
extendCells(rowCount, columnCount)
moveCell(r, c, cell)
insertRow(rowNumber, insertBlankCells=TRUE, headerCells=1, totalCells=0)
deleteRow(rowNumber)
insertColumn(columnNumber, insertBlankCells=TRUE, headerCells=1, totalCells=0)
deleteColumn(columnNumber)

The following functions also exist on the TableCells collection and are described in the Finding and Formatting vignette:

getCells(specifyCellsAsList=TRUE, rowNumbers=NULL, columnNumbers=NULL, cellCoordinates=NULL)
findCells(rowNumbers=NULL, columnNumbers=NULL, minValue=NULL, maxValue=NULL, exactValues=NULL, includeNull=TRUE, includeNA=TRUE)

Shortcuts to the above two functions also exist directly on the table object, i.e. any of the following ways of calling the functions work:

tbl$getCells(...) or tbl$cells$getCells(...)
tbl$findCells(...) or tbl$cells$findCells(...)