PHPExcel memory exhausted

Question

Im using PHPExcell to export 4000 rows with 20 columns. I wanted to color every second row in sheet, then i wrote a loop, but it returns me

FAtal Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in D:\Data\xampp\htdocs\Classes\PHPExcel\Style\Supervisor.php

I've been searching 2 hours to resolve problem but I failed. Every topic i found is pretty old, 2010 or earlier. I've tried to use caching, something liek this

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( ' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);   

But that doesn't help. Any idea how can i fix that problem?

And at the end my loop i use to generate xlsx and format cells:

$i = 2;
    foreach($table as $row){
    $objPHPExcel->getActiveSheet()
                ->setCellValue('A'.$i, $row[1])
                ->setCellValue('B'.$i, $row[2])
                ->setCellValue('C'.$i, $row[3])
                ->setCellValue('D'.$i, $row[4])
                ->setCellValue('E'.$i, $row[5])
                ->setCellValue('F'.$i, $row[6])
                ->setCellValue('G'.$i, $row[7])
                ->setCellValue('H'.$i, $row[8])
                ->setCellValue('I'.$i, $row[9])
                ->setCellValue('J'.$i, $row[10])
                ->setCellValue('K'.$i, $row[11])
                ->setCellValue('L'.$i, $row[12])
                ->setCellValue('M'.$i, $row[13])
                ->setCellValue('N'.$i, $row[14])
                ->setCellValue('O'.$i, $row[15])
                ->setCellValue('P'.$i, $row[16])
                ->setCellValue('Q'.$i, $row[17])
                ->setCellValue('R'.$i, $row[20])
                ->setCellValue('S'.$i, $row[21])
                ->setCellValue('T'.$i, $row[22]);
        // Color every second row
    if($i%2!=0){
    $objPHPExcel->getActiveSheet()->getStyle('A'.$i.':T'.$i)->getFill()->applyFromArray(
    array(
    'type'       => PHPExcel_Style_Fill::FILL_SOLID,
    'startcolor' => array('rgb' => 'e8f4ff')
    ));
    }

    //color column "O"
    if($i%2==0){
    $objPHPExcel->getActiveSheet()->getStyle('O'.$i)->getFill()->applyFromArray(
    array(
    'type'       => PHPExcel_Style_Fill::FILL_SOLID,
    'startcolor' => array('rgb' => 'fffacd')
    ));
    }
    else{
    $objPHPExcel->getActiveSheet()->getStyle('O'.$i)->getFill()->applyFromArray(
    array(
    'type'       => PHPExcel_Style_Fill::FILL_SOLID,
    'startcolor' => array('rgb' => 'fff9bd')
    ));
    }

    // color column "N"
    if($i%2==0){
    $objPHPExcel->getActiveSheet()->getStyle('N'.$i)->getFill()->applyFromArray(
    array(
    'type'       => PHPExcel_Style_Fill::FILL_SOLID,
    'startcolor' => array('rgb' => 'eaffee')
    ));
    }
    else{
    $objPHPExcel->getActiveSheet()->getStyle('N'.$i)->getFill()->applyFromArray(
    array(
    'type'       => PHPExcel_Style_Fill::FILL_SOLID,
    'startcolor' => array('rgb' => 'ccffcc')
    ));
    }           


    $i++;
    }

EDIT:: I post full code coz i have no idea what more i can do. I create new phpexcel object and then import empty template only with 1st row as header with colnames. rest cells are totaly empty with preset style. I set style for 10 000 rows. But it still returns me same error. There's code:

            error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    date_default_timezone_set('Europe/London');

    if (PHP_SAPI == 'cli')
        die('This example should only be run from a Web Browser');

    /** Include PHPExcel */
    require_once '../Classes/PHPExcel.php';



    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objReader = $objReader->load('components/com_uhlist/Classes/template.xlsx');
    $template = $objReader->getActiveSheet();

    $objPHPExcel->addExternalSheet($template);
    $objPHPExcel->setActiveSheetIndex(1);

    $user = JFactory::getUser();
    $username = $user->get('username');
    // Set document properties
    $objPHPExcel->getProperties()->setCreator($username)
                                 ->setLastModifiedBy($username)
                                 ->setTitle("User & Host List");



    // Add some data
    // Aktywowanie arkusza
    //$objPHPExcel->setActiveSheetIndex(-0);


    // Export danych
    $db = JFactory::getDBO();
    $query = 'SELECT * FROM '.$db->getPrefix().'users_n_hosts';
    $db->setQuery($query);
    $table = $db->loadRowList();

    $i = 2;
    foreach($table as $row){
    $objPHPExcel->getActiveSheet()
                ->setCellValue('A'.$i, $row[1])
                ->setCellValue('B'.$i, $row[2])
                ->setCellValue('C'.$i, $row[3])
                ->setCellValue('D'.$i, $row[4])
                ->setCellValue('E'.$i, $row[5])
                ->setCellValue('F'.$i, $row[6])
                ->setCellValue('G'.$i, $row[7])
                ->setCellValue('H'.$i, $row[8])
                ->setCellValue('I'.$i, $row[9])
                ->setCellValue('J'.$i, $row[10])
                ->setCellValue('K'.$i, $row[11])
                ->setCellValue('L'.$i, $row[12])
                ->setCellValue('M'.$i, $row[13])
                ->setCellValue('N'.$i, $row[14])
                ->setCellValue('O'.$i, $row[15])
                ->setCellValue('P'.$i, $row[16])
                ->setCellValue('Q'.$i, $row[17])
                ->setCellValue('R'.$i, $row[20])
                ->setCellValue('S'.$i, $row[21])
                ->setCellValue('T'.$i, $row[22]);       
    $i++;
    }



    // Nazwa arkusza
    $objPHPExcel->getActiveSheet()->setTitle('User & Host');


    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);


    // Redirect output to a client’s web browser (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="User&Host_'.date('d-m-Y').'.xlsx"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    exit;

    }

