PHP exports MySQL Data to an Excel file (fputcsv)

The method here is to use fputcsv to write a CSV file and directly output an Excel file to the browser.
Copy codeThe Code is as follows:
// Output the excelfile header, which 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 ');

// Obtain data from the database. To save memory usage, do not read data to the memory in one row from the handle.
$ SQL = 'select * from tbl where ...... ';
$ Stmt = $ db-> query ($ SQL );

// Open the php file handle. php: // output indicates that the file is directly output to the browser.
$ Fp = fopen ('php: // output', 'A ');

// Output the Excel column Name Information
$ Head = array ('name', 'Gender ', 'age', 'email', 'telephony ','...... ');
Foreach ($ head as $ I =>$ v ){
// CSV Excel supports GBK encoding and must be converted; otherwise, garbled characters
$ Head [$ I] = iconv ('utf-8', 'gbk', $ v );
}

// Write data to the file handle through fputcsv
Fputcsv ($ fp, $ head );

// Counter
$ Cnt = 0;
// Refresh the output buffer every $ limit line. Do not set the buffer size to too large or too small.
$ Limit = 100000;

// Extract data row 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 excessive data
Ob_flush ();
Flush ();
$ Cnt = 0;
}

Foreach ($ row as $ I =>$ v ){
$ Row [$ I] = iconv ('utf-8', 'gbk', $ v );
}
Fputcsv ($ fp, $ row );
}

The advantage is simple and easy to use, which saves a lot of memory and does not rely on third-party class libraries.