Saving a finished report or table in Excel is easy. You choose SaveAs and save the sheet as Pdf. Doing this automatically with Python is a bit trickier though. In this post, we will take a closer look on how to do this with the win32 library. The full code is available at the bottom of the post. Note that you need Excel installed in order to run this script successfully.
install the win32 library first with:
pip install pypiwin32. This will install the Win32 Api library, which according to PyPi contains: Python extensions for Microsoft Windows Provides access to much of the Win32 API, the ability to create and use COM objects, and the Pythonwin environment.
To get the file paths we use pathlib. Pathlib was introduced in Python 3.4 so it is quite new (Using Python 3.8 during the writing of this article). We specify the name of the Excel workbook we want to make a pdf of, and also the output pdf's name.
excel_file = "pdf_me.xlsx"
pdf_file = "pdf_me.pdf"
We then create paths from our current working directory (cwd) with Pathlibs cwd() method.
excel_path = str(pathlib.Path.cwd() / excel_file)
pdf_path = str(pathlib.Path.cwd() / pdf_file)
Excel is next up. We start the Excel application and hide it.
excel = client.DispatchEx("Excel.Application")
excel.Visible = 0
We then open our workbook
wb = excel.Workbooks.Open(excel_path) and load our first sheet with
ws = wb.Worksheets
Now it is time to use the SaveAs to save our sheet as a pdf.
wb.SaveAs(pdf_path, FileFormat=57)Fileformat 57 is the pdf file format.
We then close our workbook and quit our Excel application. Our pdf is now saved in our working directory.
from win32com import client import win32api import pathlib ### pip install pypiwin32 if module not found excel_file = "pdf_me.xlsx" pdf_file = "pdf_me.pdf" excel_path = str(pathlib.Path.cwd() / excel_file) pdf_path = str(pathlib.Path.cwd() / pdf_file) excel = client.DispatchEx("Excel.Application") excel.Visible = 0 wb = excel.Workbooks.Open(excel_path) ws = wb.Worksheets try: wb.SaveAs(pdf_path, FileFormat=57) except Exception as e: print("Failed to convert") print(str(e)) finally: wb.Close() excel.Quit()