Memory overflow - many files, big problem

Topics: User Forum
Sep 16, 2008 at 12:36 PM
Edited Sep 16, 2008 at 12:37 PM
Hi,

I'm currently working on a project and use PHPExcel to create about 400 .xls files at once. The problem is that after about 40 Excel-files my script stops and displays this error message:

Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 41 bytes) in S:\apache\Apache2\htdocs\phpexcel\Classes\PHPExcel\Style\Color.php on line 73

This is the part of the script, where i create the files:

[loop over all 400+ customers]
                        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
                        $objPHPExcel = $objReader->load("xls_master/NORMADR.xlsx");
                                                   
                        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
                        //$objWriter = new PHPExcel_Writer_PDF($objPHPExcel);
                       
                        $objPHPExcel->setActiveSheetIndex(0);
                       
                        $objPHPExcel->getActiveSheet()->setCellValue('D2', utf8_encode($svgltxt3));
                        $objPHPExcel->getActiveSheet()->setCellValue('C3', utf8_encode($t1_tmp[thdlnr]));
                        $objPHPExcel->getActiveSheet()->getCell('N1')->setValueExplicit(utf8_encode(substr($jahr, 2).".".$monat), PHPExcel_Cell_DataType::TYPE_STRING);
                        $objPHPExcel->getActiveSheet()->setCellValue('C4', utf8_encode($svgltxt1));
                        $objPHPExcel->getActiveSheet()->setCellValue('N3', utf8_encode($t1_tmp[thdlwaeh]));
                        // Filling the single lines...
                        foreach ($t2 as $zeile => $t2_tmp)
                        {
                            if ($zeile > 8)
                            {                               
                                $objPHPExcel->getActiveSheet()->getCell('F'.$zeile)->setValueExplicit(($t2_tmp[we1] * 1), PHPExcel_Cell_DataType::TYPE_NUMERIC);
                                $objPHPExcel->getActiveSheet()->getCell('H'.$zeile)->setValueExplicit(($t2_tmp[we2] * 1), PHPExcel_Cell_DataType::TYPE_NUMERIC);
                                $objPHPExcel->getActiveSheet()->getCell('I'.$zeile)->setValueExplicit(($t2_tmp[we3] * 1), PHPExcel_Cell_DataType::TYPE_NUMERIC);
                                $objPHPExcel->getActiveSheet()->getCell('J'.$zeile)->setValueExplicit(($t2_tmp[we4] * 1), PHPExcel_Cell_DataType::TYPE_NUMERIC);
                                $objPHPExcel->getActiveSheet()->getCell('K'.$zeile)->setValueExplicit(($t2_tmp[we5] * 1), PHPExcel_Cell_DataType::TYPE_NUMERIC);
                                $objPHPExcel->getActiveSheet()->getCell('L'.$zeile)->setValueExplicit(($t2_tmp[we6] * 1), PHPExcel_Cell_DataType::TYPE_NUMERIC);
                                $objPHPExcel->getActiveSheet()->getCell('M'.$zeile)->setValueExplicit(($t2_tmp[we7] * 1), PHPExcel_Cell_DataType::TYPE_NUMERIC);
                                $objPHPExcel->getActiveSheet()->getCell('N'.$zeile)->setValueExplicit(($t2_tmp[we8] * 1), PHPExcel_Cell_DataType::TYPE_NUMERIC);                               
                            }
                        }
                       
                        // Seitenumbrüche
                       
                        $objPHPExcel->getActiveSheet()->setBreak( 'A46' , PHPExcel_Worksheet::BREAK_ROW );
                        $objPHPExcel->getActiveSheet()->setBreak( 'A91' , PHPExcel_Worksheet::BREAK_ROW );
                        $objPHPExcel->getActiveSheet()->setBreak( 'A136' , PHPExcel_Worksheet::BREAK_ROW );
                        $objPHPExcel->getActiveSheet()->setBreak( 'A181' , PHPExcel_Worksheet::BREAK_ROW );
                        $objPHPExcel->getActiveSheet()->setBreak( 'A226' , PHPExcel_Worksheet::BREAK_ROW );
                        $objPHPExcel->getActiveSheet()->setBreak( 'A271' , PHPExcel_Worksheet::BREAK_ROW );
                        $objPHPExcel->getActiveSheet()->setBreak( 'O1' , PHPExcel_Worksheet::BREAK_COLUMN );
                       
                        @$objWriter->save($xls);

                        unset($objPHPExcel);
                        unset($objReader);
                        unset($objWriter);

[/loop]

Even with unsetting the object variables I get this message.
Is there ANY way to avoid the memory overflow? Is there another way to destroy the objects?
Developer
Sep 17, 2008 at 11:28 PM
I tried something similar to what you are showing, but there was no problem creating more than 500 Excel files. Can you create a test script I can use to reproduce the error?
Sep 25, 2008 at 3:09 PM
Try another way:

http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats
http://smarty.incutio.com/?page=BandedReportGenerator

For intellisense use Eclipse PDT

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="urn:schemas-microsoft-com:office:spreadsheet excelss.xsd
">