1. Working with Cells

Chris Bailiss

2017-09-29

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 properies:

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 call-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=

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 TableCells collection:

The following functions also exist on the TableCells collection, but are discussed 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