3

I'm trying to load a hefty Excel workbook (.xlsm format, ~30 mb) that has a large number of array calcs. 

> wb1 <- loadWorkbook("Mar_SP_20130227_V6.1.xlsm")
Error: POIXMLException (Java): java.lang.reflect.InvocationTargetException

But I am able to successfully load a values-only/no-macro version of the workbook.

> wb2 <- loadWorkbook("Mar_SP_20130227_V6.1_VALUES_ONLY.xlsx")
> wb2
[1] "Mar_SP_20130227_V6.1_VALUES_ONLY.xlsx"

What could be causing the error?

From the maintainer's website I can see that there can be issues with workbooks containing array calcs or unsupported formula functions, but this doesn't look like the same errror.

Java Info:

C:\> java -version
java version "1.6.0_21"
Java(TM) SE Runtime Environment (build 1.6.0_21-b07)
Java HotSpot(TM) Client VM (build 17.0-b17, mixed mode)
Tommy O'Dell
  • 7,019
  • 13
  • 56
  • 69
  • What's the full stacktrace? (There should be a lot more than just that one line) – Gagravarr Apr 05 '13 at 05:02
  • Hi Gagravarr, that's all that's exposed to the user (me) by the XLConnect R package (which uses the ApachePOI). Are there logs in the java directory that would show me the stracktrace? – Tommy O'Dell Apr 05 '13 at 06:45

1 Answers1

7

It turns out that the root of this error was the JVM running out of memory (even with options(java.parameters = "-Xmx1024m")).

I tried to increase the memory, but couldn't get the JVM to take more than -Xmx2048m, which still wasn't enough to load the workbook.

So I upgraded the JRE from 32 bit to 64 bit and ran 64 bit R.

I was then able to set -Xmx4096m and successfully load my 30mb workbook.

Tommy O'Dell
  • 7,019
  • 13
  • 56
  • 69