Laravel-excel: [BUG] Numbers longer than 15 digits treated wrongly in excel.

Created on 19 Nov 2018 · 3Comments · Source: Maatwebsite/Laravel-Excel

Prerequisites

[X] Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
[ ] Checked that your issue isn't already filed.
[X] Checked if no PR was submitted that fixes this problem.

Versions

PHP version: 7.1.14
Laravel version: 5.5.22
Package version: 3.1.1

Description

I am creating an excel from collection. Columns are dynamic in the export. I have issue with numbers that have more than 15 characters, which I know is an excel behaviour . I have tried to format the all the columns to text using AfterSheet, it does format the column, but the value is changed.

AfterSheet::class => function(AfterSheet $event) {
$rowColumn = $event->sheet->getDelegate()->getHighestRowAndColumn();
$cellRange = 'A2:'.$rowColumn['column'].$rowColumn['row'];
$event->sheet->getDelegate()->getStyle($cellRange)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
},

For eg: This is the value which I have in the collection 8456783123414575781, but after generating excel the value what I see is 8.45678E+18, which is 8456783123414570000.

How can I format the column before value is written to the excel. Tried BeforeSheet and BeforeExport as well, but nothing works as expected.

This issue is related to this https://github.com/Maatwebsite/Laravel-Excel/issues/1872.

Steps to Reproduce

export to an excel with collection value 8456783123414575781
After export you will see the value is changed.

Expected behavior:

I expect to have the column to be formatted as text and the value appears as it is.

Actual behavior:

Value is changed to an exponential form

8.71686E+17
8.71686E+17
8.71686E+17
8.45678E+18

Additional Information

Any additional information, configuration or data that might be necessary to reproduce the issue.

Source

hasiashiana

Most helpful comment

@FlyingBlazer Thanks a lot. This solved my issue.

hasiashiana on 19 Nov 2018

😄2

All 3 comments

See eecd0c6a3e7b65d87f01d962450f592f68e8379f

When \Maatwebsite\Excel\Writer is constructed, the DefaultValueBinder is set.

In DefaultValueBinder, numbers smaller than PHP_INT_MAX will be treated as Numeric and converted to float, so that it loses precision. This is the reason of your problem.

It seems that we need to implement \Maatwebsite\Excel\Concerns\WithCustomValueBinder to customize formats.