PHPExcel, Rows, and Dates

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

Leave a Reply

Your email address will not be published. Required fields are marked *