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, there are many ways to generate an excel standard cell and 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
| 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 );
} |
Method 2: output csv data directly in the browser with the header
| The code is as follows: |
|
/* Connect to the database */
$ DB_Server = "localhost ";
$ DB_Username = "root ";
$ DB_Password = "123456 ";
$ DB_DBName = "mydb"; // target database name
$ DB_TBLName = "mytable"; // target table name
$ Connect = @ mysql_connect ($ DB_Server, $ DB_Username, $ DB_Password) or die ("Couldn't connect .");
Mysql_query ("Set Names 'utf8 '"); $ Savename = date ("YmjHis"); // export the excel file name
$ File_type = "vnd. ms-excel ";
$ File_ending = "xls ";
Header ("Content-Type: application/$ file_type; charset = utf8 ");
Header ("Content-Disposition: attachment; filename =". $ savename. ". $ file_ending ");
// Header ("Pragma: no-cache "); /* Write remarks */
$ Now_date = date ("Y-m-j H: I: s ");
$ Title = "database name: $ DB_DBName, Data Table: $ DB_TBLName, backup date: $ now_date ";
Echo ("$ titlen "); /* Query the database */
$ SQL = "Select * from $ DB_TBLName ";
$ ALT_Db = @ mysql_select_db ($ DB_DBName, $ Connect) or die ("Couldn't select database ");
$ Result = @ mysql_query ($ SQL, $ Connect) or die (mysql_error ()); /* Write table field name */
For ($ I = 0; $ I <mysql_num_fields ($ result); $ I ++ ){
Echo mysql_field_name ($ result, $ I ).",";
}
Echo "n "; /* Write table data */
$ Sep = ", t ";
While ($ row = mysql_fetch_row ($ result )){
$ Data = "";
For ($ I = 0; $ I If (! Isset ($ row [$ I])
$ Data. = "NULL". $ sep; // process the NULL field
Elseif ($ row [$ I]! = "")
$ Data. = "$ row [$ I]". $ sep;
Else
$ Data. = "". $ sep; // process empty fields
}
Echo $ data. "n ";
}
?> |
Example 3: The second is similar.
| The code is as follows: |
|
// Search
$ Start_time = strtotime ($ start_date );
$ End_time = strtotime ($ end_date );
$ SQL = "select a. *, B. order_amount, B. money_paid from". $ ecs-> table ('invoice'). "as ".
"Left join". $ ecs-> table ('Order _ info'). "as B on a. order_id = B. order_sn ".
"Where a. add_time> =". $ start_time. "and a. add_time <=". $ end_time ."";
$ Temp_list = $ db-> getAll ($ SQL );
If ($ temp_list) {// Has data
$ Html =''. Chr (13). chr (10 );
$ Html. ='
$ Temp_time = date ('Y-m-D', $ temp_list [$ I] ['add _ time']);
$ Html. ='
| Time: |
'. $ Start_date .'~ '. $ End_date .' |
| No. |
Invoice type |
Invoice title |
Invoice content |
Order No. |
Amount |
Add date |
Recipient |
Contact Info |
Address |
';// Obtain the qualified arrayFor ($ I = 0; $ 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_ 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. ='
';
$ 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 code is as follows: |
|
|
'. $ Temp_list [$ I] [order_id].' |
You can also directly input the csv format to generate the excel standard format...
