XLConnect FAQ

This is caused by the fact that XLConnect needs to copy your entire data object over to the JVM in order to write it to a file and the JVM has to be initialized with a fixed upper limit on its memory size. To change this amount, you can pass parameters to the R's JVM just like you can to a command line Java process via rJava's options support:

options(java.parameters = "-Xmx1024m")
library(XLConnect)

Note, however, that these parameters are evaluated exactly once per R session when the JVM is initialized - this is usually once you load the first package that uses Java support, so you should do this as early as possible.

If you're using R from within Eclipse/StatET, the JVM is initialized as soon as the R process is started due to the required communication between Eclipse and R (the rj/rj-server components). Therefore, you need to set the Java heap size in Eclipse's "Run Configurations" for the R engine:

Some general Java advice: The upper limit of the Xmx parameter is system dependent - most prominently, 32bit Windows will fail to work with anything much larger than 1500m, and it is usually a bad idea to set Xmx larger than your physical memory size because garbage collection and virtual memory do not play well together.

XLConnect does not currently allow direct access to low-level formatting options. However, it is possible to assign named cell styles to cells, so the preferred workflow would be to

• define some named cell styles in an Excel document ("Format" -> "Styles" in pre-2007 Excel, "Cell styles" on the default pane of Excel 2007 and on)
• save the document to a file
• then load this template in XLConnect and use the setCellStyle method to assign the predefined styles:
  

  library(XLConnect)
  w <- loadWorkbook("template.xls")
  df <- data.frame("foo")
  c <- getCellStyle(w, "FatFont")
  
  writeWorksheet(w, data=df, sheet=1,startRow=1,startCol=1,header=TRUE)
  setCellStyle(w, sheet=1,row=1,col=1,cellstyle=c)
  saveWorkbook(w)
  

(Unfortunately, this does not work with the XLConnect 0.1-3 due to a bug (#50912) in the version of Apache POI that it uses. XLConnect 0.1-4 comes with a fixed POI and should no longer suffer from this issue.)

This type of error is triggered when XLConnect can not determine the value of a cell in the region you're trying to import. Usually, this happens because Apache POI does not support all possible Excel formulae. A particular problem are array formulas and array functions such as TRANSPOSE.

There is no direct solution for accessing the values of fields that Apache POI doesn't know how to compute. However, if you can live without the cell values and just want to ignore uncomputable cells, have a look at the onErrorCell function (new in XLConnect 0.1-4) to tell XLConnect that you want to ignore errors.

If you ware willing to invest manual effort and have Excel you can manually create a static copy that can be imported like this:

• Select the region containing your data
• Edit -> Copy
• Select an empty cell and Edit -> Paste Special
• In the Paste radio group select Values

You should then be able to import the pasted region without problems.