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].