Change date format in xls export with Sonata Admin Bundle

Question

I taken over responsibility for a Symfony2 application, built on the Sonata Admin Bundle, and have been asked to make a small change by the users. In the xls export of a list page, the dates all appear as e.g. Wed, 01 Aug 2012 00:00:00 +0200, but the Excel format is General. The users would like the data in this column to be an Excel date type, so that it is sort-able.

I have been able to find some information about export customization, but this mostly concerns choosing the list export file types, or which fields to include, rather than how to change the format in the exported document. A similar question was asked here (I think) but there is no answer.

I think this would (or should) be very simple, but it is certainly not obvious. Any help would be much appreciated.

Answer 1

A small improvement for Marciano's answer. Makes the code a bit more resilient against sonata updates.

public function getDataSourceIterator()
{
    $datasourceit = parent::getDataSourceIterator();
    $datasourceit->setDateTimeFormat('d/m/Y'); //change this to suit your needs
    return $datasourceit;
}

Answer 2

In my admin class EmployeeAdmin I use getExportFields function specifies which fields we want to export:

   public function getExportFields() {
       return array(
            $this->trans('list.label_interview_date') => 'interviewDateFormatted'           
       );
}

interviewDateFormatted is actually a call to the corresponding entity (Employee) method getInterviewDateFormatted which looks like this:

    public function getInterviewDateFormatted() {
    return ($this->interviewDate instanceof \DateTime) ? $this->interviewDate->format("Y-m-d") : "";
}

This way I can change date format or do other necessary changes to the fields I want to export.