how to get date from excel using PHPExcel library

Question

I am trying to get Date from excel using PHPExcel. But I am not getting date, I am getting string value which is not seconds from 1970 .

Code I have tried is

$InvDate=trim($excel->getActiveSheet()->getCell('B' . $i)->getValue());

Moderator

Answer 1

Try use

$cell = $excel->getActiveSheet()->getCell('B' . $i);
$InvDate= $cell->getValue();
if(PHPExcel_Shared_Date::isDateTime($cell)) {
     $InvDate = date($format, PHPExcel_Shared_Date::ExcelToPHP($InvDate)); 
}

P.S.

@DiegoDD: Should mention that $format is the desired format for the date. e.g.:

 $InvDate = date($format = "Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($InvDate)); 

P.P.S. 2019 Look at answer @gabriel-lupu, with new version of PhpOffice https://mcmap.net/q/479328/-how-to-get-date-from-excel-using-phpexcel-library

Stanleigh

Thanks its working fine searching from last few days – Metaphrase

Should mention that $format is the desired format for the date. e.g. $InvDate = date($format = "Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($InvDate)); – Nonappearance

Just a heads up for anybody referencing another cell; the first $InvDate needs to be $cell->getCalculatedValue(); in that case. – Breeches

Answer 2

For a date, getValue() should return a float, which is the Excel serialized timestamp value for that date/time... I suspect it's your trim() that's casting it to string. The actual value is the number of days since 1/1/1900 (or 1/1/1904 depending on the calendar that the spreadsheet is using).

Calling getFormattedValue() or getCalculatedValue() instead of getValue() should return the date formatted as a human-readable string according to the numberformatmask of the cell.

Alternatively, Sergey's solution tests if the cell has a date/time numberformatmask and calls the appropriate helper method to convert that Excel serialized timestamp to a unix timestamp, then uses the normal PHP date function to format it as human readable according to the value of $format. There's a similar helper method PHPExcel_Shared_Date::ExcelToPHPObject() that will convert an Excel serialized timestamp to a PHP DateTime object

Megohm

Answer 3

In the new version of the library, PhpOffice, the function that handles this is excelToDateTimeObject so the new code format should be:

$cell = $excel->getActiveSheet()->getCell('B' . $i);
$InvDate= $cell->getValue();
if (PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
     $InvDate = PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($InvDate); 
}

Chrisoula

Answer 4

Just to post a more up to date version as at 2023

$dateTime = Date::excelToDateTimeObject($spreadsheet->getActiveSheet()->getCell($cell)->getValue());

You can also use

$timestamp = Date::excelToTimestamp($value)

Oligopsony

Answer 5

You can get the cell values as string (also the date values) this way:

$sheet = $objPHPExcel->getActiveSheet();
$lastRow = $sheet->getHighestRow();
$lastColumn = $sheet->getHighestColumn();

$rows = $sheet->rangetoArray('A2:'.$lastColumn . $lastRow, NULL, True, True, False);
foreach ($rows as $row => $cols) {
    foreach($cols as $col => $cell) {
        echo trim($cell).'<br>'; // Gives the value as string
    }
}

Allred

Answer 6

$cell = $excel->getActiveSheet()->getCell('B' . $i);
$InvDate= $cell->getValue();
$InvDate= PHPExcel_Shared_Date::ExcelToPHPObject($InvDate)->format('Y-m-d H:i:s');

Try this

Bracteate