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 pillar, 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 cluster 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.

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. 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.

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.

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.

Conclusion

Template injection keeps responsibilities where they belong: the .xlsx owns appearance, your script owns data. Load the prepared file, write only the data cells, leave formulas and styling alone, and save a dated copy so the template stays pristine. Avoid pandas.to_excel for this job — it rewrites the sheet and discards everything that made the template worth keeping. The two long-tail guides below drill into the two 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 pillar overview, then work through this cluster's long-tails and a related sibling: