Generate One PDF per Excel Row

Month-end statements for each customer, a certificate for every course attendee, a remittance advice for each supplier paid this week: the data is one row per recipient in a sheet, and each recipient needs their own PDF. Mail merge in Word can do it with enough clicking; a script does it every month without anyone clicking at all. There are two good routes. Drawing each PDF directly with reportlab gives full control and runs fast. Filling an Excel template per row and converting with LibreOffice keeps the layout in a workbook that non-developers can edit. This page, part of Generating Excel Reports from Templates, builds both and checks what they produce.

Prerequisites

pip install pandas reportlab pypdf openpyxl
# for the template route
sudo apt install libreoffice-calc

statements.xlsx has five customers with an ID, name, address, balance and due date. One name contains a slash — which is not allowed in a file name — and one balance is zero. statement_template.xlsx is a one-page statement layout with labelled cells for the fields.

Step 1 — Safe, unique file names

File names come from the data, so clean them first: characters such as /, \, : and * are not allowed on one operating system or another, and two customers with the same name would overwrite each other. Build the name from the ID, which is unique, plus a readable slug:

import re
import pandas as pd

rows = pd.read_excel("statements.xlsx", parse_dates=["DueDate"])

def pdf_name(row):
    slug = re.sub(r"[^A-Za-z0-9]+", "-", row.Name).strip("-").lower()
    return f"statement_{row.CustomerID}_{slug}.pdf"

rows["File"] = [pdf_name(r) for r in rows.itertuples()]
assert rows["File"].is_unique
print(rows[["CustomerID", "Name", "File"]].to_string(index=False))

Evans / Partners becomes evans-partners, and the customer ID in front guarantees uniqueness even if two customers share a name. The assertion makes that guarantee explicit and stops the run before any file is written if it ever fails.

Step 2 — Route A: draw each PDF with reportlab

reportlab's platypus layer lays out paragraphs and tables for you. Build one document per row:

from pathlib import Path
from reportlab.lib import colors
from reportlab.lib.pagesizes import A4
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.lib.units import mm
from reportlab.platypus import Paragraph, SimpleDocTemplate, Spacer, Table, TableStyle

styles = getSampleStyleSheet()
out = Path("pdf_reportlab")
out.mkdir(exist_ok=True)

def statement_pdf(row, path):
    doc = SimpleDocTemplate(str(path), pagesize=A4, leftMargin=20 * mm, rightMargin=20 * mm, topMargin=20 * mm)
    table = Table([
        ["Customer", f"{row.Name} ({row.CustomerID})"],
        ["Address", row.Address],
        ["Balance due", f"£{row.Balance:,.2f}"],
        ["Due date", f"{row.DueDate:%-d %B %Y}"],
    ], colWidths=[40 * mm, 120 * mm])
    table.setStyle(TableStyle([("FONTNAME", (0, 0), (0, -1), "Helvetica-Bold"),
                               ("LINEBELOW", (0, 0), (-1, -1), 0.25, colors.lightgrey),
                               ("BOTTOMPADDING", (0, 0), (-1, -1), 6)]))
    note = ("No payment is due this month." if row.Balance == 0
            else "Please pay by bank transfer quoting your customer number.")
    doc.build([Paragraph("Statement of account", styles["Title"]), Spacer(1, 8 * mm),
               table, Spacer(1, 8 * mm), Paragraph(note, styles["Normal"])])

for row in rows.itertuples():
    statement_pdf(row, out / row.File)
print(sorted(p.name for p in out.iterdir()))

The zero-balance customer, Dunmore, gets a different closing line — the kind of per-row rule that is trivial in code and awkward in a mail merge. Text in Paragraph and table cells is plain strings here; if the data can contain & or <, which reportlab's paragraph markup treats specially, escape it with html.escape first. Birch & Co works in the table because table cells are not parsed as markup.

Step 3 — Check every PDF says what it should

A batch of PDFs is only useful if each one contains the right recipient and figure. Extract the text of each file with pypdf and compare it with its row:

from pypdf import PdfReader

def check(folder):
    problems = []
    for row in rows.itertuples():
        text = " ".join(page.extract_text() for page in PdfReader(folder / row.File).pages)
        for expected in (row.CustomerID, f"{row.Balance:,.2f}"):
            if expected not in text:
                problems.append((row.File, expected))
    return problems

print("reportlab PDFs:", check(out) or "all correct")

This check catches the failures that matter in a statement run, and it is cheap enough to run on every batch, not just the first one: a customer's PDF showing another customer's balance because of an indexing bug, or a missing figure because a column was renamed. It runs in well under a second for hundreds of files.

Step 4 — Route B: fill an Excel template per row

