0

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

rocky
  • 329
  • 4
  • 9
  • 21
  • As you're not actually using any features of Excel at all, why not simply write a csv file – Mark Baker Oct 20 '13 at 10:31
  • oh, i think export file csv actually simple, so i want to try with file excel and i'm having problems. – rocky Oct 20 '13 at 10:40
  • PHPExcel can't create memory when there isn't sufficient, the best that it can do is reduce its memory footprint with cell caching (at a cost in execution speed). My estimate is about 1k or memory usage per cell, which doubles when you need to write the workbook. Cell caching can help reduce that 1k/cell but there may still come a time where you need to increase the memory available to PHP – Mark Baker Oct 20 '13 at 10:44

2 Answers2

3

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.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • It's really great idea, i've done. but in the future, if data increase over 100000 or 10000000 rows, what the way to solve leak memory? – rocky Oct 20 '13 at 11:08
  • You can't eliminate memory use completely, and more rows will increase the memory needed; though I'd recommend the SQLite caching which doesn't need to maintain any cell index in memory at all... but you will certainly need to increase your PHP memory limit at some point.... cell caching allows you to squeeze a quart into a pint pot, but it won't squeeze a gallon into a pint pot – Mark Baker Oct 20 '13 at 11:13
  • And if this is being generated via a web request, you'll also probably need to switch it to a background task.... the more rows, the longer it will take to the point where you might start getting timeouts if it's a web request – Mark Baker Oct 20 '13 at 11:14
2

Remember that PHP uses memory as well as the script itself, in addition to the memory used by the PHPExcel object.

If you set cache_to_phpTemp with a memoryCacheSize, the the PHPExcel object will use memory up to that limit, and then switch to using a temporary file. Your memoryCacheSize is the same as your absolute php.ini memory limit (128MB), so PHPExcel will never switch to using the temporary file cell cache before PHP's own limit is reached. Try setting a lower memoryCacheSize (e.g. 64MB) so at least PHPExcel will get a chance to cache cells and reduce its own memory usage.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • oh, i tried set momoryCacheSize lower to 8Mb or 64Mb but still error – rocky Oct 20 '13 at 10:29
  • Have you tried any of the other caching methods? With the exception of SQLite, they all maintain an "in memory index of cells", so they don't eliminate memory usage but simply reduce it. Some are more memory efficient than others, but there's normally a trade-off between memory and speed. – Mark Baker Oct 20 '13 at 10:34
  • i have not yet try with other caching methods because i don't know other caching methods. – rocky Oct 20 '13 at 10:49
  • 1
    That's where reading the documentation helps.... there's a whole section of the developer documentation that lists the different caching methods and how to use them – Mark Baker Oct 20 '13 at 10:53