protectWorkbook & protectWorksheet do not work

[wphampton]

Describe the bug Code runs successfully but it is not password protected, and can open the file directly

To Reproduce

df = data.frame(Year = c(2018,2019,2020,2018,2019,2020,2018,2019,2020),
                Country = c("Germany","Germany","Germany", "Japan", "Japan", "Japan",  "Thailand", "Thailand", "Thailand"), 
                Count = c(17, 15, 60, 23, 25, 60, 50, 18, 31))

wb = createWorkbook()
addWorksheet(wb, "Master")
writeDataTable(wb, "Master", df, tableStyle = "TableStyleLight9")
yellow_style = createStyle(fgFill = "#FFFF00")
y = which(colnames(df) == "Count")
x = which(abs(df$Count) > 32)
addStyle(wb, sheet = "Master", style = yellow_style, rows = x+1, col = y)
protectWorkbook(wb, protect = TRUE, password = "Password", lockStructure = TRUE)
saveWorkbook(wb, "Master.xlsx", overwrite = TRUE)

Expected behavior protectWorkbook() is supposed to add password protection when trying to open Excel

Additional context This was originally posted at old repo: https://github.com/awalker89/openxlsx/issues/525

Running current versions of R and openxlsx at this time

openxlsx version 4.2.5
R version 4.1.3 (2022-03-10)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19042)

[JanMarvin]

Hi @wphampton , I guess you want protectWorksheet

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "main")
writeDataTable(wb, "main", head(iris), tableStyle = "TableStyleLight9")
# disables the ability to add or remove sheets from the workbook
protectWorkbook(wb, protect = TRUE, password = "Password", lockStructure = TRUE)
# locks the sheet against any modification, see link above for fine tuning
protectWorksheet(wb, "main", protect = TRUE, password = "Password")
tmp <- temp_xlsx()
saveWorkbook(wb, tmp, overwrite = TRUE)

openXL(tmp)

[wphampton]

Thank you for your response @JanMarvin. Maybe I am misunderstanding this feature. If this feature is working correctly will it prompt the user for a password before opening the worksheet? From the docs I interpreted that type=1 would password protect the spreadsheet from opening altogether. Thanks! -Wes

[JanMarvin]

Ah, you want the popup that the file is locked? Hm, I think that unfortunately that's not possible. It would require us to create and open encrypted zip files, thats not possible from R at the moment (maybe it would even need special support for Excel as well).

[wphampton]

Ok, I misunderstood. I was thinking this was setting the built-in MS Excel Password Protected features that Excel uses natively. Not .zip files specifically but the Excel features.

[JanMarvin]

Had a quick look to confirm: Microsoft uses something that does not look like a standard encrypted zip file. Looks like some custom zip file with embedded xml (from my test file seen in a hex editor).

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<encryption xmlns="http://schemas.microsoft.com/office/2006/encryption" xmlns:p="http://schemas.microsoft.com/office/2006/keyEncryptor/password" xmlns:c="http://schemas.microsoft.com/office/2006/keyEncryptor/certificate"><keyData saltSize="16" blockSize="16" keyBits="256" hashSize="64" cipherAlgorithm="AES" cipherChaining="ChainingModeCBC" hashAlgorithm="SHA512" saltValue="3iCDN6dWj4GmUosCVcUhOg=="/><dataIntegrity encryptedHmacKey="JYIbY7TAXsYmXXxblcKnInQ5r/af0Z2NWWiJXo3VXbzVtWKtnQHP7FCe9wv2SMQ2Qk2MGTLlhvrvt3DyIAy9oQ==" encryptedHmacValue="tWeGQDzpHDHvNdMf+vDSMo4LyjWTtzaAY3/t76NMXE3FKSktGZ9G8ck3/zYvCgMdHXtTu0/wXj9TJl6TNRU1hw=="/><keyEncryptors><keyEncryptor uri="http://schemas.microsoft.com/office/2006/keyEncryptor/password"><p:encryptedKey spinCount="100000" saltSize="16" blockSize="16" keyBits="256" hashSize="64" cipherAlgorithm="AES" cipherChaining="ChainingModeCBC" hashAlgorithm="SHA512" saltValue="VEJUl2Lp1cZLTFgp1V8a7g==" encryptedVerifierHashInput="n4+CsnegjGrgfQUzdEW0MA==" encryptedVerifierHashValue="fIC/uZSdE3aCi81xzOlaVc++0aY8yVgIneK3vjPZdTjuLcETrhVapqrTKwYyJshH5C3jBEsHOZQWv8evmBEqdA==" encryptedKeyValue="6WbP3YetKALV0qLrcHnaR6gtfhR9K/55w5oyf8gzMx0="/></keyEncryptor></keyEncryptors></encryption>