Newest Code:: Insert data into template:

            $objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
    $objPHPExcel = $objPHPExcel->load('components/com_uhlist/Classes/template.xlsx');
    $objPHPExcel->setActiveSheetIndex(0);

    $user = JFactory::getUser();
    $username = $user->get('username');
    // Set document properties
    $objPHPExcel->getProperties()->setCreator($username)
                                 ->setLastModifiedBy($username)
                                 ->setTitle("User & Host List");

    // Export danych
    $db = JFactory::getDBO();
    $query = 'SELECT * FROM '.$db->getPrefix().'autocad_users_n_hosts';
    $db->setQuery($query);
    $table = $db->loadRowList();

    $i = 2;
    foreach($table as $row){
    $objPHPExcel->getActiveSheet()
                ->setCellValue('A'.$i, $row[1])
                ->setCellValue('B'.$i, $row[2])
                ->setCellValue('C'.$i, $row[3])
                ->setCellValue('D'.$i, $row[4])
                ->setCellValue('E'.$i, $row[5])
                ->setCellValue('F'.$i, $row[6])
                ->setCellValue('G'.$i, $row[7])
                ->setCellValue('H'.$i, $row[8])
                ->setCellValue('I'.$i, $row[9])
                ->setCellValue('J'.$i, $row[10])
                ->setCellValue('K'.$i, $row[11])
                ->setCellValue('L'.$i, $row[12])
                ->setCellValue('M'.$i, $row[13])
                ->setCellValue('N'.$i, $row[14])
                ->setCellValue('O'.$i, $row[15])
                ->setCellValue('P'.$i, $row[16])
                ->setCellValue('Q'.$i, $row[17])
                ->setCellValue('R'.$i, $row[20])
                ->setCellValue('S'.$i, $row[21])
                ->setCellValue('T'.$i, $row[22]);       
    $i++;
    }

    // Redirect output to a client’s web browser (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="User&Host_'.date('d-m-Y').'.xlsx"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    exit;

Answer 1

Have you tried any of the other caching methods? SQLite or SQLite 3 are the most memory efficient, because all other methods retain an in-memory cell index.

Wherever possible, apply your styling to a range of cells rather than to every individual cell. Range styling is far more memory efficient, and is the recommended approach This is because it only creates a single style object for the specified range; while styling individual cells creates a style record for every individual usage.

Those are the two biggest memory reducing factors.

Creating an initial template with the styles already defined, and using the worksheets duplicateStyle() or duplicateStyleArray() methods may also help - especially as you can duplicate to a range of cells for your alternate rows - because this doesn't create new style objects but reuses the existing defined style objects.

Failing all that, you may still need to increase your php memory settings.