Search code examples
phpxlsxphpspreadsheet

PhpSpreadsheet passing date format to chart

I use PhpSpreadsheet to create XLSX files. The problem is that I set the date inside the cells with a formatCode but when I create a chart based on these cells the formatCode is not applied on them. This leads to having timestamps inside the chart and a proper date format inside the cells. Does anyone know how to pass the right date format into the chart?

Best regards, laobiz

Solution

  • You may need to apply date formatting to the cell, e.g. :

    $worksheet->getStyleByColumnAndRow($column, $row)
   ->getNumberFormat()
   ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);

    See supported date formats here.

    Please note, that MS Excel does not work with unix timestamps like PHP (meaning the number of seconds since 01.01.1970). Instead, Excel counts the number of days since 01.01.1900 and works with float values when converting to hours/minutes/secs. Therefore you cannot insert into a cell unix timestamp, but convert it into Excel timestamp before. See PHP conversion functions from and into Excel serial date format.