Mirai Solutions GmbH (http://www.mirai-solutions.com) is pleased to announce the availability of XLConnect 0.1-5. This release adds the following new features:

• Support for setting/getting cell formulas. See methods set/getCellFormula.
• Support for setting/getting the force formula recalculation flag on worksheets. See methods set/getForceFormulaRecalculation.
• Support for setting auto-filters via the setAutoFilter method.
• There is a set of new utility functions:
• aref2idx, idx2aref: Converts Excel area references (such as A5:D14) to row & column based cell references and vice versa.
• aref: Constructs an Excel area reference from a specification of the top left corner and the dimensionality of an object.
• getReferenceCoordinates: Queries the coordinates of an Excel name representing an Excel area reference (such as A5:D14)

See the following simple example on using cell formulas with XLConnect 0.1-5:

# Load the XLConnect package
require(XLConnect)

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

# Create a sheet named 'mtcars'
createSheet(wb, name = "mtcars")

# Create a named region called 'mtcars' referring to the sheet
# called 'mtcars'
createName(wb, name = "mtcars", formula = "mtcars!$A$1")

# Write built-in data set 'mtcars' to the above defined named region.
writeNamedRegion(wb, mtcars, name = "mtcars")

# Now, let us get Excel to calculate average weights.
# Where did we write the dataset?
corners <- getReferenceCoordinates(wb, "mtcars")
# Put the average under the wt column
colIndex <- which(names(mtcars) == "wt")
rowIndex <- corners[2,1] + 1

# Construct the input range & formula
input <- paste(idx2cref(c(corners[1,1], colIndex,
			  corners[2,1], colIndex)), collapse=":")
formula <- paste("AVERAGE(", input, ")", sep="")

setCellFormula(wb, "mtcars", rowIndex, colIndex, formula)

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

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”) or have a look into the reference manual.

We would be glad to hear about how you use XLConnect. Do you mainly use it for data import/export or do you e.g. use it for reporting purposes? What are the features you most often make use of? Your feedback will help us to further improve XLConnect. For general feedback 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]. You may also want to check out our new XLConnect FAQ.