Simple conditional text formatting with openpyxl by text

Question

I am using openpyxl 3.0.3 and python 3.7.3. I don't know what version of Excel I'm using since it does not seem to say anywhere. It's Office 365.

What I want to do is simple: if the cell value is "fail", make the cell one color, if "pass" another, etc. But everything I try seems to run just fine, but when I try to open the workbook with Excel, it gets the same result: Excel saying "We found a problem with some content" and offering to repair the workbook. When I let it repair, the formatting is gone. I've tried all of the following, taken from various SO answers and other examples:

cellref = 'B2'
red_font = Font(size=14, bold=True, color='ffffff')
red_fill = PatternFill(start_color='ffcccc', end_color='ffcccc', fill_type='solid')

ws2.conditional_formatting.add(cellref, CellIsRule(operator='equal', formula=['fail'], fill=red_fill, font=red_font))
ws2.conditional_formatting.add(cellref, CellIsRule(operator='containsText', formula=['fail'], fill=red_fill, font=red_font))
ws2.conditional_formatting.add(cellref, CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font))
ws2.conditional_formatting.add(cellref, FormulaRule(formula=[f'NOT(ISERROR(SEARCH("fail",{cellref})))'], stopIfTrue=True, fill=red_fill))

All get the identical bad result. I have read the documentation and examples as well as every StackOverflow answer I can find on the subject. This seems like such a simple, basic usage of the package.

Answer 1

I believe it has to do with the combination of options you're choosing. It's actually easier (in my opinion) to declare a Rule and then assign that a type. Your second two rules were good. Compiled fine for me. The first two had the issue. I replaced them with a Rule of 'containsText' type.

Here's what I was able to come up with based on the example you provided:

cellref = 'B1:B6' #adjusted to show more cells
red_font = Font(size=14, bold=True, color='ffffff')
red_fill = PatternFill(start_color='ffcccc', end_color='ffcccc', fill_type='solid')

ws2.conditional_formatting.add(cellref, Rule(type='containsText', operator='containsText', formula=['fail'], dxf = DifferentialStyle(fill=red_fill, font=red_font)))
ws2.conditional_formatting.add(cellref, CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font))
ws2.conditional_formatting.add(cellref, FormulaRule(formula=[f'NOT(ISERROR(SEARCH("fail",{cellref})))'], stopIfTrue=True, fill=red_fill))

Results

Answer 2

I had trouble with the text search condition despite @APhillips helpful answer.

The best thing I got to work was:

from openpyxl import styles, formatting

cellref  = 'B1:B6' #adjusted to show more cells
red_fill = styles.PatternFill(start_color='ffcccc', end_color='ffcccc', fill_type='solid')

rule = formatting.Rule(type='expression')

rule.formula = [f'ISNUMBER(SEARCH("fail", {cellref}))']
rule.dxf     = styles.differential.DifferentialStyle(fill=red_fill)

sheet_object.conditional_formatting.add(cellref, rule)