使用pyExcelerator创建自定义分页的Excel文件

项目中要用Python创建自定义报表，其中要求每个事务占excel的一页，需要抛弃默认的分页选项，自定义分页。

excel分页其实就是在excel文件中的适当位置插入page breaks（分页符）。pyExcelerator 中有相关的借口允许用户调用并自定义分页符位置。

分页符记录在biff8中的记录块分别是HorizontalPageBreaksRecord和VerticalPageBreaksRecord：

class HorizontalPageBreaksRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It contains all
horizontal manual page breaks.

Record HORIZONTALPAGEBREAKS, BIFF8:
Offset Size Contents
0 2 Number of following row index structures (nm)
2 6nm List of nm row index structures. Each row index
structure contains:
Offset Size Contents
0 2 Index to first row below the page break
2 2 Index to first column of this page break
4 2 Index to last column of this page break

The row indexes in the lists must be ordered ascending.
If in BIFF8 a row contains several page breaks, they must be ordered
ascending by start column index.
"""

class VerticalPageBreaksRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It contains all
vertical manual page breaks.

Record VERTICALPAGEBREAKS, BIFF8:
Offset Size Contents
0 2 Number of following column index structures (nm)
2 6nm List of nm column index structures. Each column index
structure contains:
Offset Size Contents
0 2 Index to first column following the page
break
2 2 Index to first row of this page break
4 2 Index to last row of this page break

The column indexes in the lists must be ordered ascending.
If in BIFF8 a column contains several page breaks, they must be ordered
ascending by start row index.
"""

具体的接口就是在worksheet中set_vert_page_breaks和set_horz_page_breaks。先上程序：


from pyExcelerator import *

wb = Workbook()
ws = wb.add_sheet('0')
horz_page_breaks_list=[]

for i in range (200):
for j in range (20):
ws.write(i,j,'BIG')
else:
for i in range(39):
#5 rows each page
horz_page_breaks_list.append([(i+1)*5,0,0])

ws.set_horz_page_breaks(horz_page_breaks_list)

wb.save('page_breaks.xls')
print 'successful set page breaks!'


这个是设置5行一页，每页列数按照默认值。