Taking long time and through error

Topics: Developer Forum
May 27, 2014 at 7:11 AM
I have 60,000 rows to write in an excel each one with 11 columns and it takes so long time and through the error message "The server at localhost is taking too long to respond".

Here is my code
sec_session_start();
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
ini_set('max_execution_time', 680);
ini_set('memory_limit', '-1');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once dirname(__FILE__) . '\Classes\PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                             ->setLastModifiedBy("Maarten Balliauw")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");


// Create a first sheet
//echo date('H:i:s') , " Add data" , EOL;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "column1");
$objPHPExcel->getActiveSheet()->setCellValue('B1', "column2");
$objPHPExcel->getActiveSheet()->setCellValue('C1', "column3");
$objPHPExcel->getActiveSheet()->setCellValue('D1', "column4");
$objPHPExcel->getActiveSheet()->setCellValue('E1', "Test1");
$objPHPExcel->getActiveSheet()->setCellValue('F1', "test2");
$objPHPExcel->getActiveSheet()->setCellValue('G1', "Customer Name");
$objPHPExcel->getActiveSheet()->setCellValue('H1', "Product Group");
$objPHPExcel->getActiveSheet()->setCellValue('I1', "Product Segment");
$objPHPExcel->getActiveSheet()->setCellValue('J1', "Product Type");
$objPHPExcel->getActiveSheet()->setCellValue('K1', "Product Code");
$objPHPExcel->getActiveSheet()->setCellValue('L1', "Product Description");
$objPHPExcel->getActiveSheet()->setCellValue('M1', "Sales Voucher Type");
$objPHPExcel->getActiveSheet()->setCellValue('N1', "Quantity");
$objPHPExcel->getActiveSheet()->setCellValue('O1', "Net Amount");
$objPHPExcel->getActiveSheet()->setCellValue('P1', "Tax Amount");
$objPHPExcel->getActiveSheet()->setCellValue('Q1', "Gross Amount");

// Freeze panes
$objPHPExcel->getActiveSheet()->freezePane('A2');


// Rows to repeat at top
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);


// Add data
$url = 'http://localhost:8090/solr/salesreport/select?q='.$_SESSION['form_controls'].'&wt=xml&start=0&rows=200000&indent=true';
$xml = simplexml_load_file($url);
//$test = $xml->result[numFound];
$i=2;
$j=1;
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
foreach($xml->result->children() as $VOUCHER)
 {
    
    $salesdate = date('d-m-Y',strtotime($VOUCHER->str[4])) ;
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $VOUCHER->str[8])
                                  ->setCellValue('B' . $i, $VOUCHER->str[16])
                                  ->setCellValue('C' . $i, $VOUCHER->str[17])
                                  ->setCellValue('D' . $i, $VOUCHER->str[18])
                                  ->setCellValue('E' . $i, $VOUCHER->str[11])
                                  ->setCellValue('F' . $i, $salesdate)
                                  ->setCellValue('G' . $i, $VOUCHER->str[5])
                                  ->setCellValue('H' . $i, $VOUCHER->str[10])
                                  ->setCellValue('I' . $i, $VOUCHER->str[3])
                                  ->setCellValue('J' . $i, $VOUCHER->str[15])
                                  ->setCellValue('K' . $i, $VOUCHER->str[0])
                                  ->setCellValue('L' . $i, $VOUCHER->str[1])
                                  ->setCellValue('M' . $i, $VOUCHER->str[14])
                                  ->setCellValue('N' . $i, $VOUCHER->str[13])
                                  ->setCellValue('O' . $i, $VOUCHER->str[6])
                                  ->setCellValue('P' . $i, $VOUCHER->str[2])
                                  ->setCellValue('Q' . $i, $VOUCHER->str[12]);
                                  $i++;
}

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Salesreport.xlsx"');
header('Cache-Control: max-age=0');
If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
Is there any chance to improve my code and get the excel export for huge records and make it faster??