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...

Answer 2

Preferred solution
xlwings added a solution in v0.24.3 to this problem: xlwings.App() can now be used as context manager, making sure that there are no zombie processes left over on Windows, even if you use a hidden instance and your code fails. It is therefore recommended to use it whenever you can, like so:

import xlwings as xw
with xw.App(visible=False) as app:
    wb = xw.Book("test.xlsx")
    sheet = wb.sheets['sheet1']
    # To evoke an error, I try to call an non-exisiting sheet here.
    nonexistent_sheet["A1"]

Solution before v24.0.3
You can use the library traceback, which makes debugging easier, because the error is displayed in red color. See this example:

import xlwings as xw
import traceback

filename = "test.xlsx"

try:
    # Do what you want here in the try block. For example, the following lines.
    app = xw.App(visible=False)
    wb = xw.Book(filename)
    sheet = wb.sheets['sheet1']
    # To evoke an error, I try to call an nonexistent sheet here.
    nonexistent_sheet["A1"]

# Use BaseException because it catches all possible exceptions: https://stackoverflow.com/a/31609619/13968392
except BaseException:
    # This prints the actual error in a verbose way.
    print(traceback.print_exc())
    app.quit()

The error displays with print(traceback.print_exc()) as follows:

Answer 3

you do it right - using try block in this case is the way to go. With statement is good when you need to open file, but not for your use case when you use library which is opening excel file using its own way.

To show details of exception you can change your code as follows:

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 

    finally:
        app.quit()
    except Exception as e:
        print('exception catched: {}'.format(e))
        app.quit()