PHP export MySQL data to Excel file (fputcsv)

The method here is to use Fputcsv to write a CSV file, directly to the browser output Excel files.
Copy CodeThe code is as follows:
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);
}

The advantages are simple and easy to use, save memory, and do not rely on the third party class library.