For Loop for Openpyxl Chart Position

Question

I am trying to make a for loop to iterate through a list of chart names and positions to make it easier to add charts to excel using openpyxl. Here is what I have tried so far.

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

def trysomething():

    letterlist=['B', 'J', 'R', 'Z']
    numlist=[22, 38, 54, 70]
    position=[]
    for k in letterlist:
        for l in numlist:
            pos = k+str(l)
            position.append(pos)

    chartlist=['chart']
    chartnum=[]
    for j in range(1,6):
        chartnum.append(j)

    cha=[]
    for h in chartlist:
        for s in chartnum:
            ch=h+str(s)
            cha.append(ch)

    wb = Workbook()
    ws = wb.active
    for i in range(10):
        ws.append([i])

    values1 = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    chart1 = BarChart()
    chart1.add_data(values1)

    values2 = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    chart2 = BarChart()
    chart2.add_data(values2)

    values3 = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    chart3 = BarChart()
    chart3.add_data(values3)

    values4 = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    chart4 = BarChart()
    chart4.add_data(values4)

    values5 = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    chart5 = BarChart()
    chart5.add_data(values5)

    for a, b in zip(iter(cha), iter(position)):
         print(a,b)
         ws.add_chart(str(a), str(b))

     wb.save("SampleChart.xlsx")

trysomething()

When I print(a,b) it outputs the correct chart with the correct chart position, however, I does not work for adding the chart.

Any help is appreciated!

Answer 1

Workbook().active.add_chart() takes a Chart object and a string cell position. When printing the values (chart, chart position) you should expect to see an instance of Chart <class 'openpyxl.chart.bar_chart.BarChart'> and a 'string', but instead you see a and b are both <str> (string) objects, so your code is passing two string objects to the .add_chart(), no Chart.

You'll want to do something like create a chart_list and iterate over it:

charts_list = [chart1, chart2, chart3, chart4, chart5]

for a, b in zip(iter(charts_list), iter(position)):
     # print(type(a))  # <class 'openpyxl.chart.bar_chart.BarChart'>
     # print(type(b))  # <type 'str'>
     ws.add_chart(a, b)

Viewing the created Excel file should resemble something like the following with 5 charts.

Hope this helps.