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

我必须自动生成Excel文件,Excel文件包含15.000到50.000行和75列.

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

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

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

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

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("

La lecture a échoué>\n");

else {

echo "

Jointure entre le $projet et la feuille de prix

";

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', '.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() . "
";

die();

}

?>