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:
