Avoiding memory exhaustion writing to xlsx file

Topics: Developer Forum
Aug 4, 2013 at 10:08 PM

PHP 5.4
PHPExcel 1.7.8
FreeBSD 9 i386

I need to dump a MySQL database into an xlsx file and I'm hitting memory limits. I'm hitting vm.kmem_size_max, 512M I think, which requires a reboot to change, which isn't easy in production (I'm running on a test box now)... My script runs a few statements, clearing memory in between, creating Excel files for each result set. The biggest one is ~180k rows but the script is breaking before that on a ~100k statement in which I select nine small columns. If it's 1 KB per cell, which I've read, it should be roughly 900 MB? This is on a 32-bit VM. This is the error:

Fatal error: Allowed memory size of 402653184 bytes exhausted (tried to allocate 36 bytes) in /.../CachedObjectStorage/CacheBase.php on line 142

This is using phpTemp cache; I've tried most of them. I'm using the built-in Excel2007 writer.

I found another thread that had interesting stuff in it, but that was about reading from a large Excel file and writing into a database--my problem is in the opposite direction. The script basically runs a select statement and then iterates through each row, passing the data through a number of filters which do things like sanitize data, count rows, etc., one of which (the last one) is PHPExcel. In the PHPExcel filter it writes that row to the worksheet. In the filter destructor, PHPExcel saves the sheet to a file. In other words:
foreach filter (
    init (create PHPExcel object)
foreach row {
    foreach filter {
        run filter on row (set cell value and advance row iterator)
foreach filter (
    finish (save file)
Is there any way to unset a row once it's written, or something else I can do, aside from selecting fewer rows? If I comment out the PHPExcel filter, it runs fine just outputting or counting rows. Reorganizing the script isn't as big a problem as rebooting.

Aug 4, 2013 at 11:11 PM
Ugh, false alarm. I found a latter memory_limit setting buried in another script. Raising that made the error go away. Sorry!