how to manipulate date formats with phpexcel

Question

I am using phpexcel to read specific set of data from a excel sheet into an array.

// example $cell_range = 'A2:AH35';
$cell_collection = $objPHPExcel->getActiveSheet()->rangeToArray($cell_range, NULL, True, TRUE);

In this sheet, two columns (L & N) contain dates formates as dd-mmm-yy (01-Jan-16). In order to upload this into database I need to convert this to format yyyy-mm-dd (2016-01-01).

I cannot simply change the last parameter of rangeToArray to FALSE (formatting retention), because that will just give me a set of x numbers instead of the date.

So how do if reformat the dates? Can be either on the level of phpexcel reading the data or by manupulating the resulting $cell_collection array.

Answer 1

You need to do it manually.... you know which columns contain dates, so return the "raw" MS Excel timestamp and use PHPExcel_Shared_Date::ExcelToPHP() or PHPExcel_Shared_Date::ExcelToPHPObject() to convert to a unix timestamp or DateTime object, and then you can use standard PHP date() function or the DateTime object's format() method to convert to a human-readable date string in whatever format you want.

Alternatively, reset to MS Excel numberformat mask to whatever date/time format you ant for those columns/cells before calling toArray().