Adding and Removing Page Breaks in Excel Using Python

When a wide table is exported to PDF or sent to the printer, Excel decides where to break the pages based on the paper size, the column widths, and the font size. That default is rarely what a report needs: a row of data can end up cut in half across a page boundary, and the header row does not repeat on the following pages.

A page break gives deterministic control over that decision. Breaking before a given row or column forces the page boundary to sit exactly where the script says, and the file prints the same way on every run. This article uses Spire.XLS for Python to add, inspect, and remove page breaks, and to combine them with print areas, repeated title rows, and other print settings.

Setting Up the Environment

Install Spire.XLS and import the modules:

pip install Spire.XLS

from spire.xls import *
from spire.xls.common import *

The breaks live on the worksheet object: sheet.HPageBreaks holds the horizontal breaks and sheet.VPageBreaks holds the vertical ones. Both collections expose exactly the same methods.

Adding Horizontal Page Breaks

A horizontal break decides where a new page starts. The example below builds a thirty-row table and forces a break before row 11 and before row 21:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]

for r in range(1, 31):
    sheet.Range[r, 1].Text = f"Region {r}"
    sheet.Range[r, 2].Text = f"2026-{((r - 1) % 12) + 1:02d}"
    sheet.Range[r, 3].NumberValue = (r * 37) % 500

# Break before row 11 and before row 21.
sheet.HPageBreaks.Add(sheet.Range["A11"])
sheet.HPageBreaks.Add(sheet.Range["A21"])

# Keep the file in the normal view; the preview is only for checking the layout.
sheet.ViewMode = ViewMode.Normal

print("count:", sheet.HPageBreaks.Count)
print("manual count:", sheet.HPageBreaks.ManualBreakCount)
print("first break row:", sheet.HPageBreaks.get_Item(0).Row)

workbook.SaveToFile("PageBreaksHorizontal.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Two details are worth understanding.

First, Add() takes a cell range, and it is the row of that range that matters: Range["A11"] means the break sits before row 11, so row 11 becomes the first row of the next page.

Second, a break is stored as a zero-based row number, which is why get_Item(0).Row returns 10 rather than 11 — the index that comes back is one less than the row number used to insert it. Count is the total number of breaks, while ManualBreakCount counts only the ones that were inserted by hand or by a script. The two can differ because Excel also inserts automatic breaks when the content exceeds the paper size.

Adding Vertical Page Breaks

A vertical break decides where a new column band starts. The usage mirrors the horizontal case exactly, except that the column is what matters:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]

for r in range(1, 31):
    sheet.Range[r, 1].Text = f"Region {r}"
    sheet.Range[r, 2].Text = f"2026-{((r - 1) % 12) + 1:02d}"
    sheet.Range[r, 3].NumberValue = (r * 37) % 500

# Rows 11 and 21 start a new printed page.
sheet.HPageBreaks.Add(sheet.Range["A11"])
sheet.HPageBreaks.Add(sheet.Range["A21"])
# Columns C and E start a new printed page.
sheet.VPageBreaks.Add(sheet.Range["C1"])
sheet.VPageBreaks.Add(sheet.Range["E1"])

print("horizontal:", sheet.HPageBreaks.Count, "vertical:", sheet.VPageBreaks.Count)

sheet.ViewMode = ViewMode.Normal

