Use R to write multiple tables to a single Excel file
This article is originally published at https://fabiomarroni.wordpress.com
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)
Edit:
Thanks to comments from Roman and code from Mihal, it was very easy to also test a third option, writexl.
Code below:
start_time <- Sys.time() library(writexl) of="example_3.xlsx" sheets<-list() for(aaa in 1:20) { mdf<-data.frame(matrix(runif(n=1000),ncol=10,nrow=100)) sname<-paste("Worksheet_",aaa,sep="") sheets[[sname]]<-mdf } write_xlsx(sheets,of) end_time <- Sys.time() end_time-start_time
Running time is less than 2 seconds.
Wow, how many option for writing excel files. And I still have to try XLConnect, as suggested by Ellie!
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!
Thanks for visiting r-craft.org
This article is originally published at https://fabiomarroni.wordpress.com
Please visit source website for post related comments.