Building very large spreadsheet with PHPSpreadsheet

Question

I'm testing out how PHPSpreadsheet works with large excel spreadsheets. Initial tests indicate that for a large spreadsheet one will quickly run out of memory.

Is there a way to write the spreadsheet progressively?

I have an old piece of code that I've been using for a long time to create spreadsheets from PHP. It uses a very old standard, and is due an update. But one advantage of my old code is that I could write to a file as it went rather than building the entire thing in memory, and as a result could easily cope with a very large spreadsheet without blowing the memory limit.

Can something similar be done in PHPSpreadsheet? I've tried reading the documentation, and searched various forums, but most responses seem to be just "increase the available memory".

Solution 1

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

Solution 2

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