number_format and styles - bug?

[Eric Hurkman]

Using openpyxl==2.1.1. and Python 2.7, OSX.

I'm seeing some inconsistent behavior in number_format. It does not seem to copy over correctly when using copy() on styles. Link to a Gist: https://gist.github.com/erichurkman/8019f57c588bd72ba075/2b4a0f395a02fb14078e8b708fce0872551a53fa

Is this a bug or am I expecting the wrong thing from copy()ing a style with a number_format?

import openpyxl

 

wb = openpyxl.Workbook()

 

ws = wb.active

 

ws.append([12311])

ws.append(['=A1'])

ws.append(['=A1+A2'])

ws.append(['=A1+A2+A3'])

ws.append(['=A1+A2+A3+A4'])

 

style = openpyxl.styles.Style(

    number_format='#,###',

    font=openpyxl.styles.Font(color='00AA00', name='Arial'))

 

# This works

ws['A1'].style = style

 

# This does not. Number format is lost on .copy()

ws['A2'].style = style.copy(font=style.font.copy(color='AA0000'))

 

# This does not - even setting an explicit number_format does not work

ws['A3'].style = style.copy(number_format='#,###', font=style.font.copy(color='AA0000'))

 

# But rebuilding the style from scratch does

ws['A4'].style = openpyxl.styles.Style(

    number_format='#,###',

    font=style.font.copy())

 

# And all four cells print '#,###' as their number_format

for i in xrange(1, 5):

    print ws['A%s' % i].number_format

wb.save("tmp/tmp.xlsx")

import os; os.system("open tmp/tmp.xlsx")

[Eric Hurkman]

Maybe something similar, but using "0.00%" as a number_format works, but "0.000%" does not.

If I change my example code to use "#,##0", it works, but "#,###" does not. "#,###" is what Excel itself recommends, including their Office documentation -- and "0.000%" is a valid format but does not seem to work.

Can anyone confirm?

[Charlie Clark]

Am .10.2014, 01:07 Uhr, schrieb Eric Hurkman <erich...@gmail.com>:

> Using openpyxl==2.1.1. and Python 2.7, OSX.
> I'm seeing some inconsistent behavior in number_format. It does not seem
> to
> copy over correctly when using copy() on styles. Link to a
> Gist:
> https://gist.github.com/erichurkman/8019f57c588bd72ba075/2b4a0f395a02fb14078e8b708fce0872551a53fa
> Is this a bug or am I expecting the wrong thing from copy()ing a style
> with
> a number_format?

Hi,

thanks for the report.

ws['A1'].number_format
'#,###'
ws['A2'].number_format
'#,###' # format was copied
ws['A2'].number_format = '0.0000%'
ws['A2'].number_format
'0.0000%'

In 2.0 we made styles immutable to prevent the styles of different cells
being affected when the style of one was changed. Unfortunately, this
imposes quite a performance penalty as Python doesn't really have the
concept of locking objects after creation, and the interface was very
clunky with the .copy() method.

But some style attributes such as `number_format` have always been
directly assignable (as it's just a string we never check what the value
is). This did change in 2.1 and may be being ignored by the .copy()
method. This is going to be the case for all going forward so that along
with c.number_format = '#,###' you'll be able to do c.font = Font(…) so
that the Style object can be relegated.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Eric Hurkman]

On Tuesday, October 14, 2014 1:52:10 AM UTC-7, Charlie Clark wrote:

ws['A1'].number_format
'#,###'
ws['A2'].number_format
'#,###' # format was copied
ws['A2'].number_format = '0.0000%'
ws['A2'].number_format
'0.0000%'

I've updated my test case to demonstrate the problem, as it appears I'm doing everything correctly from the documentation and your example.

https://gist.github.com/erichurkman/8019f57c588bd72ba075

import openpyxl

from openpyxl.styles import Style, Font

wb = openpyxl.Workbook()

ws = wb.active

# Works; the first time #,### is used

ws['A1'].value = 12311

ws['A1'].style = Style(number_format='#,###', font=Font(color='00AA00'))

print ws['A1'].number_format  # prints #,###

# Does not work; applies the new color but not the number format

ws['A2'].value = 12311

ws['A2'].style = Style(number_format='#,###', font=Font(color='AA00AA'))

print ws['A2'].number_format  # prints #,###

# Works, even though it's a new Style() object it matches A1's style, and works

ws['A3'].value = 12311

ws['A3'].style = Style(number_format='#,###', font=Font(color='00AA00'))

print ws['A3'].number_format

wb.save("tmp.xlsx")

It does print "#,###" as the number_format for all three cells, but when you open the file in Excel, only the first and third rows are correctly formatting. The number_format on A2 is ignored, but the color does pick up.

If I change number_format for A2 to " #,###" instead of "#,###" it works.

Even if I manually set <cell>.number_format = "#,###" for all cells it does not change the outcome. The cells are not formatted in Excel even though printing number_format does show the correct format.

[Charlie Clark]

Am .10.2014, 20:40 Uhr, schrieb Eric Hurkman <erich...@gmail.com>:

> It does print "#,###" as the number_format for all three cells, but when
> you open the file in Excel, only the first and third rows are correctly
> formatting. The number_format on A2 is ignored, but the color does pick
> up.

> If I change number_format for A2 to " #,###" instead of "#,###" it works.

> Even if I manually set <cell>.number_format = "#,###" for all cells it
> does
> not change the outcome. The cells are not formatted in Excel even though
> printing number_format does show the correct format.

Okay, I think I see the problem now. Please submit a bug because I don't
have an a solution off-hand. Looks like this is happening when the styles
are being written – some of the styles are having the number_format set to
None.