How to create a excel file with php in symfony 2 & 3

If you are new in the php development, the PHPExcel library may sound completely unknow for you, this project provides a set of classes for the PHP programming language, which allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML. This project is built around Microsoft's OpenXML standard and PHP.

There are 2 ways to generate Excel Files in Symfony 2-3, either you use the plain ExcelBundle to use the PHPOffice as you would on any kind of PHP project (recommended for simple and complex excel documents as it's very easy to manipulate with the original PHPOffice code) or you can generate Excel files with Twig using the TwigExcelBundle (recommended for basic CSV,XLS and XLSX files without complicated structures).

A. From controllers

In Symfony, is a good practice use bundles instead using require once in a controller to include our library. The creator of this bundle (not the PHPExcel library) is liuggio and the source code can be viewed in the official repository in github.

Note

is important to read the official documentation of the PHPExcel library first, then the process will be really intuitive with symfony 2.

To install our bundle we will add in the composer.json file in the require zone

"liuggio/excelbundle": "dev-master",

If you use composer directly in the console then execute :

composer require liuggio/excelbundle

When all the dependencies has been downloaded, then simply add the bundle to your kernel (AppKernel.php file) so :

$bundles = array(
        // ...
        new Liuggio\ExcelBundle\LiuggioExcelBundle(),// Add excel bundle
);

Creating streamed excel file created with php

In this example we will use the excel 2007 writer, remember that in the original library we used : new PHPExcel_Writer_Excel2007($objPHPExcel); but with this bundle we will use the createWriter function provided by the bundle.

// Important to include 
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;

class DefaultController extends Controller
{

    public function indexAction($name)
    {
        // ask the service for a excel object
       $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

       $phpExcelObject->getProperties()->setCreator("liuggio")
           ->setLastModifiedBy("Giulio De Donato")
           ->setTitle("Office 2005 XLSX Test Document")
           ->setSubject("Office 2005 XLSX Test Document")
           ->setDescription("Test document for Office 2005 XLSX, generated using PHP classes.")
           ->setKeywords("office 2005 openxml php")
           ->setCategory("Test result file");
       $phpExcelObject->setActiveSheetIndex(0)
           ->setCellValue('A1', 'Hello')
           ->setCellValue('B2', 'world!');
       $phpExcelObject->getActiveSheet()->setTitle('Simple');
       // Set active sheet index to the first sheet, so Excel opens this as the first sheet
       $phpExcelObject->setActiveSheetIndex(0);

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $dispositionHeader = $response->headers->makeDisposition(
            ResponseHeaderBag::DISPOSITION_ATTACHMENT,
            'PhpExcelFileSample.xlsx'
        );
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');
        $response->headers->set('Content-Disposition', $dispositionHeader);

        return $response;        
    }
}

Saving a excel file in a path

// Important to include 
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;

class DefaultController extends Controller
{

    public function indexAction($name)
    {
        // ask the service for a excel object
       $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

       $phpExcelObject->getProperties()->setCreator("liuggio")
           ->setLastModifiedBy("Giulio De Donato")
           ->setTitle("Office 2005 XLSX Test Document")
           ->setSubject("Office 2005 XLSX Test Document")
           ->setDescription("Test document for Office 2005 XLSX, generated using PHP classes.")
           ->setKeywords("office 2005 openxml php")
           ->setCategory("Test result file");
       $phpExcelObject->setActiveSheetIndex(0)
           ->setCellValue('A1', 'Hello')
           ->setCellValue('B2', 'world!');
       $phpExcelObject->getActiveSheet()->setTitle('Simple');
       // Set active sheet index to the first sheet, so Excel opens this as the first sheet
       $phpExcelObject->setActiveSheetIndex(0);

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
        // The save method is documented in the official PHPExcel library
        $writer->save('/path/to/save/filename.xlsx');


        // Return a Symfony response (a view or something or this will thrown error !!!)
        return "A symfony response";        
    }
}

B. Using Twig

TwigExcelBundle is a symfony bundle that provides an easy PhpExcel integration for Twig. To install this bundle, execute the following composer command:

composer require mewesk/twig-excel-bundle

Or in case you want to add in the composer.json and then execute composer install:

{
    "require": {
        "mewesk/twig-excel-bundle": "^2.1"
    },
}

Note: if you want to use the Twig bundle, the LuggioExcelBundle isn't required as this bundle will install automatically the original PHPOffice as a composer dependency.

After the installation of the Bundle with composer, register the bundle in your /app/AppKernel.php:

<?php

use Symfony\Component\HttpKernel\Kernel;
use Symfony\Component\Config\Loader\LoaderInterface;

class AppKernel extends Kernel
{
    public function registerBundles()
    {
        $bundles = [
            // .... //  
            new MewesK\TwigExcelBundle\MewesKTwigExcelBundle(),
            // .... //
        ];
 
        // .... //
    }

    // .... //
}

Clear the cache of your project using php bin/console cache:clear and let's get started with the usage of the bundle.

Return excel file as response

According to the way you establish the routes in symfony, the configuration will vary, however to make it work, the principle is the same:

create a twig view with a name, extension type and .twig suffix (e.g excel.xlsx.twig).
Return that view as response from a controller.

YAML

If you use YAML to configure the routes, start by adding a route that targets to a controller:

# routing.yml

mybundle_route:
    path:     /{_filename}.{_format}
    defaults: { _controller: mainBundle:Default:index, _format: xlsx }

## To access this route use: 
## www.mydomain.com/example-filename.xlsx

Then create a controller that returns a TwigExcel bundle view:

<?php

namespace mainBundle\Controller;

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

class DefaultController extends Controller
{
    public function indexAction(Request $request)
    {
        return $this->render('mainBundle:Default:excel.xlsx.twig', [
            'data' => ['La', 'Le', 'Lu']
        ]);
    }
}

Our excel.xlsx.twig view will contain the following structure (located in /src/mainBundle/default/excel.xlsx.twig):

{% xlsdocument %}
    {% xlssheet 'Worksheet' %}
        {% xlsrow %}
            {% xlscell { style: { font: { size: '18' } } } %}Values{% endxlscell %}
        {% endxlsrow %}
        {% for value in data %}
            {% xlsrow %}
                {% xlscell %}{{ value }}{% endxlscell %}
            {% endxlsrow %}
        {% endfor %}
    {% endxlssheet %}
{% endxlsdocument %}

From the controller, we are sending an array with some data and it will be rendered within rows in the twig file. Note that the format can be changed in the way you want according to your needs. Now if you access to the declared route (www.domain.com/custom-filename.xlsx) you will get an excel file as response to download.

Annotation

If you use anotation, create a route in the controller that returns a TwigExcel view:

<?php

namespace mainBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
use Symfony\Component\HttpFoundation\Response;

class DefaultController extends Controller
{
    /**
     * @Route("/{_filename}.{_format}", defaults={"_format"="xls","_filename"="example"}, requirements={"_format"="csv|xls|xlsx"})
     * @Template("mainBundle:Default:excel.xlsx.twig")
     */
    public function indexAction($_filename)
    {
        return ['data' => ['La', 'Le', 'Lu']];
    }
}

The view will be the same as the providen in the YAML configuration. Now if you access to the declared route (www.domain.com/custom-filename.xlsx) you will get an excel file as response to download.

Note: the name of the route will be the filename of the generated file, therefore verify that you provide a valid filename for your excel file in the route with the correct extension to prevent corrupt files.

The TwigExcel bundle provides a detailed documentation of all the available twig tags and functions here.