Building very large spreadsheet with PHPSpreadsheet
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".
Answer
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