Use R to write multiple sheets in excel with dynamic sheetNames

Question

This can easily be done using for loop but I am looking for a solution with lapply or dplyr.

I have multiple dataframes which I want to export to excel file in separate sheets (I saw many questions and answers on similar lines but couldn't find one that addressed naming sheets dynamically).

I want to name the sheet by the name of the dataframe. For simplicity, I have named the dataframes in a pattern (say df1 to df10). How do I do this?

Below is a reproducable example with my attempt with two dataframes mtcars and cars (which is working, but without good sheetnames).

names_of_dfs=c('mtcars','cars') 
# variable 'combined' below will have all dfs separately stored in it
combined = lapply(as.list(names_of_dfs), get)
names(combined)=names_of_dfs # naming the list but unable to use below

multi.export=function(df,filename){
  return(xlsx::write.xlsx(df,file = filename,
                          sheetName = paste0('sheet',sample(c(1:200),1)),
                          append = T))
}

lapply(combined, function(x) multi.export(x,filename='combined.xlsx'))

In case it can be done more easily with some other r package then please do suggest.

Answer 1

Try something like this:

library(xlsx)
#Workbook
wb = createWorkbook()
#Lapply
lapply(names(combined), function(s) {
  sht = createSheet(wb, s)
  addDataFrame(combined[[s]], sht)
})
saveWorkbook(wb, "combined.xlsx")