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.

Advertisements

About tylerrinker

Data Scientist and Open-source developer/contributor with a bent for the quantitative and a passion for text analysis.
This entry was posted in r, reports, work flow and tagged , , , , , , , , . Bookmark the permalink.

5 Responses to Easily Make Multi-tabbed .xlsx Files with openxlsx

  1. mielniczuk says:

    Often get requests for the data in spreadsheet format, along with the analysis. Your multi sheet solution could not have been more timely. Thank you. Rs best attribute is the community of contributors such as you.

  2. Rick_Pack2 says:

    Your use of an split and anonymous function is elegant. I answered a StackOverlow question about this same Excel .xlsx challenge in a not-so-elegant way. You might contribute your answer: https://stackoverflow.com/questions/48831405/create-new-worksheet-for-each-column-within-one-excel-file-in-r/48832437#48832437

  3. Hans says:

    Wouldn’t it be easier to just write:

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

    • tylerrinker says:

      @Hans…Nice. Thanks for sharing. I didn’t know you could do that. Cunningham’s Law strikes again. If its just tabs you want then the approach you shared works. If you want to do any styling of the sheets then the approach I discuss becomes relevant.

      • Hans says:

        Actually the parameters of write.xlsx allow quite a bit of sheet styling (see vignette), and global default styling can also be set using various options(“openxlsx.>xxx>) settings (see vignette). This is a great tool.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s