Bug: `custom` in `data_labels` of `chart.add_series()` overwrites `num_format`

[realjanpaulus]

Current behavior

When custom labels are set, they overwrite the "num_format". When they are not set or None, the "num_format" will be applied.

Expected behavior

Applying of "num_format" even if "custom" is not None.

Sample code to reproduce

import xlsxwriter

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

data = [
    ["ranges", "counts", "counts_relative"],
    ["0.0-2.0", 0, 0],
    ["2.0-4.0", 1, 2.857142857],
    ["4.0-6.0", 2, 5.714285714],
    ["6.0-8.0", 17, 48.57142857],
    [">8.0", 15, 42.85714286],
]

custom_data_labels = [
    {"value": "=Precalculations!B2"},
    {"value": "=Precalculations!B3"},
    {"value": "=Precalculations!B4"},
    {"value": "=Precalculations!B5"},
    {"value": "=Precalculations!B6"},
]

for row_num, row_data in enumerate(data):
    worksheet.write_row(row_num, 0, row_data)

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

chart.add_series(
    {
        "categories": ["Precalculations", 1, 0, 5, 0],
        "values": ["Precalculations", 1, 2, 5, 2],
        "data_labels": {
            "value": True,
            "custom": custom_data_labels,
            "num_format": "0.000",
        },
    }
)

worksheet.insert_chart("E1", chart)
worksheet.autofit()
workbook.close()

Environment

- XlsxWriter version:
- Python version: 3.11.2
- Excel version: 16.76
- OS: MacOS Ventura 13.5

Any other information

No response

OpenOffice and LibreOffice users

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

[jmcnamara]

Thanks for the detailed report. Could you explain in a bit more detail what you expect to see in the output. Also, could you check if you can manually get the result you expect in Excel.

[realjanpaulus]

More context:

When I modify the code so that custom_data_labels = None, i.e.:

...
chart.add_series(
    {
        "categories": ["Precalculations", 1, 0, 5, 0],
        "values": ["Precalculations", 1, 2, 5, 2],
        "data_labels": {
            "value": True,
            "custom": None,
            "num_format": "0.000",
        },
    }
)
...

I get the following output in the excel file:

The data labels are all correctly formatted by having 3 decimal points.

But when I set custom data labels, the number formatting disappears:

...

custom_data_labels = [
    {"value": "=Precalculations!B2"},
    {"value": "=Precalculations!B3"},
    {"value": "=Precalculations!B4"},
    {"value": "=Precalculations!B5"},
    {"value": "=Precalculations!B6"},
]

...

chart.add_series(
    {
        "categories": ["Precalculations", 1, 0, 5, 0],
        "values": ["Precalculations", 1, 2, 5, 2],
        "data_labels": {
            "value": True,
            "custom": custom_data_labels,
            "num_format": "0.000",
        },
    }
)
...

This is more visible if I manipulate the data in column B to have more than 3 decimal points:

---

I tried to change this manually in excel and it could be that it is an excel bug. I did the following steps:

1. Trying to manipulate the labels manually by clicking on "Format Data Label..."
2. The following window opens; the format code is correctly shown (bottom right) but won't be applied

I am not too experienced in excel so it may be that I can't manipulate it on this way. But for the other case where custom_data_labels = None, a manipulating like this works (by clicking the "Add" button after editing Format Code)

[jmcnamara]

I think this is an Excel issue. The format seems to be ignored, and can't be set manually, in different versions of Excel that I tried.

As a workaround could you format the source data with a column format like this:

import xlsxwriter

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

data = [
    ["ranges", "counts", "counts_relative"],
    ["0.0-2.0", 0, 0],
    ["2.0-4.0", 1, 2.857142857],
    ["4.0-6.0", 2, 5.714285714],
    ["6.0-8.0", 17, 48.57142857],
    [">8.0", 15, 42.85714286],
]

custom_data_labels = [
    {"value": "=Precalculations!B2"},
    {"value": "=Precalculations!B3"},
    {"value": "=Precalculations!B4"},
    {"value": "=Precalculations!B5"},
    {"value": "=Precalculations!B6"},
]

for row_num, row_data in enumerate(data):
    worksheet.write_row(row_num, 0, row_data)

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

chart.add_series(
    {
        "categories": ["Precalculations", 1, 0, 5, 0],
        "values": ["Precalculations", 1, 2, 5, 2],
        "data_labels": {
            "value": True,
            "custom": custom_data_labels,
        },
    }
)

# New code.
num_format = workbook.add_format({"num_format": "0.000"}) 
worksheet.set_column(1, 1, None, num_format)

worksheet.insert_chart("E1", chart)
worksheet.autofit()
workbook.close()

Output:

[realjanpaulus]

@jmcnamara Thanks a lot, this actually helps a lot and I can indeed integrate a workaround like this in my application. Because it seems to be otherwise an Excel problem, I will close this issue.