If you’ve hit memory issues with large sets of data using the excellent PHPExcel library on your PHP server, here are a couple of tips I found helped a lot on one of my projects when server resources were being stretched.
The first good place to start is to increase the PHP Memory Limit and the Max execution time which if set too low will certainly be causing issues with memory-hungry or long-running scripts manipulating your spreadsheets.
These can be set one of 3 ways.
- Editing php.ini
- Amending your .htaccess files
- From within the PHP script itself.
Depending on the exact server setup, these typically cascade over one another the closer they are to the PHP script being called.
Some hosts don’t allow access to the php.ini and hence you can sometimes override their default settings with either the .htaccess file or from within the PHP script itself.
If you are still not having any luck it’s worth speaking to your hosting provider and asking if they could increase the limits. Just remember, they’ll be more likely to say yes if you’re conservative with your limit increase!
The above can be achieved with the following
php.ini file
|
1 2 3 4 5 6 7 8 9 |
// in php.ini // set memory_limit to 32 Mb memory_limit = 32M // set script execution time to 60 seconds max_execution_time = 60 |
.htaccess file
|
1 2 3 4 5 6 7 8 9 10 |
# in .htaccess # format # php_value setting_name setting_value # set memory_limit to 32 Mb php_value memory_limit 32M # set script execution time to 60 seconds php_value max_execution_time 60 |
within the PHP script itself
|
1 2 3 4 5 6 7 8 9 10 |
// set memory_limit to 32 Mb ini_set('memory_limit', '32M'); // set script execution time to 60 seconds ini_set('max_execution_time', '60'); // alternative method - set script execution time to 60 seconds from when calling the below method. set_time_limit (60); |
If none of the above solve the issue, or perhaps you cannot increase you memory limits any more, you can try setting setPreCalculateFormulas to false as below.
I found that this helped when the above weren’t enough. It is mentioned in the documentation for certain writers and this is what the documentation states :-
By default, this writer pre-calculates all formulas in the spreadsheet. This can be slow on large spreadsheets, and maybe even unwanted. You can however disable formula pre-calculation:
|
1 2 3 |
$objWriter = PHPExcel_IOFactory::createWriter($spreadsheet, 'Excel2007'); // Save memory by not pre-calculating formulas $objWriter->setPreCalculateFormulas(false); |