workbook.SaveToFile("PageBreaksBoth.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Keep the axes straight when writing the arguments: a horizontal break takes "A11" for row 11, while a vertical break takes "C1" for column C. Mixing them up places the break somewhere unintended.

Removing Page Breaks

There are two granularities: RemoveAt(index) drops a single break at a given position, and Clear() empties the whole collection:

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("PageBreaksBoth.xlsx")
sheet = workbook.Worksheets[0]

print("before -> horizontal:", sheet.HPageBreaks.Count, "vertical:", sheet.VPageBreaks.Count)

# Remove the first horizontal break by index.
sheet.HPageBreaks.RemoveAt(0)
print("after RemoveAt ->", sheet.HPageBreaks.Count)

# Remove every vertical break at once.
sheet.VPageBreaks.Clear()

print("after -> horizontal:", sheet.HPageBreaks.Count, "vertical:", sheet.VPageBreaks.Count)

workbook.SaveToFile("PageBreaksRemoved.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

After the first horizontal break is removed, the other one stays exactly where it was; after Clear() the vertical count drops to zero while the horizontal breaks are untouched — the two collections are independent.

When a new paging rule replaces an old one, clearing the collection first and adding the new breaks is cleaner than removing them one by one, and it leaves no stale rule behind.

Checking the Layout in Page-Break Preview

Page breaks are only visible in the page-break preview. Switching the view mode to ViewMode.Preview makes the file open directly in that preview, where the boundary of every page is visible:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("PageBreaksBoth.xlsx")
sheet = workbook.Worksheets[0]

# Open the file in page-break preview so the breaks are visible.
sheet.ViewMode = ViewMode.Preview
sheet.ZoomScalePageBreakView = 80

print("view mode is preview:", sheet.ViewMode == ViewMode.Preview)

workbook.SaveToFile("PageBreakPreview.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

ZoomScalePageBreakView controls the zoom of the preview view. The default is on the small side, and a document with many pages becomes tedious to check, so a value around 80 usually works well.

Combining Breaks with the Print Setup

Page breaks answer "where does the page end", while the print settings answer "what goes on each page". In practice the two are written together:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("PageBreaksBoth.xlsx")
sheet = workbook.Worksheets[0]

pageSetup = sheet.PageSetup
# Only print A1:C30.
pageSetup.PrintArea = "A1:C30"
# Repeat the first row at the top of every page.
pageSetup.PrintTitleRows = "$1:$1"
# A4 paper in landscape orientation.
pageSetup.PaperSize = PaperSizeType.PaperA4
pageSetup.Orientation = PageOrientationType.Landscape
# Fit the width to one page, keep the height automatic.
pageSetup.FitToPagesWide = 1
pageSetup.FitToPagesTall = 0

sheet.ViewMode = ViewMode.Preview
sheet.ZoomScalePageBreakView = 80

workbook.SaveToFile("PageBreakPrintSetup.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

A few combinations deserve attention:

• The print area bounds where a break can matter. Columns outside PrintArea are never printed, so a break placed there has no effect.
• Repeated title rows (PrintTitleRows) bring the header back on every page. Combined with row-based horizontal breaks, a long table keeps its context after each page turn.
• Scaling can work against the breaks. FitToPagesTall = 1 squeezes the whole sheet onto a single page, which is the opposite of what manual breaks are for. To fit the width only, set FitToPagesWide to 1 and leave FitToPagesTall at 0.

Practical Tips

• Insert breaks after the data is final: a break stores a fixed row number, so once the row count changes the old position no longer matches the intended grouping. Compute the insertion point from the actual row count in the script.
• Keep rows and columns apart: horizontal breaks take a row, vertical breaks take a column, and mixing them up moves the break somewhere else.
• Rebuild with Clear and Add: it is tidier than repeated RemoveAt() calls and cannot leave an old rule behind.
• Distinguish manual from automatic breaks: Count includes automatic ones, ManualBreakCount counts only what was inserted explicitly.
• Debug scaling and breaks together: changing FitToPagesWide or FitToPagesTall moves the page boundaries, so switch to the preview afterwards and confirm the result.
• Release resources: call Dispose() once the workbook is no longer needed.

Conclusion

Page breaks turn "where does the page end" from an automatic calculation into an explicit rule written in code: HPageBreaks.Add() and VPageBreaks.Add() insert breaks by row and by column, Count, ManualBreakCount, and get_Item(i).Row report the current state, RemoveAt() and Clear() clean up, and ViewMode.Preview with ZoomScalePageBreakView lets the result be checked inside the file itself.

Combined with PrintArea, PrintTitleRows, PaperSize, Orientation, and the fit-to-page settings, a script can produce a print-ready workbook or PDF on every run: where each page starts, which columns it contains, and whether the header repeats are all decided by the code.