PHP does not use open source class libraries to export mysql data to Excel files
- // 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 );
- }
It is easy to use, memory saving, and does not rely on third-party class libraries. if you need it, try it on your own. |
