03. Outputs

In basictabler: Construct Rich Tables for Output to 'HTML'/'Excel'

In This Vignette

• Overview
• Example Table
• Output as Plain Text
• Output as HTML
• Output in Excel
• Output as FlexTable
• Output in Word
• Output in PowerPoint
• Output as an R Matrix
• Output as an R Data Frame
• Further Reading

Overview

The basictabler package supports outputting a table in a number of different forms:

• A htmlwidget for R-Studio - using tbl$renderTable() to render the table into the "Viewer" tab in R-Studio,
• A htmlwidget for Shiny applications - using basictabler(tbl) to render the table into the Shiny app,
• As HTML - using either:
  • tbl$getHtml() to retrieve a character variable containing HTML, or
  • tbl$saveHtml() to save the HTML to a file.
• As plain text - using tbl to output to the console or tbl$asCharacter to retrieve as a character value.
• Into an Excel Worksheet.

Sometimes it is desirable to retrieve the table as a more standard data type that is easier to work with in R code. A table can be converted to either a matrix or a data frame. Often neither data type is a perfect representation of your table - which option is better will depend upon your use case.

Example Table

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

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

Output as Plain Text

A table is outputted to the console as plain text simply by using tbl:

# 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"))

# output table
tbl

Alternatively, the plain text representation of the table can be retrieved as a character value using tbl$asCharacter.

Output as HTML

Rendering a htmlwidget

A table is outputted as a htmlwidget simply by calling tbl$renderTable(). There are numerous examples throughout these vignettes, including the example directly above.

For outputting as a htmlwidget in a Shiny application, use basictabler(tbl).

Retrieving HTML

To retrieve the HTML of a table, use tbl$getHtml(). This returns a list of html tag objects built using the htmltools package. This object can be converted to a simple character variable using as.character() or as illustrated below. The CSS declarations for a table can be retrieved using tbl$getCss() - also illustrated below.

# 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"))

#out the HTML and CSS
cat(paste(tbl$getHtml(), sep="", collapse="\n"))
cat(tbl$getCss())

Output in Excel

Please see the Excel Export vignette.

Output as FlexTable

Converting a table from the basictabler package to a table from the flextabler package is possible:

# 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"))

# convert to flextable
library(flextable)
ft <- tbl$asFlexTable()
ft

When converting to a flextable, the styles used in the basictabler table are converted to their flextable equivalents where possible. If you specify both a CSS and ft value, then the CSS value will be used when rendering to HTML and the ft value will be used when converting the table to a flex table. The table below shows how the CSS style values are mapped to the equivalent style settings in the flextable package.

| CSS Property | FT Property | FT Example | Notes | |:-------------------|:--------------------|:------------------------|:-------------------------------------------| | font-family | ft-font-name | Arial | Only the first CSS font is used in Excel. | | font-size | ft-font-size | 12 | In Points (4-72). See below for CSS units.| | font-weight | ft-bold | normal or bold | XL bold is CSS font-weight >= 600. | | font-style | ft-italic | normal or italic | italic and oblique map to italic. | | background-color | ft-bg-color | #FF0000 | See below for supported CSS colours. | | color | ft-text-color | #00FF00 | See below for supported CSS colours. | | text-align | ft-h-align | left or center or right | | | vertical-align | ft-v-align | top or middle or bottom | | | | ft-text-rotation | lrtb tblr btlr number | One of the constant values or 0 to 359. | | padding | ft-padding | number (in px, pt, etc) | See below for supported padding values. | | padding-left | ft-padding-left | number (in px, pt, etc) | See below for supported padding values. | | padding-right | ft-padding-right | number (in px, pt, etc) | See below for supported padding values. | | padding-top | ft-padding-top | number (in px, pt, etc) | See below for supported padding values. | | padding-bottom | ft-padding-bottom | number (in px, pt, etc) | See below for supported padding values. | | border | ft-border | thin black | See below for supported CSS border values. | | border-left | ft-border-left | thin black | See below for supported CSS border values. | | border-right | ft-border-right | thin black | See below for supported CSS border values. | | border-top | ft-border-top | thin black | See below for supported CSS border values. | | border-bottom | ft-border-bottom | thin black | See below for supported CSS border values. |

Notes:

• For CSS font-size, only the following units are supported: in, cm, mm, pt, pc, px, em, %.
• For CSS background-color, CSS color and border colours, only hex colours, named colours, RGB() and RGBA() values are supported. HLS() and HLSA() values are not supported. Examples of supported CSS values: #0080FF, rgb(0, 128, 255), rgba(0, 128, 255, 0.5), red, black, darkolivegreen, etc.
• For padding, either use CSS padding / ft-padding to specify the same padding on all four sides of each cell. Or use the side-specific versions, e.g. CSS padding-left / ft-padding-left.
• For borders, either use CSS border / xl-border to specify the same border on all four sides of each cell. Or use the side-specific versions, e.g. CSS border-left / xl-border-left.

Examples of valid ft border values:

• thin solid #FF00BB
• 2px dotted red
• medium dashed rgb(0, 255, 0)

Note that the following CSS properties are NOT supported for automatic mapping to flextable values:

• font - use font-family, font-size, etc. instead
• Border side specific properties, e.g. border-left-style, border-top-color, etc.

Output in Word

Converting a table to a Word document is possible using the flextabler package:

# 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"))

# convert to flextable
library(flextable)
ft <- tbl$asFlexTable()

# save word document
library(officer)
docx <- read_docx()
docx <- body_add_par(docx, "Example Table")
docx <- body_add_flextable(docx, value = ft)
print(docx, target = "example_table_word.docx")

Output in PowerPoint

Converting a table to a PowerPoint document is possible using the flextabler package:

# 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"))

# convert to flextable
library(flextable)
ft <- tbl$asFlexTable()

# save PowerPoint document
library(officer)
ppt <- read_pptx()
ppt <- add_slide(ppt, layout = "Title and Content", master = "Office Theme")
ppt <- ph_with(ppt, value = ft, location = ph_location_left()) 
print(ppt, target = "example_table_powerpoint.pptx")

Output as an R Matrix

Converting a table to a matrix can be accomplished as follows:

# 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"))

# output as matrix
tbl$asMatrix()

The firstRowAsColumnNames and firstColumnAsRowNames parameters control how the names in the matrix are set. The rawValue parameter specifies whether the matrix should contain the raw values or the formatted values.

# 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"))

# output as matrix
tbl$asMatrix(firstRowAsColumnNames=TRUE, firstColumnAsRowNames=TRUE, rawValue=TRUE)

Output as an R Data Frame

The asDataFrame() function returns a data frame with the same layout as the table, e.g. a table with a body consisting of 10 rows and 2 columns will result in a data frame also containing 10 rows and 2 columns.

Again, the firstRowAsColumnNames and firstColumnAsRowNames parameters control how the names in the data frame are set and the rawValue parameter specifies whether the matrix should contain the raw values or the formatted values.

# 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"))

# output as data frame
df <- tbl$asDataFrame(firstRowAsColumnNames=TRUE, rawValue=TRUE)
df
str(df)

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

basictabler documentation built on June 26, 2021, 5:05 p.m.