Easily Make Multi-tabbed .xlsx Files with openxlsx

This is a quick script showing how to make multi-tabbed .xlsx files. I recently had the need to do this and used the flexible openxlsx package maintained by : Alexander Walker.

The package is described by the author like this:

openxlsx: Read, Write and Edit XLSX Files

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.

This can make a repetitive task where the deliverable is a multi-tabbed Excel workbook a scriptable task. I only used a small part of the package’s capabilities and found the tools easy to use. The basic gist in the way I used the packages was to:

1. Create a workbook object in R
2. Add data sets to it (each data set is a tab)
3. Write it out to a file

There is also tooling to do all sorts of styling and other ways to impress your boss.

Example

## Load dependencies
if (!require('openxlsx')) install.packages('openxlsx')
library('openxlsx')

## Split data apart by a grouping variable;
##   makes a named list of tables
dat <- split(mtcars, mtcars$cyl)
dat


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

Addendum:

A reader, Hans, posted in the comments an approach to multiple tabs that I was unaware worked. Simply passing the list of tables as seen below works:

write.xlsx(dat, file='example.xlsx')

The approach I discuss in the main post becomes relevant if you wish to do any sort of styling to the sheets, otherwise, if you just want multiple tabs write.xlsx does the job nicely.

Advertisement