Mysql資料庫匯出替Excel(ODBC驅動)

Mysql資料庫匯出為Excel(ODBC驅動)

PHP code

currDir = dirname(__FILE__);        }                /* 擷取Mysql的列名稱 */        public function getSrcColumn(){            $handle = @mysql_connect($this->host,$this->username,$this->pwd);            $this->srcColumn = array();            if(is_bool($handle)){                echo "====**====
";                echo "Mysql串連出錯.
檔案：".(__FILE__)." 
行號：".(__LINE__)."
錯誤：".mysql_error();                echo "
====**====
";                exit(0);            }            $ret = @mysql_select_db($this->dbName,$handle);            if($handle == FALSE){                echo "====**====
";                echo "Mysql選擇資料庫出錯.
檔案：".(__FILE__)." 
行號：".(__LINE__)."
錯誤：".mysql_error();                echo "
====**====
";                exit(0);            }            $ret = @mysql_query('describe '.$this->tbName,$handle);            if(is_bool($ret)){                echo "====**====
";                echo "Mysql語句執行出錯.
檔案：".(__FILE__)." 
行號：".(__LINE__)."
錯誤：".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;        }                /* 擷取指定的Mysql資料庫表中的內容 */        public function getSrcData(){            $handle = @mysql_connect($this->host,$this->username,$this->pwd);            $this->srcData = array();            if(is_bool($handle)){                echo "====**====
";                echo "Mysql串連出錯.
檔案：".(__FILE__)." 
行號：".(__LINE__)."
錯誤：".mysql_error();                echo "
====**====
";                exit(0);            }            $ret = @mysql_select_db($this->dbName,$handle);            if($handle == FALSE){                echo "====**====
";                echo "Mysql選擇資料庫出錯.
檔案：".(__FILE__)." 
行號：".(__LINE__)."
錯誤：".mysql_error();                echo "
====**====
";                exit(0);            }            $ret = @mysql_query('select * from '.$this->tbName,$handle);            if(is_bool($ret)){                echo "====**====
";                echo "Mysql語句執行出錯.
檔案：".(__FILE__)." 
行號：".(__LINE__)."
錯誤：".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;        }                /* 主力部分 將Mysql資料庫中的內容匯出為Excel檔案 */        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){                $sql .= ($value." text,");            }            $sql = substr($sql,0,strlen($sql)-1);            $sql .= ")";            $ret = odbc_exec($handle,$sql);            //以上內容是建立一個Excel檔案 並將Mysql的列轉移到Excel檔案中去。            foreach($this->srcData as $value){                $sql = 'insert into ['.$this->tbName.'$] values(';                for($i=0;$ihost = $host;            $this->username = $username;            $this->pwd = $pwd;            $this->dbName = $dbName;            $this->tbName = $tbName;        }                /* 設定Excel資料庫的名稱 */        public function setXlsName($xlsName){            $this->xlsName = $this->currDir."\\".$xlsName.".xls";            return $this->xlsName;        }    }        /*以下部分是測試功能的，可刪除*/    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();?>
