Loaded excel file didn't round decimal value correctly.

[leonarta7]

This is:

- [ x ] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Decimal value of 2,5 in excel got rounded to 3 when using Number cell format.

What is the current behavior?

When excel data is being retrieved using toArray(), the decimal value of 2,5 got rounded to 2 instead of 3. The rounding only works when the digit before the comma is an odd number (eg. 1,5 will be rounded to 2 and 23,5 will be rounded to 24 while 2,5 will be rounded to 2 and 22,5 will be rounded to 22 instead).

What are the steps to reproduce?

All I did was simply load the excel file

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load($request->file('file'));

$sheetData = $spreadsheet->getActiveSheet()->toArray();

var_dump($sheetData);

And the cell which is formatted to Number (Format code is "0") with the decimal value came out wrong as described in the current behavior :

[13]=> string(1) "2"

Here's the excel if needed. The value I mention is on cell N7.
template_Bestorq (#21542) RING.xlsx

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.19.0 and using PHP 7.4

[leonarta7]

My temporary fix is to change the code in PhpSpreadsheet/Style/NumberFormat/NumberFormatted.php inside the formatStraightNumericValue function from :

$sprintf_pattern = "%0$minWidth." . strlen($right) . 'f';
$value = sprintf($sprintf_pattern, $value);

to :

$value = round($value, strlen($right));

I don't know how this will affect the other number formats aside from "0", so this is just temporary fix.

[oleibman]

PhpSpreadsheet is using sprintf to do the formatting. The problem is explained in:
https://stackoverflow.com/questions/27107238/why-does-phps-sprintf-not-round-5s-reliably
That doesn't mean PhpSpreadsheet can't change here; however, it will require some research, may be difficult, may cause regression problems, etc.