In my excel file I am building from PHP the first row is the header one. I need to put there several dates as column header. Each date is (in dd/mm/yyyy format) 15/mm/yyyy starting from 15/01/2007 and ending with 15/12/2018 stepping month by month.
My code is this:
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$mese = sprintf("%02s", $mese);
$periodo = '15/'.$mese.'/'.$anno;
$periodo = strtotime($periodo);
$periodo = PHPExcel_Shared_Date::PHPToExcel($periodo);
array_push($header_array,$periodo);
}
}
I build the date as a string, convert it into a unix timestap, convert it into an excel date and push it into the $header_array.
Then I draw the cells in the excel:
$ews->fromArray($header_array, ' ', 'A1');
Finally I format the cells where there is a date as:
$ews->getStyle('U1:EJ1')
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
ISSUES ARE:
- Actually my code is returning 'False' for each cell with a date;
- I need to add a custom mask that is not listed in the PHPExcel library: MM/YYYY to be shown in each cell.
For the first issue: I have checked different SO questions and followed mainly this one. with no luck.
Best Answer
Problem is here:
which would give a string values of
15/1/2015
,15/2/2015
... ``15/12/2015that you're then attempting to convert to a unix timestamp using
strtotime()`.If you read the PHP Docs on date formats used by the
strtotime()
function, you'll see that a/
separator tells PHP that the date string is US date format.... i.e. mm/dd/yyyygiving a month value of
15` in every case, and (of course) there is no month 15Either use a dash (
-
) rather than a/
to indicate European (dd-mm-yyyy
) rather than US date formator re-order the values to use US format
And for the second part of your question.... you can supply almost any format mask that MS Excel recognises, you're not restricted to the built-in formats; so:
The format code is simply a string value