saveWorkbook() as read only, potentially with a password to open with write capabilities?

[DavisVaughan]

Hello! Great work with openxlsx.

I have a workbook that is read only for general purpose use. It is updated daily with new data, which I use openxlsx to do. When I save it again, I would like to re-save it as read only, but saveWorkbook() does not allow me to do that. Ideally, I would be able to also include a password that opens it for write capabilities.

Do you think this is possible? I'm not sure if (or where) that kind of thing is stored in the underlying XML.

[kainhofer]

Depending on what you have in mind exactly, my latest pull request (https://github.com/awalker89/openxlsx/pull/411) might implement what you want. In that patch I implement worksheet and workbook protection (including a password to disable the lock). See also bug #410

I'm not implementing any functionality to automatically open a file read-only. These locks simply prevent users from making certain modifications to the read-write-opened workbook.

[DavisVaughan]

This is awesome! I tried out protectWorksheet() and it worked seamlessly. It would be REALLY powerful in combination with cell/range level protection. You could essentially use R to create Excel templates for data entry that are incredibly locked down to limit data entry issues.

I guess the cell level protection goes through the Allow Users to Edit Ranges menu (excel 2010)? There are all kinds of options there.

• Add multiple unlocked ranges
• Some ranges require a password to edit separate from the locked sheet password
• Some ranges can be set to be editable without a password for certain users

Were you planning on fixing #409 too? Would locking down cells automatically include a call to protectWorksheet()? Or would that be a separate call by the user? In theory you can lock cells and have the lock apply only when you protect the worksheet, so my guess is that they should be separate, with examples in the docs explaining that you need to call both to enforce the protection.

[DavisVaughan]

@kainhofer I've implemented protectRange() to add range-level protection. It also checks to see if a range protection currently exists so multiple can be added appropriately. Thoughts?

https://github.com/DavisVaughan/openxlsx/commits/bug409_PreserveRangeProtection

[kainhofer]

@DavisVaughan I'm currently working on #409, too, which is about the cell properties "hidden" and "locked". Internally, in the Excel file format, these are stored in the styles. I'm finished with that part and will submit a PR shortly. Setting the "locked" flag for the cell is just like checking the checkbox in Excel's cell properties dialog: It will just set the flag and will not have any further effect unless protectWorksheet is called.

Your approach with protectRange adds much more fine-grained access control. Both types of protection complement each other nicely.

[DavisVaughan]

Oh I see now. Format Cells -> Protection. Yea that seems useful as well for those one-off cells! Also protectRange() doesn't hide cells, so that's great too. I think protectRange without a password, and on a single cell, is essentially what "locked" does.

[kbzsl]

I would like to have a possibility to save the file with the “Read-only recommended “option set, which will trigger the “Open as Read Only” prompt when the document is opened. The goal is to avoid unintentional editing of the files but enable the editing on purpose. Can you add this option, too? Excel 2016 & 2013: Force “Open as Read Only” Prompt