CODEWITHSUNDEEP
pythonpython-3.xexcelopenpyxl
plegerbil9
plegerbil9

Reputation: 11

In openpyxl, is there a way to see what conditional formatting rule(s) are applied to a cell?

I'm using openpyxl 2.5.6 and py 3.7.0. My goal is to read an Excel workbook and print both the contents and the formatting of each cell into a CSV. For instance, if a cell is blue with text "Data" then I would prepend a tag of "[blu]" to the cell value, printing to the CSV as "[blu]Data" and do this likewise with a cell that's bolded and for other fill colors, etc.

I can do this perfectly fine for cells with static formatting, but not with conditional formatting. My issue is that I don't know how to tell if a conditional formatting rule is applied. I found the conditional_formatting._cf_rules dict, but I'm only seeing attributes like formula, priority, dxfId, and the dxf rules itself. I want to believe that the data of whether a cf rule is applied or not might stored somewhere, but I cannot find where it might be.

My code thus far looks something like this.

from openpyxl import load_workbook

wb = load_workbook('Workbook_Name.xlsx', data_only = True)
ws = wb['Worksheet1']

# Code that shows me each cf rule's formula, fill type, priority, etc
cellrangeslist = list(ws.conditional_formatting._cf_rules)
for cellrange in cellrangeslist:
    print('{:30s}{:^10s}{:30s}'.format('----------------------------',str(cellrange.sqref),'----------------------------'))
    for i in cellrange.cfRule:
        print('{:10s}{:8s}{:40s}{:10s}{:10s}'.format(str(i.dxf.fill.bgColor.index), str(i.dxf.fill.bgColor.type), str(i.formula), str(i.stopIfTrue), str(i.priority)))


# This is where I want to be able to identify which cf rule is applied to a given cell
#
#
#

# Code that interprets cell styling into appropriate tags, e.g.
for r in ws.iter_rows(min_row = ws.min_row, max_row = ws.max_row, min_col = ws.min_column, max_col = ws.max_column):
     for cell in r:
          if cell.font.b == True:
                 cell.value = "[bold]"+cell.value

# Code to write each cell as a string literal to a CSV file
#
#
#

My Excel file looks like this,

And my cf rules look like this,

The console output I receive from the above code is

----------------------------      C1    ----------------------------
FF92D050  rgb     ['$A1-$B1>0']                           None      2
FFFF0000  rgb     ['$A1-$B1<0']                           None      1

The output shows the rules are properly there, but I'm wanting to know if there's a way to tell which of these rules, if any, are actually applied to the cell.

I have a growing suspicion that it's something calculated on runtime of Excel, so my alternative is to write an Excel formula interpreter, but I'm really hoping to avoid that by just about any means as I'm not sure I have the skill to do it.

Upvotes: 1

Views: 1447

Answers (1)

moken
moken

Reputation: 6574

If you don't find a better option, following on from my comment this is an example of what you could do with Xlwings.

For the example output shown, A1 is a higher number than B1 so cell C1 is green.
A1 = 1236
B1 = 1235
If the A1 is changed back to 1234, C1 colour returns to red and if the same code is run after the workbook is saved the 'Colour applied to conditional format cell:' will be for 'Conditional Format 1' i.e. red

import xlwings as xw
from xlwings.constants import RgbColor


def colour_lookup(cfc):
    cell_colour = (key for key, value in colour_dict.items() if value == cfc)
    for key in cell_colour:
        return key


colour_dict = { key: getattr(RgbColor, key) for key in dir(RgbColor) if not key.startswith('_') }


wb = xw.Book('test.xlsx')
ws = wb.sheets('Sheet1')

cf = ws['C1'].api.FormatConditions
print("Number of conditional formatting rules: " + str(cf._inner.Count))
print("Colour applied to conditional format cell:\n\tEnumerated: " +
      str(cf._inner.Parent.DisplayFormat.Interior.Color))
print("\tRGBColor: " + colour_lookup(cf._inner.Parent.DisplayFormat.Interior.Color))

print("------------------------------------------------")

for idx, cf_detail in enumerate(cf, start=1):
    print("Conditional Format " + str(idx))
    print(cf_detail._inner.Formula1)
    print(cf_detail._inner.Interior.Color)
    print("\tRGBColor: " + colour_lookup(cf_detail._inner.Interior.Color))
    print("")

Output

Number of conditional formatting rules: 2
Colour applied to conditional format cell:
    Enumerated: 32768.0
    RGBColor: rgbGreen
------------------------------------------------
Conditional Format 1
=$A1-$B1<0
255.0
    RGBColor: rgbRed

Conditional Format 2
=$A1-$B1>0
32768.0
    RGBColor: rgbGreen

Upvotes: 0

Related Questions