Issue reading custom style from Excel file

[Gar Thompson]

Hi,

To avoid having to maintain code to define structure and styling of an excel file output, I'm trying to add data to a template file. I have defined a simple custom style in the template workbook which contains number formatting and external borders. When I read the workbook, openpyxl recognises the custom style, but applies a blue font colour, loses the border definition and sets the number_format to 'General'. Does anybody know if this is something that should work, or should I look at a different approach?

Thanks,

Gar.

[Charlie Clark]

Difficult to say without a file but I as you mention borders I suspect you
might be referring to a known issue related to merged cells: only the
top-left cell is formatted. However, that wouldn't explain the problems
with font colour and number format.

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

[Gar Thompson]

Hi Charlie,

I've attached a test template and output file. The code used to generate the output file is as follows:

from decimal import Decimal

from openpyxl import load_workbook

wb = load_workbook('test-template.xlsx')

val = Decimal('1234.567')

cell = wb.active.cell(row=1, column=1)

cell.set_explicit_value(value=val, data_type=cell.TYPE_NUMERIC)

cell.style = 'test_style'

wb.save(filename='test-output.xlsx')

Digging into it further, I don't see any number formats defined in the styles.xml of the template file (generated by Excel for Mac v15.33). I'm using one of the built-in Excel number formats - namely '#,##0.00'. There's an empty numFmts element in the output styles.xml.

Losing the borders could be related to a 'feature' (I'm being kind) in Excel that reared its head in https://bitbucket.org/openpyxl/openpyxl/issues/453/loading-workbook-fails-on-borders-style. The template file has two borders defined without id. It must be implying the one to use based on element order. I guess openpyxl sees these as duplicates and de-duplicates them. At least you now know one of the tools that is generating such output!

Regards,

Garrett.

[Charlie Clark]

Am .06.2017, 15:04 Uhr, schrieb Gar Thompson <gar...@gmail.com>:

> I've attached a test template and output file. The code used to generate
> the output file is as follows:

Thanks. Which version of openpyxl are you using? When I run the code
locally (explicit type-setting is not required) then the border is
preserved.

[Gar Thompson]

I'm using v2.4.5. I've tried on v2.4.8 also, with the same results.

[Charlie Clark]

Am .06.2017, 17:08 Uhr, schrieb Gar Thompson <gar...@gmail.com>:

> I'm using v2.4.5. I've tried on v2.4.8 also, with the same results.

I think there may be something wrong with your installation as I cannot
reproduce the problem on my machine and it is the structure of the
styles.xml which is broken.

You should be able to check this by inspecting wb.named_styles, the full
definition is in wb._named_styles

The following works for me with 2.4 & 2.5

from decimal import Decimal
from openpyxl import load_workbook

wb = load_workbook('Issues/test-template.xlsx')
cell = wb.active.cell(row=1, column=1)
cell.value = Decimal('1234.567')
cell.style = 'test_style'
wb.save("Issues/test-25.xlsx")

[g.tho...@keelvar.com]

Hi Charlie,

Thanks for taking the time to investigate this. I've tried updating all libs that I thought could possibly be related, but am still seeing issues.

The borders come across fine in some scenarios and not in others. It actually works in the sample file I uploaded previously.

The main issue I'm seeing is with the number formats not being applied. Any of the built-in formats are coming across as 'General'. If I use a custom format - e.g. ('##0.00' - just made up for testing!), it gets applied and round-trips fine. I've attached screenshots from excel of the input and output of a different test file. You can see that the number format isn't applied.

There also looks to be an issue with round-tripping the alignment - viewing the style in excel shows 'General' instead of 'Horizontal Center', but the style is being applied correctly. This doesn't impact me.

Regards,

Gar.

[Charlie Clark]

Am .06.2017, 12:10 Uhr, schrieb <g.tho...@keelvar.com>:

> Thanks for taking the time to investigate this. I've tried updating all
> libs that I thought could possibly be related, but am still seeing
> issues.

I can't rule anything out but as I couldn't recreate the problem here
there's not much I can do. If the files that do have problems have
sensitive information then you can send them to me directly.