Changing default page breaks in xls-file via Python module xlwt

Question

I am trying to edit page breaks using Python module xlwt. (Yes I've found a very similar question xlwt - How to add page breaks to an Excel File? but it didn't clear the problem completely)

Here is some code that doesn't work as I expect:

import xlwt

wb = xlwt.Workbook()
ws = wb.add_sheet('Report')
ws.write(0,0, '0 here')
ws.write(43,12, '12 here')
ws.horz_page_breaks = [(44,0,12),]
wb.save('sample.xls')

It does create the row page break but columns seem to have a default page break (Page 2 starts from the 'K' column which is the 10th).

Can anybody explain what am I doing wrong or how to make a strict page break?

P.S. I was advised to have a look at openpyxl library but haven't found any documentation or example how to set a page break.

UPD: Some additional information that I've discovered. There is an automatic page break in Excel that can't be deleted (as it is said here http://office.microsoft.com/en-au/excel-help/insert-move-or-delete-page-breaks-in-a-worksheet-HP010021539.aspx) but only turned into a manual page break by dragging. Using worksheet.vert_page_break I can set a smaller (than the automatic) vertical page break. But that doesn't solve my problem.

If there is a way to make auto page break manual using xlwt it will really help.

Answer 1

After months of research (that wasn't an urgent problem) I found a solution using xlsxwriter Have a look at page setup. It is not as good as it should be however it offers a way to handle page breaks. The code below changes default page breaks of the worksheet:

import xlsxwriter
import random
wb = xlsxwriter.Workbook('page_breaks.xlsx')
ws = wb.add_worksheet('Sample page')

headers = ['header1', 'header2']
# set headers
for col, header in enumerate(headers):
    ws.write(0+1, col+1, 'header'+str(col))

# let it be 300 rows of data
for row in xrange(2,302):
    ws.write(row, 1, row)

# fill table with some random data
for _ in xrange(3000):
    row = random.randrange(2, 302)
    col = random.randrange(2, 16)
    ws.write(row, col, 'X')

# print_area(), set_paper() and fit_to_pages() do the trick
ws.print_area(1, 1, 301, 15)
ws.set_paper(9)  # set A4 as page format
pages_horz = 1
pages_vert = 4
ws.fit_to_pages(pages_horz, pages_vert)

wb.close()

If you want to have certain number of rows on the page you should take care of rows height and choose parges_vert according to it. So you do with columns.

P.S. I've changed xlwt for xlsxwriter because I need charts and some other functionality that xlwt doesn't provide in addition XlsxWriter has excellent documentation. But that doesn't mean xlwt is bad. I guess xlwt has something similar to print_area(), set_paper() and fit_to_pages()

P.P.S. if my answer gets upvotes or there doesn't appear something better I'll mark this with a green tick