Previous chapter
Write Excel FilesIntroduction to openxlsx
Next chapter

openxlsx

The package openxlsx provides a high-level interface to create and style Excel files. The workhorse function write.xlsx is used to create new MS Excel files.

The package description states that it

Simplifies the creation of Excel .xlsx files by providing a high level interface to writing, styling and editing worksheets. Through the use of ‘Rcpp’, read/write times are comparable to the ‘xlsx’ and ‘XLConnect’ packages with the added benefit of removing the dependency on Java.

Basic Example

Use the iris data set and write it into two excel files. For the second file use the parameter asTable to format the data set as a Table. Inspect the two files and compare.

library(openxlsx)
write.xlsx(iris, file = "basic_example1.xlsx")
write.xlsx(iris, file = "basic_example2.xlsx", asTable = TRUE)

Write a list of data.frames to individual worksheets using list names as worksheet names

l <- list("IRIS" = iris, "MTCARS" = mtcars)
write.xlsx(l, file = "writeXLSX2.xlsx")
write.xlsx(l, file = "writeXLSXTable2.xlsx", asTable = TRUE)

See also vignette("Introduction", package = "openxlsx").

Workbook with multiple sheets

  • Split the iris dataset into multiple groups by the column Species.
  • Create a new excel workbook using createWorkbook() and write the contents of each species into a worksheet. Worksheets can be created using the function addWorksheet()
library(openxlsx)
dat <- split(mtcars, mtcars$cyl)
## Create a blank workbook
wb <- createWorkbook()
 
## Loop through the list of split tables as well as their names
## and add each one as a sheet to the workbook
Map(function(data, name){
    addWorksheet(wb, name)
    writeData(wb, name, data)
 
}, dat, names(dat))
 
## Save workbook to working directory
saveWorkbook(wb, file = "example.xlsx", overwrite = TRUE)

Example inspired from https://trinkerrstuff.wordpress.com/2018/02/14/easily-make-multi-tabbed-xlsx-files-with-openxlsx.