Import password-protected xlsx workbook into R

Question

How can I import a worksheet from a password-protected xlsx workbook into R?

I would like to be able to convert an Excel worksheet into a csv file without having to go through Excel itself.

It is possible for xls workbooks using the perl-based function xls2csv from package gdata. I gather that the problem is Spreadsheet::XLSX doesn't support it.

There are a variety of functions and packages for importing non-encrypted xlsx workbooks, but none seems to address this issue.

At present it seems the only alternatives are to go through Excel or figure out how to write perl code that can do it.

Levenson

Answer 1

It looks to be what you need except it isn't with the xlsx package:

https://stat.ethz.ch/pipermail/r-help/2011-March/273678.html

library(RDCOMClient)
eApp <-  COMCreate("Excel.Application")
wk <-  eApp$Workbooks()$Open(Filename="your_file",Password="your_password")
tf <-  tempfile()
wk$Sheets(1)$SaveAs(tf, 3)

Hyperopia

Answer 2

To build on ed82's answer, there are a few caveats:

1. You may need to pass another password parameter, WriteResPassword. See docs here

2. I didn't find learning COM interface appealing after I got used to xlsx R package. So I would rather save a copy of the protected Excel file without a password immediately, close it, and read it in with another package:

eApp <- COMCreate("Excel.Application")

# Find out whether you need to pass **Password** or **WriteResPassword**
wk <- eApp$Workbooks()$Open(Filename= filename, Password="somepass",                             WriteResPassword = "somepass")

# Save a copy, clear the password (otherwise copy is still pass-protected)
wk$SaveAs(Filename = '...somepath...', WriteResPassword = '', Password = '')

# The copied file is still open by COM, so close it
wk$Close(SaveChanges = F)

# Now read into data.frame using a familiar package {xlsx}
my.data <- raed.xlsx('...somepath...', sheetIndex = ...)

Orthoscope