Export Gradient coloured dataframe to excel

Question

I want to visualise some data, so I need to colour my dataframe in gradient colour depending on the value and I want to export that in Excel.

What I have now, is that it exports the grey colour part, but does not export the gradient yellow part. Could you help edit my code, or maybe you have another way of doing that task?

My current code:

def highlight_negative(val):
if val > 1:
    if val <500:
        color = 'dimgrey'
    elif val >=500 and val < 1000: 
        color = 'lightgray'
    else:
        color = 'None'
else:
    color = None
    
return f'background-color: {color}'


def gradient_color(val):
    # Gradient colour yellow (low value) to white (high value)
    if val >=0 and val <=1:
        min_val = 0
        max_val = 1
        range_val = max_val - min_val
        if range_val == 0:
        # if all the values are equal - return white colour
            return 'background-color: rgb(255,255,255)'
        else:
        # Setting RGB component to gradient from yellow to white 
            r = 255
            g = 255
            b = int(255 * (max_val - val) / range_val)
    else:
        r = None
        g = None
        b = None
    return f'background-color: rgb({r},{g},{b})'

# Apply gradient and highlight design to DataFrame
styled_df = df.style.applymap(gradient_color, subset=df.columns[2:]).applymap(highlight_negative, subset=df.columns[2:]).format(precision=2)

styled_df.to_excel('styled_df.xlsx', engine='openpyxl')

Answer 1

Using Conditional Formatting does not restrict the automation.
Rather than colouring the cells in Pandas and writing to Excel you are just colouring the cells in Excel instead, using Openpyxl to apply Conditional Formatting.
It shouldn't be any different unless you have a need to access the DataFrame with styling included for some reason.
Conditional Formatting does have the advantage that if you need to change any values manually in Excel for whatever reason, the fill colour will update automatically.

Anyway you can make your choice what works better for your scenario.

As @Vikas notes it would be better if you provided your dataframe and expected result so I'm guessing what your data might look like based on your code.
It seems the DF has numeric values and you want to background colour based on;

1. Values from 0 to 1, Gradient colour from White to Yellow
2. Values from 1 to 500 solid colour 'dimgrey'
3. Values from 500 to 1000 solid colour 'lightgrey'
4. Values over 1000 no background colour

In this example I have a DataFrame with various values to cover the 4 ranges above. After writing the DF to excel we use Openpyxl to apply the Conditional Formatting.

import pandas as pd
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule
from openpyxl.styles import PatternFill
from openpyxl.styles.differential import DifferentialStyle

df = pd.DataFrame({
    'ID': range(1, 5),
    'col1': [0.1, 0.99, 501, 0.01],
    'col2': [0.4, 1001, 0.6, 489],
    'col3': [10, 0.15, -0.67, 0.89],
    'col4': [0.35, 127, 0.40, 867]
})

excel_file = 'styled_df.xlsx'
sheet_name = 'Sheet1'

### Define Style colours
white_fill = PatternFill(start_color='FFFFFF', end_color='FFFFFF', fill_type='solid')
dim_grey_fill = PatternFill(start_color='696969', end_color='696969', fill_type='solid')
light_gray_fill = PatternFill(start_color='D3D3D3', end_color='D3D3D3', fill_type='solid')
### Conditional Format Range
cf_range = "B2:E5"

### Write the DataFrame to Excel
with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name=sheet_name, header=True, index=False)

    ws = writer.book[sheet_name]

    ### CF Rule1; Values greater than 1000 fill background with white (no colour)
    rule_def = CellIsRule(operator="greaterThan",
                       formula=[1000],
                       stopIfTrue=False)
    rule.dxf = DifferentialStyle(fill=white_fill)
    ws.conditional_formatting.add(cf_range, rule_def)

    ### CF Rule1; Values between > 1 and 500 fill background with dim_grey
    rule1 = CellIsRule(operator="between",
                       formula=[1.01, 500],
                       stopIfTrue=False)
    rule1.dxf = DifferentialStyle(fill=dim_grey_fill)
    ws.conditional_formatting.add(cf_range, rule1)

    ### CF Rule2; Values > 500 and 1000 fill background with light_gray
    rule2 = CellIsRule(operator='between',
                       formula=[500.01, 1000],
                       stopIfTrue=False)
    rule2.dxf = DifferentialStyle(fill=light_gray_fill)
    ws.conditional_formatting.add(cf_range, rule2)

    ### CF Rule3; Values 0 and 1 fill background with gradient from White (0) to Yellow (1)
    rule3 = ColorScaleRule(start_type='num', start_value=0.1, start_color='FFFFFFFF',
                           end_type='num', end_value=1, end_color='FFFFFF00')
    ws.conditional_formatting.add(cf_range, rule3)

The data in Excel will look like this
CF is applied to cells in the range B2:E5. As noted if you change a value the bg colour will update to suit.