Although the xlsx package is installed in R, it does not work

Question

Although the xlsx package is installed, it does not work. I get the following error. What should I do? I use the latest version of R

install.packages("xlsx")
Installing package into ‘C:/Users/Cgdm/Documents/R/win-library/4.0’
(as ‘lib’ is unspecified)
trying URL 'https://cran.rstudio.com/bin/windows/contrib/4.0/xlsx_0.6.5.zip'
Content type 'application/zip' length 374910 bytes (366 KB)
downloaded 366 KB

package ‘xlsx’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\Cgdm\AppData\Local\Temp\RtmpCy2Eng\downloaded_packages


library(xlsx)
Error: package or namespace load failed for ‘xlsx’:
onLoad failed in loadNamespace() for 'rJava', details:
call: fun(libname, pkgname)
error: JAVA_HOME cannot be determined from the Registry

Answer 1

Common Problems -- Java and xlsx package

R users who have not previously used packages that rely on Java often have problems when attempting to use the xlsx package that is used to read Excel spreadsheets.

Java runtime not installed

First, many new R users have not previously needed to install a Java runtime on their computers. The xlsx package depends on the rJava and xlsxjars packages. rJava requires the Java Runtime Environment 1.2 or above to also be present on one's computer.

Solution 1: Use an excel reader package that doesn't require Java

PRO TIP: The easiest way to work around this problem is to use an R package that does not depend on Java, such as openxlsx or readxl.

For openxlsx, it's very easy.

  install.packages("openxlsx")
  library(openxlsx)
  # read the help file to identify the arguments needed to 
  # correctly read the file
  ?openxlsx
  theData <- read.xlsx(...)
  

The same process can be used for readxl.

  install.packages("readxl")
  library(readxl)
  # read the help file to identify the arguments needed to 
  # correctly read the file 
  ?readxl
  theData <- read_excel(...)
  

Solution 2: Install Java and required R packages

That said, for people who still want to use the xlsx package, there are workable solutions for Windows, Mac OSX, and Ubuntu Linux.

SOLUTION (Windows): Download and install the latest version of the Java Runtime Environment from Oracle. Note that if you are running the 64-bit version of R, you need to install the 64-bit version of the Java Runtime.

SOLUTION (Mac OSX): As of newer releases of Mac OSX, this has become more complicated. A specific set of commands needs to be followed after installing the Java Development Kit on the computer. These are documented on the rJava Issue 86 github page.

SOLUTION (Ubuntu): Use the Ubuntu Advanced Packaging Tool to install Java, then reconfigure Java in R.

  sudo apt-get install openjdk-8-jdk # openjdk-9-jdk has some installation issues
  sudo R CMD javareconf

Then in R / RStudio install the xlsx package.

  install.packages("xlsx")

32-bit vs. 64-bit Java in Windows

Another common problem people may encounter is an incompatibility between the version of the Java Runtime Environment that is installed on their computer and the version of R, either 32-bit or 64-bit.

For example, if one has installed the 64-bit version of R but has the 32-bit version of Java Runtime Environment installed, R will not have visibility to the Java Runtime Environment, generating the same "Java not installed error" as noted above.

SOLUTION: This problem can be resolved by either installing the 64-bit version of Java Runtime for Windows, or by changing the RStudio configuration to use the 32-bit version of R.

One can identify the version of Java that is installed, and whether it is 32 or 64-bit, by executing the following function within R / RStudio.

system("java -version")

...which on a Windows-based PC will return something like this.

Extracting Java settings from Windows registry

Another approach to validating the version of Java that is installed on a Windows-based machine is to use utils::readRegistry() (h/t Access Windows Registry inside R).

readRegistry("SOFTWARE\\JavaSoft\\Java Runtime Environment","HLM",maxdepth=3)

returns the following:

> readRegistry("SOFTWARE\\JavaSoft\\Java Runtime Environment","HLM",maxdepth=3)
$BrowserJavaVersion
[1] "11.261.2"

