How to retrieve date from table cell using PhpSpreadsheet?

php phpspreadsheet
Finesse picture Finesse · Jun 1, 2017 · Viewed 24.6k times · Source

I have xlsx tables and I use PhpSpreadsheet to parse them. Some cells format is date. The problem is that PhpSpreadsheet returns the values from date-formatted cells in an unspecified format:

// How it looks in excel: 2017.04.08 0:00
$value = $worksheet->getCell('A1')->getValue();  // 42833 - doesn't look like a UNIX time

How to get the date from a cell in form of a UNIX time or a DateTimeInterface instance?

Answer

Finesse picture Finesse · Jun 1, 2017

The value is amount of days passed since 1900. You can use the PhpSpreadsheet built-in functions to convert it to a unix timestamp:

$value = $worksheet->getCell('A1')->getValue();
$date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($value);

Or to a PHP DateTime object:

$value = $worksheet->getCell('A1')->getValue();
$date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);

Related questions

Setting width of spreadsheet cell using PHPExcel

I'm trying to set the width of a cell in an Excel document generated with PHPExcel with: $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('C')->setWidth('10'); $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('C')->setAutoSize(false); …

Read More
Merge Cell values with PHPExcel - PHP

I have a simple table like: - id - first_name - last_name - email - phone I'm using PHPExcel to export my data in XLS format $rowNumber = 1; while ($row = mysql_fetch_row($result)) { $col = 'A'; foreach($row as $…

Read More
Composer require phpoffice/phpspreadsheet doesn't work

I want to write a Script using the package Phpspreadsheet. I am not experience in php. I am trying to add the reference to my script through sudo composer require phpoffice/phpspreadsheet. But it doesn't work. I am getting the …

Read More