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

Repeat a Template Block for Each Record in an Excel Report

Stamp a designed block of rows — a customer statement, a card, a mini-report — once per record down a single sheet with openpyxl, copying styles, merged cells, row heights and conditional formatting, translating formulas to their new rows, growing the block for records with more line items and starting each record on a new printed page.
Report layouts & templatesopenpyxl

Some reports are not tables but a repeated layout: a statement per customer, a summary card per project, a label per parcel. The designer draws one block in Excel, and the report needs that block once per record, down the sheet, each with its own values. openpyxl has no "copy range" function, and a naive cell-by-cell copy loses merged titles, highlight rules and formulas that point at the wrong rows. This guide, part of Generating Excel Reports from Templates, builds a stamping function that copies everything the block needs and grows it for records with more line items. The output was checked in LibreOffice for values, styles and page breaks.

The template block, in three bands The template block is split into three bands. Rows 1 to 3 are the header with the customer name, account and column headings, copied once per record. Row 4 is the item row, copied once for each line item. Row 5 is the total, copied once, with its SUM formula rewritten to cover the item rows actually created. Rows 1–3: header {customer}, {account}, headings — once per record Row 4: item row {item} {qty} {price} =B4*C4 — once per item Row 5: total =SUM(…) rewritten for the rows created

Prerequisites

Bash
pip install openpyxl

The template is a sheet named Block whose placeholders are whole-cell markers such as {customer}. In practice a designer builds it in Excel; this creates an equivalent one, including a merged, filled title row and a highlight rule on the total:

Python
import copy
import re

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side
from openpyxl.formatting.rule import CellIsRule
from openpyxl.formula.translate import Translator
from openpyxl.worksheet.pagebreak import Break

def make_template(path="statement_template.xlsx"):
    wb = Workbook()
    ws = wb.active
    ws.title = "Block"
    thin = Side(style="thin", color="FF9AA5B1")
    ws["A1"] = "{customer}"
    ws.merge_cells("A1:D1")
    ws["A1"].font = Font(size=14, bold=True, color="FFFFFFFF")
    ws["A1"].fill = PatternFill("solid", fgColor="FF1F4E79")
    ws.row_dimensions[1].height = 24
    ws.append(["Account", "{account}", "Period", "{period}"])
    ws.append(["Item", "Qty", "Price", "Amount"])
    for cell in ws[3]:
        cell.font = Font(bold=True)
        cell.border = Border(bottom=thin)
    ws.append(["{item}", "{qty}", "{price}", "=B4*C4"])                  # the repeating row
    ws.append([None, None, "Total", "=SUM(D4:D4)"])
    for ref in ("C4", "D4", "D5"):
        ws[ref].number_format = "#,##0.00"
    ws["C5"].font = ws["D5"].font = Font(bold=True)
    ws["D5"].border = Border(top=thin)
    ws.conditional_formatting.add("D5", CellIsRule(operator="greaterThan", formula=["100"],
                                                   fill=PatternFill("solid", fgColor="FFFCE4D6")))
    for col, width in zip("ABCD", [28, 8, 12, 14]):
        ws.column_dimensions[col].width = width
    wb.save(path)

make_template()

Step 1 — Copy a band of rows with everything attached

A band copy has five parts: values, styles, row heights, merged ranges and conditional formatting. Placeholders are replaced with typed values from the record, and formulas are moved to their new position with Translator, which adjusts relative references the same way Excel does when you copy a cell:

Python
PLACEHOLDER = re.compile(r"^\{(\w+)\}$")

def copy_rows(src, first, last, dst, top, values):
    offset = top - first
    for row in src.iter_rows(min_row=first, max_row=last):
        for cell in row:
            target = dst.cell(cell.row + offset, cell.column)
            value = cell.value
            if isinstance(value, str) and (m := PLACEHOLDER.match(value)):
                value = values[m.group(1)]
            elif isinstance(value, str) and value.startswith("="):
                value = Translator(value, origin=cell.coordinate).translate_formula(target.coordinate)
            target.value = value
            if cell.has_style:
                target._style = copy.copy(cell._style)
    for r in range(first, last + 1):
        if src.row_dimensions[r].height:
            dst.row_dimensions[r + offset].height = src.row_dimensions[r].height
    for rng in src.merged_cells.ranges:
        if first <= rng.min_row and rng.max_row <= last:
            moved = copy.copy(rng); moved.shift(0, offset)
            dst.merge_cells(moved.coord)
    for cf in src.conditional_formatting:
        for rng in cf.sqref.ranges:
            if first <= rng.min_row and rng.max_row <= last:
                moved = copy.copy(rng); moved.shift(0, offset)
                for rule in cf.rules:
                    dst.conditional_formatting.add(moved.coord, copy.copy(rule))
    return last + offset + 1

cell._style is an index into the workbook's shared style table, so copying it is cheap and exact — but only within the same workbook. That is why the output sheet is created inside the template workbook and the Block sheet is removed at the end; copying _style into a different workbook points at the wrong styles. Replacing whole-cell placeholders with typed values, rather than substituting text, is covered in Replace Placeholders in an Excel Template with Python.

Step 2 — Grow the block for each record's items

Records rarely have the same number of lines. Copy the header band once, the item row once per item, and the total band once — then rewrite the total's SUM to cover exactly the item rows created, because a translated =SUM(D4:D4) would only ever cover one row:

Python
def stamp_record(src, dst, top, record):
    top = copy_rows(src, 1, 3, dst, top, record)                        # header
    first_item = top
    for item in record["items"]:
        top = copy_rows(src, 4, 4, dst, top, item)                      # one row per item
    total_row = top
    top = copy_rows(src, 5, 5, dst, top, record)                        # total
    dst[f"D{total_row}"] = f"=SUM(D{first_item}:D{total_row - 1})" if record["items"] else 0
    return top

With no items the range would be backwards, so the total is written as 0 instead. The same idea extends to any band structure: a sub-header per group, a notes row that appears only when a record has notes.

Step 3 — Stamp every record, one per printed page

The records here are three customers with two, one and four items. After each record, a row break starts the next one on a new page, and a blank row separates them on screen:

Python
records = [
    {"customer": "Harbour Stationers Ltd", "account": "C-1041", "period": "Sep 2026",
     "items": [{"item": "Paper A4", "qty": 3, "price": 4.13}, {"item": "Toner", "qty": 1, "price": 38.43}]},
    {"customer": "Northfield Primary", "account": "C-1042", "period": "Sep 2026",
     "items": [{"item": "Labels", "qty": 7, "price": 2.37}]},
    {"customer": "Quayside Dental", "account": "C-1043", "period": "Sep 2026",
     "items": [{"item": "Stapler", "qty": 1, "price": 7.99}, {"item": "Paper A4", "qty": 30, "price": 4.13},
               {"item": "Pens", "qty": 2, "price": 3.07}, {"item": "Labels", "qty": 4, "price": 2.37}]},
]
Python
wb = load_workbook("statement_template.xlsx")
block = wb["Block"]
out = wb.create_sheet("Statements")
for col, dim in block.column_dimensions.items():
    out.column_dimensions[col].width = dim.width
top = 1
for i, record in enumerate(records):
    top = stamp_record(block, out, top, record)
    if i < len(records) - 1:
        out.row_breaks.append(Break(id=top - 1))                       # new page per record
        top += 1                                                        # blank spacer row
wb.remove(block)
wb.save("statements.xlsx")
print([b.id for b in out.row_breaks.brk], out.max_row, sorted(str(r) for r in out.merged_cells.ranges))
# [6, 12] 21 ['A14:D14', 'A1:D1', 'A8:D8']

Break(id=6) puts a page break after row 6, the last row of the first statement. Converted with LibreOffice, the sheet produced three pages, one per customer, and every total was recalculated correctly: 50.82, 16.59 and 147.51.

