Memory Allocation problem

Topics: Developer Forum, Project Management Forum, User Forum
Jun 2, 2010 at 1:03 PM

Hi Mark,   

                 With your suggestion to me and by seeing your previous posts to other developers i tried to manage some what and  could now  work with templates. But the problem now my template sheet consists of six sheets

from which i am trying to generate 86 sheets .  The first sheet wwill be cloned 82 times and the rest were copied once . But i could make this  only for 85 sheets after which i a getting this message

<b>Fatal error</b>:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 3 bytes) in <b>D:\projects\VRM\pfizerCRS\pfizer_crash_report\PHPExcel\Classes\PHPExcel\Worksheet.php</b> 
on line
<b>2485</b><br />

but my ini_set level is 1024M

For your convenience i have given some code part

Can u help me

<?php

   /****
    * File : dummy.php
    * created for cloning the xls worksheets dynamically and to fetch records
    * */    
   
     // include the configuartion file
    require_once('../../../include/conf.inc.php');
   
   //Declaration of variables
    $i = 0;
    
    $countrySheet = "";
    $tcoSheet = "";
    $tcoAnnual = "";
    $CO2Sheet = "";
    $safetySheet = "";
    $imburseSheet = "";
    
    $sheet = "";
    $sheetTCOQuarter = "";
    $sheetTCOAnnual = "";
    $sheetSafety = "";
    $sheetCO2 = "";
    $sheetImburse = "";
    
    
        $objPHPExcel = PHPExcel_IOFactory::load("../excel_templates/report.xls");
        
        //Get the copy of all the sheets in the template.
        $countrySheet = $objPHPExcel->getSheetByName('Sheet1')->copy();
        $tcoSheet = $objPHPExcel->getSheetByName('Sheet2')->copy();
        $tcoAnnual = $objPHPExcel->getSheetByName('Sheet3')->copy();
        $CO2Sheet = $objPHPExcel->getSheetByName('Sheet4')->copy();
        $safetySheet = $objPHPExcel->getSheetByName('Sheet5')->copy();
        $imburseSheet = $objPHPExcel->getSheetByName('Sheet6')->copy();
        
        //disconnect all existing sheets
        $objPHPExcel->disconnectWorkSheets();
            
    

    // looping with different quarters and countries
    foreach($countryArray as $ck =>$cv) {
        
        //Sheets are cloned here and 82 tabs are created and one for each country
        $sheet = "";
        $sheet = clone $countrySheet;
        $sheet->setTitle($cv);
        $objPHPExcel->addSheet($sheet);
        $objPHPExcel->setActiveSheetIndex($ck);
                       
    }    //unset the copied sheet which has been created for countries
        unset($countrySheet);
        unset($sheet);
        
        // Here the sheet is cloned for Tco quarterly report
        $sheetTCOQuarter = clone $tcoSheet;
        $sheetTCOQuarter->setTitle("TCO Report quarterly");
        $objPHPExcel->addSheet($sheetTCOQuarter);
        $objPHPExcel->setActiveSheetIndex(82);
        unset($tcoSheet);
        unset($sheetTCOQuarter);
        
        //Here the sheet is cloned for Tco Annual report
        $sheetTCOAnnual = clone $tcoAnnual;
        $sheetTCOAnnual->setTitle("TCO Annual Report");
        $objPHPExcel->addSheet($sheetTCOAnnual);
        $objPHPExcel->setActiveSheetIndex(83);
        unset($tcoAnnual);
        unset($sheetTCOAnnual);
        
        //Here the sheet is cloned for CO2 report
        $sheetCO2 = clone $CO2Sheet;
        $sheetCO2->setTitle("CO2 Report");
        $objPHPExcel->addSheet($sheetCO2);
        $objPHPExcel->setActiveSheetIndex(84);
        unset($CO2Sheet);
        unset($sheetCO2);
        
        //Here the sheet is cloned for Safety report
        $sheetSafety = clone $safetySheet;
        $sheetSafety->setTitle("Safety Report");
        $objPHPExcel->addSheet($sheetSafety);
        $objPHPExcel->setActiveSheetIndex(85);
        unset($safetySheet);
        unset($sheetSafety);
        
        //Here the sheet is cloned for Reimbursement report
        $sheetImburse = clone $imburseSheet;
        $sheetImburse->setTitle("Re-Imbursement Report");
        $objPHPExcel->addSheet($sheetImburse);
        $objPHPExcel->setActiveSheetIndex(86);
        unset($imburseSheet);
        unset($sheetImburse);
        
    
?>
Coordinator
Jun 2, 2010 at 9:51 PM

Try calling the disconnectCells() method before unsetting any of the template worksheets.

e.g.

$tcoSheet->disconnectCells();
unset($tcoSheet)

You could also try cloning just the country sheets, but simply rename the other sheets in the template. This doesn't just help memory usage, but it's also simpler code and faster.

$objPHPExcel = PHPExcel_IOFactory::load("../excel_templates/report.xls");

$objPHPExcel->getSheetByName('Sheet2')->setTitle("TCO Report quarterly");
$objPHPExcel->getSheetByName('Sheet3')->setTitle("TCO Annual Report");
$objPHPExcel->getSheetByName('Sheet4')->setTitle("CO2 Report");
$objPHPExcel->getSheetByName('Sheet5')->setTitle("Safety Report");
$objPHPExcel->getSheetByName('Sheet6')->setTitle("Re-Imbursement Report");

$lastCountry = array_pop($countryArray);
foreach($countryArray as $ck =>$cv) {
   $countrySheet = clone $objPHPExcel->getSheetByName('Sheet1');
   $countrySheet->setTitle($cv);
   $objPHPExcel->addSheet($countrySheet);
}
$objPHPExcel->getSheetByName('Sheet1')->setTitle($lastCountry);

While we've tried to improve the excessive memory usage of PHPExcel, we can only reduce it, not eliminate the need for memory completely; but this code logic should give you the complete set of sheets that you want using less memory than your code.

You may need to use $objPHPExcel->setIndexByName($sheetName, $indexPosition) for each sheet to order the tabs as you want them.

 

 

Jun 3, 2010 at 6:18 AM

hi mark,

    Ur concept of disconnecting the cells is working fine, but the problem is takes much time to generate a report. Actually to say, to generate 87 sheets without any data itself it takes more than 7 minutes .  Now i'm afraid, that i have to include 8

sheets and  along with this, if data is going to be included , how long it will take to generate.  And the other problem i a facing , when it is generating the machine gets slower in access, what would be the problem.  How to solve this

 

Once again thanks for all your posts.  With ur suggestions , now i could generate more than 87 sheets