In stla/hwriteXLSX: Write XLSX Worksheets

knitr::opts_chunk$set(collapse=TRUE)

library(hwriteXLSX)

removeNULL <- function(x){
    x <- Filter(Negate(is.null), x)
    if( is.list(x) ){
      x <- lapply( x, function(y) Filter(length, removeNULL(y)))
    }
    return(x)
}
A1 <- cell(1, 1, "text")
A2 <- cell(1, 2, 9.9, numberFormat="2Decimal")
B3 <- cell(2, 3, "abc", bold=TRUE, color="red", 
           comment="this cell is red", commentAuthor="John Doe")
sheet <- removeNULL(list(Sheet1 = c(A1, A2, B3)))

The heart: `json2xlsx`

The function at the heart of hwriteXLSX is json2xlsx. Its main arguments are two JSON strings and the name of an output XLSX file. The first JSON string defines the cells of the workbook while the second one defines the images to be included in the workbook.

Below is a possible example of the first JSON string:

jsonlite::toJSON(sheet, pretty = TRUE, auto_unbox = TRUE)

This JSON string is returned by the command jsonlite::toJSON(sheet, auto_unbox=TRUE) where sheet is the following nested list:

sheet

This nested list sheet can be obtained with the help of the cell function of the hwriteXLSX package:

A1 <- cell(1, 1, "text")
A2 <- cell(1, 2, 9.9, numberFormat="2Decimal")
B3 <- cell(2, 3, "abc", bold=TRUE, color="red", comment="this cell is red")
sheet <- list(Sheet1 = c(A1, A2, B3))

The `hwriteXLSX` function

The function json2xlsx is rather for internal purpose. Once you get a sheet represented as a nested list, you can create the XLSX file with the help of the hwriteXLSX function:

hwriteXLSX("myxlsx.xlsx", worksheet = sheet)

Now assume you want to write a worksheet with two sheets, for example with this second sheet:

A1 <- cell(1, 1, "hi")
B1 <- cell(2, 1, "hello")
sheet2 <- list(Sheet2 = c(A1, B1))

Proceed as follows:

hwriteXLSX("myxlsx.xlsx", worksheet = c(sheet, sheet2))

Create a sheet from a data object

A sheet can be created from a dataframe with the help of the createSheet function:

sheet <- createSheet(iris, "Sheet1")

But this does not allow to format the cells neither to include comments. The most general way to create a sheet with the createSheet function, allowing formatting and inclusion of comments, is to use a list of lists:

dat <-
  list(
    A = list(1, 2, 3),
    B = list(NULL, "a", 1000),
    C = list(NA, "b")
  )
attr(dat$B[[2]], "color") <- "red"
attr(dat$B[[2]], "comment") <- "this cell is red"
attr(dat$C[[1]], "comment") <- "this cell is empty"
sheet <- createSheet(dat, "Sheet1")

Thus, if you want to create a sheet from a dataframe and to format a cell or include a comment, you can proceed as follows:

dat <- lapply(as.list(mtcars), as.list)
attr(dat$drat[[6]], "color") <- "red"
attr(dat$drat[[6]], "comment") <- "this value is low"
sheet <- createSheet(dat, "Sheet1")

Dates

Dates in XLSX files are stored as integers: the number of days between the date and a certain origin. For example, if you want to create a cell with the date 2017-06-14, using the cell function, you have to pass the integer representing this date and to set a date format:

A1 <- cell(1, 1, 42900, numberFormat = "MmDdYy")

Instead of doing so, you can use the cellDate function:

A1 <- cellDate(1, 1, "2017-06-14")

The function createSheet automatically creates a date cell from the values having a date class (Date or POSIXt).

Inclusion of images

image1 <- list(file="image1.png", left=5, top=1, width=400, height=400)
image2 <- list(file="image2.png", left=5, top=22, width=400, height=267)
images <- list(Sheet1 = list(image1, image2))
json <- jsonlite::toJSON(images, auto_unbox = TRUE, pretty = TRUE)

As said as the beginning of this vignette, the json2xlsx function accepts another JSON string as argument, the one defining the images to be included in the worksheet. Below is an example of such a JSON string:

json

The values of left and top define the location of the top-left corner of the image.

Again, the json2xlsx function is rather for internal purpose, and the user can include the images with the help of the hwriteXLSX function. The user has to pass a named list to the argument images of the hwriteXLSX function, such as the one corresponding to the above JSON string: