<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/use-r-to-write-multiple-tables-to-a-single-excel-file/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://fabiomarroni.wordpress.com/2018/08/07/use-r-to-write-multiple-tables-to-a-single-excel-file/"> R – Fabio Marroni's Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>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.<br>
As a bioinformatician, I am too lazy to do that manually, and I searched the web for tools that allow doing that.<br>
I found out that there are at least two R packages that work well: xlsx and openxlsx.<br>
First of all, let’s install the packages.</p>
<pre>
install.packages(c(&quot;xlsx&quot;,&quot;openxlsx&quot;))
</pre>
<p>Now, let’s try to write some random stuff using openxlsx.</p>
<pre>
start_time &lt;- Sys.time()
library(openxlsx)
of=&quot;example_1.xlsx&quot;
OUT &lt;- createWorkbook()
for(aaa in 1:20)
{
mdf&lt;-data.frame(matrix(runif(n=1000),ncol=10,nrow=100))
sname&lt;-paste(&quot;Worksheet_&quot;,aaa,sep=&quot;&quot;)
addWorksheet(OUT, sname)
writeData(OUT, sheet = sname, x = mdf)
}
saveWorkbook(OUT,of)
end_time &lt;- Sys.time()
end_time-start_time
</pre>
<p>Running time on my machine is around 3 seconds.<br>
Now let's try to save some very similar random stuff using xlsx</p>
<p><code>
<pre>
start_time &lt;- Sys.time()
library(xlsx)
of=&quot;example_2.xlsx&quot;
for(aaa in 1:20)
{
mdf&lt;-data.frame(matrix(runif(n=1000),ncol=10,nrow=100))
sname&lt;-paste(&quot;Worksheet_&quot;,aaa,sep=&quot;&quot;)
ifelse(aaa==1,app&lt;-&quot;FALSE&quot;,app&lt;-&quot;TRUE&quot;)
write.xlsx(mdf,file=of,sheetName=sname,row.names=FALSE,append=as.logical(app))
}
end_time &lt;- Sys.time()
end_time-start_time
</pre>
<p></p>
<p>Running time is approximately 25s.</p>
<p>It looks like openxlsx is much faster than xlsx, and so I currently prefer it.<br>
If you try to use openxlsx to save with the name of an existing file, you will get an error message.</p>
<pre>
saveWorkbook(OUT,outfile)
Error in saveWorkbook(OUT, outfile) : File already exists!
</pre>
<p>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:</p>
<pre>
saveWorkbook(OUT,outfile,overwrite=TRUE)
</pre>
<p>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!</p>

				
		
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://fabiomarroni.wordpress.com/2018/08/07/use-r-to-write-multiple-tables-to-a-single-excel-file/"> R – Fabio Marroni's Blog</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/S4p7Bum1P7s" height="1"></a></div></code>