XLConnect 0.1-4

Mirai Solutions GmbH (http://www.mirai-solutions.com) is pleased to release XLConnect 0.1-4. In the time since our first release (XLConnect 0.1-3, released on Feb 28, 2011) we received very positive and constructive feedback from the R community which heavily influenced the development of XLConnect 0.1-4. At this point special thanks to Peter DeWitt, Doug Morrison, Bob Colner, Bill Behrman and Carl Finkbeiner.

XLConnect 0.1-4 fixes several issues and introduces many new features. The most important items are listed below:

  • An issue was fixed where cell styles would not be applied correctly.
  • There is a new DATA_FORMAT_ONLY style action: This style action instructs XLConnect to only set the data format for a cell but not to apply any other styling but rather keep the existing one. The data format to be applied is determined by the corresponding R data type and can be defined by the user. This style action may be useful in scenarios where Excel templates with pre-defined styling are to be filled with XLConnect.
  • Worksheets can be renamed with the new renameSheet method.
  • The positions (indices) of worksheets can be queried & redefined using the new methods getSheetPos & setSheetPos.
  • Worksheets can be cloned using the new method cloneSheet.
  • There is a set of new utility functions:
    • col2idx, idx2col: Converts Excel column names such as BD to indices and vice versa.
    • cref2idx, idx2cref: Converts cell references to row & column indices and vice versa.
  • Setting a missing value string when writing missing values from R to Excel. By default missing values result in blank/empty cells. This is to have a nice type mapping between R and Excel. In some cases, however, you might want to define a specific missing value string to be used by XLConnect when writing cells representing missing values instead of blank/empty cells. This can be done using the new method setMissingValue.
  • The handling of error cells (cells that result in an error when trying to evaluate them) has been greatly improved. With XLConnect 0.1-3 error cells directly resulted in an exception. With XLConnect 0.1-4 this behavior can be controlled using the onErrorCell method. By default error cells now result in a missing value and the corresponding error cells will be reported via warnings. The behavior may be changed to get an exception with the first occurrence of an error cell.
  • There is two new methods for merging & un-merging cells: mergeCells & unmergeCells.

XLConnect is 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).

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”).

For general feedback write us an email to xlconnect@mirai-solutions.com. If you find any bugs or hope to see some currently missing features in the future, just send us an email to xlconnect-bugs@mirai-solutions.com. You may also want to check out our new XLConnect FAQ.

Posted in R, XLConnect | Leave a comment

XLConnect: Frequently Asked Questions

In the two months since the first release of XLConnect we have received some great feedback from the community. Most questions we saw seemed to cluster around a few central topics – memory issues, font styling and Excel feature support. We’ve now distilled our answers to these questions into an FAQ document on our web site to help you get the most out of XLConnect. If you have any feedback please let us know so that we can improve it further!

In the mean time, we’ve also been working on the next release of XLConnect which will contain a number of bug fixes & new features which should help deal with some of the issues that keep popping up in the FAQ, so expect another update soon!

Posted in R, XLConnect | Tagged , , | 1 Comment

Release of XLConnect 0.1-3

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 xlconnect@mirai-solutions.com. If you find any bugs or hope to see some currently missing features in the future, just send us an email to xlconnect-bugs@mirai-solutions.com.

Posted in R, XLConnect | Tagged , | 2 Comments