I need to automatically generate an Excel file, the Excel file contains from 15,000 to 50,000 rows and 75 columns.
It is obtained using joins and formulas in Excel (68 Excel formulas, there are IF, IFERROR, COUNTIF ...).
So, I chose the PHPExcel library, it works, but I need to wait between 1h15 and 1h30, I minimized the number of cycles. After reading a lot of documentation, I noticed that this is a PHPExcel problem.
If I were thinking about the possibility of creating a php array with all Excel formulas and data obtained from my database, a method that takes a lot of time and I'm not sure if it will work.
So, I ask you, is there any other way? A method of generating an Excel workbook type with a lot of data (with 1 or 2 million cells) and a formula is pretty fast (within 15 minutes).
<?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-> getActiveSheet(); $rowCount =5; $worksheetTitle = $sheet->getTitle(); $highestRow = $sheet->getHighestRow(); // eg 10 $highestColumn = $sheet->getHighestColumn(); // eg '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->prepare($requeteSQL); //Exécuter la requête $resultat = $requetePreparee->execute(); //Tester le résultat if(! $resultat) die("<p>La lecture a échoué</>\n"); else { echo "<h1>Jointure entre le $projet et la feuille de prix </h1>"; while($ligne=$requetePreparee->fetch()){ $sheet->SetCellValue('F'.$rowCount, $ligne[4]) ->SetCellValue('F'.$rowCount, $ligne[4]) $rowCount++; } $worksheetTitle = $sheet->getTitle(); $highestRow = $sheet->getHighestRow(); // eg 10 $highestColumn = $sheet->getHighestColumn(); // eg 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; for ($row = 5; $row <= $highestRow; ++ $row) { $row1=$row+1; $rowm1=$row-1; //AA4 $sheet->setCellValue( 'AA' . $row, '=..............') //AB4 ->setCellValue( 'AB' . $row,'=..............') } } echo date('H:i:s') , " Write to Excel2007 format" , PHP_EOL; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->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->getMessage() . "<br/>"; die(); } ?>