write.xlsx function not working

RafMil picture RafMil · Dec 22, 2017 · Viewed 43.8k times · Source

I am trying to use .xlsx library but function write.xlsx is returning error that such can not be found.

When I am installing library(xlsx) in log I can read:

Error : .onLoad nie powiodło się w funkcji 'loadNamespace()' dla pakietu 'rJava', szczegóły:
  wywołanie: fun(libname, pkgname)
  błąd:  No CurrentVersion entry in Software/JavaSoft registry! Try re-installing Java and make sure R and Java have matching architectures.
In addition: Warning messages:
1: pakiet ‘xlsx’ został zbudowany w wersji R 3.3.2 
2: pakiet ‘rJava’ został zbudowany w wersji R 3.3.3 
Error: pakiet ‘rJava’ nie mógł zostać załadowany

Java is up to date.

Answer

Len Greski picture Len Greski · Dec 22, 2017

The code in the original post fails because the xlsx package uses the Apache POI Java API to Excel, and therefore requires the rJava package. In turn, the rJava package requires a working, compatible version the Java Runtime Environment to be installed on the machine and accessible from R.

One can tell whether Java is accessible from R / RStudio via the system() function.

> system("java -version")
java version "13.0.2" 2020-01-14
Java(TM) SE Runtime Environment (build 13.0.2+8)
Java HotSpot(TM) 64-Bit Server VM (build 13.0.2+8, mixed mode, sharing)
> 

There are at least four sets of R packages used for working with Excel files, including:

  1. xlsx -- requires rJava package
  2. XLConnect -- requires rJava package
  3. openxlsx -- does not require rJava package
  4. readxl / writexl -- does not require rJava package

For options 3 and 4, the solution is simply to use install.packages() to install the desired package (as noted in another answer by @Linus), once you've updated R to the latest version.

install.packages("openxlsx") 
library(openxlsx)

or

install.packages(c("readxl","writexl")) 
library(readxl)
library(writexl)

A Working Example: Write to Excel File

library(writexl)
data <- data.frame(matrix(runif(100),nrow=10,ncol=10))
write_xlsx(data,"./data/simpleExcel.xlsx")

...and the output:

enter image description here

If You Must Use rJava...

Unfortunately, options 1 and 2 are considerably more complicated than "install Java." If one must use xlsx or needs the rJava package to support other R packages, installation of Java varies significantly by operating system.

Windows: one must install a version of Java whose architecture is compatible with R (i.e. 32-bit vs. 64-bit). One may consider installing both 32-bit and 64-bit versions because some Windows programs installed on the computer may require 32-bit Java vs. 64-bit. With RStudio, one can configure R to use the 32-bit version of R if only 32-bit Java is installed on the machine.

Mac OS X: one must install Java and run a series of commands that are documented on the rJava Issues GitHub page, including executing an R script to reconfigure Java for R.

Linux: one needs to install Java using the package installer tool appropriate for the version of Linux, and then configure R to use it. For example, in Ubuntu one would install with the advanced packaging tool.

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