从上传的Excel文件中读取日期

Question

I am uploading an Excel file through PHP Excel. The column C has dates in format mm/dd/yyy.

When I echo the date for example '10/3/2016' it reads it as 42646. The format set for the column is short Date.

How can I read it as the normal date instead of 42646.

here is the model

function fi_upload($file_name){
$file = './uploads/'.$file_name;
$this->load->library('excel');
$objPHPExcel = PHPExcel_IOFactory::load($file);
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
foreach ($cell_collection as $cell) {
    $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
    $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
    $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();

    if ($row == 1) {
        $header[$row][$column] = $data_value;
    } else {
        $arr_data[$row][$column] = $data_value;
    }
}
$data['header'] = $header;
$data['values'] = $arr_data;
$datecell = $objPHPExcel->getActiveSheet()->getCell('P2');
if(PHPExcel_Shared_Date::isDateTime($datecell)) {
     $InvDate = date($format="Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($datecell)); 
}
echo $InvDate;
exit();


foreach ($arr_data as $q){
echo $q['C']; // column of date, date is echoed as 42646
exit(); //here i exit to display the date
}

I have to later insert it in the database as well in the yyyy/mm/dd format (which I can do if it is read properly)

Thanks.

UPDATE:

So apparently it was an Excel file issue, not php. Converting the cell values through TEXT() was the solution as suggested by @Hallur.

UPDATE 2.0: I am trying to do it via PHPEXCEL method but I get the following two issues.

1. : Object of class PHPExcel_Cell could not be converted to int
2. Echoes date as 2036-02-07 where as the date in cell is 10/4/2016

Answer 1

42646 is an MS Excel serialized timestamp value, the number of days since 1st January 1900 (or 1st January 1904, depending on whether the spreadsheet is using the Windows or Mac calendar)

PHPExcel provides a variety of functions to convert values between MS Excel serialized timestamps and Unix timestamps or PHP DateTime objects (or vice versa), all of which can be found in the PHPExcel_Shared_Date class, e.g.

• ExcelToPHP() to convert an MS Excel serialized timestamp to a unix timestamp
• ExcelToPHPObject() to convert an MS Excel serialized timestamp to a PHP DateTime object

Alternatively, using the getFormattedValue() method instead of getValue() will return a formatted date/time string value instead of the serialized tiemstamp