Asked  9 Months ago    Answers:  5   Viewed   112 times

i export file xlsx using phpexcel, data have 32 cols and many rows. Each day data increase so data will very big. This is my code:

$filename="data.xlsx";
            $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
            $cacheSettings = array( 'memoryCacheSize' => '128MB');
            PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
            ini_set('max_execution_time', 123456);
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            $i = 2;
            $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Header1');
            $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Header2');
            $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Header3');
            $objPHPExcel->getActiveSheet()->setCellValue('D1', 'Header4');
            $objPHPExcel->getActiveSheet()->setCellValue('E1', 'Header5');
            $objPHPExcel->getActiveSheet()->setCellValue('F1', 'Header6');
            $objPHPExcel->getActiveSheet()->setCellValue('G1', 'Header7');
            $objPHPExcel->getActiveSheet()->setCellValue('H1', 'Header8');
            $objPHPExcel->getActiveSheet()->setCellValue('I1', 'Header9');
            $objPHPExcel->getActiveSheet()->setCellValue('J1', 'Header10');
            $objPHPExcel->getActiveSheet()->setCellValue('K1', 'Header11');
            $objPHPExcel->getActiveSheet()->setCellValue('L1', 'Header12');
            $objPHPExcel->getActiveSheet()->setCellValue('M1', 'Header13');
            $objPHPExcel->getActiveSheet()->setCellValue('N1', 'Header14');
            $objPHPExcel->getActiveSheet()->setCellValue('O1', 'Header15');
            $objPHPExcel->getActiveSheet()->setCellValue('P1', 'Header16');
            $objPHPExcel->getActiveSheet()->setCellValue('Q1', 'Header17');
            $objPHPExcel->getActiveSheet()->setCellValue('R1', 'Header18');
            $objPHPExcel->getActiveSheet()->setCellValue('S1', 'Header19');
            $objPHPExcel->getActiveSheet()->setCellValue('T1', 'Header20');
            $objPHPExcel->getActiveSheet()->setCellValue('U1', 'Header21');
            $objPHPExcel->getActiveSheet()->setCellValue('V1', 'Header22');
            $objPHPExcel->getActiveSheet()->setCellValue('W1', 'Header23');
            $objPHPExcel->getActiveSheet()->setCellValue('X1', 'Header24');
            $objPHPExcel->getActiveSheet()->setCellValue('Y1', 'Header25');
            $objPHPExcel->getActiveSheet()->setCellValue('Z1', 'Header26');
            $objPHPExcel->getActiveSheet()->setCellValue('AA1', 'Header27');
            $objPHPExcel->getActiveSheet()->setCellValue('AB1', 'Header28');
            $objPHPExcel->getActiveSheet()->setCellValue('AC1', 'Header29');
            $objPHPExcel->getActiveSheet()->setCellValue('AD1', 'Header30');
            $objPHPExcel->getActiveSheet()->setCellValue('AE1', 'Header31');
            $objPHPExcel->getActiveSheet()->setCellValue('AF1', 'Header32');
            foreach ($data as $value) {
                $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $value['1']);
                $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $value['2']);
                $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $value['3']);
                $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $value['4']);
                $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $value['5']);
                $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $value['6']);
                $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $value['7']);
                $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $value['8']);
                $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $value['9']);
                $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $value['10']);
                $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $value['11']);
                $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, $value['12']);
                $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $value['13']);
                $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, $value['14']);
                $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, $value['15']);
                $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, $value['16']);
                $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $value['17');
                $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, $value['18']);
                $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, $value['19']);
                $objPHPExcel->getActiveSheet()->setCellValue('T'.$i, $value['20']);
                $objPHPExcel->getActiveSheet()->setCellValue('U'.$i, $value['21']);
                $objPHPExcel->getActiveSheet()->setCellValue('V'.$i, $value['22']);
                $objPHPExcel->getActiveSheet()->setCellValue('W'.$i, $value['23']);
                $objPHPExcel->getActiveSheet()->setCellValue('X'.$i, $value['24']);
                $objPHPExcel->getActiveSheet()->setCellValue('Y'.$i, $value['25']);
                $objPHPExcel->getActiveSheet()->setCellValue('Z'.$i, $value['26']);
                $objPHPExcel->getActiveSheet()->setCellValue('AA'.$i, $value['27']);
                $objPHPExcel->getActiveSheet()->setCellValue('AB'.$i, $value['28']);
                $objPHPExcel->getActiveSheet()->setCellValue('AC'.$i, $value['29']);
                $objPHPExcel->getActiveSheet()->setCellValue('AD'.$i, $value['30']);
                $objPHPExcel->getActiveSheet()->setCellValue('AE'.$i, $value['31']);
                $objPHPExcel->getActiveSheet()->setCellValue('AF'.$i, $value['32']);
                $i++;
            }
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            ob_end_clean();
            header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            header("Content-Disposition: attachment; filename=".$filename);
            header('Cache-Control: max-age=0');
            $objWriter->setUseDiskCaching(true);
            $objWriter->save("php://output");

two days ago, it's run good. But today, its throw a exception: Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 85 bytes) in /..../Classes/PHPExcel/CachedObjectStorage/CacheBase.php on line 155

 Answers

32

As another point to note, you're building the PHPExcel object by looping through an array called $data.... a 2d array, that I'm guessing is built from looping through the results of a database query.

That $data array is also going to be using a large part of your memory, more and more memory each day as the number of results grows.

It would be more efficient if, instead of looping through the database resultset to build a large array and then looping through that array to build the PHPExcel data, you looped through the database resultset and built the PHPExcel data directly. That eliminates the memory overhead of $data, and reduces 2 loops to 1.

Wednesday, March 31, 2021
 
cyber_truite
answered 9 Months ago
78

Default php.ini memory_limit is 128 MB. You should either:

  • Optimize your code to use a normal amount of data
  • change memory_limit in php.ini to higher value which I do not recommend at all - with your approach you will hit this wall once again
Wednesday, March 31, 2021
 
pwaring
answered 9 Months ago
83

This means you need to also update your php.ini memory_limit directive.

Try put in your php.ini :

memory_limit=1024M and restart apache :

sudo systemctl restart httpd.service

Wednesday, March 31, 2021
 
Norgul
answered 9 Months ago
43

Here are three methods to increase the limit on shared hosting:

  1. Just add this below line to before line of you getting error in your file

     ini_set('memory_limit', '-1');
    
  2. If you have access to your PHP.ini file, change the line in PHP.ini If your line shows 32M try 64M: memory_limit = 64M ; Maximum amount of memory a script may consume (64MB)

  3. If you don't have access to PHP.ini try adding this to an .htaccess file: php_value memory_limit 64M

Also make sure your file pointer is valid, and pass "r" (= reading) for mode.

Saturday, May 29, 2021
 
THEK
answered 7 Months ago
44

I have also encounted this problem.

Increase the following variables so that your page execution will not stop:

  • max_input_time
  • memory_limit
  • max_execution_time
Thursday, July 29, 2021
 
VieStar
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share