PHPExcel how to set date format in cell during export so filtering is available?
I would like to be able to filter my data like this:
Year ( + )
Month ( + )
Day ( + ).
At first I ran into a cell formatting problem because the format was set to General. So I did this:
$objPHPExcel->getActiveSheet()
->getStyle('G2:G256')
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
);
And I worked. Now my cell G2 is formatted as date (at least it says so). But I can't sort this until I select a cell and enter it, after that ONLY that cell is sorted. I have no idea why.
Thanks in advance for your help!
source to share
You need to set date values in cells as MS Excel timestamp values, not strings or unix timestamps.
Assuming you are doing this at the point where $row->user_created
is the Unix timestamp value and not
$formattedRow[] = date('Y-m-d',$row->user_created);
using
$formattedRow[] = PHPExcel_Shared_Date::PHPToExcel( $row->user_created );
which will set the value to MS Excel serialized date / timestamp
You still want to set the number format mask so that MS Excel knows how the date should be displayed; but since it is stored in Excel format and not as a string, it will be sorted or filtered or used in formulas, etc.
source to share