Formatted headers/footers?

[Wolfgang]

Hi,

Is it posible to write a formatted header string
(xlwt.worksheet.header_str)?
Is it posible to define a page number on a header of footer location?

[John Machin]

Yes and yes and a whole lot more:

import xlwt
b = xlwt.Workbook()
sheets = map(b.add_sheet, ['default', 'fancy'])
for s in sheets:
s.write(0, 0, "Lean hard on the 'Print Preview' button")
s1 = sheets[1]
s1.header_str = (
u"&Lleft\nPage &P of &N"
u"&Ccentre\n&D &T"
u"&Rright\n&Z\n&F\n&A"
)
s1.footer_str = (
u"&L\nAmpersand &&"
u'&C&"Times New Roman"'
u"&20&Bbold&B&Iitalic&I&Uunderline&U&36\u263a"
u"&R\n&&G is a picture (untested)"
)
b.save("header_footer.xls")

HTH,
John

[Nick]

BTW, can all headers/footers be turnef off ? I need to produce xls for
printing, so I've tried

ws.set_show_headers ( 0 )
ws.set_print_headers( 0 )

but these seam not to work - headers still present in print preview
(OO scalc).
For correct page margins now I'm using this:

ws.set_header_margin( 0 )
ws.set_footer_margin( 0 )
ws.set_header_str( "" )
ws.set_footer_str( "" )
ws.set_top_margin( 0.30 )
ws.set_left_margin( 0.07 )

as empty strings still take places at top/bottom.

Is there any simplier way to get rid of them?

[John Machin]

On 25/08/2009 1:34 AM, Nick wrote:
> BTW, can all headers/footers be turnef off ? I need to produce xls for
> printing,

Ummm the headers/footers are expressly designed for printing so that the
users can have all those useful things like headings, dates, and page
numbers without which they tend in my experience to become somewhat
unhappy ... not so where you are?

> so I've tried
>
> ws.set_show_headers ( 0 )
> ws.set_print_headers( 0 )
>
> but these seam not to work - headers still present in print preview
> (OO scalc).

They work quite well. The headers to which they refer are the row
"headings" ('1', '2', etc) and column headings ('A', 'B', etc) which are
are normally shown on the screen but not printed.

I'm somewhat surprised you didn't notice the effect of
ws.set_show_headers ( 0 )

BTW, did you know that
ws.show_headers = 0
has the same effect?

> For correct page margins now I'm using this:
>
> ws.set_header_margin( 0 )
> ws.set_footer_margin( 0 )
> ws.set_header_str( "" )
> ws.set_footer_str( "" )
> ws.set_top_margin( 0.30 )
> ws.set_left_margin( 0.07 )
>
> as empty strings still take places at top/bottom.
>
> Is there any simplier way to get rid of them?

Not as far as I'm aware of. xlwt uses what I presume to be what its
author believed to be the Excel defaults for all those settings. The
default page header/footer setup is for minimal page headers/footers.
You'll have to dismantle that piece by piece.

Cheers,
John

[Nick]

On 25 Aug, 05:58, John Machin <sjmac...@lexicon.net> wrote:

> users can have all those useful things like headings, dates, and page
> numbers  without which they tend in my experience to become somewhat
> unhappy ... not so where you are?

headings, etc. are really usefull except when you need to produce some
strict
official standard documents like waybill/invoice

> I'm somewhat surprised you didn't notice the effect of
>      ws.set_show_headers ( 0 )
>
> BTW, did you know that
>      ws.show_headers = 0
> has the same effect?

I did, since your second post in this topic. :-) It's not clear what's
the difference between
show_headers and print_headers. print_headers control column/row
headers at print view,
and what do the show_headers control? The same at normal view?

[John Machin]

On 26/08/2009 1:49 AM, Nick wrote:

> It's not clear what's
> the difference between
> show_headers and print_headers. print_headers control column/row
> headers at print view,

AND (of course) when the worksheet is actually printed

> and what do the show_headers control? The same at normal view?

Yes -- in other words, what the user normally sees on the screen, which
is why I was astonished that you hadn't noticed the effect.

[Wolfgang]

thanks for this example!
I would like to introduce a header as a concatenation of a string and
a variable as a header string, such as:

u"&C&20Title1 %s" % title2var

With title2 as "title2" it works. Concatinating a variable ("aaa" + str
(avar)) seems not to work.
Any idea?

[John Machin]

On 26/08/2009 6:15 PM, Wolfgang wrote:
> thanks for this example!
> I would like to introduce a header as a concatenation of a string and
> a variable as a header string, such as:
>
> u"&C&20Title1 %s" % title2var

You've lost me already. That's not a concatenation.

>
> With title2 as "title2" it works.

What is title2?? Did you mean title2var?

> Concatinating a variable ("aaa" + str
> (avar)) seems not to work.
> Any idea?

No idea at all, without the actual code that you ran and/or a clue what
"seems not to work" means. Try making your changes to that little
example script that I posted, run it, and post back (1) the changed
script (2) what you expected to see/happen (3) what you actually saw
and/or what actually happened.

[Wolfgang Belau]

I try to be more precise:

