Page Breaks in xlwt

[Rob]

Hi,

I am having some difficulties getting page breaks to work using xlwt.

I have added the following to my code to try and set up page breaks,
but when the file is opened in excel, the page breaks are not set up.

ws.vert_page_breaks = [(0, 0, 53), (0, 54, 107), (0, 108, 162)]
ws.horiz_page_breaks = [(54, 0, 255), (108, 0, 255)]

If I use the dump command in xlrd on the file created by my program
and a file in which I have added the page breaks in excel, the
HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records are
identical.

Any suggestions, solutions would be welcome

Rob

[John Machin]

On 28/02/2009 6:24 AM, Rob wrote:
> Hi,

Hi, Rob

>
> I am having some difficulties getting page breaks to work using xlwt.

AFAICT there is only one possible problem with getting page breaks to
work, and it's a problem of appearances rather than reality: whether the
file has been created by xlwt or by a spreadsheet program, Excel does
not show the page breaks in Normal View *initially*. If you go into Page
Break Preview, you will see the page breaks. If you then switch back to
Normal View, Excel will show the page breaks. Save the file and open it
again, still no page break display in Normal View. This is with Excel
2003 (11.8237.8221) SP3. Same with Excel 2007. [OOo 3.0.1 Calc does
display page breaks in Normal View immediately on opening. Gnumeric
doesn't seem to grok manual page breaks at all AFAICT (version 1.9.1,
Windows XP SP3).]

> I have added the following to my code to try and set up page breaks,
> but when the file is opened in excel, the page breaks are not set up.
>
> ws.vert_page_breaks = [(0, 0, 53), (0, 54, 107), (0, 108, 162)]

Having a vertical page break before column index 0 (i.e. column A) is
rather pointless. There's nothing to the left of it.

> ws.horiz_page_breaks = [(54, 0, 255), (108, 0, 255)]

The name of the property is horz_page_breaks.

> If I use the dump command in xlrd on the file created by my program
> and a file in which I have added the page breaks in excel, the
> HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records are
> identical.

If given the above remarks you continue to so assert, please supply (a1)
the detailed steps you used to create the page breaks manually (a2) copy
of file so created (b) the script that creates the same page breaks
using xlwt (c) what version of Excel you are using (d) [shouldn't make a
difference, but ...] what version (or SVN revision) of xlwt you are using.

FWIW here's the script I've been using for experiments:
8<---
import xlwt

def make_sheet(name, nrows, ncols, vpb, hpb):
ws = wb.add_sheet(name)
ws.vert_page_breaks = vpb
ws.horz_page_breaks = hpb
for rowx in xrange(nrows):
for colx in xrange(ncols):
ws.write(rowx, colx, rowx * 1000 + colx)

wb = xlwt.Workbook()

make_sheet('H', 20, 8, [], [(10, 0, 255)])
make_sheet('V', 20, 8, [(4, 0, 65535)], [])
make_sheet('HV', 20, 8, [(4, 0, 65535)], [(10, 0, 255)])

make_sheet('x', 180, 50,
[(0, 0, 53), (0, 54, 107), (0, 108, 162)],
[(54, 0, 255), (108, 0, 255)],
)

make_sheet('y', 180, 50,
[],
[(54, 0, 255), (108, 0, 255)],
)

wb.save('page_breaks_demo.xls')
8<---

HTH,
John

[Rob]

On 27 Feb, 19:59, John Machin <sjmac...@lexicon.net> wrote:
> On 28/02/2009 6:24 AM, Rob wrote:
>
> > Hi,
>
> Hi, Rob
>
>
>

> > I have added the following to my code to try and set up page breaks,
> > but when the file is opened in excel, the page breaks are not set up.
>
> > ws.vert_page_breaks = [(0, 0, 53), (0, 54, 107), (0, 108, 162)]
>
> Having a vertical page break before column index 0 (i.e. column A) is
> rather pointless. There's nothing to the left of it.

I set up the page breaks this way after looking at what excel was
doing when I set up the page breaks in excel. I originally did not
have the breaks set up like this.

>
> > ws.horiz_page_breaks = [(54, 0, 255), (108, 0, 255)]
>
> The name of the property is horz_page_breaks.

This was the problem, although I wasn't receiving any error indicating
that I had typed it in wrong.

>
> > If I use the dump command in xlrd on the file created by my program
> > and a file in which I have added the page breaks in excel, the
> > HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records are
> > identical.
>
> If given the above remarks you continue to so assert, please supply (a1)
> the detailed steps you used to create the page breaks manually (a2) copy
> of file so created (b) the script that creates the same page breaks
> using xlwt (c) what version of Excel you are using (d) [shouldn't make a
> difference, but ...] what version (or SVN revision) of xlwt you are using.
>

I redid the dump (with the bad file) and the vertical records are the
same, but the horizontal ones are not. I either misread the
horizontal data, or ran the dump on the wrong file.

>
> HTH,
> John

Thank you for your help, the page breaks are now working perfectly.

Rob

[John Machin]

On 1/03/2009 7:49 AM, Rob wrote:
> On 27 Feb, 19:59, John Machin <sjmac...@lexicon.net> wrote:
>> On 28/02/2009 6:24 AM, Rob wrote:
>>
>>> Hi,
>> Hi, Rob
>>
>>
>>
>>> I have added the following to my code to try and set up page breaks,
>>> but when the file is opened in excel, the page breaks are not set up.
>>> ws.vert_page_breaks = [(0, 0, 53), (0, 54, 107), (0, 108, 162)]
>> Having a vertical page break before column index 0 (i.e. column A) is
>> rather pointless. There's nothing to the left of it.
>
> I set up the page breaks this way after looking at what excel was
> doing when I set up the page breaks in excel. I originally did not
> have the breaks set up like this.

What version of Excel was doing this? As you can see from the 'H', 'V',
'HV', and 'y' sheets generated by my script, that complexity is not
required; if you require only horizontal breaks, you don't need to
specify any vertical breaks at all, especially not dummy ones.

>>> ws.horiz_page_breaks = [(54, 0, 255), (108, 0, 255)]
>> The name of the property is horz_page_breaks.
>
> This was the problem, although I wasn't receiving any error indicating
> that I had typed it in wrong.

I don't understand "although". You shouldn't expect any error message
indicating that you had typed it in wrongly.

>>> If I use the dump command in xlrd on the file created by my program
>>> and a file in which I have added the page breaks in excel, the
>>> HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records are
>>> identical.
>> If given the above remarks you continue to so assert, please supply (a1)
>> the detailed steps you used to create the page breaks manually (a2) copy
>> of file so created (b) the script that creates the same page breaks
>> using xlwt (c) what version of Excel you are using (d) [shouldn't make a
>> difference, but ...] what version (or SVN revision) of xlwt you are using.
>>
>
> I redid the dump (with the bad file) and the vertical records are the
> same, but the horizontal ones are not. I either misread the
> horizontal data, or ran the dump on the wrong file.

The wrong file, surely? Otherwise the inference is that you misread a
record with zero page breaks as containing two.

> Thank you for your help, the page breaks are now working perfectly.

Great.

Cheers,
John

[Chris Withers]

Rob wrote:
>>> ws.horiz_page_breaks = [(54, 0, 255), (108, 0, 255)]
>> The name of the property is horz_page_breaks.
>
> This was the problem, although I wasn't receiving any error indicating
> that I had typed it in wrong.

Yes, that is a bit of a shame, but a major PITA to fix...
We'd have to convert all the classes to new-style classes with slots for
you to get an error in this case.

The name of the property is annoying, though...

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk