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.

Answer 2

In addition to the solution of @mnel, here is some general information about functions.

In general, a function looks something like this:

function_name = function(input_a, input_b) {
    c = input_a * 2
    d = do_something(input_b)
    return(list(c, d))
}

where input_a, and input_b are the input arguments, and list(c, d) is the return value. This return value is assigned to the object which is on the left hand side of the = when calling the function:

out_a = function_name(a, b)

note that in the function body of function_name, a and b replace input_a and input_b. The fact that a is linked to input_a is done based on the order of the arguments. Alternatively, one can use named arguments:

out_a = function(input_a = a, input_b = b)

this makes the function call more readable in my opinion, especially for well chosen function and argument names.