Instantly share code, notes, and snippets.

Embed
What would you like to do?
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.

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.

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.

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);
    }
@Van-peterson

This comment has been minimized.

Van-peterson commented Aug 21, 2018

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
export_xlsx

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment