"Error in addWorksheet(wb, "sheet1") : First argument must be a Workbook"

Question

I am using ‘openxlsx’ package in R. ٰI want to add some data in xlsx file. I have used following code to create the workbook and add worksheet in it.

 wb=createWorkbook()
 addWorksheet(wb,"sheet 1")
 writeData(wb,sheet = 1,"From",startCol = 1,startRow = 1)
 writeData(wb,sheet = 1,"To",startCol = 2,startRow = 1)
 writeData(wb,sheet = 1,"From",startCol = 1,startRow = 2)
 writeData(wb,sheet = 1,"From",startCol = 1,startRow = 2)
 saveWorkbook(wb,"file.xlsx",overwrite = TRUE)

This code was working well for a long time, But recently, I am facing this error

Error in addWorksheet(wb, "sheet 1") : First argument must be a Workbook.

How this error will be resolved?

Rabblement

The code you have shared doesn't throw any error in my case. The error message you have shared indicates that the workbook wb was not created. Maybe you skipped executing line 1 of the code. – Fowling

I have not skipped that line. Actually, this was working well but recently failed. but don't know why? – Rabblement

What does str(wb) show? – Ulberto

Formal class 'jobjRef' [package "rJava"] with 2 slots ..@ jobj :<externalptr> ..@ jclass: chr "org/apache/poi/xssf/usermodel/XSSFWorkbook" – Rabblement

Answer 1

I was able to fix this error by disabling XLSX package

detach("package:xlsx", unload = TRUE)

Saloop

A related solution to this is to make sure that you're using the openxlsx package by calling openxlsx::createWorkbook() – Composure

Answer 2

I had the same problem and I just unloaded the package, reinstalled it and reloaded it, and it worked (without having to close R studio) :

detach("package:openxlsx", unload=TRUE)

install.packages("openxlsx")

library(openxlsx)

Bainite

Worked for me without reinstalling – Tombaugh

Answer 3

I had the same issue with this. I did the followings and it fixed the issue. Maybe it can solve yours.

• Close R or RStudio.
• Make sure that your current working directory does not have any other file or folder. In other words, the path in which you would like to save the xlsx is empty prior to running createWorkbook(). If you have already saved any file in there, just copy and paste it somewhere else.
• Run your code again from the beginning.

Lagrange

This worked for me, but it would be great to know whats causing the issue – Szabadka

Answer 4

Works for me unload it and re-install (or uninstall) library rio

detach("package:rio", unload=TRUE)
detach("package:openxlsx", unload=TRUE)

install.packages("openxlsx", "rio")

library(openxlsx)

Intonation

Answer 5

Encountered the same issue, removed library(xlsx) & library(readxl) from script and now runs without issue. Using the below libraries now in script for context:

library(openxlsx)
library(rio)
library(rJava)

International

Answer 6

I experienced a similar issue when I tried to re-run code to export an XLSX file. To have my code working well, I just made sure the workbook (wb) was removed from my global environment. I think inserting the following line right before your code may help.

  rm(list=deparse(substitute(wb)),envir=.GlobalEnv)

Milano