Replace Placeholders in an Excel Template with Python

Let report designers type named markers such as customer and period straight into the template, then fill them from Python: whole-cell placeholders that keep numbers numeric, placeholders inside text, a repeating row for line items, and checks for anything left unfilled.

Report layouts & templates

Filling a template by cell address — "the customer name goes in C4" — works until somebody inserts a row above the header and every address in the script is off by one. Placeholders move the contract into the template itself. The person who designs the report types {{customer}} where the customer name should appear and Invoice for {{period}} where a sentence needs a value, formats those cells however they like, and your script finds and replaces the markers wherever they currently are. This guide, part of Generating Excel Reports from Templates, builds a placeholder filler with openpyxl.

Prerequisites

Bash

pip install openpyxl

This builds the template shown above, with formatting on the placeholder cells so you can see it survive the fill:

Python

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Invoice"
ws["A1"] = "INVOICE"
ws["A1"].font = Font(size=18, bold=True)
ws["A2"], ws["B2"] = "Customer", "{{customer}}"
ws["A3"], ws["B3"] = "Period", "Services for {{period}}"
ws["A4"], ws["B4"] = "Total due", "{{total}}"
ws["B4"].number_format = '"€"#,##0.00'
ws["B4"].font = Font(bold=True)
for col, label in zip("ABC", ["Item", "Quantity", "Price"]):
    ws[f"{col}6"] = label
    ws[f"{col}6"].fill = PatternFill("solid", start_color="E7EBEF")
ws["A7"], ws["B7"], ws["C7"] = "{{item.name}}", "{{item.qty}}", "{{item.price}}"
ws["C7"].number_format = '"€"#,##0.00'
ws["A9"] = "Thank you for your business, {{customer}}."
wb.save("invoice_template.xlsx")

Step 1 — Find every placeholder in the workbook

Start by listing what the template asks for. A regular expression finds {{name}} markers, allowing dots for grouped names, and a scan over every cell of every sheet returns where each one is. This inventory drives both the fill and the checks.

Python

import re
from openpyxl import load_workbook

PLACEHOLDER = re.compile(r"\{\{\s*([A-Za-z_][\w.]*)\s*\}\}")

def find_placeholders(wb) -> dict[str, list[str]]:
    found: dict[str, list[str]] = {}
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str):
                    for name in PLACEHOLDER.findall(cell.value):
                        found.setdefault(name, []).append(f"{ws.title}!{cell.coordinate}")
    return found

template = load_workbook("invoice_template.xlsx")
for name, cells in find_placeholders(template).items():
    print(f"{name:12} {cells}")

