Reading a password-protected xlsx-file into R without installing Java (password is known)

Question

currently I am preparing an R-lesson for a customer. We want to read in data from a password protected xlsx file. Thus, I am trying to read this password protected xlsx file into R (unprotecting the file itself is no solution). Unfortunately I cannot use ways that include installing additional software, such as Java.

Neither openxlsx, readr nor readxl seem to support using passwords when reading in excel files.

There are two packages which seem to support this: xlsx and excel.link I have tried the xlsx package but it requires Java. So does XLConnect

I have tried the excel.link package but it just crashes ("R session aborted") everytime I try to open the file. So does RDCOMClient.

Stackoverflow postings that unfortunately were not suitable answers to my problem:

How do you read a password protected excel file into r?

How to read an .xls file that is encrypted with R?

Import password-protected xlsx workbook into R

Reading Any kind of password protected file into R

How to read an .xls file that is encrypted with R?

After encountering these problems I just updated all my packages. Here is info on my R-version.

 version
               _                           
platform       x86_64-w64-mingw32          
arch           x86_64                      
os             mingw32                     
system         x86_64, mingw32             
status                                     
major          4                           
minor          0.3                         
year           2020                        
month          10                          
day            10                          
svn rev        79318                       
language       R                           
version.string R version 4.0.3 (2020-10-10)
nickname       Bunny-Wunnies Freak Out     

Do you know any other packages that fit my needs? Do you alternatively have any clue why both excel.link and RODCOMClient seem not to work at all?

Thank you very much for any suggestions!

Toothsome

You can't open the file in Excel, then copy/paste the data into a new file? – Forgetful

Sorry in case I did not express myself clearly: I do have the password and I am able to undo the password protection. But part of the solution needs to be that the original file remains password-protected at all times (we may ask whether or not it makes sense, but that is the way I need the solution anyway). – Toothsome

The discussion a the (closed, not implemented) feature request for readxl is interesting - because Excel encrypts the files, it seems like there are very limited ways to get it. Either a C++ v 11 package from MS, or having Excel do it (maybe the approach that the Java-requiring packages use...). – Spud

I'd love to be wrong, but it sounds like at this point there are ways to read password-protected Excel files into R that rely on Java, and there are ways to read Excel files into R without Java, but not both. – Spud

Answer 1

I have been able to open a password protected Excel file with RDCOMClient with the following code :

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
xlWbk1 <- xlApp$Workbooks()$Open("C:\\..\\Test.xlsx", Password = "my_Password")
xlWbk1$sheets(1)$cells(8,3)$value()

Carollcarolle

Answer 2

Hope it's not too late to share an answer. I tried the below and it worked

library(xlsx)

read.xlsx2("path/to/your/password_protected_file.xlsx", sheetIndex = 1, password = "password")

Gonsalves