03. Outputs

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

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

# 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.

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

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