Cell properties "protected" and "hidden" (formulas hidden!) are not preserved on loading/saving

kainhofer commented 6 years ago

I have a workbook where some cells are set to "protected" (can not be changed when worksheet protection is turned on), while others are not set to "protected" (input parameters, can be changed if when worksheet protection is turned on). Similarly, the "hidden" cell attribute (formula is not visible when worksheet is protected) is set for some cells.

Expected Behavior

Loading such a file with loadWorkbook and saving it with saveWorkbook should preserve the protected and hidden cell attributes.

Actual Behavior

Loading such a file with loadWorkbook and saving it with saveWorkbook results in all cells reverting to the default value of "protected" turned on and "hidden" turned off.

Steps to Reproduce the Problem

Example file: 2018-08-16_CellProperties_ProtectedHidden_Lost.xlsx

library(openxlsx) wb = loadWorkbook("2018-08-16_CellProperties_ProtectedHidden_Lost.xlsx") saveWorkbook(wb, "2018-08-16_CellProperties_ProtectedHidden_Lost.saved.xlsx")

Cells A3, A4 and A5 have cell protection turned on, off and hidden turned on in the input file. The saved workbook 2018-08-16_CellProperties_ProtectedHidden_Lost.saved.xlsx does not retain these cell properties

sessionInfo()

Version of openxlsx: 4.1.1 (git master)
Version of R: 3.4.3 (2017-11-30)

kainhofer commented 6 years ago

FWIW, the cell-level protection (locked and hidden flag of each cell) is stored in the cell style. In particular, in xl/styles.xml:

<cellXfs count="3">
    <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
    <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0" applyProtection="1">
        <protection locked="0"/>
    </xf>
    <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0" applyProtection="1">
        <protection locked="0" hidden="1"/>
    </xf>
</cellXfs>

DavisVaughan commented 6 years ago

I was looking at the Allow Users to Edit Ranges section, and after setting A1 to be editable with a password after the entire sheet is locked, it looks like this in xl/worksheets/sheet1.xml, see protectedRanges:

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">
<dimension ref="A1"/>
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0"/>
</sheetViews>
<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
<sheetData/>
<sheetProtection password="8FA1" sheet="1" objects="1" scenarios="1"/>
<protectedRanges>
<protectedRange password="83AF" sqref="A1" name="Range1"/>
</protectedRanges>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<pageSetup paperSize="9" orientation="portrait" horizontalDpi="300" verticalDpi="300"/>
</worksheet>

Also, if you don't use https://github.com/jennybc/excelgesis, its pretty great for this stuff

kainhofer commented 6 years ago

PR #413 should implement the functionality of this report, of bug #410 (and more)

kainhofer commented 6 years ago

Fixed by PR #413 (commit 3fc002555cc4338c3e48164168326b0382ca3377)

@DavisVaughan Davis has a PR that adds much more fine-grained access control to individual cells and/or cell ranges. My fix simply adds the cell properties that apply to all users equally (but they are the easiest and most straight-forward way to allow some cells as input cells while protecting the rest of the document from inadvertent modifications)

awalker89 / openxlsx