Problems with charts in openpyxl 3.1.4 and later

[John Skilleter]

A number of things seem to have broken with regard to chart layout in version 3.1.4 (and later).

I've run the example bar chart code  from the documentation (https://openpyxl.readthedocs.io/en/stable/charts/bar.html) with 3.1.3 and 3.1.4 and, other than the column widths being to narrow in both cases (I'm ignoring this) the graphs produced by 3.1.3 resemble the illustration in the document, but those produced by 3.1.4 have the following issues:

• Title is too small and is overlaid on the chart instead of being above it
• X and Y axes are missing as are the axis data labels
  
• X and Y axis labels are overlaid on the chart instead of being to the side/below
• Chart frame has rounded corners rather than square

I've attached screen dumps to show the issues - I've just done this for 3.1.3 and 3.1.4 as 3.1.5 produces the same results as 3.1

Technical bits:

• Python 3.10.12
• Code run in a virtual environment with openpyxl the only thing in the requirements.txt file with version set to either '==3.1.3 ' or '==3.1.4'
• Excel from Microsoft 365 Enterprise v2404

For the moment, I've locked the version of openpyxl that I'm using to 3.1.3 so things work as expected, but that feels like a short-term solution.

John Skilleter

[Charlie Clark]

On 5 Jul 2024, at 12:47, John Skilleter wrote:

A number of things seem to have broken with regard to chart layout in
version 3.1.4 (and later).

There are no relevant changes in the code, as you can confirm by comparing the generated XML.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[John Skilleter]

Hi,

Ignoring timestamps in the core.xml file there is a small difference in the generated XML code between the files generated by 3.1.3 and 3.1.4; the app.xml file changes as per the diff below (I've pretty-printed the XML to make it easier to see the differences):

<   <Application>Microsoft Excel</Application>
<   <AppVersion>3.1</AppVersion>
---
>   <Application>Microsoft Excel Compatible / Openpyxl</Application>
>   <AppVersion>3.1.4</AppVersion>

If I unpack the xlsx file generated by 3.1.4 and just remove " Compatible / Openpyxl" from app.xml then re-pack it to an xlsx file Excel displays the charts correctly. The 'AppVersion' change seems to be irrelevant.

I've attached the original, unmodified .xlsx files for reference.

John Skilleter

[Charlie Clark]

On 5 Jul 2024, at 16:10, John Skilleter wrote:

> If I unpack the xlsx file generated by 3.1.4 and just remove " Compatible /
> Openpyxl" from app.xml then re-pack it to an xlsx file Excel displays the
> charts correctly. The 'AppVersion' change seems to be irrelevant.

Not quite. See the discussions on 3.1.5: Microsoft doesn't like major.minor.patch numbering, which is why the Application element includes it. :-/

This element conforms to the specification, so it sounds like a bug in MS Excel.

[John Skilleter]

I agree that it is an Excel bug - LibreOffice Calc is happy with either file.

Unfortunately, my managers like Excel, warts and all.

John

[Charlie Clark]

On 5 Jul 2024, at 16:29, John Skilleter wrote:

I agree that it is an Excel bug - LibreOffice Calc is happy with either
file.

Unfortunately, my managers like Excel, warts and all.

If it's still the case with 3.1.5, I'd suggest you monkey patch the code. LibreOffice does something similar, so it should be possible to include something with the full version number, which is supposed to make debugging easier. But at the moment I don't the time to try out all the possibilities and I'd rather pull all my teeth out. But it is possible that 3.1.5 actually fixes it. If not, maybe you're

I seem to remember seeing something like this years ago, which is why I left things as they were. But it really is useful to be able know exactly which version is in use. And it really is a completely stupid bug by Microsoft. Fortunately, the Office team has actually been fairly receptive to bug reports: I have the impression that those working there now hate some of the design and implementation decisions taken years ago. Overloading CustomFilter's operators with a secret code springs to mind and we know they have no formal validators for the various parts of the specification done in BNF.

I'd, and I think the world, would be much happier if we had a clear definition of the application name, though it's actually irrelevant as the stuff in [ContentTypes].xml specifies what the documents are. The AppVersion could, in theory, denote a specific version of Excel, except that, of course, OOXML shouldn't differentiate between MS Excel, OpenOffice, Openpyxl or John's Funky Spreadsheet…