xlwt: formatting cells?

3,136 views
Skip to first unread message

erikcw

unread,
Feb 12, 2008, 4:34:08 PM2/12/08
to python-excel
Hi,

I've been trying to get cell formatting working with xlwt based on
this blog post http://ntalikeris.blogspot.com/2007/10/create-excel-file-with-python-my-sort.html
- but I'm not having any luck.

I need to make the top row of my worksheet bold.

And I need to make 2 columns display negative numbers in red with
perentheses instead of a minus sign - ($2.00) instead of -$2.00.

Can anyone point me in the right direction?

Thanks!
Erik

PS Does xlwt have a public site, or is it just the SVN repo?

John Machin

unread,
Feb 13, 2008, 6:53:15 AM2/13/08
to python...@googlegroups.com
erikcw wrote:
> Hi,

Hi, Erik, and welcome ...

>
> I've been trying to get cell formatting working with xlwt based on
> this blog post http://ntalikeris.blogspot.com/2007/10/create-excel-file-with-python-my-sort.html
> - but I'm not having any luck.

A general point on asking for help: show a brief piece of code that
represents your best effort, together with a short description of what
happened when you ran the code.

> I need to make the top row of my worksheet bold.
>
> And I need to make 2 columns display negative numbers in red with
> perentheses instead of a minus sign - ($2.00) instead of -$2.00.

You need to work out a "number format" that does what you want -- so
consult the docs/help for Excel / OOo Calc / Gnumeric and fiddle around
in the GUI till you think you've got it right, then put it in your code.

> Can anyone point me in the right direction?

That blog gives examples of setting up a style with a bold font, and
with a "number format" string. You will need one style for your heading
row and one style for your red/black money amounts. For the other cells,
you can either use no style at all in the write method call, or you can
explicitly create a default style and use that. I'd use the explicit way.

> PS Does xlwt have a public site, or is it just the SVN repo?

(1) No (2) No, "it" is the SVN repo plus this newsgroup/list. Volunteers
for the xlwt marketing department and the xlwt documentation team would
be welcomed.

Cheers,
John

erikcw

unread,
Feb 20, 2008, 3:36:17 PM2/20/08
to python-excel
Hi John,

Still seem to be having trouble. Here is my code:

myFont2 = xl.Font()e
myFont2.num_format_str = '#,##0.00;[RED]-#,##(0.00)'

# the font should be transformed to style *
myFontStyle2 = xl.XFStyle()
myFontStyle2.font = myFont2

i_row = 0
for row in reader:
if len(row) <= 2:
meta.append(row)
continue
if len(row) > 2:#to ignore metadata rows
if header is None:
header = row
row.insert(4, 'Profit/Loss')
row.insert(5, 'P/L/K Impressions')
i_col = 0
for item in row:
worksheet.write(0,i_col, item)
i_col +=1
else:
i_col=0
row.insert(4, 'Profit/Loss')
row.insert(5, 'P/L/K Impressions')

for item in row:
if item is 'Profit/Loss':
item = xl.Formula('J%i-I%i' % (i_row+1,i_row
+1))
elif item is 'P/L/K Impressions':
item = xl.Formula('E%i/G%i*1000' % (i_row
+1,i_row+1))
worksheet.write(i_row,i_col, item, myFontStyle2)
i_col += 1
i_row += 1


The cell is just turning out blank (in terms of style). Any idea why
myFontStyle2 isn't "attaching"?

Thanks!
Erik

On Feb 13, 6:53 am, John Machin <sjmac...@lexicon.net> wrote:
> erikcw wrote:
> > Hi,
>
> Hi, Erik, and welcome ...
>
>
>
> > I've been trying to get cell formatting working with xlwt based on
> > this blog posthttp://ntalikeris.blogspot.com/2007/10/create-excel-file-with-python-...

John Machin

unread,
Feb 20, 2008, 4:57:14 PM2/20/08
to python...@googlegroups.com
erikcw wrote:
> Hi John,
>
> Still seem to be having trouble. Here is my code:
>
> myFont2 = xl.Font()e
> myFont2.num_format_str = '#,##0.00;[RED]-#,##(0.00)'
>
> # the font should be transformed to style *
> myFontStyle2 = xl.XFStyle()
> myFontStyle2.font = myFont2
>

num_format_str is an attribute of the XFStyle class, *NOT* the Font
class. For what you are doing, you don't need to go anywhere near the
Font class.

Reply all
Reply to author
Forward
0 new messages