openpyxl conditional formatting - Rule is in Excel file but not the formatting

Question

My goal is to create an Excel file and change the background color of some cells based on their value using conditional formatting, using openpyxl.

When I open the file created with Excel, I can see that the rule is there, but the rule does not include the formatting to apply (background color set to none). The cells have thus no background color, although the border of the cells respecting the formula are not visible, like when the background is white. I don't see if I made a mistake, or if there is some trouble with openpyxl.

Here is a MWE:

from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()
ws = wb.active

ws['B2'] = -2
ws['B3'] = -1
ws['B4'] =  0
ws['C2'] = -1
ws['C3'] =  0
ws['C4'] =  1

fill = PatternFill(start_color='538DD5', fill_type='solid')
ws.conditional_formatting.add('B2:C4', CellIsRule(operator='lessThan', formula=[0], fill=fill))

wb.save('mwe.xlsx')
wb.close()

Answer 1

You need to add the parameter end_color like this :

fill = PatternFill(start_color='538DD5',end_color='538DD5',fill_type='solid')

check this link : https://openpyxl.readthedocs.io/en/stable/formatting.html

from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()
ws = wb.active

ws['B2'] = -2
ws['B3'] = -1
ws['B4'] =  0
ws['C2'] = -1
ws['C3'] =  0
ws['C4'] =  1

fill = PatternFill(start_color='538DD5',end_color='538DD5',fill_type='solid')
#print(fill)
ws.conditional_formatting.add('B2:C4', CellIsRule(operator='lessThan', formula=[0], fill=fill))
wb.save('mwe.xlsx')
wb.close()

result :

Answer 2

Following @GiovaniSalazar answer, I did more tests. The parameters used for the color (start_color, end_color, fgColor, bgColor) do not have the same behaviour with conditional formatting and simple formatting (bug in openpyxl ?).

Here is a comparison of both. The only one working for both formatting is start_color + end_color.

from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()
ws = wb.active

ws['C2'] = -4
ws['C3'] = -3
ws['C4'] = -2
ws['C5'] = -1
ws['D2'] =  4
ws['D3'] =  3
ws['D4'] =  2
ws['D5'] =  1


ws['C1'] = 'Cond. formatting'
ws['F1'] = 'Formatting'

ws['A2'] = 'start+end'
fill = PatternFill(start_color='538DD5', end_color='538DD5', fill_type='solid')
# OK
ws.conditional_formatting.add('C2:D2', CellIsRule(operator='lessThan', formula=[0], fill=fill))
# OK
ws['F2'].fill = fill


ws['A3'] = 'start'
fill = PatternFill(start_color='538DD5', fill_type='solid')
# Problem (white background)
ws.conditional_formatting.add('C3:D3', CellIsRule(operator='lessThan', formula=[0], fill=fill))
# OK
ws['F3'].fill = fill


ws['A4'] = 'fgColor'
fill = PatternFill(fgColor='538DD5', fill_type='solid')
# Problem (white background)
ws.conditional_formatting.add('C4:D4', CellIsRule(operator='lessThan', formula=[0], fill=fill))
# OK
ws['F4'].fill = fill


ws['A5'] = 'bgColor'
fill = PatternFill(bgColor='538DD5', fill_type='solid')
# OK
ws.conditional_formatting.add('C5:D5', CellIsRule(operator='lessThan', formula=[0], fill=fill))
# Problem (black background)
ws['F5'].fill = fill


wb.save('mwe.xlsx')
wb.close()

Output Excel file:

Output Excel file