pythonexcelpywin32

Unable to set any property when protecting an excel worksheet using pywin32

I am using pywin32 for a project to automate a few excel files using python.

In the excel file, I want to protect all the cells that contain a formula.
So, I first unlock all the cells and then only lock those cells which has a formula.
When I protect the worksheet with a password, I also pass all the relevant protection properties such as;
AllowFormattingCells,
AllowFormattingColumns,
AllowFormattingRows,
AllowInsertingColumns,
AllowInsertingRows,
AllowSorting and
AllowFiltering
and set them to True. However, if I check what the properties are after I have protected the worksheet, they return as False.

When I open the file using Excel, the sheet is being protected and I am able to edit the contents of the unlocked cells but I am unable to format, filter, insert columns or rows or do anything else.

Relevant Information

Below is the python code for reproducibility

import win32com.client


excel_app = win32com.client.DispatchEx("Excel.Application")
excel_app.Visible = False
workbook = excel_app.Workbooks.Open("path_to_file.xlsx")

sheet = workbook.Sheets("Sheet1")

all_cells = sheet.Cells
merge_cells = sheet.Cells(1, 1).MergeArea
edited_cell = merge_cells.Cells(1, 1)
value = edited_cell.Formula if edited_cell.HasFormula else edited_cell.Value

edited_cell.Formula = "=1+1"

formula_cells = all_cells.SpecialCells(Type=-4123)  # -4213 represent xlCellTypeFormulas

all_cells.Locked = False
formula_cells.Locked = True

if isinstance(value, str) and value.startswith("="):
    edited_cell.Formula = value
else:
    edited_cell.Value = value
    merge_cells.Locked = False


sheet.Protect(
    Password="random_password",
    Contents=True,
    UserInterfaceOnly=True,
    AllowFormattingCells=True,
    AllowFormattingColumns=True,
    AllowFormattingRows=True,
    AllowInsertingColumns=True,
    AllowInsertingRows=True,
    AllowSorting=True,
    AllowFiltering=True,
)

print("AllowFormattingCells: ", sheet.Protection.AllowFormattingCells)
print("AllowFormattingColumns: ", sheet.Protection.AllowFormattingColumns)
print("AllowFormattingRows: ", sheet.Protection.AllowFormattingRows)
print("AllowInsertingColumns: ", sheet.Protection.AllowInsertingColumns)
print("AllowInsertingRows: ", sheet.Protection.AllowInsertingRows)
print("AllowSorting: ", sheet.Protection.AllowSorting)
print("AllowFiltering: ", sheet.Protection.AllowFiltering)

workbook.Save()

excel_app.Quit()

When I manually set the protection while using Excel, the protection is working as expected. However, when I am setting it using pywin32, protection is working, but the protections properties are not set, hence I am unable to add rows, use filters, etc.

I've tried all combinations for the 'Contents' and the 'UserInterfaceOnly' to see if that may cause it to change.

Solution

  • I tested originally on a Windows 10 PC with Excel 2013 and as stated your code worked.
    I then tried a Windows 10 PC with Excel 2021 and it appeared to exhibit the same issue as you, the protections remained as False after being set to True. But another run later and it all worked fine on that Excel version. I tested with older versions of pywin32 306, 307 and then upgraded to 308.

    Anyway have applied the same protections using Xlwings (which also uses the Excel App and is similar to win32com) it works on both my systems so you can try it with your set up, assuming there is no issues using Xlwings.

    import xlwings as xw
from xlwings import constants


in_excelfile = 'unprotected.xlsx'
out_excelfile = 'protected.xlsx'

with xw.App(visible=True) as xl:
    wb = xw.Book(path_to_excel)
    ws = wb.sheets["Sheet1"]

    # Get range of used cells, this is the range of cells in the sheet with values  
    sheet_range = ws.used_range
    # Set all cells in the sheet_range Locked status to False
    ws.range(sheet_range).api.Locked = False
    # Select all cells with formulas in the sheet_range and set Locked to True
    ws.range(sheet_range).api.SpecialCells(constants.CellType.xlCellTypeFormulas, 23).Locked = True

    # Set the protection states
    ws.api.Protect(
        Password='random_password',
        Contents=True,
        UserInterfaceOnly=True,
        AllowFormattingCells=True,
        AllowFormattingColumns=True,
        AllowFormattingRows=True,
        AllowInsertingColumns=True,
        AllowInsertingRows=True,
        AllowSorting=True,
        AllowFiltering=True,
    )

    # Print the Status of the Protections 
    print(f"AllowFormattingCells: {ws.api.Protection.AllowFormattingCells}")
    print(f"AllowFormattingColumns: {ws.api.Protection.AllowFormattingColumns}")
    print(f"AllowFormattingRows: {ws.api.Protection.AllowFormattingRows}")
    print(f"AllowInsertingColumns: {ws.api.Protection.AllowInsertingColumns}")
    print(f"AllowInsertingRows: {ws.api.Protection.AllowInsertingRows}")
    print(f"AllowSorting: {ws.api.Protection.AllowSorting}")
    print(f"AllowFiltering: {ws.api.Protection.AllowFiltering}")

    # Save file
    wb.save(out_excelfile)

    If Xlwings doesn't work for you there is also Openpyxl, which doesn't use the Excel App but rather edits the underlying XML files that make up the XLSX files, that might do the trick.