# XLConnect 0.1-7

October 24, 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 pleased to announce the availability of XLConnect 0.1-7. This release includes a number of improvements and new features:

• Performance improvements when writing large xlsx files
• New workbook data extraction & replacement operators [, [<-, [[, [[<-. These are "syntactic sugar" for the already existing methods readWorksheet, writeWorksheet, readNamedRegion and writeNamedRegion.
• Append data to named regions and worksheets: appendNamedRegion & appendWorksheet
• Dumping and restoring data.frames to/from Excel files (xlcDump, xlcRestore)
• Support for saving workbooks to a new file ("save as")
• Support for row names
• Extended use of setMissingValue. setMissingValue allows to define a set of missing value identifiers that are recognized as missing values when reading in data. The first element of this set is used as missing value identifier when writing data.
• Auto-sizing of columns

A full listing of changes can be found here.

The following is a simple example showing the use of the new extraction/replacement operators introduced with XLConnect 0.1-7:

# Load the XLConnect package
require(XLConnect)

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

# Write mtcars data set on a worksheet named ’mtcars1’.
# Note: The ’mtcars1’ sheet will be created automatically if it does
# not exist yet. Also, default values for other writeWorksheet arguments
# hold, i.e. the data set is written starting at the top left corner.
wb["mtcars1"] = mtcars

# Write mtcars data set on a worksheet named ’mtcars2’.
# Again, the ’mtcars2’ worksheet is created automatically.
# Additionally specify arguments passed to the underlying method
# writeWorksheet.
wb["mtcars2", startRow = 6, startCol = 11, header = FALSE] = mtcars

# Read worksheets ’mtcars1’ and ’mtcars2’.
# Note: The default arguments hold for the underlying method
wb["mtcars1"]
wb["mtcars2"]

# Write mtcars data set to a named region named ’mtcars3’. Since
# it doesn’t exist yet we also need to specify the formula to
# define it. Also note that the sheet ’mtcars3’ referenced in the
# formula does not yet exist – it will be created automatically!
# Moreover, default values for other writeNamedRegion arguments hold.
wb[["mtcars3", "mtcars3!$B$7"]] = mtcars

# Redefine named region ’mtcars3’. Note that no formula specification
# is required since named region is already defined (see above example).
wb[["mtcars3"]] = mtcars

# Write mtcars data set to a named region ’mtcars4’. Since the named
# region does not yet exist a formula specification is required. Also,
# additional arguments are specified that are passed to the underlying
# method writeNamedRegion.
wb[["mtcars4", "mtcars4!$D$8", rownames = "Car"]] = mtcars

# Read the named regions ’mtcars3’ and ’mtcars4’.
# Note: Default values hold for the underlying method readNamedRegion.
wb[["mtcars3"]]
wb[["mtcars4"]]

# 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 are always happy to hear from you! 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.