<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/modifying-excel-files-using-openxlsx/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://webbedfeet.netlify.com/post/modifying-excel-files-using-openxlsx/"> R on Abhijit Dasgupta</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I’ve been creating several output tables for a paper, which I usually store as<br>
sheets in an Excel file, since my collaborators are entirely in the Microsoft Office ecosystem.</p>
<p>One issue I often run into is having to modify a single sheet in that file with updated data, while keeping the rest of the file intact. This is necessary since I’ve perhaps done some custom formatting in Excel on some of the tables, and I don’t<br>
want to re-format them everytime I modify a single sheet. This problem can be alleviated by creating output functions in R that properly format the output tables in the first place, making the entire process reproducible. Working on this one, stay tuned!</p>
<p>But for now, how to modify a single Excel sheet in a file? The <a rel="nofollow" href="https://cran.r-project.org/package=openxlsx" target="_blank"><code>openxlsx</code></a> package allows this to be done very easily. As an aside, if you interact with R and Excel and are not using <code>openxlsx</code>, why aren’t you? This doesn’t depend on Java and has several powerful features.</p>
<p>The following code reads an existing Excel file, checks if a particular sheet exists,<br>
creates it if it doesn’t, and writes data from a data.frame <code>results</code> to it and then saves it back on disk:</p>
<pre><code>library(openxlsx)
boldHeader &lt;- createStyle(textDecoration = 'bold') # Makes first row bold
wb &lt;- loadWorkbook('Tables.xlsx')
if (!('Supplemental Table 1' %in% names(wb))) addWorksheet(wb, 'Supplemental Table 1')
writeData(wb, 'Supplemental Table 1', results, headerStyle = boldHeader)
setColWidths(wb, 'Supplemental Table 1', cols = 1:ncol(results), widths = 'auto')
saveWorkbook(wb, 'Tables.xlsx', overwrite = T)</code></pre>
	<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://webbedfeet.netlify.com/post/modifying-excel-files-using-openxlsx/"> R on Abhijit Dasgupta</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/uFXrvQDzvCI" height="1"></a></div>