XLConnect loadWorkbook error - POIXMLException (Ja

问题:

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)

回答1:

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.