XLConnect: Attaching to 64 bit R

Question

I have a need to export some R data to formatted excel files and have successfully used both the XLConnect and xlsx packages in R before. I recently moved to 64 bit R and can't get either to work. I worked through some Oracle/Java architecture conflicts in a bunch of other packages. I am sure there is something similar going on here, but I can't crack it.

when I call library(XLConnect), rJava and XLConnectJars attach without issue.

Here is the error and session info:

> library(XLConnect)
Loading required package: XLConnectJars
Error : .onLoad failed in loadNamespace() for 'XLConnect', details:
  call: .jfindClass(as.character(class))
  error: class not found
Error: package or namespace load failed for ‘XLConnect’

> sessionInfo()
R version 3.3.1 (2016-06-21)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] XLConnectJars_0.2-12

loaded via a namespace (and not attached):
[1] rsconnect_0.4.3 tools_3.3.1     rJava_0.9-8

I get a similar error when I try to attach xlsx.

> library(xlsx)
Loading required package: rJava
Loading required package: xlsxjars
Error : .onAttach failed in attachNamespace() for 'xlsx', details:
  call: .jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook")
  error: java.lang.UnsupportedClassVersionError: Bad version number in .class file
Error: package or namespace load failed for ‘xlsx’

If I switch to 32 bit R, everything runs fine with both XLConnect and xlsx. I really need to run 64 bit R for other requirements.

I use `readxl` to import Excel files - no issue there. I just have a couple projects that require formatted Excel outputs. My `JAVA_HOME` variable does point to a 64 bit jre. `rJava` loads fine - that should be using the 64-bit Java. — justin a, Nov 01 '16 at 19:26

score 1 · Answer 1 · answered Nov 02 '16 at 09:32

Without getting into technicalities, you will need a 64-bit version of Java to go with the 64-bit version of R.

Even though this will solve the question you posted, I think you will be helped more by abandoning the Java-based XLConnect and xlsx-packages to connect to Excel. Instead, use the openxlsx-package. It does the same stuff (yes, also exporting formatted Excel sheets) as the other two, but doesn't rely on Java.

Besides not having the 64-bit/32-bit problem with Java, there are some advantages:

Firstly, while working with large tables you will not be confronted with OutOfMemoryErrors:

Error: OutOfMemoryError (Java): GC overhead limit exceeded.

Secondly, even when the Java-based packages don't show errors, it can take ages for them to work with large tables. openxlsx does not have these issues.

On a final and personal note: I was hesitant to switch between packages at first, but it solved all the things I disliked about the Java-based packages and the syntax is very similar.

XLConnect: Attaching to 64 bit R

1 Answers1