check if sheet is protected when loading in read_only

[Enzo AGOSTA]

Hi all,

I'm writing a script which needs to go around any number of Excel files and create a report on what is present in the file, including defined names, as well as if a sheet is protected or not (among a lot of other things).

Unfortunately it looks like using read_only=True when loading a workbook removes this ability.

As I'm only trying to check what the file contains, I would prefer to use this mode to optimize performance.

Do you know if there is any way to check if a worksheet is protected while in read_only mode or if I just have to bite the bullet and stay in standard mode?

code snippets and error below:

import openpyxl as xl

Results = dict()

wb = xl.open('file', keep_vba=True, read_only=True)

Results['Global defined names'] = dict()

for name in wb.defined_names:

    Results['Global defined names'][name] = dict()

    Results['Global defined names'][name]['Name'] = wb.defined_names[name].name

    Results['Global defined names'][name]['Type'] = wb.defined_names[name].type

    Results['Global defined names'][name]['Value'] = (wb.defined_names[name].

                                                      value)

Results['Sheets'] = dict()

for ws in wb.worksheets:

    Results['Sheets'][ws.title] = dict()

    Results['Sheets'][ws.title]['Visibility'] = ws.sheet_state

    Results['Sheets'][ws.title]['Sheet defined names'] =\

        list(ws.defined_names.keys())

# Fails at the line below

    if ws.protection.sheet is True:

        Results['Sheets'][ws.title]['Protection'] = 'Protected'

    else:

        Results['Sheets'][ws.title]['Protection'] = 'Unlocked'

Traceback (most recent call last):
  File "Excel_Preflight.py", line 23, in <module>
    if ws.protection.sheet is True:
       ^^^^^^^^^^^^^
AttributeError: 'ReadOnlyWorksheet' object has no attribute 'protection'

[Cuatro Jones]

From my experience with Openpyxl, the most likely cause of this problem is that "protected" doesn't exist as an attribute for the ReadOnly class because Openpyxl doesn't care if the sheet/workbook is already read-only. 

You will likely have to just interact with the sheets as standard workbooks. 

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/b9fb4b0f-da95-423e-90b2-e077710cc46en%40googlegroups.com.

[Enzo AGOSTA]

Hi Cuatro,

Thanks for the reply, that's unfortunately what I feared, oh well openpyxl is already plenty fast anyway, I'll just warn users that massive files might take a bit to analyze.

[Charlie Clark]

On 24 Jul 2023, at 14:07, Enzo AGOSTA wrote:

> Hi Cuatro,
>
> Thanks for the reply, that's unfortunately what I feared, oh well openpyxl
> is already plenty fast anyway, I'll just warn users that massive files
> might take a bit to analyze.

Yes, the feature was never implemented in read-only mode the reason is that the protection information is stored at the end of the XML so you would have to read all the cells to get at it, which would largely remove the advantage of read-only mode: it would be slightly faster and uses less memory, of course.

Not too long ago I added some tests that check implementation details:

https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.1/openpyxl/worksheet/tests/test_read_only.py#L222

You can use these to check whether a feature is available in particular implementation.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226