Function to save R list into separate Excel worksheets

Question

From this post I got a script that exports a list as separate worksheets in an Excel file (code as below). Now I would like to wrap it in a convenient function to reproduce this behavior by providing the input list name and output file name.

Sample data:

var1 <- list('2003' = 1:3, '2004' = c(4:3), '2005' = c(6,4,1), '2006' = 1:4 )

Current script:

require("XLConnect")
wb <- loadWorkbook("var1.xlsx", create = TRUE)
createSheet(wb, names(var1))
writeWorksheet(wb, var1, names(var1),header=FALSE)
saveWorkbook(wb)

Disclaimer: While I blush to ask such a simple question, I'm sure many other visitors to SO will love to find this information here ;7)

EDIT :: ready-to-use function:

save.excel <-function(.list, default = 'var1', path = ''){
    require("XLConnect")
    .name <- as.list(match.call())[2]
    if(is.language(.name[[1]])) wb_name <- paste0(paste0(path, default, collapse = '/'), '.xlsx')
    if(is.symbol(.name[[1]])) wb_name <- paste0(paste0(path, as.character(.name), collapse = '/'), '.xlsx')
    wb <- loadWorkbook(wb_name, create = TRUE)
    createSheet(wb, names(.list))
    writeWorksheet(wb,.list, names(.list),header=FALSE)
    saveWorkbook(wb)
    }

The only difference from the solution below is that I added XLConnect as a library requested from inside the function, in case you didn't do it manually before ;7)

Answer 1

This is untested, as XLConnect will not install on my machine. but something like the following might work

Simple approach

A function with two arguments

• my_list - the list which you wish to export elements of as separate worksheets
• wb_name - the name of the workbook

The function looks like this

write_list <-function(my_list, wb_name = 'var1.xlsx') {    
  wb <- loadWorkbook(wb_name, create = TRUE)
  createSheet(wb, names(my_list))
  writeWorksheet(wb, my_list, names(my_list),header=FALSE)
  saveWorkbook(wb)
 }

fancy option that will use the name of the list

If you want to use the name of the list to create the file then you can have fun with match.call, is.symbol and is.language. Details of why you do this are below

write_list_name <-function(.list, default = 'var1', path = ''){
  .name <- as.list(match.call())[2]
   if(is.language(.name[[1]])){
     wb_name <- sprintf("%s/%s.xlsx", path, default)
   }
   if(is.symbol(.name[[1]])) {
    wb_name <- sprintf("%s/%s.xlsx", path, as.character(.name))
   }
  wb <- loadWorkbook(wb_name, create = TRUE)
  createSheet(wb, names(.list))
  writeWorksheet(wb,.list, names(.list),header=FALSE)
  saveWorkbook(wb)
  }

the is.language / is.symbol /match.call deal with the two situations

write_list_name(var1)

#in which case .name[[1]] is the symbol var1

and

write_list_name(list(n=2:3))
# in which case .name[[1]] is list(n=2:3), and class language 
# a file called list(n=2:3).xlsx would be not ideal, hence the `default` argument.