Memory Full Error

Topics: User Forum
May 27, 2014 at 2:27 PM
Edited May 27, 2014 at 2:40 PM
I need help in structuring my code to prevent this error.
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 19 bytes) in d:\php\Classes\PHPExcel\Cell.php on line 130
My platform is:
Windows xP
PHP 5.3
PHPExcel 1.7.6

When I attempt to write 3K+ records with 39 columns it runs out of memory in either the cell.php or worksheet.php app.

Note - the app has no problem when I only attempt to export a few records.

Here is my code (if this insufficient to provide help please suggest what else I can show)
<?php
try 
{
    require_once 'PHPExcel.php';

    $Excel = new PHPExcel();
    $objSheet = "";

    class setupFunctions
    {
        .............................
        function defaultFields()
        {
            global $aFieldList,$defaultSelect...;

            //setup column labels for output
            $aFieldList =  array( 'l1', 'l2', 'l3',......);

            //setup revised sql select
            $defaultSelect  = 'select f1, f2, f3.....';
            return;
        }

        ..............
        function setUpExcel()
        {
            global $Excel, $objSheet, $cWorkSheetName;

            $Excel->getDefaultStyle()->getFont()->setName('Arial');
            $Excel->getDefaultStyle()->getFont()->setSize(10);
            $Excel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
            $Excel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
            $Excel->getActiveSheet()->getPageSetup()->setFitToPage(false);
            $Excel->getActiveSheet()->getPageSetup()->setScale(100);
            $Excel->getActiveSheet()->getPageSetup()->setHorizontalCentered(false);
            $Excel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1,1);
            $Excel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
            $Excel->getActiveSheet()->getHeaderFooter()->setOddFooter('&C&B Printed on &D &T &R&B Page &P of &N');

            $objSheet = $Excel->getActiveSheet();
            $objSheet->setTitle($cWorkSheetName);
            $objSheet->setShowGridlines(true);
        }

    } //end setupFunctions class;
    $pl = new setupFunctions;

    $aFieldList = array();/*column header list*/
    $defaultSelect = "";/*default list of fields if user does not select specific fields*/
    .....
    $pl->defaultFields();
    ........
    $sql = $defaultSelect.' from <my table>....';

    if ( $result = $mysqli->query($sql) )
    {
        if ($result->num_rows > 0)
        {
            ...
            $cWorkSheetName = "Client";
            ....
            /*output is an xls spreadsheet*/
            $pl->setUpExcel();

            /* add header row */
            $cFile = "Adhoc_summary_report_of_".$cWorkSheetName."_records";
            $row = 1; // 1-based index
            $col = 0;
            for($i=0; $i < count($aFieldList); $i++) {
                $Excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $aFieldList[$i]);
                $col++;
            }

            /*add content*/
            $row++;
            while($row_data = $result->fetch_assoc()) {
                $col = 0;
                foreach($row_data as $key=>$value) {
                    ....................................
                    $Excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $thisVal);
                    $col++;
                }
                $row++;
            }

            /*adjust column width for each column*/
            $nCols = count($aFieldList); //set the number of columns

            foreach (range(0, $nCols) as $col) {
                $Excel->getActiveSheet()->getColumnDimensionByColumn($col)->setAutoSize(true);                
            }
            require_once 'PHPExcel\IOFactory.php';   
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename ="'.$cFile.'.xls"');
            header('Cache-Control: max-age=0');
            $objWriter = PHPExcel_IOFactory::createWriter($Excel, 'Excel5');
            $objWriter->save('php://output');
            exit;
        }else{
            throw new Exception("No matching records found. Try typing in less in your search request.");
        }
    }else{
        throw new Exception("Query failed!.");
    }
}

catch (exception $e) 
{
    echo 'An error occured<br/>'.$e->getMessage();
}

?>
Coordinator
May 27, 2014 at 2:57 PM
Start by looking at the options for cell caching
May 27, 2014 at 3:15 PM
Edited May 27, 2014 at 4:09 PM
Thanks.
May 27, 2014 at 5:33 PM
Edited May 27, 2014 at 5:35 PM
Looked at some of the simple ways to cache the cell memory, to no avail.

Reviewed Kim Steinhaug suggestion on Jan 7, 2011 at 11:02 AM and tried the following methods outlined in the documentation on my version of PHPExcel in chapter 4.2.1
// Initiate cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '32MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
I inserted the following in my code
    require_once 'PHPExcel.php';

    $Excel = new PHPExcel();
            // Initiate cache
//          $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
//          $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
//          $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
            $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
            $cacheSettings = array( 'memoryCacheSize' => '128MB');
            PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
Not really sure how to get the other options installed as my previous attempt to use Pear have been unsuccessful:
PHPExcel_CachedObjectStorageFactory::cache_to_apc;
PHPExcel_CachedObjectStorageFactory::cache_to_memcache;
PHPExcel_CachedObjectStorageFactory::cache_to_wincache;
Since I am working on a Windows platform, Do I need to do the following:
  1. Have Pear installed
  2. Have to compile each of these and their dependencies files as well
  3. Then adjust the php.ini file to reference the related app
Or is there some simpler way to do this?