Search code examples
pythonexcelgraph3dopenpyxl

How can I make a 3D bar graph and plot it on excel using data where the negative values on the graph will be colored as well?

I am trying to use data from an excel, now hardcoded, and create a 3D graph from it. However, all of my graphs are bringing back colored graphs where the negative values (bars) are black and white. Is there a way I can have the bars colored the same way the positive values are?

This is my code:

import openpyxl
from openpyxl.chart import (
Reference,
Series,
BarChart3D,
)

wb = Workbook()
ws = wb.active

rows = [
(None, 2013, 2014),
("Apples", -5, -4),
("Oranges", 6, -2),
("Pears", -8, 3)
]

for row in rows:
ws.append(row)

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.style = 2
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
chart.series[0].graphicalProperties.solidFill = 'FFFF66'

ws.add_chart(chart, "E5")
wb.save("bar3d.xlsx")

I have attempted the following:

  • Changing the style of the graph with chart.style
  • Converting the values from negative to positive but I am also appending this data to the excel and doing other calculations so I need the original format
  • Creating a temporary file and deleting it (I don't want another excel saved or hidden), but this would delete the pointer for the graph

Any solutions are helpful.

Solution

  • Current Excel versions have a new feature to explicitly set fill color for negative bars in bar-chart-series. This is done using a combination of the series property invertIfNegative = True and providing a second color. Some Excel versions take invertIfNegative = True as the default, if not set. And openpyxl does not explicitly set invertIfNegative. In Excel versions, which take invertIfNegative = True as the default, the negative bars will be white (in fact no color) as no second color is set.

    To always set invertIfNegative to False, which means to take same color for positive as well as for negative, one would must set this explicitly:

    ...
for series in chart.series:
    series.invertIfNegative=False
...

    That should work then for all current Excel versions.