Can't read a date value

Sep 29, 2009 at 3:27 PM
Edited Sep 29, 2009 at 3:29 PM

Hi all,

I have a date field formated like 'd.m.Y 24.06.2001' but all I get is 37231 when I read it. Here is how I do it:

        include_once 'Vendor/PHPExcel.php';
                       
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objReader->setReadDataOnly(true);
        
        $objPHPExcel = $objReader->load("my.xls");
        $objPHPExcel->setActiveSheetIndex(0);
        
        $objWorksheet = $objPHPExcel->getActiveSheet();       
                
        echo '<table>' . "\n";
        foreach ($objWorksheet->getRowIterator() as $row_number =>  $row) {
          echo '<tr>' . "\n";
                
          $cellIterator = $row->getCellIterator();
          $cellIterator->setIterateOnlyExistingCells(false);


          foreach ($cellIterator as $cell_number => $cell) {          
               echo '<td>' . $cell->getValue() . '</td>' . "\n";
          }
          
          echo '</tr>' . "\n";
        }
        echo '</table>' . "\n";

thanks in advance

Developer
Sep 29, 2009 at 4:27 PM

The complication arises because Excel stores datetimes as number of days since 1900 (or sometimes 1904).

Furthermore there are also time zones to think about.

Here is some code you can use:

// put this at beginning of your script
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set('UTC'); // PHP's date function uses this value!


$date = $sheet->getCell('M4')->getValue(); // 37231 (Excel date)
$date = PHPExcel_Shared_Date::ExcelToPHP($date); // 1007596800 (Unix time)
$date = date('d.m.Y', $date); // 06.12.2001 (formatted date)


// put this at the end of your script
date_default_timezone_set($saveTimeZone);

Coordinator
Sep 29, 2009 at 4:28 PM

Excel holds dates (such as 24-Jun-2001) as a timestamp value (37231) based on the number of days since 1-Jan-1900, and that's the value that you're retrieving when you do $cell->getValue()

A number of functions are available that will allow you to convert this value so that you can display it formatted as a date:

e.g

if (PHPExcel_Shared_Date::isDateTime()) {
   echo PHPExcel_Style_NumberFormat::ToFormattedString( $cell->getValue(), PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 );
}
or
if (PHPExcel_Shared_Date::isDateTime()) {
   echo date('d-M-Y',PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));
}

Sep 30, 2009 at 7:38 AM

Thank you both.