PHPExcel date required dd/MM/yy

Question

I am using PHPExcel Lib to download excel sheet and have required the column date format like 25-May-17 instead of 25-05-17 in my download excel sheet.

I was able to added number format 25-05-17 but not like 25-May-17 and below is my code like.

      $objPHPExcel->getActiveSheet()
    ->getStyle('V'.$i)
    ->getNumberFormat()
    ->setFormatCode(
        PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY
    );

I can not find the constant in Lib class

const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd';
const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd';
const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy';
const FORMAT_DATE_DMYSLASH = 'd/m/y';
const FORMAT_DATE_DMYMINUS = 'd-m-y';

Can some please help me on this to set date like d-M-yy(25-May-17)?.

Answer 1

You're not constrained by the predefined constants; those are just some of the format strings that MS Excel recognises... a comprehensive list of ever possible mask would be excessive.... and unnecessary... all you're doing is passing a string value to the setFormatCode() method, so you can pass a string literal.

The MS Excel mask for the format that you want is dd-mmm-yyyy, and you can set that simply as a string literal rather than using any constant:

$objPHPExcel->getActiveSheet()
    ->getStyle('V'.$i)
    ->getNumberFormat()
    ->setFormatCode('dd-mmm-yyyy');

Note that MS Excel itself has a "custom" option for number format codes, which allows you to set a string literal in exactly the same way