CODEWITHSUNDEEP
phpexportphpexcel
Ajit R
Ajit R

Reputation: 3

Export not working faster for huge records.. timed out error when im exporting Below is the code i am using

<?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!

Upvotes: 0

Views: 117

Answers (2)

Mark Baker
Mark Baker

Reputation: 212522

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

Upvotes: 0

besciualex
besciualex

Reputation: 1892

Please add the following line on the second line of your code:

set_time_limit(0);

It has the role of disabling the timeout on your script. ;)

Upvotes: 1

Related Questions