XLConnect 0.2-0

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

# 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

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

23 Responses to XLConnect 0.2-0

  1. jme says:

    Any idea why the Mac binary version still won’t pass the CRAN checks? I can still build from source, but it makes me nervous…

  2. Hi Joran,
    the reason is that the CRAN build environment uses Java 1.5 while XLConnect requires Java 1.6. Currently, there are no automated Java version checks on CRAN to prevent the build so it just fails due to some missing dependencies (StAX API). Java 1.5 is ancient – Java 1.6 will already be EOL Nov 2012. If you have Java 1.6 installed on your (Intel) Mac, installing XLConnect from source is absolutely fine.
    Hope that helps,

  3. jme says:

    Awesome, thanks! It might be worthwhile trying to put a note about that on the CRAN page somewhere (maybe in NEWS, or something) to cut down on the number times you are pestered to answer that question! 🙂

  4. Markus says:

    Many thanks for the package! I find it really helpful and used it a few weeks ago to access UK house price data stored in a spread sheet:

  5. Can I export images and analyses from R to Excel using this package?

    • Hi Orlando,

      yes, you can add graphs created in R to your worksheets – have a look at the addImage function. Regarding analyses: Not quite sure what you mean with that but if you are refering to data/outputs that you have generated in R then yes. As long as you have data.frames you can easily write them using either writeWorksheet or writeNamedRegion. Also have a look at the package vignette that provides some examples.


  6. Steve Sidney says:

    Dear all
    This is a wonderful package and the improvements are maiking it into a ‘must have’.

    One small problem I have downloaded Ver 0.2-0 from the CRAN and I get the following files
    XLConnect ver 0.1-8
    XLConnectJars ver 0.1-4

    Are the files just incorrcetly labelled or is there a problem, Could you please investigate and let me know.
    BTW i downloaded them both in R as well as via RSTUDIO and got the same thing

  7. rpremraj says:

    Hi Martin,

    I have a question regarding copying a formula across several rows in a single column by automatically updating the references to the relevant cells.

    Say, I wish to compute the deviation of each car’s “mpg” from the average mpg in the data. Of course, this is straightforward in R, but for the sake of transparency, I wish to do using a formula in Excel.

    The setCellFormula can certainly achieve this for one row. For example, for row 2, I can set the formula as B2-AVERAGE(B2:B33) and the output is correctly computed while preserving the formula in Excel. But how can I copy this formula across other rows by automatically updating the cell references, i.e. C2-AVERAGE(B2:B33), D2-AVERAGE(B2:B33), and so on?

    Many thanks!

    • Hi Rahul,

      You can achieve what you are looking for using setCellFormula but you won’t get around building the cell formulas on your own. However, setCellFormula is completely vectorized so this can be achieved relatively easily:

      wb = loadWorkbook(“mtcars.xlsx”, create = TRUE)
      createSheet(wb, name = “mtcars”)
      writeWorksheet(wb, data = mtcars, sheet = “mtcars”, rownames = “Car”)
      which_mpg = idx2col(which(names(mtcars) == “mpg”) + 1)
      rows = seq_len(nrow(mtcars)) + 1
      setCellFormula(wb, sheet = “mtcars”, row = rows, col = ncol(mtcars) + 2,
      formula = paste(which_mpg, rows, ” – AVERAGE(“,
      paste(which_mpg, range(rows), sep = “”, collapse = “:”),
      “)”, sep = “”

      As you see, there is only one single call to setCellFormula. We’re passing
      in a vector of formulas that we first build up dynamically.

      Hope that helps.

      Best regards,

  8. david says:

    very nice.
    when reading in a text file I can have an arbitrary number of descriptive rows before the data by preceding with “#” or some other ‘comment.char’ in read.table.
    Is this possible with XLConnect without having to know upfront ‘startRow’?

    • Hi David,
      there is no direct way of doing this in XLConnect. However, you may easily write your own function that implements the “skipping” logic you are looking for, i.e. read in the data (maybe up to a max number of rows) and then determine the rows you want to skip.

      • david says:

        That works nicely. Thanks Martin.
        Since I can’t get everyone else to abandon Excel and work with text data files, with this I can avoid separate .xls and .txt versions of the data.

  9. R. Mark Sharp says:

    I am on Mac OS 10.7.5 with R 2.15.2. I have just built XLConnect 0.2-3 from source with all dependencies and am getting the following

    > require(XLConnect, quietly = TRUE)
    Loading required package: rJava
    XLConnect 0.2-3 by Mirai Solutions GmbH
    http://www.mirai-solutions.com ,

    > positive_animal_file positive_animals <- readWorksheetFromFile(positive_animal_file)
    Error in function (classes, fdef, mtable) :
    unable to find an inherited method for function ‘readWorksheet’ for signature ‘"workbook", "missing"’

    Do you have any suggestions? The builds seemed to be fine.
    P.S. Why does quietly = TRUE not work?

    • Hi Mark,

      readWorksheetFromFile also requires the specification of the sheet you actually want to read in. Please see the help file for more information and/or have a look at the package vignette at http://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf

      With respect to quietly = TRUE: according to the help file of require that only prevents printing of package loading confirmation messages. I guess what you are looking for is suppressPackageStartupMessages.

      Hope that helps.

      Best regards,

      • R. Mark Sharp says:

        Thank you for your prompt reply. I am now able to read the worksheet without an error being generated. However, I now find I have what may simply be an Excel problem. I did not find this problem addressed in the FAQ though I expected this to be common problem.

        According to Excel all five columns within the short test file (test.xlsx) have a type of text, yet two of the columns that could be interpreted as numbers are being modified before I get them. I have tried using colTypes = ‘character’ and the data frame does indeed have all character data types (shown below), but the strings look like Excel changed them into numbers. (According to my understanding and testing forceConversion = TRUE is not doing anything, but it should not hurt.)

        Note: If I insert an apostrophe at the beginning of each cell within the ID and SAMPE_DATE columns, the conversion to numbers does not occur. However, I am getting these files from another person, there are thousands of rows and I need an automated mechanism.

        From the file.
        12345 P12345 20090408 POSITIVE MOUSE
        54321 P54321 20090324 POSITIVE MOUSE
        Output from R session

        > test_wkbk test_df test_df
        1 12345.0 P12345 2.0090408E7 POSITIVE MOUSE
        2 54321.0 P54321 2.0090324E7 POSITIVE MOUSE
        > str(test_df)
        ‘data.frame’: 2 obs. of 5 variables:
        $ ID : chr “12345.0” “54321.0”
        $ SAMPLE : chr “P12345” “P54321”
        $ SAMPLE_DATE: chr “2.0090408E7” “2.0090324E7”
        $ TEST : chr “POSITIVE” “POSITIVE”
        $ SPECIES : chr “MOUSE” “MOUSE”

  10. Hi Mark,

    generally, in Excel every cell has a type (string, numeric, …). As I understand, the ID and SAMPLE_DATE columns are supposed to be numeric. However, in case in any of those (supposed-to-be) numeric columns a string would appear (even just a single cell), XLConnect would need to do a conversion to string – because string is the more generic type and data.frame columns in R can only be of one single type. A side-effect of the numeric-to-string/character conversion in the current version of XLConnect is that you will see a “.0” appended to integer numbers. What you can try to do is to force a conversion to numeric for those columns. Any cells that are not numeric and cannot be converted to a number (because they are strings like “asdf”), would be converted to NA – but you would easily be able to detect those. Also, if you don’t mind and if it’s inline with your company’s policies, I would ask if you could send me the file (or a sample subset) to xlconnect@mirai-solutions.com such that I can have a look.

    Best regards,

  11. sunny says:

    I tried to use lapply as my directories have multiple xls, xlsx and csv files. But got this error. The only way i am thinking right now is to use a composite lapply for loading and then reading and while loading apply the missing value.

    > xllfulldata

  12. sunny says:

    xllfulldata <- lapply(xlfilelist,readWorksheetFromFile,header=TRUE,startCol=0,startRow=0,endCol=-1,endRow=-1)
    Error in (function (classes, fdef, mtable) :
    unable to find an inherited method for function 'readWorksheet' for signature '"workbook", "missing"'

  13. yb2125 says:

    Thanks for this great package! Is there a plan for the future releases that would enable creating pivot tables through XLConnect?

    • Limited support for Pivot tables is available in POI for xlsx files – but not for xls files. Currently there is no plan to support pivot tables but we keep it on our list of possible extensions.

  14. pramod kumar says:

    the following codes produces the error
    setSheetColor(wb , “Red sheet”, XLC$COLOR.RED)

    Error: NoSuchMethodError (Java): org.apache.poi.ss.usermodel.IndexedColors.fromInt(I)Lorg/apache/poi/ss/usermodel/IndexedColors;

    writeWorksheet(wb, data = “mtcars”, sheet = “Red sheet”, header = TRUE, rownames = “Cars” )
    Error: NoSuchMethodError (Java): org.apache.poi.ss.usermodel.Cell.setCellType(Lorg/apache/poi/ss/usermodel/CellType;)V

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