Having issues getting the style.protection to work with excel write (in version 1.6.1)

Issue #163 resolved
bog.gob
created an issue

cell style protection seems to work fine when reading a workbook but not when writing. Attached is a snippet of code used to test this

Comments (9)

  1. Luke Cyca

    I have not been able to get cell protection to work either.

    Note: cell protection in Excel is ineffective unless the sheet is also protected. By looking at the source code, openpyxl doesn't seem to currently support reading or writing sheet protection, so we're out of luck for now.

  2. CharlieC

    As @Luke Cyca says protection is a bit complicated but we do now support roundtripping. I've modified your sample script to use current (2.1) idioms

    from openpyxl import Workbook, load_workbook
    from openpyxl.styles import Protection, Style
    from openpyxl.cell import get_column_letter
    
    
    wb = Workbook()
    dest_filename = 'empty_book.xlsx'
    
    ws = wb.active
    ws.title = "range names"
    ws.protection.sheet = True
    pt = Protection(hidden=True, locked=True)
    style = Style(protection=pt)
    
    for col_idx in range(1, 40):
        col = get_column_letter(col_idx)
        for row in range(1, 600):
            _cell = ws.cell('%s%s'% (col, row))
            _cell.value = '%s%s' % (col, row)
            _cell.style = style
    
    ws.column_dimensions["A"].hidden = True
    
    wb.save(filename = dest_filename)
    
    from openpyxl import load_workbook
    wb = load_workbook("empty_book.xlsx")
    ws = wb.active
    ws['A1'].style.protection
    
  3. girl spider

    Am I missing something?

    I have Openpyxl 2.1.1 and I copied your code and ran, but get:

    Traceback (most recent call last):

    File "simpleWriter.py", line 24, in <module>

    wb.save(filename = dest_filename)
    

    File "/Library/Python/2.7/site-packages/openpyxl/workbook/workbook.py", line 281, in save

    save_workbook(self, filename)
    

    File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 211, in save_workbook

    writer.save(filename)
    

    File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 194, in save

    self.write_data(archive)
    

    File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 106, in write_data

    self._write_worksheets(archive)
    

    File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 131, in _write_worksheets

    write_worksheet(sheet, self.workbook.shared_strings,
    

    File "/Library/Python/2.7/site-packages/openpyxl/writer/worksheet.py", line 283, in write_worksheet

    if worksheet.protection.sheet:
    

    AttributeError: 'bool' object has no attribute 'sheet'

    What's going on?

  4. girl spider

    Thanks. That works.

    I still have trouble getting individual cells to hide, not a whole row or column. This example doesn't address that. Do you have an example where particular cells can be hidden?

  5. Log in to comment