How to update range of cells where Excel Conditional Formatting applies with openpyxl

Question

I have an Excel Workbook for which I would like to update the Conditional Formatting by using openpyxl.

The documentation of openpyxl can be found here for Conditional Formatting: https://openpyxl.readthedocs.io/en/stable/formatting.html ... however it only indicates how to create Conditional Formatting and not remove / update it.

I am trying to extend the range of some rules: I have a rule on range A4:V4 and I want to update it to A4:V100.

How can I do this update of range of Conditional Formatting with openpyxl?

Thanks

Answer 1

The current (version 3.0.9) implementation of Conditional Formatting in openpyxl is the following:

1. The Conditional Formatting are stored in an OrderedDict (a dictionary with an order)
2. The keys are ConditionalFormatting objects; it has an attribute sqref for the reference of range (a MultiCellRange object that can be easily converted to a string that looks like A4:V4)
3. The values of this dictionary are a list of rules applied to this range

The documentation clearly indicate ws.conditional_formatting.add(<range>, <rule>) to add a rule, but you can actually access and delete rules like you would with a regular dictionary:

• To access: ws.conditional_formatting[<range>]
• To access: del ws.conditional_formatting[<range>]

One way to update is to delete and add again (like it seems to work if you want to update the reference of a Table in openpyxl by the way).

Put together, that could look like this:

import re
from os import startfile

from openpyxl import load_workbook


wb = load_workbook(xlsx)
ws = wb["My Super Worksheet"]

range_cells = "A4:V4"
new_range = "A4:V100"

rules = ws.conditional_formatting[range_cells]
del ws.conditional_formatting[range_cells]
for rule in rules:
    ws.conditional_formatting.add(new_range, rule)

wb.save()

Note that you can access all the range references for Conditional Formatting with following list comprehension:

refs_conditional_formats = [str(c.sqref) for c in ws.conditional_formatting]