Quickly export multiple R objects to an Excel Workbook

June 19, 2014
By

(This article was first published on statMethods blog, and kindly contributed to R-bloggers)

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.

To leave a comment for the author, please follow the link and comment on his blog: statMethods blog.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



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.