PHPExcel date issue on file iteration

Question

im using PHPExcel to import xlsx file to a database, the following code works great for passing $val as the cell value and avoid the insertion of colum headers:

        $dataArr = array();

        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);

            for ($row = 1; $row <= $highestRow; ++ $row) {
                for ($col = 0; $col < $highestColumnIndex; ++ $col) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $val = $cell->getValue();
                    $dataArr[$row][$col] = $val;
                }
            }
        }

        unset($dataArr[1]);

The problem is when I try to insert a date value, the current Excel cells date format is 10/03/17, but inserts 0000-00-00 in my database.

I tried the reply here but not luck.

Here is my current code:

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);

                for ($row = 1; $row <= $highestRow; ++ $row) {
                    for ($col = 0; $col < $highestColumnIndex; ++ $col) {
                        $cell = $worksheet->getCellByColumnAndRow($col, $row);
                        $val = $cell->getValue();
                        if (PHPExcel_Shared_Date::isDateTime($val)) {
                         echo $cell->getFormattedValue() , EOL;
                        } 
                       else {
                            echo $cell->getValue() , EOL;
                        }
                        $dataArr[$row][$col] = $val;
                    }
                }
            }

But this leads me to a Fatal Error "Uncaught TypeError: Argument 1 passed to PHPExcel_Shared_Date::isDateTime() must be an instance of PHPExcel_Cell, string given"

Any suggestions?

See also [`PHPExcel_Shared_Date::ExcelToPHP()`](https://github.com/PHPOffice/PHPExcel/blob/1.8/Classes/PHPExcel/Shared/Date.php#L105) instead of `$cell->getFormattedValue()`. Looks like it returns a UNIX timestamp value. — miken32, Apr 04 '17 at 21:58

score 2 · Accepted Answer · answered Apr 04 '17 at 21:52

If the date format mask for the cell is 10/03/17, then that is the format that a call to getFormattedValue() will return; which isn't a good format to insert in the database. You're better off explicitly using a different format mask, or even retrieving the value as a DateTime object (or a unix timestamp) than a formatted string.

However, more critical is

if (PHPExcel_Shared_Date::isDateTime($val)) {

where you're passing the MS Excel timestamp value (a simple float) to see if it's a date/time. PHPExcel can't identify whether a float is a date or not; it needs to be able to read the number format mask for the cell. You need to pass the cell itself to isDateTime(), not just the value.

if (PHPExcel_Shared_Date::isDateTime($cell)) {

as shown in the PHPExcel API Documentation; and exactly as the error message

"Uncaught TypeError: Argument 1 passed to PHPExcel_Shared_Date::isDateTime() must be an instance of PHPExcel_Cell, string given"

tells you:

PHPExcel date issue on file iteration

1 Answers1