FORMAT_DATE_* in API Docs

Topics: User Forum
Mar 2, 2011 at 3:53 PM

If I use this code...

$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14);

...the API class doc says this format is 'mm-dd-yy', but no, it is some other format. Instead I must use this code...

$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode('MM-DD-YY');

...which is a lot easier, and note CAPS are required or this won't work either...

...which also makes me wonder if the other FORMAT_DATE_* entries in the API class docs are correct..?

setFormatCode(PHPExcel_Style_NumberFormat::

Coordinator
Mar 2, 2011 at 7:54 PM
ScottMSanders wrote:

If I use this code...

$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14);

...the API class doc says this format is 'mm-dd-yy', but no, it is some other format. Instead I must use this code...

setFormatCode(PHPExcel_Style_NumberFormat::

What format exactly are you getting? And which writer?
The API class document simply specifies these constants and their values, that should match the relevant Excel format values; so the API document is not wrong.

What may be wrong is the code used to render Excel dates to readable dates using these values (for functions like toArray() or the Writers). I've not seen any evidence of this, but it is possible.

ScottMSanders wrote:

$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode('MM-DD-YY');

...which is a lot easier, and note CAPS are required or this won't work either...

setFormatCode(PHPExcel_Style_NumberFormat::

 All format codes should be case insensitive: capitalisation should be irrelevant. If this isn't the case, then there is a code fault... to identify this, I need to know exactly what method (Writer, toArray(), whatever) you are using to test this. Otherwise, I need to test all possible permutations myself to try and identify the location of any fault, and (as I've indicated elsewhere) I have rather less time at the moment than I normally would have. Knowing what you actually get from PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14 would also help. If you could provide a code example that demonstrates this problem, it would certainly help.

Mar 2, 2011 at 8:15 PM

I get format M/D/YYYY, with writers Excel2007 and Excel5, using the above code and writer code like this:

$objWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');

A given date is in MySQL format like 2011-03-02.

I dunno what other code you need, as my script is kinda extensive, but can supply more if needed.

Coordinator
Mar 4, 2011 at 2:14 PM

Interesting; and very strange behaviour. Excel is clearly recognising it as a date mask (otherwise it would simply be displayed as a number), but is ignoring the actual mask value and reverting to the default (locale specific) date mask.

It's similar with a couple of the other masks as well:

d-mmm-yy (FORMAT_DATE_XLSX15) and d-mmm (FORMAT_DATE_XLSX16), which should display the day without leading zeroes, both show it with leading zeroes, while m/d/yy h:mm (FORMAT_DATE_XLSX22) reverts to the locale date/time mask as well. These were all new default masks introduced to the number format dropdowns in MS Excel2007, and all seem to work correctly when set as uppercase rather than lowercase.

I've only tested reading the PHPExcel output in MS Excel 2003 at the moment: I'll test using MS Excel 2010 later.

 

Simply solution is to simply make those particular masks upper case in the constants list, but I want to do some experimentation with custom masks as well to verify that those don't suffer from the same problems.