PHP export MySQL data to Excel file

 
 

  
  

  
Output Excel file headers, you can replace user.csv with the file name you want
  
  

  
Header (' Content-type:application/vnd.ms-excel ');
  
  

  
Header (' Content-disposition:attachment;filename= "user.csv");
  
  

  
Header (' cache-control:max-age=0 ');
  
  

  
Get the data from the database, in order to save memory, do not read the data to memory at once, one line in the handle can be read
  
  

  
$sql = ' SELECT * from tbl where ... ';
  
  

  
$stmt = $db->query ($sql);
  
  

  
Open the PHP file handle, php://output for direct output to the browser
  
  

  
$fp = fopen (' php://output ', ' a ');
  
  

  
Output Excel Column name information
  
  

  
$head = Array (' name ', ' gender ', ' age ', ' Email ', ' telephone ', ' ... ');
  
  

  
foreach ($head as $i => $v) {
  
  

  
CSV Excel supports GBK encoding, be sure to convert, otherwise garbled
  
  

  
$head [$i] = iconv (' utf-8 ', ' GBK ', $v);
  
  

  
}
  
  

  
Write data to a file handle by fputcsv
  
  

  
Fputcsv ($fp, $head);
  
  

  
Counter
  
  

  
$cnt = 0;
  
  

  
Every $limit line, refresh the output buffer, not too big, not too small
  
  

  
$limit = 100000;
  
  

  
Fetching data line by row without wasting memory
  
  

  
while ($row = $stmt->fetch (zend_db::fetch_num)) {
  
  

  
$cnt + +;
  
  

  
if ($limit = = $cnt) {//Refresh the output buffer to prevent problems caused by too much data
  
  

  
Ob_flush ();
  
  

  
Flush ();
  
  

  
$cnt = 0;
  
  

  
}
  
  

  
foreach ($row as $i => $v) {
  
  

  
$row [$i] = iconv (' utf-8 ', ' GBK ', $v);
  
  

  
}
  
  

  
Fputcsv ($fp, $row);
  
  

  
}