Handling java.lang.OutOfMemoryError when writing to Excel from R

Building on @richie-cotton answer, I found adding gc() to the jgc function kept the CPU usage low.

jgc <- function()
{
  gc()
  .jcall("java/lang/System", method = "gc")
}    

My previous for loop still struggled with the original jgc function, but with extra command, I no longer run into GC overhead limit exceeded error message.

This is a known issue: http://code.google.com/p/rexcel/issues/detail?id=33

While unresolved, the issue page links to a solution by Gabor Grothendieck suggesting that the heap size should be increased by setting the java.parameters option before the rJava package is loaded. (rJava is a dependency of xlsx.)

options(java.parameters = "-Xmx1000m")

The value 1000 is the number of megabytes of RAM to allow for the Java heap; it can be replaced with any value you like. My experiments with this suggest that bigger values are better, and you can happily use your full RAM entitlement. For example, I got the best results using:

options(java.parameters = "-Xmx8000m")

on the machine with 8GB RAM.

A further improvement can be obtained by requesting a garbage collection in each iteration of the loop. As noted by @gjabel, R garbage collection can be performed using gc(). We can define a Java garbage collection function that calls the Java System.gc() method:

jgc <- function()
{
  .jcall("java/lang/System", method = "gc")
}    

Then the loop can be updated to:

for(i in seq_along(the_data))
{
  gc()
  jgc()
  message("Creating sheet", i)
  sheet <- createSheet(wb, sheetName = names(the_data)[i])
  message("Adding data frame", i)
  addDataFrame(the_data[[i]], sheet)
}

With both these code fixes, the code ran as far as i = 29 before throwing an error.

One technique that I tried unsuccessfully was to use write.xlsx2 to write the contents to file at each iteration. This was slower than the other code, and it fell over on the 10th iteration (but at least part of the contents were written to file).

for(i in seq_along(the_data))
{
  message("Writing sheet", i)
  write.xlsx2(
    the_data[[i]], 
    "test.xlsx", 
    sheetName = names(the_data)[i], 
    append    = i > 1
  )
}