php - Optimizing setCellValueExplicit() in PHPExcel -

i dealing 700 rows of data in excel.

and add on column entry:

foreach($data $k => $v){    $users ->getcell('a'.$k)->setvalue($v['username']);    $users->setcellvalueexplicit('b'.$k,        '=index(\'feed\'!h2:h'.$lastrow.',match(a'.$k.',\'feed\'!g2:g'.$lastrow.',0))',       phpexcel_cell_datatype::type_formula); } 

$users stands spreadsheet.

i see writing 700 cells above setcellvalueexplicit() takes more 2 minutes processed. if omit line takes 4 seconds same machine process it.

2 minutes can ok, if have 2000 cells. there way can speed optimized?

ps: =vlookup same slow above function.

---

update

the whole idea of script: read csv file (13 columns , @ least 100 rows), write spreadsheet, create new spreadsheet ($users), read 2 columns, sort them based 1 column , write $users spreadsheet.

read columns:

$data = array(); ($i = 1; $i <= $lastrow; $i++) {     $user = $feed ->getcell('g'.$i)->getvalue();     $number = $feed ->getcell('h'.$i)->getvalue();     $row = array('user' => $user, 'number' => $number);     array_push($data, $row); } 

sort data

function cmpb($a,$b){     //get string less or 0 if both same     if($a['number']>$b['number']){         $cmpb = -1;     }elseif($a['number']<$b['number']){         $cmpb = 1;     }else{         $cmpb = 0;     }     //if strings same, check name     if($cmpb == 0){         //compare name         $cmpb = strcasecmp($a['user'], $b['user']);     }     return $cmpb; } usort($data, 'cmpb'); 

write data

foreach($data $k => $v){    $users ->getcell('a'.$k)->setvalue($v['username']);    $users ->getcell("b{$k}")->setvalueexplicit("=index('feed'!h2:h{$lastrow},match(a{$k},'feed'!g2:g{$lastrow},0))",  phpexcel_cell_datatype::type_formula); } 

and unset data memory:

unset($data); 

so if comment line setvalueexplicit becomes smoother.

looking @ phpexcel's source code, phpexcel_worksheet::setcellvalueexplicit function:

public function setcellvalueexplicitbycolumnandrow($pcolumn = 0, $prow = 1, $pvalue = null, $pdatatype = phpexcel_cell_datatype::type_string) {     return $this->getcell(phpexcel_cell::stringfromcolumnindex($pcolumn) . $prow)->setvalueexplicit($pvalue, $pdatatype); } 

for data type you're using, phpexcel_cell_datatype::type_formula, phpexcel_cell::setvalueexplicit function executes:

case phpexcel_cell_datatype::type_formula:     $this->_value = (string)$pvalue;     break; 

i can't find logical explanation old on executing of instruction in particular. try replace following , let me know if there improvement:

$users ->getcell("b{$k}")->setvalueexplicit("=index('feed'!h2:h{$lastrow},match(a{$k},'feed'!g2:g{$lastrow},0))", phpexcel_cell_datatype::type_formula); 

as last resource advice time track execution of instruction find bottleneck.