Working with XLS format in Symfony 4

Cédric THIBAULT

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).

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade