Guide

Automating Reporting WorkflowsDeep dive

Add Headers, Footers and Page Numbers to an Excel PDF

Q: Why does my column header only appear on the first page?

The header row repeats only if you set print_title_rows. Setting it to "1:1" makes row 1 print at the top of every page, which is what makes a multi-page table readable.

Make an exported PDF look like a document — set headers and footers with openpyxl, repeat the title row on every page, and control margins and page breaks before converting.

A workbook exported to PDF straight from the default settings looks like a spreadsheet screenshot: no title, no page numbers, and the column headers on page one only, so page four is a grid of unlabelled numbers. Every one of those is a page-setup property that openpyxl can write, and LibreOffice honours all of them during conversion. This guide sets up the page properly before exporting. It extends Exporting Excel Reports to PDF.

Prerequisites

Bash

pip install pandas openpyxl

Plus LibreOffice installed as a program for the conversion, as in converting an Excel file to PDF:

Bash

soffice --version

A report long enough to span pages:

Python

import pandas as pd

rows = []
for region in ("North", "South", "West", "East"):
    for branch in range(1, 26):
        rows.append({
            "region": region,
            "branch": f"{region} branch {branch}",
            "units": 100 + branch,
            "revenue": (100 + branch) * 12.5,
            "target": (100 + branch) * 11.8,
        })

report = pd.DataFrame(rows)
report["variance"] = report["revenue"] / report["target"] - 1
report.to_excel("report.xlsx", index=False)

Step 1 — Set the header and footer

Headers and footers live on the worksheet's page setup, in three sections each — left, centre and right:

Python

from datetime import date
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb.active

ws.oddHeader.left.text = "Regional Revenue"
ws.oddHeader.center.text = "August 2026"
ws.oddHeader.right.text = f"{date.today():%Y-%m-%d}"

ws.oddFooter.left.text = "Confidential — internal use only"
ws.oddFooter.right.text = "Page &P of &N"

wb.save("report_paged.xlsx")

&P and &N are Excel's format codes for the current page and the page count. The full set worth knowing:

Code	Renders as
`&P`	current page number
`&N`	total pages
`&D`	the date the file was printed
`&T`	the time it was printed
`&F`	the workbook filename
`&A`	the sheet name
`&&`	a literal ampersand

The && matters if any of your text contains one — "Smith & Co" in a header renders as "Smith Co" unless you write "Smith && Co".

Each section also takes its own font settings:

Python

ws.oddHeader.left.size = 11
ws.oddHeader.left.font = "Calibri,Bold"
ws.oddHeader.left.color = "4338CA"

ws.oddFooter.right.size = 9
ws.oddFooter.right.color = "5B6780"

The font string is Excel's own "Name,Style" form — "Calibri,Bold", "Calibri,Italic", "Calibri,Bold Italic". It is not a CSS-like value, and an unrecognised style is ignored silently.

oddHeader applies to every page unless you enable different first or even pages:

Python

# A different header on page one — often no header at all, or a title block.
ws.HeaderFooter.differentFirst = True
ws.firstHeader.center.text = "Regional Revenue — August 2026"
ws.firstFooter.right.text = "Page &P of &N"

Step 2 — Repeat the column headings

This is the single change that most improves a multi-page report. Without it, every page after the first is unlabelled numbers:

Python

ws.print_title_rows = "1:1"        # repeat row 1 at the top of every page
ws.print_title_cols = "A:B"        # and columns A and B on every page

Set print_title_cols only when the table is wide enough to split across page-widths — repeating the label columns is what makes the right-hand pages meaningful. If you have set fit-to-width (next step), the table never splits horizontally and this is unnecessary.

Define the print area too, so stray cells outside the table do not drag empty pages into the output:

Python

from openpyxl.utils import get_column_letter

last_col = get_column_letter(ws.max_column)
ws.print_area = f"A1:{last_col}{ws.max_row}"

Step 3 — Control the page geometry

Orientation, scaling and margins decide whether the table fits at all.

Python

from openpyxl.worksheet.properties import PageSetupProperties

ws.page_setup.orientation = "landscape"
ws.page_setup.paperSize = ws.PAPERSIZE_A4

# Scale every column onto one page width; let the rows flow down.
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)

ws.page_margins.left = 0.5
ws.page_margins.right = 0.5
ws.page_margins.top = 0.8          # room for the header
ws.page_margins.bottom = 0.8       # room for the footer
ws.page_margins.header = 0.3
ws.page_margins.footer = 0.3

ws.print_options.horizontalCentered = True
ws.print_options.gridLines = False

The pageSetUpPr=PageSetupProperties(fitToPage=True) line is the one people miss. Without it the fitToWidth value is stored but ignored, and the table splits across page-widths exactly as if you had never set it.

Margins are in inches, and the header and footer margins are the distance from the paper edge to the header text — they must be smaller than the top and bottom margins or the header overlaps the data.

Step 4 — Break pages at meaningful boundaries

A section that starts three rows before a page break reads badly. Insert breaks where the data changes group:

Python

from openpyxl.worksheet.pagebreak import Break

def break_on_change(ws, column=1, first_row=2):
    """Start a new page each time the value in `column` changes."""
    previous, inserted = None, 0
    for (cell,) in ws.iter_rows(min_row=first_row, min_col=column,
                                max_col=column):
        if cell.value is None:
            continue
        if previous is not None and cell.value != previous:
            ws.row_breaks.append(Break(id=cell.row - 1))
            inserted += 1
        previous = cell.value
    return inserted

print(f"{break_on_change(ws)} page break(s) inserted")

Break(id=n) puts the break after row n, so passing cell.row - 1 starts the new group at the top of a page. Off by one in either direction and every section begins with one orphaned row from the previous one.

Excel caps the number of manual breaks per sheet at 1,026, so a break-per-group on a sheet with thousands of groups silently stops working partway. Guard it when the group count is not known:

Python

groups = report["region"].nunique()
if groups > 1_000:
    print(f"{groups} groups — too many for per-group page breaks; skipping")
else:
    break_on_change(ws)

Step 5 — Convert and check

The page setup travels with the workbook, so the conversion is unchanged:

Python

import shutil, subprocess, tempfile
from pathlib import Path

def to_pdf(xlsx_path, out_dir="delivery", timeout=180):
    soffice = shutil.which("soffice") or shutil.which("libreoffice")
    if soffice is None:
        raise RuntimeError("LibreOffice not found")

    src = Path(xlsx_path).resolve()
    out = Path(out_dir).resolve()
    out.mkdir(parents=True, exist_ok=True)

    with tempfile.TemporaryDirectory() as profile:
        result = subprocess.run(
            [soffice, f"-env:UserInstallation=file://{profile}",
             "--headless", "--convert-to", "pdf", "--outdir", str(out), str(src)],
            capture_output=True, text=True, timeout=timeout,
        )
    if result.returncode != 0:
        raise RuntimeError(result.stderr.strip())

    pdf = out / (src.stem + ".pdf")
    if not pdf.is_file() or pdf.stat().st_size == 0:
        raise RuntimeError(f"no usable PDF at {pdf}")
    return pdf

pdf = to_pdf("report_paged.xlsx")
print("wrote", pdf)

A cheap structural check catches the case where the page setup produced far more or far fewer pages than expected:

Python

import re

def page_count(pdf_path):
    """Approximate page count without a PDF library."""
    data = pdf_path.read_bytes()
    counts = [int(m) for m in re.findall(rb"/Count\s+(\d+)", data)]
    return max(counts) if counts else len(re.findall(rb"/Type\s*/Page[^s]", data))

pages = page_count(pdf)
print(f"{pages} page(s)")
assert 1 <= pages <= 40, f"unexpected page count: {pages}"

An unexpectedly high count almost always means fit-to-width did not take effect — the table split horizontally and doubled the pages.

Common pitfalls and fixes

