PEAR Spreadsheet Excel Writer memory exhausted

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ozzy86
Forum Newbie
Posts: 1
Joined: Thu Apr 07, 2011 3:17 am

PEAR Spreadsheet Excel Writer memory exhausted

Post by ozzy86 »

Hello

Got a strange problem when trying to create a quite heavy excel file (1.8M). It only works if I set php_value memory_limit 1250M.
The created excel file is constantly growing in size due to new data inserted in DB. So I have to increase memory_limit all the time, which is not very cool, as u understand:)
If I don't increase this value the script will give me fatal error:
Allowed memory size of 587202560 bytes exhausted (tried to allocate 205455 bytes) in /usr/share/php/Spreadsheet/Excel/Writer/BIFFwriter.php on line 158

So I am looking for any method to reduce this unbelievable memory usage.

Any help will be much appreciated:)
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: PEAR Spreadsheet Excel Writer memory exhausted

Post by mecha_godzilla »

I've had a similar problem, though when I was generating web page reports rather than Excel files. As with your script, the memory requirement was huge and it was taking an age to generate. Unfortunately I can't remember what the exact solution was as it was a while ago, but in a general sense here's how I diagnosed the problem:

SUB-OPTIMAL CODE
Look very carefully at any loops or nested loops that you're using - the code isn't necessarily wrong but PHP isn't handling it very well. For my problem script, before the rewrite the memory usage was going beyond 100MB and afterwards was around 600KB. In your case, is it really likely that 1.25GB is needed to generate a tiny little 1.8MB Excel file? :)

A very brief guide to optimising PHP code can be found here:

http://atomized.org/2005/04/php-perform ... practices/

The excessive memory usage is almost certainly a repeating function that uses values that aren't being 'cleared' from the memory after each iteration, so try limiting the number of rows you're outputting to see if this might be the case.

DIAGNOSING PROBLEM CODE
Comment out sections of your code and/or time them to see how long they're taking to complete and what their memory usage is. Bear in mind that when you echo() out any values this will stop your Excel file from being output.

To time a section of your code, do this:

Code: Select all

$start = microtime_float();
function_or_other_code_that_you_want_to_test_the_execution_time_of_here();
$end = microtime_float();

echo 'Script Execution Time: ' . round($end - $start, 6) . ' seconds';

function microtime_float() {
    list ($msec, $sec) = explode(' ', microtime());
    $microtime = (float)$msec + (float)$sec;
    return $microtime;	
}
To calculate the memory usage, do this:

Code: Select all

first_section_of_code();
echo 'Script Memory Usage: ' . parse_memory_usage(memory_get_usage(true));
second_section_of_code();
echo 'Script Memory Usage: ' . parse_memory_usage(memory_get_usage(true));

function parse_memory_usage($size) {
    $unit = array('b','kb','mb','gb','tb','pb');
    return @round($size/pow(1024,($i=floor(log($size,1024)))),2) . ' ' . $unit[$i];
}
Set-up as many breakpoints as needed until you identify where the problem lies. You could also post your code here - it might be possible to identify the problem just by looking at it.

HTH,

Mecha Godzilla
Post Reply