【ThinkPHP+Excel】PHP操作Excel数据导入导出！

Excel导入导出，源码直接奉上！

数据导入

 /**
     * @导入试题
     */
    public function import(){
        //引入EXCEL操作库
        require_once( WEB_ROOT . '/Application/Common/excel/reader.php' );
        //
        $this->bcItemPush('导入问题');
        //
        //$this->assign('questionnaire', M('Questionnaires')->field('id,type,name,description')->find(I('get.questionnaire_id/d')));
        $qid = I('get.questionnaire_id/d');
        if (empty ($qid)) {
            $this->error('问卷ID不存在，请重新导入！');
        }

        $tmp = $_FILES['upload_file']['tmp_name'];
        //
        if (empty ($tmp)) {
            $this->error('请选择要导入的EXCEL(.xls)文件！');
        }

        switch ($_FILES['upload_file']['type']){
            case "application/kset" :
                break;
            case "application/excel" :
                break;
            case "application/vnd.ms-excel" :
                break;
            case "application/msexcel" :
                break;
            case "application/octet-stream" :
                break;
            default:
                $flag = 1;
        }
        if($flag ==1){
            $this->error('目前只支持EXCEL(.xls)格式文件！！！');
        }

        $file_name = WEB_ROOT . "/Public/Upload/Qid_{$qid}_".time().".xls";
        if (move_uploaded_file($tmp, $file_name)) {
            $xls = new \Spreadsheet_Excel_Reader();
            $xls->setOutputEncoding('utf-8');
            $xls->read($file_name);
            $i=2;$j=0;$k=0;
            $len = $xls->sheets[0]['numRows'];
            while( $i <= $len){

                $name = trim($xls->sheets[0]['cells'][$i][2]);
                $type = intval($xls->sheets[0]['cells'][$i][3]);
                $right = trim($xls->sheets[0]['cells'][$i][4]);

                $type_str = $type == 1 ? 'radio' : 'checkbox';

                $options = [];
                if( trim($xls->sheets[0]['cells'][$i][5]) ){
                    $options[0] = [
                        'type' => $type_str,
                        'text' => trim($xls->sheets[0]['cells'][$i][5])
                    ];
                }

                if( trim($xls->sheets[0]['cells'][$i][6]) ){
                    $options[1] = [
                        'type' => $type_str,
                        'text' => trim($xls->sheets[0]['cells'][$i][6])
                    ];
                }

                if( trim($xls->sheets[0]['cells'][$i][7]) ){
                    $options[2] = [
                        'type' => $type_str,
                        'text' => trim($xls->sheets[0]['cells'][$i][7])
                    ];
                }

                if( trim($xls->sheets[0]['cells'][$i][8]) ){
                    $options[3] = [
                        'type' => $type_str,
                        'text' => trim($xls->sheets[0]['cells'][$i][8])
                    ];
                }

                if( trim($xls->sheets[0]['cells'][$i][9]) ){
                    $options[4] = [
                        'type' => $type_str,
                        'text' => trim($xls->sheets[0]['cells'][$i][9])
                    ];
                }

                if( trim($xls->sheets[0]['cells'][$i][10]) ){
                    $options[5] = [
                        'type' => $type_str,
                        'text' => trim($xls->sheets[0]['cells'][$i][10])
                    ];
                }

                if( M('Questions')->where(['questionnaire_id' => $qid,'name' => $name])->find() ){
                    $k ++;
                }else{
                    $data['name'] = $name;
                    $data['options'] = json_encode($options);
                    $data['standard'] = str_ireplace(['A','B','C','D','E','F'],[0,1,2,3,4,5],str_replace('，',',',$right));
                    $data['score'] = 1;
                    $data['questionnaire_id'] = $qid;
                    $data['sort'] = $i;
                    M('Questions')->data( $data )->add();
                    $j ++;
                }
                $i++;
            }
            $this->success('成功导入数据'.$j.'条！剔除重复数据'.$k.'条！');
        }

    }

数据导出

//导出题目
    public function export(){

        //引入EXCEL操作库
        require_once( WEB_ROOT . '/Application/Common/excel/reader.php' );
        //
        $this->bcItemPush('导出问题');
        //
        //$this->assign('questionnaire', M('Questionnaires')->field('id,type,name,description')->find(I('get.questionnaire_id/d')));
        $qid = I('get.questionnaire_id/d');
        if (empty ($qid)) {
            $this->error('问卷ID不存在，请重新导出！');
        }

        $list = M('Questions')->where(['questionnaire_id' => $qid])->order('sort')->select();
        //
        $max_choice = 0;
        foreach ($list as $k => $v){

            $options = json_decode($v['options'],true);
            $type = isset( $options[0]['type'] ) && $options[0]['type'] == 'radio' ? '单选题' : '多选题';

            $data[$k] = [
                'id' => $v['id'],
                'name' => $v['name'],
                'type' => $type,
                'standard' => str_ireplace([0,1,2,3,4,5],['A','B','C','D','E','F'],$v['standard'])
            ];
            //
            $choice = 'A';
            foreach ($options as $key => $val){
                $max_choice = $key > $max_choice ? $key : $max_choice;
                $data[$k][$choice] = $val['text'];
                $choice ++;
            }
        }

        $head = array("ID","题目","类型","正确答案");
        $hChoice = 'A';
        for($i = 0;$i<=$max_choice;$i++){
            $head[] = '选项' . $hChoice;
            $hChoice ++;
        }
        $this->export_excel($data,$head,time());
        exit();
    }

    //导出EXCEL
    private function export_excel($data=array(),$title=array(),$filename='report'){
        header("Content-Type: text/csv");
        header("Content-Disposition:attachment;filename=".$filename.".csv");
        if (!empty($title)){
            foreach ($title as $k => $v) {
                $title[$k]=iconv("UTF-8", "GBK",$v);
            }
            $title= implode(",", $title);
            echo "$title\n";
        }
        if (!empty($data)){
            foreach($data as $key=>$val){
                foreach ($val as $ck => $cv) {
                    $data[$key][$ck]=iconv("UTF-8", "GBK", $cv);
                }
                $data[$key]=implode(",", $data[$key]);
            }
            echo implode("\n",$data);
        }
    }