Insert Page Breaks Between Groups with openpyxl

Make each region, customer or department start on a new printed page: find where each group ends, add manual row breaks with openpyxl, repeat the header on every page, add column breaks for wide sheets, clear old breaks, do the same with XlsxWriter, and prove the result by rendering a PDF and checking which group starts each page.

Cell styling & layout Report layouts & templates PDF export

A printed report that lists every region on one long sheet is hard to hand out: North's last rows share a page with South's first ones, so nobody can take "their" pages. The fix in Excel is manual page breaks — one after the last row of each group — so every region, customer or cost centre starts on a fresh page that can be distributed separately. Placing them by hand is tedious and has to be redone every month as group sizes change; a script can place them exactly, every time. This page adds the breaks with openpyxl, sets up the page so every page is readable on its own, covers XlsxWriter and column breaks, and — because a page break is only correct if the printout is — checks the result by rendering the sheet to PDF. It belongs to Styling Excel Cells with openpyxl.

Prerequisites

Bash

pip install openpyxl pandas pypdf

regional_customers.xlsx lists customers for four regions with very different sizes — 23, 9, 31 and 4 rows — sorted by region. LibreOffice (soffice) renders the PDF in Step 5; pypdf reads it back.

Step 1 — Find where each group ends

Python

from openpyxl import load_workbook

wb = load_workbook("regional_customers.xlsx")
ws = wb.active
group_col = 1
ends = []
for r in range(2, ws.max_row + 1):
    this = ws.cell(r, group_col).value
    following = ws.cell(r + 1, group_col).value if r < ws.max_row else None
    if this != following:
        ends.append((this, r))
print(ends)

A group ends where the next row's value differs. The data must be sorted by the group column, or a region that appears twice would get two separate blocks. The last group needs no break after it — the sheet simply ends — which is why the next step skips it.

Step 2 — Add a row break after each group

Python

from openpyxl.worksheet.pagebreak import Break

for name, last_row in ends[:-1]:
    ws.row_breaks.append(Break(id=last_row))
print("breaks after rows:", [b.id for b in ws.row_breaks.brk])

A Break(id=n) is a manual break after row n, so passing each group's last row starts the next group on a new page. Breaks are stored in the sheet and honoured by Excel, LibreOffice and PDF export alike. Long groups still flow over several pages where they must — the North region's 23 rows fit on one page here, but a 200-row region would break automatically inside itself and then manually at its end.

Step 3 — Make every page readable on its own

Pages that start mid-report need their own headers and labels, or the person holding page three has no idea what the columns mean:

Python

ws.print_title_rows = "1:1"
ws.page_setup.orientation = "portrait"
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.oddHeader.center.text = "Customers by region"
ws.oddFooter.left.text = "&A"
ws.oddFooter.right.text = "Page &P of &N"
ws.column_dimensions["B"].width = 18
wb.save("regional_customers_breaks.xlsx")

print_title_rows repeats the header row at the top of every printed page. Fit-to-width keeps all columns on each page while fitToHeight = 0 lets long groups take as many pages as they need — important, because fit-to-page with a fixed height would shrink a long group to fit and ignore the breaks' intent. The footer codes &A, &P and &N insert the sheet name, page number and page count. Page setup is covered in Set the Print Area and Page Setup with openpyxl.

Step 4 — Clear old breaks before adding new ones

A monthly script usually starts from last month's file or a template that already has breaks. Remove them first, or the breaks from last month's group sizes stay and split the new groups in odd places:

Python

from openpyxl.worksheet.pagebreak import RowBreak

wb = load_workbook("regional_customers_breaks.xlsx")
ws = wb.active
print("breaks found:", len(ws.row_breaks.brk))
ws.row_breaks = RowBreak()
for name, last_row in ends[:-1]:
    ws.row_breaks.append(Break(id=last_row))
wb.save("regional_customers_breaks.xlsx")
print("breaks now:", [b.id for b in load_workbook("regional_customers_breaks.xlsx").active.row_breaks.brk])

Assigning a fresh RowBreak() empties the collection; re-adding the breaks from the current group boundaries makes the script idempotent — running it twice gives the same three breaks, not six. Column breaks work the same way through ws.col_breaks with ColBreak, for sheets too wide to fit on one page even in landscape.

Step 5 — Prove it by rendering a PDF

A page break is right only if the printout is right. Render the sheet to PDF and check which region each page starts with:

Python

import subprocess
from pypdf import PdfReader

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "--outdir", "pdf",
                "regional_customers_breaks.xlsx"], check=True, capture_output=True)
