PHPExcel library get date

Question

Using PHPExcel, I a trying to fetch data from Excel file and save it in MySql database using the following code:

$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$i=0;

$arr =array("","","","","","","");

for ($row = 1; $row <= $highestRow; $row++) 
{
 //  Read a row of data into an array
 $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
 foreach($rowData[0] as $k=>$v)
 {
    if(PHPExcel_Shared_Date::isDateTime($v))
    {
        $arr[$i] = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($InvDate));
    }
    else
        $arr[$i] = $v;
    $i++;
}

This code gives following error:

Catchable fatal error: Argument 1 passed to PHPExcel_Shared_Date::isDateTime() must be an instance of PHPExcel_Cell, double given, called in ............

When I read columns as follows:

foreach($rowData[0] as $k=>$v)
{
    $arr[$i] = $v;
    $i++;
}

The date columns are read as normal text even if I set Cell Formatting to date in excel. In all, i have 3 date columns in each record of my excel file which may either have a date or will be blank.

The date column of excel has dates in format: d-m-Y. I want it to be fetch in the format: 'Y-m-d' if possible otherwise i will have to explode it and convert it manually. The date will be finally stored into mysql Date type column.

I read several articles at stackoverflow too, but couldn't get the perfect solution.

Can you var_dump($rowData); after getting it? – user4035 Dec 22 '16 at 20:27 — user4035, Dec 22 '16 at 20:27

score 1 · Accepted Answer · answered Dec 23 '16 at 01:06

$v is just a numeric value, the serialized timestamp value used by MS Excel, no different to any other float value; it isn't possible to tell if $v represents a date or not without being able to access the format mask for the cell, which is why the isDateTime() method needs to read the cell itself to determine if it contains a date or not.

Ensure that your array is returned as an associative array (by setting the additional last argument for the method as true), which gives you the address of each cell:

$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE, TRUE);

This will return an array indexed by its row address, as a nested array by column address, so your foreach will need to reflect the row number:

foreach($rowData[$row] as $k=>$v)

and $k will then be the column address.

You can then use $row and $k to get the cell, and pass that to the isDateTime() call:

if(PHPExcel_Shared_Date::isDateTime($sheet->getCell($k . $row)))

And note that

$arr[$i] = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($InvDate));

should be

$arr[$i] = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($v));

in your code

PHPExcel library get date

1 Answers1