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.

score 4 · Answer 1 · answered Dec 19 '20 at 16:45

4

Here's an approach with writexl:

library(writexl)
write_xlsx(setNames(lapply(names_of_dfs,get),names_of_dfs),
           path = "Test.xlsx")

We need to use setNames because the names of the sheets are set from the list names. Otherwise, the unnamed list that lapply returns will result in default sheet names.

answered Dec 19 '20 at 16:45

Ian Campbell

23,484
14
36
57

Thanks. I didn't know about `setNames` (I was searching for something like this). Didn't use your solution as I not very familier with this package and I got another answer. But I am sure this also works, so thanks again :) – Dayne Dec 19 '20 at 17:03
This method is more concise. It simply takes a list from R and export to an Excel file. – Steve Feb 08 '21 at 03:02
@Steve: I realised this later. I was too lazy to explore a new package and so depended on `xlsx` based answer. But indeed this is neater. – Dayne May 30 '22 at 04:36

score 1 · Accepted Answer · answered Dec 19 '20 at 16:45

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

answered Dec 19 '20 at 16:45

Duck

39,058
13
42
84

1

Thanks! This works. After this answer I realized I don't even need to create list of all my dfs. In `addDataFrame` I used `get(s)` instead. – Dayne Dec 19 '20 at 17:01

Use R to write multiple sheets in excel with dynamic sheetNames

2 Answers2