Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

The possibility of saving several tables in a single file is a nice feature of Excel. When sharing results with colleagues, it might be useful to compact everything in a single file.
As a bioinformatician, I am too lazy to do that manually, and I searched the web for tools that allow doing that.
I found out that there are at least two R packages that work well: xlsx and openxlsx.
First of all, let’s install the packages.

install.packages(c("xlsx","openxlsx"))


Now, let’s try to write some random stuff using openxlsx.

start_time <- Sys.time()
library(openxlsx)
of="example_1.xlsx"
OUT <- createWorkbook()
for(aaa in 1:20)
{
mdf<-data.frame(matrix(runif(n=1000),ncol=10,nrow=100))
sname<-paste("Worksheet_",aaa,sep="")
writeData(OUT, sheet = sname, x = mdf)
}
saveWorkbook(OUT,of)
end_time <- Sys.time()
end_time-start_time


Running time on my machine is around 3 seconds.
Now let's try to save some very similar random stuff using xlsx

 start_time <- Sys.time() library(xlsx) of="example_2.xlsx" for(aaa in 1:20) { mdf<-data.frame(matrix(runif(n=1000),ncol=10,nrow=100)) sname<-paste("Worksheet_",aaa,sep="") ifelse(aaa==1,app<-"FALSE",app<-"TRUE") write.xlsx(mdf,file=of,sheetName=sname,row.names=FALSE,append=as.logical(app)) } end_time <- Sys.time() end_time-start_time 

Running time is approximately 25s.

It looks like openxlsx is much faster than xlsx, and so I currently prefer it.
If you try to use openxlsx to save with the name of an existing file, you will get an error message.

saveWorkbook(OUT,outfile)
Error in saveWorkbook(OUT, outfile) : File already exists!


This might be useful if you want to be sure you never overwrite files by mistake, but it can also be annoying. To avoid this error message and allow overwriting of files, you just have to use the following command:

saveWorkbook(OUT,outfile,overwrite=TRUE)


Note: I had an experience (which I am no longer able to reproduce, and maybe I am not remembering well!) of openxlsx being unable to save files that xlsx could save without any problem. With this in mind, I kept note of both approaches, hoping that at least one would work in any situation!