1. Working with Cells

Chris Bailiss

2019-03-21

In This Vignette

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:

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:

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

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:

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