How to color excel cells with specific values in dataframe Python

Question

I created a code to insert my dataframe, called df3, into an excel file.

My code is working fine, but now I'd like to change background cells color in all cells based on value

I tried this solution, I don't get any errors but I also don't get any cells colored

My code:

def cond_formatting(x):
    if x == 'OK':
        return 'background-color: lightgreen'
    elif x == 'NO':
        return 'background-color: red'
    else:
        return None
    
print(pd.merge(df, df2, left_on='uniquefield', right_on='uniquefield2', how='left').drop('uniquefield2', axis=1))
df3 = df.merge(df2, left_on='uniquefield', right_on='uniquefield2', how='left').drop(['uniquefield2', 'tournament2', 'home2', 'away2', 'result2'], axis=1) 
df3 = df3[["home","away","scorehome","scoreaway","best_bets","oddtwo","oddthree","htresult","shresult","result","over05ht","over15ht","over05sh","over15sh","over05","over15","over25","over35","over45","goal","esito","tournament","uniquefield"]]
df3 = df3.sort_values('best_bets')

df3.style.applymap(cond_formatting)



# determining the name of the file
file_name = camp + '_Last_20' + '.xlsx'
 
# saving the excel
df3.to_excel(file_name, freeze_panes=(1, 0))
print('Tournament is written to Excel File successfully.')

How I said, code is working but all background cells color are white (no colors) any suggestion?

Thanks for your help

Answer 1

If you provided complete code, it would be easier for me to set colors for the cells for your excel file. But an example script given below might assist you:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

data = {
    "ID": [1, 2, 3, 4, 5],
    "Status": ["OK", "NO", "OK", "NO", "OK"]
}

df = pd.DataFrame(data)

excel_filename = "status.xlsx"
df.to_excel(excel_filename, index=False)

wb = load_workbook(excel_filename)
ws = wb.active

# Define fill colors
green_fill = PatternFill(start_color="00FF00", end_color="00FF00", fill_type="solid")  # Green for "OK"
red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")    # Red for "NO"

# Apply color based on cell value in the "Status" column
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=2, max_col=2):  
    for cell in row:
        if cell.value == "OK":
            cell.fill = green_fill
        elif cell.value == "NO":
            cell.fill = red_fill

wb.save(excel_filename)

print("Excel file created successfully!")

Thank you. If you require more assistance, please write a comment.