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

Generate Invoices from an Excel Template with Python

Produce one invoice workbook per customer from a designed Excel template — named cells instead of hard-coded addresses, money calculated with Decimal and a stated VAT rounding rule, invoice numbers that never repeat or skip, a fixed block of line rows with an overflow check, a register of everything issued and a PDF for each invoice.
Report layouts & templatesopenpyxl

Invoices are the template job where mistakes cost the most: a wrong total, a reused number or a missing line is a problem with a customer and an accountant, not just a cosmetic flaw. The mechanics are the same as any template fill — load, write cells, save — but invoices add money arithmetic, numbering rules and a paper trail. This guide, part of Generating Excel Reports from Templates, builds an invoice run that uses named cells, exact decimal arithmetic, gap-free numbers and a register, and produces a workbook and a PDF per customer.

The invoice template and its named cells The template has fields on the right for the invoice number, date and due date, the customer's name and address on the left, a block of ten line-item rows with description, quantity, unit price and amount, and three totals at the bottom for net, VAT and total due. Each field and the line block has a defined name that the Python code uses instead of a cell address. A B C D E 1 A D E F 2 3 Invoice no. inv_number 3 4 Date inv_date 4 7 customer_name 5 11 Description Qty Unit price Amount 6 12–21 lines (10 rows) 7 23 Net net_total 8 24 VAT 20% vat_total 9 25 Total due gross_total code refers to names; the designer can move cells without breaking the run

Prerequisites

Bash
pip install openpyxl pandas

A designer would normally build the template in Excel. For a reproducible example, this creates one with the same structure — styles, number formats and the defined names the code relies on:

Python
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path
import csv
import datetime as dt

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font
from openpyxl.workbook.defined_name import DefinedName

def make_template(path="invoice_template.xlsx"):
    wb = Workbook()
    ws = wb.active
    ws.title = "Invoice"
    ws["A1"] = "INVOICE"
    ws["A1"].font = Font(size=20, bold=True)
    for row, label in [(3, "Invoice no."), (4, "Date"), (5, "Due")]:
        ws.cell(row, 5, label)
    ws["F4"].number_format = ws["F5"].number_format = "yyyy-mm-dd"
    for col, label in zip("ADEF", ["Description", "Qty", "Unit price", "Amount"]):
        ws[f"{col}11"] = label
        ws[f"{col}11"].font = Font(bold=True)
    for row in range(12, 22):
        ws[f"E{row}"].number_format = ws[f"F{row}"].number_format = "#,##0.00"
    for row, label in [(23, "Net"), (24, "VAT 20%"), (25, "Total due")]:
        ws.cell(row, 5, label)
        ws.cell(row, 6).number_format = "#,##0.00"
    ws["F25"].font = Font(bold=True)
    ws.column_dimensions["A"].width = 34
    for col in "EF":
        ws.column_dimensions[col].width = 14
    names = {"inv_number": "$F$3", "inv_date": "$F$4", "due_date": "$F$5",
             "customer_name": "$A$7", "customer_address": "$A$8", "lines": "$A$12:$F$21",
             "net_total": "$F$23", "vat_total": "$F$24", "gross_total": "$F$25"}
    for name, ref in names.items():
        wb.defined_names[name] = DefinedName(name, attr_text=f"Invoice!{ref}")
    wb.save(path)

make_template()

Step 1 — Refer to cells by name

Hard-coding "F3" ties the code to today's layout; the first time someone adds a logo row, every invoice is filled one row off. Defined names move with the cells when the designer edits the template, so the code keeps working. A small helper resolves a name to its cell or range:

Python
def named(wb, name):
    sheet, ref = next(iter(wb.defined_names[name].destinations))
    return wb[sheet][ref.replace("$", "")]

wb = load_workbook("invoice_template.xlsx")
print(named(wb, "inv_number").coordinate, len(named(wb, "lines")))
# F3 10

A missing name raises KeyError with the name in it — much clearer than an invoice with the total written into an empty cell. The general technique is covered in Fill an Excel Template with Python and openpyxl.

Step 2 — Calculate money with Decimal and one rounding rule

Floats cannot represent most cent amounts exactly, and a sum of floats can end in ...99999. Use Decimal for every amount, round each line to cents with ROUND_HALF_UP, and decide where VAT is rounded — per line or once on the net total — because the two can disagree:

Python
CENT = Decimal("0.01")
VAT_RATE = Decimal("0.20")