$CurrentVersion
[1] "1.8"

$`1.8`
$`1.8`$JavaHome
[1] "C:\\Program Files\\Java\\jre1.8.0_261"

$`1.8`$MicroVersion
[1] "0"

$`1.8`$RuntimeLib
[1] "C:\\Program Files\\Java\\jre1.8.0_261\\bin\\server\\jvm.dll"


$`1.8.0_261`
$`1.8.0_261`$JavaHome
[1] "C:\\Program Files\\Java\\jre1.8.0_261"

$`1.8.0_261`$MicroVersion
[1] "0"

$`1.8.0_261`$RuntimeLib
[1] "C:\\Program Files\\Java\\jre1.8.0_261\\bin\\server\\jvm.dll"

$`1.8.0_261`$MSI
$`1.8.0_261`$MSI$AUTOUPDATECHECK
[1] "1"

$`1.8.0_261`$MSI$AUTOUPDATEDELAY
[1] ""

$`1.8.0_261`$MSI$EULA
[1] ""

$`1.8.0_261`$MSI$FROMVERSION
[1] "NA"

$`1.8.0_261`$MSI$FROMVERSIONFULL
[1] ""

$`1.8.0_261`$MSI$FullVersion
[1] "1.8.0_261-b12"

$`1.8.0_261`$MSI$INSTALLDIR
[1] "C:\\Program Files\\Java\\jre1.8.0_261\\"

$`1.8.0_261`$MSI$JAVAUPDATE
[1] "1"

$`1.8.0_261`$MSI$JU
[1] ""

$`1.8.0_261`$MSI$OEMUPDATE
[1] ""

$`1.8.0_261`$MSI$PRODUCTVERSION
[1] "8.0.2610.12"

This is a lot of output, but we can still see that the current version of Java is 1.8, and the JavaHome setting is C:\\Program Files\\Java\\jre1.8.0_261. Since the registry tree is relatively complicated, it takes a fair amount of knowledge to write the correct key to reduce the amount of output returned.

# more specific extract, given that $CurrentVersion is 1.8
readRegistry("SOFTWARE\\JavaSoft\\Java Runtime Environment\\1.8","HLM",maxdepth=3)

...returns a more targeted set of registry settings.

> readRegistry("SOFTWARE\\JavaSoft\\Java Runtime Environment\\1.8","HLM",maxdepth=3)
$JavaHome
[1] "C:\\Program Files\\Java\\jre1.8.0_261"

$MicroVersion
[1] "0"

$RuntimeLib
[1] "C:\\Program Files\\Java\\jre1.8.0_261\\bin\\server\\jvm.dll"

Java / R compatibility with non-English versions of Windows 10

Note that as of July 2020, users on Stackoverflow.com have reported problems installing Java and rJava in the scenario where the version of Windows is a non-English language version (e.g. Chinese, Polish, etc.). It appears that the way the Java installer works with these versions of Windows, R and the rJava package are not able to access the JAVA_HOME directory correctly.

To correct the problem, reinstall R with the same language used by Windows. That is, on the Chinese version of Windows, install R with Chinese langauge support. Once installed, you can change the language to English by setting language = "en" in the .Rconsole file.

Reference: Common Problems: Java and xlsx package, originally posted by me on my Johns Hopkins Data Science Specialization Community Mentor Repository, January 2017.

Answer 2

It seems that your JAVA_HOME is not configured correctly, hence from https://www.rdocumentation.org/packages/xlsx:

"Ensure that the system environment variable JAVA_HOME is configured appropriately and points to your jdk of choice. Typically, this will be included in your PATH environment variable as well. Options and system environmental variables that are available from R can be seen with Sys.getenv()."

Check your config by doing

d <- Sys.getenv()
d[names(d) == "JAVA_HOME"]

It should point to your Java installation, e.g. "C:\Program Files\Java\jdk1.8.0_104"

On Windows 10, this can be set in "Advanced System Settings, System Properties, Environment Variables"