Filling a template workbook

Why templates beat from-scratch reports

Excel templates can have logos, frozen panes, formulas, colours, named ranges — all the spreadsheety polish. With openpyxl you can open the template, plug in values, and save a copy — keeping every bit of formatting.

The script

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

template = "templates/invoice.xlsx"
orders = pd.read_csv("orders_to_invoice.csv")

for _, row in orders.iterrows():
    wb = load_workbook(template)
    ws = wb.active

    ws["B2"] = f"Invoice {row['invoice_no']}"
    ws["B3"] = row["customer"]
    ws["B4"] = row["billing_address"]
    ws["B5"] = date.today().isoformat()
    ws["E10"] = row["quantity"]
    ws["E11"] = row["unit_price"]
    ws["E13"] = row["tax_rate"]
    # formulas in the template will recalc when the file is opened

    out = f"invoices/{row['invoice_no']}.xlsx"
    wb.save(out)
    print(f"  wrote {out}")

Use named ranges to make this readable

In Excel: Formulas → Define Name. Then in Python:

wb["TemplateSheet"]["CustomerName"]   # if you defined CustomerName as a named range

(For most everyday cases, the ws["B3"] style is fine.)