Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Fill PDF Forms from Excel Rows with Python

Fill a fillable PDF form — an expense claim, an order form, a government template — once per spreadsheet row with pypdf: list the form's field names, map sheet columns to fields, format numbers and dates as text, tick checkboxes with the right state name, flatten the result so it cannot be edited, and verify every filled file.
PDF exportReport layouts & templates

Some documents have to be on someone else's form: a supplier's order form, an insurer's claim template, a government return, an internal expense claim that finance will only accept in its own layout. When the data is a spreadsheet with one row per form, filling them by hand means copying the same fields dozens of times. A fillable PDF — an AcroForm — has named fields, and pypdf can write values into them, so a script fills one copy per row, ticks the right boxes and, if needed, flattens the result so the values are part of the page. This page is part of Generating Excel Reports from Templates.

Prerequisites

Bash
pip install pypdf pandas openpyxl

claim_form.pdf is a one-page expense claim form with five text fields and a "receipts attached" checkbox. claims.xlsx has four claims, one per row, with columns that correspond to the fields but are not named the same way — the usual situation.

From a row to a filled, flattened form The form's field names are listed once, each sheet column is mapped to a field, values are formatted as text, each row fills a copy of the form, the copy is flattened, and the result is checked. List fields get_fields() Map columns sheet → field names Format values numbers, dates, ticks Fill a copy per row Flatten + check values on the page

Step 1 — List the form's fields

Every fillable field has an internal name, which is what the script writes to — not the label printed next to it. List them with their types and current values:

Python
from pypdf import PdfReader

fields = PdfReader("claim_form.pdf").get_fields()
for name, field in fields.items():
    print(f"{name:12} type={field.get('/FT')} value={field.get('/V')!r} states={field.get('/_States_', '')}")

/Tx is a text field and /Btn a button — here a checkbox. For checkboxes, /_States_ lists the values the box accepts: /Off and the "on" state, which is usually /Yes but can be anything the form's author chose, such as /On or /1. Writing any other value leaves the box looking unticked, so always read the state name from the form rather than assuming. Real-world forms often have unhelpful internal names like Text12; list them once, and if the names do not say which box is which, fill each with its own name as a test and look at the result.

Step 2 — Map columns to fields and format the values

Keep the mapping between the sheet and the form in one dictionary. Everything a PDF field holds is text, so format numbers and dates exactly as they should appear:

Python
import pandas as pd

claims = pd.read_excel("claims.xlsx", parse_dates=["ClaimDate"])
CHECKED = [s for s in fields["receipts"]["/_States_"] if s != "/Off"][0]

def field_values(row):
    return {
        "employee": row.Name,
        "employee_id": row.EmployeeID,
        "cost_centre": row.CostCentre,
        "amount": f"{row.Amount:,.2f}",
        "claim_date": f"{row.ClaimDate:%d/%m/%Y}",
        "receipts": CHECKED if str(row.Receipts).strip().lower() in {"yes", "y", "true", "1"} else "/Off",
    }

values = [field_values(r) for r in claims.itertuples()]
missing = set().union(*values) - set(fields)
assert not missing, f"no such fields in the form: {missing}"
print(values[1])
Sheet columns mapped to form fields On the left a row of the claims sheet with its column names. On the right the same values as form field names and the text written into each field, with the amount and date formatted and Yes turned into the checkbox's on state. A B 1 Column Value 2 Name Jonas Lindqvist 3 Amount 1275 4 ClaimDate 2026-09-26 5 Receipts yes A B 1 Field Written 2 employee Jonas Lindqvist 3 amount 1,275.00 4 claim_date 26/09/2026 5 receipts /Yes before after field_values(row)

The assertion catches a mapping that names a field the form does not have — a typo, or a new version of the form with renamed fields — before any file is written. Formatting in Python makes the output independent of whatever formatting the form itself might apply: 1,275.00 and 26/09/2026 appear exactly as written.

Step 3 — Fill one copy per row

Clone the blank form for each row, write the values, and save under a name built from the row:

Python
from pathlib import Path
from pypdf import PdfWriter

out = Path("filled")
out.mkdir(exist_ok=True)
for row, vals in zip(claims.itertuples(), values):
    writer = PdfWriter(clone_from="claim_form.pdf")
    writer.update_page_form_field_values(writer.pages[0], vals, auto_regenerate=False)
    writer.set_need_appearances_writer(True)
    writer.write(out / f"claim_{row.EmployeeID}.pdf")
print(sorted(p.name for p in out.iterdir()))

PdfWriter(clone_from=...) copies the whole form, fields and all, so each output starts blank. update_page_form_field_values writes the values and generates appearance streams — the drawing of the text inside each box that viewers display. set_need_appearances_writer(True) additionally asks viewers to redraw the fields from their values, which helps viewers that ignore generated appearances. The filled files are still forms: the recipient can change the values.

Step 4 — Flatten so the values cannot be changed

For a claim that has been signed off, the values should be part of the page, not editable fields. flatten=True draws the values into the page content; removing the field widgets and the form dictionary then leaves a plain PDF:

Python
flat = Path("flattened")
flat.mkdir(exist_ok=True)
for row, vals in zip(claims.itertuples(), values):
    writer = PdfWriter(clone_from="claim_form.pdf")
    writer.update_page_form_field_values(writer.pages[0], vals, auto_regenerate=False, flatten=True)
    writer.remove_annotations(subtypes="/Widget")
    if "/AcroForm" in writer._root_object:
        del writer._root_object["/AcroForm"]
    writer.write(flat / f"claim_{row.EmployeeID}.pdf")

