CODEWITHSUNDEEP
pythonexcelpython-3.xjupyter-notebookxlwings
Randall Clintsman
Randall Clintsman

Reputation: 108

How to detect protected worksheet in excel workbook using Python xlwings?

I have a script running to update excel spreadsheets from our timesheet MongoDB database. Essentially, people are inputting their timesheets in my webapp and I update their excel spreadsheets via Python script.

However, when billing is done for a month, we protect that sheet. Sometimes, there's a cross over of the update being in the two week period in a protected sheet.

My issue is my current script wipes the data and rewrites it essentially in that two week time period. But on the protected sheet, it will keep attempting to write to it even though it protected and will not continue. It doesn't throw an error, and keeps trying to execute that line of code.

I tried implementing a timeout because the line doesn't throw an error and python can't catch it. However, python doesn't check the while statement constantly, so it will see the current time is initially less than the timeout, but never rechecks it and can't because it gets stuck on the line of code.

for j,user in enumerate(users):
    if user not in email_timesheet_dict:
        continue
    wb = xw.Book(email_timesheet_dict[user])
    app = xw.apps.active
    for sheet in sheets:
        time.sleep(1)                                 //What I've tried
        timeout = 30                                  //
        timeout_start = time.time()                   //
        print(time.time())                            //
        print(timeout_start)                          //
        print(timeout + timeout_start)                //
        while time.time() < timeout_start + timeout:  //What I've tried
            sht =wb.sheets[sheet]
            ...
            ...

This is the line of code it gets stuck on:

sht.range(f"{col}{row}").value = None

I'm wondering if there's a way to check if the sheet i'm editing is protected, and if so it will skip it and go to the next sheet. It's an older excel file (.xls), so I need it to be compatible.

Upvotes: 0

Views: 1526

Answers (1)

David Zemens
David Zemens

Reputation: 53663

python doesn't check the while statement

Because the application is hanging on something within that statement. Typically there is a dialog box that informing the user of the protection:

enter image description here

The Excel Application is unresponsive for as long as that dialog box is displayed. Your best bet is probably to ignore protected sheets. Untested, but explained in comments:

for sheet in sheets:
    sht = wb.sheets[sheet]
    alerts = wb.api.Application.DisplayAlerts  # get the current value of this property
    wb.api.Application.DisplayAlerts = False   # suppress the warning dialog which is freezing Excel Application
    try:
        sht.range(f'{col}{row}').value = None
    except:
        # if the range is not writeable, an error should be raised here.
        print(f'{sheet} is protected and can''t be written to...')
    finally:
        # return the application.DisplayAlerts to its previous value
        wb.api.Application.DisplayAlerts = alerts

Upvotes: 1

Related Questions