Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to generate a big Excel file with php?

Tags:

php

phpexcel

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

    ?>
like image 750
Krokodile Avatar asked Jun 16 '15 08:06

Krokodile


People also ask

Can php create Excel file?

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.

How do I export data from php to Excel?

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.


2 Answers

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';
like image 200
aVadim Avatar answered Oct 07 '22 11:10

aVadim


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

like image 27
Faiz Rasool Avatar answered Oct 07 '22 11:10

Faiz Rasool