Search code examples
phpphpexcel

Why PHPexcel insert single quote in date field?

I'm trying to insert date into the cell using PHPExcel. This is my code:

    include('xlsx/Classes/PHPExcel.php');
    include('xlsx/Classes/PHPExcel/Calculation.php');
    include('xlsx/Classes/PHPExcel/Cell.php');
    $objPHPExcel = new PHPExcel();
    $start = 3;
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode('DD.MM.YYYY');
    $objPHPExcel->getActiveSheet()->SetCellValue('A1', date('d.m.Y', time()+60*($start + 3)));
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode('DD.MM.YYYY');
    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    $objWriter->save('text.xls');

However, PHPexcel choose right format, but it inserts date with single quote

'27.02.2016

instead

27.02.2016

I don't know how to insert date without this quote, hope you help me.

Solution

  • The setFormatCode method sets the format Excel will use to output the date, rather than setting a format for PHPExcel to interpret the date. Excel spreadsheets store dates in a specific timestamp format which can be generated using the PHPExcel_Shared_Date class. It has methods for converting between various date formats and Excel's expected format.

    $objPHPExcel->getActiveSheet()->SetCellValue('A1',
    PHPExcel_Shared_Date::PHPToExcel(time()+60*($start + 3)));

    ...should do the trick.