如何用php生成一个大的Excel文件？

我必须自动生成Excel文件,Excel文件@R_33_11262@5.000到50.000行和75列.

它是使用Excel中的连接和公式获得的(68个Excel公式,有IF,IFERROR,COUNTIF …).

所以我选择了PHPExcel库,但是我必须在1h15到1h30之间等待,我已经减少了循环次数.在阅读了大量文档后,我注意到这是PHPExcel的问题.

如果我考虑创建一个包含从我的数据库中检索的所有Excel公式和数据的PHP数组的可能性,这个方法需要很长时间,而且我不确定它是否会起作用.

所以我问你,还有另外一种方法吗？一种生成Excel工作簿类型的方法,该类型包含大量数据(@R_33_11262@或2百万个单元格)和公式(在15分钟内).

<?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(); // e.g. 10
$highestcolumn = $sheet->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->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(); // e.g. 10
$highestcolumn = $sheet->getHighestcolumn(); // e.g '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',__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();
    }

    ?>