尝试从mysql数据库中的记录创建excel文件时PHPExcel内存耗尽。 最多需要复制20000条记录

Question

I have to export mysql database to an excel file. The number of records are very large (about 20000) when I was exporting the database, the server reported this fatal error:

failed to allocated 68 bytes.

Every time I run the script, the 68 bytes value changes to some new value like 33 bytes. My script is:

set_time_limit(0);


error_reporting(E_ALL);
ini_set('display_errors', '1');

ini_set("memory_limit", "1000M");

require_once("php_excel/PHPExcel.php");
require_once("php_excel/PHPExcel/IOFactory.php");
require_once("includes/config.php");
require_once("includes/functions/functions.php");






// Get The Field Name of equipments
$objPHPExcel = new PHPExcel();
$objPHPExcel->createSheet();



$objPHPExcel->setActiveSheetIndex(0);
//code to display headers
$activesheet=$objPHPExcel->getActiveSheet();

$result=exec_query("s...... ");

$num_fields = mysql_num_fields($result);
$X='A';
$columns=array();

$p=0;
for($i=0;$i<$num_fields;$i++)
{

$columns[]=mysql_field_name($result, $i);
$activesheet->setCellValue($X.'1', $columns[$p]);
$X++;$p++;
}

//second time....


$ts_id="";

$result=exec_query("........ ");

while($row=fetch_array($result))

{


if($ts_id!=$row['ts_id'])
{
  $columns[]=$row['ts_id'];
  $activesheet->setCellValue($X.'1', utf8_encode(explode(' ',$row['attribute_desc'])[0]).' Game');
  $X++;$p++;
  $ts_id=$row['ts_id'];
 }



$columns[]=$row['attribute_desc'];

$activesheet->setCellValue($X.'1', $columns[$p]);
$X++;$p++;

$columns[]=$row['attribute_desc'];

$activesheet->setCellValue($X.'1', $columns[$p].' ok');
$X++;$p++;



}





//end of second....

$X='A';
$k=2;

//getting attribute ids 


$result=exec_query("............s ");
$tsid=array();
$attribute=array();

    while($row=fetch_array($result))
    {
       $attribute[]=$row['....._id'];
       $tsid[]=$row['ts_id'];
    }


//getting attribute ids end...



//filling values...

$result=exec_query("................");



$majorwordid=array();

    while($row=fetch_array($result))
    {

      $majorwordid[]=$row['word_id'];

        for($i=0;$i<$num_fields;$i++)
        {


         //echo $row[$columns[$i]]."<br>";
              $activesheet->setCellValue($X.$k,  utf8_encode($row[$columns[$i]]));

            $X++;
        }
        $X='A';
        $k++;
    }


    //audio number


$result=exec_query("...................");


$X='C';

$k=2;

    while($row=fetch_array($result))
    {


            $activesheet->setCellValue($X.$k,  utf8_encode($row['audio number']));

              $k++;
    }
    //audio number ends...


    //mait codesss


$X++;

  $forid=''; 



    for($a=0;$a<count($attribute);$a++)
    {
     $k=2;  

    if($forid!=$tsid[$a] ||$forid=='')
    {

            $result=exec_query(".............");
            $word_ids=array();

            while($row=fetch_array($result))
                {
                    $word_ids[]=$row['word_id'];
                }



                for($i=0;$i<count($majorwordid);$i++)
                {

                    if (in_array($majorwordid[$i], $word_ids)) {

                         $activesheet->setCellValue($X.$k,'1');
                    }

                    else
                     $activesheet->setCellValue($X.$k,' ');

                   $k++;
                }



      $forid=$tsid[$a];
      $X++;
    }

     $k=2;  

            $result=exec_query("...............");
            $word_ids=array();

            while($row=fetch_array($result))
                {
                    $word_ids[]=$row['word_id'];
                }



                for($i=0;$i<count($majorwordid);$i++)
                {
                    if (in_array($majorwordid[$i], $word_ids)) {

                         $activesheet->setCellValue($X.$k,'1');
                    }

                    else
                     $activesheet->setCellValue($X.$k,' ');

                   $k++;
                }


// for ok valuess....

                $k=2;
                $X++;   

$result=exec_query("S............");
            $word_ids=array();

            while($row=fetch_array($result))
                {
                    $word_ids[]=$row['word_id'];
                }

                for($i=0;$i<count($majorwordid);$i++)
                {

                    if (in_array($majorwordid[$i], $word_ids)) {

                         $activesheet->setCellValue($X.$k,'1');
                    }

                    else
                     $activesheet->setCellValue($X.$k,' ');

                   $k++;
                }


                $X++;



    }





$activesheet->setTitle('LexicoCMS');
$activesheet->getColumnDimension('S')->setWidth(140);


//second page...

$objPHPExcel->setActiveSheetIndex(1);
$activesheet1=$objPHPExcel->getActiveSheet();
$query="........";

$result=exec_query($query);

$num_fields = mysql_num_fields($result);

$X='A';
$columns=array();
$i=0;

for($i=0;$i<$num_fields;$i++)
{
$columns[]=mysql_field_name($result, $i);
$activesheet1->setCellValue($X.'1', utf8_encode($columns[$i]));
$X++;
}




$result=exec_query($query);

$num_fields = mysql_num_fields($result);

$X='A';
$k=2;

    while($row=fetch_array($result))
    {

        for($i=0;$i<$num_fields;$i++)
        {


            $activesheet1->setCellValue($X.$k,  utf8_encode($row[$columns[$i]]));

            $X++;
        }
        $X='A';
        $k++;
    }

$activesheet1->setTitle('Other info');
$activesheet1->getColumnDimension('S')->setWidth(140);


//end of second page

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Lexico_cms.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

Answer 1

The issue is that you're keeping a large Excel object in memory, and your script just runs out of usable memory.

First you should try to determine if there are ways to reduce the memory consumption of your script, but if that fails you need to increase your PHP memory limit. In your php.ini file, locate the memory_limit setting that determines the maximum amount of memory a script may consume, e.g.

memory_limit = 64M;

Increase the value until you no longer see the error.