Reading dates from excel 2007

Topics: Developer Forum
Apr 15, 2011 at 12:19 AM

Task : read all cells from an uploaded excel 2007 file and write into csv format.

Issue : reading dates and writing them into mm/dd/yyyy format ( using PHP)

Now once it is determined that a cell being read has date , it can be converted from MS datatype ( days since 1 jan 1900) to MM/DD/YYYY using

PHPExcel_Shared_Date::ExcelToPHPObject($objWorksheet->getCellByColumnAndRow($col, $row)->getValue())->format('m/d/Y');

But really the problem is : How to determine if a cell being read has date value in it ? Have done a lot of research online and through the documentation but no luck.

I tried using echo $objWorksheet->getCellByColumnAndRow($col, $row)->getDataType(); but it returns 'n' for even datatype for date values.

Any help will be greatly appreciated.

Coordinator
Apr 15, 2011 at 7:40 AM

If you want the whole worksheet, then read using Excel2007, select required worksheet, and write using CSV... as long as you don't set readDataOnly(true), then that's the simplest 5 or 6 lines to do a conversion between formats.

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($inputFileName);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save($outputFileName);

And this will handle all date conversions for you, using the formats identified in the Excel worksheet.

To Excel, a date is just a number, hence the return from getDataType().

If you want to test a cell to identify if it's a date, perhaps to change the date formatting:

$isDate = PHPExcel_Shared_Date::isDateTime($objWorksheet->getCellByColumnAndRow($col, $row));
Apr 15, 2011 at 12:03 PM

Thanks Mark for your response.

isDateTime() worked .

Apr 15, 2011 at 1:28 PM

I am facing more problems now.

Here is the code snippet to read a cell and determine whether it is date or not

 if( PHPExcel_Shared_Date::isDateTime($objWorksheet->getCellByColumnAndRow($j, $i) )){
            $cell = PHPExcel_Shared_Date::ExcelToPHPObject($objWorksheet->getCellByColumnAndRow($j, $i)->getValue())->format('m/d/Y');
          }else{
            $cell =  $objWorksheet->getCellByColumnAndRow($j, $i)->getCalculatedValue();
          }

On uploading an excel 2007 file with just 10 rows, the conversion runs out of both php execution time and Allocated memory. I upped the php execution time to 200 ( set_time_limit) and Allocated memory to '512M' . But even then the php maximum execution time was reached.

Can't figure out what is going wrong. Full code below.

Apr 15, 2011 at 1:48 PM

<?php
require_once '../Classes/PHPExcel/IOFactory.php';
set_time_limit(80);
ini_set('memory_limit','512M');
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly('true');

$objPHPExcel = $objReader->load("test.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
  echo '<tr>' . "\n";

  for ($col = 0; $col <= $highestColumnIndex; ++$col) {
      if(PHPExcel_Shared_Date::isDateTime($objWorksheet->getCellByColumnAndRow($col, $row))){
          //echo "<td>" ."yes </td> \n";
          echo '<td>' . PHPExcel_Shared_Date::ExcelToPHPObject($objWorksheet->getCellByColumnAndRow($col, $row)->getValue())->format('m/d/Y') . '</td>' . "\n";
      }else{
          echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(). '</td>' . "\n";     
      }
  }

  echo '</tr>' . "\n";
}
echo '</table>' . "\n";
?>

Apr 15, 2011 at 1:50 PM

isDateTime doesn't work without setting

$objReader->setReadDataOnly('true');

And when setReadDataOnly is not set to true, the loading of small xlsx file takes forever and doesn't complete successfully.

Apr 15, 2011 at 2:40 PM

Now it seems to be related to 3 blank columns in the uploaded file which caused the trouble. Testing.