How to group the x_axis dates in openpyxl

Yarzooo 0
Viewed 40

I want to group the x_axis (Date) so that if there is a cell with 01/01/2022 & another 01/01/2022 it will only show one bar with 01/01/2022, here it actualy print each row with a bar How can i do that in openpyxl please

Thanks in advance

from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference

wb = Workbook(write_only=True)
ws = wb.create_sheet()

rows = [
    ('Date', 'Batch 1'),
    (01/01/2022, 10),
    (01/01/2022, 40),
    (02/01/2022, 50),
    (04/01/2022, 20)
]


for row in rows:
    ws.append(row)


chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=5, max_col=2)
cats = Reference(ws, min_col=1, min_row=2, max_row=5)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
openpyxl pandas python
1 Answers

So you want a stacked chart like this then?

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference


rows = [
    ('Date', 'Batch 1', 'Batch 2'),
    ('01/01/2022', 10, 40),
    ('02/01/2022', 50, ''),
    ('04/01/2022', 20, '')
]

wb = Workbook(write_only=True)
ws = wb.create_sheet()

for row in rows:
    ws.append(row)

chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.grouping = "stacked"  # Stacked column chart 
chart1.overlap = 100  # Set overlap to 100% so top column sits squarely on the bottom column
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=4, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")

wb.save('new_chart1.xlsx')
moken0
Related