How to use Worksheet.VPageBreaks in the xlwings API way

The VPageBreaks member of a Worksheet object in Excel’s object model represents the collection of vertical page breaks within the sheet. In xlwings, this collection is accessible via the api property, allowing you to programmatically manage where pages are divided when printing. This is particularly useful for creating custom print layouts, ensuring that data is segmented logically across multiple pages without manual adjustment. The VPageBreaks collection contains VPageBreak objects, each representing a single vertical page break.

Syntax in xlwings:
To access the VPageBreaks collection in xlwings, use the following pattern:
worksheet.api.VPageBreaks
Here, worksheet is an xlwings Sheet object. The VPageBreaks collection supports methods and properties for adding, counting, and retrieving vertical page breaks. Key members include:

• Add(Before): Adds a new vertical page break. The Before parameter is a required argument of type Object, specifying the column to the left of which the break is inserted. In xlwings, you typically pass an Excel Range object representing the column (e.g., worksheet.range("C1").api to insert a break before column C).
• Count: A read-only property that returns the number of vertical page breaks in the collection (integer).
• Item(Index): Returns a single VPageBreak object from the collection. The Index parameter is the break number, starting from 1 for the leftmost break.

Example Usage with xlwings:
Below are code examples demonstrating how to use VPageBreaks in xlwings for common tasks. Ensure you have an Excel workbook open and referenced via xlwings.

1. Adding a Vertical Page Break:
   This example inserts a vertical page break before column D (i.e., between columns C and D) on the active sheet.

import xlwings as xw
wb = xw.Book.active # Assume an active workbook
ws = wb.sheets.active
# Add a vertical page break before column D
ws.api.VPageBreaks.Add(Before=ws.range("D1").api)
print("Vertical page break added before column D.")

2. Counting Vertical Page Breaks:
   This snippet counts and displays the total vertical page breaks in the worksheet.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
break_count = ws.api.VPageBreaks.Count
print(f"Number of vertical page breaks: {break_count}")

3. Iterating Through and Removing Vertical Page Breaks:
   This example loops through all vertical page breaks and removes each one, clearing manual break settings.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
vpage_breaks = ws.api.VPageBreaks
# Iterate in reverse to avoid index issues when deleting
for i in range(vpage_breaks.Count, 0, -1):
    vbreak = vpage_breaks.Item(i)
    vbreak.Delete() # Remove the page break
    print("All vertical page breaks have been removed.")

4. Accessing a Specific Page Break’s Location:
   This code retrieves the leftmost vertical page break and prints its column location.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets.active
if ws.api.VPageBreaks.Count > 0:
    first_vbreak = ws.api.VPageBreaks.Item(1)
    # The Location property returns a Range object for the column to the left of the break
    break_location = first_vbreak.Location.Column
    print(f"The first vertical page break is before column: {break_location}")
else:
    print("No vertical page breaks exist.")