PHP exports MySQL data to an Excel file (fputcsv)

I often encounter the need to export data from a database to an Excel file. using some open-source class libraries, such as PHPExcel, is indeed easy to implement, but it does not support a lot of data, it is easy to reach the PHP memory usage limit. The method here is to use fputcsv to write a CSV file and directly output an Excel file to the browser.

The 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.