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

Question

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)

Alternatively, flatten your list into a tabular format (e.g. data frame) and write as one action. — Paul Stafford Allen, Aug 24 '23 at 07:13
Hi @Martin Gal could you propose that as an answer. Yes, I know its a stupid error but it is the solution and its the most economical and closest to the original question. I was specifically not wanting to pad the lists into a data frame. Thanks very much. — Peter King, Aug 24 '23 at 21:00

score 2 · Answer 1 · answered Aug 24 '23 at 07:41

It you can save the entire list at one, i.e, not one element after ther other, here is a possible solution:

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 <- "mylist.xlsx"

# put your data in a table-like structure
to_save <- tidyr::unnest_wider(tibble::tibble(ListofLists), col = ListofLists, names_sep = "")
# save it as it is
openxlsx::writeData(wb, "options",to_save, startCol = 2, startRow = 2, colNames = FALSE, rowNames = FALSE)
saveWorkbook(wb, file = filetosave, overwrite = TRUE)

I got the idea from here: https://stackoverflow.com/a/36070406/10264278

score 1 · Accepted Answer · answered Aug 25 '23 at 16:21

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)}

For an empty list, seq_len(EmptyList) returns integer(0) and the loop doesn't do anything.
For a non-empty list, seq_len(NonEmptyList) returns 1:length(NonEmptyList).

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

2 Answers2