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
•
written
by
luca •
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")
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?
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.
Log in to answer this question.
