15.12.2019

Save an Excel sheet as pdf with Python and win32

Saving an Excel sheet to Pdf with Python

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.

Installing dependencies

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.

File paths

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)

Firing up Excel

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[1]

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.

The code

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[1]

try:
    wb.SaveAs(pdf_path, FileFormat=57)
except Exception as e:
    print("Failed to convert")
    print(str(e))
finally:
    wb.Close()
    excel.Quit()

 

Leave a Reply

Your email address will not be published.

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram