Deleting legend entry does not remove item from legend when opening in Excel; chart XML appears correct
Instruction to remove a series from a legend does not work when opening in Excel. This might actually be an issue with the structure of the output chart xml itself, as the expected legendEntry
item is there before opening in Excel.
To replicate:
from openpyxl import chart, Workbook
from openpyxl.chart import legend
workbook = Workbook(write_only=True)
worksheet = workbook.create_sheet('Example')
# set up data
index = ('Index', 1, 2, 3, 4, 5)
show_line = ('Show Line', 2, 5, 6, 8, 10)
hide_line = ('Hide Line', 1, 3, 8, 4, 2)
data = (index, show_line, hide_line)
for row in zip(*data):
worksheet.append(row=row)
# create chart, the categories, and the two series
chart = chart.LineChart()
chart.title = 'Sample Chart'
chart.set_categories(chart.Reference(worksheet, min_col=1, max_col=1, min_row=2, max_row=len(index)))
show_series = chart.Series(
title='Show',
values=chart.Reference(worksheet, min_col=2, max_col=2, min_row=2, max_row=len(show_line)),
)
chart.append(show_series)
hide_series = chart.Series(
title='Hide',
values=chart.Reference(worksheet, min_col=3, max_col=3, min_row=2, max_row=len(hide_line)),
)
chart.append(hide_series)
# remove last series from legend
chart.legend.legendEntry.append(legend.LegendEntry(idx=len(chart.series) - 1, delete=1))
# to be consistent with what we see when removing the item manually...
chart.legend.overlay = 0
worksheet.add_chart(chart, 'D2')
workbook.save('remove_legend_example.xlsx')
The output chart XML document has the legendEntry
item as expected.
<!-- See before_opening_chart1.xml -->
<legend>
<legendPos val="r"/>
<legendEntry>
<idx val="1"/>
<delete val="1"/>
</legendEntry>
<overlay val="0"/>
</legend>
If I open and close the XLSX file, the legendEntry
disappears, and the chart shows the Series in the legend. Note that the legendEntry
node disappears.
<!-- See after_opening_chart1.xml -->
<c:legend>
<c:legendPos val="r"/>
<c:overlay val="0"/>
</c:legend>
While opening the XLSX file, I can manually remove the legend entry. If I do that, the legendEntry
node shows up.
<!-- See manually_removed_legend_item_chart1.xml -->
<c:legend>
<c:legendPos val="r"/>
<c:legendEntry>
<c:idx val="1"/>
<c:delete val="1"/>
</c:legendEntry>
<c:overlay val="0"/>
</c:legend>
Doing a diff between the "before" and "after" documents has not been helpful given the namespace "c" is part of the "after" documents, so it is hard to show the differences.
Attached are the three files.