check = PdfReader(flat / "claim_E02.pdf")
print("fields left:", check.get_fields())
print(check.pages[0].extract_text().splitlines()[-5:])

flatten=True on its own writes the values into the page but leaves the field objects in place; removing the /Widget annotations and the /AcroForm entry turns it into an ordinary PDF with no fields. The extracted text now contains the values as page text, which is also what makes flattened forms searchable and printable everywhere. Keep the editable version too if the forms may need correcting later, since changing a flattened form means filling it again.

Filled form or flattened copy Two panels. A filled form keeps its fields, so values can still be edited and read back as fields. A flattened copy has the values drawn on the page and no fields, so it cannot be edited but prints and searches everywhere. Filled form values in named fields easy to correct later recipient can change values Flattened copy values are page content prints and searches everywhere no fields left to edit

Step 5 — Verify every file

Check each output against its row: read the fields back from the editable files, and the page text from the flattened ones:

Python
problems = []
for row, vals in zip(claims.itertuples(), values):
    got = {k: v.get("/V") for k, v in PdfReader(out / f"claim_{row.EmployeeID}.pdf").get_fields().items()}
    if got != vals:
        problems.append((row.EmployeeID, "fields"))
    text = PdfReader(flat / f"claim_{row.EmployeeID}.pdf").pages[0].extract_text()
    if vals["amount"] not in text or row.Name not in text:
        problems.append((row.EmployeeID, "flattened text"))
print(problems or "all forms correct")
print({r.EmployeeID: r.Receipts for r in claims.itertuples()})

Every form holds exactly the values mapped from its row, including the checkbox states — ticked for the three claims with receipts and unticked for Claire Martin's. Running this check before the forms are sent is what makes a batch of filled forms trustworthy.

Forms that are not fillable

Many "forms" are just printed layouts saved as PDF, with no fields at all — get_fields() returns None. They can still be filled: draw the values at the right coordinates on a transparent overlay page and merge it onto the form, the same technique used for watermarks:

Python
from reportlab.pdfgen import canvas as rl_canvas

POSITIONS = {"employee": (185, 726), "amount": (185, 606), "claim_date": (185, 566)}

def overlay_fill(blank, vals, dest):
    page = PdfReader(blank).pages[0]
    w, h = float(page.mediabox.width), float(page.mediabox.height)
    c = rl_canvas.Canvas("_overlay.pdf", pagesize=(w, h))
    c.setFont("Helvetica", 11)
    for name, (x, y) in POSITIONS.items():
        c.drawString(x, y, vals[name])
    c.save()
    writer = PdfWriter(clone_from=blank)
    writer.pages[0].merge_page(PdfReader("_overlay.pdf").pages[0])
    writer.write(dest)

print("fields in printed form:", PdfReader("printed_form.pdf").get_fields())
overlay_fill("printed_form.pdf", values[0], "overlay_E01.pdf")
print(PdfReader("overlay_E01.pdf").pages[0].extract_text().splitlines()[-3:])

printed_form.pdf has the same boxes as the fillable form but no fields, as get_fields() confirms. Coordinates are in points from the bottom-left corner of the page; each value is placed a few points inside its printed box. The example fills three of the boxes to keep the positions table short; a real script lists every box, and the same verification — extracting the page text and looking for each row's values — works on overlay-filled forms exactly as on flattened ones. Finding them is trial and error the first time — render the result, adjust, repeat — and they have to be updated whenever the form's layout changes, which is exactly why fillable fields are better when the form's owner offers them.

Common pitfalls

  • Writing to label text instead of field names. List the fields with get_fields() and use their internal names.
  • Assuming the checkbox value is /Yes. Read the on-state from /_States_.
  • Numbers and dates written raw. Fields hold text; format values exactly as they should appear.
  • Expecting flatten=True to remove the form. Remove the widget annotations and /AcroForm as well for a plain PDF.
  • XFA forms. Some government and insurance forms use Adobe's XFA format instead of AcroForm fields; pypdf cannot fill those, and get_fields() returns little or nothing.

Conclusion

List the form's field names and checkbox states with get_fields(), map sheet columns to fields in one dictionary, format every value as the text that should appear, and fill a fresh clone of the form per row with update_page_form_field_values. Flatten with flatten=True plus removal of the widgets and /AcroForm when the values must not change, and read every output back — fields or page text — to confirm each file carries its own row's values.

Frequently asked questions

How do I fill a PDF form with Python? Open the form with pypdf's PdfWriter(clone_from="form.pdf"), pass a dictionary of field names and values to update_page_form_field_values for the page, and write the result. Use PdfReader(...).get_fields() to find the field names.

How do I tick a checkbox in a PDF form with pypdf? Set the field to its on-state name, which you find in the field's /States list — usually /Yes, but it can differ. Set it to /Off to untick it.

How do I flatten a filled PDF form? Pass flatten=True to update_page_form_field_values, then remove the /Widget annotations and the /AcroForm entry from the document. The values remain drawn on the page and no editable fields are left.

Why can't pypdf find any fields in my form? The PDF may use XFA, a different form technology used by some government and insurance forms, or it may not be fillable at all — just a printed layout. Check with get_fields(); for non-fillable PDFs, draw the values onto the page instead.

More on pdf export

More on pdf export →