How to create PDF reports from Excel with Python – PyXLL

Using Excel's Object Model or COM Interface you can script Excel in the same way as you would from VBA. The following links have some more general details about how to use Excel's Object Model from Python:

https://youtu.be/ubqsRcCUcB4
https://www.pyxll.com/docs/userguide/vba.html

To write out an Excel file as a PDF file we can use the "Workbook.ExportAsFixedFormat" method. The documentation for this method can be found here: https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbook.ExportAsFixedFormat

Exporting from inside Excel

To output the current workbook in Excel (from an Excel macro, menu function, or ribbon function using PyXLL) we would do something like:

from pyxll import xl_app, xl_menu

@xl_menu("Export as PDF")
def export_to_pdf():
     # Here we might ask the user for a filename, but we'll use a hardcoded one
     # to illustrate how to call the ExportAsFixedFormat method.
 
     # Get the Excel Application object
     xl = xl_app()

     # Get the current workbook from the active worksheet
     sheet = xl.ActiveSheet
     wb = sheet.Parent

     # Export the sheet as a PDF file
     # See https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbook.ExportAsFixedFormat
     # xlTypePDF = 0 (use use win32com.client.constants.xlTypePDF)
     wb.ExportAsFixedFormat(0, "C:/workbook.pdf")

Exporting using a batch script

Rather than exporting the workbook from a ribbon button or macro in Excel we might instead want a script to open a workbook, calculate it, and then write out a PDF file. This could then be scheduled as a Windows Scheduled Task.

To get the Excel Application object outside of Excel we can use "win32com.client.Dispatch", but that will always return us the current Excel instance if Excel is running. In this case it is better to use "win32com.client.DispatchEx" which will always create a new Excel process.

Once we have the Excel Application object we can load, calculate and export a workbook. If we were just to do this though there are a couple of problems that might occur though:

If Excel displays an alert or message our process will freeze
Some things (like charts) might not update as we would expect after calculating

1. To solve this set the Application.DisplayAlerts property to False.

2. We need to poll the Windows Message loop after calculating so Excel can "catch up". We can do this by calling pythoncom.PumpWaitingMessages() which does the same job as DoEvents in VBA.

Your script might look something like this:

from win32com.client import DispatchEx

# Get a new Application object
xl = DispatchEx("Excel.Application")

# Stop Excel from displaying alerts
xl.DisplayAlerts = False

# Load the workbook
wb = xl.Workbooks.Open(xlsx_filename)

# Calculate the workbook
xl.CalculateFull()

# Do anything else you like here to update the workbook

# Poll windows event loop (same as VBA DoEvents)
import pythoncom
pythoncom.PumpWaitingMessages()

# Export as pdf
# See https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbook.ExportAsFixedFormat
# xlTypePDF = 0
wb.ExportAsFixedFormat(0, pdf_filename)

Finally, one other thing to consider is that if you run this script as another user or as an administrator user (eg from a scheduled task) then that user will not have the same add-ins installed as you. You may need to install the add-ins you need (like PyXLL) as part of your script, for example:

addin = xl.AddIns.Add(xll_filename, False)
addin.Installed = True

Exporting from inside Excel

Exporting using a batch script

Related articles