PermissionError [WinError 32]: File is being used by another process after opening excel file with xlwings

Question

Description

In order to trigger the calculation of some formula fields in excel and then read in their values using pandas, I have the following portion of a script that opens the workbook in excel, then saves and closes the workbook and attempts to delete the temporary file created when the workbook is opened:

def df_from_excel(path):
    """Automates the opening and saving of an excel workbook before reading it
    in through pd.read_excel, which addresses the NaN issue described in this
    post: https://stackoverflow.com/a/41730454/7338319
    """
    # open and save the wb to trigger formulas
    app = xl.App(visible=False)
    book = app.books.open(path)
    book.save()
    book.close()
    app.kill()
    # delete the temporary file that's created
    temp_path = path.parent / ("~$" + str(path.name))
    if temp_path.exists():
        temp_path.unlink()
    # read the excel into a dataframe
    return pd.read_excel(path, engine="openpyxl")

This script runs successfully on Mac but when I try to run it on Windows I get the following error which seems to be raised by temp_path.unlink()

self = WindowsPath('C:/Users/william.daly/Documents/GitHub/prompt_payment/tests/run/~$CoreIntegrator.xlsx')

    def unlink(self):
        """
        Remove this file or link.
        If the path is a directory, use rmdir() instead.
        """
        if self._closed:
            self._raise_closed()
>       self._accessor.unlink(self)
E       PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 'C:\\Users\\william.daly\\Documents\\GitHub\\prompt_payment\\tests\\run\\~$CoreIntegrator.xlsx'

env\lib\pathlib.py:1304: PermissionError

Additional Context

• I am attempting to run this script on a virtual desktop with Windows Server 2019 Standard
• I'm using python 3.7.7 and xlwings version 0.21.4
• The portion of the script that I'm trying to execute was mainly pulled from this answer: https://stackoverflow.com/a/41730454/7338319
• And I added book.close() based on the notes in this issue: PermissionError: [WinError 32] The process cannot access the file because it is being used by another process
• I can also no longer delete the file manually, presumably because of the same issue

Questions

• Is there a better/different way to force the close of the file?
• Is there a reason this error occurs on Windows but not on Mac?
• Is there a better/different way to trigger the calculations of formulas in excel without opening the workbook via the app, which is what is presumably causing this issue

Answer 1

macOS doesn't block files just because they are being used by another application, so you won't get an error even if you delete a file that may be still open in an application.

Windows on the other hand doesn't allow you to delete a file or folder as soon as it's open somewhere.

Now, in your case, Python wants to delete the file while Excel is still in the process of closing the file which is why you get the error.

Last time I ran into the issue, I solved it like this:

import time

for attempt in range(5):
    try:
        temp_path.unlink()
        break
    # you may want to explicitly check for PermissionError
    except Exception as e:
        time.sleep(1)

With regard to your last question: I don't know of any Python library that can evaluate formulas without Excel, but I believe in other languages such as C# such libraries exist.