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:
While 16.11 generated this entry:
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.
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.
Hi,
I am using Aspose v - 22.12.0.0
Aspose.Cells.CellsHelper.SignificantDigits=15;
Workbook workbook = new Workbook(strFileFullName);
Worksheet worksheet = workbook.Worksheets[0];
var data123 = workbook.Worksheets[0].Cells["A1"].Value;
workbook.Worksheets[0].Cells["A1"].Value= 1234567890.123456777777777;
data123 = workbook.Worksheets[0].Cells["A1"].Value;
I get cell value as 1234567890.1234567
But when I copy 1234567890.123456777777777 to an Excel sheet the number is formatted to 1234567890.12346 and I am expecting the same from Aspose.
Can you please let me know if I am missing anything?
@santhoshsas,
When you input a numeric value more than 15 digits in ms excel, it will be truncated to 15 digits automatically. So, when you input 1234567890.123456777777777 for a cell in ms excel, you can get 1234567890.12345 only, not 1234567890.12346.
So, maybe you want to get 1234567890.12345 for Cell.PutValue(1234567890.123456777777777)?
However, we are afraid we cannot support this requirement and perform the truncate operation automatically. For most users, the double values come from vary sources and they want to keep them as they are. Even in the spreadsheet template files, most double values are saved with more than 15 digits and they should be used as their original values, not the truncated result.
So, if you need 15-digits value be assigned to a cell by Cell.PutValue(), please truncate the input value by yourself before calling this method firstly and then put the truncated value to the cell.
Thanks Johnson for the response.
yes, I want to get 1234567890.12345 for Cell.PutValue(1234567890.123456777777777).
Does it mean that Aspose.Cells.CellsHelper.SignificantDigits=15; is obsolete? I see a pervious update in the same thread that this issue was fixed with Aspose.Cells for .NET v16.12.2. We are using these values in formula calculation and this is impacting some critical formulas used in the project. I tried formatting
Style style = workbook.Worksheets[0].Cells[“A1”].GetStyle();
style.Number = 0;
workbook.Worksheets[0].Cells["A1"].SetStyle(style);
That is also not working. Can you please advise?
@santhoshsas,
Aspose.Cells.CellsHelper.SignificantDigits is used for exporting workbook to files where those double values are saved with text representation. And at the beginning it is designed for user’s special requirement of exporting numeric values with 17-digits explicitly. So it has no effect for setting value for cell.
Custom formatting for a cell just influences the displayed result of this cell, in formula calculation the used value is always cell’s actual value.
And for your code of applying style, setting number as 0 for the style will keep the cell be formatted as “General”. Without any other special settings it is just the default behavior of formatting a cell, so your code will not change the displayed result either. To format cell’s value to 15 digits, we think you may try style.Custom = “0.##############E+0”.
However, even with the specified formatting, the cell value will be formatted as 1.23456789012346E+9 for Cell.PutValue(1234567890.123456777777777), not your expected 1.23456789012345E+9. For your requirement, you have to truncate the double value to 15 digits by yourself.
Thank You for the detailed explainination.
@santhoshsas,
You are welcome.