Use multiple cells in openpyxl conditional formatting formula

user_14079207 0
Viewed 1653

I am trying to write a spreadsheet in Python which uses conditional formatting to find the largest of three cells in a row and give it a green fill. I have been able to do it for the comparison of two cells, but there is very little information about how to write a formula in the openpyxl documentation that I can see, and there is similarly very little on StackOverflow about the problem.

For two cells, which worked as desired, my code was the below:

sheet.conditional_formatting.add(f'G${row+iter_num}', CellIsRule(operator='greaterThan', \
formula=[f'H${row+iter_num}'], fill=greenfill))
sheet.conditional_formatting.add(f'H${row+iter_num}', CellIsRule(operator='greaterThan', \
formula=[f'G${row+iter_num}'], fill=greenfill))

The {row+iter_num} is required as this is used in a for loop.

To do this comparison for more cells, I tried changing the formula to include and:

sheet.conditional_formatting.add(f'G${row+iter_num}', CellIsRule(operator='greaterThan', \
formula=[f'H${row+iter_num}' and f'I${row+iter_num}'], fill=greenfill))
sheet.conditional_formatting.add(f'H${row+iter_num}', CellIsRule(operator='greaterThan', \
formula=[f'G${row+iter_num}' and f'I${row+iter_num}'], fill=greenfill))
sheet.conditional_formatting.add(f'I${row+iter_num}', CellIsRule(operator='greaterThanOrEqual', \
formula=[f'G${row+iter_num}' and f'H${row+iter_num}'], fill=greenfill))

enter image description here

I am not sure if using and is logical in this situation, but like I said there is very little in the documentation. I also cannot use any comparisons to find which of G, H and I are largest in Python as they are determined by excel functions. For the image above, the green cells for each row should be 200, 16, 82, 1890, 150 (second), 1025, 527, 392, 150 (second), there should only be one green cell per row.

openpyxl python-3.x python
1 Answers

If doing this directly in Excel, I would use a formula. You'll want to put that same formula into the formula parameter to Rule(). Here's a complete working example:

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.styles import Font, PatternFill, Border
from openpyxl.styles.differential import DifferentialStyle

wb = Workbook()
ws = wb.active

ws.append([1, 2, 3])
ws.append([3, 1, 2])
ws.append([2, 3, 1])
ws.append([1, 2, 3])
ws.append([3, 1, 2])
ws.append([2, 3, 1])

green_fill = PatternFill(bgColor="00FF00")
dxf = DifferentialStyle(fill=green_fill)
r = Rule(type="expression", dxf=dxf, formula=["=A1=MAX($A1:$C1)"])
ws.conditional_formatting.add('A1:C6', r)

# Save the file
wb.save("sample.xlsx")
user_113867060
Related