Home / Questions / 178480
Accepted
Andrés Vélez
Asked: 2020-07-07 09:00:42 +0800 CST 2020-07-07 09:00:42 +0800 CST

excelphp filters

  • 772

Hello I am using this library https://phpspreadsheet.readthedocs.io/en/develop/

and I want to add a date and time filter that looks like this:

enter image description here

and in the DB the info is in the next structure: 2018-06-19 12:13:21but when you put it in the cell it reads it as text and not as a date

what have i tried?

$worksheet->setCellValue('I'.$i, $user->created);
$this->longitudCeldas('I', $user->created,  $worksheet);

$worksheet->getCell('I'.$i)->setValueExplicit("'".$user->created."'",\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC);

If I add the previous line, it reads it as a number, so I think the solution is on this side.

$worksheet->getStyle('I'.$i)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);
php

1 Answers

  1. Best Answer

    Well I already found the solution so that the excel file automatically filters as a date: 

     $worksheet->setCellValue('I'.$i, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($user->created));

    In the previous line I convert the date obtained ( $user->created) from the database into the valid format for excel, and then I give a format to the cell that is how the client will see the date

    $worksheet->getStyle('I'.$i)->getNumberFormat()->setFormatCode('yyyy/mm/dd  [$-240A]hh:mm:ss am/pm');

    So it yyyy/mm/ddis equivalent to the year with 4 digits, the month in numerical format with 2 digits, the day in numerical format with two digits and everything separated by /, [$-240A]hh:mm:ss am/pmit is equivalent to a 12-hour format, so if it is configured in the database as a 24-hour format, this converts it by differentiating the day and night with am/pm, and the hours, minutes and seconds it puts in 2 digits and separated by:

    And with this the filter looks like this:

    enter image description here

    • 2