[FIXED] How to split the combined graph into a single graph separated in openpyxl

Issue

Required to display the graph plotted by taking each row in X-Axis over a column set as Y-Axis. Here is my data imported from an Excel sheet and displayed into a separate Excel sheet.

from openpyxl import Workbook
from openpyxl.chart import (
    Reference,
    BarChart3D
)

wb = Workbook()
fileItemPath = os.path.join(os.path.dirname(__file__), "Data.xlsx")
print('Input_FilePath: {}'.format(fileItemPath))
wb = openpyxl.load_workbook(fileItemPath)
sheet = wb['TMO_Reliability_Stats_Combined']

# This part of the code assigning to data causing the whole trouble...
data = Reference(sheet, min_col=2, min_row=2, max_col=2, max_row=6)

titles = Reference(sheet, min_col=2, max_col=2, min_row=1, max_row=1)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True, from_rows=True)
chart.set_categories(titles)
sheet.add_chart(chart, "A10")
wb.save("bar3d.xlsx")

In the above code, I am not able to assign the correct values data = Reference(sheet, min_col=2, min_row=2, max_col=2, max_row=6)

Actual Result: Displaying combined graph instead of single.

Expected Result: Single graph needs to be plotted separately. The combined needs to be eliminated as shown in the snapshot

Solution

I'm uncertain of exactly why you are getting this error, but I believe it has to do with you using the openpyxl bar chart in a way it wasn't quite intended. It could just as easily something to do with Excel. Deep diving into the openpyxl source code for this module hasn't given me an answer yet.

However, altering your data to include the first column does solve your issue. The only way I could get a seperate graph for 2021 was to move the data and use cases so they were a seperate pair. While this isn't an ideal solution if you have many years, it does work as you intended.

from openpyxl import Workbook
from openpyxl.chart import (
    Reference,
    BarChart3D
)
import os
import openpyxl

wb = Workbook()
fileItemPath = os.path.join(os.path.dirname(__file__), "Data.xlsx")
print('Input_FilePath: {}'.format(fileItemPath))
wb = openpyxl.load_workbook(fileItemPath)
sheet = wb['Sheet1']

# Copying/moving 2021 data
for row in sheet.iter_rows(min_col=1, max_col=5):
    # Copy data
    colA = row[0].value
    colC = row[2].value

    # Delete colC
    row[2].value = None

    # Paste data
    row[3].value = colA
    row[4].value = colC

# 2020 chart
data = Reference(sheet, min_col=1, min_row=2, max_col=2, max_row=6)
titles = Reference(sheet, min_col=2, min_row=1)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True, from_rows=True)
chart.set_categories(titles)
sheet.add_chart(chart, "A10")

# 2021 chart
data = Reference(sheet, min_col=4, min_row=2, max_col=5, max_row=6)
titles = Reference(sheet, min_col=5, min_row=1)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True, from_rows=True)
chart.set_categories(titles)
sheet.add_chart(chart, "J10")

# Saving
wb.save("bar3d.xlsx")

Outputted bar charts:

Answered By - jezza_99

Answer Checked By - Robin (PHPFixing Admin)