XLConnect 0.1-5

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

# 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)

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


About Mirai Solutions

Mirai Solutions is an independent consulting company with broad expertise in statistics, finance and IT. Together with our customers, we design and implement smart sustainable solutions ranging from small-scale software components or prototypes to enterprise-wide applications and platforms, positioning your organization for long-term success. Our interdisciplinary team can help you cover all aspects in developing the tools and processes you need to interpret complex statistical data and transform them into better business decisions. Be it highly customized and specialized advisory services and risk management solutions for financial services institutions, or more general statistical or IT consulting, or training courses on related topics, Mirai offers the appropriate services for all cases. At Mirai, we pride ourselves in the high level of customer satisfaction among all our existing clients.
This entry was posted in R, XLConnect. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s