[BUG] Some number formats are read as dates

[JulioPablo]

Versions

Laravel version: v8.0.0
Php version: 8.0
Laravel-Excel version: 3.1.29

Description

If you try to import a file, with read_only set to false some formatted numbers, in this particular case, dollar amounts set to be shown as red (when negative) and enclosed in parenthesis get read as a date.

These formatted negative numbers produce this output: [Sun, 19 Jan 1902 00:00:00 +0000190219]($#,##0.00)

If the file is imported with read_only set to true, then the numbers get read properly but the currency sign is not kept, which is necessary in my use case.

How to reproduce

Just import the following sheet using the ToModel concern and you should be able to see the behavior previously described
Test_Sheet.xlsx

Expected behaviour

The negative formatted numbers get properly read with currency sign when read_only is set to false in the config and no data is lost.

[patrickbrouwers]

If you require the currency sign to be kept, you'll have to use the string value binder I think. PhpSpreadsheet makes assumption about data based on number format and styles and tries to format accordingly.

See: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/config/excel.php#L204
You can also write your own value binder and handle on per column basis.

Alternatively you can use on each row and get the value yourself from the Cell. Pseudo code:

foreach ($row->getDelegate()->getCellIterator() as $cell)
$cell->getValue(null, false, false)

[JulioPablo]

Thanks, this didn't actually help me get the actual value, but it provided very helpful insight on what was going on, the issue is with PHPSpreadsheet and the format.

I opened a issue on their repo as well, PHPOffice/PhpSpreadsheet#1972

But yeah, not really a Laravel Excel issue. Again, thanks for the prompt and insightful reply.