Fatal error: Allowed memory size

Apr 13, 2009 at 11:21 AM
Edited Apr 13, 2009 at 1:10 PM
1) many thx to PHPExcel dev team for this great lib. I really don't know how/who to thank you, but koyama replayed at my threads and I will mention it.
2) I'm trying to make a monthly report with the  visitors from a site and to have this report as a excel file. I have a pattern/template for this excel file (35sheets x 30col x 50rows). When I try to read this pattern excel file i get the following error:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 15888 bytes) in C:\wamp\www\Tests\PHPExcel\Reader\Excel5.php on line 3286

my questions are:
a) how to prevent having this error?
b) 30sheets(from 35) are almoust the same (just different sheet name, as being the name of the day). Can I reduce this to one sheet template/pattern and use it in PHPExcel? How to load just one sheet pattern.
c) which is the best approach to use when i have such a big number of sheets. I don't have too many datas, just the number of users per day and hour, in a array from a mysql DB. I need to have in the excel file colored cells & formulas.

After some readings on this forum I increased the memory limit and execution time. Now I'm stuked to this error:
Peak memory usage: 409.25 MB
Fatal error: Uncaught exception 'Exception' with message 'Syntax error: ), lookahead: , current char: 0' in C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Parser.php:1406 Stack trace: #0 C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Parser.php(1312): PHPExcel_Writer_Excel5_Parser->_fact() #1 C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Parser.php(1272): PHPExcel_Writer_Excel5_Parser->_term() #2 C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Parser.php(1220): PHPExcel_Writer_Excel5_Parser->_expression() #3 C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Parser.php(1207): PHPExcel_Writer_Excel5_Parser->_condition() #4 C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Worksheet.php(965): PHPExcel_Writer_Excel5_Parser->parse(''$'!$B$3') #5 C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Worksheet.php(484): PHPExcel_Writer_Excel5_Worksheet->_writeFormula(26, 2, '='$'!$B$3', 92) #6 C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Workbook.php(492): PHPExcel_Writer_Excel5_Worksheet->close() #7 C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Workbook.php(233): PHPExcel_Writer_Excel5_Work in C:\wamp\www\Tests\PHPExcel\Writer\Excel5\Parser.php on line 1406

I don't know yet which is the best approach for me in order to have a a excel file with many sheets and formulas.
Developer
Apr 14, 2009 at 1:14 AM
a) With the planned modifications in PHPExcel, the use of shared styles will reduce memory consumption and most users will not need to increase memory limit. We hope to have something ready for PHPExcel 1.6.7.

b) Example how to load a template with just one sheet and copy it within the workbook:

$reader = PHPExcel_IOFactory::createReader('Excel2007');
$excel = $reader->load('template.xlsx');

$sheet = $excel->getActiveSheet();

$copiedSheet = $sheet->copy();
$copiedSheet->setTitle('Monkey');

$excel->addSheet($copiedSheet);

Performance-wise, this is the correct approach, rather than loading a template with many identical sheets.

c) In the future, the recommended best-approach for styling in PHPExcel is likely to be the duplicateStyleArray() method where you can style many cells at once, e.g. 'A1:Z900'. This way, the PHPExcel core can operate fast.

d) Unfortunately, Excel5 writer is having a problem with one of your formulas. It can apparently not handle sheet names such as $. Sorry, but we need to fix this:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=7895

Apr 14, 2009 at 6:36 PM
Edited Apr 14, 2009 at 7:17 PM
many thx for your replay :)

I'm using a complex excel file template. I can send it to you if you are curious and wish to test it with my scenario. my e-mail adress is: dole.doug [at] yahoo [dot] com.

my question now: which is the best approach to use? To use a excel file with formulas or to fill the excel with computed data using PHP from a multi-dimensional array.
Developer
Apr 15, 2009 at 2:30 AM
>> my question now: which is the best approach to use? To use a excel file with formulas or to fill the excel with computed data using PHP from a multi-dimensional array.
Well, you would end up two different Excel files. Personally, I would keep the formulas in the Excel file so the end user has the chance to see how results were calculated.

You are welcome to send me your Excel template (erik at phpexcel dot net). We can use it for testing.
Mar 10, 2011 at 7:41 AM

Same problem for me.

I've solved it by enlarge memory limit at the begining of my script:

ini_set("memory_limit","100M");

If your server can support this memory extension, this will solve your pb.

For exemple, with this :

ini_set("memory_limit","12M");

I obtained 

Fatal error: Allowed memory size of 12582912 bytes exhausted (tried to allocate 71 bytes) in /servers/apache/sites/www.zoom43.fr/dev_site/_mod/sondage.php on line 237

with 50M :

Fatal error: Allowed memory size of 52428800 bytes exhausted (tried to allocate 95 bytes) in /servers/apache/sites/www.zoom43.fr/dev_site/_mod/PHPExcel1.7.5/Classes/PHPExcel/Worksheet.phpon line 961