PhpExcel中允许的内存大小为134217728字节 [英] Allowed memory size of 134217728 bytes exhausted in PhpExcel

问题描述

我使用phpexcel导出文件xlsx，数据具有32个列和许多行.每天数据都在增加，因此数据将非常庞大.这是我的代码:

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");

两天前，它运行良好.但是今天，它抛出了一个例外: 严重错误:在第155行的/..../Classes/PHPExcel/CachedObjectStorage/CacheBase.php中，已用完的内存大小为134217728字节(尝试分配85字节)

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

推荐答案

要注意的另一点是，您正在通过遍历名为$ data ...的数组来构建PHPExcel对象.猜测是通过遍历数据库查询结果而建立的.

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.

该$ data数组还将占用您很大一部分内存，随着结果数量的增加，每天会越来越多.

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.

如果不是遍历数据库结果集以构建一个大型数组，然后遍历该数组以构建PHPExcel数据，而是遍历数据库结果集并直接构建PHPExcel数据，则将更加高效.这样就消除了$ data的内存开销，并将2个循环减少到1.

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.

这篇关于PhpExcel中允许的内存大小为134217728字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！