Create a gist now

Instantly share code, notes, and snippets.

Embed
Custom sonata admin export

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.

<?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:
sonata.admin.exporter:
class: AppBundle\Exporter\Exporter
<?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;
}
}
<?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
);
}
}
@mlaopane

This comment has been minimized.

Show comment
Hide comment
@mlaopane

mlaopane Feb 7, 2018

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.

mlaopane commented Feb 7, 2018

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.

@kunicmarko20

This comment has been minimized.

Show comment
Hide comment
@kunicmarko20

kunicmarko20 Feb 9, 2018

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.

Owner

kunicmarko20 commented Feb 9, 2018

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.

@hroux

This comment has been minimized.

Show comment
Hide comment
@hroux

hroux Feb 26, 2018

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

hroux commented Feb 26, 2018

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);
    }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment