Quickly export multiple R objects to an Excel Workbook

Working with a business audience, I am frequently called upon to send analytic results to clients in the form of Excel Workbooks. The xlsx package facilitates exporting tables and datasets Excel, but I wanted a very simple function that would let me easily export an arbitrary number of R objects to an Excel Workbook in a single call. Each object should appear on in own worksheet, and the worksheets should be named after their objects.

Specifically, the function

save.xlsx("myworkbook.xlsx", mtcars, Titanic, AirPassengers, state.x77)

should save the R objects mtcars (a data frame),  Titanic (a table),  AirPassengers (a time series) and state.x77 (a matrix) to the workbook myworkbook.xlsx. Each object should be in it’s own worksheet and the worksheet should take on the name of the object.

One solution was to write a wrapper for write.xlsx() function in the xlsx package.

save.xlsx <- function (file, ...)
  {
      require(xlsx, quietly = TRUE)
      objects <- list(...)
      fargs <- as.list(match.call(expand.dots = TRUE))
      objnames <- as.character(fargs)[-c(1, 2)]
      nobjects <- length(objects)
      for (i in 1:nobjects) {
          if (i == 1)
              write.xlsx(objects[[i]], file, sheetName = objnames[i])
          else write.xlsx(objects[[i]], file, sheetName = objnames[i],
              append = TRUE)
      }
      print(paste("Workbook", file, "has", nobjects, "worksheets."))
}

Then

save.xlsx("myworkbook.xlsx", mtcars, Titanic, AirPassengers, state.x77)
[1] "Workbook myworkbook.xlsx has 4 worksheets."

spreadsheet

The method will work for data frames, matrices, time series, and tables, and has simplified my workflow greatly.

About these ads
This entry was posted in R Programming. Bookmark the permalink.

One Response to Quickly export multiple R objects to an Excel Workbook

  1. jonjhkim says:

    Reblogged this on R-Toolbox and commented:
    Very useful!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s