title: How to generate a big Excel file with php?
body: <p>I have to generate automatically Excel file, the Excel file contains 15.000 to 50.000 rows and 75 columns.</p>
  
  <p>It is obtained using a join and formulas in Excel (68 Excel formulas, there are IF, IFERROR, COUNTIF ...).</p>
  
  <p>So I opted for the library <code>PHPExcel</code>, it works but I have to wait between 1h15 to 1h30,I have minimized the number of loops. After reading a lot of documentation, I noticed that this is the problem of PHPExcel.</p>
  
  <p>If I thought about the possibility of creating a php array with all Excel formulas and data retrieved from my database, a method that takes a long time and I'm not sure it will work.</p>
  
  <p>So I ask you, is there another way? A method for generating an Excel workbook type with a lot of data (with 1 or 2 million cells) and formulas rather quickly (within 15 minutes).</p>
  
  <pre><code>&lt;?php       
  require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
  require_once dirname(__FILE__) .  '/Classes/PHPExcel/IOFactory.php';
  
  $path = "Lirefichierexcel/Trame.xlsx";
  
  $objPHPExcel = new PHPExcel(); 
  $sheet = $objPHPExcel-&gt; getActiveSheet();
  
  $rowCount =5;
  
  $worksheetTitle = $sheet-&gt;getTitle();
  $highestRow = $sheet-&gt;getHighestRow(); // e.g. 10
  $highestColumn = $sheet-&gt;getHighestColumn(); // e.g 'F'
  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
  $nrColumns = ord($highestColumn) - 64;
  
  $rowCount=5;
  
     $projet=$_REQUEST['projet'];
       try {
         //Etablir la connexxion
         include 'Proprietes.php';
  
         $connexion = new PDO("$driver:host=$host;dbname=$dbase", $user, $password);
  
         //Préparer la requête
         $requeteSQL="select * from $projet as a left join feuille_de_prix as b 
         on b.Liasse = a.Liasse and b.Item = a.Item order by 1";
          $requetePreparee= $connexion-&gt;prepare($requeteSQL);
  
         //Exécuter la requête
       $resultat = $requetePreparee-&gt;execute();
  
       //Tester le résultat
       if(! $resultat) die("&lt;p&gt;La lecture a échoué&lt;/&gt;\n");
      else {
  
     echo "&lt;h1&gt;Jointure entre le $projet et la feuille de prix &lt;/h1&gt;";
  
         while($ligne=$requetePreparee-&gt;fetch()){
  
      $sheet-&gt;SetCellValue('F'.$rowCount, $ligne[4])
      -&gt;SetCellValue('F'.$rowCount, $ligne[4])    
  
     $rowCount++;
  
      } 
  
         $worksheetTitle = $sheet-&gt;getTitle();
  $highestRow = $sheet-&gt;getHighestRow(); // e.g. 10
  $highestColumn = $sheet-&gt;getHighestColumn(); // e.g 'F'
  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
  $nrColumns = ord($highestColumn) - 64;
  
        for ($row = 5; $row &lt;= $highestRow; ++ $row) {
      $row1=$row+1;
      $rowm1=$row-1;
  
         //AA4
      $sheet-&gt;setCellValue(
              'AA' . $row, '=..............')
  
  //AB4
          -&gt;setCellValue(
              'AB' . $row,'=..............')
  
  }
  
  }
  
  echo date('H:i:s') , " Write to Excel2007 format" , PHP_EOL;
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $objWriter-&gt;save(str_replace('.php', '.xlsx', __FILE__));
  echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', __FILE__) , PHP_EOL;
  // Echo memory peak usage
  echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , PHP_EOL;
  
  // Echo done
  echo date('H:i:s') , " Done writing file" , PHP_EOL;
  
       $connexion=null;
  
     }catch (PDOException $e) {
       print "Erreur !: " . $e-&gt;getMessage() . "&lt;br/&gt;";
       die();
      }
  
      ?&gt;
  </code></pre>