I want to download my large db data as excel. But due to the huge amount of data, it feels like impossible to download it, as it puts a lot of load on the server, takes a lot of processing time & also it keeps crashing.

Now, I want to create multiple temporary excel files 'on the go' of some limited data Ex: 50000 rows of data & at the end of the data I want to download all these temporary files in Zip. So, it doesn't loads up the server & keeps it from crashing.

Is it achievable via PHP-CodeIgniter. Can anybody guide me ?

CodePudding user response：

You can do it in many ways.

1. which you have thought zipping that in a folder
2. increase the limit of execution and memory limit in CI or in server also.
3. implement a queue service

For zipping you can do like below way

<?php
  
// Enter the name of directory
$pathdir = "DirectoryName/"; 
$zipcreated = "NameOfZip.zip";

$zip = new ZipArchive;
   
if($zip -> open($zipcreated, ZipArchive::CREATE ) === TRUE) {
      
    // Store the path into the variable
    $dir = opendir($pathdir);
       
    while($file = readdir($dir)) {
        if(is_file($pathdir.$file)) {
            $zip -> addFile($pathdir.$file, $file);
        }
    }
    $zip ->close();
}
  
?>

For Increasing option:

You can use excel5 in PHPExcel. It can handle 65536 rows. For that

ini_set("max_execution_time", 'time_limit'); //see manual
ini_set('memory_limit', 'memory_limit'); //your memory limit as string

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

After that follow the documentation of PHPExcel.

And for the queue option: This is a bit complex process. You can implement a queue service and give the responsibility to generate the excel to the queue service. After generation, you can notify the user or return the download URL of the excel file. For notification, you need to implement a notification service also.