PHP exports MySQL data to Excel file of fputcsv

The method here is to use fputcsv to write CSV files and output Excel files directly to the browser.

 
//  The output Excel File header can be put user.csv Change it to 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 data from the database, in order to save memory, do not put the data 1 Secondary read into memory, in the handle of the clause 1 line 1 Rows can be read  
$sql = 'select * from tbl where  ... '; 
$stmt = $db->query($sql); 

//  Open the PHP File handle, php://output  Means output directly to the browser  
$fp = fopen('php://output', 'a'); 

//  The output Excel The column name information  
$head = array(' The name ', ' gender ', ' age ', 'Email', ' The phone ', ' ... '); 
foreach ($head as $i => $v) { 
// CSV the Excel support GBK Coding, 1 Must convert, otherwise garbled code  
$head[$i] = iconv('utf-8', 'gbk', $v); 
} 

//  Pass the data through fputcsv To the file handle  
fputcsv($fp, $head); 

//  counter  
$cnt = 0; 
//  every $limit Ok, refresh 1 The output buffer Nothing too big and nothing too small  
$limit = 100000; 

//  Extract data line by line without wasting memory  
while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) { 

$cnt ++; 
if ($limit == $cnt) { // The refresh 1 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); 
} 

Advantages simple to use, very memory saving, do not rely on the third side of the library.