`Workbook._named_styles['Normal'].number_format` behave strangely.

Issue #1130 resolved
Sinyeol An
created an issue

The sample code

from openpyxl import Workbook
from openpyxl.styles.numbers import FORMAT_TEXT
wb = Workbook()
wb._named_styles['Normal'].number_format = FORMAT_TEXT

The expected behavior

  • The "Normal" named style's number format is set to "@" (text format)
  • All the cells' number format is set to "@" (text format)

The current behavior

  • [O] The "Normal" named style's number format is set to "@" (text format)
  • [X] All the cells' number format is set to "@" (text format)
  • It seems to break the relationship between all cells and the "Normal" style.
    • If a cell or a range of cells are set to another style then re-set to this "Normal" style, then the cell's or cells' number format changes into "@" (text format).
    • If you didn't try the previous procedure to re-connect cells with this "Normal" style, although the number format of "Normal" style changes, all the cells are not affected. (It should be, and it is in the Excel application.)

More info

  • This might be related with #920.
  • I'm not sure exactly which component is related with this. It might be workbook or writer or even styles.
  • I've been using Debian (stretch) package and it's only 2.3.x. But I tested this with 2.5.x, too, and got same result.

Comments (9)

  1. CharlieC

    I don't understand what the problem is. Changing one of the builtin styles will not change the formatting of existing cells that have had formats applied.

  2. Sinyeol An reporter

    @CharlieC I don't understand why it is not a problem. Changing font, border, background styles of "Normal" format will change cells' style if they are set with the "Normal" format. Then why wouldn't it do for number format?

    FYI, you've mentioned font color would work in #920.

    normal = wb._named_styles['Normal']
    normal.font.color = "FF0000" # set the default colour to red

    Then, I don't see any reason to prevent this work.

    normal = wb._named_styles['Normal']
    normal.number_format = openpyxl.styles.numbers.FORMAT_TEXT # set the default number format to "text"
  3. Sinyeol An reporter

    Hmm.... okay. Then, this is a part of "unexpected consequences".

    Actually, the code I've noted at the first place is repeatable, because the code doesn't behave randomly. If you want you can check with this:

    import openpyxl
    wb1 = openpyxl.Workbook()
    wb1._named_styles['Normal'].font.color = 'FF0000'
    wb1.active['A1'] = 'wb1'
    wb1.active['A2'] = 1
    wb2 = openpyxl.Workbook()
    wb2._named_styles['Normal'].alignment.horizontal = 'center'
    wb2.active['A1'] = 'wb2'
    wb2.active['A2'] = 2
    wb3 = openpyxl.Workbook()
    wb3._named_styles['Normal'].number_format = openpyxl.styles.numbers.FORMAT_TEXT
    wb3.active['A1'] = 'wb3'
    wb3.active['A2'] = 3

    Apparently, Workbook._named_styles.alignment.* is not applied directly either.

    Also, there seems to be another problem: some of Workbook._named_styles properties are shared through instances. I expected "wb2.xlsx" and "wb3.xlsx" not to have the red color, for example.

  4. CharlieC

    Thanks for the examples. If you look at source of of the files you'll see that the changes are being applied but they have different effects. The first one does redefine the first font in the styles definition, the other ones are applied to the "style templates" and, as such, have no effect unless the style is (re)assigned, ie. ws['A1'].style = 'Normal'. This is the intended behaviour.

    You are working with a private attribute, that does fall very much under the proviso "consenting adults". ;-)

    The (unintended) side-effect is due to the fact that builtins are library globals. I guess this is a bug but also inadvisable use of the API but easy enough to avoid by making sure that workbooks use copies.

  5. Sinyeol An reporter

    Thank you for the explanation, and sorry for not being "consenting adult".

    But I'm still confusing with font related values redefine "styles definition", the others are applied to the "style templates". Why only font is intended to be redefine, and why the others are not?

  6. CharlieC

    See the commit which will prevent this happening in future but basically, if you redefine module globals, such as the default font or border, it will affect all cells that use it. The defined named style used the default font directly, which is why the change was global. As I said, it is inadvisable to change the builtin named styles.

  7. Sinyeol An reporter

    I understand your decision. But then, the comment on #920 seems conflicted. Because, if I get it correctly, the reporter wanted to apply some style globally, instead of having a named style, and your suggestion in the comment is a workaround for that, but with the commit 863ae0a3203e, the workaround will be lost.

  8. Log in to comment