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.

Simply ask the question in the Sonata Exporter repository: https://github.com/sonata-project/exporter — mate64, Sep 02 '14 at 13:33
Thanks @cept0. I looked there and found a similar question. The answer was this: "The only solution is to provide a custom DataSourceIterator by extending the getDataSourceIterator method from the Admin class." I'm afraid this is far too vague for me. If I find a more detailed answer, I'll post it here. — j_goldman, Sep 02 '14 at 14:02
Well, this is easy - just extend the Sonata Admin Bundle Base Class and force Sonata to use your custom `DataSourceIterator` like here https://gist.github.com/Tocacar/4337861 — mate64, Sep 02 '14 at 15:47
thanks again. There is no solution, just a question on that link, but it's a nudge in the right direction. I'll give that a try and post the solution if it works. (interesting that the link you posted is from the original developer) — j_goldman, Sep 02 '14 at 16:37

score 6 · Answer 1 · answered Dec 18 '15 at 11:09

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;
}

score 3 · Answer 2 · answered Apr 17 '15 at 08:52

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.

score 2 · Answer 3 · answered Jan 15 '15 at 12:58

this is my code. It's work!

use Exporter\Source\DoctrineORMQuerySourceIterator;
use Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery;
use Sonata\AdminBundle\Datagrid\ProxyQueryInterface;

and function:

/**
 * {@inheritdoc}
 */
public function getDataSourceIterator()
{

    $datagrid = $this->getDatagrid();
    $datagrid->buildPager();
    $fields=$this->getExportFields();
    $query = $datagrid->getQuery();


    $query->select('DISTINCT ' . $query->getRootAlias());
    $query->setFirstResult(null);
    $query->setMaxResults(null);



    if ($query instanceof ProxyQueryInterface) {
        $query->addOrderBy($query->getSortBy(), $query->getSortOrder());

        $query = $query->getQuery();
    }


    return new DoctrineORMQuerySourceIterator($query, $fields,'d.m.Y');
    }

score 2 · Answer 4 · answered Mar 09 '15 at 21:11

just add this in your admin (overriding a method of the admin class you are extending). Found it reading the code. It's not in the docs.

public function getDataSourceIterator()
{
    $datagrid = $this->getDatagrid();
    $datagrid->buildPager();

    $datasourceit = $this->getModelManager()->getDataSourceIterator($datagrid, $this->getExportFields());
    $datasourceit->setDateTimeFormat('d/m/Y'); //change this to suit your needs

    return $datasourceit;
}

score 1 · Answer 5 · answered Dec 21 '14 at 20:28

Did you managed to make it work?

Date format is defined as parameter for new DoctrineORMQuerySourceIterator.php (https://github.com/sonata-project/exporter/blob/master/lib/Exporter/Source/DoctrineORMQuerySourceIterator.php)

DoctrineORMQuerySourceIterator.php is created inside getDataSourceIterator function (https://github.com/sonata-project/SonataDoctrineORMAdminBundle/blob/2705f193d6a441b9140fef0996ca392887130ec0/Model/ModelManager.php)

Inside of Admin.php there is function calling it:

   public function getDataSourceIterator()
   {
        $datagrid = $this->getDatagrid();
        $datagrid->buildPager();
        return $this->getModelManager()->getDataSourceIterator($datagrid, $this->getExportFields());
    }

If you write your own getDataSourceIterator() then you can change date format.

score 1 · Answer 6 · answered Feb 25 '22 at 08:59

Since sonata-admin 4.0, the function getDataSourceIterator() is tagged as final, so you can't override it.

So you need to create a decorating iterator :

<?php

namespace App\Service\Admin;

use Sonata\AdminBundle\Datagrid\ProxyQueryInterface;
use Sonata\AdminBundle\Exporter\DataSourceInterface;
use Sonata\DoctrineORMAdminBundle\Exporter\DataSource;
use Sonata\Exporter\Source\DoctrineORMQuerySourceIterator;
use Sonata\Exporter\Source\SourceIteratorInterface;

class DecoratingDataSource implements DataSourceInterface
{
    private DataSource $dataSource;

    public function __construct(DataSource $dataSource)
    {
        $this->dataSource = $dataSource;
    }

    public function createIterator(ProxyQueryInterface $query, array $fields): SourceIteratorInterface
    {
        /** @var DoctrineORMQuerySourceIterator $iterator */
        $iterator = $this->dataSource->createIterator($query, $fields);

        $iterator->setDateTimeFormat('Y-m-d H:i:s');

        return $iterator;
    }
}

And add it in your config/services.yaml

services:
    ...
    App\Service\Admin\DecoratingDataSource:
        decorates: 'sonata.admin.data_source.orm'
        arguments: ['@App\Services\Admin\DecoratingDataSource.inner']

Found here : https://docs.sonata-project.org/projects/SonataDoctrineORMAdminBundle/en/4.x/reference/data_source/

This is the one. – ManBehindTheCurtain Jul 09 '23 at 17:44 — ManBehindTheCurtain, Jul 09 '23 at 17:44

Change date format in xls export with Sonata Admin Bundle

6 Answers6