Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Issues using xlsx package to insert data from R to excel

Tags:

r

excel

xlsx

rjava

I´m trying to create a new excel workbook from R to save a few small datasets using xlsx package. For some reason it was working fine, but i´m unable to do it again.

Code to create a new workbook

library("xlsx")
library("xlsxjars")
library("rJava")

file <- "marca_imei.xlsx"
wb <- loadWorkbook(file)

# The error:
# Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
#  java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

I´ve searched for an answer but it seems people are having the same error when importing data from excel. I´ve tried what was recommended but it didn´t work. Here are some links for future searchers:

  • http://r.789695.n4.nabble.com/Read-shortcuts-of-MS-Excel-files-through-R-td4677020.html
  • http://r.789695.n4.nabble.com/Problem-with-xlsx-package-td3298470.html

sessionInfo():

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

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

other attached packages:
 [1] xlsx_0.5.5             xlsxjars_0.6.0         RJDBC_0.2-3            rJava_0.9-6           
 [5] DBI_0.2-7              slidifyLibraries_0.3.1 slidify_0.4            knitr_1.5             
 [9] devtools_1.4.1         scales_0.2.3           ggplot2_0.9.3.1        data.table_1.8.11     
[13] reshape2_1.2.2        

loaded via a namespace (and not attached):
 [1] colorspace_1.2-4   dichromat_2.0-0    digest_0.6.4       evaluate_0.5.1     formatR_0.10      
 [6] grid_3.0.2         gtable_0.1.2       httr_0.2           labeling_0.2       markdown_0.6.3    
[11] MASS_7.3-29        memoise_0.1        munsell_0.4.2      parallel_3.0.2     plyr_1.8          
[16] proto_0.3-10       RColorBrewer_1.0-5 RCurl_1.95-4.1     stringr_0.6.2      tools_3.0.2       
[21] whisker_0.3-2      yaml_2.1.10     
like image 518
marbel Avatar asked Feb 27 '14 19:02

marbel


People also ask

Can you use xlsx in R?

xlsx package is one of the powerful R packages to read, write and format Excel files. It is a java-based solution and it is available for Windows, Mac and Linux. It works for both Excel 2007 and Excel 97/2000/XP/2003 file formats (xls and xlsx file formats).

How do I get an xlsx package in R?

To install a package in R environment, we can execute the below command on the console of R: > install. packages("xlsx")

Can RStudio read xlsx?

To ease this task, the RStudio IDE includes new features to import data from: csv, xls, xlsx, sav, dta, por, sas and stata files.


1 Answers

Martin,

I believe the issue is that the file you are reading in is not a valid .xlsx file. Here is a code example to reproduce your problem. You can also modify the example to solve the problem. The example uses an example data set from the web (Speed Camera locations baltimore :-)).

In essence line 16 is the culprit of the error triggered on line 26 that generates the error you see.

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
`java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

to reproduce the error download the file "rows.csv", when you invoke read.xlsx on line 26 it triggers the error you see. To fix change line 16 to download "rows.xlsx" and rerun the script below:

#!/usr/bin/env Rscript

# Ensure Clean Setup...
# Unload packages
if (require(xlsx)) {
        detach("package:xlsx", unload=TRUE)
}
if (require(xlsxjars)) {
        detach("package:xlsxjars", unload=TRUE)
}
# Delete Environment...
rm(list = ls())

# Delete directory
if (file.exists("data")) {
        unlink("./data", recursive = TRUE)
}

# OK - we should be in a base state setup test...

if (!require(xlsx)) {
        install.packages("xlsx")
}

if (!file.exists("data")) {
        dir.create("data")
}

# Download the file as a CSV file (Deliberate mistake) not a XLSX file
# This causes the error seen when read.xlsx is invoked...
# To fix replace rows.csv with rows.xlsx

if (!file.exists("data/cameras.xlsx")) {
        fileUrl <- "https://data.baltimorecity.gov/api/views/dz54-2aru/rows.csv?accessType=DOWNLOAD"
        download.file(fileUrl, destfile = "./data/cameras.xlsx", method = "curl")
}

list.files("./data")

# Now we check the file exists and read in the data...
# read.xlsx will throw the java error as the file downloaded is not a valid excel file...

if (!file.exists(".data/cameraData.xlsx")) {
        cameraData.xlsx <- read.xlsx("./data/cameras.xlsx", sheetIndex=1, header = TRUE)
}

head(cameraData.xlsx)

Here is the example output:

  1. Load rows.csv...

    source('test.R') Loading required package: xlsx Loading required package: xlsxjars % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0 0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0100 9294 100 9294 0 0 33870 0 --:--:-- --:--:-- --:--:-- 33796 Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, : java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

    now we replace rows.csv with rows.xlsx...

> source('test.R', echo=TRUE)

> #!/usr/bin/env Rscript
> 
> # Ensure Clean Setup...
> # Unload packages
> if (require(xlsx)) {
+         detach("package:xlsx", unload=TRUE)
+ }

> if (require(xlsxjars)) {
+         detach("package:xlsxjars", unload=TRUE)
+ }

> # Delete Environment...
> rm(list = ls())

> # Delete directory
> if (file.exists("data")) {
+         unlink("./data", recursive = TRUE)
+ }

> # OK - we should be in a base state setup test...
> 
> if (!require(xlsx)) {
+         install.packages("xlsx")
+ }
Loading required package: xlsx
Loading required package: xlsxjars

> if (!file.exists("data")) {
+         dir.create("data")
+ }

> # Download the file as a CSV file (Deliberate mistake) not a XLSX file
> # This causes the error seen when read.xlsx is invoked...
> # To fix replac .... [TRUNCATED] 
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0100  9923  100  9923    0     0  48559      0 --:--:-- --:--:-- --:--:-- 48642

> list.files("./data")
[1] "cameras.xlsx"

> # Now we check the file exists and read in the data...
> # read.xlsx will throw the java error as the file downloaded is not a valid excel file...
> .... [TRUNCATED] 

> head(cameraData.xlsx)
                         address direction      street  crossStreet               intersection                      Location.1
1       S CATON AVE & BENSON AVE       N/B   Caton Ave   Benson Ave     Caton Ave & Benson Ave (39.2693779962, -76.6688185297)
2       S CATON AVE & BENSON AVE       S/B   Caton Ave   Benson Ave     Caton Ave & Benson Ave (39.2693157898, -76.6689698176)
3 WILKENS AVE & PINE HEIGHTS AVE       E/B Wilkens Ave Pine Heights Wilkens Ave & Pine Heights  (39.2720252302, -76.676960806)
4        THE ALAMEDA & E 33RD ST       S/B The Alameda      33rd St     The Alameda  & 33rd St (39.3285013141, -76.5953545714)
5        E 33RD ST & THE ALAMEDA       E/B      E 33rd  The Alameda      E 33rd  & The Alameda (39.3283410623, -76.5953594625)
6        ERDMAN AVE & N MACON ST       E/B      Erdman     Macon St         Erdman  & Macon St (39.3068045671, -76.5593167803)
> 
like image 95
Technophobe01 Avatar answered Sep 28 '22 00:09

Technophobe01