README

The main idea is taken from XlsxWriter.php.

Except:

• PHPOffice\PHPExcel has been replaced with PHPOffice\PhpSpreadsheet;
• The class is used as a service to be able to use it anywhere and make changes to the file.
• The XlsxService class is completely independent and can be used outside of Symfony.

1. Install
2. Use Service
3. Customization
4. Use in SonataAdmin Export
5. Errors

Install

Run:

composer require --prefer-dist denisok94/symfony-export-xlsx
# or
php composer.phar require --prefer-dist denisok94/symfony-export-xlsx

or add to the require section of your composer.json file:

"denisok94/symfony-export-xlsx": "*"

composer update
# or
php composer.phar update

Use XlsxService

namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use \Denisok94\SymfonyExportXlsxBundle\Service\XlsxService;

class ExportController extends AbstractController
{
    /** @var XlsxService */
    private $export;
    /**
     * @param XlsxService $export
     */
    public function __construct(XlsxService $export)
    {
        $this->export = $export;
    }
    /**
     * @return Response
     */
    public function index(): Response
    {
        $fileName = 'my_first_excel.xlsx';
        $temp_file = tempnam(sys_get_temp_dir(), $fileName);
        $this->export->setFile($temp_file)->open();
        $test = [
            ['header1' => 'value1', 'header2' => 'value2', 'header3' => 'value3'],
            ['header1' => 'value4', 'header2' => 'value5', 'header3' => 'value6']
        ];
        foreach ($test as $line) {
            $this->export->write($line);
        }
        $this->export->close();
        return $this->file($temp_file, $fileName, ResponseHeaderBag::DISPOSITION_INLINE);
    }
}

Customization

use \Denisok94\SymfonyExportXlsxBundle\Service\XlsxService;
/** @var XlsxService */
private $export;
/**
 * @param XlsxService $export
 */
public function __construct(XlsxService $export)
{
    $this->export = $export;
}
/**
 * @return Response
 */
public function index(): Response
{
    $fileName = 'my_first_excel.xlsx';
    $temp_file = tempnam(sys_get_temp_dir(), $fileName);
    $this->export->setFile($temp_file)->open();

    //
    $this->export->getProperties()
        ->setCreator('Denis')
        ->setLastModifiedBy('Denis')
        ->setSubject('my_first_excel')
        ->setTitle('my_first_excel');

    $test = [
        ['header1' => 'value1', 'header2' => 'value2', 'header3' => 'value3'],
        ['header1' => '4', 'header2' => '5', 'header3' => '=A3+B3']
    ];
    // header1 (A1) / header2 (B1) / header3 (C1) 
    // value1 (A2) / value2 (B2) / value3 (C2) 
    // 4 (A3) / 5 (B3) / =A3+B3 (C3) 
    foreach ($test as $line) {
        $this->export->write($line);
    }

    // https://phpoffice.github.io/PhpSpreadsheet/classes/PhpOffice-PhpSpreadsheet-Worksheet-Worksheet.html#method_mergeCells
    $this->export->getActiveSheet()->mergeCells('B2:C2');

    // https://phpoffice.github.io/PhpSpreadsheet/classes/PhpOffice-PhpSpreadsheet-Style-Borders.html
    $this->export->getActiveSheet()
        ->getStyle('A2:C3')->getBorders()->applyFromArray(['allBorders' => ['borderStyle' => 'thin', 'color' => ['rgb' => '000000']]]);

    // https://phpoffice.github.io/PhpSpreadsheet/classes/PhpOffice-PhpSpreadsheet-Style-Color.html
    $this->export->getActiveSheet()
        ->getStyle('C3')->getFont()->getColor()->applyFromArray(['rgb' => 'FF0000FF']);

    $this->export->close();

    return $this->file($temp_file, $fileName, ResponseHeaderBag::DISPOSITION_INLINE);
}

Table class

use Denisok94\SymfonyExportXlsxBundle\DBAL\Table as T;
$worksheet = $this->export->getActiveSheet();
//
$value = $worksheet->getCell([T::A, 1])->getValue(); // A:1
$worksheet->getCell([T::B, 2])->setValue($value); // B:2
//
$x = T::B;
while ($x <= T::K) {
    try {
        // code...
        $worksheet->getCell([$x, $y])->setValue($value);
    } catch (\Throwable $th) {
        throw new \RuntimeException('error set "' . T::getLetter($x) . ':' . $y . '":' . $th->getMassage());
    }
    $x++;
}

Use in SonataAdmin Export

Install if missing SonataExporterBundle

composer require sonata-project/exporter

Minimum version doctrine/orm: 2.8

add in config:

# ~config/packages/sonata_exporter.yaml
services:
  sonata.exporter.writer.xlsx:
    class: Denisok94\SymfonyExportXlsxBundle\Writer\XlsxWriter
    arguments: ["php://output"]
    tags:
      - { name: sonata.exporter.writer }

add in YourAdmin class according to the documentation:

public function getExportFormats(): array
{
    return ['xlsx'];
}

and if you need to configure the fields and their translation

protected function configureExportFields(): array
{
    // example:
    return [
        $this->trans('export.title') => 'title',
        $this->trans('export.anons') => 'text',
        $this->trans('export.date') => 'date'
    ];
}

Errors

If you see the error:

Attempted to call an undefined method named "toIterable" of class "Doctrine\ORM\Query"

Then make sure that the doctrine/orm version is 2.8 or higher.

You may need to update:

• sonata-project/admin-bundle: ~3.*
• sonata-project/doctrine-orm-admin-bundle: ~3.*
• doctrine/doctrine-bundle: ~^2.3