title: Optimizing setCellValueExplicit() in PHPExcel
body: <p>I am dealing with 700 rows of data in my excel.</p>
  
  <p>And I add on a column this entry:</p>
  
  <pre><code>foreach($data as $k =&gt; $v){
     $users -&gt;getCell('A'.$k)-&gt;setValue($v['Username']);
     $users-&gt;setCellValueExplicit('B'.$k, 
        '=INDEX(\'Feed\'!H2:H'.$lastRow.',MATCH(A'.$k.',\'Feed\'!G2:G'.$lastRow.',0))',
        PHPExcel_Cell_DataType::TYPE_FORMULA);
  }
  </code></pre>
  
  <p><code>$users</code> stands for a spreadsheet.</p>
  
  <p>I see that writing 700 cells with the above <code>setCellValueExplicit()</code> takes more than 2 minutes to get processed. If I omit that line it takes 4 seconds for the same machine to process it.</p>
  
  <p>2 minutes can be ok, but what if I have 2000 cells. Is there any way that can be speed optimized?</p>
  
  <p>ps: <code>=VLOOKUP</code> is the same slow as the above function.</p>
  
  <hr>
  
  <p><strong>Update</strong></p>
  
  <p>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 (<code>$users</code>), read two columns, sort them based to one column and write it to the <code>$users</code> spreadsheet.</p>
  
  <p>Read the columns:</p>
  
  <pre><code>$data = array();
  for ($i = 1; $i &lt;= $lastRow; $i++) {
      $user = $Feed -&gt;getCell('G'.$i)-&gt;getValue();
      $number = $Feed -&gt;getCell('H'.$i)-&gt;getValue();
      $row = array('User' =&gt; $user, 'Number' =&gt; $number);
      array_push($data, $row);
  }
  </code></pre>
  
  <p>Sort the data</p>
  
  <pre><code>function cmpb($a,$b){
      //get which string is less or 0 if both are the same
      if($a['Number']&gt;$b['Number']){
          $cmpb = -1;
      }elseif($a['Number']&lt;$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');
  </code></pre>
  
  <p>Write data</p>
  
  <pre><code>foreach($data as $k =&gt; $v){
     $users -&gt;getCell('A'.$k)-&gt;setValue($v['Username']);
     $users -&gt;getCell("B{$k}")-&gt;setValueExplicit("=INDEX('Feed'!H2:H{$lastRow},MATCH(A{$k},'Feed'!G2:G{$lastRow},0))",
   PHPExcel_Cell_DataType::TYPE_FORMULA);
  }
  </code></pre>
  
  <p>and also unset the data for memory:</p>
  
  <pre><code>unset($data);
  </code></pre>
  
  <p>So if comment the line with <code>setValueExplicit</code> everything becomes smoother.</p>