Building very large spreadsheet with PHPSpreadsheet

Unfortunately PHPExcel and PHPSpreadsheet are not very performant for large files.

Your options are pretty limited:

• Keep increasing the memory limit
• Chunk data into separate spreadsheets
• Fallback to CSV (using PHP's built in functions)

The cache suggestion from Maarten is a nice idea but in my experience came with a huge speed cost that completely negated any memory benefit.

My suggestion would be to ditch PHPSpreadsheet entirely and try box/spout

It is built with performance in mind and promises to use less than 3MB of memory no matter what the file size! Not only is it memory efficient, but was about 20-30 times faster than PHPSpreadsheet.

It has some limitations (only 3 file formats supported, no automatic column widths, no column number/string formatting) but I think some of those missing features are planned and for now it was the best option for me for dealing with writing a massive spreadsheet.

Note:You may want to stick with version 2.7 until version 3 performance issues are resolved

Another option I have not tried is PHP_XLSXWriter. Seems to have similar goals as spout

There is a topic on this within their documentation:

https://phpspreadsheet.readthedocs.io/en/latest/topics/memory_saving/#memory-saving

You can basically have cells stored in cache, for example in Redis (from their documentation):

$client = new \Redis();$client->connect('127.0.0.1', 6379);$pool = new \Cache\Adapter\Redis\RedisCachePool($client);$simpleCache = new \Cache\Bridge\SimpleCache\SimpleCacheBridge($pool);\PhpOffice\PhpSpreadsheet\Settings::setCache($simpleCache);

If you use Predis you can use the following repository:

https://github.com/php-cache/predis-adapter

And use this code:

$client = new \Predis\Client($yourParameters, $yourOptions);$pool = new \Cache\Adapter\Predis\PredisCachePool($client);$simpleCache = new \Cache\Bridge\SimpleCache\SimpleCacheBridge($pool);\PhpOffice\PhpSpreadsheet\Settings::setCache($simpleCache);