R Save a excel workbook with password protection

Question

I'm a bit impressed because I couldn't find an answer so far.

I have a small R script that is loading data from one source and writing a few dozens Excel files from it. The problem now is: those files need a password protection.

Currently my script is using the XLConnect library to save the excel files. The script is something like this:

# Load data from source
source = <connection string to source>
dataFromSource = <read from source>

# Process
for (data in dataFromSource) {
    wb = loadWorkbook( paste("myWorkbook", sourceName, ".xlsx"), create = TRUE)
    createSheet(wb, name = "sheet")
    <put data into the "sheet">
    saveWorkbook(wb)
}

So, making it simple: how do I create an Excel file with password protection in R?

Answer 1

You should specify whether you're looking to protect an entire workbook or a single sheet. If you're looking to password protect a single sheet, you can use the following rJava function (before saving the workbook):

rJava::.jcall(wb$getSheet("Sheet1"),"V","protectSheet", "MyPassword123")
xlsx::saveWorkbook(wb,"C:/myfilepath)

The ".jcall" function applies the password to the indicated sheet within the workbook. The "rJava" library must be installed and working properly for this to work.

Note

This function only works when using the XLSX package to manipulate Excel files in R. I worked with XLConnect to write the files initially, then, using XLSX, read them back in, apply password protection, and re-save the files to the same path.

Answer 2

Overview

After reading How do I password-protect an Excel file created in R with write.xlsx?, it seems versioning of certain packages is at play here.

This might be particular to someone's environment, but this solution just helped a colleague and I write a data.frame to a password protected .xlsx file:

# install necessary packages -----
# note: downloading the latest versions of all packages from CRAN
install.packages(c("XLConnect", "XLConnectJars", "rJava", "xlsx", "openxlsx"))

# attach necessary packages ----
library(XLConnect)

# load but do not attach the write.xlsx() function from the xlsx package ----
xlsx::write.xlsx(x = mtcars, file = "mtcars.xlsx", password = "1234_fghj")

# confirm that the .xlsx file is password protected ----
openxlsx::openXL("mtcars.xlsx")

# session info ----
sessionInfo()
# R version 3.5.1 (2018-07-02)
# Platform: x86_64-redhat-linux-gnu (64-bit)
# Running under: Red Hat Enterprise Linux
# 
# Matrix products: default
# BLAS/LAPACK: /usr/lib64/R/lib/libRblas.so
# 
# locale:
# [1] LC_CTYPE=en_US.UTF-8         
# [2] LC_NUMERIC=C                 
# [3] LC_TIME=en_US.UTF-8          
# [4] LC_COLLATE=en_US.UTF-8       
# [5] LC_MONETARY=en_US.UTF-8      
# [6] LC_MESSAGES=en_US.UTF-8      
# [7] LC_PAPER=en_US.UTF-8         
# [8] LC_NAME=en_US.UTF-8          
# [9] LC_ADDRESS=en_US.UTF-8       
# [10] LC_TELEPHONE=en_US.UTF-8     
# [11] LC_MEASUREMENT=en_US.UTF-8   
# [12] LC_IDENTIFICATION=en_US.UTF-8
# 
# attached base packages:
# [1] stats     graphics  grDevices utils     datasets 
# [6] methods   base     
# 
# other attached packages:
# [1] XLConnect_0.2-15     XLConnectJars_0.2-15
# 
# loaded via a namespace (and not attached):
# [1] compiler_3.5.1 tools_3.5.1    yaml_2.2.0    
# [4] xlsxjars_0.6.1 rJava_0.9-10   xlsx_0.6.1  

Answer 3

If you have access to a file archiver and can't install rJava, a possible workaround that also works with other output formats is to save the files as usual and then archive them with password protection.

E.g. using 7-zip:

setwd("c:/Program Files/7-Zip/")
pwd <- "123"
shell(paste0("7z a d:/Data/myfile.zip d:/Data/myfile.xlsx -p", pwd))