XLConnect 0.1-7

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 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 these ads

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.

2 Responses to XLConnect 0.1-7

  1. Ian Fellows says:

    Very cool stuff. Thank you very much for making this package available. I hated not being able to support the most widely used consumer data storage format.

    FYI, as of JGR 1.7-8 (propagating though CRAN now). JGR/Deducer use XLConnect to load in workbooks as data.frames. Nothing fancy, but definitely something our users have been asking for for some time.

  2. This is really the second blog, of yours I read through.

    And yet I personally love this particular one, “XLConnect 0.

    1-7 | Mirai Solutions” the very best. Thanks ,Shad

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s