XLSX Writer Python- 3 Color Scale with Number as Midpoint

Question

I'm trying to conditional formatting in XLSX writer with a 3 color scale with a 0 midpoint value in the middle. I want all negative values to scale from red (lowest number) to yellow (when the value is zero) and all positive numbers to scale from yellow (at zero) to green (at the highest).

The scaling gets all messed up when I try the following..

Something that would look like the following in Excel:

I can figure out how to do a 3 color scale in XLSX writer, but there doesnt seem to be an option (I can see) for midpoint being a number:

worksheet.conditional_format('G2:G83', {'type': '3_color_scale',
                                     'min_color': "red",
                                     'mid_color': "yellow",
                                     'max_color': "green"})

I then tried to break it down with a criteria with one format applied to values above zero and one below zero

worksheet.conditional_format('G2:G83', {'type': '2_color_scale',
                                    'criteria': '<',
                                    'value': 0,
                                    'min_color': "red",
                                    'max_color': "yellow"})


worksheet.conditional_format('G2:G83', {'type': '2_color_scale',
                                    'criteria': '>',
                                    'value': 0,
                                     'min_color': "yellow",
                                     'max_color': "green"})

But that doesn't seem to work either - if anybody has any ideas.. please let me know.. would really appreciate it.

Complete sample code:

import xlsxwriter

workbook = xlsxwriter.Workbook('conditional_format.xlsx')
worksheet1 = workbook.add_worksheet()


# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
                           'font_color': '#9C0006'})

# Add a format. Green fill with dark green text.
format2 = workbook.add_format({'bg_color': '#C6EFCE',
                           'font_color': '#006100'})

# Some sample data to run the conditional formatting against.
data = [
[34, 72, -38, 30, 75, 48, 75, 66, 84, 86],
[-6, -24, 1, -84, 54, 62, 60, 3, 26, 59],
[-28, 0, 0, 13, -85, 93, 93, 22, 5, 14],
[27, -71, -40, 17, 18, 79, 90, 93, 29, 47],
[0, 25, -33, -23, 0, 1, 59, 79, 47, 36],
[-24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, -57, -88, 0, 10, 26, 37, 7, 41, 48],
[-52, 78, 1, -96, 26, -45, 47, 33, 96, 36],
[60, -54, -81, 66, 81, 90, 80, 93, 12, 55],
[-70, 5, 46, 14, 71, -19, 66, 36, 41, 21],

]

for row, row_data in enumerate(data):
    worksheet1.write_row(row + 2, 1, row_data)


worksheet1.conditional_format('B2:B12', {'type': '2_color_scale',
                                    'criteria': '<',
                                    'value': 0,
                                    'min_color': "red",
                                    'max_color': "yellow"})


worksheet1.conditional_format('C2:C12', {'type': '2_color_scale',
                                    'criteria': '>',
                                    'value': 0,
                                     'min_color': "yellow",
                                     'max_color': "green"})

worksheet1.conditional_format('C2:C12', {'type': '2_color_scale',
                                    'criteria': '<',
                                    'value': 0,
                                     'min_color': "red",
                                     'max_color': "yellow"})


worksheet1.conditional_format('D2:D12', {'type': '3_color_scale',
                                     'min_color': "red",
                                     'mid_color': "yellow",
                                     'max_color': "green"})







workbook.close()    
writer.save()

This is what I get:

As you can see, column B (the first column) has no green

Column C has no red

Column D has 0 as green

Any ideas how to do the 3 step scaling with zero in the middle?

Thanks

Laskowski

Answer 1

I can figure out how to do a 3 color scale in XLSX writer, but there doesnt seem to be an option (I can see) for midpoint being a number:

You can use the min_type, mid_type and max_type parameters to set the following types:

min        (for min_type only)
num
percent
percentile
formula
max        (for max_type only)

See Conditional Format Options

So in your case it should be something like.

worksheet1.conditional_format('D2:D12', {'type': '3_color_scale',
                                         'min_color': "red",
                                         'mid_color': "yellow",
                                         'max_color': "green",
                                         'mid_type': "num"})

However, I'm not sure if that will fix your overall problem. Maybe add that to your example and if it doesn't work then open a second question.

One thing that you will have to figure out is how to do what you want in Excel first. After that it is generally easier to figure out what is required in XlsxWriter.

Hesitancy

Well done. I found the possible types (min, num, percent, etc.) to be very hard to find in the documentation. Your response was very helpful! – Cryo

Thanks. That would be a compliment if I hadn't also written the documentation. :-| – Hesitancy

Answer 2

I know this is an old question but I just ran into this problem and figured out how to solve it.

Below is a copy of a utility function I wrote for my work. The main thing is that the min, mid and max types ALL need to be 'num' and they need to specify values for these points.

If you only set the mid type to 'num' and value to 0 then the 3 color scale will still use min and max for the end points. This means that if the contents of the column are all on one side of the pivot point the coloring will in effect disregard the pivot.

from xlsxwriter.utility import xl_col_to_name as index_to_col

MIN_MIN_FORMAT_VALUE = -500
MAX_MAX_FORMAT_VALUE = 500

def conditional_color_column(
        worksheet, df, column_name, min_format_value=None, pivot_value=0, max_format_value=None):
    """
    Do a 3 color conditional format on the column.

    The default behavior for the min and max values is to take the min and max values of each column, unless said value
    is greater than or less than the pivot value respectively at which point the values MIN_MIN_FORMAT_VALUE and
    MAX_MAX_FORMAT_VALUE are used. Also, if the min and max vales are less than or greater than respectively of
    MIN_MIN_FORMAT_VALUE and MAX_MAX_FORMAT_VALUE then the latter will be used

    :param worksheet: The worksheet on which to do the conditional formatting
    :param df: The DataFrame that was used to create the worksheet
    :param column_name: The column to format
    :param min_format_value: The value below which all cells will have the same red color
    :param pivot_value: The pivot point, values less than this number will gradient to red, values greater will gradient to green
    :param max_format_value: The value above which all cells will have the same green color
    :return: Nothing
    """
    column = df[column_name]
    min_value = min(column)
    max_value = max(column)

    last_column = len(df.index)+1
    column_index = df.columns.get_loc(column_name)
    excel_column = index_to_col(column_index)
    column_to_format = f'{excel_column}2:{excel_column}{last_column}'

    if min_format_value is None:
        min_format_value = max(min_value, MIN_MIN_FORMAT_VALUE)\
            if min_value < pivot_value else MIN_MIN_FORMAT_VALUE

    if max_format_value is None:
        max_format_value = min(max_value, MAX_MAX_FORMAT_VALUE)\
            if max_value > pivot_value else MAX_MAX_FORMAT_VALUE

    color_format = {
        'type': '3_color_scale',
        'min_type': 'num',
        'min_value': min_format_value,
        'mid_type': 'num',
        'mid_value': pivot_value,
        'max_type': 'num',
        'max_value': max_format_value
    }
    worksheet.conditional_format(column_to_format, color_format)

Hemorrhoidectomy

BIG THANKS! This is not obvious. If you pass in a number, the pythonic thing would be for it to see the type of data from what's passed. But excel is not pythonic, so I appreciate your helping fixing my same problem. – Grille