How to create an Excel file with PHP in Symfony 4

The creation of excel files in the development of web applications is a must nowadays and you as a developer, must be aware of the new techniques available in your language of preference to create it. Since 2015, the last PHPExcel version 1.8.1, was released and everyone got a message in the official repository that project wouldn't be no longer maintained and should not be used anymore.

But don't worry, is not like there's no way to generate more excel files using the library. Instead, a new version was published as well with a new name, namely the PHPSpreadsheet library, of the same package of PHPOffice. This library, PhpSpreadsheet is a library written in pure PHP and aims to provide a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc. This library offers support for:

As someone that uses everything in its latest version, we won't teach you how to create excel files with the old library, instead we'll use the new library. In this article, we'll show you how to install the library to create Excel files with PHP in your Symfony 4 based project.

1. Install PHPSpreadsheet

The recommend way to install the PHPExcel successor PHPSpreadsheet is with composer. Open a terminal in the root directory of your symfony 4 project and run the following command:

composer require phpoffice/phpspreadsheet

This will add the phpoffice/phpspreadsheet package as a dependency in your Symfony 4 project and will install it. For more information about the methods, more examples that aren't aware of Symfony 4 but the excel itself, please visit the official repository at Github of PHPSpreadsheet here or the official documentation here.

2. Create excel files by doing

Normally as a developer, you will learn by doing and that means, write and run some code ! We'll show you a couple of traditional examples that you will need often, this cases are the creation of an excel file in the disk (Saving a excel file in a path) and a streamed response (an inline disposition file):

A. Writing an excel file to a local directory

If you are willing to create the file in the server disk, you can use the traditional save method of the library that writes directly to the disk. Just provide the absolute path to the directory where you want to save it and a filename (be sure that the directory exists and you have write permissions in the given directory):

<?php

namespace App\Controller;

use Symfony\Component\HttpFoundation\Response;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;

// Include PhpSpreadsheet required namespaces
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class DefaultController extends Controller
{
    public function index()
    {
        $spreadsheet = new Spreadsheet();
        
        /* @var $sheet \PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet */
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Hello World !');
        $sheet->setTitle("My First Worksheet");
        
        // Create your Office 2007 Excel (XLSX Format)
        $writer = new Xlsx($spreadsheet);
        
        // In this case, we want to write the file in the public directory
        $publicDirectory = $this->get('kernel')->getProjectDir() . '/public';
        // e.g /var/www/project/public/my_first_excel_symfony4.xlsx
        $excelFilepath =  $publicDirectory . '/my_first_excel_symfony4.xlsx';
        
        // Create the file
        $writer->save($excelFilepath);
        
        // Return a text response to the browser saying that the excel was succesfully created
        return new Response("Excel generated succesfully");
    }
}

B. Generate an Excel file response (attachment)

By default when you work with PHP, the response logic is pretty different depending of the framework that you use or the logic that you write by yourself, that's why the library only allows to write to a particular directory. However, this isn't useful when you generate excel files on the fly, because you would need to take care of the files, create them and remove them as well.

If you follow the mentioned logic, the generation of the excel file can be a headache, so the easiest way to do it is to generate a temporary file file on your system with PHP, write the excel content to it and then generate the download of the file with a custom name (the temporary file will be deleted by the system after some time). To return the file as an attachment, we'll use the file helper of the Symfony base controller. This class includes several helper methods for common controller tasks and provides direct access to the service container. The helper method that we needs is called file to simplify serving binary files. The simplest way to use the file() helper is to pass the path of the file to download. The browser will force the download of this file and it will assign it the same name as the original file:

<?php

namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;

// Include PhpSpreadsheet required namespaces
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class DefaultController extends Controller
{
    public function index()
    {
        $spreadsheet = new Spreadsheet();
        
        /* @var $sheet \PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet */
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Hello World !');
        $sheet->setTitle("My First Worksheet");
        
        // Create your Office 2007 Excel (XLSX Format)
        $writer = new Xlsx($spreadsheet);
        
        // Create a Temporary file in the system
        $fileName = 'my_first_excel_symfony4.xlsx';
        $temp_file = tempnam(sys_get_temp_dir(), $fileName);
        
        // Create the excel file in the tmp directory of the system
        $writer->save($temp_file);
        
        // Return the excel file as an attachment
        return $this->file($temp_file, $fileName, ResponseHeaderBag::DISPOSITION_INLINE);
    }
}

Please note that the generated excel file is very simple, we just wrote to a single cell, however the article focuses on how to generate it, save it and download it using the symfony 4 controller. For more documentation about the PHPSpreadsheet library itself, don't forget to visit their documentation.

Happy coding !