FastExcelWriter: breaking free from the PhpSpreadsheet curse

Almost every PHP developer who has ever had to read or write Excel files knows the PhpSpreadsheet library (formerly PHPExcel). It is a powerful pure-PHP library that lets you read and, more importantly, create Excel spreadsheets. And everything is fine with it as long as you work with a small dataset. But with large files PhpSpreadsheet starts to devour memory monstrously, performance drops sharply, and a PHP script that uses the library often simply dies on a timeout. The problem lies in the library’s architecture.

PhpSpreadsheet seems to be designed very correctly: sheets, rows, cells and other entities are all classes; styles, formats and every possible property are classes too. When a spreadsheet is created, a huge number of interconnected objects is built in memory, properties are assigned to them, all sorts of manipulations are performed, cells are filled with data — and all of this is held in memory and written to the file only at the moment of saving. On small volumes of data this causes no problems, but when creating large Excel spreadsheets the library turns into an extremely voracious and very slow monster.

And so one day, when the resources needed to generate a large Excel file exceeded all reasonable limits, I started looking for an alternative to the well-known library. An alternative turned up. Unlike PhpSpreadsheet, everything there was packed into a single class, the formatting and styling options were as modest as could be — but the XLSX file was created many times faster, and memory consumption was next to nothing. That was because it implemented a completely different principle: data was not accumulated in memory but written to the file row by row, immediately. Inspired by that idea, I created my own library — FastExcelWriter.

I kept the same principle: data is written almost immediately into a temporary XML file, which is then added to the resulting XLSX file (just a reminder that an XLSX is a ZIP archive of XML files). I make heavy use of arrays instead of objects (which also gives a speed boost). Of course I use classes, but without fanaticism, to a minimum.

Today FastExcelWriter is one of the fastest (and most memory-efficient) libraries for creating XLSX files in PHP; in my own measurements the generation speed came out 7–9 times higher than with PhpSpreadsheet.

At the same time it supports almost all features, including formatting styles, conditional formatting, inserting images, charts and comments, and data validation. On top of that, the library’s interface turned out much more concise. Below are two fragments for adding conditional formatting, for comparison.

Code using PhpSpreadsheet:

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$conditional = new Conditional();
$conditional->setConditionType(Conditional::CONDITION_CELLIS);
$conditional->setOperatorType(Conditional::OPERATOR_GREATERTHAN);
$conditional->addCondition(80);
$conditional->getStyle()->getFont()->getColor()->setARGB(Color::COLOR_DARKGREEN);
$conditional->getStyle()->getFill()->setFillType(Fill::FILL_SOLID);
$conditional->getStyle()->getFill()->getStartColor()->setARGB(Color::COLOR_GREEN);

$conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('A1:A10')->getConditionalStyles();
$conditionalStyles[] = $conditional;

$spreadsheet->getActiveSheet()->getStyle('A1:A10')->setConditionalStyles($conditionalStyles);

$writer = new Xlsx($spreadsheet);
$writer->save('demo.xlsx');

And here is the code using FastExcelWriter:

use avadim\FastExcelWriter\Conditional\Conditional;
use avadim\FastExcelWriter\Excel;

$excel = Excel::create();
$style = Excel::newStyle()->setFontColor('#008000')->setFillColor('#00ff00');
$conditional = Conditional::greaterThan(80, $style->toArray());
$excel->sheet()->addConditionalFormatting('A1:A10', [$conditional]);

$excel->save('demo.xlsx');

You have to admit — it is both more concise and clearer!

Of course you can also write formulas into cells with the library, but there is a nuance: formula values are not computed and not written to the file. Yet when the created file is later opened in the appropriate program (MS Excel, LibreOffice, etc.), those programs recalculate everything and display the values on the fly.

“And what about reading XLSX files?” you may ask. For that there is a companion library — FastExcelReader — and it, too, reads files many times faster than the same PhpSpreadsheet.

There is also FastExcelLaravel — a wrapper that bundles both libraries (FastExcelWriter and FastExcelReader) and adds extra methods for importing and exporting Laravel models.

And all of this is open source. Take it, use it, enjoy!