Set Print Options, Headers and Footers with XlsxWriter

A report that looks right on screen can print badly: columns spill onto a second sheet of paper, the header row appears only on page one, and nothing on page four says which report it belongs to or how many pages there are. Excel's page setup fixes all of this, and XlsxWriter exposes every part of it as worksheet methods, so a generated report can be correct for printing — and for PDF export, which uses the same settings — from the start. This page sets up a multi-page sales listing for printing: orientation, paper size and scaling, repeated header rows, print area, margins and centring, page breaks between regions, and headers and footers built from Excel's codes for page numbers, dates and the sheet name, with a logo. The result is exported to PDF with LibreOffice and read back to confirm what each page shows. It belongs to Building Excel Reports with xlsxwriter.

Prerequisites

pip install xlsxwriter pandas pdfplumber

LibreOffice (soffice) exports the workbook to PDF so the printed layout can be checked from Python; Excel's own Save as PDF uses the same page setup. The example data is 120 sales lines across four regions, and a small logo image for the header.

Step 1 — Data and the basic page setup

import numpy as np
import pandas as pd
import xlsxwriter

rng = np.random.default_rng(3)
regions = ["East", "North", "South", "West"]
sales = pd.DataFrame({"Region": np.repeat(regions, 30), "Order": [f"SO-{5000 + i}" for i in range(120)],
                      "Customer": [f"Customer {i % 37:02d} Ltd" for i in range(120)], "Product": rng.choice(["Bolts", "Nuts", "Washers", "Rivets", "Brackets"], 120),
                      "Qty": rng.integers(5, 400, 120), "Unit price": rng.uniform(0.05, 9, 120).round(2),
                      "Discount": rng.choice([0, 0.05, 0.1], 120), "Rep": rng.choice(["A. Shah", "B. Lewis", "C. Ortiz"], 120)})
sales["Net"] = (sales["Qty"] * sales["Unit price"] * (1 - sales["Discount"])).round(2)

wb = xlsxwriter.Workbook("sales_print.xlsx")
ws = wb.add_worksheet("Sales Q3")
head = wb.add_format({"bold": True, "bg_color": "#DDEBF7", "border": 1})
money = wb.add_format({"num_format": "#,##0.00"})
ws.write_row(0, 0, sales.columns, head)
for r, row in enumerate(sales.itertuples(index=False), start=1):
    ws.write_row(r, 0, row)
    ws.write_number(r, 8, row.Net, money)
ws.set_column("A:B", 9)
ws.set_column("C:C", 18)
ws.set_column("D:H", 10)
ws.set_column("I:I", 11)

ws.set_landscape()
ws.set_paper(9)                      # 9 = A4; 1 = US Letter
ws.fit_to_pages(1, 0)                # one page wide, as many pages tall as needed
ws.repeat_rows(0)                    # print row 1 at the top of every page
ws.print_area(0, 0, len(sales), len(sales.columns) - 1)
ws.set_margins(left=0.5, right=0.5, top=0.9, bottom=0.8)
ws.center_horizontally()

set_landscape() and set_paper(9) choose A4 landscape — XlsxWriter's paper codes are Excel's, with 9 for A4 and 1 for US Letter. fit_to_pages(1, 0) scales the sheet so all columns fit across one page while letting it run to as many pages down as needed; a 0 for height is what keeps a long table from being squeezed onto a single page. repeat_rows(0) prints the header row at the top of every page, which is the single most useful print setting for a long list. The print area limits printing to the data, margins are in inches, and center_horizontally() puts the scaled table in the middle of the page rather than against the left margin. Checking the result for cut-off columns is covered in Check a PDF Export for Cut-Off Columns.

Step 2 — Page breaks between regions

Each region should start on a new page. set_h_pagebreaks() takes the rows above which a break goes:

region_starts = sales.index[sales["Region"] != sales["Region"].shift()].tolist()
breaks = [i + 1 for i in region_starts[1:]]          # +1 for the header row
ws.set_h_pagebreaks(breaks)
print("page breaks before rows:", breaks)

Rows are zero-based and the header occupies row 0, so a region whose first data row is at DataFrame index 30 starts on worksheet row 31. In the LibreOffice export below, the manual breaks combine with fit_to_pages: each region begins a new page and, within a region, pages would break wherever the height runs out. XlsxWriter's documentation warns that in Excel fit-to-page scaling can override manual page breaks, so if your readers print from Excel, confirm the breaks there — or replace fit_to_pages with a fixed set_print_scale chosen to fit the width, which leaves manual breaks alone. The same idea with openpyxl is in Insert Page Breaks Between Groups with openpyxl.

Step 3 — Headers and footers

