Last Updated on July 14, 2022 by Jay
We can use openpyxl to customize Excel chart settings such as the color, pattern/style, or even adding a secondary axis.
This is part 2 and a follow-up to a previous tutorial that you can find here: How to Create Charts in Excel with Python openpyxl. Make sure you understand what we did in the first part to create the chart.
Code To Create The Basic Chart
Below is the code we used from the last tutorial. We modified the data a little bit to include a cumulative sum for both Apple and Banana sales. Most of the new code we’ll add is right before where we add the chart to a worksheet.
from openpyxl import Workbook from openpyxl.chart import Reference, LineChart wb = Workbook() ws = wb.active ws.title = 'chart' rows = [ ('Month', 'Apple Sales', 'Banana Sales', 'Apple Cumulative', 'Banana Cumulative'), ('Jan', 100, 200, 100, 200), ('Feb', 200, 300, 300, 500), ('Mar', 300, 400, 600, 900), ('Apr', 50, 20, 650, 920), ('May', 500, 600, 1150, 1520), ('Jun', 100, 200, 1250, 1720), ] for row in rows: ws.append(row) values = Reference(ws, min_col= 2, min_row=1, max_col = 5, max_row = 7) x_values = Reference(ws, range_string="chart!A2:A7") #initialize LineChart object chart = LineChart() #add data to the LineChart object chart.add_data(values, titles_from_data = True) #set x-axis chart.set_categories(x_values) ##cosmetics chart.title = 'Fruit Sales' chart.x_axis.title = 'Month' chart.y_axis.title = 'Fruit Sales (USD Mil)' chart.legend.position = 'b' ################### NEW CODE HERE ################### ws.add_chart(chart, 'H1') wb.save('chart_eg.xlsx')
Changing The Charts Color And Pattern In openpyxl
We know that in Excel, the underlying data for a chart is called “Series”. It appears the openpyxl developers have adopted that name as well. There are 2 series in our example.
In Python, we can access these series objects like the following:
s1 = chart.series s2 = chart.series s3 = chart.series s4 = chart.series
- chart.series refers to the Apple Sales series
- chart.series refers to the Banana Sales series
- And chart.series and chart.series refer to the Apple and Banana cumulative sales, respectively
These series objects have a bunch of parameters we can modify.
For example, let’s change the Apple Sales series to cyan, which has a RGB hex value of “00FFFF”, then we’ll change it from a solid line to a dash line. Note you have to save the Excel file to view the changes.
s1.graphicalProperties.line.solidFill = "00FFFF" s1.graphicalProperties.line.dashStyle = "dash"
Let’s then change the Banana Sales series. Instead of a line, let’s do stars with red color “FF0000”.
s2.marker.symbol = "star" #change line to start s2.graphicalProperties.line.noFill = True #hide line s2.marker.graphicalProperties.line.solidFill = "00FF00" #Marker color
Add A Secondary Y Axis
Sometimes we add a secondary y-axis to the chart so the graph scaling looks better. Let’s put the cumulative sales on a secondary y-axis. We need to take the following steps to do this:
- Remove the cumulative data series from the first chart
- Then create a new chart2 object with the cumulative data like normal
- Set the y_axis.axId for chart2 to 200
- Set the chart2.y_axis.crosses to max. This will display y-axis of the secondary chart on the far right end on the x-axis
- Combine the two charts
#remove the cumulative data series from the first chart values = Reference(ws, min_col= 2, min_row=1, max_col = 3, max_row = 7) #create 2nd chart values2 = Reference(ws, min_col= 4, min_row=1, max_col = 5, max_row = 7) chart2 = LineChart() chart2.add_data(values2, titles_from_data=True) chart2.y_axis.title = 'Cumultaive Sales' chart2.y_axis.axId = 200 #secondary y-axis ID chart2.y_axis.crosses = 'max' #display y-axis of the 2nd chart on the far right end on the x-axis chart += chart2
Now the cumulative sales data is on a separate axis, and we can modify the color or patterns like usual. Feel free to try this on your own as an exercise!
How to Create Charts in Excel with Python openpyxl
How to Use Python to Read Excel Formula
Work with Excel Named Range in Python
But how can I change title, lebels and values on the chart? And how can I remove grid from the chart?