openpyxl set color of bar in bar chart

Question

I would like to set the color of the bar in a bar chart with openpyxl. I created the following

data = Reference(sheet, min_col=3, min_row=6, max_col=4, max_row=10)
titles = Reference(sheet, min_col=1, min_row=6, max_row=10)
chart = BarChart3D()
chart.title = title
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)

I found here How to set Background Color of Plot Area of Chart using openpyxl how change the background color using chart.plot_area.graphical_properties

However, I don't know to change the color of the bars.

Ulibarri

Answer 1

For 2D plots I'm using graphicalProperties.line.solidFill and graphicalProperties.solidFill :

wb = load_workbook('data.xlsx')
ws = wb['sheet1']

chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Chart Title"
chart.y_axis.title = 'Y Axis'
chart.x_axis.title = 'X Axis'

data = Reference(ws, min_col=3, min_row=1, max_row=3, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=3)

chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4

# Change bar filling and line color 
s = chart.series[0]
s.graphicalProperties.line.solidFill = "00000"
s.graphicalProperties.solidFill = "ff9900" 


ws.add_chart(chart, "A10")
wb.save("bar.xlsx")

I hope it's the same for 3D plots

Hexangular

yes, it works. Ideally, I would like to have custom colors different for each column though, while now they all have the same color. – Ulibarri

@Nicolas thank you. Is there a way to make an area chart transparent? Could you reply to my latest post? Thanks – Feeder

Answer 2

I changed the one_color chart to a multiple_color chart with the attribute of the chart.varyColors:

chart.varyColors = "0000FFFF"

The string "0000FFFF" its the color value in the module openpyxl, you can choose your own https://openpyxl.readthedocs.io/en/stable/styles.html#colours

Munro

Answer 3

If you want each bar to have it's own color like you mentioned in the reply to Nicolas's answer.

slices = [DataPoint(idx=i) for i in range(3)]
sliceOne, sliceTwo, sliceThree = slices
sliceOne.graphicalProperties.solidFill = "02c212"
sliceTwo.graphicalProperties.solidFill = "d62b00"
sliceThree.graphicalProperties.solidFill = "f5ad31"
chart.series[0].data_points = slices

I understand I'm 5 years too late but in case someone else needs it.

Noria

still useful! thanks for the tip. – Ulibarri

Answer 4

You can change color of each set of data using the following line:

chart.series[0].graphicalProperties.solidFill = 'FFFF00'
chart.series[1].graphicalProperties.solidFill = '000FFF'

where 'FFFF00' is your color of choice and [0] is series of data.

Bluecollar