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)
  • Added a package vignette
  • 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
# readWorksheet.
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.


To leave a comment for the author, please follow the link and comment on his blog: Mirai Solutions » R.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.