Three records stamped down one sheet The Statements sheet after stamping. Harbour Stationers occupies rows 1 to 6 with two items and a total of 50.82. Northfield Primary occupies rows 8 to 12 with one item and a total of 16.59. Quayside Dental starts at row 14 with four items and a total of 147.51, which the copied highlight rule marks because it is over 100. Page breaks follow rows 6 and 12. A B C D E 1 A B C D 2 1 Harbour Stationers Ltd 3 4–5 2 items =B4*C4 … 4 6 Total 50.82 5 8 Northfield Primary 6 12 Total 16.59 7 14 Quayside Dental 8 17–20 4 items =B17*C17 … 9 21 Total 147.51 each total's SUM covers its own item rows; only 147.51 is over the highlight threshold

Step 4 — Check the result before sending it

Stamped layouts fail in quiet ways: a total that sums the wrong rows, a record that lost its merged title. Read the output back and check the structure against the records:

Python
check = load_workbook("statements.xlsx")["Statements"]
titles = [check.cell(r.min_row, 1).value for r in sorted(check.merged_cells.ranges, key=lambda r: r.min_row)]
assert titles == [r["customer"] for r in records], titles

totals = [c.value for c in check["D"] if isinstance(c.value, str) and c.value.startswith("=SUM")]
print(totals)
# ['=SUM(D4:D5)', '=SUM(D11:D11)', '=SUM(D17:D20)']

openpyxl does not calculate formulas, so check the calculated totals after a LibreOffice or Excel recalculation when the numbers matter — or write totals as values computed in Python, as Generate Invoices from an Excel Template with Python does.

Blocks on one sheet, a sheet each, or a file each? Use stamped blocks on one sheet when records are short and readers scroll or print them together. Use one sheet per record, made with copy_worksheet, when records are long or need their own tab, remembering to re-add conditional formatting. Use one file per record when each goes to a different recipient. How will each record be read? together blocks on one sheet row break per record on their own tab copy_worksheet re-add CF rules sent separately one file each loop over the template

Other ways to repeat a layout

When each record deserves its own tab, wb.copy_worksheet(wb["Block"]) duplicates the whole sheet within the workbook. In a test it kept values, styles, merged cells, row heights and column widths, but the copy had no conditional formatting rules, so re-add them with the loop from Step 1. When each record goes to a different person, one workbook per record is simpler still — see Generate One Excel Report per Region in a Loop.

Common pitfalls

  • Copying styles across workbooks. _style indices only make sense in the workbook they came from. Stamp inside the template workbook, or copy font, fill, border, alignment and number_format individually.
  • Data validation and images. The band copy above does not move drop-down lists or pictures. Re-create data validation for each band's range, and anchor images again at the new rows.
  • Formulas pointing outside the block. A reference such as =$G$1*D4 keeps $G$1 fixed, which is usually right for a rate cell; a relative reference to a cell outside the block moves with it and ends up pointing at another record.
  • Very long outputs. Thousands of stamped records make a slow, heavy sheet. Past a few hundred, one file or sheet per record is easier on everyone.

Conclusion

Split the template block into bands, copy each band with its values, styles, heights, merged ranges and conditional formatting, move formulas with Translator, repeat the item row per line and rewrite the total's range, and add a row break after each record. Work inside the template workbook so styles copy exactly, and check the stamped structure before sending it.

Frequently asked questions

How do I copy a range of rows with formatting in openpyxl? openpyxl has no built-in range copy. Loop over the source cells, write each value to the target cell and copy the style with target._style = copy.copy(cell._style), then shift and re-create merged ranges, row heights and conditional formatting yourself. Do it within one workbook, because style indices belong to the workbook.

How do I adjust formulas when copying template rows? Use openpyxl.formula.translate.Translator. Translator("=B4C4", origin="D4").translate_formula("D11") returns "=B11C11", moving relative references the same way Excel does when you copy a cell, while absolute references with $ stay fixed.

How do I handle records with different numbers of line items? Split the template block into a header, one repeating item row and a footer. Copy the header once, the item row once per item and the footer once, then write the footer's SUM formula over the rows you actually created.

Does copy_worksheet copy conditional formatting? No. In a test, wb.copy_worksheet kept values, styles, merged cells, row heights and column widths but the copy had no conditional formatting rules. Re-add them to the copy if the block depends on them.

More on report layouts & templates

More on report layouts & templates →