openpyxl font conditional formatting

Question

I am using pyopenxl to output some excel spreadsheets, I encountered a problem with font conditional formatting. I want to highlight the cells lesser than 0 with red color and here's what I've done:

from pyopenxl import formatting, styles

red_font = styles.Font(size=self.font_size, bold=bold, color=self.red_color_font)
red_fill = styles.PatternFill(start_color=self.red_color, end_color=self.red_color, fill_type='solid')

self.ws.conditional_formatting.add(
    cell.coordinate,
    formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font)
)

So I simply created styles for font and fill and applied them for my cell. The bad thing is that it doesn't work. As soon as I remove the font formatting from the CellIsRule() everything goes back to normal and I'm having my cell filled with red. But the thing is that I need to change the color as well, does anyone has any idea what's wrong with my code? Or maybe with openpyxl?

How is red_color and red_color_font defined? Having red text with a red background would result in a red cell with no visible text. — Martin Evans
They are RGB format strings, like self.red_color = 'ffc7ce' and self.red_color_font = '9c0103'. I should also add that they work perfectly well in any other situation, just not with conditional formatting :/ — Nhor
What version of openpyxl are you using? import openpyxl; print openpyxl.__version__ This seems to work fine for me on 2.2.6 — Martin Evans
I was using 2.2.5 and today switched to 2.2.6 but with no success. Could you please paste your code? — Nhor

Martin Evans Martin Evans · Accepted Answer · 2015-09-08T09:25:33

To highlight cells that are less than zero, you can use the following code when using openpyxl version 2.2.6:

from openpyxl import formatting, styles

wb = Workbook()
ws = wb.active

red_color = 'ffc7ce'
red_color_font = '9c0103'

red_font = styles.Font(size=14, bold=True, color=red_color_font)
red_fill = styles.PatternFill(start_color=red_color, end_color=red_color, fill_type='solid')

for row in range(1,10):            
    ws.cell(row=row, column=1, value=row-5)
    ws.cell(row=row, column=2, value=row-5)
               
ws.conditional_formatting.add('A1:A10', formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font))
ws.conditional_formatting.add('B1:B10', formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill))
wb.save("test.xlsx")

This displays as follows:

For openpyxl version 2.5.1,CellIsRule is now inside rule as follows:

from openpyxl import formatting, styles, Workbook

wb = Workbook()
ws = wb.active

red_color = 'ffc7ce'
red_color_font = '9c0103'

red_font = styles.Font(size=14, bold=True, color=red_color_font)
red_fill = styles.PatternFill(start_color=red_color, end_color=red_color, fill_type='solid')

for row in range(1,10):            
    ws.cell(row=row, column=1, value=row-5)
    ws.cell(row=row, column=2, value=row-5)

ws.conditional_formatting.add('A1:A10', formatting.rule.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font))
ws.conditional_formatting.add('B1:B10', formatting.rule.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill))
wb.save("test.xlsx")

openpyxl font conditional formatting

1 Answers