How to generate password protected multi-sheet Excel file in R? - Stack Overflow

I am trying to generate a password protected Excel file consisting of multiple sheets using R. I have tried different solutions, but none have been satisfactory.

1. The excel.link package allows password protection, but collapses my dataframes into a single sheet.
2. openxlsx allows saving multi-sheet files but does not provide password protection.

Below a reproducible example:

 library(excel.link)
 library(openxlsx)
    
 # Create some sample data
 df1 <- data.frame(A = 1:5, B = letters[1:5])
 df2 <- data.frame(X = 10:14, Y = LETTERS[10:14])
    
 # Create a multi-sheet Excel file 
 wb <- createWorkbook()
 addWorksheet(wb, "Sheet1")
 addWorksheet(wb, "Sheet2")
 writeData(wb, "Sheet1", df1)
 writeData(wb, "Sheet2", df2)
    
 # Save the file
 file_name <- "test.xlsx"
 saveWorkbook(wb, file_name,
 overwrite = TRUE) # saved multi-sheet file without password protection
    
 # Use excel.link to save a password protected file
 excel.link::xl.save.file(r.obj = list(df1 = df1, df2 = df2),
                          filename = "protected.xlsx",
                          password = "password") # saved single-sheet protected file

I am trying to generate a password protected Excel file consisting of multiple sheets using R. I have tried different solutions, but none have been satisfactory.

1. The excel.link package allows password protection, but collapses my dataframes into a single sheet.
2. openxlsx allows saving multi-sheet files but does not provide password protection.

Below a reproducible example:

 library(excel.link)
 library(openxlsx)
    
 # Create some sample data
 df1 <- data.frame(A = 1:5, B = letters[1:5])
 df2 <- data.frame(X = 10:14, Y = LETTERS[10:14])
    
 # Create a multi-sheet Excel file 
 wb <- createWorkbook()
 addWorksheet(wb, "Sheet1")
 addWorksheet(wb, "Sheet2")
 writeData(wb, "Sheet1", df1)
 writeData(wb, "Sheet2", df2)
    
 # Save the file
 file_name <- "test.xlsx"
 saveWorkbook(wb, file_name,
 overwrite = TRUE) # saved multi-sheet file without password protection
    
 # Use excel.link to save a password protected file
 excel.link::xl.save.file(r.obj = list(df1 = df1, df2 = df2),
                          filename = "protected.xlsx",
                          password = "password") # saved single-sheet protected file

• r
• excel
• password-protection

Share Improve this question Follow edited Feb 4 at 14:34 Ben Bolker 228k2626 gold badges400400 silver badges493493 bronze badges asked Feb 4 at 10:14 user29496084user29496084 111 bronze badge 2

• 1 A small but potentially important point: adding a password to a file does not (necessarily) encrypt the file. Though it might protect the file. – Limey Commented Feb 4 at 10:18
• Thank you very much, dear @Limey. I have amended the question. – user29496084 Commented Feb 4 at 10:21

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Here is a solution (for Windows) using inspiration from the officerWinTools package.

First Define a Custom Function

This function writes, executes, and removes a temporary VBScript (.vbs) file that:

1. Opens your workbook in a new instance of Excel.
2. Encrypts the workbook with a password by seting the Workbook.Password property as desired.
3. Overwrites your workbook.
4. Closes your workbook.

protect_workbook <- function(wb, pass) {
  
  if(Sys.info()['sysname'] != "Windows"){
    stop('The this function requires Windows operating system.')
  }
  
  wb_path <- file.path(getwd(), wb)
  vbs_path <- file.path(getwd(), "tmp.vbs")

  writeLines(
    text = c(
      'Set objExcel = CreateObject("Excel.Application")',
      'objExcel.Visible = False',
      'objExcel.DisplayAlerts = False',
      sprintf('Set wb = objExcel.Workbooks.Open("%s")', normalizePath(wb_path)),
      'wb.Saved = False',
      sprintf('wb.Password = "%s"', pass),
      'wb.Save',
      'wb.Close (TRUE)',
      'objExcel.Quit'
    ),
    con = vbs_path
  )

  shell(shQuote(normalizePath(vbs_path)), "cscript", flag = "//nologo")

  invisible(file.remove(vbs_path))
}

Then Do As Before

But this time use your new custom function protect_workbook() instead of excel.link::xl.save.file().

library(openxlsx)
    
# Create some sample data
df1 <- data.frame(A = 1:5, B = letters[1:5])
df2 <- data.frame(X = 10:14, Y = LETTERS[10:14])
    
# Create a multi-sheet Excel file 
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
writeData(wb, "Sheet1", df1)
addWorksheet(wb, "Sheet2")
writeData(wb, "Sheet2", df2)
    
# Save the file
file_name <- "test.xlsx"
saveWorkbook(wb, "test.xlsx")

# protect the workbook
protect_workbook(file_name, "opensesame")

_{Reprex files hosted with on GitHub}