Regression with floats in 5.0.0

[IndrekHaav]

After #4575, when reading data from a spreadsheet, some numeric values are read incorrectly, with a floating point rounding error.

To reproduce:

<?php

require __DIR__ . '/vendor/autoload.php';

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setCellValue('A1', 123.4);
echo $worksheet->getCell('A1')->getFormattedValue();
?>

• Expected output: 123.4
• Actual output: 123.40000000000001

Looks like the issue arises in StringHelper::convertToString(), if the result of $positive - $floor cannot be accurately represented as a float:

$ php -a
Interactive shell

php > echo 123.4 - 123;
0.40000000000001

This incorrect representation of the decimal part is later appended to the original integer part, causing the error.

I tested this with PhpSpreadsheet 5.0.0 (PHP 8.2), the issue was not present in v4.4. The above code uses a dynamically created spreadsheet, so is not specific to any file format, though I originally reproduced the issue with an ODS file.

[oleibman]

When you deal with floats, you will always have rounding problems no matter what you do. PR #4479 and later PR #4575 addressed problems that were reported with earlier versions of the code. As with a test introduced in one of those:

self::assertSame(123.4, 123.40000000000001);

That check passes - the internal representation of the two numbers is exactly the same. This is in contrast to the situations addressed by those PR's, where an equivalent check would not pass. We cannot do any better than Php itself in how we handle floats. You might consider using an Excel function like ROUND, or a number format that constrains the number of decimal digits displayed, if this imprecision is a problem for you.

We will not be making any changes in response to this issue. My apologies if this is a disappointing answer.

[IndrekHaav]

I understand that internally 123.4 and 123.40000000000001 are the same. The problem is that when I use $cell->getFormattedValue() or $sheet->rangeToArray(), I don't get a float, I get a string containing an incorrect representation of the float. Essentially the same bug as #1324 - the expected behaviour is for the reader to return exactly what's in the file, no more and no less.

By the way, the test case from #1324 is broken also:

<?php

require __DIR__ . '/vendor/autoload.php';

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setCellValue('A1', '753.149999999999');
echo $worksheet->getCell('A1')->getFormattedValue();
?>

Outputs: 753.14999999999895

Note that I set the cell value as a string, but get back a different string. It seems that if the cell value looks like it might be a float, then it gets converted to one and processed as such before being converted back to a string, even if the value was never intended to be numeric. For example, "12.04" (representing the 12th of April) gets changed to "12.039999999999999".

Using spreadsheet functions or specific cell formats is unfortunately not an option, as the files are uploaded by users and I cannot control what's in them.

[oleibman]

You can certainly use setValueExplicit or StringValueBinder to ensure that new data entered in cells is treated as you wish.

rangeToArray (and its allied functions) has a parameter which controls whether the data is returned as a formatted string or in its "native" format (e.g. as a float).

The fix for 1324 is not broken, except in the sense that floating point arithmetic is broken. Php cannot distinguish between 753.149999999999 and 753.14999999999895, neither can we.

[IndrekHaav]

Like I mentioned, the issue is with files uploaded by users, so I cannot ensure anything about the data. The code examples I gave used an in-memory spreadsheet for the sake of brevity and to show that this isn't specific to any one file format; sorry if that caused confusion. But the essence of the problem is that the data read from the file doesn't match what the user sees when they open the file in a spreadsheet program, and this cannot be explained away with "that's just how floating point arithmetic works".

Disabling formatting when reading cells causes other issues, like cells formatted as dates being returned as Excel's internal numeric representations.

Anyway, I guess we're not going to come to an agreement about the severity of this regression. I will have to look into workarounds (maybe ini_set('precision') to a lower value), or just go back to v4.x of the library. Thank you for taking the time to respond.

[oleibman]

You will experience different float-related problems if you use an earlier version. The difference is that now the values will be internally consistent, which they were not before - that's the best we can do. All versions will provide unsatisfactory results for certain cases. That's the cost of using floating point.

[oleibman]

You can see if 4640 meets your needs.

[IndrekHaav]

Commit ebdbdd4 certainly seems to fix the issue. With the $lessFloatPrecision parameter set to true, it basically just reverts to previous behaviour?

You will experience different float-related problems if you use an earlier version.

Fair enough, although I don't think I've encountered any float-related issues in all the years of using this library (and PHPExcel before it). Or if there were, then bugs like #1324 look to the end user just like a rounding error, so perhaps no one noticed or bothered to complain. By comparison, the fictional decimal points described here are much more noticeable, at least for my use case, which is admittedly limited to mainly reading spreadsheet files and importing the data from them.

I appreciate you taking the time do provide a fix. Is it planned for v5.2?

[oleibman]

Yes, I will merge the change this week, and it will part of 5.2. It could happen earlier, but so far I'm not planning a release till just before or just after Php8.5 arrives in about 2 months.