Easy way to export multiple data.frame to multiple Excel worksheets

Question

I am surprised to find that there is no easy way to export multiple data.frame to multiple worksheets of an Excel file? I tried xlsx package, seems it can only write to one sheet (override old sheet); I also tried WriteXLS package, but it gives me error all the time...

My code structure is like this: by design, for each iteration, the output dataframe (tempTable) and the sheetName (sn) got updated and exported into one tab.

for (i in 2 : ncol(code)){          ...         tempTable <- ...         sn <- ...         WriteXLS("tempTable", ExcelFileName = "C:/R_code/../file.xlsx",               SheetNames = sn); } 

I can export to several cvs files, but there has to be an easy way to do that in Excel, right?

Answer 1

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx) write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1", row.names=FALSE) write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE) 

Another option, one that gives you more control over formatting and where the data frame is placed, is to do everything within R/xlsx code and then save the workbook at the end. For example:

wb = createWorkbook()  sheet = createSheet(wb, "Sheet 1")  addDataFrame(dataframe1, sheet=sheet, startColumn=1, row.names=FALSE) addDataFrame(dataframe2, sheet=sheet, startColumn=10, row.names=FALSE)  sheet = createSheet(wb, "Sheet 2")  addDataFrame(dataframe3, sheet=sheet, startColumn=1, row.names=FALSE)  saveWorkbook(wb, "My_File.xlsx") 

In case you might find it useful, here are some interesting helper functions that make it easier to add formatting, metadata, and other features to spreadsheets using xlsx: http://www.sthda.com/english/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-software

Answer 2

You can also use the openxlsx library to export multiple datasets to multiple sheets in a single workbook.The advantage of openxlsx over xlsx is that openxlsx removes the dependencies on java libraries.

Write a list of data.frames to individual worksheets using list names as worksheet names.

require(openxlsx) list_of_datasets <- list("Name of DataSheet1" = dataframe1, "Name of Datasheet2" = dataframe2) write.xlsx(list_of_datasets, file = "writeXLSX2.xlsx")