Custom sonata admin export

Readme.md

Custom Sonata Admin Exporter

This example will help you create Sonata Admin export for excel 2007, if you need this do not forget to install PHPOffice/PHPExcel :

composer require phpoffice/phpexcel

Or just take parts you need for your own custom exporter.

Exporter.php

<?php
/**
 * Created by PhpStorm.
 * User: markokunic
 * Date: 4/21/17
 * Time: 3:41 PM
 */

namespace AppBundle\Exporter;

use Sonata\CoreBundle\Exporter\Exporter as BaseExporter;
use Exporter\Source\SourceIteratorInterface;
use AppBundle\Exporter\Writer\XlsxWriter;
use Exporter\Writer\XlsWriter;
use Exporter\Writer\XmlWriter;
use Exporter\Writer\JsonWriter;
use Exporter\Writer\CsvWriter;
use Exporter\Handler;
use Symfony\Component\HttpFoundation\StreamedResponse;

class Exporter extends BaseExporter
{
 /**
     * @throws \RuntimeException
     *
     * @param string                  $format
     * @param string                  $filename
     * @param SourceIteratorInterface $source
     *
     * @return StreamedResponse
     */
    public function getResponse($format, $filename, SourceIteratorInterface $source)
    {
        switch ($format) {
            case 'xls':
                $writer = new XlsWriter('php://output');
                $contentType = 'application/vnd.ms-excel';
                break;
            case 'xml':
                $writer = new XmlWriter('php://output');
                $contentType = 'text/xml';
                break;
            case 'json':
                $writer = new JsonWriter('php://output');
                $contentType = 'application/json';
                break;
            case 'csv':
                $writer = new CsvWriter('php://output', ',', '"', '', true, true);
                $contentType = 'text/csv';
                break;
            case 'xlsx':
                $writer = new XlsxWriter('php://output');
                $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
                break;
            default:
                throw new \RuntimeException('Invalid format');
        }

        $callback = function () use ($source, $writer) {
            $handler = Handler::create($source, $writer);
            $handler->export();
        };

        return new StreamedResponse($callback, 200, array(
            'Content-Type' => $contentType,
            'Content-Disposition' => sprintf('attachment; filename="%s"', $filename),
        ));
    }
}

services.yml

services:
    sonata.admin.exporter:
        class: AppBundle\Exporter\Exporter

XlsxWriter.php

<?php
/**
 * Created by PhpStorm.
 * User: markokunic
 * Date: 4/21/17
 * Time: 3:35 PM
 */

namespace AppBundle\Exporter\Writer;

use Exporter\Writer\TypedWriterInterface;
use PHPExcel_IOFactory;
use PHPExcel;
use PHPExcel_Style_Alignment;

class XlsxWriter implements TypedWriterInterface
{
    const LABEL_COLUMN = 1;
    /** @var  PHPExcel */
    private $phpExcelObject;
    /** @var array */
    private $headerColumns = [];
    /** @var  string */
    private $filename;
    /** @var int */
    protected $position;

    public function __construct($filename)
    {
        $this->filename = $filename;
        $this->position = 2;
    }
    public function getDefaultMimeType()
    {
        return 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
    }

    public function getFormat()
    {
        return 'xlsx';
    }

    /**
     * Create PHPExcel object and set defaults
     */
    public function open()
    {
        $this->phpExcelObject = new PHPExcel();
    }

    /**
     * {@inheritdoc}
     */
    public function write(array $data)
    {
        $this->init($data);

        foreach ($data as $header => $value) {
            $this->setCellValue($this->getColumn($header), $value);
        }

        ++$this->position;
    }

    /**
     *  Set labels
     * @param $data
     *
     * @return void
     */
    protected function init($data)
    {
        if ($this->position > 2) {
            return;
        }
        $i = 0;
        foreach ($data as $header => $value) {
            $column = self::formatColumnName($i);
            $this->setHeader($column, $header);
            $i++;
        }

        $this->setBoldLabels();
    }

    /**
     * Save Excel file
     */
    public function close()
    {
        $writer = PHPExcel_IOFactory::createWriter($this->phpExcelObject, 'Excel2007');
        $writer->save($this->filename);
    }

    /**
     * Returns letter for number based on Excel columns
     * @param int $number
     * @return string
     */
    public static function formatColumnName($number)
    {
        for ($char = ""; $number >= 0; $number = intval($number / 26) - 1) {
            $char = chr($number%26 + 0x41) . $char;
        }
        return $char;
    }
    /**
     * @return \PHPExcel_Worksheet
     */
    private function getActiveSheet()
    {
        return $this->phpExcelObject->getActiveSheet();
    }
    /**
     * Makes header bold
     */
    private function setBoldLabels()
    {
        $this->getActiveSheet()->getStyle(
            sprintf(
                "%s1:%s1",
                reset($this->headerColumns),
                end($this->headerColumns)
            )
        )->getFont()->setBold(true);
    }

