Calculated numeric cell value changes on saving

Aspose.Cells Product Family
1

I am seeing some very strange behavior. In the attached workbook, cell B1726 on the Input sheet has formula =B1725. In memory in my C# program, after CalculateFormula is called for the workbook, I can see the DoubleValue of both cells as 0.15, with no difference in precision or rounding. I even set a Watch condition in Visual Studio to compare them with == using the DoubleValue properties and it returned true.

However, upon saving the workbook to the disk, if I inspect the XML for the worksheet, the value for B1726 is 0.14999999999999999. This appears to be a bug. Please investigate.

InputCombinedAspose.zip (947.5 KB)

2

@bytefyre

It sounds like you’re encountering a precision issue with the calculated values in your workbook. This can happen due to the way floating-point numbers are represented in memory, which can lead to discrepancies when saving to disk.

Cause: The precision issue arises from the inherent limitations of floating-point representation, which can result in unexpected values when saving.

Solution: Here are some steps you can take to troubleshoot and resolve this issue:

  1. Check Calculation Mode: Ensure that the calculation mode is set to automatic. You can do this by setting workbook.CalculateFormula() before saving the workbook.

  2. Rounding Values: If precision is critical, consider rounding the values before saving. You can use Math.Round(value, precision) to round the values to a specific number of decimal places.

  3. Inspect the Formula: Double-check the formula in cell B1726 to ensure it is correctly referencing B1725. Sometimes, indirect references or other formulas can lead to unexpected results.

  4. Update Aspose.Cells: Make sure you are using the latest version of Aspose.Cells, as bugs are often fixed in newer releases.

  5. Save Options: When saving the workbook, you can specify options that might help preserve the precision. For example, using SaveFormat.Xlsx can sometimes yield better results.

Next Steps: Here’s a minimal code snippet to illustrate rounding before saving:

var workbook = new Workbook("InputCombinedAspose.xlsx");
workbook.CalculateFormula();

double valueB1725 = workbook.Worksheets[0].Cells["B1725"].DoubleValue;
workbook.Worksheets[0].Cells["B1726"].PutValue(Math.Round(valueB1725, 2));

workbook.Save("Output.xlsx");

This code calculates the formulas, rounds the value of B1725, and assigns it to B1726 before saving.

If you follow these steps, you should be able to resolve the precision issue. Please let me know if this helps or if you have any further questions!

3

@bytefyre
By using the following sample code for testing, we can reproduce the issue. The result file stores precision values that differ from the original data after calling Cell.PutValue method

At the same time, we also manually created the file and filled in the numbers, and we found that the file correctly saved the input values. Please refer to the attachment. result.zip (222.8 KB)

Workbook wb = new Workbook();
Cells cells = wb.Worksheets[0].Cells;
wb.CalculateFormula();
Cell a1 = cells["A1"];
a1.PutValue(0.15);
Style a1Style = a1.GetStyle();
a1Style.Custom = "0.00%";
a1.SetStyle(a1Style);
wb.Save(filePath + "out_net.xlsx");

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-58944

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

4

@bytefyre,

We evaluated your issue in details. We investigated MS Excel’s behavior about handling precision and found inconsistency too. For example, MS Excel does output “0.15” to the generated XLSX file for double value 0.15. However, for double value 1.0E-16, it outputs “9.9999999999999998E-17” to the generated XLSX file instead of “1.0E-16”.

We do not know what’s the rule MS Excel is using for converting double values to string. For us, there are two options: formatting double value by “G17”(what is used currently by our component) or rounding the value to 17-digits by ourselves. The first option will produce “9.9999999999999998E-17” for 1.0E-16 which is consistent with MS Excel. But for “0.15” it gives “0.14999999999999999” which is not expected. The result of the second option is exactly the opposite. It produces “1.0E-16” and “0.15”, so there is inconsistency with MS Excel too. For us we can only choose one fixed option between those two. Now that we cannot find one solution to make the results completely consistent with MS Excel. For performance consideration we determine to keep using current one(the first option).

5

Hi Amjad,

How much of a performance hit using the second method are we looking at here? Personally, I would prefer to at least have an option for Aspose to accurately write the numeric values held in memory to the disk with the same precision instead of using the G17 formatting which may or may not replicate Excel in all cases. If you can consider adding the second method and introduce a flag for Save to choose which one to use, I would greatly appreciate it.

Thank you!

6

@bytefyre,

Thanks for your feedback and details.

We will evaluate and provide details for the second option. Additionally, we will carefully review your suggestion to determine its feasibility. Once our assessment is complete, we will then get back to you with our feedback and details.

7

@bytefyre,

We would like to inform you that the ticket (“CELLSNET-58944”) has been updated. Its type has been changed from “Bug” to “New Feature,” and it has been reopened after previously being closed as “Won’t Fix.” Rest assured, we will thoroughly investigate the scenario and may provide you options or means to help you determine how to output significant digits for numeric values based on your requirements. We will keep you informed of any updates on it.

8

@bytefyre,

We are pleased to inform you that your issue (Ticket ID: “CELLSNET-58944”) has been resolved. The enhancement/fix will be included in our upcoming release (Aspose.Cells v25.10) that we plan to release in the first half of October 2025. You will be notified when the next version is released

Please note, in the upcoming version, we will provide one option for users to control the outputting of numeric values:

WorkbookSettings.SignificantDigitsType

The type can be Digits15, G17 or Rounding17.

9

The issues you have found earlier (filed as CELLSNET-58944) have been fixed in this update. This message was posted using Bugs notification tool by leoluo