Easily Make Multi-tabbed .xlsx Files with openxlsx

February 14, 2018
By

[This article was first published on R – TRinker's R Blog, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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)

 

To leave a comment for the author, please follow the link and comment on their blog: R – TRinker's R Blog.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers

Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)