Headers and footers are strings with control codes: &L, &C and &R start the left, centre and right sections; &P is the page number, &N the number of pages, &D and &T the print date and time, &A the sheet name and &F the file name. &G marks where an image goes:

ws.set_header('&L&G&C&"Calibri,Bold"&14Sales listing — Q3 2026&R&A',
              {"image_left": "logo.png", "scale_with_doc": False})
ws.set_footer("&LPrinted &D &T&CPage &P of &N&RConfidential — internal use")
wb.close()

The header shows the logo on the left, the report title in 14-point bold Calibri in the centre — &"Font,Style" and &14 change the font and size for the text that follows — and the sheet name on the right. The footer shows when it was printed, Page 3 of 9 in the centre and a confidentiality note on the right. Because &P, &N and &D are filled in at print time, they are always correct, whichever pages are printed and whenever. scale_with_doc: False stops the logo shrinking along with the fit-to-width scaling. Header text is limited to 255 characters including the codes, and a literal ampersand must be written as &&.

Step 4 — Check the printed pages

Export to PDF and read each page's first and last lines:

import subprocess
import pdfplumber

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "--outdir", "pdf", "sales_print.xlsx"], check=True, capture_output=True)
with pdfplumber.open("pdf/sales_print.pdf") as pdf:
    print("pages:", len(pdf.pages), "| size (pt):", round(pdf.pages[0].width), "x", round(pdf.pages[0].height))
    for n, page in enumerate(pdf.pages, start=1):
        lines = page.extract_text().splitlines()
        first_data = next(l for l in lines if l.startswith(tuple(regions)))
        print(f"{n}: {lines[0][:34]:<36} | {first_data.split()[0]:<5} | {lines[-1][:44]}")

The PDF has four A4 landscape pages (842 × 595 points), one per region: each of the 30-line regions fits on a single page once the sheet is scaled to the page width, and the manual breaks start East, North, South and West on pages 1 to 4. Every page starts with the header line — title and sheet name — then the repeated column headings, and every footer reads Page n of 4 with the date and time of the export. If something is wrong, it is wrong here, before the report reaches a printer or a client. Exporting reports to PDF as part of a job is covered in Convert an Excel File to PDF with Python.

Other print settings worth knowing

A few more methods round out page setup. hide_gridlines(0) prints the gridlines, which helps on dense numeric listings that have no borders of their own; by default Excel shows them on screen and leaves them off paper. print_row_col_headers() prints the A, B, C column letters and row numbers, useful for review copies where people will refer to cells. set_print_scale(85) sets a fixed zoom instead of fitting to pages — the two are alternatives, and when fit-to-pages is set the print scale is ignored. set_start_page(5) makes page numbering start at 5 when the sheet is one section of a larger printed pack, and print_across() prints across then down for sheets that are both wider and taller than a page. A workbook with several sheets needs these calls on each worksheet, so put the setup in one function and apply it to every sheet the report writes, so all of them print consistently.

Common pitfalls

• fit_to_pages(1, 1) on long lists. It shrinks everything onto one page; use (1, 0).
• Header row only on page one. Call repeat_rows(0).
• Page break off by one. Breaks are zero-based rows above which the break falls; count the header.
• Literal ampersands in headers. Write &&.
• Logos shrinking with the sheet. Set scale_with_doc to False.

Conclusion

Set up printing when the report is written: set_landscape() and set_paper() for the page, fit_to_pages(1, 0) for one page wide, repeat_rows(0) for headings on every page, print_area(), margins and centring for position, and set_h_pagebreaks() to start groups on new pages. Build headers and footers from codes — &P of &N, &D, &A, &G with an image — and check the result by exporting to PDF and reading each page.

Frequently asked questions

How do I repeat the header row on every printed page with XlsxWriter? Call worksheet.repeat_rows(0) for the first row, or repeat_rows(first, last) for several header rows.

How do I fit all columns on one page width in XlsxWriter? Call worksheet.fit_to_pages(1, 0): one page wide and as many pages tall as needed.

How do I add page X of Y to an Excel footer with Python? Use worksheet.set_footer("&CPage &P of &N"); &P and &N are replaced with the page number and page count when printing.

How do I put a logo in an Excel header with XlsxWriter? Include &G in the header string where the image should go and pass image_left, image_center or image_right with the image path in the options.

Related

• Up one level: Building Excel Reports with xlsxwriter — reports with XlsxWriter.
• Set the Print Area and Page Setup with openpyxl — the openpyxl equivalent.
• Add Headers, Footers and Page Numbers to an Excel PDF — headers for PDF exports.
• Write a Formatted Excel Report with xlsxwriter — the rest of the report.