month = "some_month"
sheet1.header_str = (
u"&C&20&BBackup Protocol %s" % "some_month"
u"&L&D"
)

This works fine.

sheet1.header_str = (
u"&C&20&BBackup Protocol %s" % month
u"&L&D"
)
This does not work.

sheet1.header_str = (
u"&C&20&BBackup Protocol %s" % (month)
u"&L&D"
)
This does not work eigher.

--

MfG, kind regards, cordialement,

Wolfgang Belau Phone: +49 421 539 4519
ASTRIUM Space Transportation, Dept.TE55 Fax: +49 421 539 4424
Airbus-Allee 1
D-28199 BREMEN
Email: Wolfgan...@astrium.eads.net

This email (including any attachments) may contain confidential and/or privileged information or information otherwise protected from disclosure. If you are not the intended recipient, please notify the sender immediately, do not copy this message or any attachments and do not use it for any purpose or disclose its content to any person, but delete this message and any attachments from your system. Astrium disclaims any and all liability if this email transmission was virus corrupted, altered or falsified.
---------------------------------------------------------
Astrium GmbH Vorsitzender des Aufsichtsrates: Thomas Mueller - Geschaeftsfuehrung: Evert Dudok (Vorsitzender), Dr. Reinhold Lutz, Guenter Stamerjohanns, Josef Stukenborg
Sitz der Gesellschaft: Muenchen - Registergericht: Amtsgericht Muenchen, HRB Nr. 107 647

Weitere Informationen ueber EADS Astrium @ http://www.astrium.eads.net/

[Chris Withers]

Wolfgang Belau wrote:
>
> I try to be more precise:
>
> month = "some_month"
> sheet1.header_str = (
> u"&C&20&BBackup Protocol %s" % "some_month"
> u"&L&D"
> )
>
> This works fine.

By some definition of "fine", it's not doing what you expect, it's
actually doing:

sheet1.header_str = (
u"&C&20&BBackup Protocol %s" % "some_month&L&D"
)

...because strings over multiple lines are concatenated in Python ;-)

> sheet1.header_str = (
> u"&C&20&BBackup Protocol %s" % month
> u"&L&D"
> )
> This does not work.

Always give an error message when you say something doesn't work.
The SyntaxError you would have got indicates this is nothing to do with
xlwt ;-)

sheet1.header_str = (
u"&C&20&BBackup Protocol %s"

u"&L&D"
) % month

cheers,

Chris

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

[Wolfgang Belau]

ok, thanks for the Python lesson!

[Brian Ray]

> import xlwt
> b = xlwt.Workbook()
> sheets = map(b.add_sheet, ['default', 'fancy'])
> for s in sheets:
>      s.write(0, 0, "Lean hard on the 'Print Preview' button")
> s1 = sheets[1]
> s1.header_str = (
>      u"&Lleft\nPage &P of &N"
>      u"&Ccentre\n&D &T"
>      u"&Rright\n&Z\n&F\n&A"
>      )
> s1.footer_str = (
>      u"&L\nAmpersand &&"
>      u'&C&"Times New Roman"'
>          u"&20&Bbold&B&Iitalic&I&Uunderline&U&36\u263a"
>      u"&R\n&&G is a picture (untested)"
>      )
> b.save("header_footer.xls")
>

Any luck getting &G to work?

http://msdn.microsoft.com/en-us/library/aa174225(office.11).aspx

I guess &G[Name of Graphic object] but I was not sure how to insert
the graphic.

[John Machin]

On Jan 30, 1:11 pm, Brian Ray <brianh...@gmail.com> wrote:
[snip]

> > s1.footer_str = (
> >      u"&L\nAmpersand &&"
> >      u'&C&"Times New Roman"'
> >          u"&20&Bbold&B&Iitalic&I&Uunderline&U&36\u263a"
> >      u"&R\n&&G is a picture (untested)"
> >      )
> > b.save("header_footer.xls")
>
> Any luck getting &G to work?

No. This is probably because I haven't tried to get it to work.

> http://msdn.microsoft.com/en-us/library/aa174225(office.11).aspx
>
> I guess &G[Name of Graphic object]  but I was not sure how to insert
> the graphic.

I wouldn't have guessed that from the VBA code and comments that you
quote. I'd guess that you would need to do:

(1) write whatever record(s) are necessary to instantiate a (Left|
Center|Right)(Header|Footer)Picture object
(2) Insert "&G" in the right place in the appropriate Sheet.(header|
footer) string

Part (2) is easy. Part (1) looks like it might need something ugly ...
see page 318 in the latest [MS-XLS].pdf that you can download from
http://msdn.microsoft.com/en-us/library/cc313154.aspx

"""2.4.138 HFPicture
This record specifies a picture used by a sheet header or footer. The
picture MUST be specified in either an OfficeArtDgContainer or
OfficeArtDggContainer record as specified in [MS-ODRAW]. The picture
can be continued across multiple HFPicture records. The
OfficeArtClientAnchor structure mentioned in [MS-ODRAW] refers to
OfficeArtClientAnchorHF."""

Looks like you'll need to get an [MS-ODRAW].pdf as well.

HTH,
John