Different behavior of “Equal” formula on numeric data between Spread and Excel

Different behavior of “Equal” formula on numeric data between Spread and Excel

Posted by: amd86 on 10 October 2023, 12:24 am EST

• 

  amd86

  • Post Options:
  • Link
    Copy

  Posted 10 October 2023, 12:24 am EST

  Hi!

  Probably the reason is my misunderstanding of something fundamental and the solution lies in one unsettled flag. But.

  In the empty and new solution, I wrote:

  fpSpread1_Sheet1.SetFormula(0, 0, "8,29 * 150 = 1243,5");

  And the value of A1 became false.
  
  But the same formula in Excel makes A1 true. Even if I export this newly created Spread to Excel.

  I humbly ask for assistance…

  Farpoint version - 12.45.20193.0

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 10 October 2023, 2:32 pm EST

  Hello Anton,

  Apologies for the inconvenience caused.
  
  We too can observe the issue at our end with your said and with the latest build so we have escalated this to the development team for the fix. We will let you know as soon as we get the update from the developers.
  
  [Internal Tracking ID: SPNET-34248]

  Note: The version that you are using is quite old and the fix will be available in the future release only so we recommend you to upgrade to the latest version.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 10 October 2023, 3:11 pm EST

  Hello Anton,

  As per the developers, the issue is caused by numeric precision and it’s from .NET but not by Spread. You can try the following code to see the result:

  bool isEqual = 8.29 * 150 == 1243.5;

  You can workaround the issue using the ROUND function.

  fpSpread1_Sheet1.SetFormula(0, 0, "ROUND(8.29 * 150, 11) = 1243.5");

  Regards,
  
  Prabhat Sharma.

• 

  amd86

  • Post Options:
  • Link
    Copy

  Posted 10 October 2023, 5:35 pm EST

  Hello Prabhat!
  
  Unfortunately, these workarounds will not help our real use case.

  What we need is to import external Excel files that contain a specific table structure in which cells can contain any formula relationships. And our users sent us one file in which the cell values actually change when imported into Spread. And this is kind of a critical issue for us.

  This is more detailed code of the problem:

  fpSpread1_Sheet1.ReferenceStyle = ReferenceStyle.R1C1;
  fpSpread1_Sheet1.SetValue(0, 0, 8.29);
  fpSpread1_Sheet1.SetValue(0, 1, 150);
  fpSpread1_Sheet1.SetFormula(0, 2, "ROUND(RC[-1]*RC[-2];0)/1000");
  fpSpread1.SaveExcel("test.xlsx", ExcelSaveFlags.UseOOXMLFormat);

  
  
  We cannot limit our users to using only certain formulas and expect Spread to have 100% exact Excel import/export compatibility. But is the developer’s answer final? And Spread on .Net Isn’t 100% compatible with Excel?
  
  This is weird. Because in my opinion, platform specific issues should be specifically fixed in this platform version of the product…

  And about workarounds. Is it possible to import an Excel file without formulas (using cached values in cells) but with styles, spans and etc.? Because the ExcelOpenFlags.DataAndFormulasOnly flag kills not only formulas, but also styles and spans.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 11 October 2023, 2:52 pm EST

  Hello,

  We are discussing your concerns with the developers and will get back to you soon on this.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 15 October 2023, 8:01 pm EST

  Hello,

  As per the developer, the issue in this bug is not about Excel Import/Export compatibility but calculating floating point numbers. All .NET applications follow IEEE 754 for binary floating-point arithmetic.
  
  https://learn.microsoft.com/en-us/dotnet/api/system.double?view=net-5.0

  However, Excel has exceptional cases which don’t follow the IEEE 754 specification (see “Cases in which we do not adhere to IEEE 754“
  
  https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

  The difference comes from the framework but not the application (Spread in this case).

  is it possible to import an Excel file without formulas (using cached values in cells) but with styles, spans, etc?
  
  Currently, we don’t support loading XLSX file and exclude formulas only. But you can open it with DataOnly and copy values to override the formula. Here is the sample code:

  fpSpread1.OpenExcel("D:\\tmp\\Book2.xlsx");    
  //Create another Spread to load values only
  var fpSpread2 = new FpSpread(fpSpread1.LegacyBehaviors);
  fpSpread2.OpenExcel("D:\\tmp\\Book2.xlsx", ExcelOpenFlags.DataOnly);
  //Copy values only to the first Spread
  var book1 = fpSpread1.AsWorkbook();
  var book2 = fpSpread2.AsWorkbook();
  for (int i = 0, count = book1.Worksheets.Count; i < count; i++)
  {
    book2.Worksheets[i].Cells.Copy(book1.Worksheets[i].Cells, ManipulationOptions.Values);
  }  
  fpSpread2.Dispose();

  For the root cause, the devs will continue investigating if there is a solution for it. However, they are not sure when can they fix the issue. As mentioned above, it comes from the different of frameworks but not our implementation.

  Regards,
  
  Prabhat Sharma.