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.