Working with XLS format in Symfony 4

When I was developing on Symfony 2 I had a lot of options to export and read XLS files (PHP Excel was one of those). During my first steps with Symfony 4 it has been more difficult to find an elegant way to do it.

Fortunatly, i found the excellent PHPSpreadSheet which is the next version of PHPExcel, compatible with the last PHP features. To work with it in my Symfony project, I decided to use the PHPSpreadSheetBundle which makes your life easier.

So, after the bundle installation (composer require yectep/phpspreadsheet-bundle) you can use directly the library in your controler.

Begin by declaring the service at the beginning of your controler :

use Yectep\PhpSpreadsheetBundle\Factory;

And don’t forget to inject the service in your controler method :

public function export($id, Factory $factory)

Then you can begin to create your XLS file if it’s what you want (look at the documentation page of the project to read files, it’s very easy…).

$spreadsheet = $factory->createSpreadsheet();

Usually, you’ll want to set document properties :

// Set document properties
$spreadsheet->getProperties()->setCreator('Infy')
->setLastModifiedBy('Infsy')
->setTitle('Scan result export')
->setSubject('Office 2007 XLSX Test Document')
->setDescription('Export of scan results with all vulnerabilities found.')
->setKeywords('office 2007 openxml php')
->setCategory('Test result file');

Then set a title to the current sheet and begin to write in it :

$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Vulnerabilities');
$sheet->setCellValue('A1', 'List of vulnerabilities from '.$detection->getSourceName());

Don’t forget than in Excel you can adress a cell adress by the column letter and the line number, which is very interesting if you have a list to write with a counter :

$sheet->setCellValue('D'.$i, $vulnerability->getVulnerability()->getPluginId());

You can write the result in a file stored on the server, but if you want you can also send the result directly to the client browser for a local download :

$response = $factory->createStreamedResponse($spreadsheet, 'Xls');

// Redirect output to a client’s web browser (Xls)
$response->headers->set('Content-Type', 'application/vnd.ms-excel');
$response->headers->set('Content-Disposition', 'attachment;filename="ExportScan.xls"');
$response->headers->set('Cache-Control','max-age=0');

return $response;

With this bundle you’ll have everything you need to create and read complex Excel documents, in multiple formats (CSV, HTML, ODT, XLS, XLSX and even PDF with some aditional libraries).