Search code examples
pythonexcelopenpyxlseries

Changing the name of an excel chart series with openpyxl, with python

So, I'm making some line charts with Openpyxl and to make them I changed the key strings of the dict (that is going as input for the workbook) to not be equal and always be a different string, so the data doesn't overwrite because some key string was equal.
The problem is in the chart series name, I need them to be unchanged and not the title from the data column but the unchanged string that I have stored on other list.>br>

Is it possible to just change the name of the series in the chart and leave the column name not changed? (if the column name changed for the name i want the series name to be it was going to exist more than one column with the same name.)

This is the code I have:



x_values = Reference(sheet, min_col=1, min_row=2, max_col=1, max_row=sheet.max_row)
y_values = Reference(sheet, min_col=column_init, min_row=row_num, max_col=column_end, max_row=sheet.max_row)

Create the chart
chart = LineChart()

chart.style = 12

chart.add_data(y_values, titles_from_data=True)
chart.set_categories(y_values)
chart.set_categories(x_values)
chart.x_axis.number_format = 'hh:mm'  # 'dd-mm-yyyy hh:mm:ss'
chart.x_axis.majorTimeUnit = "hours"
chart.x_axis.title = "Date"

graph_location = E2

sheet.add_chart(chart, graph_location)

book.save(excel_name)

I tried to change the series title, but that's not right and throws errors about the object type.

chart.series.title = "SeriesName-bup"
Solution

  • Rather than using titles_from_data=True, create series for your data and set the title as you require

    y_values = Reference(sheet, min_col=column_init, min_row=row_num, max_col=column_end, max_row=sheet.max_row)
series1 = Series(y_values, title='Whatever')
...

chart.append(series1)

    Need to import 'Series' from 'openpyxl.chart' as well.

    Working Example;
    The following example is a self contained example that incorporates your code as able and uses dummy data.

    Ensure that you are using the latest Openpyxl release, 3.1.2

    from openpyxl import Workbook
from openpyxl.chart import Reference, LineChart, Series  # <-- Import Series


book = Workbook()
sheet = book.active

### Test Data
rows = [
    ('Time', 'Values1', 'Values2'),
    ('01:00', 100, 200),
    ('02:00', 200, 300),
    ('03:00', 300, 400),
    ('04:00', 50, 20),
    ('05:00', 500, 600),
    ('06:00', 100, 200),
]

### Write test data to Sheet
for row in rows:
    sheet.append(row)

#initialize LineChart object
chart = LineChart()

### X Axis names
x_values = Reference(sheet, min_col=1, min_row=2, max_col=1, max_row=7)

### Create Series1
data1 = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=7)
series1 = Series(data1, title='SeriesName-bup')
### Add Series1 to the chart
chart.append(series1)

### Create Series2
data2 = Reference(sheet, min_col=3, min_row=1, max_col=3, max_row=7)
series2 = Series(data2, title='ThisIsSeries2')
### Add Series2 to the chart
chart.append(series2)

### Set Chart Style
chart.style = 12

### Set X-axis
chart.set_categories(x_values)

##cosmetics
chart.title = 'Example'
chart.y_axis.title = 'Y Axis'
chart.x_axis.number_format = 'hh:mm'  # 'dd-mm-yyyy hh:mm:ss'
chart.x_axis.majorTimeUnit = "hours"
chart.x_axis.title = "Date"

### Add Chart to Sheet
graph_location = 'E2'
sheet.add_chart(chart, graph_location)

### Save workbook
book.save('line_chart.xlsx')

    Example Line Chart