Filters excelphp

Question

Filters excelphp

Navigation

#1 by (2 votes)

0

Hi, I'm using this library link

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

and in the BD the info is in the sgt structure: 2018-06-19 12:13:21 but when you put it in the cell it reads it as text and not as 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, for which I think the solution is on this side.

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

php cakephp phpexcel

asked by Andrés Vélez 06.07.2018 в 19:00

source

1 answer

JavaScript: Error with .then and innerHTML Get the results inserted

score 2 · Accepted 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 ) of the database in the format valid 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');

Then yyyy/mm/dd equals the year with 4 digits, the month in numerical format with 2 digits, the day in numeric format with two digits and everything separated by / , [$-240A]hh:mm:ss am/pm equals that it is a format of 12 hours, so if the database is configured as a 24-hour format, this is done by differentiating the day and night with am/pm , and the hours, minutes and seconds are set to 2 digits and separated by :

And with this the filter looks like this: