### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

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

#### jollytall

• Full Member
• Posts: 231
##### 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);
20. WB.Free;
22. end.
23.
I get:
Code: [Select]
`-A2+A39294967296500000000010000000000`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: 10632
##### 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: 231
##### 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.