Synfony 4: exporter personalizado

Escenario

El bunde de SonataAdmin para Symfony te permite crear listados exportables sin apenas esfuerzo. Por defecto, ofrece la posibilidad de exportar a csv, xls, json y xml. Basta con crear el fichero Admin correspondiente a la entidad. Por ejemplo si quisiéramos que un listado de facturas de nuestra aplicación pueda ser exportado a Excel, deberemos crear primero la clase de tipo Admin, InvoiceAdmin, en la que sobreescriberemos dos métodos extendidos de la clase AbstractAdmin. getExportFormats() para indicar qué formatos queremos que estén disponibles en el listado para exportar y getExportFields() en el que definiremos los campos que queremos que figuren el fichero exportado.

<?php

// src/Admin/InvoiceAdmin.php

namespace Acme\Admin;

use Sonata\AdminBundle\Admin\AbstractAdmin;
use Sonata\AdminBundle\Datagrid\DatagridMapper;
use Sonata\AdminBundle\Datagrid\ListMapper;
use Sonata\AdminBundle\Form\FormMapper;
use Sonata\AdminBundle\Route\RouteCollection;
use Sonata\AdminBundle\Show\ShowMapper;
use Sonata\DoctrineORMAdminBundle\Filter\DateRangeFilter;
use Sonata\Form\Type\DateTimeRangePickerType;
use Symfony\Component\Form\Extension\Core\Type\MoneyType;
use Symfony\Component\Form\Extension\Core\Type\TextareaType;

/**
 *
 * @author Marcos 
 */
class InvoiceAdmin extends AbstractAdmin
{
...
    /**
     * @return array
     */
    public function getExportFormats()
    {
        return [
            'xls',
        ];
    }

    /**
     * @return array
     */
    public function getExportFields()
    {
        return array(
            $this->trans('reference') => 'reference',
            $this->trans('customer') => 'customer',
            $this->trans('amount') => 'amount',
            $this->trans('issueDate') => 'issueDate'
        ...
        );
    }
}
...

<?php

// src/Admin/InvoiceAdmin.php

namespace Acme\Admin;

use Sonata\AdminBundle\Admin\AbstractAdmin;

use Sonata\AdminBundle\Datagrid\DatagridMapper;

use Sonata\AdminBundle\Datagrid\ListMapper;

use Sonata\AdminBundle\Form\FormMapper;

use Sonata\AdminBundle\Route\RouteCollection;

use Sonata\AdminBundle\Show\ShowMapper;

use Sonata\DoctrineORMAdminBundle\Filter\DateRangeFilter;

use Sonata\Form\Type\DateTimeRangePickerType;

use Symfony\Component\Form\Extension\Core\Type\MoneyType;

use Symfony\Component\Form\Extension\Core\Type\TextareaType;

/**

* @author Marcos

class InvoiceAdmin extends AbstractAdmin

{

...

/**

* @return array

public function getExportFormats()

{

return [

'xls',

];

}

/**

* @return array

public function getExportFields()

{

return array(

$this->trans('reference') => 'reference',

$this->trans('customer') => 'customer',

$this->trans('amount') => 'amount',

$this->trans('issueDate') => 'issueDate'

...

);

}

...

Problema

Los campos de fecha e importe no vienen formateados cómo quiere el cliente. Podría crear dos métodos adicionales en la entidad Invoice que devolvieran el campo transformado. Por ejemplo:

    public function getIssueDateFormatted(): ?string
    {
        return $this->issueDate !== null ? $this->issueDate->format("d/m/Y") : null;
    }

public function getIssueDateFormatted(): ?string

{

return $this->issueDate !== null ? $this->issueDate->format("d/m/Y") : null;

}

Y, en el fichero de InvoiceAdmin:

    /**
     * @return array
     */
    public function getExportFields()
    {
        return array(
            ...
            $this->trans('issueDate') => 'issueDateFormatted'
            ...
        );
    }

