Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Generating Excel Reports from Templates

Inject data into a prepared .xlsx with openpyxl instead of rebuilding sheets from scratch — preserve branding, formulas, and print areas, then save a dated copy.

Inside the Automating Reporting Workflows pipeline, the generate stage has two strategies. You can build a workbook from scratch every run, or you can take a workbook someone in your organization already designed — logo, brand colors, formulas, print areas, a tuned page layout — and inject only the numbers that change. This guide is about the second strategy: template injection. It is faster to write, produces output stakeholders recognize, and keeps the formatting decisions where they belong — with the person who made the template, not in your Python.

Inject data into a branded template to produce a dated copy A branded .xlsx template carrying a logo, styles and formulas stays untouched while the run's data is injected into its data cells, producing a new dated output file. template.xlsx (untouched) logo styles =SUM(...) formula inject data into data cells only report_2026-06-18.xlsx logo styles kept data filled in

Why template injection beats building from scratch

When a finance team hands you a quarterly report template, that file encodes a lot of work: merged title banners, a corporate color palette, conditional formatting rules, a SUM over the data range, a frozen header, and a print area that fits one page. Rebuilding all of that in code is brittle — every styling tweak the team makes later has to be re-translated into Python, and the two copies drift.

Template injection inverts the relationship. The .xlsx is the source of truth for appearance; your script is responsible only for data. The core pattern is three lines of intent:

  1. Load the prepared template with openpyxl's load_workbook().
  2. Write values into the specific cells that hold data, leaving everything else untouched.
  3. Save the result as a new, dated file — never overwriting the template.

Everything below is a runnable variation on that loop. The first block builds a small styled template so each example runs end to end without you supplying a file.

Build a reusable template in code

In production the template comes from a designer. Here we generate one so the rest of the page is self-contained. Install the library first:

Bash
pip install openpyxl

This block writes report_template.xlsx with a title cell, a styled header row, a formula in the totals cell, and placeholder data cells:

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

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

# Title banner
ws["A1"] = "Regional Sales Report"
ws["A1"].font = Font(size=16, bold=True, color="1F4E78")
ws.merge_cells("A1:C1")

# Report-date cell (a fixed label + a value cell we will fill)
ws["A2"] = "Report date:"
ws["A2"].font = Font(italic=True)

# Styled header row at row 4
headers = ["Region", "Units", "Revenue"]
header_fill = PatternFill("solid", fgColor="4472C4")
for col, name in enumerate(headers, start=1):
    cell = ws.cell(row=4, column=col, value=name)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# Totals row with a live formula (data goes in rows 5..9)
ws["A10"] = "Total"
ws["A10"].font = Font(bold=True)
ws["C10"] = "=SUM(C5:C9)"
ws["C10"].number_format = "#,##0.00"

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

The core pattern: load, write data cells, save a dated copy

Now treat that file as read-only input. Load it, write into the known data cells, and save under a date-stamped name so each run is archived and the template stays pristine:

Python
from datetime import date
from openpyxl import load_workbook

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

# Fill the report-date value cell next to its label
ws["B2"] = date.today().isoformat()

# Save as a NEW file — never back over the template
out = f"sales_report_{date.today():%Y%m%d}.xlsx"
wb.save(out)
print(f"Wrote {out}")

The dated filename matters: it gives you an audit trail, prevents one run from clobbering the previous one, and makes the template reusable forever. It is also what lets the same script run unattended on a schedule — each run writes its own distinct file instead of racing the last one. Treat report_template.xlsx as you would a source file under version control — read it, never write it.

Fill a table region from a DataFrame, row by row

The repeating body of most reports is a table. Map each DataFrame row to a worksheet row, starting at the first data row beneath your styled header. Because you write into existing cells, the header styling, the totals formula, and the print area all survive:

Python
from datetime import date
import pandas as pd
from openpyxl import load_workbook

# Stand-in for your real query result
data = pd.DataFrame({
    "region": ["North", "South", "West"],
    "units": [120, 95, 143],
    "revenue": [15990.00, 12047.50, 18744.25],
})

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

START_ROW = 5   # first row under the header at row 4
for offset, record in enumerate(data.itertuples(index=False)):
    r = START_ROW + offset
    ws.cell(row=r, column=1, value=record.region)
    ws.cell(row=r, column=2, value=record.units)
    ws.cell(row=r, column=3, value=record.revenue)

out = f"sales_report_{date.today():%Y%m%d}.xlsx"
wb.save(out)
print(f"Wrote {len(data)} rows to {out}")

The deeper mechanics of this loop — choosing the start row, handling DataFrames vs. plain lists, and avoiding off-by-one mistakes — are covered in Fill an Excel Template with Python and openpyxl.

Keep formulas intact — write values, not over formulas

The totals cell C10 holds =SUM(C5:C9). You never write to it; you write the data it sums and let Excel recalculate when the file opens. The rule is simple: write only to cells that hold data, never to cells that hold formulas. If you assign a number to C10, you overwrite the formula with a static value and the report stops being live.

The whole discipline of template injection comes down to knowing which cells belong to your script and which belong to the template. This is the cell map of the workbook built above — the same grid every code block on this page targets:

Which template cells your script writes and which it must leave untouched A spreadsheet grid of the report template. The merged title banner in row 1, the header row 4 and the row labels are template styling the script never touches. Rows 5 to 9 across columns A to C are data cells the script writes. Cell C10 holds the SUM formula and must never be written over. A B C 1 2 3 4 5 6 7 8 9 10 Regional Sales Report · merged A1:C1 Report date: 2026-07-15 Region Units Revenue North 120 15990.00 South 95 12047.50 West 143 18744.25 Total =SUM(C5:C9) Data cells — your script writes these Formula cell — never write over it Banner, header and labels — template styling, untouched

openpyxl does not evaluate formulas — it stores the formula string and Excel computes the result on open. If you need the computed value inside Python (rare for templates), reload with load_workbook(path, data_only=True), but note that returns the last value Excel cached, which is None for a file that has never been opened in Excel.

Why pandas.to_excel is the wrong tool for templates

It is tempting to reach for df.to_excel("report_template.xlsx", ...). Do not. pandas.to_excel with the default openpyxl engine creates a brand-new worksheet and writes a plain grid into it. It does not edit your template in place — it replaces the sheet contents wholesale, discarding the title banner, the header fill, the merged cells, the totals formula, and the print area. The result opens, but every bit of design work is gone.

ApproachPreserves template styling?What it actually does
openpyxl load_workbook + write cellsyesedits existing cells, leaves the rest untouched
pandas.to_excel(path)nowrites a fresh, unstyled sheet over your file
xlsxwriter enginenocannot open or edit an existing workbook at all

Preserving formatting is the whole point of templates, and it is subtle enough to deserve its own page: see Populate an Excel Template Without Losing Formatting.

Macro templates and the .xltx vs .xlsx question

Excel has a dedicated template format, .xltx (and .xltm for macro-enabled templates). When a user double-clicks an .xltx, Excel opens a copy rather than the original — exactly the no-overwrite behavior you want. openpyxl can load_workbook() an .xltx; just save your output as .xlsx. A plain .xlsx works equally well as a template as long as your script enforces the save-as-new-file discipline itself.

If the template carries VBA macros (an .xlsm or .xltm file), pass keep_vba=True so the macro project survives the round-trip:

Python
from openpyxl import load_workbook

# For a macro-enabled template, preserve the VBA project
wb = load_workbook("macro_template.xlsm", keep_vba=True)
ws = wb.active
ws["B2"] = "2026-06-18"
wb.save("report_with_macros.xlsm")   # macros intact
print("Saved macro-enabled report")

Without keep_vba=True, openpyxl strips the macros on save and Excel warns the file is corrupt.

Anchoring by name, not by coordinate

A template that a colleague maintains will eventually gain a row, and every hardcoded B7 in your script becomes wrong without any error. Named ranges are the fix, and they cost nothing:

Writing to a coordinate versus writing to a named range A hardcoded coordinate points at whatever cell now occupies that position, so an inserted row silently writes to the wrong place. A named range moves with the cell it labels, so the same code keeps writing to the right one. ws["B7"] = value breaks when a row is inserted fails silently no error to notice named range "Revenue" moves with the cell readable in the template survives edits
Python
from openpyxl import load_workbook
from openpyxl.utils import range_boundaries

def write_named(wb, name, value):
    if name not in wb.defined_names:
        raise KeyError(f"template has no named range {name!r}")
    target = list(wb.defined_names[name].destinations)[0]
    sheet_name, ref = target
    min_col, min_row, *_ = range_boundaries(ref.replace("$", ""))
    wb[sheet_name].cell(row=min_row, column=min_col, value=value)

wb = load_workbook("template.xlsx")
write_named(wb, "Report_Month", "March 2026")
write_named(wb, "Total_Revenue", 1_284_600.00)
wb.save("march.xlsx")

Raising when the name is missing turns a template change into an immediate, legible failure rather than into a report with an empty cell. Documenting the expected names in the script — or asserting them all in one preflight loop — makes the contract between the template and the code explicit.

Keeping the template pristine

Two rules prevent the most expensive mistake in template-based reporting. Always load the template and save under a new name, so the original is never touched. And keep the template in version control alongside the code, so a change to it is reviewable and revertible like any other change.

Python
from pathlib import Path

TEMPLATE = Path("templates/monthly_v3.xlsx")

def render(month: str, outdir="reports"):
    wb = load_workbook(TEMPLATE)                 # read-only by convention
    write_named(wb, "Report_Month", month)
    target = Path(outdir) / f"monthly_{month.replace(' ', '_').lower()}.xlsx"
    target.parent.mkdir(parents=True, exist_ok=True)
    wb.save(target)                              # never TEMPLATE
    return target

Versioning the filename — monthly_v3.xlsx — is worth the small friction. When a report's layout changes mid-year, being able to regenerate an old month with the template it was designed for is what makes historical reruns meaningful rather than confusing.

What survives a template fill, and what does not

A template's value is everything a script does not have to rebuild — and knowing which of those things a write can destroy is what keeps the output looking like the template:

