Chart values getting manipulated

Aspose.Cells Product Family
1

I’m trying to plot a chart where “#N/A” values are gaps in the series. I’m populating the chart directly with data (rather than referencing cells). This means I’m using a string with the values of interest:

series.XValues = “{” + string.Join(",", categories) + “}”;
series.Values = “{” + string.Join(",", values) + “}”;

Some of my values in the values objects are “#N/A”.

This works great.

But once Aspose processes my values, the “#N/A”'s are replaced with “-1.#QNAN”'s. If I do the same process using VBA, my “#N/A” values remain as-is, and are not converted to “-1.#QNAN”. Likewise, if I copy the appropriate string (so, my values are “#N/A”, not “-1.#QNAN”) to my clipboard, and manually paste it into the Excel chart, it works, and the values remain “#N/A” and they are not converted to “-1.#QNAN”, so it appears to be something Aspose is doing. I noticed this because we are having several charts incorrectly displaying data when saving the Excel document to PDF (the chart looks good in Excel, but once saving the document to PDF, the chart is corrupted) - which might be related to these “-1.#QNAN”, and it might not. Why would “#N/A” be replaced by another value?

As an example, series.Values should equal:
"={#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,7.6E-3,3.7100000000000001E-2,1.26E-2,9.7000000000000003E-3,9.7000000000000003E-3,1.2E-2,9.1000000000000004E-3,1.2699999999999999E-2,1.2500000000000001E-2,1.14E-2,1.4E-2,1.3299999999999999E-2,1.4999999999999999E-2,1.61E-2,1.3899999999999999E-2,1.3899999999999999E-2,1.43E-2,1.44E-2,1.23E-2,1.17E-2,1.2500000000000001E-2,1.21E-2,1.3599999999999999E-2,1.38E-2,1.3899999999999999E-2,1.4800000000000001E-2,1.6400000000000001E-2,1.7899999999999999E-2,1.83E-2,1.77E-2,1.7399999999999999E-2,2.06E-2,1.9800000000000002E-2,1.55E-2,1.24E-2,1.6400000000000001E-2}"
But, I’m seeing:
“={-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,-1.#QNAN,7.6E-3,3.7100000000000001E-2,1.26E-2,9.7000000000000003E-3,9.7000000000000003E-3,1.2E-2,9.1000000000000004E-3,1.2699999999999999E-2,1.2500000000000001E-2,1.14E-2,1.4E-2,1.3299999999999999E-2,1.4999999999999999E-2,1.61E-2,1.3899999999999999E-2,1.3899999999999999E-2,1.43E-2,1.44E-2,1.23E-2,1.17E-2,1.2500000000000001E-2,1.21E-2,1.3599999999999999E-2,1.38E-2,1.3899999999999999E-2,1.4800000000000001E-2,1.6400000000000001E-2,1.7899999999999999E-2,1.83E-2,1.77E-2,1.7399999999999999E-2,2.06E-2,1.9800000000000002E-2,1.55E-2,1.24E-2,1.6400000000000001E-2}”


2

Hi,


Thanks for providing us details.

Could you create a simple console application (runnable) with template file(s) to reproduce the issue on our end. You may zip the project and post us here as attachment, we will check it soon.

Thank you.
3

Hi,


Thanks for your posting and using Aspose.Cells.

I have tested your issue with the following sample code using the latest version: Aspose.Cells for .NET v16.10.9.0. I have attached the output excel file and output pdf file generated by it for your reference.

I don’t see any problem or any change in the output excel file but pdf file chart looks different than excel file chart. Please confirm if output excel file is good or faulty and if output pdf is good or faulty.

PS:
When I simply load and re-save your source excel file into pdf, the chart does not match with source excel file, so it means, pdf is wrong and there is no issue with output excel file.

C#
string categories = @"{"“9/09"”,"“10/09"”,"“11/09"”,"“12/09"”,"“1/10"”,"“2/10"”,"“3/10"”,"“4/10"”,"“5/10"”,"“6/10"”,"“7/10"”,"“8/10"”,"“9/10"”,"“10/10"”,"“11/10"”,"“12/10"”,"“1/11"”,"“2/11"”,"“3/11"”,"“4/11"”,"“5/11"”,"“6/11"”,"“7/11"”,"“8/11"”,"“9/11"”,"“10/11"”,"“11/11"”,"“12/11"”,"“1/12"”,"“2/12"”,"“3/12"”,"“4/12"”,"“5/12"”,"“6/12"”,"“7/12"”,"“8/12"”,"“9/12"”,"“10/12"”,"“11/12"”,"“12/12"”,"“1/13"”,"“2/13"”,"“3/13"”,"“4/13"”,"“5/13"”,"“6/13"”,"“7/13"”,"“8/13"”,"“9/13"”,"“10/13"”,"“11/13"”,"“12/13"”,"“1/14"”,"“2/14"”,"“3/14"”,"“4/14"”,"“5/14"”,"“6/14"”,"“7/14"”,"“8/14"”,"“9/14"”,"“10/14"”,"“11/14"”,"“12/14"”,"“1/15"”,"“2/15"”,"“3/15"”,"“4/15"”,"“5/15"”,"“6/15"”,"“7/15"”,"“8/15"”,"“9/15"”,"“10/15"”,"“11/15"”,"“12/15"”,"“1/16"”,"“2/16"”,"“3/16"”,"“4/16"”,"“5/16"”,"“6/16"”,"“7/16"”,"“8/16"”,"“9/16"”,"“10/16"”}";
string series0Values = @"{#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,1.1599999999999999,0.06,1.41,1.3,1.3,1.38,1.05,1.43,1.39,1.2,1.39,1.25,1.42,1.42,1.24,1.35,1.34,1.3,1.26,1.2,1.29,1.26,1.29,1.3,1.3,1.29,1.32,1.31,1.23,1.27,1.25,1.33,1.33,1.24,1.2,1.29}";
string series1Values = @"{#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,7.6E-3,3.7100000000000001E-2,1.26E-2,9.7000000000000003E-3,9.7000000000000003E-3,1.2E-2,9.1000000000000004E-3,1.2699999999999999E-2,1.2500000000000001E-2,1.14E-2,1.4E-2,1.3299999999999999E-2,1.4999999999999999E-2,1.61E-2,1.3899999999999999E-2,1.3899999999999999E-2,1.43E-2,1.44E-2,1.23E-2,1.17E-2,1.2500000000000001E-2,1.21E-2,1.3599999999999999E-2,1.38E-2,1.3899999999999999E-2,1.4800000000000001E-2,1.6400000000000001E-2,1.7899999999999999E-2,1.83E-2,1.77E-2,1.7399999999999999E-2,2.06E-2,1.9800000000000002E-2,1.55E-2,1.24E-2,1.6400000000000001E-2}"; 
Workbook workbook = new Workbook("Book1.xlsx");
Worksheet worksheet = workbook.Worksheets["Sheet1"];
Chart chart = worksheet.Charts[0];
Series series0 = chart.NSeries[0];
series0.XValues = categories;
series0.Values = series0Values;
Series series1 = chart.NSeries[1];
series1.XValues = categories;
series1.Values = series1Values;

chart.Calculate();

workbook.Save(@"Test.xlsx");
//workbook.Save(@"Test.pdf");
4

Were you able to recreate the issue where #N/A values were replaced with -1.#QNAN values?


So, the PDF output does not match the Excel chart display. Does that mean there is a bug in the save to PDF functionality that your team needs to address?
5

And, when looking at your Excel workbook “Test.xlsx”, I still see the -1.#QNAN values, so that means the #N/A values are still being replaced.


Also, the PDF output you are seeing (Test.pdf) is the original issue I was investigating, and that is when I noticed the -1.#QNAN values. Do you think the -1.#QNAN are causing the PDF version of the workbook to incorrectly display?
6

Hi,


Thanks for your posting and using Aspose.Cells.

And, when looking at your Excel workbook “Test.xlsx”, I still see the -1.#QNAN values, so that means the #N/A values are still being replaced.

I have attached the screenshots of Test.xlsx file and it looks good to me. I have attached two screenshots for a reference. I am unable to see your mentioned issue, so please provide some screenshots to highlight your issue.

Also, the PDF output you are seeing (Test.pdf) is the original issue I was investigating, and that is when I noticed the -1.#QNAN values. Do you think the -1.#QNAN are causing the PDF version of the workbook to incorrectly display?

Yes, this issue should also be fixed and I think, it is not related to your code but it is occurring because of some bug in Aspose.Cells. I was able to reproduce this issue just by opening the excel file and then saving it to pdf. It means, it is not related to #QNAN thing at all.

7

Ok, glad you were able to reproduce the Export-To-PDF issue I was seeing.


As for the -1.#QNAN values:

Attachment I1.png shows my oringinal “Book1.xlsx” workbook - the workbook included in my project which I sent to you. I’ve cleard out all data from the 2 series.

Attachment I2.png shows the output from the project, “Test.xlsx”. You can see that the series are showing -1.#QNAN instead of #N/A.

8

Hi,


Thanks for your posting and using Aspose.Cells.

We were not able to see this issue as per your screenshot. However, I noticed that Book1.xlsx in your screenshot is not the same which was provided by you earlier. Please provide the same Book1.xlsx as you have shown in the screenshot so that we could look into it further. Thanks for your cooperation in this regard.

Please also let us know which Microsoft Excel you are using like 2007/2010/2013/2016 etc.

9

We use Office 2010 as of now. The only thing different between the “Book1.xlsx” I originally attached, and the latest (attached) is I cleared out the series data (left just one data point, {1}, in each series).

10

Hi,


Thanks for providing us further details with sample.

After an initial test, I observed the issue as you mentioned by using your template file with your sample code. I found that the “#N/A” values are replaced with “-1.#QNAN” for chart data series. The issue can only be seen in MS Excel 2007/2010 as MS Excel 2013 does not show this issue. I have logged a ticket with an id “CELLSNET-44900” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.
11

Thank you!


Is there also a ticket for the export-to-pdf issue we are seeing as well?
12

Hi,


As we already observed this issue as well by using your template file with your sample code. I found an Issue with chart’s data series in Excel to PDF rendering. I have logged a separate ticket with an id “CELLSNET-44902” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.
13

Hi,


We have evaluated your issue “CELLSNET-44900”.
Well, it should be a bug of MS Excel 2007/2010. For confirmation, please create a chart with data “#N/A” in MS Excel, save the file, close the file, re-open the file in MS Excel2007/2010, you will get “-1.#QNAN” too. So, it is not an issue with Aspose.Cells and we won’t/cannot fix it.

Thank you.
14

Sounds good. Assuming the -1.#QNAN was not causing the export-to-pdf issue (CELLSNET-44902), then the replaced #N/A values does not seem to be adversely affecting functionality.

15

Hi,


Yes, this is a separate issue “CELLSNET-44902” in Aspose.Cells’ Excel to PDF rendering module for shape to image feature. Please spare us a little time so our concerned developer from product could evaluate the issue thoroughly before we get back to you with an update/fix on the issue.

Thank you.
16

Hello, Do you have an update on CELLSNET-44902? I’m wondering when we can expect a fix. Thanks!

17

Hi,


I am afraid, your issue “CELLSNET-44902” is not resolved yet. I have asked the concerned developer from product team to provide an update on it or provide a fix soon (if possible). I have also asked him to share an eta if it will take more time.

Once we have an update on it, we will share it with your immediately.

Thank you.
18

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v16.11.2

We have fixed your issue “CELLSNET-44902” now.

Let us know your feedback.

Thank you.
19

Great! Thanks. Do you know when you plan to release to Nuget (including this fix)?

20

Hi,


Thanks for your posting and using Aspose.Cells.

This fix will be present in official release version 16.12.0 which will be released around 20th December. That release will also be available via Nuget. So you will have to wait till then. Thanks for your cooperation and have a good day.