dd/mm/yyyy format in PHPExcel adding single quote(

Question

In my webpage, I am using PHPexcel 1.8 to generate excel files. Now I am facing an issue which describes below.

I have some date values in the format dd/mm/yyyy. The value is coming. But it shows with a single quote(') at the begining.

I tried many solutions. But none helps me out.

My sample code portion is given below

$cnt = 1;
foreach($gluuidArr as $uuid){
    $this -> excel_180->getActiveSheet() ->setCellValue('A'.$cnt,$aVal);
    $this -> excel_180->getActiveSheet() ->setCellValue('B'.$cnt,$bVal);
    $this -> excel_180 -> getActiveSheet() -> setCellValue('C'.$cnt, $cVal);
    $this -> excel_180->getActiveSheet() ->setCellValueExplicit('D'.$cnt,$dVal, PHPExcel_Cell_DataType::TYPE_STRING);
    $this -> excel_180 -> getActiveSheet() -> setCellValue('E'.$cnt, $eVal);

    $cnt += 1;
}

where $aVal, $bVal,.. are declaring inside the loop and $eval contains date in dd/mm/yyyy format

suppose the date is 20/05/2016, when click on the excel cell, it will show like '20/05/2016

I have tried

$this -> excel_180->getActiveSheet()->getStyle('E'.$cnt)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);

But no luck.

Please help me to get rid of this issue. Any help could be appreciated.

Answer 1

That's because you're not storing a date, you're storing a string that happens to represent a date to human beings. MS Excel doesn't store dates as strings, but as floats, representing the number of days since 1st January 1900 (or 1st January 1904 on the Mac version)

If you want to store a date/time value in PHPExcel, then you need to convert that date/time to an MS Excel timestsamp value, and then set a format mask for the cell indicating how you want it to be displayed.

This is all explained in the PHPExcel Documentation and demonstrated in the Examples