def money(value):
    return Decimal(value).quantize(CENT, rounding=ROUND_HALF_UP)

lines = [
    {"description": "Paper A4, box", "qty": 3, "unit": Decimal("4.13")},
    {"description": "Toner cartridge", "qty": 1, "unit": Decimal("38.43")},
    {"description": "Labels", "qty": 7, "unit": Decimal("2.37")},
    {"description": "Pens, 10 pack", "qty": 2, "unit": Decimal("3.07")},
    {"description": "Stapler", "qty": 1, "unit": Decimal("7.99")},
]
for line in lines:
    line["amount"] = money(line["qty"] * line["unit"])

net = sum(line["amount"] for line in lines)
vat_on_total = money(net * VAT_RATE)
vat_per_line = sum(money(line["amount"] * VAT_RATE) for line in lines)
print(net, vat_on_total, vat_per_line)
# 81.54 16.31 16.32

One cent apart on a five-line invoice. Neither is wrong in general; what matters is using the same rule as the accounting system that receives the invoice, so the two never disagree. This guide rounds VAT on the total. Why Excel and Python round differently in the first place is covered in Match Excel Rounding in pandas.

Two VAT rules, one cent apart Five lines with a net total of 81.54. Rounding VAT on the net total gives 16.31. Rounding VAT on each line and adding the results gives 16.32, because each line's small rounding up adds together. Either rule is legitimate; the invoice must match the rule the accounting system uses. VAT on the net total 81.54 × 20% = 16.308 rounded once: 16.31 one rounding step − line VAT not shown VAT per line, then summed 2.48 + 7.69 + 3.32 + 1.23 + 1.60 total: 16.32 each line shows its VAT − rounding errors add up

Step 3 — Fill the template, with a capacity check

The line block has ten rows. An order with twelve lines must not silently lose two of them, so check capacity before writing anything. Write the totals as values rather than formulas: openpyxl saves formulas without results, so previews and programs that read the file without recalculating would show empty totals:

Python
def fill_invoice(template, invoice, out_path):
    wb = load_workbook(template)
    block = named(wb, "lines")
    if len(invoice["lines"]) > len(block):
        raise ValueError(f"{invoice['number']}: {len(invoice['lines'])} lines, template holds {len(block)}")
    named(wb, "inv_number").value = invoice["number"]
    named(wb, "inv_date").value = invoice["date"]
    named(wb, "due_date").value = invoice["date"] + dt.timedelta(days=30)
    named(wb, "customer_name").value = invoice["customer"]
    named(wb, "customer_address").value = invoice["address"]
    for row, line in zip(block, invoice["lines"] + [None] * len(block)):
        values = [None] * 6 if line is None else [
            line["description"], None, None, line["qty"], line["unit"], line["amount"]]
        for cell, value in zip(row, values):
            cell.value = value                           # clears unused rows too
    named(wb, "net_total").value = invoice["net"]
    named(wb, "vat_total").value = invoice["vat"]
    named(wb, "gross_total").value = invoice["net"] + invoice["vat"]
    wb.save(out_path)

openpyxl accepts Decimal values and stores them as numbers, so the cells keep their #,##0.00 format and can be summed in Excel. When orders legitimately exceed the block, add a continuation sheet or a longer template variant rather than inserting rows, which openpyxl does not do cleanly; Replace Placeholders in an Excel Template with Python shows how to expand a repeating row when you must.

Step 4 — Number invoices from a register

Invoice numbers must be unique and, in many jurisdictions, sequential without gaps. Keep a register of every invoice issued and derive the next number from it. Write the invoice first, then append it to the register, and never overwrite an existing invoice file — if the run dies between the two steps, the next run stops at the existing file instead of issuing a duplicate:

Python
REGISTER = Path("invoice_register.csv")
FIELDS = ["number", "date", "customer", "net", "vat", "gross", "file"]

def next_number(prefix="INV-", first=1041):
    if not REGISTER.exists():
        return f"{prefix}{first:06d}"
    with REGISTER.open(newline="") as f:
        last = max(int(row["number"].removeprefix(prefix)) for row in csv.DictReader(f))
    return f"{prefix}{last + 1:06d}"

