XLConnect 0.2-0

July 23, 2012
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 very pleased to announce the release of XLConnect 0.2-0, which can be found at CRAN.

As one of the updates, XLConnect has moved to the newest release of Apache POI: 3.8. Also, the lazy evaluation issues with S4 generics are now fixed: generic methods now fully expand the argument list in order to have the arguments immediately evaluated.
Furthermore, we have added an XLConnect.R script file to the top level library directory, which contains all code examples presented in the vignette, so that it’s easier to reuse the code.

In addition, we introduced the following improvements to existing XLConnect methods:

  • readWorksheet now returns a named list if more than one worksheet is read. This provides convenient access to the corresponding data by list subsetting.
  • readWorksheet and readNamedRegion now provide a new argument named check.names that controls whether data.frame column names are checked for syntactically valid variable names.

Inspired by some great feedback we received from XLConnect users, we have introduced the following new features to the package:

  • Support for setting the color of worksheet tabs via the setSheetColor method (only for .xlsx files)
  • Support for freeze panes via the createFreezePane method
  • Support for split panes via the createSplitPane method

A summary of news in XLConnect 0.2-0 can be found in the NEWS file on CRAN.

The following is a simple example of how the new XLConnect 0.2-0 methods can be used to produce colored tabs, freeze panes and split panes:

# Loading XLConnect package
require(XLConnect)

# Creating a new workbook
wb <- loadWorkbook("testwb.xlsx", create = TRUE)

# Creating a new sheet called "Red Sheet"
createSheet(wb, name = "Red Sheet")

# Setting the sheet tab color to red
setSheetColor(wb, "Red Sheet", XLC$COLOR.RED)

# Creating a new sheet called "Green Sheet"
createSheet(wb, name = "Green Sheet")

# Setting the sheet tab color to green
setSheetColor(wb, "Green Sheet", XLC$COLOR.GREEN)

# Writing the mtcars data frame on the Red Sheet, with header and row names
writeWorksheet (wb, data=mtcars, sheet="Red Sheet", header = TRUE, rownames="Cars")

# Autosizing the first column in the Red Sheet
setColumnWidth(wb, sheet="Red Sheet", column=1)

# Writing the USJudgeRatings data frame on the Green Sheet, with header and row names
writeWorksheet (wb, data=USJudgeRatings, sheet="Green Sheet", header = TRUE, rownames="Judge")

# Autosizing the first column in the Green Sheet
setColumnWidth(wb, sheet="Green Sheet", column=1)

# Creating a freeze pane in the Red Sheet. The top row and the leftmost column are frozen.
createFreezePane(wb, "Red Sheet", "B", 2)

# Creating a split pane on the Green Sheet, with coordinates (5000, 5000) expressed as 1/20th of a point,
# "J" as the left column visible in the right pane and 10 as the top row visible in the bottom pane 
createSplitPane(wb, "Green Sheet", 5000, 5000, "J", 10)

# Saving the workbook
saveWorkbook(wb)

We would also like to mention that we were very pleased to discover that XLConnect has been mentioned in two books about R programming:

  1. R for Dummies by Joris Meys and Andrie de Vries, page 211
  2. Grundlagen der Datenanalyse mit R: Eine anwendungsorientierte Einführung by Daniel Wollschläger, page 513

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 package vignette, the numerous demos available via demo(package = "XLConnect") or browse through the comprehensive 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 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.