Memory error, how to bypass this ?

Topics: Developer Forum
Jul 12, 2011 at 1:28 PM

Dear all,

 

First, thanks for your crazy class PHPExcel.
It's like magic !

I try to use your class to generate xls file for exporting some database data 

I build a simple sample :

<?phpheader("Content-Type: text/html");echo "Start bench<br>";error_reporting(E_ALL);$iterator=2000;echo date('H:i:s') . " last Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB<br>";require_once '1.7.6/Classes/PHPExcel.php';echo date('H:i:s') . " last Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB<br>";$objPHPExcel = new PHPExcel();for ($i=0;$i<$iterator;$i++){ echo $i . " -> : " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB<br>"; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i, "A_".$i); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, "B_".$i); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$i, "C_".$i); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$i, "D_".$i); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$i, "E_".$i); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$i, "F_".$i); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$i, "G_".$i); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$i, "H_".$i);
}
echo date('H:i:s') . " last Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB<br>";$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');echo "Output to "."output/test".date('H_i_s').".xls";$objWriter->save("output/test".date('H_i_s').".xls");?>

 

 

And my output is 

 

Start bench
10:07:40 last Peak memory usage: 0.5 MB
10:07:40 last Peak memory usage: 0.75 MB
0 -> : 3 MB
1 -> : 4.5 MB
2 -> : 4.5 MB
3 -> : 4.5 MB
4 -> : 4.5 MB
5 -> : 4.5 MB
6 -> : 4.5 MB
7 -> : 4.5 MB
8 -> : 4.5 MB
9 -> : 4.5 MB
10 -> : 4.5 MB
11 -> : 4.5 MB
12 -> : 4.5 MB
13 -> : 4.5 MB
14 -> : 4.5 MB
15 -> : 4.5 MB
16 -> : 4.5 MB
17 -> : 4.5 MB
18 -> : 4.5 MB
19 -> : 4.5 MB
20 -> : 4.5 MB
21 -> : 4.5 MB
22 -> : 4.5 MB
23 -> : 4.5 MB
24 -> : 4.5 MB
25 -> : 4.5 MB
26 -> : 4.5 MB
27 -> : 4.5 MB
28 -> : 4.5 MB
29 -> : 4.5 MB
30 -> : 4.5 MB
31 -> : 4.5 MB
32 -> : 4.5 MB
33 -> : 4.5 MB
34 -> : 4.5 MB

 

....

 

 

1466 -> : 16 MB
1467 -> : 16 MB
1468 -> : 16 MB
1469 -> : 16 MB
1470 -> : 16 MB
1471 -> : 16 MB
1472 -> : 16 MB
1473 -> : 16 MB
1474 -> : 16 MB
1475 -> : 16 MB
1476 -> : 16 MB
1477 -> : 16 MB
1478 -> : 16 MB
1479 -> : 16 MB
1480 -> : 16 MB
1481 -> : 16 MB
1482 -> : 16 MB
1483 -> : 16 MB

( ! ) Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 59 bytes) in D:\wamp\www\xlstest\1.7.6\Classes\PHPExcel\Worksheet.php on line 961
Call Stack
# Time Memory Function Location
1 0.0082 386120 {main}( ) ..\test.php:0
2 5.5122 16770864 PHPExcel_Worksheet->setCellValue( ) ..\test.php:13
3 5.5122 16770896 PHPExcel_Worksheet->getCell( ) ..\Worksheet.php:860

 

Maybe I'm doing something wrong ?

Do you know there is a solution to save memory and complete my loop ?

I can't add more memory to my php script because I'm on shared server.

 

Best

Ultral ?

Jul 12, 2011 at 2:20 PM

I play with the PHPExcel_CachedObjectStorageFactory and if I add this , it's much much better :

 

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp   ;

PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

 

Unfortunelly, it crash when I try to save the file :(

This is my ouput :

 

Output to output/test13_18_16.xls

( ! ) Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 24 bytes) in D:\wamp\www\xlstest\1.7.6\Classes\PHPExcel\CachedObjectStorage\PHPTemp.php on line 98
Call Stack
# Time Memory Function Location
1 0.0076 386040 {main}( ) ..\test.php:0
2 8.6953 11845800 PHPExcel_Writer_Excel2007->save( ) ..\test.php:36
3 13.9172 12052008 PHPExcel_Writer_Excel2007_StringTable->createStringTable( ) ..\Excel2007.php:210
4 16.5482 16635744 PHPExcel_Worksheet->getCell( ) ..\StringTable.php:64
5 16.5482 16635744 PHPExcel_CachedObjectStorage_PHPTemp->getCacheData( ) ..\Worksheet.php:929
6 16.5483 16636224 unserialize ( ) ..\PHPTemp.php:98

 

Jul 12, 2011 at 2:23 PM

Hi, you need to increase your memory limit. 16MB is a little low ;) try 128M or higher! (search this forum for more memory conserving tips and tricks)

Jul 12, 2011 at 3:01 PM

I can't increase memory because I'm on shared server :(

This is why i'll be happy if there is another solution.