Need help with setting PageBreak excel file with xlwings package in python.
According to Microsoft website: https://learn.microsoft.com/zh-tw/office/vba/api/excel.range.pagebreak
I've tried
app = xw.App(visible=True, add_book=False)
wb = app.books.open("raw_data/" + raw_file_name, update_links=False)
sht = wb.sheets['sheet1']
sht.api.Rows(24).PageBreak = 'xlPageBreakManual' # I would like to set on row 24
and the program stuck forever. Does anyone know how to solve the problem?
Thanks
xlwings does not know what xlPageBreakManual is unless its referenced. Nevertheless in this case it's not necessary. The default is to add a manual Page Break therefore syntax is only; sht.api.Rows(24).PageBreak = True. If you then check the PageBreak status on row 24; print(sht.api.Rows(24).PageBreak) it should return -4135 which is the excel constant for xlPageBreakManual.
You can use the constants to set the type of PageBreak, e.g. the line sht.api.Rows(24).PageBreak = True could also be written as sht.api.Rows(24).PageBreak = -4135 or sht.api.Rows(24).PageBreak = PageBreak.xlPageBreakManual
The page break values can be referenced from the xlwings constants using the syntax from xlwings.constants import PageBreak. My example shown below sets the page break at row 24 then shows the status of the previous, actual and ensuing rows. The previous and ensuing rows have a status of -4142 i.e. no page break while row 24 is -4135.
The code then removes the page break using xlPageBreakNone and the three rows 23-25 are all -4142 again.
For this test I used xlwings v0.26.1
import xlwings as xw
from xlwings.constants import PageBreak
### Constant values for reference
# xlPageBreakAutomatic = -4105
# xlPageBreakManual = -4135
# xlPageBreakNone = -4142
app = xw.App(visible=True, add_book=False)
sht = xw.Book("book.xlsx").sheets['Sheet1']
print("Add Page break at row 24")
sht.api.Rows(24).PageBreak = True
# sht.api.Rows(24).PageBreak = -4135 # This does the same as the line above
# sht.api.Rows(24).PageBreak = PageBreak.xlPageBreakManual # As does this line
print('Row23: ' + str(sht.api.Rows(23).PageBreak))
print('Row24: ' + str(sht.api.Rows(24).PageBreak))
print('Row25: ' + str(sht.api.Rows(25).PageBreak))
print("\nDelete Page break at row 24")
sht.api.Rows(24).PageBreak = PageBreak.xlPageBreakNone
print('Row23: ' + str(sht.api.Rows(23).PageBreak))
print('Row24: ' + str(sht.api.Rows(24).PageBreak))
print('Row25: ' + str(sht.api.Rows(25).PageBreak))
Note attempting to manually set a page break to -4105 (xlPageBreakAutomatic) fails which I would expect.