PHPExcel changes xls DATETIME to numbers

Question

When i upload a .xls file to PHPExcel i´d like to read the raw data.
I read about using "getValue()" and it should fix it.
But not in my case.

So in .xls i have a column: 2015-10-08 11:31
When i process it with PHPExcel i got: 42285.47993055556

Because of PHPExcel reading the file first, i cannot use PHP to make a DATETIME object?!

PHP

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

How do i solve this?

Threw the accepted answear i got this code to work for me:

for ($row = 2; $row <= $highestRow; ++ $row) {
  $val=array();
  for ($col = 0; $col < $highestColumnIndex; ++ $col) {
    $cell = $worksheet->getCellByColumnAndRow($col, $row);
    $val[] = $cell->getValue();
  }//close 2nd for loop
  //Create and convert xls date to php date
  $val[10] = PHPExcel_Shared_Date::ExcelToPHPObject($val[10]);
  $val[10] = $val[10]->format('Y-m-d H:i:s');
  //query stuff...
}//close first for loop

Mark Baker Mark Baker · Accepted Answer · 2015-10-08T14:34:56

No PHPExcel doesn't change it.... it already is a number

MS Excel stores dates/times as a serialized timestamp based on the number of days since 1st January 1900 (or 1st January 1904 if using the Mac calendar).... that value of 42285.47993055556 is the Excel serialized timestamp that corresponds to 2015-10-08 11:31:06. The cell where this value is stored then has a number format code that converts it to a human representation of that date/time. If you change that mask, it changes the visible appearance of the value, but the underlying serialized value remains the same.

If you do

$val[] = $cell->getFormattedValue();

then PHPExcel will apply the number format mask to that serialized date/timestamp, and return a string containing the human readable format of that date, based on the mask that was stored in the Excel file.

If you do

$val[] = PHPExcel_Shared_Date::ExcelToPHP($cell->getValue());

then PHPExcel will return a unix timestamp that you can pass to the PHP date() function to format however you want

If you do

$val[] = PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue());

then PHPExcel will return a PHP DateTime object that you can then format however you want using the DateTime object format() method

PHPExcel changes xls DATETIME to numbers

1 Answers