feature request: Show #N/A as an empty cell

[Balandino]

Feature Request

Hello,

Following on from this old StackOverflow question:

https://stackoverflow.com/questions/58818257/hiding-n-a-or-empty-cells-from-an-excel-chart-produced-by-xlsxwriter

I'd like to know it's feasible to implement the "Show #N/A as an empty cell" option? I've looked over the documentation and can't see that it has been implemented since the question, hence the feature request.

Thanks for the awesome library,

[jmcnamara]

It was never requested, outside of that SO question, so I never looked at adding it. I'll have a look now but the priority would be low so I can't promise it will be implemented.

Out of curiosity, what is your use case here. In what case do you generate '#N/A' in data you wish to graph?

[Balandino]

Hello,

I essentially have a line chart with 2 different coloured series overlaid on each other. The 'on-top' red series has #N/A where there's no data which, when ticked to show as a gap, allows the 'on the bottom' green series to show through. This creates a sort of series overlay where the 'on-top' series sometimes shows up, but when it doesn't allows the lower series to shine through. like so:

The red line is where series 2 has data, the green line is where series 2 has #N/A and series 1 has data

If this is too low a requested feature then I understand if it doesn't get implemented, I just thought I'd ask here on the off chance it's simpler to implement than I thought.

Thanks for the prompt reply.

[jmcnamara]

Thanks for the detail. A couple of other questions.

How are you generating the #N/A in the data.

Also, could you use blanks instead of #N/A for the same effect?

[Balandino]

1. The #N/A are generated via an IF formula. If there's data then a number is put into the cell, otherwise #N/A is used.
2. I've tried blanks and other combinations, but they all seem to default the line series to a 0 value, which forces the line to the bottom of the chart or connects the gaps via straight lines in between which looks rather off.

[jmcnamara]

I've tried blanks and other combinations, but they all seem to default the line series to a 0 value, which forces the line to the bottom of the chart or connects the gaps via straight lines in between which looks rather off.

What about the case of using blanks instead of #N/A and plotting them using the "gaps" option?

I started to create test cases in order to implement this feature but I'm not seeing the difference between "blanks + Show empty cells as Gaps" and "N/A + show N/A as blanks".

[Balandino]

Example.xlsx

I've attached an example file of how this seems to turn out for me. I suspect the issue rests on the fact that I'm using a formula as opposed to raw values and as such, can't truly set the cells to a blank value. So Excel interprets the value as 0 instead and drags the line right down.

[jmcnamara]

Thanks for the example. That clarifies the difference. It is a bit bizarre that Excel behaves like that in the default case.

I'll go ahead and implement the "N/A as blanks option" and let you know when it is available to test.

[jmcnamara]

I've pushed this feature to main so you can try it out. The method is:

        chart.show_na_as_empty_cell()

[Balandino]

Good morning John,

Worked perfectly, thank you. The chart looks correct and inspecting the series shows the option has been ticked.

I've popped a small sponsorship payment across to you by way of thanks,

[jmcnamara]

Thank you.

I have pushed this addition to PyPI in XlsxWriter v3.1.4.