The \s* inside the braces tolerates {{ customer }} with spaces, which people type as often as not. Allowing only identifier characters keeps a stray {{ in an ordinary sentence from being mistaken for a marker.

Step 2 — Replace whole-cell placeholders with typed values

When a cell contains only a placeholder, the replacement should become the cell's value with its real type: a number stays a number so the template's currency format applies and formulas can use it, and a date stays a date. Replacing it with a string would display 1234.5 left-aligned as text.

Python

def fill_scalars(wb, values: dict) -> list[str]:
    missing = []
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if not isinstance(cell.value, str):
                    continue
                whole = PLACEHOLDER.fullmatch(cell.value.strip())
                if whole and whole.group(1) in values:
                    cell.value = values[whole.group(1)]             # keeps the value's type
                    continue

                def substitute(match):
                    name = match.group(1)
                    if name in values:
                        return str(values[name])
                    missing.append(f"{ws.title}!{cell.coordinate}:{name}")
                    return match.group(0)

                if PLACEHOLDER.search(cell.value) and not cell.value.strip().startswith("{{item."):
                    cell.value = PLACEHOLDER.sub(substitute, cell.value)
    return missing

wb = load_workbook("invoice_template.xlsx")
missing = fill_scalars(wb, {"customer": "Northwind Traders", "period": "August 2026", "total": 4630.5})
print(wb["Invoice"]["B2"].value, "|", wb["Invoice"]["B3"].value, "|", wb["Invoice"]["B4"].value, wb["Invoice"]["B4"].number_format)
print("missing:", missing)

Placeholders embedded in text — Services for {{period}} — are substituted as strings, because the cell is text anyway. Assigning cell.value leaves the cell's font, fill, border and number format untouched, which is why the bold, currency-formatted total survives. Line-item markers are skipped here and handled in the next step.

Step 3 — Expand a repeating row for line items

Invoices and statements have a variable number of rows. Mark one template row with {{item.*}} placeholders, then insert as many copies as there are items, copying the template row's styles to each, and fill the values. Everything below the table moves down to make room.

Python

from copy import copy

def expand_rows(ws, items: list[dict], prefix: str = "item") -> int:
    marker_row = next(
        cell.row for row in ws.iter_rows() for cell in row
        if isinstance(cell.value, str) and f"{{{{{prefix}." in cell.value
    )
    template_cells = [
        (cell.column, cell.value, copy(cell._style)) for cell in ws[marker_row]
    ]
    if len(items) > 1:
        ws.insert_rows(marker_row + 1, amount=len(items) - 1)

    for offset, item in enumerate(items):
        target_row = marker_row + offset
        for column, raw, style in template_cells:
            cell = ws.cell(row=target_row, column=column)
            cell._style = copy(style)
            match = PLACEHOLDER.fullmatch(raw.strip()) if isinstance(raw, str) else None
            if match and match.group(1).startswith(prefix + "."):
                cell.value = item[match.group(1).split(".", 1)[1]]
            else:
                cell.value = raw
    return marker_row

items = [
    {"name": "Consulting", "qty": 12, "price": 250.0},
    {"name": "Training day", "qty": 1, "price": 1200.0},
    {"name": "Travel", "qty": 1, "price": 430.5},
]
first = expand_rows(wb["Invoice"], items)
print("items start on row", first, "; closing line now on row", first + len(items) + 1)

insert_rows shifts cell values and styles below the insertion point, but it does not rewrite formulas that refer to shifted cells, and it does not move merged ranges or conditional formatting. Keep formulas that total the table above it, or write the total as a value as this template does with {{total}}. The limits of row insertion are described in Insert and Delete Rows and Columns with openpyxl.

Step 4 — Fill headers and footers too

Page headers and footers are where report names and periods often belong, and they are invisible to a cell scan. Apply the same substitution to their text.

Python

def fill_header_footer(wb, values: dict) -> None:
    for ws in wb.worksheets:
        for part in (ws.oddHeader, ws.oddFooter, ws.evenHeader, ws.evenFooter):
            for section in (part.left, part.center, part.right):
                if section.text:
                    section.text = PLACEHOLDER.sub(
                        lambda m: str(values.get(m.group(1), m.group(0))), section.text)

wb["Invoice"].oddFooter.center.text = "Northwind invoice — {{period}}"
fill_header_footer(wb, {"period": "August 2026"})
print(wb["Invoice"].oddFooter.center.text)

Unknown names are left in place rather than replaced with an empty string, so the check in the next step can report them.

Step 5 — Refuse to save a half-filled report

A report that reaches a customer with {{po_number}} printed on it is worse than a report that is late. After filling, scan again; if any placeholder remains, raise with the list of names and cells instead of saving.

Python

def assert_filled(wb) -> None:
    leftover = find_placeholders(wb)
    if leftover:
        detail = "; ".join(f"{name} at {', '.join(cells)}" for name, cells in leftover.items())
        raise ValueError(f"unfilled placeholders: {detail}")

assert_filled(wb)                           # raises with names and cells if anything is left
wb.save("invoice_northwind.xlsx")

saved = load_workbook("invoice_northwind.xlsx")["Invoice"]
print([saved.cell(row, 1).value for row in range(7, 10)])   # the three line items
print(saved["A11"].value)                   # closing line, moved down by the inserted rows

The check also protects against the template changing under the script: when a designer adds {{po_number}} to the template next month, the first run fails with a message that names the new field, instead of quietly producing invoices with a marker on them.

Common pitfalls

Symptom	Cause	Fix
Total shows as text, currency format ignored	Whole-cell placeholder replaced with a string	Assign the typed value when the cell holds only the placeholder
Placeholder not found	Excel autocorrected braces, or spaces inside them	Allow `\s*` in the pattern; check the inventory output
Formulas below the table point at the wrong rows	`insert_rows` does not rewrite references	Keep totals above the table, or compute them in Python
Styles missing on expanded rows	New rows inserted without copying styles	Copy each template cell's `_style`
Bold words inside a sentence lost	Rich text flattened on load	Load with `rich_text=True` and substitute per run
Report saved with markers visible	No post-fill check	Scan again and raise before saving

Performance and scale notes

A template scan touches every cell once, which is trivial for report-sized templates. When producing hundreds of reports from one template — one per customer — load the template once, record the placeholder inventory, and for each report reload the template from bytes held in memory (load_workbook(BytesIO(template_bytes))) rather than from disk. Row insertion is the slowest operation on large sheets, because every cell below moves; for statements with thousands of lines, a template that ends at the line-item table avoids that cost. Looping over many recipients is covered in Generate One Excel Report per Region in a Loop.

Conclusion

Placeholders let the template's designer decide where values go and how they look, and let the script find them wherever they are. Inventory the markers first, assign typed values to whole-cell placeholders so formats and formulas keep working, substitute inline markers as text, expand a marked row for line items with copied styles, include headers and footers, and refuse to save while any marker remains. Keep the template under version control next to the script, because the placeholders in it are now part of the program's interface.

Frequently asked questions

Can I use Jinja2 syntax in Excel templates? The double-brace markers look like Jinja2, and the approach is similar, but Excel cells are not a text document, so a small openpyxl filler like this one is simpler than rendering the workbook XML with a template engine.

Will placeholders inside formulas be replaced? Not by this code, which treats formula cells like any other string only if they contain a marker. Put placeholders in value cells and let formulas refer to those cells instead.

How do I fill a placeholder with a date? Pass a datetime or date object for a whole-cell placeholder, and give that cell a date number format in the template. For inline text, format the date as a string first.

Can the same placeholder appear in several places? Yes. Every occurrence is replaced, which is useful for a customer name on a cover sheet, in a header and in a closing sentence.

Up one level: Generating Excel Reports from Templates — template-driven reporting end to end.
Fill an Excel Template with Python and openpyxl — the named-cell alternative to placeholders.
Populate an Excel Template Without Losing Formatting — what survives a template fill.
Find and Replace Text in Excel with openpyxl — rich text and formulas during replacement.
Keep Charts and Images When Filling an Excel Template — templates with pictures and charts.

More on report layouts & templates