Chart is dropped when chart title is a formula deriving the title from a cell

Issue #948 resolved
Brian Ritz
created an issue

Using openpyxl version 2.5.0b2 inside a docker container on a mac, I have a line chart that is dropped from the output when the title of the chart is dynamically linked to a cell through a formula.

I have attached two example files, one with the undesired behavior and one without. test_chart_with_formula.xlsx is the version of the chart with the formula in the title. test_chart_without_formula.xlsx is the version of the chart without the formula in the title.

The following code reproduces the issue. It round trips each file by loading it in and then writing it out:

import openpyxl
print(openpyxl.__version__) #'2.5.0b2'
wb_with_formula = openpyxl.load_workbook("/data/test_chart_with_formula.xlsx")
wb_with_formula.save("/data/test_chart_with_formula_output.xlsx")
wb_without_formula = openpyxl.load_workbook("/data/test_chart_without_formula.xlsx")
wb_without_formula.save("/data/test_chart_without_formula_output.xlsx")

test_chart_without_formula_output.xlsx will open as expected, with the chart still there as it should be.

When I open test_chart_with_formula_output.xlsx, a dialog box will appear:

scrn.png

If I click "Open and repair", then another dialog box pops up saying: "Excel repaired or removed unreadable content from this workbook. Do you want to view the log file to see what changed, or delete it?" I chose to view the log file, and I've attached it to this issue as well -- it is file Repair Result to test_chart_with_formula_output0.xml.

After all is said and done, the chart is not present on the final file once it is fully opened.

Comments (5)

  1. CharlieC

    Thanks for the report and the sample files. The problem is that the text element of the title element can have either strRef (for formulae) or rich child for standard text which openpyxl always creates so that users don't have to jump through all the hoops associated with rich text.

    Excel always just removes the object from the archive that it doesn't like when it is "repairing" a file which is why the chart is lost.

    Going to be a bit tricky to implement the necessary logic for this so no timeline so you'll have to do without formulae titles for a while.

  2. Log in to comment