PHPExcel limit memory

1
votes

Each time I try to export query results to Excel with PHPExcel class, I get those errors:

PHP Fatal error: Out of memory (allocated 1293942784) (tried to allocate 134217728 bytes) in ...\Classes\PHPExcel\Worksheet.php on line 1218

With just a few rows, this work well, but now I have 130k rows and I don't know how to export this.

I tried with Cell Caching, but I get a error too:

Fatal error: Out of memory (allocated 1686896640) (tried to allocate 131072 bytes) in C:\xampp\htdocs\vinhos\Classes\PHPExcel\CachedObjectStorage\MemoryGZip.php on line 49

With cache_in_memory the same error.

php.ini is configured with:

memory_limit=-1

1
phpphpexcel
Cell caching doesn't mean "use no memory at all", it is simply a way of reducing memory requirements.... test the different options and find those that work best for you... SQLite or SQLite are the most memory efficient, but there is a speed trade-off in using cell caching - Mark Baker
Note that cache_in_memory is the default, and stores absolutely everything in memory exactly "as is" so it is the least memory efficient - Mark Baker
I've read in the past about PHPExcel being too lazy to release itself the memory after operations. Seems this isn't fixed yet. - user1299518
How much memory is actually available for php on the server? Shared host or tiny vps might not have enough - Steve
Are you building large arrays in memory as well, e.g. fetching all your database results into an array and then writing them to PHPExcel? - Mark Baker

1 Answers

0
votes

Your script is using over 1.2Gb in memory and you want to give it more?

If you must get the data into Excel via PHP then maybe writing a line at a time to a CSV file might be a more sensible approach. Or pushing the data back into the database and fetching directly into Excel usnig ODBC.