Export data frames in list to xlsx with named sheets

Question

I need to create an xlsx file with my list of data frames. I came across this solution openxlsx solution, see below (or 5th Answer by Syed). However, my list has 51 named data frames, what changes do I make to below for a long list of data frames? Because my created excel file is not opening.

require(openxlsx)
list_of_datasets <- list("Name of DataSheet1" = dataframe1, "Name of Datasheet2" = dataframe2)
write.xlsx(list_of_datasets, file = "writeXLSX2.xlsx")

I tried to run write.xlsx(listname,file="") , the command ran successfully and created an xlsx file, but while opening it, it throws up an error "microsoft excel unable to open file because it is corrupt". I tried to shorten length of the vector down to 1-2, but it won't open.

EDIT: SOLVED BY HACK for now

#extract all data frames 
list2env(soup ,.GlobalEnv)
#reassign names and form new list 
list_of_datasets1 <- list("filename"=dataframe,.....)
#write new list
write.xlsx(list_of_datasets1, file = "template.xlsx")

Answer 1

To create a workbook with multiple named worksheets, one must use createWorkbook(), addWorksheet(), writeDataTable(), and saveWorkbook() (in this order) instead of write.xlsx(). Here is an example that generates worksheets based on a list of data frames that I create with random data.

library(openxlsx)
id <- 1:5
# create data frames
aList <- lapply(id,function(x){
   # generate output as list so we can use id as index to worksheets
   list( data.frame(matrix(runif(50),nrow=10,ncol=5)),x)
})
# initialize a workbook
wb <- createWorkbook("Workbook")
# add worksheets to workbook
lapply(aList,function(x){
     addWorksheet(wb,paste("worksheet",x[[2]])) 
     writeDataTable(wb,paste("worksheet",x[[2]]),x[[1]])
})
# save workbook to disk once all worksheets and data have been added
saveWorkbook(wb,file="./data/newWorkbook.xlsx")

...and the output, noting that there are 5 tabs in the workbook.