Symptom	Cause	Fix
Header does not appear in Excel	It is print-only	Use Print Preview, or check the PDF.
Columns split across page-widths	`fitToPage` not enabled	Set `pageSetUpPr=PageSetupProperties(fitToPage=True)`.
Column headings only on page one	`print_title_rows` unset	Set it to `"1:1"`.
`&` missing from header text	Treated as a format code	Escape it as `&&`.
Header overlaps the data	Header margin ≥ top margin	Keep `page_margins.header` smaller than `top`.
Blank pages at the end	Print area larger than the data	Set `ws.print_area` explicitly.
Each section starts with an orphan row	Break id off by one	`Break(id=row - 1)`.
Later breaks ignored	Over Excel's 1,026-break cap	Break by larger groups, or not at all.

Performance and scale notes

Page setup costs nothing at write time — every property is a handful of attributes on the sheet. The cost is entirely in the conversion, which scales with page count because LibreOffice lays out and renders each one.

That makes fit-to-width the biggest performance lever available, since a table that splits horizontally doubles the pages and therefore roughly doubles the render time. Setting it correctly is both a readability fix and a speed fix.

Two further habits for a batch. Convert many files in one soffice invocation so the one-to-two-second start-up is paid once rather than per file:

Python

import shutil, subprocess, tempfile
from pathlib import Path

def to_pdf_batch(paths, out_dir="delivery", timeout=900):
    soffice = shutil.which("soffice") or shutil.which("libreoffice")
    out = Path(out_dir).resolve()
    out.mkdir(parents=True, exist_ok=True)
    srcs = [str(Path(p).resolve()) for p in paths]

    with tempfile.TemporaryDirectory() as profile:
        subprocess.run(
            [soffice, f"-env:UserInstallation=file://{profile}",
             "--headless", "--convert-to", "pdf", "--outdir", str(out), *srcs],
            capture_output=True, text=True, timeout=timeout, check=True,
        )
    return [out / (Path(s).stem + ".pdf") for s in srcs]

Cap what you print. A PDF is a document, not a data dump, and a four-hundred-page export of raw transactions serves nobody. Print the summary and publish the full workbook for anyone who needs the rows — the split described in publishing Excel reports to cloud storage. Where a long export genuinely must be printed, restricting the print area to the columns that matter cuts both the page count and the render time.

Note also that page-setup properties are lost if a later step rewrites the sheet through pandas, so set them last — after all data is written, alongside the other final-pass work in protecting and sharing Excel workbooks.

Conclusion

Everything that makes an exported PDF look like a document is a worksheet page-setup property, and LibreOffice carries all of it through the conversion. Set a three-section header and footer with &P of &N for page numbers, repeat row 1 with print_title_rows so every page is labelled, and enable fit-to-width — remembering that fitToWidth does nothing without pageSetUpPr=PageSetupProperties(fitToPage=True). Add page breaks at group boundaries with the id one row before the new group, set the print area so stray cells do not add blank pages, and check the resulting page count, because an unexpected number almost always means the fit did not take.

Frequently asked questions

Where do Excel headers and footers actually live? On the worksheet's page-setup properties, not in any cell. openpyxl exposes them as ws.oddHeader and ws.oddFooter with left, center and right sections, and they appear only in print and PDF output.

How do I put a page number in the footer? Use the format codes: &P for the current page and &N for the total. openpyxl's header and footer sections accept them directly, so "Page &P of &N" renders as "Page 2 of 7".

Why does my column header only appear on the first page? The header row repeats only if you set print_title_rows. Setting it to "1:1" makes row 1 print at the top of every page, which is what makes a multi-page table readable.

My table splits awkwardly across pages — what can I do? Set fit-to-width so columns never split, and insert manual page breaks at group boundaries so each section starts on a fresh page. Both are page-setup properties openpyxl can write.

Do these settings survive the conversion to PDF? Yes. LibreOffice reads the workbook's page setup, so headers, footers, repeated title rows, margins and page breaks all carry through to the PDF.

Up to the parent: Exporting Excel Reports to PDF — the conversion methods compared.
Convert an Excel File to PDF with Python — the headless LibreOffice recipe in full.
Convert Only Selected Sheets of a Workbook to PDF — controlling what ends up in the document.
Merge Cells and Centre a Report Title with openpyxl — the on-sheet banner the header complements.
Publishing Excel Reports to Cloud Storage — delivering the finished PDF.