0

I am trying to insert an R dataframe from Rstudio to an already existing Excel sheet without overwriting the entire file. I already tried this solution, but the instruction loadWorkbook gives me this error:

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, : org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException

With this traceback: 

6.
stop(structure(list(message = "org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException", call = .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, .jcast(if (inherits(o, "jobjRef") || inherits(o, "jarrayRef")) o else cl, "java/lang/Object"), .jnew("java/lang/String", ...
5.
.jcheck(silent = FALSE)
4.
.jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, .jcast(if (inherits(o, "jobjRef") || inherits(o, "jarrayRef")) o else cl, "java/lang/Object"), .jnew("java/lang/String", method), j_p, j_pc, use.true.class = TRUE, evalString = simplify, ...
3.
.jrcall(x, name, ...)
2.
wbFactory$create(inputStream)
1.
loadWorkbook("Recon Metro Fuels JR B VN Ene 2019 V2.xlsx")

I am using the libraries:

  • pdftools, for extracting text from a PDF.
  • openxlsx& xlsx, for Excel manipulation.
  • dplyr, for the %>% operator.

For more information about the Excel, Size: 29MB, is in .xlsx file format.

Here is the code on how i'm doing it:

#load exel file
old_data<- readWorkbook("Recon Metro Fuels JR B VN Ene 2019 V2.xlsx", sheet = "Raw BAC VN CON FIREFOX");
#Combines the Excel sheet with the new updated sheet
new_data<-bind_rows(old_data, pdf_refined);

wb<- loadWorkbook("Recon Metro Fuels JR B VN Ene 2019 V2.xlsx")

What causes this error? Are there other ways to do it?

René Tarot
  • 87
  • 7
  • Which package are you using to handle excel files? The mentioning of java in the error makes me think it's `XLConnect` which is somewhat notorious for its java dependency... I eventually gave up on it and switched to `openxlsx`. Never looked back (except once where I had to deal with an '.xls' file...) – dario Feb 07 '20 at 23:15
  • @dario I am using openxlsx and xlsx, to handle my Excel file. I also updated the post with the libraries I am using. – René Tarot Feb 08 '20 at 00:44
  • then it must be 'xlsx'. I'd suggest avoiding it and using `openxlsx` only. Is there a reason you load both libraries 'xlsx' **and** 'openxlsx'? – dario Feb 08 '20 at 00:54
  • @dario Thanks, I do not remember why I'd used that library, removing it resolved the error. But Rsutdio reported another error, I don't know if I should update the question or write the problem in the comments. – René Tarot Feb 09 '20 at 00:23

1 Answers1

0

The problem was a conflict with the libraries:openxlsx & xlsx.

So in the end I Removed xlsx.

René Tarot
  • 87
  • 7