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

    ?>

That really depends on your code, and you are generating a large spreadsheet.... PHPExcel isn't fast, but there are generally ways to code your script that will improve overall performance..... without seeing your code, it's difficult to say how; but based on your description.... don't build large arrays from your database first, and then write the array to PHPExcel.... write to PHPExcel as you read from the database, and that will give you a big performance improvement — Mark Baker, Jun 16 '15 at 08:34
Thank you so much for you answer :) I searched a long time to minimize the time and I have found nothing ...I stick my code in the first message. — Krokodile, Jun 16 '15 at 08:55

score 30 · Accepted Answer · answered Jun 16 '15 at 08:33

30

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.

box spout reading and writing speeed

https://github.com/box/spout

answered Jun 16 '15 at 08:33

Faiz Rasool

1,379
1
12
20

Thank you Faiz Rasool for you answer :P I tested Spout but I can't fine documentation about the formulas, how to write this formula ? $sheet ->setCellValue('M'$row,'=IF(OR(CNUM(N'.$row.')=1,CNUM(N'.$row.')=2),0,1+CNUM(M'.$row.'))'); – Krokodile Jun 16 '15 at 08:58
@Krokodile why you don't do calculation in the PHP and paste the value in the cell? – Faiz Rasool Jun 16 '15 at 09:03
This is the technique best ? – Krokodile Jun 16 '15 at 09:05
1

@Krokodile i think so because you are using native php function in the PHP. The excel function in PHP should be derived function and would be slower than native one. – Faiz Rasool Jun 16 '15 at 09:07
1

@Krokodile accept the answer if it does help you :) so it would be useful for other to. – Faiz Rasool Jun 16 '15 at 09:11
Spout does not support formulas. So +1 to what @FaizRasool suggested! – Adrien Jun 27 '15 at 17:11
@Adrien why you don't do calculation in the PHP and paste the value in the cell? – Faiz Rasool Sep 15 '16 at 11:30
Because Spout only holds one row at a time in memory. So if you have a formula referencing a cell that is not on the same row, it won't work. To fully support formulas, you need to somehow hold the entire spreadsheet in memory, which then leads to out of memory exceptions. Tradeoff... – Adrien Sep 15 '16 at 20:34

score 3 · Answer 2 · answered Apr 24 '21 at 17:55

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

How to generate a big Excel file with php?

2 Answers2