i'm trying export records excel mysql (webserver) , when query returns >4k records script hangs web browser , temporaly web hosting.
my php_version 5.2.13-pl1-gentoo , memory_limit configurated in php.ini 128m
the result excel have 1 column , n rows. 100 or 200 rows php script runs fine.
this php script
<? session_start(); ini_set('memory_limit', '1024m'); set_time_limit(0); include("include/conexion.php"); require_once 'include/phpexcel/classes/phpexcel.php'; require_once 'include/phpexcel/classes/phpexcel/iofactory.php'; $objphpexcel = new phpexcel(); $objphpexcel->getproperties()->setcreator("name") ->setlastmodifiedby("name") ->settitle("listado") ->setsubject("listado") ->setdescription("listado.") ->setkeywords("listado") ->setcategory("listado"); $query = explode("|",stripcslashes($_post['query'])); $objphpexcel->getactivesheet()->settitle('list'); $resemp = mysql_query ($query, $conexion ) or die(mysql_error()); $tot = mysql_num_rows($resemp); $num_fields = mysql_num_fields($resemp); $fistindex = $objphpexcel->getactivesheet()->getcellbycolumnandrow(0, 1)->getcolumn(); $lastindex = $objphpexcel->getactivesheet()->getcellbycolumnandrow($num_campos - 1, 1)->getcolumn(); //tittles ($e=0;$e < $num_fields;$e++){ $objphpexcel->getactivesheet()->setcellvaluebycolumnandrow($e, 2, utf8_decode(ucwords(mysql_field_name($resemp,$e)))); $objphpexcel->getactivesheet()->getcolumndimension($objphpexcel->getactivesheet()->getcellbycolumnandrow($e, 2)->getcolumn())->setautosize(true); } //color tittles $objphpexcel->getactivesheet()->getstyle( $fistindex.'1:'.$lastindex.'2' )->getfill()->setfilltype(phpexcel_style_fill::fill_solid)->getstartcolor()->setrgb('c5c5c7'); $objphpexcel->getactivesheet()->getstyle( $fistindex.'1:'.$lastindex.'2' )->getfont()->setbold(true); if(isset ( $_post ['mail'] )){ $objphpexcel->getactivesheet()->setcellvaluebycolumnandrow(0, 2, "email"); $emails = array(); ($row = 0; $row < $totemp; $row++) { //more 1 mail in field separated ";" $aux = explode(";", mysql_result($resemp,$row,$col)); for($i=0; $i<count($aux); $i++){ $cleaned = utf8_encode(strtolower(trim($aux[$i]))); //filter repeated mails if(!in_array($cleaned, $emails) && $aux[$i] != ""){ $num_rows = $objphpexcel->getactivesheet()->gethighestrow(); $objphpexcel->getactivesheet()->insertnewrowbefore($num_rows + 1, 1); array_push($emails, $cleaned); $objphpexcel->getactivesheet()->setcellvaluebycolumnandrow(0, $num_rows + 1, $cleaned); } } } } $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel2007'); header('content-type: application/vnd.ms-excel'); header("content-disposition: attachment; filename=".$nom_archivo.".xlsx"); // write file browser $objwriter->save('php://output'); exit(); ?> when enter script run mysql query , then, iterate result mail field, if obtained mail not exist in array mail inserted in excel
i've tried set
ini_set('memory_limit', '1024m'); set_time_limit(0); but problem persist.
any idea solve problem?
thanks lot
edit 1
i've updated code recommendations , works fine. anyway how can if occurs error or memory usage before of hanging? how can max memory_limit available set ini_set('memory_limit', '2048m'); ?
<? session_start(); ini_set('memory_limit', '2048m'); set_time_limit(0); include("include/conexion.php"); require_once 'include/phpexcel/classes/phpexcel.php'; require_once 'include/phpexcel/classes/phpexcel/iofactory.php'; $objphpexcel = new phpexcel(); $objphpexcel->getproperties()->setcreator("name") ->setlastmodifiedby("name") ->settitle("listado") ->setsubject("listado") ->setdescription("listado.") ->setkeywords("listado") ->setcategory("listado"); $activesheet = $objphpexcel->getactivesheet(); $query = explode("|",stripcslashes($_post['query'])); $activesheet->settitle('list'); $resemp = mysql_query ($query, $conexion ) or die(mysql_error()); $tot = mysql_num_rows($resemp); $num_fields = mysql_num_fields($resemp); $fistindex = $activesheet->getcellbycolumnandrow(0, 1)->getcolumn(); $lastindex = $activesheet->getcellbycolumnandrow($num_campos - 1, 1)->getcolumn(); //tittles ($e=0;$e < $num_fields;$e++){ $activesheet->setcellvaluebycolumnandrow($e, 2, utf8_decode(ucwords(mysql_field_name($resemp,$e)))); $activesheet->getcolumndimension($activesheet->getcellbycolumnandrow($e, 2)->getcolumn())->setautosize(true); } //color tittles $activesheet->getstyle( $fistindex.'1:'.$lastindex.'2' )->getfill()->setfilltype(phpexcel_style_fill::fill_solid)->getstartcolor()->setrgb('c5c5c7'); $activesheet->getstyle( $fistindex.'1:'.$lastindex.'2' )->getfont()->setbold(true); if(isset ( $_post ['mail'] )){ $activesheet->setcellvaluebycolumnandrow(0, 2, "email"); $emails = array(); ($row = 0; $row < $totemp; $row++) { //more 1 mail in field separated ";" $aux = explode(";", mysql_result($resemp,$row,$col)); for($i=0; $i<count($aux); $i++){ $cleaned = utf8_encode(strtolower(trim($aux[$i]))); //filter repeated mails if(!in_array($cleaned, $emails) && $aux[$i] != ""){ array_push($emails, $cleaned); } } } ($row = 0; $row < count($emails); $row++) { $activesheet->setcellvaluebycolumnandrow(0, $row + 3, $emails[$row]); } } $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel2007'); header('content-type: application/vnd.ms-excel'); header("content-disposition: attachment; filename=".$nom_archivo.".xlsx"); // write file browser $objwriter->save('php://output'); exit(); ?>
seems library has serious problem in parsing large excel spreadsheets, i'd issue & couldn't find proper solution. guess normal behaviour because library written in php causes lot of parsing overhead.
suggest use excel parsing php-extension this one.
thinkable solution [if possible], can break down big file several smaller files (e.g sheets), otherwise guess should use faster cpu or use library or programming language parse exel files (e.g. apache-poi in java, maybe php/java bridge).
Comments
Post a Comment