rexcellistopenxlsx

How can I write a list of different length lists to an Excel worksheet?

This code runs just fine (though obviously you will need your username) unfortunately all it produces is "We" "Are" "Not" "Alone" on row 6 Rows 3..5 are empty

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb,sheetName = "options")

List1 <- list("Boo","yay")
List2 <- list("Sok","mmm", "Woohoo")
List3 <- list("A", "B","C","D","E","F")
List4 <- list("We","Are","Not","Alone")
ListofLists <- list(List1,List2,List3,List4)
filetosave <- "C:/Users/Username/Desktop/mylist.xlsx"

writeRow <- function(r)
{
  openxlsx::writeData(wb, "options", ListofLists[[r]], startCol = 2, startRow = r+2,  
          colNames = FALSE, rowNames = FALSE)
}

for (r in length(ListofLists))
{writeRow(r)}

saveWorkbook(wb, file = filetosave, overwrite = TRUE)
Solution

  • The error was caused by a misinterpretation of Rs length() function. length(ListOfList) returns just the length, in this case 4. So iterating over length(ListofLists) only uses the values of 4

    for (r in length(ListofLists))
{writeRow(r)}

    To get your desired result, you can use

    for (r in 1:length(ListofLists))
{writeRow(r)}

    instead. To prevent unwanted effects in case of ListofLists being empty you should use

    for (r in seq_len(ListofLists))
{writeRow(r)}