PHP realizes real time generation and downloading of EXCEL files with large data volume

Preface

Recently, I received a demand in my work, and exported the corresponding user access log to excel through the selected time period. Due to the large number of users, 500,000 plus data is often exported. However, the commonly used PHPexcel package needs to get all the data before generating excel, which will obviously cause memory overflow when generating excel files with huge data volume. Therefore, consider using the form of letting PHP write the output stream and let the browser download it to fulfill the requirements.

We write the PHP output stream as follows

$fp = fopen('php://output', 'a');
fputs($fp, 'strings');
....
....
fclose($fp)

php://output Is a writable output stream, which allows the program to write output to the output stream like operation file 1. PHP will send the contents of the output stream to web server and return it to the browser that initiated the request

In addition, since excel data is gradually read from the database and then written to the output stream, it is necessary to set the execution time of PHP to be 1 point longer (the default is 30 seconds) set_time_limit(0) There is no limit on the execution time of PHP.

Note: The following code only clarifies the ideas and steps of generating large data volume EXCEL, and after removing the project business code, the program has syntax errors and cannot be used to run directly. Please fill in the corresponding business code according to your own needs!

 /**
  *  Article access log 
  *  Log files that are downloaded are usually very large ,  So set it first csv Relative Header Head ,  Then open it 
  * PHP output Stream ,  Progressive direction output Write data to stream ,  Write to 1 After quantification, the system buffer is washed into the response 
  *  Avoid buffer overflow 
  */
 public function articleAccessLog($timeStart, $timeEnd)
 {
  set_time_limit(0);
  $columns = [
   ' Article ID', ' Title of an article ', ......
  ];
  $csvFileName = ' User log ' . $timeStart .'_'. $timeEnd . '.xlsx';
  // Set up to tell the browser to download excel Documentary headers
  header('Content-Description: File Transfer');
  header('Content-Type: application/vnd.ms-excel');
  header('Content-Disposition: attachment; filename="'. $fileName .'"');
  header('Expires: 0');
  header('Cache-Control: must-revalidate');
  header('Pragma: public');
  $fp = fopen('php://output', 'a');// Open output Stream 
  mb_convert_variables('GBK', 'UTF-8', $columns);
  fputcsv($fp, $columns);// Formatting data as CSV Format and write to the output In stream 
  $accessNum = '1000000'// Get the total amount from the database, assuming that 1 Millions 
  $perSize = 1000;// Number of items per query 
  $pages = ceil($accessNum / $perSize);
  $lastId = 0;
  for($i = 1; $i <= $pages; $i++) {
   $accessLog = $logService->getArticleAccessLog($timeStart, $timeEnd, $lastId, $perSize);
   foreach($accessLog as $access) {
    $rowData = [
     ......// Every 1 Row data 
    ];
    mb_convert_variables('GBK', 'UTF-8', $rowData);
    fputcsv($fp, $rowData);
    $lastId = $access->id;
   }
   unset($accessLog);// Release variable memory 
   // Refresh output buffer to browser 
   ob_flush();
   flush();// Must be used at the same time  ob_flush()  And flush()  Function to refresh the output buffer. 
  }
  fclose($fp);
  exit();
 }

Well, in fact, it is very simple to write the output stream step by step and send it to the browser to let the browser download the whole file step by step. Because it is written step by step, it is impossible to get the overall size of the file, so it is impossible to pass the setting header("Content-Length: $size"); Tell the browser how big the file is before downloading. But does not affect the overall effect here the core problem is to solve the real-time generation and download of large files.

Summarize