EasyAdmin & Symfony – Add an Excel export button to your table

EasyAdmin is a powerful package to create backends for Symfony application. Backends mean CRUD tool for your entities.

In this article, we will see how add an Export button to your tables to export your data.

If you need more informations on the package, visit the official website : https://symfony.com/doc/master/bundles/EasyAdminBundle/index.html

We suppose here you have already installed the plugin, and have some entities configurated to use your dashboard.

First, we will override the main controller to use our own controller, for this, edit the config file config/routes/easy_admin.yaml :

easy_admin_bundle:
    resource: 'App\Controller\AdminController'
    prefix: /admin
    type: annotation

Then, create this controller, and you can use our PHP function to simply export to XLS :

<?php

namespace App\Controller;

use Doctrine\Common\Collections\ArrayCollection;
use EasyCorp\Bundle\EasyAdminBundle\Controller\EasyAdminController;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Symfony\Contracts\Translation\TranslatorInterface;

class AdminController extends EasyAdminController
{
    /**
     * @var TranslatorInterface
     */
    private $translator;

    /**
     * AdminController constructor.
     * @param TranslatorInterface $translator
     */
    public function __construct(TranslatorInterface $translator)
    {
        $this->translator = $translator;
    }

    /**
     * @return StreamedResponse
     * @throws \Exception
     */
    public function exportExcelAction()
    {
        $entity = $this->request->get('entity');

        if (! $entity) {
            throw new \Exception('Error');
        }

        $sortDirection = $this->request->query->get('sortDirection');
        if (empty($sortDirection) || !in_array(strtoupper($sortDirection), ['ASC', 'DESC'])) {
            $sortDirection = 'DESC';
        }

        $queryBuilder = $this->createListQueryBuilder(
            $this->entity['class'],
            $sortDirection,
            $this->request->query->get('sortField'),
            $this->entity['list']['dql_filter']
        );

        $entities = new ArrayCollection($queryBuilder->getQuery()->getArrayResult());

        if (! count($entities)) {
            throw new \Exception("No data to export");
        }

        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();

        $row = 1;

        $headers = array_keys((array) $entities[0]);

        // write headers
        foreach ($headers as $i => $header) {
            $sheet->setCellValueByColumnAndRow(++$i, $row, $this->translator->trans($this->humanize($header)));
        }

        // set header style
        $styleArray = [
            'font'  => [
                'bold'  => true,
                'color' => ['rgb' => 'FFFFFF'],
            ]];

        $spreadsheet->getActiveSheet()
            ->getStyle(Coordinate::stringFromColumnIndex(1) .'1:'. Coordinate::stringFromColumnIndex(count($headers)) .'1')
            ->applyFromArray($styleArray)
            ->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('066885')
        ;

        // write values
        foreach ($entities as $entry) {
            $row++;
            $values = array_values($entry);

            foreach ($values as $i => $value) {
                $sheet->setCellValueByColumnAndRow(++$i, $row, $value);
            }
        }

        // autosize all columns
        foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
            $spreadsheet->setActiveSheetIndex($spreadsheet->getIndex($worksheet));

            $sheet = $spreadsheet->getActiveSheet();
            $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(true);

            foreach ($cellIterator as $cell) {
                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
            }
        }

        $sheet->setTitle('Export - '. $entity);

        // Create and force download the file
        $streamedResponse = new StreamedResponse();

        $streamedResponse->setCallback(function () use ($spreadsheet) {
            // $spreadsheet = //create you spreadsheet here;
            $writer =  new Xlsx($spreadsheet);
            $writer->save('php://output');
        });

        $streamedResponse->setStatusCode(200);
        $streamedResponse->headers->set('Content-Type', 'application/vnd.ms-excel');
        $streamedResponse->headers->set('Content-Disposition', 'attachment; filename="Export.xlsx"');

        return $streamedResponse->send();
    }
	
    /**
     * @param string $value
     * @return string
     */
    private function humanize(string $value): string
    {
        return ucfirst(strtolower(trim(preg_replace(['/([A-Z])/', '/[_\s]+/'], ['_$1', ' '], $value))));
    }
}

This is for the Controller part. In this file, we extend main EasyAdminController to create our own action (exportExcelAction). Then we just write our logic, it’s a simple and basic code to export all rows and columns for a given entity.

Then, we have to add the export button to the listing view. This is simple, we will just override the list view. For this, just create this file : templates/bundles/EasyAdminBundle/default/list.html.twig, and write :

{# templates/bundles/EasyAdminBundle/default/list.html.twig #}

{% extends '@!EasyAdmin/default/list.html.twig' %}

{% block new_action %}
    <div class="button-action">
        <a class="{{ _action.css_class|default('') }}" href="_action.name })) }}" target="{{ _action.target }}" rel="noopener noreferrer">
            {% if _action.icon %}<i class="fa fa-fw fa-{{ _action.icon }}"></i>{% endif %}
            {{ _action.label is defined and not _action.label is empty ? _action.label|trans(_trans_parameters) }}
        </a>

        {% if easyadmin_action_is_enabled_for_list_view('export', _entity_config.name) %}
            <a class="{{ _action.css_class|default('') }}" href="'exportExcel' })) }}" target="{{ _action.target }}" rel="noopener noreferrer">
                <i class="fa fa-file-excel"></i>
                {{ 'Export Excel'|trans }}
            </a>
        {% endif %}
    </div>
{% endblock new_action %}

Here, we just add an HTML button, according to a condition, then we point this link to our new action in our new controller.

The condition we have added allow you to use easy_admin config to manage the button. If you want to show the Export button into the list view of an Entity, just add in your config/packages/easy_admin.yaml :

MyEntity:
        class: App\Entity\MyEntity
        label: 'My entity title'
        list:
            actions: ['edit', 'search', 'show', 'export']

You can add the export action to the list view config to activate the button.

One comment

Laissez un commentaire