Print Excel to pdf with xlwings

Question

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 1

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)

Answer 2

xlwings documentation recommends using xw.App():

from pathlib import Path
import xlwings as xw
import os

with xw.App() as app:
    # user will not even see the excel opening up
    app.visible = False
    book = app.books.open(path_to_excelfile)
    sheet = book.sheets[0]
    sheet.page_setup.print_area = '$A$1:$Q$66'
    sheet.range("A1").value = "experimental"

    # Construct path for pdf file
    current_work_dir = os.getcwd()
    
    pdf_file_name = "pdf_workbook_printout.pdf"
    pdf_path = Path(current_work_dir, pdf_file_name)

    # Save excel workbook as pdf and showing it
    sheet.to_pdf(path=pdf_path, show=True)