setCellValue big number with PHP7

[philwu]

A big number such as "650108197510161947" being set to a cell in php5.6.24 seems fine. However, in php7.1.2, it turns to "6.50108E+17" and the value of the cell become to "650108197510160000". Is there anything I can do before set the value to make it back to normal?

[PowerKiKi]

Please provide a Minimal, Complete, and Verifiable example of code that exhibits this issue, without the need for any external Excel files. And don't forget to mention what version of this project you are using. That way there will be more chance for the issue to be solved.

[oveach]

@PowerKiKi Here you are:

$data = array('foo' => '150000197301');
$phpExcel = new PHPExcel();
$sheet = $phpExcel->getSheet(0);
$sheet->fromArray($data, null, 'A1');
$sheet->getStyle('A1:A1')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$writer = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007');
$writer->save('test.xlsx');

With PHP 5.6 we had a cell with "150000197301". With PHP 7.1 we have a cell with "1,5E+11". You can notice I force the format to "text"...

I presume this is an issue related to the "Numerical string conversions now respect scientific notation" BC break introduced in 7.1: https://secure.php.net/manual/en/migration71.incompatible.php#migration71.incompatible.numerical-strings-scientific-notation

Note : PHPExcel 1.8.1, PHP 7.1.2 on Windows and tested in Excel 2010 in french.

[Vadim-Podlevsky]

To fix this big number issue this code worked for me:

$spreadsheet->setActiveSheetIndex(0)->
setCellValueExplicit('A'.$i, $big_number_value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);