Openpyxl条件格式：如何基于列规则高亮整行而非单个单元格

Got it, let's tweak your code to highlight the full row instead of just the single cell in column K. The main fixes involve adjusting the conditional formatting scope to cover entire rows and updating the formula references to check the correct cell relative to each row.

Modified Working Code #

import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

# Load the workbook
wb = openpyxl.load_workbook('output.xlsx')
# Get the active sheet
ws = wb.active

# Set reference values in L3-L6
ws["L3"].value = "P"
ws["L4"].value = "F"
ws["L5"].value = "+"
ws["L6"].value = "-"

# Define fill styles
blueFill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid')
greenFill = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')
redFill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
yellowFill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# Update rules to check relative K column cell against fixed L references
# $K locks the column (always check column K), 2 is a relative row number that auto-adjusts
rule1 = CellIsRule(operator='equal', formula=['$K2=$L$3'], stopIfTrue=True, fill=blueFill)
rule2 = CellIsRule(operator='equal', formula=['$K2=$L$4'], stopIfTrue=True, fill=greenFill)
rule3 = CellIsRule(operator='equal', formula=['$K2=$L$5'], stopIfTrue=True, fill=redFill)
rule4 = CellIsRule(operator='equal', formula=['$K2=$L$6'], stopIfTrue=True, fill=yellowFill)

# Apply formatting to entire rows (adjust column range as needed, e.g., A-ZZ covers most use cases)
ws.conditional_formatting.add('A2:ZZ1000', rule1)
ws.conditional_formatting.add('A2:ZZ1000', rule2)
ws.conditional_formatting.add('A2:ZZ1000', rule3)
ws.conditional_formatting.add('A2:ZZ1000', rule4)

# Save the changes
wb.save('output2.xlsx')

Key Changes Explained #

• Relative Formula References: Instead of just $L$3, we use $K2=$L$3. The $K locks the column (so we always check column K), while the 2 is a relative row number—when the rule applies to row 3, it will automatically check $K3=$L$3, and so on for every row in the range.
• Expanded Formatting Range: Changed from K2:K1000 to A2:ZZ1000 to cover all columns in each target row. You can narrow this down (like A2:L1000) if you only need to highlight up to a specific column.
• Retained stopIfTrue=True: This ensures that if a row somehow matches multiple rules (unlikely here, but good practice), it only applies the first matching fill style.

Now every cell in rows 2-1000 will be highlighted when the corresponding column K cell matches the values in L3-L6.

内容的提问来源于stack exchange，提问作者Midnight-Joker