CODEWITHSUNDEEP
pythonopenpyxl
Hiten Chedda
Hiten Chedda

Reputation: 41

Customized series title

I am trying to modify the existing xlsx sheet adding graphs to it using openpyxl module in Python. But while creating a line chart the series title is shown as Series 1,Series 2,Series 3,Series 4 where I need to rename the series title as "A", "B", "C", D" (these names are not fetched from any cell).

Another solution would be to give series a name from another worksheet apart from row/column of the same worksheet. But I'm not sure if it is doable.

My code gives me the series name from the row/column of worksheet 'ws'. But I need to rename it (customize name) or assign the series name from another sheet. How do I do this?

c1 = LineChart()
c1.title = worksheet
c1.x_axis.title = "Average depth"
c1.y_axis.title = "Average Response time (ms)"
c1.y_axis.majorGridlines = None

refseries1 = Reference(ws, min_col=5, min_row=2, max_col=5, max_row=9)
seriesdata1 = Reference(ws, min_col=6, min_row=10, max_col=6, max_row=15)

c1.add_data(data=seriesdata1,titles_from_data=False)
c1.set_categories(refseries1)

In above code refseries1 is x-axis data and seriesdata1 is y-axis data.

Upvotes: 2

Views: 8725

Answers (2)

cobyjohnson
cobyjohnson

Reputation: 1

All the "chart types" like bar or pie of the openpyxl.chart.series module have a tx attribute that has to be of type openpyxl.chart.series.SeriesLabel. This has two options:

  • strRef, which is an Excel cell that has a string like A1.
  • v, for nested text, meaning you don't have to specify a reference.

This is my approach now:

from openpyxl.chart.series import Series, SeriesLabel
series = Series(tx=SeriesLabel(v="data01"))
series.xvalues = x_values
series.yvalues = y_values

Upvotes: 0

JimA
JimA

Reputation: 99

I found a solution in a different question that works for me:

from openpyxl.chart import LineChart, Reference, Series

chart1 = LineChart()
# setup and append the first series
values = Reference(dprsheet, min_col=2, min_row=1, max_row=34)
series = Series(values, title="First series of values")
chart1.append(series)

# setup and append the second series
values = Reference(dprsheet, min_col=3, min_row=1, max_row=34)
series = Series(values, title="Second series of values")
chart1.append(series)

#set x-axis
dates = Reference(dprsheet, min_col=2, min_row=1, max_row=34)
chart1.set_categories(dates)

It's a bit more fiddly than the chart.add_data method, I'm going to assume that if you dig around in the code for that method you'll find something a bit more... pythony.

Upvotes: 3

Related Questions