Check a PDF Export for Cut-Off Columns

The most common complaint about an automated PDF report is not wrong numbers — it is a report that is hard to read: the last four columns printed on separate pages after all the rows, so totals sit three pages away from the customer names they belong to; a revenue column showing ######## because the figures grew wider than the column; or, after someone "fixed" that with fit-to-page, a table shrunk to six-point type. Each looks fine in the workbook on screen and only goes wrong in the PDF, which in an unattended job nobody looks at before it is emailed out. This page reads the PDF back with pdfplumber and checks for all three problems, then fixes the workbook's page setup with openpyxl and shows the check passing. It belongs to Exporting Excel Reports to PDF.

Prerequisites

pip install openpyxl pdfplumber

LibreOffice (soffice) does the conversion, as in Convert Excel to PDF on Linux with LibreOffice; the checks work on any PDF, whichever tool produced it. pdfplumber gives the position and font size of every word on a page, which is what the checks need.

Step 1 — A report that exports badly

A fourteen-column sales sheet, portrait page, no scaling — the defaults — with the header row set to repeat on each page and a net-amount column that is a little too narrow for its largest values:

import subprocess
from openpyxl import Workbook

COLUMNS = ["Order", "Date", "Customer", "Region", "Product", "Category", "Qty",
           "Price", "Discount", "Net", "Tax", "Gross", "Rep", "Status"]
WIDTHS = [10, 11, 24, 9, 14, 9, 6, 10, 9, 8, 7, 14, 10, 8]

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(COLUMNS)
for i in range(60):
    ws.append([f"SO-{4000 + i}", f"2026-09-{i % 28 + 1:02d}", f"Customer name {i} Ltd", "North", f"Product {i}", "Cat",
               i + 1, 12.5, 0.05, 12345.891 * (i + 1), 0.2, 15000.5 * (i + 1), "A. Rep", "Open"])
    ws.cell(ws.max_row, 10).number_format = "#,##0.00"
for letter, width in zip("ABCDEFGHIJKLMN", WIDTHS):
    ws.column_dimensions[letter].width = width
ws.print_title_rows = "1:1"
wb.save("sales.xlsx")

def export(xlsx):
    subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "--outdir", "pdf", xlsx], check=True, capture_output=True, timeout=180)
    return f"pdf/{xlsx.rsplit('.', 1)[0]}.pdf"

print(export("sales.xlsx"))

Nothing here is unusual, and on screen the sheet looks fine. Repeating the header row with print_title_rows is good practice and also makes the check below easier, since every page then starts with the names of the columns it holds.

Step 2 — Read the PDF back and check it

Three checks, all based on the words pdfplumber finds on each page: which columns' headers are on the page (the top line of words), whether any cell shows ###, and the smallest font size used:

import pdfplumber

def check_pdf(path, columns, min_font=7.0):
    problems, summary = [], []
    with pdfplumber.open(path) as pdf:
        for n, page in enumerate(pdf.pages, start=1):
            words = page.extract_words(extra_attrs=["size"])
            top = min(w["top"] for w in words)
            header = [w["text"] for w in words if abs(w["top"] - top) < 2]
            hashes = sum("###" in w["text"] for w in words)
            smallest = min(w["size"] for w in words)
            summary.append((n, header[0], header[-1], hashes, round(smallest, 1)))
            if header[0] != columns[0]:
                problems.append(f"page {n}: columns {header[0]}–{header[-1]} printed apart from {columns[0]}")
            if hashes:
                problems.append(f"page {n}: ### in {hashes} places (column too narrow)")
            if smallest < min_font:
                problems.append(f"page {n}: text shrunk to {smallest:.1f} pt")
    return len(pdf.pages), summary, problems

pages, summary, problems = check_pdf("pdf/sales.pdf", COLUMNS)
print(pages, "pages")
for row in summary:
    print("  page {}: columns {}–{}, ### found {}, smallest font {} pt".format(*row))
print("\n".join(problems))

The export has four pages. Pages 1 and 2 hold columns Order to Qty for all sixty rows; pages 3 and 4 hold Price to Status — so a reader matching a gross amount to its customer has to flip between page 3 and page 1. The check reports both spill pages and the hash marks in the Net column — 42 on page 3 and 18 on page 4 — because the column, eight characters wide, cannot show amounts formatted with thousands separators and two decimals. Unlike text, which spills into the empty cell next to it, a number that does not fit is replaced by hash marks, so the PDF loses the figures entirely rather than just looking untidy. Fonts are full size, because nothing is scaled. These are exactly the problems a person would see, found without anyone opening the file.

