Excel Charts Corrupted After Open/Save with Openpyxl Library

[maxkuzkin]

Description:

When using the Openpyxl library to open and save an Excel file containing charts (please note multiple tabs in the file to simplify case-by-case navigation), the charts become corrupted in the resulting file.

Steps to Reproduce:

1. Take an Excel file (source-file-with-charts.xlsx) with charts.
2. Use the following Python script to open and save the workbook:

import openpyxl

# Load the source workbook
wb = openpyxl.load_workbook('./source-file-with-charts.xlsx')

# Save the workbook to a new file
wb.save('./target-file-with-charts.xlsx')

# Close the workbook
wb.close()

3. Open the resulting Excel file (target-file-with-charts.xlsx).

Expected Result:

The charts in the resulting file (target-file-with-charts.xlsx) should appear the same as in the source file (source-file-with-charts.xlsx).

Actual Result:

The charts in the resulting file (target-file-with-charts.xlsx) are corrupted.

Common Corruptions:

Some of the common corruptions include:

1. Rounded corners are added to charts when they were not needed.
2. Font sizes and colors change to different values.
3. Chart backgrounds and colors change to different values.
4. Certain chart types (like maps) disappear entirely.

Attachments:

• source-file-with-charts.xlsx
• target-file-with-charts.xlsx
• source-vs-target-examples.pptx
• source-vs-target-examples.pdf

Examples from the file (source and corrupted target):

Case 1

Case 2

Case 3

Case 4

Case 5

Case 6

Library Version

This issue has been present in Openpyxl for many years, but I haven't had the chance to report it until now. The version used to reproduce this problem is:

% pip info
Name: openpyxl
Version: 3.1.5
Summary: A Python library to read/write Excel 2010 xlsx/xlsm files
Home-page: https://openpyxl.readthedocs.io
Author: See AUTHORS
Author-email: [email protected]
License: MIT

[maxkuzkin]

I've also reported this issue at https://foss.heptapod.net/openpyxl/openpyxl/-/issues/2220. I'm not sure which issue tracker is the main one for openpyxl, so sorry for the duplicate. You can close either one.

[pajarrige]

Probably caused by 3.1.5 writing property Application as f"Microsoft Excel Compatible / Openpyxl {version}" instead of "Microsoft Excel". Patch line 129 of \Lib\site-packages\openpyxl\packaging\extended.py will solve the issue.
Excel seems to dislike this modified property; you can open the "corrupted" file with LibreOffice and save as .xlsx, then open with Excel: charts look better then.

[maxkuzkin]

Thanks for the hint - I've tested with LibreOffice - indeed, it makes the situation... different. I couldn't say it fixes all of the examples above, but after I open/save there, charts change their look - still break but in very different ways. Will be happy to test all the cases above with a new version and report back what is working better/worse.