python3 and pywin32 closing excel

Question

I am having an issue closing excel after using Dispatch.

import openpyxl
import os 
from win32com import client



class CTAutomation:

    def __init__(self, file):
        self.invoice = xl.load_workbook(os.getcwd() + "\Templates\ctrates.xlsx")
        self.xlTemplate = xl.load_workbook(os.getcwd() + "\Templates\invoiceTemplate.xlsx")
        self.vpc = xl.load_workbook(os.getcwd() + "\Templates\Vpc.xlsx")
        self.file = file

    def invoice_make(self):
        self.xlApp = client.Dispatch("Excel.Application")
        self.xlbook = self.xlApp.Workbooks.Open(os.getcwd() + '\TestFiles\\' + self.file)
        self.ws = self.xlbook.Worksheets[0]
        self.ws.Visible = 1
        self.ws.ExportAsFixedFormat(0, os.getcwd() + "\complitedpdf\\" + self.file + ".pdf")
        self.quit()

    def quit(self):
        self.xlbook.Close()
        self.xlApp.Quit()

    def xlformater(self):
        return None

def main():
    pwd = os.listdir(os.getcwd() + "\TestFiles")
    for file in pwd:
        CTAutomation(file.strip(".xlsx")).invoice_make()

if __name__ == "__main__":
    main()

all works well till this part. i have found a few posts about this topic in the forum but i feel that im still missing something to close the app, .xlsx and xls(Latest Versions) to pdf using python in example

some advice would be much appreciated .

Answer 1

Essentially it is your class object persisting in memory. Consider wrapping the process in a context manager using with(). And call the invoice_make() within the context.

Additionally, you had an incorrect Excel method by indexing workbook by zero with square brackets.

Finally, consider using os.path.join() to aviod back or forward slashes and use a try/except block to catch COM exceptions and properly release objects from memory.

import openpyxl as xl
import os 
from win32com import client

cwd = os.getcwd()

class CTAutomation:

    def __init__(self):
        self.invoice = xl.load_workbook(os.path.join(cwd, "Templates", "ctrates.xlsx"))
        self.xlTemplate = xl.load_workbook(os.path.join(cwd, "Templates", "invoiceTemplate.xlsx"))
        self.vpc = xl.load_workbook(os.path.join(cwd, "Templates", "Vpc.xlsx"))

    def invoice_make(self, file):
        try:
            self.xlApp = client.Dispatch("Excel.Application")
            self.xlbook = self.xlApp.Workbooks.Open(os.path.join(cwd, "TestFiles", file))
            self.ws = self.xlbook.Worksheets(1)       # USE PARENTHESES (NOT BRACKETS AND NON-ZERO INDEX)
            #self.ws.Visible = 1                      # KEEP PROCESS IN BACKGROUND
            self.ws.ExportAsFixedFormat(0, os.path.join(cwd, "complitedpdf", file.replace(".xlsx",".pdf")))
            self.xlbook.Close(False)
            self.xlApp.Quit()

        except Exception as e:
            print(e)

        finally:
            self.ws = None                            # RELEASE EXCEL OBJS FROM MEMORY
            self.xlbook = None
            self.xlApp = None

    def xlformater(self):
        return None

    def __enter__(self):
        return self                                   # BOUND TO as IN with()

    def __exit__(self, *err):
        return None

def main():
    pwd = os.listdir(os.path.join(cwd, "TestFiles"))   

    with CTAutomation() as obj:                       # CONTEXT MANAGER
        for file in pwd:
            print(file)
            obj.invoice_make(file)

if __name__ == "__main__":
    main()