When the layout should be maintained in Excel — by the finance team, say — fill a copy of the template for each row with openpyxl, then convert all of them to PDF in one LibreOffice run:

import subprocess
from openpyxl import load_workbook

xl_dir, pdf_dir = Path("filled"), Path("pdf_template")
xl_dir.mkdir(exist_ok=True)
pdf_dir.mkdir(exist_ok=True)
for row in rows.itertuples():
    wb = load_workbook("statement_template.xlsx")
    ws = wb["Statement"]
    ws["B3"], ws["B4"] = f"{row.Name} ({row.CustomerID})", row.Address
    ws["B6"], ws["B7"] = row.Balance, row.DueDate.to_pydatetime()
    if row.Balance == 0:
        ws["A9"] = "No payment is due this month."
    wb.save(xl_dir / row.File.replace(".pdf", ".xlsx"))

subprocess.run(["soffice", f"-env:UserInstallation=file://{Path.cwd()}/lo_profile", "--headless",
                "--convert-to", "pdf", "--outdir", str(pdf_dir), *map(str, sorted(xl_dir.glob("*.xlsx")))],
               check=True, capture_output=True, timeout=300)
print(sorted(p.name for p in pdf_dir.iterdir()))
print("template PDFs:", check(pdf_dir) or "all correct")

The template's number formats do the formatting — £#,##0.00 for the balance and a long date for the due date — so the Python code writes plain numbers and dates. Passing every file to one soffice call matters: LibreOffice's start-up takes about a second, so converting files one at a time costs a second each, while a batch pays it once. Populate an Excel Template Without Losing Formatting covers the template side in depth, and Convert Excel to PDF on Linux with LibreOffice the conversion.

Step 5 — One combined file for printing

Individual files go to email; the print room wants one file. Merge them in customer order with pypdf:

from pypdf import PdfWriter

merged = PdfWriter()
for row in rows.itertuples():
    merged.append(str(out / row.File))
merged.write("all_statements.pdf")
print(len(PdfReader("all_statements.pdf").pages), "pages in all_statements.pdf")

append copies every page of each file, so multi-page statements stay together, and the loop over rows rather than over the folder keeps the printed order the same as the sheet's. Merge Excel PDF Exports into One File adds bookmarks per customer.

Performance notes

The two routes differ by more than an order of magnitude. In a quick test on this machine, reportlab wrote 200 simple one-page PDFs in under a tenth of a second, while LibreOffice converted 100 filled templates in one batch call in about two and a half seconds — roughly a second of start-up plus a fraction of a second per file. For a few hundred statements a month either is fine. For tens of thousands, reportlab is the practical choice, or split the template route into several LibreOffice batches running in parallel, each with its own -env:UserInstallation profile so they do not collide. Filling the templates with openpyxl costs time too: loading the template once per row re-reads it from disk each time, which is simple and safe; for very large runs, keep the template bytes in memory and load from a BytesIO copy instead.

Common pitfalls

• Unsafe file names. Slashes and colons from the data break paths; slugify and prefix with a unique ID.
• One LibreOffice call per file. Batch every file into one conversion.
• reportlab markup characters. & and < in Paragraph text need escaping.
• Fonts. reportlab's built-in Helvetica covers Western European text; register a TrueType font for other scripts.
• Sending without checking. Extract each PDF's text and compare it with its row before anything is emailed.

Conclusion

Give every output a safe, unique file name built from the row's ID, then either draw each PDF with reportlab's platypus layouts or fill a copy of an Excel template per row and convert them all in one LibreOffice call. Apply per-row rules in code, extract the text of every PDF with pypdf to confirm it carries the right ID and amount, and merge the files with pypdf when someone needs a single document to print.

Frequently asked questions

How do I create a separate PDF for each row in Excel with Python? Read the sheet with pandas and loop over the rows. For each row, either build a PDF directly with reportlab, or fill a copy of an Excel template with openpyxl and convert the copies to PDF with headless LibreOffice.

Which is better, reportlab or an Excel template? reportlab is faster and needs no office software, but the layout lives in code. A template keeps the layout editable in Excel by people who do not write Python, at the cost of needing LibreOffice or Excel for the conversion.

How do I avoid invalid characters in the PDF file names? Replace everything except letters and digits with hyphens, and prefix the name with a unique ID from the row so two rows can never produce the same file name.

How can I check the generated PDFs are correct? Extract each file's text with pypdf and check that the row's identifying values, such as the customer ID and the formatted amount, appear in it.

Related

• Up one level: Generating Excel Reports from Templates — template-driven report generation.
• Generate One Excel Report per Region in a Loop — the same loop producing workbooks.
• Fill PDF Forms from Excel Rows with Python — when the layout is a fixed PDF form.
• Send Each Recipient Their Own Excel Report — delivering the files one by one.