25

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

xtempore
  • 5,260
  • 4
  • 36
  • 43

4 Answers4

35

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

andrewtweber
  • 24,520
  • 22
  • 88
  • 110
  • 2
    I have tried out `box/spout`, it is really good for handling very large excel file but it is not fully support the styling / seting column width / auto width / cell merging. – Casper May 07 '19 at 04:35
  • 1
    Wow I just tried box/spout and the speed is astonishing. Also in the year 2021 it is still maintained. If you don't need fancy features beside some formatting of the font or borders it is awesome! – Calamity Jane Jul 29 '21 at 15:42
  • `box/spout` works great. Super fast and super light on resources. However, where db queries are a bottleneck (5k max in one go, 40k records), you need to chunk data which is not possible by default on spout (you need to write to a diff file each time and copy it - which does not seem most optimal) – Deepak Thomas Nov 19 '21 at 06:40
  • Box/spout is no longer maintained in autumn 2022. I am searching for a replacement – Calamity Jane Oct 06 '22 at 14:48
8

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);
tarexme
  • 17
  • 1
  • 5
Maarten Manders
  • 151
  • 4
  • 3
    Unfortunately comes at a huge speed cost, at least in my experience :( – andrewtweber Aug 20 '18 at 21:07
  • 3
    Agree! There's a tipping point at which PHPSpreadsheet becomes unusable because it's too slow. This point depends on the caching mechanism used and on your machine. Spout on the other hand provides near constant performance. – Adrien Aug 28 '18 at 15:17
  • I just changed to box/spout, which was mentioned above after running into exact this problem. So bye bye phpSpreadsheet for large files! – Calamity Jane Jul 29 '21 at 15:44
  • I have a little question, I am using PHPSpreadSheet import classes. should I put this code in __construct() method or AppServiceProvider of Laravel framework? – Rushabh Joshi Feb 15 '22 at 08:58
4

tl;dr: Recommendation as of August 2022: Openspout may be the right choice for your challenge. For me it worked perfectly.

A bit of explanation about the why:

I also ran into the memory problems with PhpSpreadsheet and found this question from a couple of years ago. To make your life easier:

Using other caches for PhpSpreadsheet seems to be too slow - Jason Klein analyzed different caching options for PhpSpreadsheet and none of them was really providing useful performance for large sheets (and large sheets are the reason why you are reading this post, correct?)

The architecture of PhpSpreadsheet just heavily relies on in-memory access to the cells. Any file- or network-traffic for caching impacts performance drastically.

box/spout is no more active - but there is a successor: Openspout, which is a fork of box/spout and is actively maintained as of now. This seems to be the go-to option at the moment for large Excel files via PHP.

Marco
  • 89
  • 2
  • 6
1

Alternative way is https://packagist.org/packages/avadim/fast-excel-writer.

It's support only XLSX-files (not XLS) but 7-9 times faster then PhpSpreadsheet and uses much less memory, with automatic column widths and column/cell formatting. I use this library for generation huge XLSX-files in 100K rows

aVadim
  • 84
  • 2