CODEWITHSUNDEEP
phpphpexcel
DaHuJIka
DaHuJIka

Reputation: 81

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.

Upvotes: 6

Views: 2710

Answers (3)

Antony
Antony

Reputation: 4300

This is the same in PHPSpreadsheet in 2021 but the following solution will resolve this:

$phpSpreadsheet->getActiveSheet()->setCellValue('H'.$start,'=DATE('.date('Y,m,d',strtotime($yourtime)).')');

You don't obviously have to use strtotime but any Unix date stamp or format as long as its Y,m,d which Excel dates take as the input format (https://exceljet.net/excel-functions/excel-date-function)

This solution makes use of the inbuilt XLSX date function rather than relying on PhpSpreadsheet.

Upvotes: 0

DaHuJIka
DaHuJIka

Reputation: 81

And one more decision:

  $objPHPExcel->getActiveSheet()->SetCellValue('A1', '=NOW()+0.00001*60*'.$start);

Upvotes: 0

Matt Raines
Matt Raines

Reputation: 4218

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.

Upvotes: 5

Related Questions