Python openpyxl - want to protect a sheet but allow filtering, sorting on all cols, and allow editin-CodePudding

i have an excel file that I want to protect but also want to allow

filtering on all cols
sorting on all cols
EDITING of all cells in column "D" (preferably D2 to end (to preserve the heading in D1)

I have gotten this far.

this executes and works to a degree, in that the sheet is now protected.

But ... The filters "exist" but don't work (can't click on them) Sort doesn't work Column D is still not editable

Hoping for some guidance on this.

using python 3.9 and openpyxl 3.0.10

from openpyxl import load_workbook
from openpyxl.styles.protection import Protection

# open wb
wb = load_workbook("test2.xlsx")

# select sheet1
ws = wb["Sheet1"]

# protect sheet
ws.protection.enabled = True
ws.protection.password = 'password'

# allow filtering on all cols
ws.auto_filter.ref = "A1:H1"
ws.auto_filter.enable = True

# allow editing on all cells in col D
for row in ws.iter_rows():
    for cell in row:
        cell.protection = Protection(locked=(cell.column != "D"))

# save to new file
wb.save("test9.xlsx")

CodePudding user response：

I've added into your code the individual protections that can be set on the sheet. The state of each is set to what is expected when executing

ws.protection.enabled = True

except for those marked as changed.
Re-enabling the autofilter is easy enough, just set autoFilter back to False

.autoFilter = False

This allows the filters to be selected.
There is a sort setting but changing that back to False still does not enable sorting. The best I can determine is you cannot enable this. This from a MS help site;
You can't sort locked cells on a protected sheet. You have to either unlock the cells, or unprotect, sort, and then reprotect.

.sort = False

I also changed the loop to set column D lock protection off for row 2 to last used row. Editing column D works excluding the Header and cells below row max. If you want more rows in column D than the used range to be editable you can set a max_row in the iter_rows.

from openpyxl import load_workbook
from openpyxl.styles.protection import Protection

# open wb
wb = load_workbook("test2.xlsx")

# select sheet1
ws = wb["Sheet1"]

# allow editing on all cells in col D
for row in ws.iter_rows(min_row=2, min_col=4, max_col=4):
    for cell in row:
        cell.protection = Protection(locked=False)
        print(cell.protection.locked)

### protect sheet
ws.protection.enabled = True
ws.protection.password = 'password'

### All individual sheet protections set per 'protection.enabled = True'
### Except those marked as 'Changed'
prot = ws.protection
prot.selectLockedCells = False
prot.selectUnlockedCells = False
prot.algorithmName = None
prot.sheet = True
prot.objects = False
prot.insertRows = True
prot.insertHyperlinks = True
prot.autoFilter = False  # Changed
prot.scenarios = False
prot.formatColumns = True
prot.deleteColumns = True
prot.insertColumns = True
prot.pivotTables = True
prot.deleteRows = True
prot.formatCells = True
prot.saltValue = None
prot.formatRows = True
prot.sort = False  # Changed
prot.spinCount = None
prot.hashValue = None


# allow filtering on all cols
ws.auto_filter.ref = "A1:H1"
ws.auto_filter.enable = True

# save to new file
wb.save("test9.xlsx")