user2991243 user2991243 - 6 months ago 29
Python Question

How we can draw two series of data in "openpyxl" package of python (line-chart)

Suppose that we have this code:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

for row in range(1,10):
value = ws.cell(row=row,column=1).value = row+5

for row in range(1,10):
value2 = ws.cell(row=row,column=2).value = row

wb.save("SampleChart.xlsx")
from openpyxl.charts import Reference, Series,LineChart

values = Reference(ws, (1, 1), (9, 1))
series = Series(values, title="First series of values")
chart = LineChart()
chart.append(series)
chart.drawing.name = 'This is my chart'
ws.add_chart(chart)
wb.save("SampleChart.xlsx")


How can i plot second column values to same line-chart? (and add
legend
)?

Answer

I rearranged your code, slightly, so that the chart and its properties are declared before the series are created. Then, it's simply a matter of repeating your series creation on the second range of values. As far as I know, unless I'm misunderstanding the question, Excel creates the legend automatically.

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

for row in range(1,10):
        value = ws.cell(row=row,column=1).value = row+5

for row in range(1,10):
        value2 = ws.cell(row=row,column=2).value = row

wb.save("SampleChart.xlsx")

from openpyxl.charts import Reference, Series,LineChart

# setup the chart
chart = LineChart()
chart.drawing.name = 'This is my chart'

# setup and append the first series
values = Reference(ws, (1, 1), (9, 1))
series = Series(values, title="First series of values")
chart.append(series)

# setup and append the second series
values = Reference(ws, (1, 2), (9, 2))
series = Series(values, title="Second series of values")
chart.append(series)

ws.add_chart(chart)
wb.save("SampleChart.xlsx")