/**

* @return array

public function getExportFields()

{

return array(

...

$this->trans('issueDate') => 'issueDateFormatted'

...

);

}

Es una solución, y de hecho la he empleado para los campos de fecha, pero para el de importe no conseguía que se mostrara correctamente en el fichero Excel exportado, cuando en el listado HTML sí aparecía correctamente.

Solución

Primero damos de alta un Writer para un nuevo tipo de fichero (xlsx) en la aplicación. La clase ha de implementar los métodos de la interfaz TypedWriterInterface. A continuación registramos el servicio en services.yaml y a correr. Para el writer he utilizado la biblioteca PhpOffice\PhpSpreadsheet y me he inspirado en un gist algo desfasado, pero que me ha puesto sobre la pista:

<?php

namespace Acme\Export;

use Sonata\Exporter\Writer\TypedWriterInterface;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**
 * Description of XlsxWriter
 *
 * @author marcos
 */
class XlsxWriter implements TypedWriterInterface
{

    const LABEL_COLUMN = 1;

    /** @var  Spreadsheet */
    private $phpExcelObject;
    
    /** @var array */
    private $headerColumns = [];

    /** @var  string */
    private $filename;

    /** @var int */
    protected $position;

    public function __construct(string $filename)
    {
        $this->filename = $filename;
        $this->position = 2;
    }

    public function getDefaultMimeType(): string
    {
        return 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
    }

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

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

    /**
     * {@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 = IOFactory::createWriter($this->phpExcelObject, 'Xlsx');
        $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 \PhpOffice\PhpSpreadsheet\Spreadsheet
     */
    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;
    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

<?php

namespace Acme\Export;

use Sonata\Exporter\Writer\TypedWriterInterface;

use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**

* Description of XlsxWriter

* @author marcos

class XlsxWriter implements TypedWriterInterface

{

const LABEL_COLUMN = 1;

/** @var Spreadsheet */

private $phpExcelObject;

/** @var array */

private $headerColumns = [];

/** @var string */

private $filename;

/** @var int */

protected $position;

public function __construct(string $filename)

{

$this->filename = $filename;

$this->position = 2;

}

public function getDefaultMimeType(): string

{

return 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

}

public function getFormat(): string

{

return 'xlsx';

}

/**

* Create PHPExcel object and set defaults

public function open()

{

$this->phpExcelObject = new Spreadsheet();

}

/**

* {@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 = IOFactory::createWriter($this->phpExcelObject, 'Xlsx');

$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 \PhpOffice\PhpSpreadsheet\Spreadsheet

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;

}

Añadimos el siguiente bloque en el fichero de configuracón de servicios services.yaml…

    gestpark.exporter.writer.xlsx:
        class: GestPark\Export\XlsxWriter        
        arguments: ['%sonata.exporter.writer.xls.filename%']
        tags:
            - { name: sonata.exporter.writer }

gestpark.exporter.writer.xlsx:

class: GestPark\Export\XlsxWriter

arguments: ['%sonata.exporter.writer.xls.filename%']

tags:

- { name: sonata.exporter.writer }

El nuevo formato en el fichero de InvoiceAdmin…

    /**
     * @return array
     */
    public function getExportFormats()
    {
        return [
            'xlsx',
        ];
    }

/**

* @return array

public function getExportFormats()

{

return [

'xlsx',

];

}

Y, por último, en el fichero translation/SonataAdminBundle.es.xlf incluimos el siguiente bloque de traducción:

<?xml version="1.0"?>
<xliff version="1.2" xmlns="urn:oasis:names:tc:xliff:document:1.2">
    <file source-language="en" datatype="plaintext" original="file.ext">
        <body>
            ...
            <trans-unit id="export_format_xlsx">
                <source>export_format_xlsx</source>
                <target>xlsx</target>
            </trans-unit>  
            ...
        </body>
    </file>
</xliff>

<?xml version="1.0"?>

<body>

...

<trans-unit id="export_format_xlsx">

<source>export_format_xlsx</source>

</trans-unit>

...

</body>

</file>

</xliff>

Nota: Estoy usando la versión de Symfony 4.4 y la 3.78.1 de SonataAdmin. Es posible que, para que te funcione, tengas que instalar el bundle de sonata Exporter.

Escenario

Problema

Solución

Deja una respuesta Cancelar la respuesta