Laravel-excel: don't understand $sheet->setColumnFormat() with date?

[thearabbit]

I use $sheet->setColumnFormat(array('A1:A5' => 'dd-mm-yyyy'));.
And then i input '2014-05-21', but it show the same for inputting.

[MaatwebsiteSupport]

Did you call ->setColumnFormat() after ->fromArray()? It should be called before.

[thearabbit]

i try the both (after and before), but it don't work.

[MaatwebsiteSupport]

Im just reading some google results and PHPExcel doesn't transform dates to the format you give. It's only meant for telling Excel which date format you have entered.

So you have to format the dates yourself before inserting them.

[thearabbit]

Thanks.

[michelgallant]

I've found with some playing around that you can get the setColumnFormat() to actually change the outputted format of the dates, but you have to convert your dates from what Excel considers a plain text field, to a date value first. Here's what I have and it works well.

$sheet->setColumnFormat([PHPExcel_Cell::stringFromColumnIndex($fieldIdx) => "dd-mmm-yy"]);
foreach($data AS &$row) {
    // convert content to Excel date stamp
    $row[$key] = PHPExcel_Shared_Date::PHPToExcel(strtotime($row[$key]));
}

or, more simplified, assuming that your date column is the first column, and the key in your array is 'datecol'

$sheet->setColumnFormat(['A' => "dd-mmm-yy"]);
foreach($data AS &$row) {
    // convert content to Excel date stamp
    $row['datecol'] = PHPExcel_Shared_Date::PHPToExcel(strtotime($row[$key]));
}

The key being the conversion using PHPExcel_Shared_Date::PHPToExcel

[gcanivet]

Hello, I came to the same conclusion as @michelgallant however applying setColumnFormat still has no effect. In this case, the outputted column contains the correct Excel datetime (ie. 41914) however it is not displayed in the right format (Oct 2 2014). Upon opening the file, and formatting the date, Excel does recognize it as a proper date. This is the best for working with the column as a date in Excel, but a user would be confused seeing only the underlying numbers. Note, I am using Excel for Mac 2011, and not sure if this has something to do with it. Sorry to drag this ticket up. Any ideas?

[gcanivet]

Nevermind. It appears to have been a caching issue or my changes were not updated on the filesystem. Sorry. It is now working as expected. Very useful.

For others sake, I too had to use PHPToExcel. It appears that function will accept a string (ie. "2014-10-02") or object, however, when I used a string it returned false. As such, I used strtotime() as per above and it returned a proper Excel datetime which could then be correctly formatted using setColumnFormat.