Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Set the Print Area and Page Setup with openpyxl

Make a generated workbook print properly: print area, landscape orientation, fit to width, repeated header rows, margins, headers and footers, and page breaks.

A generated workbook that looks right on screen can print as fourteen pages of orphaned columns, and nobody discovers that until someone in the meeting tries. openpyxl exposes the whole page-setup surface — print area, orientation, scaling, repeated headers, margins, headers and footers — and setting it takes about ten lines. The same settings govern PDF export, which is where most generated reports actually end up. This guide configures a sheet for printing properly. It belongs to Styling Excel Cells with openpyxl.

The same sheet printed with and without page setup Without configuration the columns overflow onto separate pages with no repeated header, while fit-to-width, landscape orientation and repeated title rows produce readable pages. default settings configured cols A–D page 1 cols E–F no header col G orphaned three pages wide, unreadable repeated header all columns page 1 of 2 repeated header all columns page 2 of 2 one page wide, two pages long

Prerequisites

Bash
pip install openpyxl

Set the print area

Without one, Excel prints everything it considers used — including a stray value someone left in AZ900:

Python
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Summary"]

last_col = ws.cell(row=1, column=ws.max_column).column_letter
ws.print_area = f"A1:{last_col}{ws.max_row}"
wb.save("report.xlsx")

Computing the range from the data rather than hardcoding it means the setting stays correct as the report grows. For several blocks, assign a list — ws.print_area = ["A1:F20", "A30:F60"] — and each prints as its own page.

Orientation, paper and fit to width

The single most valuable setting is fit-to-width: it scales the sheet so no column is orphaned, while letting the rows flow over as many pages as they need.

Python
from openpyxl.worksheet.properties import PageSetupProperties

ws.page_setup.orientation = "landscape"        # 'portrait' is the default
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0                  # 0 = as many pages as needed
ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)

That last line is the one everybody misses. fitToWidth is ignored unless the sheet's page-setup properties say the sheet is in fit-to-page mode; without it Excel keeps whatever percentage scale was set and the setting appears to do nothing.

Repeat the header row on every page

Python
ws.print_title_rows = "1:1"       # repeat row 1 at the top of every page
ws.print_title_cols = "A:A"       # and column A down the left

On a report longer than a page this is the difference between a printout somebody can read and one where page four is a grid of unlabelled numbers. If the report has a two-row header — a title and a column row — use "1:2".

The page-setup settings that matter, in order Print area bounds what is printed, fit-to-page controls scaling, title rows repeat the header, and header and footer text carries the page numbering. Four settings, biggest effect first 1. print_area — what prints at all 2. fitToPage + fitToWidth — no orphans 3. print_title_rows — headers repeat 4. footer "&P of &N" — page numbers All four also govern how the sheet converts to PDF

Headers, footers and margins

Excel's header and footer strings use its own format codes, and openpyxl passes them straight through:

Python
ws.oddHeader.left.text = "Regional sales"
ws.oddHeader.left.size = 11
ws.oddHeader.right.text = "&D &T"                 # date and time
ws.oddFooter.center.text = "Page &P of &N"
ws.oddFooter.right.text = "&F — &A"               # filename — sheet name

ws.page_margins.left = 0.5
ws.page_margins.right = 0.5
ws.page_margins.top = 0.6
ws.page_margins.bottom = 0.6

The codes worth knowing are &P (page number), &N (total pages), &D (date), &T (time), &F (file name), &A (sheet name) and &B (bold). Margins are in inches regardless of the paper size.

Centre, gridlines and print quality

Python
ws.print_options.horizontalCentered = True
ws.print_options.verticalCentered = False
ws.print_options.gridLines = False        # True prints the sheet grid
ws.print_options.headings = False         # row numbers and column letters
ws.page_setup.blackAndWhite = False

Printing gridlines is usually wrong for a styled report — the borders you applied already convey the structure, and the grid competes with them. For an unformatted data dump, turning them on helps.

Control where the pages break

Let Excel paginate unless a break belongs somewhere specific — between regions, or before a summary block:

Python
from openpyxl.worksheet.pagebreak import Break

for row in (25, 50, 75):
    ws.row_breaks.append(Break(id=row))       # break after this row

Manual breaks are worth adding when each page should be self-contained, for example one region per page in a pack that gets split up and handed out.

Apply it to every sheet in the workbook

Page setup is per sheet, and a multi-sheet report needs it everywhere:

Python
from openpyxl.worksheet.properties import PageSetupProperties

def configure_printing(ws, landscape: bool = True) -> None:
    last_col = ws.cell(row=1, column=max(ws.max_column, 1)).column_letter
    ws.print_area = f"A1:{last_col}{ws.max_row}"
    ws.page_setup.orientation = "landscape" if landscape else "portrait"
    ws.page_setup.paperSize = ws.PAPERSIZE_A4
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0
    ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)
    ws.print_title_rows = "1:1"
    ws.print_options.horizontalCentered = True
    ws.oddFooter.center.text = "&A — page &P of &N"

