Print Excel to pdf with xlwings

I am trying to print Excel files to pdf with xlwings. I am using the excel api for this.

I have tried it in two ways:

1/ Using the PrintOut() call with PrintToFile argument:

 wb.api.PrintOut(PrintToFile=True, PrToFileName="5.pdf", Preview=True)

The problem here is Excel just prints the file, ignoring my additional settings.

2/ Using ExportAsFixedFormat

 wb.api.ExportAsFixedFormat(0, str(SwmId) + ".pdf")

Here Excel flashes a bit, but does not do anything in the end.

For the record: I can’t use a macro and call it from Python because I have about a thousand of these Excel files. So, I can’t put the macro in every single one of them. It would probably be a workaround to create a custom function in VBA and than call it every file. But, honestly, it would be easier if I could just do this directly from Python, in one line of code.

Answer

Below is a self-standing code example of what worked on my machine to print an excel workbook to pdf (using the ExportAsFixedFormat method):

# Environment
# -----------
# OS: Windows 10
# Excel: 2013
# python: 3.7.4
# xlwings: 0.15.8

import os
import xlwings as xw

# Initialize new excel workbook
book = xw.Book()
sheet = book.sheets[0]
sheet.range("A1").value = "dolphins"

# Construct path for pdf file
current_work_dir = os.getcwd()
pdf_path = os.path.join(current_work_dir, "workbook_printout.pdf")

# Save excel workbook to pdf file
print(f"Saving workbook as '{pdf_path}' ...")
book.api.ExportAsFixedFormat(0, pdf_path)

# Open the created pdf file
print(f"Opening pdf file with default application ...")
os.startfile(pdf_path)