CODEWITHSUNDEEP
phpperformanceoptimizationphpexcel
Diolor
Diolor

Reputation: 13450

Optimizing setCellValueExplicit() in PHPExcel

I am dealing with 700 rows of data in my excel.

And I add on a column this entry:

foreach($data as $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 for a spreadsheet.

I see that writing 700 cells with the above setCellValueExplicit() takes more than 2 minutes to get processed. If I omit that line it takes 4 seconds for the same machine to process it.

2 minutes can be ok, but what if I have 2000 cells. Is there any way that can be speed optimized?

ps: =VLOOKUP is the same slow as the above function.

Update

The whole idea of the script: read a CSV file (13 columns and at least 100 rows), write it into a spreadsheet, create a new spreadsheet ($users), read two columns, sort them based to one column and write it to the $users spreadsheet.

Read the columns:

$data = array();
for ($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 the data

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

Write data

foreach($data as $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 also unset the data for memory:

unset($data);

So if comment the line with setValueExplicit everything becomes smoother.

Upvotes: 1

Views: 19762

Answers (1)

Rolando Isidoro
Rolando Isidoro

Reputation: 5114

Looking at PHPExcel's source code, this is 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 the data type you're using, PHPExcel_Cell_DataType::TYPE_FORMULA, the PHPExcel_Cell::setValueExplicit function just executes:

case PHPExcel_Cell_DataType::TYPE_FORMULA:
    $this->_value = (string)$pValue;
    break;

I can't find a logical explanation for the old up on the execution of that particular instruction. Try to replace it for the following and let me know if there was any 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 a last resource my advice would be to time track the execution of the instruction to find the bottleneck.

Upvotes: 3

Related Questions