3

How can I add a conditional formatting rule on a column of numeric data, using pandas and openpyxl, which would use a different colour for the negative against positive values? Ultimately the exact same result one would get when applying the data bar rule in Excel on a column.

Expected output:

enter image description here

I am fairly successful in applying conditional formatting to a column that contains only positive values, using:

from openpyxl.formatting.rule import DataBar

rule_pos = DataBarRule(start_type="percentile",start_value=10, end_type="percentile",
        end_value="90", color="FF638EC6", showValue="None", minLength=None,maxLength=None)

wb["sheet_name"].conditional_formatting.add("K44:K48", rule_pos)

which returns succesfully:

enter image description here

In Excelwriter this can be achieved with conditional_format utilising 'min_color': 'red', and 'max_color':'green' parameters.

I tried several different methods, for example applying both negative and positive rules with green / red data bars respectively, or adjusting the parameters of openpyxl's conditional formatting. The result that I get is the same colour, but smaller bars for the negative values. This proved to be trickier than what I expected.

sophocles
  • 13,593
  • 3
  • 14
  • 33
  • I think you need the equivalents of **bar_negative_color_same** (bar_negative_color) and **bar_axis_position** that Excelwriter provides. Don't see these in the openpyxl docs. – moken Aug 17 '22 at 10:58
  • Exactly, that's what I tried to do, but they are not available in ```openpyxl```. So I guess I am looking for an alternative way. – sophocles Aug 17 '22 at 14:02
  • In situations like this it's often to look at the source XML that Excel produces. You can usually then implement this 1:1 in openpyxl. – Charlie Clark Aug 18 '22 at 13:25

0 Answers0