Chart does not allow treating #N/A as empty cells when saving as pdf

Aspose.Cells Product Family
1

Hi

There seems to be an issue with the chart respecting #N/A in cells when saving to PDF. Have a look at attached and cell M219 in bands. In Excel if you right click a chart and select Select Data, in the bottom left you have an option “Hidden and Empty cells”. In there you can tell it how to interpret #N/A and I have check it meaning it should interpret it as an empty cell. It is really the only way to get a chart to consider a cell as being truely empty as “” is not empty so the only output you can have from an equation here is NA(). Conversion to PDF does not seem to respect that checkmark which means there is no way to get to these vertical bands
Test.zip (271.9 KB)

Kind Regards
Franz

2

@FranzThomsen,

Could you elaborate with some screenshots and more details on it. We are not sure what you really want to accomplish. How could you do this in MS Excel manually? I mean how to treat “#N/A” values as empty value on chart? If MS Excel can do that Aspose.Cells should do it.

3

Sure … here is the data, notice the two #N/As

image.png (364.3 KB)

If you just plot it, it looks like this

image.png (388.2 KB)

But if you right click the chart and press Select Data you get to this

image.png (41.7 KB)

And if you then pick Show #N/A as an empty value the chart will treat the value as if you had deleted the content of the cell, which is different from an empty string

image.png (14.5 KB)

And you get a chart that looks like this

image.png (390.3 KB)

So why is this important at all you may ask. Its important as the data comes from equations and some of these equations needs to return something that the chart interprets as an “empty” value. But that does not exist as something you can return from an equation … there is no such things as =if(A1<>"";A1;null) and =if(A1<>"";A1;"") is an empty string, not an empty value. The only way you can get to it is to do =if(A1<>"";A1;NA()) which gives #N/A in the cell, but to get the chart to accept this, you need to tick what I showed above

4

@FranzThomsen,
We have observed this issue and logged it in database for further investigation. You will be notified here once any update is available about this issue.

This issue is logged as:
CELLSNET-47555 - Chart does not allow treating #N/A as empty cells when saving as pdf

5

I can see it is not just saving it to pdf. If you open a spreadsheet that has this checked and save it again, it has lost the checkmark. So it seems to be missing inside Aspose Cells

6

@FranzThomsen,
Thank you for sharing this information. We have logged it with the ticket and will consider it while working on this issue.

7

@FranzThomsen,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47555”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

8

This is great news. Any progress on the other bug:

Kind Regards
Franz

9

@FranzThomsen,
Please try our latest version/fix: Aspose.Cells for .NET v20.8.4:
Aspose.Cells20.8.4 For .Net4.0.Zip (5.4 MB)
Aspose.Cells20.8.4 For .Net2_AuthenticodeSigned.Zip (5.4 MB)

Your issue should be fixed in it.

Let us know your feedback.

10

Fantastic, will do

11

The issues you have found earlier (filed as CELLSNET-47555) have been fixed in Aspose.Cells for .NET v20.9. This message was posted using Bugs notification tool by Amjad_Sahi
You may also get the new version @ nuget repos. (NuGet Gallery | Aspose.Cells 20.9.0)