How to ensure xlwings connection is closed if script fails

Question

I am trying to develop something with xlwings because I need to manipulate a xls file with macros etc. Although it is always good to close connections, Excel is notorious in that it blocks access if more than one instance is running. Therefore I need to make sure that the app closes even though my code fails somewhere upstream.

I am currently doing this with a try statement that spans the whole script and when it fails calls app.quit(). But this suppresses my error messages, which makes debugging hard. So I feel there must be something better.

In another context I have seen with being used. And I have the feeling it would apply here too, but I do not understand how it works, nor how it would work in this specific case.

import xlwings as xw

def myexcel():
    try:
        #connect to Excel app in the background
        excel = xw.App(visible=False)
        # open excel book
        wb = excel.books.open(str(file))
        # asign the active app so it can be closed later
        app = xw.apps.active

        # more code goes here 

    except:
        app.quit()

How could one make sure that the excel connection gets always closed no-matter the most efficient way? If with is the solution, I would also appreciate a pointer to a good source to learn more about that concept.

Answer 1

As you mentioned, you can use a with statement and build your own contextmanager. Here's a converted example based on your code:

import xlwings as xw

class MyExcelApp:
    def __init__(self):
        self.excel = xw.App(visible=False)

    def __enter__(self):
        return self.excel

    def __exit__(self, exc, value, traceback):
        # Handle your app-specific exceptions (exc) here
        self.excel.quit()
        return True   
        # ^ return True only if you intend to catch all errors in here.
        # Otherwise, leave as is and use try... except on the outside.

class MyExcelWorkbook:
    def __init__(self, xlapp, bookname):
        self.workbook = xlapp.books.open(bookname)

    def __enter__(self):
        return self.workbook

    def __exit__(self, exc, value, traceback):
        # Handle your workbook specific exceptions (exc) here
        # self.workbook.save()   # depends what you want to do here
        self.workbook.close()
        return True   
        # ^ return True only if you intend to catch all errors in here.
        # Otherwise, leave as is and use try... except on the outside.    

With this set up you can simply call it like this:

with MyExcelApp() as app:
    with MyExcelWorkbook(filename) as wb:
        # do something with wb

You can also implement it with a generator, which will be quite similar to the other answer. Here's a simplified version:

import xlwings as xw
from contextlib import contextmanager

@contextmanager
def my_excel_app():
    app = xw.App(visible=False)
    try:
        yield app

    except:  # <-- Add SPECIFIC app exceptions
        # Handle the errors

    finally:
        app.quit()

Usage:

with my_excel() as app:
    wb = app.books.open(some_file)
    # do something...