Fill an Excel Template with Python and openpyxl

This guide is the hands-on companion to Generating Excel Reports from Templates. The job is concrete: you have a branded .xlsx, and on every run you need to drop today's data into it and emit a fresh file. With openpyxl that is a load, a handful of cell assignments, a loop over your data rows, and a save under a new name. The care is in the details — the right start row, merged cells, and not writing over formulas.

Prerequisites and install

You need Python 3.8+ and openpyxl:

pip install openpyxl pandas

pandas is optional — the table-fill loop works from any iterable — but it is the usual source of report data, so we show both.

Step 1: Generate a sample template

In production this file is handed to you. Here we build it so every later block runs. It has a merged title, a label/value pair for the report date, a styled header row at row 4, and a totals formula at row 10:

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

wb = Workbook()
ws = wb.active
ws.title = "Report"

ws["A1"] = "Weekly Orders Report"
ws["A1"].font = Font(size=16, bold=True, color="1F4E78")
ws.merge_cells("A1:C1")

ws["A2"] = "Report date:"
ws["A2"].font = Font(italic=True)

header_fill = PatternFill("solid", fgColor="4472C4")
for col, name in enumerate(["Customer", "Orders", "Total"], start=1):
    c = ws.cell(row=4, column=col, value=name)
    c.font = Font(bold=True, color="FFFFFF")
    c.fill = header_fill
    c.alignment = Alignment(horizontal="center")

ws["A10"] = "Total"
ws["A10"].font = Font(bold=True)
ws["C10"] = "=SUM(C5:C9)"          # live formula over the data range
ws["C10"].number_format = "#,##0.00"

wb.save("orders_template.xlsx")
print("Wrote orders_template.xlsx")

Step 2: Load the template and set named cells

Open the file with load_workbook, select the sheet, and assign the one-off cells: the report date and anything else that lives at a fixed address. Writing ws["B2"] = value sets the value and leaves that cell's existing style untouched:

from datetime import date
from openpyxl import load_workbook

wb = load_workbook("orders_template.xlsx")
ws = wb["Report"]

ws["B2"] = date.today().isoformat()    # the report-date value cell

print("Date cell set to", ws["B2"].value)

These are your "named" cells in the informal sense — known fixed addresses for known fields. If you prefer real Excel named ranges, look them up with wb.defined_names and resolve the destination, but fixed addresses are simpler and just as robust for a template you control.

Step 3: Fill the repeating data table

The body of the report starts at the first row under the header. Our header is row 4, so data begins at row 5. Loop your records, computing each target row as START_ROW + offset:

from datetime import date
from openpyxl import load_workbook

# Your data, as a list of tuples (or DataFrame rows — see below)
rows = [
    ("Acme Co", 14, 2380.00),
    ("Globex", 9, 1755.50),
    ("Initech", 21, 3990.75),
]

wb = load_workbook("orders_template.xlsx")
ws = wb["Report"]
ws["B2"] = date.today().isoformat()

START_ROW = 5
for offset, (customer, orders, total) in enumerate(rows):
    r = START_ROW + offset
    ws.cell(row=r, column=1, value=customer)
    ws.cell(row=r, column=2, value=orders)
    ws.cell(row=r, column=3, value=total)

wb.save("orders_report.xlsx")
print(f"Filled {len(rows)} rows starting at row {START_ROW}")

From a DataFrame, the only change is the source of the loop. Use itertuples so column access stays explicit:

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame({
    "customer": ["Acme Co", "Globex", "Initech"],
    "orders": [14, 9, 21],
    "total": [2380.00, 1755.50, 3990.75],
})

wb = load_workbook("orders_template.xlsx")
ws = wb["Report"]

START_ROW = 5
for offset, rec in enumerate(df.itertuples(index=False)):
    r = START_ROW + offset
    ws.cell(row=r, column=1, value=rec.customer)
    ws.cell(row=r, column=2, value=rec.orders)
    ws.cell(row=r, column=3, value=rec.total)

wb.save("orders_report.xlsx")
print(f"Wrote {len(df)} rows from DataFrame")

Step 4: Save as a new file — do not overwrite the template

Notice every save above targets orders_report.xlsx, never orders_template.xlsx. The template is input; overwrite it once and the next run has nowhere to start from. Make it impossible to get wrong by stamping the output name and guarding against clobbering the source:

from datetime import date
from pathlib import Path
from openpyxl import load_workbook

TEMPLATE = "orders_template.xlsx"
out = f"orders_report_{date.today():%Y%m%d}.xlsx"
assert Path(out).resolve() != Path(TEMPLATE).resolve(), "Refusing to overwrite the template"

wb = load_workbook(TEMPLATE)
wb["Report"]["B2"] = date.today().isoformat()
wb.save(out)
print(f"Wrote {out}")

Common pitfalls

A note on merged cells

A merged range in Excel is backed by a single real cell — the top-left anchor. Assigning to any other cell in the range silently does nothing visible. If your template merges, say, A1:C1 for a title, write to A1. To find anchors programmatically, inspect ws.merged_cells.ranges.

Frequently asked questions

How do I fill more rows than the template's formula covers? Track your row count and rewrite the formula to match: ws[f"C{last+1}"] = f"=SUM(C5:C{last})". Or size the template's data block for your largest run so the formula never needs touching.

Can I clear old data before filling? If the template ships empty there is nothing to clear. If it carries sample rows, set those cells to None first, but leave the styling alone — assigning None to cell.value clears the value without disturbing the format.

Do I need data_only=True when loading? No, and avoid it here. data_only=True returns Excel's last cached values and reading mode loses formulas. Load normally so formulas pass through untouched.

What if the sheet name is unknown? Use wb.active for the default sheet, or wb.sheetnames to list them and pick by position. Hard-coding the name is fine when you own the template.

Conclusion

Filling a template is load, set the fixed cells, loop the data table from the correct start row, and save a new dated file. The mistakes are predictable — overwriting the template, an off-by-one start row, writing to a merged non-anchor cell, or clobbering a formula — and every one is avoided by writing only to data cells and never to the template path. With those guardrails the same script runs unattended for years against the same designer-owned file.

Where to go next

• Generating Excel Reports from Templates — the cluster overview: why template injection beats building from scratch.
• Populate an Excel Template Without Losing Formatting — exactly which styles survive the load-edit-save round-trip.
• openpyxl: Append Data to an Existing Excel Sheet — when the table grows beyond a fixed region and you append instead.