I'm using PHPExcel to create excel files using php. First take look at below code, the problem with this code is, After saving some amount of data into excel it only remains in process but doesn't saves anything. I think the script saves any data to catch and temporary values and this is causing the script to take more and more loading after each iteration. Explanation:
First of all this code retrieves some integer values from one excel file one by one viz. sample.xls (this files contains values in only column A.). suppose it has retrieved first value from cell A1 is 1212, then the code sets $target=1212, after the curl functions retrieves data for 1212 and saves as html in results folder as 1212.html. After the dom library starts their work. the 1212.html file contains table with three columns and so many rows. so dom grabs data for td and tr and saves respective values in excel cell and finally it saves data into excelresult folder as 1212.xlsx, and again the same process taken place for cell A2 in sample.xls, retrives some values like 1213, and starts grabbing and so on.
Problem:
Here it takes little time for first value like 1212, then takes little more time for second value 1213, and little more and after four or five values it takes so long (lots of minutes) time for execution, Please help me decrease this time, and make this process faster. Thanks.
code:
<?php
......
ini_set('include_path', ini_get('include_path').';../Classes/');
include_once 'PHPExcel.php';
include_once 'Excel2007.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->....//set some properties//
$excel->read('sample.xls'); // added excel reader from which we need to take some values
$x=1;
while($x<=$excel->sheets[0]['numRows']) { // reading row by row
$y=1;
while($y<=$excel->sheets[0]['numCols']) {// reading column by column
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
$target = $cell;
// $objWorksheet = $objPHPExcel->getActiveSheet();
// $highestRow = $objWorksheet->getHighestRow();
// for($row=1; $row < $highestRow; ++$row){
// $objPHPExcel->getActiveSheet()->removeRow($row,$row);
// }
/* some lines of code using curl to fetch data for $target value
........... */
//below is the code which retrives data from html table and saves into excel file.
$url='results/'.$target.'.html';
include_once('dom.php');
$html=file_get_html($url);
$record_find='first';
foreach($html->find('table#GridView1') as $e){
if($record_find=='first')
$i=1;
$j=0;
foreach($e->find('tr') as $e1){
$distno=trim($e1->find('td', 0)->innertext);
$acno=trim($e1->find('td', 1)->innertext);
$partno=trim($e1->find('td', 2)->innertext);
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$j, $distno);
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$j, $acno);
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$j, $partno);
$j++;
}
}
$objPHPExcel->getActiveSheet()->setTitle($target);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('excelresult/'.$target.'.xlsx');
$y++;
}
$x++;
}
?>
Curl:
$debug = 1;
$url = "url";
$f = fopen('log.txt', 'w');
$cookies = 'cookies.txt';
touch($cookies);
$useragent = 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_COOKIEJAR, $cookies);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_USERAGENT, $useragent);
$html = curl_exec($ch);
curl_close($ch);
preg_match('~<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="(.*?)" />~', $html, $viewstate);
preg_match('~<input type="hidden" name="__EVENTVALIDATION" id="__EVENTVALIDATION" value="(.*?)" />~', $html, $eventValidation);
$viewstate = $viewstate[1];
$eventValidation = $eventValidation[1];
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
//curl_setopt($ch, CURLOPT_HEADER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_COOKIEJAR, $cookies);
curl_setopt($ch, CURLOPT_COOKIEFILE, $cookies);
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt($ch, CURLOPT_STDERR, $f);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 0);
curl_setopt($ch, CURLOPT_TIMEOUT, 985000);
curl_setopt($ch, CURLOPT_USERAGENT, $useragent);
// Collecting all POST fields
$postfields = array();
$postfields['__EVENTTARGET'] = "";
$postfields['__EVENTARGUMENT'] = "";
$postfields['__LASTFOCUS'] = "";
$postfields['__VIEWSTATE'] = $viewstate;
$postfields['__EVENTVALIDATION'] = $eventValidation;
$postfields['cns_fer'] = 2;
$postfields['xttPd'] = $target;
$postfields['tsfDes'] = "Search";
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postfields);
$ret = curl_exec($ch);
curl_close($ch);
file_put_contents('results/'.$target.'.html', $ret);