<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/xlconnect-0-2-14/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://mirai-solutions.ch/news/2018/01/25/XLConnect-0.2-14/"> Mirai Solutions</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><a rel="nofollow" href="https://cran.r-project.org/package=XLConnect" target="_blank"><img alt="CRAN_Status_Badge" src="https://www.r-pkg.org/badges/version/XLConnect"></a> <a rel="nofollow" href="http://depsy.org/package/r/XLConnect" target="_blank"><img alt="Research software impact" src="http://depsy.org/api/package/cran/XLConnect/badge.svg"></a> <a rel="nofollow" href="http://www.rdocumentation.org/packages/XLConnect?tap_a=5644-dce66f&tap_s=10907-287229" target="_blank"><img alt="Rdoc" src="http://www.rdocumentation.org/badges/version/XLConnect"></a></p>
<p>We are pleased to announce the release of <strong>XLConnect 0.2-14</strong>, now available from <a rel="nofollow" href="https://cran.r-project.org/package=XLConnect" target="_blank">CRAN</a> and from our <a rel="nofollow" href="https://github.com/miraisolutions/xlconnect" target="_blank">GitHub</a> repository.</p>
<p><span></span></p>
<p>This release is mainly a maintenance release and comes with the following changes:</p>
<ul>
<li>Upgrade to <a rel="nofollow" href="http://poi.apache.org/" target="_blank">Apache POI</a> 3.17</li>
<li>Upgraded <strong>XLConnectJars</strong> dependency to version 0.2-14</li>
<li>Fixed Java version specification in system requirements</li>
<li>Checking Java version at runtime according to <a rel="nofollow" href="https://cran.r-project.org/doc/manuals/r-release/R-exts.html" target="_blank">CRAN requirements</a></li>
<li>Added <code>existsCellStyle</code> to check for existence of cell styles (<a rel="nofollow" href="https://github.com/miraisolutions/xlconnect/issues/71" target="_blank">#71</a>)</li>
<li>Added <code>getOrCreateCellStyle</code> combinator (<a rel="nofollow" href="https://github.com/miraisolutions/xlconnect/issues/14" target="_blank">#14</a>, <a rel="nofollow" href="https://github.com/miraisolutions/xlconnect/issues/71" target="_blank">#71</a>)</li>
<li>Updates to vignettes: <a rel="nofollow" href="https://cran.r-project.org/package=XLConnect/vignettes/XLConnect.pdf" target="_blank">The XLConnect Package</a>, <a rel="nofollow" href="https://cran.r-project.org/package=XLConnect/vignettes/XLConnectImpatient.pdf" target="_blank">XLConnect for the Impatient</a></li>
</ul>
<p>The following example illustrates the use of <strong>XLConnect</strong> to produce a simple report:</p>
<div>
<div>
<pre><code><span>require</span><span>(</span><span>XLConnect</span><span>)</span><span>
</span><span># Create new Excel workbook</span><span>
</span><span>wb</span><span> </span><span>&lt;-</span><span> </span><span>loadWorkbook</span><span>(</span><span>&quot;earthquakes.xlsx&quot;</span><span>,</span><span> </span><span>create</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>)</span><span>
</span><span># Create worksheet to hold data</span><span>
</span><span>targetSheet</span><span> </span><span>&lt;-</span><span> </span><span>&quot;Earthquakes&quot;</span><span>
</span><span>createSheet</span><span>(</span><span>wb</span><span>,</span><span> </span><span>name</span><span> </span><span>=</span><span> </span><span>targetSheet</span><span>)</span><span>
</span><span># Export earthquake data</span><span>
</span><span>writeWorksheet</span><span>(</span><span>wb</span><span>,</span><span> </span><span>data</span><span> </span><span>=</span><span> </span><span>quakes</span><span>,</span><span> </span><span>sheet</span><span> </span><span>=</span><span> </span><span>targetSheet</span><span>)</span><span>
</span><span># Create cell style to color events with a magnitude >= 6.0 on the</span><span>
</span><span># Richter scale</span><span>
</span><span>largeMagnitudeStyle</span><span> </span><span>&lt;-</span><span> </span><span>getOrCreateCellStyle</span><span>(</span><span>wb</span><span>,</span><span> </span><span>name</span><span> </span><span>=</span><span> </span><span>&quot;Large Magnitude&quot;</span><span>)</span><span>
</span><span># Use solid red foreground to color rows</span><span>
</span><span>setFillForegroundColor</span><span>(</span><span>largeMagnitudeStyle</span><span>,</span><span> </span><span>color</span><span> </span><span>=</span><span> </span><span>XLC</span><span>$</span><span>COLOR.RED</span><span>)</span><span>
</span><span>setFillPattern</span><span>(</span><span>largeMagnitudeStyle</span><span>,</span><span> </span><span>fill</span><span> </span><span>=</span><span> </span><span>XLC</span><span>$</span><span>FILL.SOLID_FOREGROUND</span><span>)</span><span>
</span><span># Determine large magnitude earthquakes</span><span>
</span><span>rowIdx</span><span> </span><span>&lt;-</span><span> </span><span>which</span><span>(</span><span>quakes</span><span>$</span><span>mag</span><span> </span><span>>=</span><span> </span><span>6.0</span><span>)</span><span>
</span><span># Color rows accordingly</span><span>
</span><span># NOTE: Since we include a header row in the worksheet</span><span>
</span><span># we need to offset the row indices by 1!</span><span>
</span><span>setCellStyle</span><span>(</span><span>wb</span><span>,</span><span> </span><span>sheet</span><span> </span><span>=</span><span> </span><span>targetSheet</span><span>,</span><span> </span><span>row</span><span> </span><span>=</span><span> </span><span>rep</span><span>(</span><span>rowIdx</span><span> </span><span>+</span><span> </span><span>1</span><span>,</span><span> </span><span>each</span><span> </span><span>=</span><span> </span><span>ncol</span><span>(</span><span>quakes</span><span>)),</span><span>
</span><span>col</span><span> </span><span>=</span><span> </span><span>rep_len</span><span>(</span><span>seq_len</span><span>(</span><span>ncol</span><span>(</span><span>quakes</span><span>)),</span><span> </span><span>length</span><span>(</span><span>rowIdx</span><span>)),</span><span>
</span><span>cellstyle</span><span> </span><span>=</span><span> </span><span>largeMagnitudeStyle</span><span>)</span><span>
</span><span># Save workbook</span><span>
</span><span>saveWorkbook</span><span>(</span><span>wb</span><span>)</span><span>
</span></code></pre>
</div>
</div>
<p></p>
<p><strong>XLConnect</strong> is a comprehensive and cross-platform R package for manipulating Microsoft Excel files from within R. It does not require any installation of Microsoft Excel or any other special drivers to be able to read &amp; write Excel files. The only requirement is a recent version of a Java Runtime Environment (JRE).</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="http://mirai-solutions.ch/news/2018/01/25/XLConnect-0.2-14/"> Mirai Solutions</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/mrvbg3VRsY4" height="1"></a></div>