对于大量记录导出不能更快地工作..当我导出时超时错误下面是我正在使用的代码

Question

<?php 

require_once('../../config.php');


$MySQL_host     = $CFG->dbhost;
$MySQL_username = $CFG->dbuser;
$MySQL_password = $CFG->dbpass;
$MySQL_database = $CFG->dbname;
require_login();

$queries=$_POST['export'];



$Connect = @mysql_connect($MySQL_host, $MySQL_username, $MySQL_password) 
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); 
//select database 
$Db = @mysql_select_db($MySQL_database, $Connect) 
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 
//execute query 
$result = @mysql_query($queries,$Connect) 
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); 
error_reporting(E_ALL);

 require_once 'PHPExcel.php';
 $objPHPExcel = new PHPExcel();

 // Set the active Excel worksheet to sheet 0 

$objPHPExcel->setActiveSheetIndex(0);  

// Initialise the Excel row number 

$styleArray = array(
  'borders' => array(
    'allborders' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN
    )
  )
);


$current_date = date("d/m/y");
$filename = "Individual Employee Completion " . $current_date ;

$objPHPExcel->getActiveSheet()->SetCellValue('A2', "Individual Employee Completion");
//$objPHPExcel->getActiveSheet()->SetCellValue('A2', date('d-m-y'));
$objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFont()->setBold(true)->getColor()->setRGB('F0FFFF');
$objPHPExcel->getActiveSheet()->getStyle('A2:F2')->getFont()->setBold(true)->getColor()->setRGB('F0FFFF');
$objPHPExcel->getActiveSheet()->mergecells('A2:F2');
$objPHPExcel->getActiveSheet()->getStyle('A2:F2')->getAlignment('A2:F2')->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:F2')->getAlignment('A2:F2')->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);








function cellColor($cells,$color){
        global $objPHPExcel;
        $objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()
        ->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array('rgb' => $color)
        ));
    }
   
  cellColor('A3:F3', '000000');
    cellColor('A2:F2', '9966CC');

    
    
$rowCount = 3;  


//start of printing column names as names of MySQL fields  

 $column = 'A';

for ($i = 0; $i < mysql_num_fields($result); $i++)  

{
    $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, mysql_field_name($result,$i));
    $column++;
}

//end of adding column names  
//start while loop to get data  

$rowCount = 4;  

while($row = mysql_fetch_row($result))  

{  
    $column = 'A';

   for($j=0; $j<mysql_num_fields($result);$j++)  
    {  
        if(!isset($row[$j]))  

            $value = NULL;  

        elseif ($row[$j] != "")  

            $value = strip_tags($row[$j]);  

        else  

            $value = "";  


        $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value);
        $column++;
    }  

    $rowCount++;
    $objPHPExcel->getActiveSheet()->getStyle(
    'A3:' . 
    $objPHPExcel->getActiveSheet()->getHighestColumn() . 
    $objPHPExcel->getActiveSheet()->getHighestRow()
)->applyFromArray($styleArray);

} 

// Redirect output to a client’s web browser (Excel5) 
header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="'.$filename.'".xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');
?>

any of your help is greatly appreciated. I have tried to do an export of huge (more than 10,000) records. While i am trying to export it starts hanging and at last it is showing timed out error. Please help me to sort out this issue. thanks!

</div>

Answer 1

Standard comments apply:

Setting

$worksheet as $objPHPExcel->getActiveSheet();

outside the loop, then using

$worksheet->setCellValue($column.$rowCount, $value);

is more efficient because it saves a call to $objPHPExcel->getActiveSheet() every iteration

---

If a cell is empty, then don't bother setting a value for that cell. This saves the performance overhead of actually creating a cell in PHPExcel, saves memory, and reduces the overall file size of the generated file

---

Move

$objPHPExcel->getActiveSheet()->getStyle(
'A3:' . 
$objPHPExcel->getActiveSheet()->getHighestColumn() . 
$objPHPExcel->getActiveSheet()->getHighestRow()
)->applyFromArray($styleArray);

to after you've closed your row loop, because you're executing it redundantly every single row

---

Try to avoid using autosize on columns

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);

if possible

It's a very expensive operation, because PHPExcel has to calculate the actual size to use for each autosized column on save by looping over every cell in that column working out what the max size should be. It's a lot faster if you can work with fixed-width columns, where no calculation is necessary