Saving variable number of objects to .xls file

Question

[luca]

Hi there, As a result of my -omics analyses I have a series of data frames that I would like to output. Since they all belong to the same analysis I thought it was nice to save all these dataframes into a single .xls file, putting each dataframe into a separate sheet. I found the function xlsx.writeMultipleData from the r2excel package that would do the job for me. The code for this function is:

xlsx.writeMultipleData = function (file, ...)  
{  
  require(xlsx, quietly = TRUE)  
  objects <- list(...)  
  fargs <- as.list(match.call(expand.dots = TRUE))  
  objnames <- as.character(fargs)[-c(1, 2)]  
  nobjects <- length(objects)  
  for (i in 1:nobjects) {  
    if (i == 1)  
      write.xlsx(objects[[i]], file, sheetName = objnames[i])  
    else write.xlsx(objects[[i]], file, sheetName = objnames[i],  
                    append = TRUE)  
  }  
}

If I assume to have two dataframe like this:

df1 <- data.frame(name = c("Jon", "Bill", "Maria"),
                 age = c(23, 41, 32))  
df2 <- data.frame(name =c("Albert", "Mary","Joe"),
                 age = c(80,28,1))

I can save df1 and df2 in the same file by doing: xlsx.writeMultipleData(file="outputfile.xls",df1,df2).

My problem arise from the fact that the number of dataframes that I generate is not always constant (i.e. one time I can have df1 and df2, one time I can have df1, df2 and df3, one time I can have df1, df2, df3 and df4). So, I was wondering is there a general way to tell the function to save all the objects whose name matches df into one file, without having to explicitly write all of them? I know it is not R, but what I am looking for is something like xlsx.writeMultipleData(file="outputfile.xls", df*).

Thanks a lot in advance for your help
Luca

Answer 1

[rpolicastro]

I'm not sure how your data.frames are being generated, but you generally want to approach problems like this by saving your data.frames into a named list, either upon generation or after the fact. If you do, you can use a function such as writexl::write_xlsx to save that named list of data.frames to an excel file with each named list element as a tab.

That being said, there is a way to do this I do not recommend:

dfs <- ls(pattern="df")
names(dfs) <- dfs
writexl::write_xlsx(lapply(dfs, get), "tables.xlsx")

[luca]

Hi rplicastro! You keep saving my life with your adivice! :D Thanks a lot. you suggestion to use writexl::write_xlsx works like a charm. My dataframes are already attached to the R environment. I only have to attach them to the same list.

Can I ask you why you do not recommend the solution you mentioned above?

[rpolicastro]

Glad to hear my answers are helping!

The code itself is perfectly valid, but generally if you are using certain functions (e.g. get, <<-, eval, etc.) there is usually a better way to approach the problem. I don't know how the data.frames are being generated in your case, but ideally they would be generated in a list already so that you don't need to search for them in the environment, or manually specify the list later.

[luca]

I see, thanks for the explanation! 😊