How to generate a big Excel file with php?

Question

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();
    }

    ?>

Answer 1

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

Answer 2

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';