for sheet in wb.worksheets:
    configure_printing(sheet)
wb.save("report.xlsx")

Putting that in the same module as the report's style constants keeps every generated workbook consistent, in the spirit of Apply a reusable style theme across an Excel report.

Check the settings landed

Page setup is invisible until somebody prints, which is the worst moment to discover it is wrong. Read the properties back after saving:

Python
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
for ws in wb.worksheets:
    setup = ws.page_setup
    props = ws.sheet_properties.pageSetUpPr
    print(f"{ws.title:20s} area={ws.print_area} titles={ws.print_title_rows} "
          f"orient={setup.orientation} fitW={setup.fitToWidth} "
          f"fitToPage={getattr(props, 'fitToPage', None)}")
Text
Summary              area=A1:H42 titles=1:1 orient=landscape fitW=1 fitToPage=True
Detail               area=None   titles=None orient=portrait  fitW=None fitToPage=None

The second line is the failure this check exists to catch: a sheet added later that never went through the configure function. Looping over wb.worksheets at the end of the job, rather than configuring sheets as they are created, makes that impossible.

Configuring sheets as a final pass rather than at creation Applying page setup inside each builder leaves any later-added sheet unconfigured, while a single pass over every worksheet at the end covers all of them. configure per builder configure in one final pass every builder must remember to call it a new sheet ships unconfigured one loop over wb.worksheets new sheets covered automatically The same argument applies to freeze panes and column widths

Scale by percentage when fitting is wrong

Fit-to-page is right for a wide table, but it can shrink a narrow report to an unreadable size — a five-column sheet fitted to one page wide is fine, while the same setting on a sheet with one very wide text column drags everything down with it. Use an explicit percentage scale instead:

Python
ws.sheet_properties.pageSetUpPr = None       # leave fit-to-page mode
ws.page_setup.scale = 85                     # per cent of natural size

The two are mutually exclusive: Excel honours scale only when the sheet is not in fit-to-page mode, which is why the property is cleared first. Between 80 and 90 per cent usually buys a column or two without hurting legibility; below about 70 the text stops being comfortable to read on paper.

A practical rule for a generated report: fit to width when the sheet is a wide table of numbers, and use a fixed scale when it is a narrow document-like sheet with long text. If you cannot decide, set the width of the widest text column explicitly and wrap it — the layout then fits without any scaling at all, as covered in Set column width and row height in openpyxl.

Common pitfalls and gotchas

  • fitToWidth with no fitToPage. The setting is silently ignored; set the sheet property too.
  • A stale print area. Hardcoding A1:F100 on a report that grows to 300 rows truncates it without warning.
  • Margins in the wrong unit. They are inches, not centimetres or points.
  • Setting page options on the workbook. They live on each worksheet.
  • Expecting xlsxwriter's API here. xlsxwriter uses methods such as ws.set_landscape() and ws.fit_to_pages(); the concepts match but the names do not.

Performance and scale notes

Page setup costs nothing at write time — it is a handful of XML attributes — so apply it to every generated sheet rather than only the ones somebody has complained about. Where it does have a measurable effect is PDF conversion: a sheet without a print area can produce a PDF hundreds of pages long, which is slow to render and slow to email. Setting print_area and fitToWidth before conversion often turns a multi-megabyte PDF into a two-page one. The conversion itself is covered in Convert an Excel file to PDF with Python and the page furniture in Add headers, footers and page numbers to an Excel PDF.

Conclusion

Ten lines of page setup decide whether a generated report is usable on paper or as a PDF. Set the print area from the data's real extent, turn on fit-to-page and fit-to-width so no column is orphaned, repeat the header row, and put "page P of N" in the footer. Apply the same function to every sheet in the workbook, and the output is consistent whether a reader prints it, exports it, or just scrolls.

Frequently asked questions

Why does fitToWidth do nothing? Scaling options only apply when sheet properties are set to use them. Set ws.sheet_properties.pageSetUpPr.fitToPage = True as well as fitToWidth, or Excel keeps the percentage scale instead.

How do I repeat the header row on every printed page? Set ws.print_title_rows = "1:1". The equivalent for columns is print_title_cols, and both accept ranges like "1:2" or "A:B".

Does this affect a PDF export? Yes, and that is the main reason to bother. LibreOffice and Excel both honour page setup when converting to PDF, so an unconfigured sheet becomes a PDF spread across a dozen ragged pages.

Can I set the print area to several ranges? Yes — assign a list of ranges. Excel prints each as its own page, which is useful for a summary block and a detail block on one sheet.

How do I put the page number in the footer? Use Excel's format codes in ws.oddFooter.center.text, for example "Page &P of &N". The same codes work in headers.