Function to export multiple data in list to separate Excel sheet

Question

I'm trying to create a function to export multiple list elements to an excel workbook with different sheets.

I get the error:

"Error in if (nchar(sheetName) > 31) { : missing value where TRUE/FALSE needed".

My attempt is below:

library("openxlsx")

data <- list("ABCD", "EFGH")
names(data) <- c("X", "Y")

#x = list, y = file name
createWorksheet <- function(x, y){
  wb <- createWorkbook() 
  for(i in x){
    addWorksheet(wb, names(x)[i])
    writeData(wb, sheetName = names(x)[i], x[[i]])
    saveWorkbook(wb, file = y, overwrite = TRUE)
  }
}
createWorksheet(data, "data.xlsx")

Any idea what is wrong?

Do you need `for(i in seq_along(x))` instead of `for(i in x)`? — Allan Cameron, Oct 26 '20 at 14:27
You're right. I thought for loops would just assume 1:length(x) for the 2nd argument. — user13589693, Oct 26 '20 at 14:32

score 0 · Accepted Answer · answered Oct 26 '20 at 14:33

With Allan's help:

library("openxlsx")

data <- list("ABCD", "EFGH")
names(data) <- c("X", "Y")

#x = list, y = file name
createWorksheet <- function(x, y){
  wb <- createWorkbook() 
  for(i in seq_along(x)){
    addWorksheet(wb, names(x)[i])
    writeData(wb, sheet = names(x)[i], x[[i]])
    saveWorkbook(wb, file = y, overwrite = TRUE)
  }
}
createWorksheet(data, "data.xlsx")

Function to export multiple data in list to separate Excel sheet

1 Answers1