Sahib Uz Zaman Sahib Uz Zaman - 1 year ago 91
PHP Question

Reading date from uploaded Excel file

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



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

Answer Source

The answer to this question is here:

Excel weird behaviour with dates => text

The number 42646, is the amount of days since january 1st year 1900.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download