Phpspreadsheet cells are not being formatted as specified-CodePudding

I'm trying to set a format other than "General" on cells, but they remain as "General".

This is a simplification of my code:

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

// Data sample
$data = [
    ['Pete', 1, '[email protected]', 23, 600000000000],
    ['Carl', 2, '[email protected]', 29, 600000000000],
];
// Format for each column
$column_types = [
            'Name'                  => DataType::TYPE_STRING,
            'Id'                    => DataType::TYPE_NUMERIC,
            'Email'                 => DataType::TYPE_STRING, 
            'Age'                   => DataType::TYPE_NUMERIC,
            'Commerce ID'           => DataType::TYPE_NUMERIC,
];
$column_types = array_values($data);

$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0);
$sheet = $spreadsheet->getActiveSheet();

foreach ($data as $row => $row_data) {
    $i = 1;
    $row  ;
    foreach ($row_data as $value) {
        // VALUE IS SET, BUT NOT DATA TYPE!!
        $sheet->getCellByColumnAndRow($i, $row)->setValueExplicit($value, $column_types[$i - 1]);
        $i  ;
    }
}

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setPreCalculateFormulas(false);
$writer->save(PATH_TEMP . 'my_excel.xlsx');

Now when I open the resulting xlsx, all the cells will be "General", not Number, in case it was assigned DataType::TYPE_NUMERIC. And what it's worse, the last column, which is "600000000000", is converted to "6E 11", even though it's still a "General" cell.

Any ideas what should I do?

CodePudding user response：

You have to set the number format of the cell style like this (simplified example):

$sheet->getCellByColumnAndRow(1, 1)
    ->setValueExplicit(6000000000000, DataType::TYPE_NUMERIC)
    ->getStyle()
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);