Use R to write multiple tables to a single Excel file

August 7, 2018
By

(This article was first published on R – Fabio Marroni's Blog, and kindly contributed to R-bloggers)

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="")
addWorksheet(OUT, sname)
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!

To leave a comment for the author, please follow the link and comment on their blog: R – Fabio Marroni's Blog.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers


Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)