Pandas XLSX export with background color based on cell value

This example script uses openpyxl to set a cell’s background color to red for False cells or to green for True cells. Note that we convert the boolean values to "True"or "False" strings based on the method we outlined in our previous post How to write Pandas bool column as True/False instead of 1/0 to XLSX. For more details on how to just set the background color in OpenPyXL, see our post on How to set cell background color in OpenPyXL.

import pandas as pd
from openpyxl.styles import PatternFill

df = pd.DataFrame([
    {"a": True},
    {"a": False},
    {"a": True},
    {"a": False},
    {"a": False},
])
df["a"] = df["a"].map({True: "True", False: "False"})
with pd.ExcelWriter("out.xlsx", engine="openpyxl") as writer:
    sheet_name = "Bool"
    # Export DataFrame content
    df.to_excel(writer, sheet_name=sheet_name)
    # Set backgrund colors depending on cell values
    sheet = writer.sheets[sheet_name]
    for cell, in sheet[f'B2:B{len(df) + 1}']: # Skip header row, process as many rows as there are DataFrames
        value = df["a"].iloc[cell.row - 2] # value is "True" or "False"
        cell.fill = PatternFill("solid", start_color=("5cb800" if value == "True" else 'ff2800'))

The output from this script looks like this: