php利用ExcelParser 導入excel存入mysql 資料庫

php教程 導入excel存入mysql教程 資料庫教程, 利用ExcelParser


class ExcelParser


{


    private $_data=array(0,'');


    private $_excel_handle;


    private $_excel=array();


    /**


     * 建構函式


     * @param <string> $filename 上傳檔暫存檔案名稱


     */


    public function __construct($filename)


    {


        /**


         * 引入excelparser類


         * 普通方法為


         * requires 路徑.'excelparser.php';


         */


import('@. Util.PHPExcelParser.excelparser','','.php');


        $this->_excel_handle=new ExcelFileParser();


        //錯誤獲取


        $this->checkErrors($filename);


    }


    /**


     * 錯誤校驗


     */


    private function checkErrors($filename)


    {


        /**


         * 方法一


         */


        $error_code=$this->_excel_handle->ParseFromFile($filename);


/**


         * 方法二


         * $file_handle = fopen($this->_filename,'rb');


         * $content = fread($file_handle,filesize($this->_filename));


         * fclose($file_handle);


         * $error_code = $this->_excel->ParseFromString($content);


         * unset($content,$file_handle);


         */


        switch($error_code)


        {


            case 0:


無錯誤不處理


                break;


            case 1:


                $this->_data=array(1,'檔讀取錯誤(Linux注意讀寫許可權)');


                break;


            case 2:


                $this->_data=array(1,'檔太小');


                break;


case 3:


                $this->_data=array(1,'www.jzread.com讀取Excel表頭失敗');


                break;


            case 4:


                $this->_data=array(1,'檔讀取錯誤');


                break;


            case 5:


                $this->_data=array(1,'檔可能為空');


break;


            case 6:


                $this->_data=array(1,'檔不完整');


                break;


            case 7:


                $this->_data=array(1,'讀取資料錯誤');


                break;


            case 8:


$this->_data=array(1,'版本錯誤');


                break;


        }


        unset($error_code);


    }


    /**


     * Excel資訊獲取


     */


    private function getExcelInfo()


    {


        if(1==$this->_data[0])return;


        /**


         * 獲得sheet數量


         * 獲得sheet單元對應的行和列


*/


        $this->_excel['sheet_number']=count($this->_excel_handle->worksheet['name']);


        for($i=0;$i<$this->_excel['sheet_number'];$i++)


        {


            /**


             * 行于列


             * 注意:從0開始計數


             */


            $row=$this->_excel_handle->worksheet['data'][$i]['max_row'];


$col=$this->_excel_handle->worksheet['data'][$i]['max_col'];


            $this->_excel['row_number'][$i]=($row==Null)?0:++$row;


            $this->_excel['col_number'][$i]=($col==Null)?0:++$col;


            unset($row,$col);


        }


    }


    /**


     * 中文處理函數


     * @return <string>


     */


    private function uc2html($str)


    {


        $ret = '';


for( $i=0; $i<strlen($str)/2; $i++ )


        {


            $charcode = ord($str[$i*2])+256*ord($str[$i*2+1]);


            $ret .= '&#'.$charcode.';';


        }


        return mb_convert_encoding($ret,'UTF-8','HTML-ENTITIES');


    }


    /**


     * Excel資料獲取


     */


    private function getExcelData()


    {


        if(1==$this->_data[0])return;

        //修改標記


        $this->_data[0]=1;


        //獲取資料


        for($i=0;$i<$this->_excel['sheet_number'];$i++)


        {


            /**


             * 對行迴圈


             */


            for($j=0;$j<$this->_excel['row_number'][$i];$j++)


            {


                /**


* 對列迴圈


                 */


                for($k=0;$k<$this->_excel['col_number'][$i];$k++)


                {


                    /**


                     * array(4) {


                     *   ["type"]   => 類型 [0字元類型1整數2浮點數3日期]


*   ["font"]   => 字體


                     *   ["data"]   => 資料


                     *   ...


                     * }


                     */


                    $data=$this->_excel_handle->worksheet['data'][$i]['cell'][$j][$k];


switch($data['type'])


                    {


                        case 0:


                            //字元類型


                            if($this->_excel_handle->sst['unicode'][$data['data']])


{


                                //中文處理


                                $data['data'] = $this->uc2html($this->_excel_handle->sst['data'][$data['dat a']]);


                            }


else


                            {


                                $data['data'] = $this->_excel_handle->sst['data'][$data['data']];


                            }


                            break;


                        case 1:


                            //整數


                            //TODO


                            break;


                        case 2:


浮點數


                            //TODO


                            break;


                        case 3:


                            //日期


TODO


                            break;


                    }


                    $this->_data[1][$i][$j][$k]=$data['data'];


                    unset($data);


                }


}


        }


    }


    /**


     * 主函數


     * @return <array> array(識別碼,內容s)


     */


    public function main()


    {


        //Excel資訊獲取


        $this->getExcelInfo();


        //Excel資料獲取


        $this->getExcelData();


        return $this->_data;


    }


}

?>