[JanMarvin]

Ok, I misunderstood. I was thinking this was setting the built-in MS Excel Password Protected features that Excel uses natively. Not .zip files specifically but the Excel features.

We only support modifications to the xml structure of the underlying xml files in the xlsx output. Our functions create xml snippets that the openxml 2.8 standard accepts. Encryption in Excel (and maybe LibreOffice) can make use of features to create encrypted (most likely zstd) compressed files. Most likely it's not even very hard to add such features (I've added something like this for SPSS files a long time ago), but that is currently out of our scope and as of today unlikely to happen anytime soon.

[JanMarvin]

@wphampton I've found a way to create the file is encrypted popup:

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "main")
writeDataTable(wb, "main", head(iris), tableStyle = "TableStyleLight9")

nams <- names(wb$workbook)

# function that uses the weaker password hashing we already provide, not the
# stronger sha-512!
# if readOnlyRecommended is TRUE after the password Excel tells that the author
# requested to open this file in read only mode unless changes are applied.
fileSharingPassword <- function(x, readOnlyRecommended = FALSE) {
  hashed_password <- openxlsx:::hashPassword(x)
  readonly <- ifelse(readOnlyRecommended, 'readOnlyRecommended="1"', '')
  sprintf('<fileSharing userName="Test" %s reservationPassword="%s"/>', readonly, hashed_password) 
}

# use Rstudio password promt to handle the password
wb$workbook$fileSharing <- fileSharingPassword(rstudioapi::askForPassword())

# order the workbook list in the expected order. if the list is not ordered,
# Excel will complain and is unable to open the workbook at all!
wb$workbook <- wb$workbook[c("fileSharing", nams)]

tmp <- temp_xlsx()
saveWorkbook(wb, tmp, overwrite = TRUE)

openXL(tmp)

[JanMarvin]

This will open the password prompt, the user can authenticate and if successful the file is unlocked, otherwise users can open the file in read only mode. Though this is not file encryption, even without a password the user is able to read the file and can for instance use tools like openxlsx to remove the password asking xml entry.

[wphampton]

I really appreciate your response, It sounds like this is actually a feature request then, due to my misunderstanding of what the current protectWorkbook() function is designed to do. I was hoping with openxlsx we could set the setting in Excel shown below. I'll give your method a try though, maybe I will stumble onto something. Thanks!

[JanMarvin]

Unfortunately not. I've found a single command line program claiming to be capable to read and write encrypted xlsx files. These files are similar to encrypted zip files, you can not look inside without the password. For now the best option is to create such encrypted xlsx files with Excel or Libreoffice.

[danmota83]

@wphampton I've found a way to create the file is encrypted popup:

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "main")
writeDataTable(wb, "main", head(iris), tableStyle = "TableStyleLight9")

nams <- names(wb$workbook)

# function that uses the weaker password hashing we already provide, not the
# stronger sha-512!
# if readOnlyRecommended is TRUE after the password Excel tells that the author
# requested to open this file in read only mode unless changes are applied.
fileSharingPassword <- function(x, readOnlyRecommended = FALSE) {
  hashed_password <- openxlsx:::hashPassword(x)
  readonly <- ifelse(readOnlyRecommended, 'readOnlyRecommended="1"', '')
  sprintf('<fileSharing userName="Test" %s reservationPassword="%s"/>', readonly, hashed_password) 
}

# use Rstudio password promt to handle the password
wb$workbook$fileSharing <- fileSharingPassword(rstudioapi::askForPassword())

# order the workbook list in the expected order. if the list is not ordered,
# Excel will complain and is unable to open the workbook at all!
wb$workbook <- wb$workbook[c("fileSharing", nams)]

tmp <- temp_xlsx()
saveWorkbook(wb, tmp, overwrite = TRUE)

openXL(tmp)

I tested this code and it work for me, whereas the protectWorkbook() function doesn´t. Shouldn´t you replace the function code for this one you sugested?

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.