Merge excel sheets into a single PDF file

 Here you will find a python code that will merge all excel sheets in a workbook into a single PDF file.

An excel workbook usually contains more than one or more sheets. In this post I want to automate the process of converting several sheets into pdf files then merge those pdf files into one single pdf.

import os
import glob
from PyPDF2 import PdfFileMerger
import win32com.client as client

# ------------------------------------------------
# Convert Excel file to PDF
# ------------------------------------------------
my_xlxs_file = r"C:\Users\`HYJ7\Desktop\XLS to PDF\Sample_quicktransportsolutions.xlsx"

# Open Microsoft Excel in the background
xl_app = client.DispatchEx("Excel.Application")
xl_app.Visible = False
xl_app.DisplayAlerts = False
xl_app.ScreenUpdating = False

pdf_path = os.path.splitext(my_xlxs_file)[0]

# Read Excel File
workbook = xl_app.Workbooks.Open(my_xlxs_file)
sheet_names = [sheet.Name for sheet in workbook.Sheets] # get sheet names

n = 1
for sht in range(len(sheet_names)):
    work_sheet = workbook.Worksheets[sht]

    work_sheet.ExportAsFixedFormat(0, f"{pdf_path}_{n}")
    n += 1

# Close the workbook
workbook.Close()

# ------------------------------------------------
# Merge PDF files into a single file...
# ------------------------------------------------

output_pdf_name = 'TestMergedPDF'
input_dir = r'C:\\Users\\`HYJ7\\Desktop\\XLS to PDF\\'
merge_list = []

for x in os.listdir(input_dir):
    if not x.endswith('.pdf'):
        continue
    merge_list.append(os.path.join(input_dir, x))

merger = PdfFileMerger()

for pdf in merge_list:
    merger.append(pdf)

merger.write(input_dir + f"\\{output_pdf_name}.pdf") #your output directory and pdf_file name
merger.close()

# Delete individual pdf files...
for pdf in merge_list:
    os.remove(pdf)

That is it!