PHP exports the mysql database as an excel table

There are many ways to use php to export a mysql database as an excel table. The simplest way is to directly use the phpfputcsv function. you can also directly input the csv format, to generate a standard excel format, we need a third-party plug-in. side... there are many ways to use php to export a mysql database as an excel table. The simplest way is to directly use the php fputcsv function. you can also directly input the csv format, to generate a standard excel format, we need a third-party plug-in.

Method 1: Use fputcsv with the following code:

// 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) {// open source code phprm.com $ 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 );}

Method 2: output csv data directly in the browser using the header. the code is as follows:

 
Example 3: The second is similar. the code is as follows:
// Search for $ start_time = strtotime ($ start_date); $ end_time = strtotime ($ end_date); $ SQL = "select. *, B. order_amount, B. money_paid from ". $ ecs-> table ('invoice '). "as ". "left join ". $ ecs-> table ('Order _ info '). "as B on. order_id = B. order_s \ n ". "where. add_time> = ". $ start_time. "and. add_time <= ". $ end_time. ""; $ temp_list = $ db-> getAll ($ SQL); if ($ temp_list) {// Has Data $ Html =''. Chr (13). chr (10); $ Html. ='
  
  '; // Obtain the qualified array for ($ I = 0; $ I <count ($ temp_list); $ I ++) {$ temp_ I = $ I + 1; if ($ temp_list [$ I] [order_amount] = 0) {$ temp_money = $ temp_list [$ I] [money_paid];} else {$ temp_money = $ temp_list [$ I] [order_amount];} $ temp_time = date ('Y-m-D ', $ temp_list [$ I] ['add _ time']); $ Html. =' 
    ';} $ Html. =' 
   
 
   
   
 
    
 
     
Time:
 
     
'. $ Start_date .'~ '. $ End_date .'
 
    
 
    
 
     
No.
 
     
Invoice type
 
     
Invoice title
 
     
Invoice content
 
     
Order No.
 
     
Amount
 
     
Add date
 
     
Recipient
 
     
Contact Info
 
     
Address
 
    
 
     
'. $ Temp_ I .'
 
     
'. $ Temp_list [$ I] [type_name].'
 
     
'. $ Temp_list [$ I] [top].'
 
     
'. $ Temp_list [$ I] [content].'
 
     
'. $ Temp_list [$ I] [order_id].'
 
     
'. $ Temp_money .'
 
     
'. $ Temp_time .'
 
     
'. $ Temp_list [$ I] [user_name].'
 
     
'. $ Temp_list [$ I] [mobile].'. $ temp_list [$ I] [tel].'
 
     
'. $ Temp_list [$ I] [address].'
 
    
  
  
'; $ Html. =''; $ Mime_type = 'application/vnd. ms-excel '; header ('content-Type :'. $ mime_type); header ('content-Disposition: attachment; filename = "invoice.xls" '); header ('cache-Control: must-revalidate, post-check = 0, pre-check = 0'); header ('pragma: public'); echo $ Html;
Sometimes excel will automatically convert the number format, so some mobile phone numbers, ID cards and so on will be messy, so you can define the following code before exporting: <tdalign = "center" nowrap = "nowrap" style = "vnd. ms-excel.numberformat: @ "> '. $ temp_list [$ I] [order_id]. '</td>