[Python] Generate a PDF from Excel through Python, without opening Excel

We'd generate PDFs from Excel through Python. We currently do this using the Win32com library as per this Stack Overflow answer. In summary, we explicitly call o = win32com.client.Dispatch("Excel.Application"), opening an actual Excel instance (visible in the Task Manager), call Excel's PDF printing capability, wb.ActiveSheet.ExportAsFixedFormat(0, PATH_TO_PDF), and finally closing the Excel instance, o.Quit(). The main downside here is that if Excel is already open in the system, the Quit() call causes Excel to open its system dialogue asking to save changes, ignore changes and close, or abort. Though we can catch this error in Python, we seem to cannot close the Excel instance after this happens. Relaunching the Python program doesn't help, since the old Excel instance still lives. We need to quell it through the Task Manager in order to be able to run the program again.

Our generated XLSX file has a fixed size, A1:G50. It contains 3 images (2 logos and the result graph as saved to a PNG file by matplotlib) and otherwise text and numbers, of which some are formatted bold.

An MRE:

def save_as_pdf(ExcelInstance, path_to_pdf):
wb_path = r'~/path_to_xlsx/workbook.xlsx'
wb = ExcelInstance.Workbooks.Open(wb_path)

print_area = 'A1:G50'

ws = wb.Worksheets[0]
ws.PageSetup.Zoom = False
ws.PageSetup.FitToPagesTall = 1
ws.PageSetup.FitToPagesWide = 1
ws.PageSetup.PrintArea = print_area

wb.WorkSheets([1]).Select()
wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)

wb.Close(False)

o = win32com.client.Dispatch("Excel.Application")
o.Visible = False
save_as_pdf(o, path_to_pdf)
o.Quit()
del o


To reproduce the issue, run the above program with an Excel instance (on any random workbook) open already.

Circumventing this problem would be good, since having to tell users to close Excel through the Task Manager isn't practicable. Additionally, having multiple users generating reports on a centralised system causes trouble if two or more calls open up Excel instances simultaneously.

We found Python solutions such as Spire.XLS or ReportLab to generate PDFs from Excel documents, but these are rather expensive third-party libraries. Given we do have a valid Excel license, we'd prefer utilising that.

How can we automatically generate PDF documents from Excel without explicitly having to open Excel?

Continue reading...