Export the Mysql database as an Excel (ODBC driver) PHPcode & lt ;? Phpdate_default_timezone_set (AsiaShanghai); ** @ author: jinger7281@gmail.com * when this program does not infringe on any company or individual rights: * This program follows GP Mysql database export as Excel (ODBC driver)
PHP code
CurrDir = dirname (_ FILE _);}/* obtain the Mysql column name */public function getSrcColumn () {$ handle = @ mysql_connect ($ this-> host, $ this-> username, $ this-> pwd); $ this-> srcColumn = array (); if (is_bool ($ handle )) {echo "= ** =
"; Echo" Mysql connection error.
FILE: ". (_ FILE __)."
Row number: ". (_ LINE __)."
Error: ". mysql_error (); echo"
==== ** ====
"; Exit (0);} $ ret = @ mysql_select_db ($ this-> dbName, $ handle); if ($ handle = FALSE) {echo "= ** =
"; Echo" an error occurred while selecting the database for Mysql.
FILE: ". (_ FILE __)."
Row number: ". (_ LINE __)."
Error: ". mysql_error (); echo"
==== ** ====
"; Exit (0);} $ ret = @ mysql_query ('describe '. $ this-> tbName, $ handle); if (is_bool ($ ret) {echo "=====** ====
"; Echo" an error occurred while executing Mysql statements.
FILE: ". (_ FILE __)."
Row number: ". (_ LINE __)."
Error: ". mysql_error (); echo"
==== ** ====
"; Exit (0);} while ($ data = mysql_fetch_array ($ ret ))! = FALSE) {array_push ($ this-> srcColumn, $ data ['field']);} mysql_free_result ($ ret); mysql_close ($ handle ); return $ this-> srcColumn;}/* get the content of the specified Mysql database table */public function getSrcData () {$ handle = @ mysql_connect ($ this-> host, $ this-> username, $ this-> pwd); $ this-> srcData = array (); if (is_bool ($ handle )) {echo "= ** =
"; Echo" Mysql connection error.
FILE: ". (_ FILE __)."
Row number: ". (_ LINE __)."
Error: ". mysql_error (); echo"
==== ** ====
"; Exit (0);} $ ret = @ mysql_select_db ($ this-> dbName, $ handle); if ($ handle = FALSE) {echo "= ** =
"; Echo" an error occurred while selecting the database for Mysql.
FILE: ". (_ FILE __)."
Row number: ". (_ LINE __)."
Error: ". mysql_error (); echo"
==== ** ====
"; Exit (0);} $ ret = @ mysql_query ('select * from '. $ this-> tbName, $ handle); if (is_bool ($ ret) {echo "=====** ====
"; Echo" an error occurred while executing Mysql statements.
FILE: ". (_ FILE __)."
Row number: ". (_ LINE __)."
Error: ". mysql_error (); echo"
==== ** ====
"; Exit (0);} while ($ data = mysql_fetch_array ($ ret ))! = FALSE) {array_push ($ this-> srcData, $ data);} mysql_free_result ($ ret); mysql_close ($ handle); return $ this-> srcData ;} /* export the content in the Mysql database as an Excel file */public function m2Xls () {$ handle = odbc_connect ("Driver = {MicroSoft Excel Driver (*. xls)}; READONLY = false; CREATE_DB = \"". $ this-> xlsName. "\"; Dbq = ". $ this-> xlsName, "", ""); $ SQL = 'create table '. $ this-> tbName. '('; foreach ($ this-> srcColumn as $ value) {$ sq L. = ($ value. "text,") ;}$ SQL = substr ($ SQL, 0, strlen ($ SQL)-1); $ SQL. = ")"; $ ret = odbc_exec ($ handle, $ SQL); // the above content is to create an Excel file and transfer the Mysql column to the Excel file. Foreach ($ this-> srcData as $ value) {$ SQL = 'Insert ['. $ this-> tbName. '$] values ('; for ($ I = 0; $ I
Host = $ host; $ this-> username = $ username; $ this-> pwd = $ pwd; $ this-> dbName = $ dbName; $ this-> tbName = $ tbName;}/* Set the Excel database name */public function setXlsName ($ xlsName) {$ this-> xlsName = $ this-> currDir. "\\". $ xlsName. ". xls "; return $ this-> xlsName;}/* The following sections test the function. you can delete */header (" Content-Type: text/html; charset = UTF-8 "); $ mysql2xls = new mysql2Xls (); $ mysql2xls-> setMysqlArgs ('localhost', 'root ','****** ', 'Others', 'zhuifeng'); $ mysql2xls-> setXlsName ('test'); $ mysql2xls-> getSrcColumn (); $ mysql2xls-> getSrcData (); $ mysql2xls-> m2Xls ();?>