I have to generate automatically Excel file, the Excel file contains 15.000 to 50.000 rows and 75 columns.
It is obtained using a join and formulas in Excel (68 Excel formulas, there are IF, IFERROR, COUNTIF ...).
So I opted for the library PHPExcel
, 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.
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.
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).
<?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', '.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();
}
?>
Spreadsheet creation is a very common use case in PHP development. It is used to export data to an Excel spreadsheet. Below is the code for creating an excel spreadsheet using the PHP Excel library.
Export Data from Database:$fileName – Define the name of the excel file to be downloaded. $fields – Define the column named of the excel sheet. $excelData – Add the first row to the excel sheet as a column name. Fetch member's data from the database and add to the row of the excel sheet.
Try to use https://github.com/aVadim483/fast-excel-writer It's very fast XLSX generator: 2,000 rows (6,000 cells) - 0.198 sec 2,000,000 rows (6,000,000 cells) - 17.049 sec
It seems CNUM() is the French name for the function, so you need set French locale in this library
$sheetData = [];
// fill data
for ($row = 1; $row <= 200000; $row++) {
$rowData = [];
for ($col = 0; $col < 3; $col++) {
$rowData[] = '=IF(OR(CNUM(N' . $row . ')=1,CNUM(N' . $row . ')=2),0,1+CNUM(M' . $row . '))';
}
$sheetData[] = $rowData;
}
$excel = \avadim\FastExcelWriter\Excel::create();
$excel->setLocale('fr');
$sheet = $excel->getSheet();
$timer = microtime(true);
foreach($sheetData as $rowData) {
$sheet->writeRow($rowData);
}
$excel->save('simple.xlsx');
echo 'elapsed time: ', round(microtime(true) - $timer, 3), ' sec';
Use BoxSpout.
It is a PHP library to read and write CSV and XLSX files, in a fast and scalable way. Contrary to other file readers or writers, it is capable of processing very large files while keeping the memory usage really low (less than 10MB). Here are a few numbers regarding the performance of Spout.
https://github.com/box/spout
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With