Enable worksheet protection

[JDasHO]

Hi everyone, is there a way to enable protection of a worksheet? Right now what I'm trying to do is setting a cell to protected like so:

ws.cell("D4").protection(xlnt::protection::locked_and_visible());

However it doesn't take effect. When I open the so created xlsx file in Excel I can still edit the protected cell and under cell/formatting Excel also informs me that cell protection is only effective after I enabled sheet protection.

[doomlaur]

Is this what you are looking for? This should allow you to set a password, and should work as long as you are using the normal xlnt::workbook. This does not work for the xlnt::streaming_workbook_reader or xlnt::streaming_workbook_writer, though, as explained in #180.

Let me know if this does not work for some reason and I'll take a look at it :wink:

[JDasHO]

Hi doomlaur, thanks for your reply and sorry that it took me a while to test it. Eventhough the protection class looks promissing, I don't quite get my head around on how to apply it on my worksheet. I also couldn't find any place in the code where the sheet_protection class is getting used. Is it maybe possible that the actual attribute was never added to the worksheet class or am I overseeing something?

[m7913d]

It seems to me that this is indeed not implemented. I think tfussel#180 is not related to this issue. As this issue is about sheet protection (i.e. locking), the other issue is about full file encryption (to avoid reading any content if the encryption password is unknown).

@JDasHO Are you interested in creating a PR to add support for this sheet-protection feature? If so, please let me know if you need some help.

[JDasHO]

Ok, good to know. @m7913d I don't know if I'll find the time for this in the next time. If I do, how can I get some more insight in how the .xlsx-file as a whole is structured? Are there resources you can recommend as a starting point?

[m7913d]

You can download the full XLSX specification at https://ecma-international.org/publications-and-standards/standards/ecma-376/ (Part1):

• The downloaded zip contains a PDF with all information, especially section "18. SpreadsheetML Reference Material" may be usefull. Using the outline of this section allows you to navigate to all the relevant parts of the xlsx format.
• OfficeOpenXml-XMLSCHEMA-Strict.zip > sml.xsd contains the xsd specification of the xlsx file. This information is also available in the PDF in the annex A.2 SpreadsheetML.

The steps needed to implement this feature:

• For this feature "18.3.1.85 sheetProtection" of the worksheet contains the relevant information. We probably have to extend xlnt::sheet_protection to be able to store the needed sheetProtection information.
• xlnt::worksheet should get some getter and setter to retreive/apply the sheet protection to the worksheet, similar to the phonetic properties.
• Implement serialisation: see xlsx_consumer::read_worksheet_end and xlsx_consumer::read_worksheet_end.
• Implement unit tests (tests/worksheet/worksheet_test_suite.cpp). Same approach as test_phonetics can be used.