We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Representation of double numbers in Aspose.Cells

I recently updated from Aspose.Cells 7.6 to 16.11 and have noticed that the double numbers represented in 16.11 seem to have additional precision in the way they are represented in the Excel OpenXML Document.


For example, version 7.6 generated this XML entry for cell B4:

101956.704694253

While 16.11 generated this entry:
101956.70469425319

As you can see, the 16.11 generated value has additional decimal precision. It looks to me like Aspose 16.11 stores 15 decimal digits for numeric values, whereas Aspose 7.6 only stored 13.

A simple test program that stores the value 101956.70469425319 in a cell will demonstrate this. For example:
double value = 101956.70469425319;
worksheet.Cells[0,0].Value = value;

The actual decimal number recorded in the Excel worksheet will be 101956.70469425319 if Aspose 16.11 is used and 101956.704694253 if Aspose 7.6 is used.

We use Aspose to insert numeric values into Excel workbooks that implement complex mathematical models. Any difference in the input values – even very small differences – could result in the model producing different results. We need the model to produce exactly the same output given the same input regardless of the version of Aspose.Cells for NET used. So the question is: Is there a way to ensure that Aspose.Cells for NET 16.11 will record the numeric values the same as 7.6.0.0 did?



Hi,


Thanks for your posting and using Aspose.Cells.

We were able to observe this issue and found the latest version stores 15 decimal digits unlike Microsoft Excel, which stores only 13 decimal digits.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44986 - Representation of double numbers in Aspose.Cells

Thank you for the fast response, Shakeel. I look forward to hearing how Aspose intends to deal with this.

Hi,


Thanks for using Aspose.Cells.

Please note, your issue is still Open and we will update you, once there is some news for your.

FYI:
I have looked into this issue further and found, MS-Excel truncates the number instead of rounding it. So Math.Round() cannot be used. Please consider this number

101956.70469425399

MS-Excel will truncate it to 101956.704694253 and will not round it like 101956.704694254, so Math.Round() method cannot be used.

Please see the following sample code and its console output for your reference.

C#
//Precision
int prec = 9;

//Truncation
double key = Math.Pow(10, prec); ;
double o = 101956.70469425399;
double d1 = Math.Truncate(o * key) / key;

//Round
double d2 = Math.Round(o, prec);

Console.WriteLine(d1);
Console.WriteLine(d2);

Console Output
101956.704694253
101956.704694254

Hi,


(Please ignore my previous post in which I shared the fix: v16.12.1 with sample code, I have deleted the post already as it does not include the fix for your issue).

Well, We have added CellsHelper.SignificantDigits attribute for your requirements. We will provide you the fix i.e., v16.12.2 in the few days after incorporating other enhancements and fixes.

Keep in touch.

Thank you.
Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v16.12.2 and let us know your feedback.

Note:
The above fix is compiled with .NET 2.0, if you need .NET 4.0 compiled fix, then please download: Aspose.Cells for .NET v16.12.2 (.NET 4.0).

Please use the following sample code with the given fix.

C#
CellsHelper.SignificantDigits = 15;
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[0, 0].Value = 101956.70469425319;

workbook.Save(@"C:\CELLSNET44986.xlsx");

Just tried the new version and it works great. I can now get exactly the same numeric values as in Aspose 7.6. Thanks for your fast response.

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

The issues you have found earlier (filed as CELLSNET-44986) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.