CODEWITHSUNDEEP
rpasswordsxlsx
Ben
Ben

Reputation: 1556

Using saveWorkbook in R - How do I add a password?

I sometimes create Excel spreadsheets in R using the xlsx package (Version 0.6.1), and I save these with the saveWorkbook function. I am trying to password-protect the Excel file by adding a password variable into the save statement as shown in the package documentation. This attempt leads me to the following error:

#The object WB is a workbook created with the xlsx pacakage

> saveWorkbook(WB, "MyFile.xlsx", password = "PASS");
Error in .jnew("org/apache/poi/poifs/crypt/EncryptionInfo", encMode) : 
  java.lang.NoSuchMethodError: <init>

I have no idea what this error means. Can anyone tell me what is wrong and how to fix this?

Upvotes: 1

Views: 1578

Answers (1)

lukeA
lukeA

Reputation: 54277

Maybe the method is missing in the xlsxjars companion. As as workaround, to set a password on Excel workbooks in R, you can use the RDCOMClient package e.g. like this: 

# devtools::install_github("omegahat/RDCOMClient")
set_excel_psw <- function(filename, password = rstudioapi::askForPassword()) {
  require(RDCOMClient)
  filename <- normalizePath(path.expand(filename))
  Application <- COMCreate("Excel.Application")
  wkb <- Application$Workbooks()$Open(filename) 
  wkb[['Password']] <- password
  wkb$Save()
  Application$Quit()
  Application <- NULL    
  invisible(gc())
}

xlsx::write.xlsx(iris, tf<-tempfile(fileext = ".xlsx"))
set_excel_psw(filename = tf, password = "foo")
shell.exec(tf)

The Password property is documented here.

Upvotes: 4

Related Questions