Write a list of named data.frames to an xlsx file

Question

I trying to use the xlsx package to save a list of named dataframes to an xlsx file. The data frames in the list are all named, but it would be nice if it worked on unnamed elements of the list as well.

Here's the function I've written:

toXLS <- function(myList,myFile) {

    #Initialize output workbook
    require(xlsx)
    wb <- createWorkbook()
    for (name in names(myList)) {
        createSheet(wb, sheetName=name)
    }
    
    #Write blank workbook to file
    saveWorkbook(wb, myFile)

    #Write output data to saved workbook
    for (name in names(myList)) {
        write.xlsx(myList[[name]], myFile, sheetName=name)
    }
}

myList <- list(a=data.frame(1,2,3),b=data.frame('a','b','c'),d=data.frame('01-01-1900'))
toXLS(myList,paste(getwd(),'/output.xlsx',sep=''))

It works all right, but overwrites the output workbook each time I call write.xlsx. How do I insert a data.frame as a sheet in an workbook?

I'm not married to the xlsx package, but it seems to provide a good interface to excel files, with the least setup required. I'm open to other good options as well.

Answer 1

First create the Excel file:

wb <- createWorkbook()
saveWorkbook(wb, 'output.xlsx')

Then append each workbook:

lapply(names(myList), function(x) write.xlsx(myList[[x]], 'output.xlsx', sheetName=x, append=TRUE))