Sonata Admin is Admin Generator for Symfony, and it is something that I use on almost every project. In this post, I will explain how to add an option for export to Sonata Admin. As you already know Sonata Admin has default exports formats (xls, xml, json, csv) but I needed one more, I needed to export to never version of excel (Excel2007).

You can take parts that you need and create a new export to something else, but this is the best way I found to do this.

TL;DR: Code for this can be found here.


What are we going to do ?

Following the example of current Sonata Exporter Writers we need to create a custom writer, we need to overwrite Sonata Exporter from SonataCoreBundle with our own and we need to edit our AdminClass to accept new type.

For this example we will also need PHPOffice/PHPExcel so add it with composer :

composer require phpoffice/phpexcel

XlsxWriter

After looking at other Writers (XlsWriter, CsvWriter …​) this is the code I created for our new xlsx export:

XlsxWriter.php
<?php

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

Exporter

We will copy code from Sonata Exporter and only add our new option to switch statement:

case 'xlsx':
    $writer = new XlsxWriter('php://output');
    $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
    break;

So our Exporter now looks like:

Exporter.php
<?php

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

We also have to overwrite service that sonata declared with our class:

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

Admin Class

The only thing that is left is to add a new option to our Admin Class and set fields for export:

YourAdmin.php
<?php

namespace AppBundle\Admin;

use Sonata\AdminBundle\Admin\AbstractAdmin;
//...

class YourAdmin extends AbstractAdmin
{
    //...
    public function getExportFormats()
    {
        return ['xlsx', 'xls', 'csv', 'json', 'xml'];
    }
    //optional, if not set Sonata will take all variables
    public function getExportFields()
    {
        return array(
            $this->trans('export.createdAt') => 'createdAt',
            $this->trans('export.id') => 'id'
            // add your fields with or without translations
        );
    }
}

That is it, run it and try new export option, I hope this helps someone with this problem.