Lazarus

Programming => Packages and Libraries => FPSpreadsheet => Topic started by: jollytall on April 08, 2022, 06:20:08 pm

Title: Handling of (relatively) large numbers
Post by: jollytall 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
10000000000Reading 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?

Title: Re: Handling of (relatively) large numbers
Post by: wp 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;  

Title: Re: Handling of (relatively) large numbers
Post by: jollytall 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.