In a recent project, I needed to read in an Excel worksheet and extract out a structure. Luckily, there is an Excel parsing PHP Library called PHPExcel. There are several good docs as well as good auto-generated documentation. However, I was looking to loop through a few rows in an Excel spreadsheet, with one of the columns being a date. It worked for me using the built in Iterators and the isDateTime method. Here is how I modified one of the examples:
error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />'); //date_default_timezone_set('Europe/London'); /** Include PHPExcel_IOFactory */ require_once '../Classes/PHPExcel/IOFactory.php'; $inputFileName = "library-after.xlsx"; if (!file_exists($inputFileName)) { exit("Can't find file." . EOL); } /** detect the type of file **/ PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); $inputFileType = PHPExcel_IOFactory::identify($inputFileName); /** load the data,**/ $objReader = PHPExcel_IOFactory::createReader($inputFileType); /** if we read only the data, then dates are funky **/ //$objReader->setReadDataOnly(true); $objReader->setLoadAllSheets(); $objPHPExcel = $objReader->load($inputFileName); $spreadsheet_data = array(); foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { //echo 'Worksheet - ' , $worksheet->getTitle() , EOL; foreach ($worksheet->getRowIterator() as $row) { echo ' Row number - ' , $row->getRowIndex() , EOL; $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set foreach ($cellIterator as $cell) { if (!is_null($cell)) { if(PHPExcel_Shared_Date::isDateTime($cell)) { echo ' Cell - ' , $cell->getColumn() , $cell->getRow() , ' - ' , date('r',PHPExcel_Shared_Date::ExcelToPHP($cell->getValue())) , '(' , $cell->getDataType(), ')', EOL; $spreadsheet_data[$cell->getColumn()][$cell->getRow()] = PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()); } else { echo ' Cell - ' , $cell->getColumn() , $cell->getRow() , ' - ' , $cell->getFormattedValue() , '(' , $cell->getDataType(), ')', EOL; $spreadsheet_data[$cell->getColumn()][$cell->getRow()] = $cell->getFormattedValue(); } } } } var_dump($spreadsheet_data); }