Release of XLConnect 0.1-3

February 28, 2011
By

(This article was first published on Mirai Solutions » R, and kindly contributed to R-bloggers)

Mirai Solutions GmbH (http://www.mirai-solutions.com) is proud to announce the first release of XLConnect, a comprehensive and cross-platform R package for manipulating Microsoft Excel files from within R. XLConnect differs from other related R packages in that it is completely cross-platform and as such runs under Windows, Unix/Linux and Mac (32- and 64-bit). Moreover, it does not require any installation of Microsoft Excel or any other special drivers to be able to read & write Excel files. The only requirement is a recent version of a Java Runtime Environment (JRE). This is possible thanks to Apache POI (http://poi.apache.org), a Java API to manipulate Microsoft Office documents.

XLConnect is released under the GPL-3 license. Current features include:

  • Support for Excel 97 (*.xls) and OOXML (*.xlsx) file formats
  • Creating/removing sheets & names
  • Reading/writing named regions (ranges)
  • Reading/writing worksheets
  • Adding graphics
  • (Named) cellstyles: data formats, borders, back- and foreground fill color, fill pattern, text wrapping
  • Controlling sheet visibility
  • Defining column width and row height
  • … and many more!

The package can easily be installed from CRAN via install.packages(“XLConnect”). In order to get started have a look at the numerous demos available via demo(package = “XLConnect”). E.g. in order to run the “writeNamedRegion” demo simply run demo(topic = “writeNamedRegion”, package = “XLConnect”).

In order to give you a feel straightaway on how easy it is to work with XLConnect, have a look at the following example:

# Load workbook - create if not existing
wb = loadWorkbook("XLConnect.xlsx", create = TRUE)

# Create a worksheet
createSheet(wb, name = "mtcars")

# Create a name reference
createName(wb, name = "mtcars", formula = "mtcars!$C$5")

# Write R built-in data.frame 'mtcars' to the specified named region
writeNamedRegion(wb, mtcars, name = "mtcars")

# Save workbook - this actually writes the Excel file to disk
saveWorkbook(wb)

XLConnect makes use of S4 classes and methods to deal with workbooks and cell styles. Also note that in the example above the Excel file is written to disk just at the end with the call to saveWorkbook(wb). All other operations are just performed in-memory which has great performance advantages. However, if you are just interested to read/write a single data.frame from/to an Excel file without having to create an intermediate workbook instance, you may also just make use of the easier-to-use wrapper function:

writeNamedRegionToFile("XLConnect.xlsx", mtcars, name = "mtcars", formula = "mtcars!$C$5")

This line basically has the same effect as the more granular set of statements above – it couldn’t be easier!

We hope that you find XLConnect useful – we would be glad to hear your opinions! Just write us an email to [email protected]. If you find any bugs or hope to see some currently missing features in the future, just send us an email to [email protected].


To leave a comment for the author, please follow the link and comment on his blog: Mirai Solutions » R.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: 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.