Error in XLConnect

Question

I am trying to import an excel sheet into r. I used the following code:

x <- loadWorkbook("x.xlsx")
b <- readWorksheet(x, sheet="b")

The first line works fine, however, running the second gives the following error:

Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘readWorksheet’ for signature ‘"jobjRef", "character"’

I have no missing values in that sheet.

For the purpose of reproducing, download trial.xlsx from https://github.com/ahmedfsalhin/1stpaper.

system info: Yosemite operating system.

Monomer

It would be very helpful to post a link to an example workbook that causes this. That said, Yosemite is brand-new; you may need to reinstall Java and/or wait for new CRAN builds of Rjava . – Jemadar

@CarlWitthoft I have just updated the question with a link to excel file to reproduce. – Monomer

@CarlWitthoft I did update to Java Development Kit 8 (JDK 8) and after installing the XLConnect again, it works fine now. – Monomer

@CarlWitthoft Sorry it is not the Java. It is xlsx package that make the confusion. – Monomer

Answer 1

It appears the "root cause" is that you should add code to specify both the function and the package it belongs to. Type XLConnect::loadWorkbook to select the one you want in this case. There's no 'confusion,' or random selection of duplicated function names in R. The choice depends on the load order of all loaded packages. Use search() to see the order in which packages are checked for the command you've entered.

E.g., at present I get

search()
 [1] ".GlobalEnv"            "package:caTools"      
 [3] "package:XLConnect"     "package:XLConnectJars"
 [5] "package:stats"         "package:graphics"     
 [7] "package:datasets"      "package:vecsets"      
 [9] "package:cgwtools"      "package:grDevices"    
[11] "package:utils"         "package:methods"      
[13] "Autoloads"             "package:base"

You'll notice that anything in your environment (.GlobalEnv) is selected first, and that all loaded libraries override the base package, for example.

Jemadar

Answer 2

After lot of struggle found the solution to this. In R studio go to the packages and remove all the packages related to XLConnect and xlsx. Then instal only XLConnect packages by typing

install.packages("XLConnect", dependencies=TRUE)

After this the issue should not exist.

Whooper

It works for me in the situation when i tried to write numeric to cell using writeWorksheet and received the same error. – Tetrapody

Answer 3

I had the same problem while testing out three different packages for loading .xlsx files into R (XLConnect, xlsx, gdata). The solution was to specify the namespace for loadWorkbook:

d3_wb = XLConnect::loadWorkbook("Megadataset_v3.xlsx")
d3 = readWorksheet(d3_wb, 1)

Then it works, no matter if xlsx is loaded/installed or not. The error is because there is also a function with the same name in xlsx and it is defaulting to using the wrong one, which depends on the order the packages were loaded.

Dena

Answer 4

Try this instead x <- readWorksheetFromFile("x.xlsx") in XLConnenct package.

Tiepolo