PHPEXCEL get formatted date as is visible in excel file [duplicate]

Question

I am trying to import a excel file using PhpExcel lib for all other fields the getValue() function works but when it encounters a field with format date as set in ms-excel2013 the date field in exel file is in format d-m-Y like 16-11-2014 but when I try to import it's value the getValue() returns 11-16-14 which when passed to strtotime further returns false in turn causing the date('Y-m-d',strtotime($date)) to return 1970-01-01.

I searched whole of web and stackoverflow but none solution fixed my problem. In excel file i see the date as 16-11-2014 and want it to be imported as is.

Here's the code

protected function importExcel($filePath) {
    $excelData = array();
    if ($filePath) {
        $objPHPExcel = PHPExcel_IOFactory::load($filePath);
        $objPHPExcel->setReadDataOnly(true);
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $worksheetTitle = $worksheet->getTitle();
            $highestRow = $worksheet->getHighestRow(); // e.g. 10
            $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $nrColumns = ord($highestColumn) - 64;
            $data = array();
            for ($row = 1; $row <= $highestRow; ++$row) {
                $values = array();
                for ($col = 0; $col < $highestColumnIndex; ++$col) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    if (PHPExcel_Shared_Date::isDateTime($cell))
                        throw new Exception("is date time"); // just a check
                    $val = $cell->getValue();
                    if (isset($val) && $val)
                        $data[$row][$col] = $val;
                }
            }
            $excelData[$worksheetTitle] = $data;
        }
        return $excelData;
    }
    return FALSE;
}

Answer 1

A getValue() call on a field containing a date should return a value like 41959.00 if that field really does contain an MS Excel date value.... that is, an MS Excel serialized datetime stamp based on the number of days since 1st January 1900 (or 1st January 1904 if the file was created using the Mac version of MS Excel)

To get a formatted date string, you need to call getFormattedValue() instead; and PHPExcel then uses the number format mask for that cell to format the date according to that mask.

To identify if a cell contains an MS serialized datetime stamp, you can use a call to PHPExcel_Shared_Date::isDateTime() first.

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    echo 'Worksheet - ' , $worksheet->getTitle() , EOL;

    foreach ($worksheet->getRowIterator() as $row) {
        echo '    Row number - ' , $row->getRowIndex() , EOL;

        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
        foreach ($cellIterator as $cell) {
            if (!is_null($cell)) {
                echo '        Cell - ' , $cell->getCoordinate() , ' - ';
                if (PHPExcel_Shared_Date::isDateTime($cell)) {
                    echo $cell->getFormattedValue() , EOL;
                } else {
                    echo $cell->getValue() , EOL;
                }
            }
        }
    }
}

---

Rather than returning a formatted data value, you can also ask PHPExcel to return the date as a Unix timestamp, or as a PHP DateTime object instead; and then you'll be able to format it however you want using PHP's built-in date functions or DateTime methods.

if (PHPExcel_Shared_Date::isDateTime($cell)) {
    $unixTimeStamp = PHPExcel_Shared_Date::ExcelToPHP($cell->getValue());
    echo date('d-M-Y H:i:s', $unixTimeStamp), PHP_EOL;
}

or

if (PHPExcel_Shared_Date::isDateTime($cell)) {
    $dateTimeObject = PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue());
    echo $dateTimeObject->format('d-M-Y H:i:s'), PHP_EOL;
}