reader = PdfReader("pdf/regional_customers_breaks.pdf")
print("pages:", len(reader.pages))
for i, page in enumerate(reader.pages, start=1):
    lines = [l for l in page.extract_text().splitlines() if l.startswith(("North", "South", "East", "West"))]
    regions_on_page = sorted({l.split()[0] for l in lines})
    print(f"page {i}: regions {regions_on_page}, first row: {lines[0] if lines else '-'}")

Each page contains exactly one region: North on the first page, South on the second, East on the third and West on the fourth, each starting with its own first customer and the repeated header. A test asserting "one region per page" protects the layout against later changes — a longer region that now needs two pages, or a template edit that drops the print titles. The PDF route is covered in Convert Excel to PDF on Linux with LibreOffice.

Step 6 — The same with XlsxWriter

When the report is generated from scratch, XlsxWriter takes the break rows as a list:

Python

import pandas as pd

df = pd.read_excel("regional_customers.xlsx")
group_ends = df.groupby("Region", sort=False).tail(1).index + 1     # worksheet row of each group's last line
with pd.ExcelWriter("regional_customers_xw.xlsx", engine="xlsxwriter") as xw:
    df.to_excel(xw, index=False, sheet_name="Customers")
    sheet = xw.sheets["Customers"]
    sheet.set_h_pagebreaks(list(group_ends[:-1] + 1))
    sheet.repeat_rows(0)
    sheet.fit_to_pages(1, 0)
print("break rows:", list(group_ends[:-1] + 1))

XlsxWriter's set_h_pagebreaks takes zero-based row indexes where the new page should start, so it is "last row of the group plus one" in its numbering — the mirror image of openpyxl's "break after row n". repeat_rows(0) repeats the first row and fit_to_pages(1, 0) fits the width. One limit to know: Excel accepts at most about 1,023 manual horizontal breaks per sheet, and XlsxWriter silently drops any beyond that — a report with thousands of tiny groups should go into separate sheets or files instead, as in Generate One Excel Report per Region in a Loop.

Performance notes

Adding breaks is instant — they are a short list in the sheet XML, one entry per group. The cost in this workflow is the PDF check, which starts LibreOffice for a second or two; run it in the test suite or once per build rather than on every small change. The group scan in Step 1 reads each row once; for very large sheets, compute the group ends with pandas instead (df.groupby("Region", sort=False).tail(1).index) and translate them to worksheet rows by adding the header offset, as the XlsxWriter step does — the two approaches give the same rows here, 24, 33 and 64.

Common pitfalls

Unsorted data. Groups must be contiguous; sort by the group column first.
Off-by-one breaks. openpyxl breaks after row n (one-based); XlsxWriter breaks before zero-based row n.
Fit-to-one-page. A fixed page height overrides the intent of the breaks; use fitToHeight = 0.
Stale breaks. Clear existing breaks before adding new ones.
No repeated header. Set print titles so every page stands alone.
Hidden rows at a boundary. A filtered or hidden last row still receives the break; check the PDF if the sheet uses filters or grouping.

Conclusion

Find each group's last row in sorted data, add Break(id=last_row) to ws.row_breaks for every group but the last, repeat the header row and fit the width while letting the height flow, and clear old breaks first so the script is repeatable. With XlsxWriter, pass the zero-based start rows to set_h_pagebreaks, mindful of its 1,023-break cap. Then render a PDF and check that each page holds exactly one group — the only test that proves the printout is right.

Frequently asked questions

How do I insert a page break with openpyxl? Import Break from openpyxl.worksheet.pagebreak and append Break(id=24) to the worksheet's row_breaks collection; that places a manual break after row 24, so row 25 starts a new page.

How do I start each group on a new page in Excel with Python? Sort the data by the group column, find the last row of each group, and add a row break after each of those rows except the last. Repeat the header row with ws.print_title_rows = "1:1".

How do I remove existing page breaks with openpyxl? Replace the collection with an empty one: from openpyxl.worksheet.pagebreak import RowBreak, then ws.row_breaks = RowBreak(). Column breaks are cleared the same way with ColBreak.

Is there a limit to the number of page breaks? Excel supports about 1,023 manual horizontal page breaks per sheet in practice, and XlsxWriter silently ignores any beyond that. For more groups, use separate sheets or files.

Up one level: Styling Excel Cells with openpyxl — layout and formatting for reports.
Print an Excel Workbook from Python with win32com — sending the pages to a printer.
Add Headers, Footers and Page Numbers to an Excel PDF — header and footer codes in depth.
Group and Outline Rows in Excel with openpyxl — groups on screen rather than on paper.

More on cell styling & layout