    /**
     * Sets cell value
     * @param string $column
     * @param string $value
     */
    private function setCellValue($column, $value)
    {
        $this->getActiveSheet()->setCellValue($column, $value);
    }

    /**
     * Set column label and make column auto size
     * @param string $column
     * @param string $value
     */
    private function setHeader($column, $value)
    {
        $this->setCellValue($column.self::LABEL_COLUMN, $value);
        $this->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
        $this->headerColumns[$value] = $column;
    }

    /**
     * Get column name
     * @param string $name
     * @return string
     */
    private function getColumn($name)
    {
        return $this->headerColumns[$name].$this->position;
    }
}

YourAdmin.php

<?php

namespace AppBundle\Admin;

use Sonata\AdminBundle\Admin\AbstractAdmin;
use Sonata\AdminBundle\Datagrid\DatagridMapper;
use Sonata\AdminBundle\Datagrid\ListMapper;
use Sonata\AdminBundle\Form\FormMapper;
use Sonata\AdminBundle\Show\ShowMapper;
use Sonata\AdminBundle\Route\RouteCollection;

class YourAdmin extends AbstractAdmin
{
    //...
    public function getExportFormats()
    {
        return ['xlsx'];
    }
    public function getExportFields()
    {
        return array(
            $this->trans('export.createdAt') => 'createdAt',
            $this->trans('export.id') => 'id'
            // add your types
        );
    }
}

I've tried your method to override the JsonWriter but Sonata doesn't seem to use my Exporter
(even if I see my Exporter matching the sonata.admin.exporter service id)

Actually I needed to modify the behaviour of the json_encode to unescape unicode.

I failed hard after hours of experimenting but cannot figure out why.

What version of Symfony are you using? because on Symfony4+ you will have to create a CompailerPass to replace the Sonata exporter, this:

services:
    sonata.admin.exporter:
        class: AppBundle\Exporter\Exporter

won't work anymore.

Hi ! Thanks for your code 👍

However Phpexcel is deprecated. You should use PhpSpreadsheet.

For install, use composer :
composer require phpoffice/phpspreadsheet

Then, you must change XlsxWriter.php like this :

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

    /** @var  Spreadsheet */
    private $phpExcelObject;

    /**
     * Create PHPExcel object and set defaults
     */
    public function open()
    {
        $this->phpExcelObject = new Spreadsheet();
    }

    /**
     * Save Excel file
     */
    public function close()
    {
        $writer = IOFactory::createWriter($this->phpExcelObject, 'Xlsx');
        $writer->save($this->filename);
    }

Thank you @kunicmarko20, It works for me. But i want to change 'export_format_xlsx' to 'xlsx' in the export list under the results.
Please view attached image

I don't think it's necessary to override the Exporter (sonata.admin.exporter) with AppBundle\Exporter\Exporter. Just add to your services.yaml:

    app.exporter.writer.xlsx:
        class: AppBundle\Exporter\Writer\XlsxWriter        
        arguments: ['%sonata.exporter.writer.xls.filename%']
        tags:
            - { name: sonata.exporter.writer }

And you are all set. You might need sonata-project/exporter .

I don't think it's necessary to override the Exporter (sonata.admin.exporter) with AppBundle\Exporter\Exporter. Just add to your services.yaml:

    app.exporter.writer.xlsx:
        class: AppBundle\Exporter\Writer\XlsxWriter        
        arguments: ['%sonata.exporter.writer.xls.filename%']
        tags:
            - { name: sonata.exporter.writer }

And you are all set. You might need sonata-project/exporter .

Totally agree! Since this was created a long time ago, I feel like the whole gist would need an update 😄

Hello, I want to export a custom csv template namely "adwords" template. I have added this to services.yaml file. But still doesnt recognize my new custom export:

sonata.admin.exporter.writer.adwords:
        class: App\Util\Exporter\Writer\AdwordsWriter
        arguments: [ '%sonata.admin.exporter.writer.adwords.filename%' ]
        tags:
            - { name: sonata.exporter.writer }

Hello, I want to export a custom csv template namely "adwords" template. I have added this to services.yaml file. But still doesnt recognize my new custom export:

sonata.admin.exporter.writer.adwords:
        class: App\Util\Exporter\Writer\AdwordsWriter
        arguments: [ '%sonata.admin.exporter.writer.adwords.filename%' ]
        tags:
            - { name: sonata.exporter.writer }

sorry @Bakhtiyar-Garashov but I haven't used sonata in years, maybe you can get help on Symfony Slack #sonata channel.