exports hundreds of dataframes as xlsx using loop in R

Question

I created hundreds of data frames in R, and I want to export them to a local position. All the names of the data frames are stored in a vector :

name.vec<-c('df1','df2','df3','df4','df5','df5')

each of which in name.vec is a data frame . what I want to do is to export those data frames as excel file, but I did not want to do it the way below :

library("xlsx")
write.xlsx(df1,file ="df1.xlsx")
write.xlsx(df2,file ="df2.xlsx")
write.xlsx(df3,file ="df3.xlsx")

because with hundreds of data frames, it's tedious and dangerous. I want some thing like below instead :

library('xlsx')
for (k in name.vec) {
  write.xlsx(k,file=paste0(k,'.xlsx'))
}

but this would not work.

Anyone know how to achieve this? your time and knowledge would be deeply appreciated. Thanks in advance.

Answer 1

The first reason the for loop doesn't work is that the code is attempting to write a single name, 'df1' for example, as the xlsx file contents, instead of the data frame. This is because you're not storing the data frames themselves in the "name.vec" you have. So to fix the for loop, you'd have to do something more like this:

df.list<-list(df1,df2,df3)
name.vec<-c('df1','df2','df3')

library('xlsx')

for (k in 1:length(df.list)){
  write.xlsx(df.list[[k]],file=paste0(name.vec[k],'.xlsx'))
}

However, for loops are generally slower than other options. So here's another way:

sapply(1:length(df.list),
       function(i) write.xlsx(df.list[[i]],file=paste0(name.vec[i],'.xlsx')))

Output is 3 data frames, taken from the df list, named by the name vector.

It may also be best to at some point switch to the newer package for this: writexl.