Export MySQL Data to an Excel file using PHP

02 // output the excelfile header. You can replace user.csv with the file name 03 header ('content-Type: application/vnd. ms-excel '); 04 header ('content-Disposition: attachment; filename = "user.csv"'); 05 header ('cache-Control: max-age = 0 '); 06 07 // obtain data from the database. To save memory, do not read data to the memory at one time. You can read the data from one row in the handle in 08 $ SQL = 'select * from tbl where ...... '; 09 $ stmt = $ db-> query ($ SQL); 10 11 // open the php file handle, php: // output indicates that the file is directly output to the browser 12 $ fp = fopen ('php: // output', 'A '); 13 14 // output Excel column name information 15 $ head = array ('name', 'Gender ', 'age', 'email', 'telephony ','...... '); 16 foreach ($ head as $ I => $ v) {17 // CSV Excel supports GBK encoding and must be converted, otherwise, garbled 18 $ head [$ I] = iconv ('utf-8', 'gbk', $ v ); 19} 20 21 // write data to file handle 22 fputcsv ($ fp, $ head) through fputcsv; 23 24 // counter 25 $ cnt = 0; 26 // refresh the output buffer every $ limit row. Do not set the buffer size to too large or too small. 27 $ limit = 100000; 28 29 // fetch data row by row, 30 while ($ row = $ stmt-> fetch (Zend_Db: FETCH_NUM) {31 32 $ cnt ++; 33 if ($ limit = $ cnt) {// refresh the output buffer to prevent problems caused by excessive data. 34 ob_flush (); 35 flush (); 36 $ cnt = 0; 37} 38 39 foreach ($ row as $ I => $ v) {40 $ row [$ I] = iconv ('utf-8', 'gbk ', $ v); 41} 42 fputcsv ($ fp, $ row); 43}

It is easy to use and saves memory without relying on third-party class libraries.