Step 3 — Fix the page setup

The usual fix is landscape orientation, fit all columns on one page wide with as many pages tall as needed, and a wider column where values overflow:

from openpyxl import load_workbook
from openpyxl.worksheet.properties import PageSetupProperties

wb = load_workbook("sales.xlsx")
ws = wb["Sales"]
ws.page_setup.orientation = "landscape"
ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.column_dimensions["J"].width = 13
wb.save("sales_fixed.xlsx")

pages, summary, problems = check_pdf(export("sales_fixed.xlsx"), COLUMNS)
print(pages, "pages")
for row in summary:
    print("  page {}: columns {}–{}, ### found {}, smallest font {} pt".format(*row))
print("problems:", problems or "none")

fitToPage=True switches the sheet from a fixed zoom to fit-to-pages mode; fitToWidth = 1 asks for one page across and fitToHeight = 0 means "as many pages down as needed" — without the zero, the whole table would be squeezed onto a single page. The fixed export has two pages, each with every column from Order to Status, no ### and a smallest font of 8.8 points: LibreOffice scaled the sheet to 80%, which is still comfortably readable. More page setup options are covered in Fit an Excel Sheet to One Page in PDF.

Step 4 — Make it a gate in the job

The check is cheap — well under a second for a few pages — so run it on every export and refuse to send a report that fails:

def export_checked(xlsx, columns):
    pdf_path = export(xlsx)
    _, _, problems = check_pdf(pdf_path, columns)
    if problems:
        raise RuntimeError(f"{pdf_path} failed layout checks:\n  " + "\n  ".join(problems))
    return pdf_path

for name in ["sales_fixed.xlsx", "sales.xlsx"]:
    try:
        print("ok:", export_checked(name, COLUMNS))
    except RuntimeError as e:
        print(str(e).splitlines()[0], f"({len(str(e).splitlines()) - 1} problems)")

The fixed workbook passes; the original fails with its four problems listed. A failure should stop the send and alert someone, as with any other failed step — Validate an Excel Report Before Sending It describes the same gate for the workbook's contents. The checks are deliberately simple heuristics. The header check relies on repeated print titles; for sheets without them, look for pages that contain none of the key column's values instead. The font threshold is a judgement — seven points is about the smallest people read comfortably on paper — and a table that can only fit by going smaller than that needs fewer columns, a larger paper size or splitting into two tables rather than more scaling.

It is also worth running the check on the largest plausible input, not just this month's. Overflow depends on the data: a column wide enough for this quarter's figures fails when a large order arrives or a customer with a long name signs up. A test that builds the report from a fixture with the widest values you expect — the biggest amounts, the longest names — and runs check_pdf on the export catches the problem when the code changes, rather than on the day the data grows.

Common pitfalls

• Trusting the screen view. Overflow and spills appear only in the printed layout.
• fitToHeight left at 1. Use 0 so the table may run to as many pages as needed.
• Forgetting fitToPage=True. Without it the fit settings are ignored.
• Fitting very wide sheets to one page. Check the font size; split the table if it is unreadable.
• No print titles. Repeat the header row so every page is readable and checkable.

Conclusion

Read every PDF export back with pdfplumber before sending it: check that each page's header starts with the key column (otherwise columns spilled onto separate pages), that no word contains ### (a numeric column is too narrow), and that the smallest font is still readable. Fix failures in the workbook's page setup — landscape, fitToPage with fitToWidth = 1 and fitToHeight = 0, wider columns — and make the check a gate that stops the report from being sent.

Frequently asked questions

Why does my Excel PDF put some columns on separate pages? The sheet is wider than the page, so Excel and LibreOffice print the extra columns on following pages. Set fit to one page wide, and landscape orientation if needed.

How do I detect ### in a PDF export with Python? Extract the words on each page with pdfplumber and look for any that contain ###, which is how a number too wide for its column is printed.

How do I fit all columns on one page width with openpyxl? Turn on fit-to-page in the sheet properties (the fitToPage flag of PageSetupProperties), then set ws.page_setup.fitToWidth = 1 and ws.page_setup.fitToHeight = 0.

How small can fit-to-page make the text? Excel and LibreOffice scale down to 10 percent, far below readable; check the smallest font size in the PDF and split the table if it falls below about 7 points.

Related

• Up one level: Exporting Excel Reports to PDF — PDFs from Excel in Python.
• Set the Print Area and Page Setup with openpyxl — all the page setup settings.
• Add Headers, Footers and Page Numbers to an Excel PDF — finishing the printed pages.
• Create a PDF Report from Excel Data with ReportLab — full control of the layout.