XLSX Writer Python- 3 Color Scale with Number as Midpoint

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)numpercentpercentileformulamax        (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.

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_colMIN_MIN_FORMAT_VALUE = -500MAX_MAX_FORMAT_VALUE = 500def 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)