How to use Worksheet.HPageBreaks in the xlwings API way

The HPageBreaks member of the Worksheet object in the Excel object model provides access to the collection of horizontal page breaks within a worksheet. In xlwings, this collection is accessible via the api property, which exposes the underlying Excel VBA object model. This allows for programmatic control over where pages break when the worksheet is printed, enabling precise formatting for reports and documents. The primary use is to add, delete, or query horizontal page breaks, which are essential for managing print layout in multi-page data sets.

Syntax and Parameters

In xlwings, you access the HPageBreaks collection through a worksheet object’s api property:

hpagebreaks = ws.api.HPageBreaks

The collection is 1-indexed, similar to Excel VBA. Key methods and properties include:

• Add(Before): Adds a new horizontal page break.
• Before: A required parameter of type Object. It specifies the range above which the page break will be inserted. You typically pass an xlwings Range object’s .api property (e.g., ws.range("A10").api). The break is inserted above the top edge of this range.
• Count (Property): Returns a Long representing the number of horizontal page breaks in the collection.
• Item(Index): Returns a single HPageBreak object from the collection.
• Index: The index number of the page break (1-indexed).
• Location (Property of an HPageBreak object): Returns a Range object representing the cell where the page break is set (the cell immediately below the break line). This is read/write, allowing you to move an existing break.

Code Examples

1. Adding a Horizontal Page Break:
   Inserts a horizontal page break above row 15.

import xlwings as xw
wb = xw.Book("report.xlsx")
ws = wb.sheets["Sheet1"]
# Add a page break above cell A15
ws.api.HPageBreaks.Add(Before=ws.range("A15").api)

2. Counting and Listing Page Breaks:
   Prints the count and location of each horizontal page break.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets[0]
hbreaks = ws.api.HPageBreaks
print(f"Number of horizontal page breaks: {hbreaks.Count}")
for i in range(1, hbreaks.Count + 1):
    break_obj = hbreaks.Item(i)
    # The Location property returns the cell below the break
    location_cell = break_obj.Location.Address
    print(f" Break {i}: Above row {break_obj.Location.Row} (at {location_cell})")

3. Deleting All Horizontal Page Breaks:
   Clears all manually set horizontal page breaks from the sheet. Note: This does not remove automatic breaks inserted by Excel based on page margins and size.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets[0]
hbreaks = ws.api.HPageBreaks
# Loop backwards to avoid index shifting when deleting
for i in range(hbreaks.Count, 0, -1):
    # The HPageBreak object itself doesn't have a .Delete() method.
    # You delete it by clearing the break from its location.
    hbreaks.Item(i).Location.PageBreak = -4142 # xlPageBreakNone
    # Alternatively, reset all page breaks on the sheet:
    # ws.api.ResetAllPageBreaks()

4. Moving an Existing Page Break:
   Changes the position of the first horizontal page break to be above row 25.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets[0]
hbreaks = ws.api.HPageBreaks
if hbreaks.Count >= 1:
    first_break = hbreaks.Item(1)
    first_break.Location = ws.range("A25").api