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

The source code shows:

const FORMAT_DATE_DMYMINUS                = 'd-M-Y';

M is A short textual representation of a month, three letters Jan through Dec, but with Y you'll still get a 4 digit year.

Why not just pass your format d-M-y?

$objPHPExcel->getActiveSheet()
    ->getStyle('V'.$i)
    ->getNumberFormat()
    ->setFormatCode('d-M-y');

Answer 2

You could use your desired format directly in your code instead of using one of the predefined formats accessible with PHPExcel_Style_NumberFormat's constants.

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

would become:

$objPHPExcel->getActiveSheet()
    ->getStyle('V'.$i)
    ->getNumberFormat()
    ->setFormatCode('d-M-yy');

Note that you might want to check and set your locale properly so that the "M" in your format gives you english month names.

Answer 3

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