Memory issue on Styling borders

Dec 13, 2011 at 12:07 PM
Edited Dec 13, 2011 at 12:09 PM

PHP ver 5.3.1

PHPExcel ver 1.7.6

this is the portion of code where i access PHPExcel

 

the commented line (with a arrow in front) is the source of all evil....

            $objPHPExcel->getActiveSheet()->getStyle('B:C')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle('B:C')->getFill()->getStartColor()->setARGB('00ff9933');
            $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
            $objPHPExcel->getActiveSheet()->getStyle('I:J')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle('I:J')->getFill()->getStartColor()->setARGB('00ff9933');
->        //$objPHPExcel->getActiveSheet()->getStyle('A:B')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            echo phpversion();
        while ($first = mysql_fetch_array($query))
            {
                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$first['NR_SESIZ']);
                    //$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    //$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    //$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    //$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                   
                   
                    //$objPHPExcel->getActiveSheet()->getStyle('B',$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                    //$objPHPExcel->getActiveSheet()->getStyle('B',$i)->getFill()->getStartColor()->setARGB('00ff9933');
                    $objPHPExcel->getActiveSheet()->setCellValue('B'.$i,strip_date($first['DATA_S']));
                    //$objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    //$objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    //$objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    //$objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                   
                    //$objPHPExcel->getActiveSheet()->getStyle('C',$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                    //$objPHPExcel->getActiveSheet()->getStyle('C',$i)->getFill()->getStartColor()->setARGB('00ff9933');
                    $objPHPExcel->getActiveSheet()->setCellValue('C'.$i,strip_time($first['DATA_S']));
                    //$objPHPExcel->getActiveSheet()->getStyle('C'.$i)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                   
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.$i,$first['NUME']);
                   
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.$i,$first['TELEFON']);
                    $query_cod = mysql_query("SELECT COD FROM cod_deranj WHERE ID_COD_DERANJ=".$first['ID_COD_DERANJ_S'],$dbconnection) or die('Raport L:124');
                    $cod_der_s = mysql_fetch_array($query_cod);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.$i,$cod_der_s['COD']);
                   
                    $query_strada = mysql_query("SELECT ID_LOCALITATE FROM strazi WHERE ID_STRADA='".$first['ID_STRADA']."'",$dbconnection) or die('Raport L:127');   
                    $strada = mysql_fetch_array($query_strada);
                    $query_localitate = mysql_query("SELECT DENUMIRE FROM localitati WHERE ID_LOCALITATE='".$strada['ID_LOCALITATE']."'",$dbconnection) or die(mysql_error());   
                    $localitate = mysql_fetch_array($query_localitate);
                   
                    $objPHPExcel->getActiveSheet()->setCellValue('G'.$i,$localitate['DENUMIRE']);
                    $objPHPExcel->getActiveSheet()->setCellValue('H'.$i,$first['ADRESA']);
                   

                    $objPHPExcel->getActiveSheet()->setCellValue('I'.$i,strip_date($first['DATA_T']));
   
                    $objPHPExcel->getActiveSheet()->setCellValue('J'.$i,strip_time($first['DATA_T']));
                   
                    $i++;
                    //echo 'data_s : '.$first['DATA_S'].' NR_SESIZ : '.$first['NR_SESIZ'].' ID_SESIZ : '.$first['ID_SESIZ'].' Localitate : '.$localitate['DENUMIRE'].'</br>';
                   
                   
            }

 

sometimes it gives me this error Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 43 bytes) in *\classes\PHPExcel\CachedObjectStorage\Memory.php on line 47

and sometimes it refers to line 1008 within Worksheet.php (didn't copy paste that one).

Same amount of memory being referenced.

It so happens that the current query i was playing with amounts to 2860 cells within the excel file. That means..with border styling activated...every cell wants more than 100KB mem....

With that line commented it barely budges beyond 10MB of memory usage...

with                     //$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)) bla bla (all 4 lines that manually add borders to every cell accesed) uncommented it goes to around 10 MB.

with the lines for B row activated same story...more or less 10MB. (i'm talking about task manager httpd process monitoring as i presume it gets ram for the php process).

Also for C...Also if i add for row D.

Does AllBorders method get ALL the borders of even blank cells? It's pretty weird..

Hope i'm not being a bug myself... :)

(edited) even if i leave only Column A on that getStyle , it still exhausts the memory.


Coordinator
Dec 14, 2011 at 11:00 AM

Try using cell caching, as described in the documentation

Dec 15, 2011 at 7:53 AM

Applying style for multiple cell range once with getStyle('Ax:AAy')->getAllBorders solved the problem :).

Individual applying of cell to cell with loop is tenfold memory and time consuming...

Applying for whole column like getStyle('A')->getAllBorders will result in a sure memory insufficiency.(allocated 1GB to scripts from .ini and that's eaten up too)

There's also some other possible issues....as i stumbled upon some other but related articles on the www, regarding the usage of PHP 5.3.x.

5.3.x apparently has some memory allocation issues that don't appear in 5.2.x usage. So...if possible go with 5.2.x for now. differences are in a 8~10 factor (10MB in 5.2.x  - 100MB+ 5.3.x mem usage for same script).