Request: option `invert_if_negative` should be able to define the "Inverted Fill Colour" similar to the "Format Data Series" menu in Excel

[funnel20]

Current behavior

Enabling option "invert if negative" in Excel (more info), shows an additional Colour picker to define the Inverted Fill Colour:

This makes it useful to specify 2 colours; for example green for positive and red for negative values:

s

XlsxWriter has the option invert_if_negative for chart.add_series(), but does not allow to specify the Inverted Fill Colour.

Hence the negative bars are always coloured white (default), which makes property invert_if_negative not very useful:

Expected behavior

I expect besides property fill, a new property to define the inverted colour when invert_if_negative is set to True, e.g. inverted_fill.

chart.add_series({
    'invert_if_negative': True,
    'fill' : {'color': "green"},
    'inverted_fill' : {'color': "red"}
})

Or instead of 2 parameters for the invert option, change data type of invert_if_negative from bool to dict; just specify a colour like fill:

chart.add_series({
    'fill' : {'color': "green"},
    'invert_if_negative' : {'color': "red"}
})

This allows users to specify both the positive and negative bar colours.

Sample code to reproduce

import xlsxwriter

SHEET_NAME_DAILY_RESULTS = "Daily Results"

# Data, including negative values:
day_results_df = pd.DataFrame({'Data': [10, 20, -30, 20, -15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
day_results_df.to_excel(writer, sheet_name=SHEET_NAME_DAILY_RESULTS)

# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets[SHEET_NAME_DAILY_RESULTS]

# Create a chart object (see: https://xlsxwriter.readthedocs.io/chart.html):
chart = workbook.add_chart({'type': 'column'})

# Get the dimensions of the dataframe:
(max_row, max_col) = day_results_df.shape

# Configure the series of the chart from the dataframe data.
chart.add_series({
    'categories': [SHEET_NAME_DAILY_RESULTS, 1, 1, max_row, 1],
    'values': [SHEET_NAME_DAILY_RESULTS, 1, max_col, max_row, max_col],
    'invert_if_negative': True, # MISSING option to define negative fill colour
    'fill' : {'color': "green"} 
})

# Insert the chart into the worksheet, 2 columns next to the last data column:
worksheet.insert_chart(1, max_col+2, chart)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Environment

- XlsxWriter version: 3.0.2
- Python version: 3.9.6
- Excel version: 16.57 (22011101)
- OS: macOS Monterey 12.1 (21C52)

Any other information

No response

OpenOffice and LibreOffice users

• [ ] I have tested the output file with Excel.

[jmcnamara]

I think it is fair to say that this is a feature request rather than a bug since that wasn't an intended feature.

I'll add it to the feature request backlog.

[funnel20]

The implementation of this feature in XlsxWriter is incomplete w.r.t. Excel, since it misses the ability to specify the inverted colour. Since I can't get the same functionality (i.e. red negative bars) programmatically as by the menu in Excel, it seems to me like a bug. It would love to see this inverted colour added.

[jmcnamara]

Again, it wasn't an intended feature, or documented behaviour, so its absence isn't a bug.

This feature doesn't even exist in Excel 2007 and the original xlsx specification. It was added in a later version of Excel and the specification. If you convert this to a feature request I will look into it, along with other items on the backlog.

[funnel20]

@jmcnamara I've changed the subject. If I can assist you with anything, please let me know. For example finding Excel docu about the constant name for this colour setting.

[jmcnamara]

Thanks. I'll look into it.

[jmcnamara]

I've added this feature to main. You can now use an additional "invert_if_negative_color" parameter:

import xlsxwriter

workbook = xlsxwriter.Workbook("chart.xlsx")
worksheet = workbook.add_worksheet()

chart = workbook.add_chart({"type": "column"})

worksheet.write_column("A1", [1, 2, -3, 4, -1])

chart.add_series(
    {
        "values": "=Sheet1!$A$1:$A$5",
        "fill": {"color": "red"},
        "invert_if_negative": True,
        "invert_if_negative_color": "yellow",
    }
)

worksheet.insert_chart("C1", chart)

workbook.close()

Output: