PHP Excel value binder doesn't recognize date

Question

code sample

require_once 'excel/PHPExcel.php';
require_once 'excel/PHPExcel/Cell/AdvancedValueBinder.php';

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
$objPHPExcel->setActiveSheetIndex(0)
                                ->setCellValue('A1', '12/08/2010')
                                ->setCellValue('A2', '14/12/2013');

Why is that that value binder doesn't recognize that it's a date? Therefore Excel handles the data as string so no sorting :(

What exactly happens? Which cell isn't recognized - the second one, or both? — Pekka, Mar 16 '12 at 09:26
It loads them like strings. When you go into format Cells you can see that the data is general and not date. (using Excel 2010...) — Peter, Mar 16 '12 at 09:30
And I'm using PHPExcel 1.7.3 cause the new one 1.7.6 is even worse. It writes the date into the cell like 2010-08-12 for instance. I've tried excel time gmmktime etc... Nothing works. — Peter, Mar 16 '12 at 09:34
PHPExcel seems to have a `setDataType` method? http://stackoverflow.com/questions/4944301/phpexcel-set-data-type-on-range-of-cells — Pekka, Mar 16 '12 at 09:38
I've checked the dataType.php file and there is no such thing as setDataType(PHPExcel_Cell_DataType::TYPE_DATE) — Peter, Mar 16 '12 at 09:52
Additional info: I just realized if I manually set the cell format to date, Excel still handles it as string :S — Peter, Mar 16 '12 at 10:01
@Peter - if 1.7.6 is manipulating the value so that it appears as "2010-08-12" then it clearly _is_ recognising it as a date, and setting a default date mask for it. — Mark Baker, Mar 16 '12 at 12:15
Tested your code with both 1.7.3c and 1.7.6 versions of PHPExcel and written using Excel2007 writer. Loaded into MS Excel 2010. It's recognising and storing as dates as expected — Mark Baker, Mar 16 '12 at 12:22
@Mark Baker thank you very much for your reply. Firstly I need the date to be formatted as dd/mm/yyyy it doesn't work like this. I also tried $objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH) afterwords which sorted the display issue however then it set the format to general. Also intersting remark is that when you try to sort them it say A to Z instead of Newest to oldest and oldest to newest. Meaning it thinks they're strings. Try to sort them and you will see. — Peter, Mar 16 '12 at 13:05
I see that 1.7.3 does seem to store the value in the shared strings table in xlsx, but that 1.7.6 stores the date correctly so the appropriate "custom" style appears in the style dropdown, and sort option is earliest/latest... looking back through the older code, the PHPExcel_Shared_Date::stringToExcel() method didn't recognise / as a valid date separator character, only -. This is something that was fixed in version 1.7.5 and improved further in 1.7.6 to prevent false date recognition — Mark Baker, Mar 16 '12 at 13:49
@Mark Baker You were right. I got carried away with the fact how it looked and every single time I went into Format Cells it showed that it was general or custom but not date. However it does the sorting right because the data type is correct regardless the format. Thank you for your help! Peter Please vote up my issue and add a proper reply so I can set your answer as solution. — Peter, Mar 17 '12 at 09:31
There's only one question left. How can I show the data like dd-mm-yyyy? I manged to do it like d-m-yy but it's be nicer to do it like dd-mm-yyyy. Thank you. — Peter, Mar 17 '12 at 09:38

score 4 · Accepted Answer · answered Mar 17 '12 at 10:16

This problem related to the PHPExcel_Shared_Date::stringToExcel() method failing to recognise strings containing / as potentially being valid dates.... an issue that was resolved in version 1.7.5.

The default date format mask for the AdvancedValueBinder is 'yyyy-mm-dd'. You're not limited to the formats listed in PHPExcel_Style_NumberFormat; but you can then set the format to anything that is accepted by Excel. so:

$objPHPExcel->getActiveSheetIndex()
            ->getStyle('A1')
            ->getNumberFormat()->setFormatCode('dd-mm-yyyy');

PHP Excel value binder doesn't recognize date

1 Answers1