Python: adding to Excel conditional formatting of type 'containsText'

Question

I am trying to add conditional formatting (highlight cell containing pipe character) to Excel using openpyxl (2.5.4) in Python 2.7. I have tried with many variants of code as seen below. None of it worked. Can you point where is error?

import openpyxl
from openpyxl import formatting, styles, Workbook

wb = openpyxl.load_workbook('export.xlsx')
sheet = wb['all']

red_text = Font(color='9C0006')
red_fill = PatternFill(bgColor='FFC7CE')

Variant 1 - code

sheet.conditional_formatting.add('A1:E4', rule(type='containsText', operator='containsText', text='|', fill=red_fill, font=red_text))

Variant 1 - error

NameError: name 'rule' is not defined

Variant 2 - code

sheet.conditional_formatting.add('A1:E4', CellIsRule(type='containsText', operator='containsText', text='|', fill=red_fill, font=red_text))

Variant 2 - error

TypeError: CellIsRule() got an unexpected keyword argument 'type'

Variant 3 - code

sheet.conditional_formatting.add('A1:E4', formatting.rule.CellIsRule(type='containsText', operator='containsText', text='|', fill=red_fill, font=red_text))

Variant 3 - error

TypeError: CellIsRule() got an unexpected keyword argument 'type'

Variant 4 - code

sheet.conditional_formatting.add('A1:E4', formatting.rule.CellIsRule(operator='containsText', text='|', fill=red_fill, font=red_text))

Variant 4 - error

TypeError: CellIsRule() got an unexpected keyword argument 'text'

Kafka · Accepted Answer · 2018-07-30 08:51:00Z

Have you looked at the samples given on the site?

https://openpyxl.readthedocs.io/en/stable/formatting.html

For example here is one I am using:

ws.conditional_formatting.add('B2:B23',
             CellIsRule(operator='lessThan', formula=['-100'], stopIfTrue=True, 
fill=redFill))

Here is the function itself:

def CellIsRule(operator=None, formula=None, stopIfTrue=None, font=None, border=None, 
fill=None)

Notice the arguments (Operator, Formula, stopIfTrue, font, border, fill)

type & text are not arguments. In your case you likely need something like:

sheet.conditional_formatting.add('A1:E4', 
formatting.rule.CellIsRule(operator='containsText', formula=['|'], fill=red_fill, 
font=red_text))

I will be honest with the latest versions of Office this doesn't all work as smoothly as older versions. Make sure you are using a supported version. :)

JamesD · Accepted Answer · 2019-10-21 09:41:37Z

1

Try the following:

from openpyxl.styles.differential import DifferentialStyle
from openpyxl import styles
from openpyxl.formatting.rule import Rule

red_colour = 'ffc7ce'
red_colour_font = '9c0103'

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

rule = Rule(type='containsText', text='|', stopIfTrue=True)
rule.dxf = DifferentialStyle(font=red_font, border=None, fill=red_fill)
ws.conditional_formatting.add('A1:E4', rule)

This is using openpyxl version 3.0.

This is how I am creating a textContains conditional formatting rule.

answered Oct 21, 2019 at 9:41

JamesD

2,56627 silver badges41 bronze badges

4 Comments

Levon Over a year ago

This works, almost. The conditional formatting is in the file, but not applied until I explicitly go into the document, manage/edit (no changes)/apply the rule. Any idea how I can activate this automatically, or what I am missing?

KJH Over a year ago

@Levon - I have the same issue. Did you happen to find a solution? Thx!

Levon Over a year ago

@KJH No, unfortunately not. I end up going into the file, and just double-clicking the cell with the conditional formatting at the top of the column and it applies it to the rest of the column. Not ideal, but workable, esp since I have to open the file anyway.

johnDanger Over a year ago

@Levon,I ran into this same problem and could only fix it by using type='expression' instead and setting formula=['ISNUMBER(SEARCH("|", A1:E4))']

Robin Hilliard · Accepted Answer · 2020-08-11 07:01:08Z

1

Note that the example in the documentation actually has an example of containsText:

>>> # Highlight cells that contain particular text by using a special formula
>>> red_text = Font(color="9C0006")
>>> red_fill = PatternFill(bgColor="FFC7CE")
>>> dxf = DifferentialStyle(font=red_text, fill=red_fill)
>>> rule = Rule(type="containsText", operator="containsText", text="highlight", dxf=dxf)
>>> rule.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))']
>>> ws.conditional_formatting.add('A1:F40', rule)

It's pretty hacky/horrible, I had to add that formula with the A1 reference updated appropriately to make it work. Without it the conditional formatting rule would appear correctly inside the generated Excel file but it wouldn't actually work until I edited the rule. Perhaps the internal Excel representation actually has something like that formula in it.

answered Aug 11, 2020 at 7:01

Robin Hilliard

5094 silver badges6 bronze badges

1 Comment

Nicu Tofan Over a year ago

FWIW, A1 in SEARCH("highlight",A1) is the address of the first cell in the range. Here's the documentation

Collectives™ on Stack Overflow

Python: adding to Excel conditional formatting of type 'containsText'

Variant 1 - code

Variant 1 - error

Variant 2 - code

Variant 2 - error

Variant 3 - code

Variant 3 - error

Variant 4 - code

Variant 4 - error

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Variant 1 - code

Variant 1 - error

Variant 2 - code

Variant 2 - error

Variant 3 - code

Variant 3 - error

Variant 4 - code

Variant 4 - error

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related