Explicitly unlocked cells are still marked as locked in protection style

Issue #339 resolved
stringfellow
created an issue

Attached is a simple workbook with Sheet1!A1 'unlocked'.

The other cells are locked (this is the default for all cells, actually).

The sheet has been protected (without a password). The Style for A1 suggests it is locked, however, it is not:

In [18]: openpyxl.load_workbook('protection.xlsx')
Out[18]: <openpyxl.workbook.workbook.Workbook at 0x10e7d0b10>

In [19]: _18.get_sheet_by_name('Sheet1')
Out[19]: <Worksheet "Sheet1">

In [20]: _19['A1']
Out[20]: <Cell Sheet1.A1>

In [21]: _20.style.protection
Out[21]: Protection(locked=True)

Comments (13)

  1. CharlieC

    The cell is unlocked. Compare the values for A1 and A2. The problem is the way protection values are applied. If something is selected it means that the protection defaults do not apply for that cell. I agree it's confusing but that's how it works. Happy to accept suggestions for the documentation.

  2. stringfellow reporter

    But why when I open this in Excel (before putting it through OpenPyXL) is the cell selectable, editable, etc...? If you click the cell in Excel and look at protection it says it is 'unlocked'. Should the Style not read Proctetion(locked=False).

    I don't see any way you can explain this in the docs without it sounding illogical (at the moment), to be honest - I don't fully understand what you mean.

  3. stringfellow reporter

    Are you saying that a solution would be to do this on loading a spreadsheet from Excel:

    for column in ws.columns:
        for cell in column:
            if cell.style.protection.locked:
                cell.style = cell.style.copy(protection=Protection(locked=False)
    

    Incidentally, doing this results in the default which is no protection:

    In [10]: cell.style
    Out[10]: Style(font=Font(sz=12.0, u='none', color=Color(rgb=Value must be type 'basestring', indexed=Value must be type 'int', auto=Value must be type 'bool', theme=6, type='theme')), protection=Protection(locked=True))
    
    In [11]: cell.style = cell.style.copy(protection=openpyxl.styles.protection.Protection(locked=False))
    
    In [12]: cell.style
    Out[12]: Style(font=Font(sz=12.0, u='none', color=Color(rgb=Value must be type 'basestring', indexed=Value must be type 'int', auto=Value must be type 'bool', theme=6, type='theme')))
    

    Which is exactly the same as not having done anything to it in the first place ... which means it is impossible to unlock the cell!

  4. CharlieC

    The protection objects refer to the options shown when a sheet is protected, otherwise they have no effect. As cells are locked by default, flipping the bit by setting the value to True makes them unlocked. I'm sorry if that's about as clear as mud but that's the way it works.

    The best thing is to try this out in Excel: selecting options refer to what users can do with cells such as select unlocked cells. As cells are locked by default, ie. the value is missing or False, setting it to True unlocks them. The attribute names were chosen to reflect the way they are serialised. 18.8.33 in the specification. The documentation could be improved but it should also be remembered that none of the protection is really any protection at all.

  5. stringfellow reporter

    Ok, then the bug is that flipping the bit has no effect when you re-open the sheet in Excel.

    Here:

    In [1]: import openpyxl
    
    In [2]: openpyxl.load_workbook('protection.xlsx')
    Out[2]: <openpyxl.workbook.workbook.Workbook at 0x106668890>
    
    In [3]: _2.get_sheet_by_name('Sheet1')
    Out[3]: <Worksheet "Sheet1">
    
    In [4]: cell = _3['A1']
    
    In [5]: cell.style
    Out[5]: Style(font=Font(sz=12.0, u='none', color=Color(rgb=Value must be type 'basestring', indexed=Value must be type 'int', auto=Value must be type 'bool', theme=6, type='theme')), protection=Protection(locked=True))
    
    In [7]: _2.save('overprotected.xlsx')
    
    In [8]: openpyxl.load_workbook('overprotected.xlsx')
    Out[8]: <openpyxl.workbook.workbook.Workbook at 0x106026610>
    
    In [9]: _8.get_sheet_by_name('Sheet1')
    Out[9]: <Worksheet "Sheet1">
    
    In [10]: cell = _9['A1']
    
    In [11]: cell.style
    Out[11]: Style(font=Font(sz=12.0, u='none', color=Color(rgb=Value must be type 'basestring', indexed=Value must be type 'int', auto=Value must be type 'bool', theme=6, type='theme')), protection=Protection(locked=True))
    

    The output (bearing in mind this is unmodified) is not the same as the input when reading it in Excel, yet sure enough OpenPyXL loads the protection style from the self-written workbook. So who is right? Excel or OpenPyXL? If it is OpenPyXL, then please tell me how I can set a cell to be unprotected and that be true in Excel because I can't see any other options to do so.

  6. stringfellow reporter

    Here's what Excel says about the protection.xlsx (before it goes through OpenPyXL):

    Screen Shot 2014-07-09 at 15.08.33.png

    And here's it from overprotected.xlsx which in theory should be exactly what was read in originally (and crafted by Excel's own fair hands): Screen Shot 2014-07-09 at 15.08.54.png

    I can't see any way around this.

  7. Log in to comment