PhpOffice\PhpSpreadsheet Cannot set default format for a column or whole spreadsheet

Question

I am stuck trying to set the default formatting of a particular column or whole spreadsheet. I'm using PhpOffice\PhpSpreadsheet package for creating an Excel spreadsheet. Is there any way to set format of whole sheet or whole column at once?

Below is my code snippet how I have used the library:

require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
// Set document properties
$spreadsheet->getProperties()->setCreator('Someone')
    ->setLastModifiedBy('Someone Else')
    ->setTitle('XLSX Test Document')
    ->setSubject('Office 2007 XLSX Test Document')
    ->setDescription('PhpOffice')
    ->setKeywords('PhpOffice')
    ->setCategory('PhpOffice');

$header = [
              "Order No",
              "ID",
              "Salutaion",
              "First Name",
              "Last Name",
              "Address"
          ];
$rows = [
            [
                "SDKJFBHIDUF",
                "23123156478978945",
                "Mr.",
                "John",
                "Doe",
                "101, Lorem Ipsum"
            ]
        ];

 $sheet = $spreadsheet->getActiveSheet();
 $sheet->setTitle('Orders');
 $sheet->fromArray($header, null, 'A1');
 $sheet->fromArray($rows, null, 'A2');
 $spreadsheet->setActiveSheetIndex(0);

 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
 header('Content-Disposition: attachment;filename="samplemultitab-invoice-' . time() . '.xlsx"');
 header('Cache-Control: max-age=0');
 // If you're serving to IE 9, then the following may be needed
 header('Cache-Control: max-age=1');
 // If you're serving to IE over SSL, then the following may be needed
 header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
 header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
 header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
 header('Pragma: public'); // HTTP/1.0
 $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
 $writer->save('php://output');
 exit;

For now I'm modifying /PhpSpreadsheet/Worksheet/Worksheet.php file fromArray method to as below

$this->getCell($currentColumn . $startRow)->setValue($cellValue);

to

$this->getCell($currentColumn . $startRow)->setValueExplicit($cellValue,\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

But I need a valid approach to fix this generic option setting.

Answer 1

I had a similar issue with large ID strings (1234567890123456789, for instance) being automatically converted to numbers and displayed as 1.23456789012346E+018 in my spreadsheet.

When using getStyle($columnIndex)->getNumberFormat()->setFormatCode() you have to use the values from PhpOffice\PhpSpreadsheet\Style\NumberFormat class and although there is a "FORMAT_TEXT" value, it seems to be too late because the cell values are considered as numbers (Cell\DataType::TYPE_NUMERIC) already and not as pure strings.

From my own experience I assumed that the only (and cumbersome) solution was to iterate over the cells of the given column to use the setValueExplicit() method.

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
// Fill the sheet with values using fromArray() method
$spreadsheet->getActiveSheet()->fromArray($dataToFillTheSheet, NULL, 'A1');
// Get the highest row index
$highestRow = $spreadsheet->getActiveSheet()->getHighestRow();
for($i = 1; $i <= $highestRow; $i++) {
  $cell = $spreadsheet->getActiveSheet()->getCellByColumnAndRow($columnIndex, $i);
  $cell->setValueExplicit($cell->getValue(), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
}

I'd be interested to know if anyone knows a better solution, though.