def issue(customer, address, lines, date, out_dir=Path("invoices")):
    out_dir.mkdir(exist_ok=True)
    net = sum(line["amount"] for line in lines)
    invoice = {"number": next_number(), "date": date, "customer": customer,
               "address": address, "lines": lines, "net": net, "vat": money(net * VAT_RATE)}
    out = out_dir / f"{invoice['number']}.xlsx"
    if out.exists():
        raise FileExistsError(f"{out} exists but is not registered; check before continuing")
    fill_invoice("invoice_template.xlsx", invoice, out)
    new = not REGISTER.exists()
    with REGISTER.open("a", newline="") as f:
        writer = csv.DictWriter(f, FIELDS)
        if new:
            writer.writeheader()
        writer.writerow({"number": invoice["number"], "date": date.isoformat(), "customer": customer,
                         "net": net, "vat": invoice["vat"], "gross": net + invoice["vat"], "file": out.name})
    return out

print(issue("Harbour Stationers Ltd", "12 Quay Street, Bristol", lines, dt.date(2026, 9, 30)))
print(issue("Northfield Primary School", "Mill Lane, Leeds", lines[:2], dt.date(2026, 9, 30)))
# invoices/INV-001041.xlsx
# invoices/INV-001042.xlsx

Run the batch from one process at a time; two runs reading the register simultaneously would pick the same number. The register doubles as the control total for the batch: its gross column should add up to what the accounting export expects.

Issuing one invoice The next number comes from the register of issued invoices. Line amounts and VAT are calculated with Decimal. The template is filled through its defined names after a capacity check. The workbook is saved under the invoice number, refusing to overwrite, and only then is the invoice appended to the register. PDFs are produced for the whole batch at the end. Next number from the register Decimal amounts one VAT rule Fill by name capacity checked Save never overwrite Register then PDF batch

Step 5 — Check each invoice and export PDFs

Read every invoice back and check that its lines add up to its net total before anything is sent. Then convert the batch to PDF in a single LibreOffice call:

Python
def check_invoice(path):
    wb = load_workbook(path)
    amounts = [row[5].value for row in named(wb, "lines") if row[5].value is not None]
    net = named(wb, "net_total").value
    assert money(sum(Decimal(str(a)) for a in amounts)) == money(Decimal(str(net))), path

for path in sorted(Path("invoices").glob("*.xlsx")):
    check_invoice(path)
print("all invoices add up")
Python
import subprocess

files = sorted(str(p) for p in Path("invoices").glob("*.xlsx"))
subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "--outdir", "invoices"] + files,
               check=True, timeout=600)

Values read back as floats, so convert through str before comparing as Decimal. PDF page setup — margins, fitting to one page — lives in the template, and Fit an Excel Sheet to One Page Wide When Exporting to PDF covers it.

Common pitfalls

  • Overwriting the template. Always save to a new path. One accidental wb.save(template) bakes a customer's data into every future invoice.
  • Floats for money. 0.1 + 0.2 is not 0.3, and totals built from floats occasionally end one cent off. Keep amounts as Decimal until the moment they are written.
  • Reissuing a number after a failed run. Deleting the half-written invoice and rerunning is fine only if it never left the building. Once sent, correct it with a credit note, never a reused number.
  • Date as text. Write datetime.date objects, not strings, so the template's date format applies and the cell sorts correctly.

Conclusion

A dependable invoice run fills a designed template through defined names, calculates every amount with Decimal under one stated VAT rounding rule, checks the line block's capacity, writes totals as values, takes numbers from a register it appends to only after the file is saved, and verifies each invoice before converting the batch to PDF.

Frequently asked questions

How do I fill an Excel invoice template with Python? Load the template with openpyxl, look up each field through a defined name such as inv_number or customer_name, write the values, fill the line-item rows, clear the unused ones and save under a new file name. Never save over the template.

Should an invoice template use formulas or values for the totals? Write the totals as values calculated in Python. openpyxl saves formulas without results, so viewers that do not recalculate — file previews, mobile apps, other programs reading the file — show empty totals. Values also guarantee the invoice shows exactly the amounts you recorded.

Should VAT be rounded per line or on the invoice total? Either can be correct depending on the rules you follow, but the two can differ by a cent. In our example VAT per line summed to 16.32 and VAT on the net total was 16.31. Pick one rule, apply it consistently and match what the accounting system does.

How do I make invoice numbers sequential without gaps or duplicates? Derive the next number from a register of issued invoices, write the invoice, then append it to the register. Refuse to overwrite an existing invoice file, so a crash between the two steps stops the next run instead of reusing a number.

More on report layouts & templates

More on report layouts & templates →