openpyxl modifies existing chart/does not read full chart format (?)

[fhg-isi]

• I use openpyxl to generate a bar chart and save it on a sheet in an Excel file.
• Then I read that Excel file and create a second bar chart sheet, using the same functions and save it again.

=> The chart on the first sheet looks differnt then the chart on the second sheet. The first chart misses for example a background hatch.

=> It seems that openpyxl does not consider all chart information when reading existing Excel files?

=> How can I append to existing Excel files while keeping existing chart formatting?

I created a small code example to illustrate the issue (see below). In my real case, in addition to the grid pattern further information is lost, for example line styles.

a) With reading step the fill pattern is missing:

b) Without reading step the fill pattern is present:

import os

import openpyxl
from openpyxl.chart.shapes import GraphicalProperties
from openpyxl.drawing.colors import ColorChoice
from openpyxl.drawing.fill import PatternFillProperties


def main():
    file_path = 'export.xlsx'

    data = [
        ["Category", 2017, 2018, 2019],
        ["Apples", 10, 7, 12],
        ["Oranges", 5, 3, 4],
        ["Bananas", 8, 6, 9],
    ]

    sub_sectors = ['foo', 'baa']

    for sub_sector in sub_sectors:
        workbook = _load_or_create_workbook(file_path)
        worksheet = workbook.create_sheet(sub_sector)

        _write_data(worksheet, data)
        chart = _create_chart(worksheet)
        _adapt_chart(chart)  # this step is lost when reading the first version of the file

        workbook.save(file_path)


def _write_data(worksheet, data):
    for row in data:
        worksheet.append(row)


def _load_or_create_workbook(file_path):
    if os.path.exists(file_path):
        workbook = openpyxl.load_workbook(file_path)
    else:
        workbook = openpyxl.Workbook()
    return workbook


def _create_chart(worksheet):

    # Create a stacked bar chart
    chart = openpyxl.chart.BarChart()
    chart.title = "Stacked Bar Chart"
    chart.x_axis.title = "Year"
    chart.y_axis.title = "Amount"
    chart.type = "col"
    chart.grouping = "stacked"
    chart.overlap = 100
    values = openpyxl.chart.Reference(
        worksheet,
        min_col=1,
        max_col=4,
        min_row=2,
        max_row=4,
    )
    chart.add_data(
        values,
        titles_from_data=True,
        from_rows=True,
    )
    categories = openpyxl.chart.Reference(
        worksheet,
        min_col=2,
        max_col=4,
        min_row=1,
        max_row=1,
    )
    chart.set_categories(categories)
    worksheet.add_chart(chart, "F1")
    return chart


def _adapt_chart(chart):
    plot_area = chart.plot_area
    fill = PatternFillProperties('ltUpDiag')
    fill.foreground = ColorChoice(srgbClr='d0d0d0')
    fill.background = ColorChoice(srgbClr='ffffff')
    if plot_area.graphicalProperties is None:
        plot_area.graphicalProperties = GraphicalProperties()
    plot_area.graphicalProperties.pattFill = fill


if __name__ == '__main__':
    main()