Wrong DisplayValue and Value for the Cell

Aspose.Cells Product Family
1

Hi Aspose team,

Let’s have the attached input.xlsx file and read the Value and DisplayStringValueproperties of the cell V154 which value in excel is 0.00, with the following C# code:

var excelDoc = new Workbook(“TestFiles\input.xlsx”);
var value1 = excelDoc.Worksheets[0].Cells[“V154”].Value.ToString();
var displayValue1 = excelDoc.Worksheets[0].Cells[“V154”].DisplayStringValue;

We we’ll obtain value1=1.77635683940025E-15, which is not correct. The real value is 0. DisplayValue in this case is correct = 0.00


Note:
If we change, in the excel, the format of this cell (V154) to 30 decimals - see the second attached excel: input1.xlsx and run the same code for the second excel file:

var excelDoc = new Workbook(“TestFiles\input1.xlsx”);
var value2 = excelDoc.Worksheets[0].Cells[“V154”].Value.ToString();
var displayValue2 = excelDoc.Worksheets[0].Cells[“V154”].DisplayStringValue;

The both values are 0 now. Why in the first case we obtain 1.77635683940025E-15 instead of 0.

Internal ID: 48525

Thanks,
Vitalie Semenciuc
Senior Software Developer
IBM Romania

2

Hi Vitalie,


Thank you for sharing the samples.

We are able to notice the said problem while using the latest version of Aspose.Cells for .NET 8.8.2.8 therefore we have logged this incident as CELLSNET-44532 in our bug tracking system for further analysis. As soon as we have completed the preliminary analysis, we will share the results here for your kind reference.
3

Hi,


We have evaluated your issue further.
Well, from both input.xlsx and input1.xlsx (the xml data saved for XLSX file), you can see the value of H20 is -4.6000000000000014, please see the output of below simple code:
e.g
Sample code:

double v1 = -4.6000000000000014;
double v2 = 4.6;
Console.WriteLine(-v1 - v2);

which is the logic of V154’s formula and will give the result as 1.77635683940025E-15. We don’t know exactly how MS Excel does with the precision for such kind of decimal value. In fact we did provide option for handling such kind of issue: i.e., CalculationOptions.PrecisionStrategy. And,the strategies other than NONE can give the expected result 0 for this case. However, even though from MS Excel’s calculated result we can find it should have used different strategies for different situations, we don’t know the rule MS Excel uses to decide the strategy. By our test, the NONE strategy is the most popular one and suitable for most cases.

Thank you.
4

Hi,

This option (with CalculationOptions.PrecisionStrategy) was introduced from version 8.5 version of the Aspose.Cell, as I know. Beginning with this version we started to have the issue with precision described above.

Indeed, I tried this option and it works (it works with CalculationPrecisionStrategy.Decimal and CalculationPrecisionStrategy.Round and it doesn’t work with CalculationPrecisionStrategy.None).

My question is what parameter (Round or Decimal) did you use before this new option ?
We have to change CalculateFormula() method in a lot of places in our application and we want to be sure that this new parameter won’t affect the old implementation.

Thanks a lot,
Vitalie Semenciuc
Senior Software Developer
IBM Romania

5

Hi,


Good to know that the option figures out your issue.
For your question/query, I have logged your concerns against your issue “CELLSNET-44532” into our database. Our concerned developer from product team will soon evaluate it and provide details for your requirements.

Thank you.
6

Hi,

Thanks for using Aspose.Cells.

By our test, no one strategy can work fine for all cases. As we have
said, it seems ms excel uses different strategies for different
situations but we don’t know how ms excel decides which strategy should
be used for one formula. In previous versions, we use different strategy
for some special cases with special conditions. And we also found the
old logic cannot work fine for some users’ cases. So we move the
strategy out of the special formula and make it as a common option for
user to decide which one should be used. Maybe the old special logic can
work for your special case, but there must have other cases for which it
cannot work.


Also as we said, by our test the default strategy NONE is the most suitable one for most cases. To change the strategy for all your cases, you need to test it for your existing cases to make sure the new strategy can work for you.

7
Hello,

We're thinking of using CalculateFormula with the Round CalculationPrecisionStrategy in certain scenarios. If my understanding is correct this strategy is about rounding a number to a certain number of decimals (the precision) - is this strategy customizable - meaning the number of decimals?

Thank you,
Ruxandra
8

Hi,

Thanks for your posting and using Aspose.Cells.

We have logged your comment in our database for product team consideration and investigation. We will look into it and update you. Once, there is some news for you, we will let you know asap.

9

Hi Ruzandra,

ibmromania:
We’re thinking of using CalculateFormula with the Round CalculationPrecisionStrategy in certain scenarios. If my understanding is correct this strategy is about rounding a number to a certain number of decimals (the precision) - is this strategy customizable - meaning the number of decimals?


I am afraid, the current implementation of CalculationPrecisionStrategy does not provide the ability to round a number to specified precision when calculating formulas. Moreover, the said feature is not on the road map of Aspose.Cells APIs.

Thank you for your understanding.
10

Hi,


We have found since a solution for our problem using the precision strategy - so if you haven’t closed the ticket you can close it.

Thanks,
Ruxandra
11

Hi,


Thanks for sharing a good news with us. We have closed this thread now.