Uses the Python library msoffcrypto-tool to decrypt password protected Excel files (either .xlsx or .xlsb), which can then be read into R with e.g. the readxl or readxlsb R packages.
Install from GitHub with:
# install.packages("remotes")
remotes::install_github("epicentre-msf/rpxl")Next, make sure Python is installed on your system. You can use the reticulate R package for this, which will already be installed alongside rpxl.
reticulate::py_available(initialize = TRUE)If you don’t already have Python installed, it can be downloaded from
www.python.org/downloads, or installed directly from R using the
install_python() function in reticulate. See also Using Python with
the RStudio
IDE
for more information on configuring RStudio to use Python.
reticulate::install_python()Finally, use the rpxl::install_rpxl() function to install the required
Python dependencies.
library(rpxl)
install_rpxl()library(rpxl)Read password-protected .xlsx files with function rp_xlsx(), a wrapper
to read_xlsx() in the readxl package.
path_xlsx <- system.file("extdata", "xltest.xlsx", package = "rpxl")
df <- rp_xlsx(path_xlsx, password = "1234")Read password-protected .xlsb files with function rp_xlsb(), a wrapper
to read_xlsb() in the readxlsb
package. Note that read_xlsb() requires the worksheet to be explicitly
specified (either with argument ‘sheet’ or ‘range’), whereas
read_xlsx() defaults to sheet 1.
path_xlsb <- system.file("extdata", "xltest.xlsb", package = "rpxl")
df <- rp_xlsb(path_xlsb, password = "1234", sheet = 1)To decrypt a password-protected Excel file and save it under a different
filename (without immediately reading it into R), you can use the
function decrypt_wb(). The function returns the path to the decrypted
file, which defaults to a temporary file created with tempfile(), but
could alternatively be user-specified.
path_decrypted <- decrypt_wb(path_xlsx, password = "1234")The decrypted file can then be read into R in a separate step.
df <- readxl::read_xlsx(path_decrypted)You may want to explicitly delete the decrypted file afterwards, if its
contents are sensitive. Otherwise, if it’s a temporary file created with
tempfile(), it will be automatically deleted when the current R
session is closed.
file.remove(path_decrypted)