03. Outputs

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

Sale ID    Item  Quantity  Price  
   5334   Apple         5   0.34  
   5336  Orange         8   0.47  
   5338  Banana         6   1.34

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

<table class="Table">
  <tr>
    <th class="ColumnHeader">Sale ID</th>
    <th class="ColumnHeader">Item</th>
    <th class="ColumnHeader">Quantity</th>
    <th class="ColumnHeader">Price</th>
  </tr>
  <tr>
    <th class="RowHeader">5334</th>
    <td class="Cell">Apple</td>
    <td class="Cell">5</td>
    <td class="Cell">0.34</td>
  </tr>
  <tr>
    <th class="RowHeader">5336</th>
    <td class="Cell">Orange</td>
    <td class="Cell">8</td>
    <td class="Cell">0.47</td>
  </tr>
  <tr>
    <th class="RowHeader">5338</th>
    <td class="Cell">Banana</td>
    <td class="Cell">6</td>
    <td class="Cell">1.34</td>
  </tr>
</table>

cat(tbl$getCss())

.Table {display: table; border-collapse: collapse; }
.ColumnHeader {font-family: Arial; font-size: 0.75em; padding: 2px; border: 1px solid lightgray; vertical-align: middle; text-align: center; font-weight: bold; background-color: #F2F2F2; }
.RowHeader {font-family: Arial; font-size: 0.75em; padding: 2px 8px 2px 2px; border: 1px solid lightgray; vertical-align: middle; text-align: left; font-weight: bold; background-color: #F2F2F2; }
.Cell {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; }
.Total {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; font-weight: bold; }

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

     [,1]      [,2]     [,3]       [,4]   
[1,] "Sale ID" "Item"   "Quantity" "Price"
[2,] "5334"    "Apple"  "5"        "0.34" 
[3,] "5336"    "Orange" "8"        "0.47" 
[4,] "5338"    "Banana" "6"        "1.34"

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)

     Item     Quantity Price       
5334 "Apple"  "5"      "0.34452354"
5336 "Orange" "8"      "0.4732543" 
5338 "Banana" "6"      "1.3443243"

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

  Sale.ID   Item Quantity     Price
1    5334  Apple        5 0.3445235
2    5336 Orange        8 0.4732543
3    5338 Banana        6 1.3443243

str(df)

'data.frame':   3 obs. of  4 variables:
 $ Sale.ID : num  5334 5336 5338
 $ Item    : chr  "Apple" "Orange" "Banana"
 $ Quantity: num  5 8 6
 $ Price   : num  0.345 0.473 1.344