---
title: "03. Outputs"
author: "Chris Bailiss"
date: "`r Sys.Date()`"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{03. Outputs}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

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

```{r, message=FALSE, warning=FALSE}
# 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`:

```{r, message=FALSE, warning=FALSE, comment=""}
# 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.

```{r, message=FALSE, warning=FALSE, comment=""}
# 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](v07-excelexport.html) vignette.

## Output as FlexTable

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

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
# 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:

```{r, eval=FALSE}
# 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:

```{r, eval=FALSE}
# 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:

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
# 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.

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
# 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.

```{r, message=FALSE, warning=FALSE, eval=TRUE, comment=""}
# 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](v01-introduction.html)
2. [Working with Cells](v02-workingwithcells.html)
3. [Outputs](v03-outputs.html)
4. [Styling](v04-styling.html)
5. [Finding and Formatting](v05-findingandformatting.html)
6. [Shiny](v06-shiny.html)
7. [Excel Export](v07-excelexport.html)