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

Is each dataframe supposed to be a new sheet? – Cybernetic Dec 30 '17 at 00:41 — Cybernetic, Dec 30 '17 at 00:41
Yes it is, it's meant to be used as a data entry form. – pyeR_biz Dec 30 '17 at 00:45 — pyeR_biz, Dec 30 '17 at 00:45

Len Greski · Accepted Answer · 2017-12-30T13:00:38.977

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.

Thanks, @Len Greski . It's important for my named elements in the list to come out as sheet names, its like a form that needs to be filled, so the name of the form is important. I have managed to complete the task by an inefficient way for now. Please see my edit. — pyeR_biz, Dec 30 '17 at 03:12

score 0 · Answer 2 · edited Jul 19 '18 at 21:19

0

You can also simply use append to create new sheets and name them as you want.

write.xlsx(datatable, file = "File.xlsx", sheetName = "sheet1", row.names = FALSE)

edited Jul 19 '18 at 21:19

Mr. T

11,960
10
32
54

answered Jul 19 '18 at 21:00

michelad

1

score 0 · Answer 3 · answered May 31 '23 at 20:51

0

You can pass the list names to the sheetName argument as a function:

openxlsx::write.xlsx(datalist, file = "file.xlsx"),
                 sheetName = names(datalist), rowNames = FALSE)

answered May 31 '23 at 20:51

Emily

41
4

Export data frames in list to xlsx with named sheets

3 Answers3