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.
// Output the excelfile header. You can replace user.csv with the file name 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, do not read data to the memory at one time. Read the data from one row in 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 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 code $ head [$ I] = iconv ('utf-8', 'gbk', $ v);} // writes data to the file handle fputcsv through fputcsv ($ fp, $ head); // counter $ cnt = 0; // refresh the output buffer every $ limit row. Do not set it 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 );}
It is easy to use and saves memory without relying on third-party class libraries.