Recent

Author Topic: Handling of (relatively) large numbers  (Read 403 times)

jollytall

  • Full Member
  • ***
  • Posts: 214
Handling of (relatively) large numbers
« on: April 08, 2022, 06:20:08 pm »
Wp,

Please check the following code:
Code: Pascal  [Select][+][-]
  1. program project1;
  2. {$mode objfpc}{$H+}
  3. uses
  4.   sysutils,
  5.   fpsTypes, fpspreadsheet, fpsopendocument;
  6. var
  7.   WB : TsWorkbook;
  8.   WS : TsWorksheet;
  9. begin
  10. WB := TsWorkbook.Create;
  11. WB.Options := WB.Options + [boCalcBeforeSaving, boAutoCalc];
  12. WS := WB.AddWorksheet('test');
  13. WS.WriteFormula(0, 0, '=-A2+A3');
  14. WS.WriteNumber(1, 0, 5000000000);
  15. WS.WriteNumber(2, 0, 10000000000);
  16. writeln(WS.ReadFormula(0, 0));
  17. writeln(WS.ReadAsText(0, 0));
  18. writeln(WS.ReadAsText(1, 0));
  19. writeln(WS.ReadAsText(2, 0));
  20. WB.Free;
  21. readln;
  22. end.
  23.  
I get:
Code: [Select]
-A2+A3
9294967296
5000000000
10000000000
Reading as number makes no difference.
If I delete one 0 from both numbers it works.
If I change the formula from -A2+A3 to A3-A2 it works.

Any idea?

wp

  • Hero Member
  • *****
  • Posts: 10297
Re: Handling of (relatively) large numbers
« Reply #1 on: April 08, 2022, 07:16:29 pm »
Integer overflow... I don't know what Excel precisely understands as "integer". But since Excel is fairly old, I think that they do not mean Int64; therefore I decided to stick to standard 32-bit signed integer.

The other bug was a missing negative sign in the unary negation node.

To fix the issue open unit fpsexprparser and replace the "rtCell" case in procedure "TsUMinusExprNode.GetNodeValue" by the following code:
Code: Pascal  [Select][+][-]
  1.     rtCell:
  2.       begin
  3.         cell := ArgToCell(Result);
  4.         if cell = nil then
  5.           Result := FloatResult(0.0)
  6.         else if (cell^.ContentType = cctUTF8String) then begin
  7.           if TryStrToFloat(cell^.UTF8StringValue, val) then
  8.             Result := FloatResult(-val)
  9.           else
  10.             Result := ErrorResult(errWrongType);
  11.         end else
  12.         if (cell^.ContentType = cctNumber) or (cell^.ContentType = cctDateTime) then
  13.         begin    
  14.           if (frac(cell^.NumberValue) = 0.0) and
  15.              (cell^.NumberValue >= -Integer(MaxInt)-1) and
  16.              (cell^.NumberValue <= MaxInt)
  17.           then
  18.             Result := IntegerResult(-trunc(cell^.NumberValue))
  19.           else    
  20.             Result := FloatResult(-cell^.NumberValue);
  21.         end else
  22.         if (cell^.ContentType = cctBool) then
  23.           Result := ErrorResult(errWrongType);
  24.       end;
  25.  

The same overflow is possible also in the TsUPlusExprNode:
Code: Pascal  [Select][+][-]
  1.     rtCell:
  2.       begin
  3.         cell := ArgToCell(Result);
  4.         if cell = nil then
  5.           Result := FloatResult(0.0)
  6.         else
  7.         if (cell^.ContentType = cctUTF8String) then begin
  8.           if TryStrToFloat(cell^.UTF8StringValue, val) then
  9.             Result := FloatResult(val)
  10.           else
  11.             Result := ErrorResult(errWrongType);
  12.         end else
  13.         if cell^.ContentType = cctNumber then
  14.         begin
  15.           if (frac(cell^.NumberValue) = 0.0) and
  16.              (cell^.Numbervalue >= -Integer(MaxInt)-1) and
  17.              (cell^.NumberValue <= MaxInt)
  18.           then
  19.             Result := IntegerResult(trunc(cell^.NumberValue))
  20.           else
  21.             Result := FloatResult(cell^.NumberValue);
  22.         end;
  23.       end;  
« Last Edit: April 08, 2022, 07:48:28 pm by wp »

jollytall

  • Full Member
  • ***
  • Posts: 214
Re: Handling of (relatively) large numbers
« Reply #2 on: April 08, 2022, 07:37:43 pm »
Thanks,
Maybe for integers it was also that, but my actual numbers were not integer anyway, so it was more the second bug.
The good news, that these changes solved the issue.

 

TinyPortal © 2005-2018