What survives writing into a template, and what does not Cell styles, column widths, conditional formatting and named ranges survive an openpyxl write. Images, charts and data validation survive an openpyxl write but not a pandas rewrite of the sheet. Anything written by pandas replaces the sheet entirely. survives openpyxl writes styles and widths conditional formatting named ranges survives, but fragile images and charts data validation lost if pandas rewrites never survives to_excel the whole sheet is replaced drawings and rules go write cells instead
Python
from openpyxl import load_workbook

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

for i, (region, revenue) in enumerate(rows, start=0):        # write into the template's rows
    ws.cell(row=8 + i, column=2, value=region)
    ws.cell(row=8 + i, column=3, value=revenue)

wb.save("march.xlsx")

Writing cell by cell into an existing sheet is what preserves the template. The moment a DataFrame.to_excel targets that sheet, the whole thing is replaced and every image, rule and validation on it is gone — which is why template filling is one of the few reporting jobs where pandas is the wrong tool for the final write.

Clearing last month's data first

A template filled repeatedly accumulates: this month has fewer rows than last, and the leftovers sit below the new data looking exactly like real figures. Clearing the block before writing removes the problem entirely:

Python
def clear_block(ws, first_row, last_row, columns):
    for row in range(first_row, last_row + 1):
        for column in columns:
            ws.cell(row=row, column=column, value=None)

clear_block(ws, first_row=8, last_row=ws.max_row, columns=range(2, 7))

Clearing values rather than deleting rows is deliberate: delete_rows would take the template's formatting and any conditional rules with it, while setting values to None leaves the styled empty rows ready for next month.

Templates are a contract

A template and the script that fills it are two halves of one agreement: the script promises to write into named places, and the template promises those places will exist. Writing that agreement down — the named ranges, the sheets, the block that gets cleared — and asserting it in a preflight check is what stops a well-meaning layout change breaking next month's report silently.

Preflight the template

Every template-based job should start by asserting that the template exists, that every sheet it expects is present, and that every named range it writes to is defined — failing with the missing names listed. A template that has been edited otherwise produces a report with empty cells where figures should be: output that looks complete and is not. Two dozen lines of checking at the top of the job converts that silent failure into a message someone can act on before anything is delivered.

Ranges come from the data

The habit that keeps a monthly report correct is deriving every range, row count and column position from what was just written rather than from a number typed once. ws.max_row after the write, a header map built from row one, a table reference rebuilt on each run — each of those removes a class of failure that is silent rather than loud. A hardcoded range does not raise when the data outgrows it; it simply stops covering the rows nobody looks at, and the report is wrong in a way that takes months to notice. Deriving costs a line and removes the whole category.

Version the template with the code

A template is an input to the job in the same way a source file is, and treating it as one solves several problems at once. Keeping it in the repository means a layout change is reviewable, revertible and visible in the history; naming it with a version means an old month can be regenerated with the template it was designed for.

The alternative — a template living on a shared drive, edited in place — produces reports whose appearance changes without any change to the code, which is among the more confusing things to debug. When the template and the script that fills it move together, the contract between them stays explicit and a break in it is caught at review rather than at month end.

Frequently asked questions

Can I add more rows than the template's formula range covers? Yes, but extend the formula too. If data fills rows 5–12 and the totals formula only sums C5:C9, update it: ws["C13"] = "=SUM(C5:C12)". Better, size the template's data region for your largest expected run, or compute the range in code from your row count.

Does openpyxl preserve charts and images in the template? Modern openpyxl preserves most charts and images on a load-edit-save round-trip, but it has historically dropped some objects (pivot caches, certain chart types). Write only data cells, keep the template simple, and open one output file to verify before trusting the pipeline. The formatting preservation page covers exactly what survives.

Should the template live in my repo? Yes. Commit it alongside the script and treat it as code. That way a styling change is a reviewable diff, and every run uses a known version.

Can openpyxl fill a template that spans several sheets? Yes. load_workbook() returns the whole workbook; select each sheet by name with wb["SheetName"] and write into its data cells independently. Cross-sheet formulas in the template keep working because openpyxl stores the formula strings untouched. When a report grows into a linked front page plus detail tabs, the multi-sheet dashboards guide covers structuring it.

Key takeaways

  • The template owns appearance; your script owns data. Load the prepared .xlsx, write only the data cells, and leave the banner, styles, merged cells, and print area to the person who designed them.
  • Always save a dated copy, never over the template. A date-stamped filename gives you an archive, protects the source file, and keeps the pipeline safe to run on a schedule.
  • Write values, never over formulas. Assign into the cells a SUM reads, not the SUM cell itself, so the report stays live and recalculates on open.
  • Skip pandas.to_excel for templates. It replaces the sheet wholesale and discards every bit of design work; reach for load_workbook() plus cell writes instead.
  • Pass keep_vba=True whenever the template is macro-enabled, or openpyxl strips the VBA project on save.

The two detailed guides below drill into the skills this pattern depends on: filling a table region cleanly, and doing it without losing a single style.

Where to go next

Start with the overview, then work through the detailed guides below and a related sibling: