fastadmin实现导出Excel和导入Excel数据

/**
     * 导出EXCEL
     * 导出Excel方法
     */
     
    public function export()
    {
        if ($this->request->isPost()) {
            require_once(VENDOR_PATH .'/PHPExcel-1.8/Classes/PHPExcel.php');//执行期间包含并运行指定文件(通俗一点，括号内的文件会执行一遍
            set_time_limit(0);
            $ids = $this->request->post('ids');
                $excel = new \PHPExcel();
                $excel->getProperties()
                    ->setTitle("员工管理");
                //设置表格参数
                $excel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
                $excel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
                $excel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
                $excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
                $excel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
                $excel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
                $excel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
                //设置表头内容
                $worksheet = $excel->setActiveSheetIndex(0)
                    ->setCellValue('A1', 'id')
                    ->setCellValue('B1', '姓名')
                    ->setCellValue('C1', '手机号')
                    ->setCellValue('D1', '证件类型')
                    ->setCellValue('E1', '证件号')
                    ->setCellValue('F1', '性别')
                    ->setCellValue('G1', '生日')
                    ->setCellValue('H1', '年龄')
                    ->setCellValue('I1', '是否已婚')
                    ->setCellValue('J1', '地址')
                    ->setCellValue('K1', '民族')
                    ->setCellValue('L1', '政治面貌')
                    ->setCellValue('M1', '是否健康')
                    ->setCellValue('N1', '学历')
                    ->setCellValue('O1', '入职日期');


                    //根据情况搜索内容有其他条件可在select之前添加
                   
                    if($ids=='all' || empty($ids)){
                        $list = $this->model->select();
                    } else {
                        $list = $this->model->select($ids);
                    }

                    //循环添加表中内容 严谨一些可以在$val['']后添加 ?? '里面填入数据为空时的信息'
                    //$val[]里面填写字段名
                foreach ($list as $k => $val) {
                    $k = $k + 2;
                    $worksheet->setCellValue('A' . $k, $val['id'])
                        ->setCellValue('B' . $k, $val['name'])
                        ->setCellValue('C' . $k, $val['tel'] ."\t")
                        ->setCellValue('D' . $k, $val['type'])
                        ->setCellValue('E' . $k, $val['typenum'] . "\t")
                        ->setCellValue('F' . $k, $val['sex'])
                        ->setCellValue('G' . $k, $val['birth'])
                        ->setCellValue('H' . $k, $val['age'])
                        ->setCellValue('I' . $k, $val['yeson'])
                        ->setCellValue('J' . $k, $val['loc'])
                        ->setCellValue('K' . $k, $val['han'])
                        ->setCellValue('L' . $k, $val['face'])
                        ->setCellValue('M' . $k, $val['jiank'])
                        ->setCellValue('N' . $k, $val['xueli'])
                        ->setCellValue('O' . $k, $val['rutime']);
                }

                $excel->createSheet();
                $title = "员工管理" . date("YmdHis");
                header('Content-Type: applicationnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
                header('Cache-Control: max-age=0');
                header('Cache-Control: max-age=1');

                header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
                header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
                header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
                header('Pragma: public'); // HTTP/1.0
                $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
                $objWriter->save('php://output');
                exit;
                return;
            }
    }

/**
     * 导出EXCEL
     * 导入Excel方法
     */
public function import(){
            require_once(VENDOR_PATH .'/PHPExcel-1.8/Classes/PHPExcel.php');
            if($this->request->isPost()){
                $apply_file = $this->request->file('file');
                if (!$apply_file) {
                    $this->error('请上传数据文件');
                }
                $info = $apply_file->validate(['size'=>1567800,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . DS . 'public/uploads'. DS .'excel');
                $excelname=$info->getSaveName();
                $filePath = ROOT_PATH . DS . 'public/uploads/excel' . DS .  $excelname;
                
                if (!is_file($filePath)) {
                    $this->error('找不到数据文件，请重新上传');
                }
                $PHPReader = new \PHPExcel_Reader_Excel2007();
                if (!$PHPReader->canRead($filePath)) {
                    $PHPReader = new \PHPExcel_Reader_Excel5();
                    if (!$PHPReader->canRead($filePath)) {
                        $PHPReader = new \PHPExcel_Reader_CSV();
                        if (!$PHPReader->canRead($filePath)) {
                            $this->error('文件类型不合法');
                        }
                    }
                }
    
                $excel = $PHPReader->load($filePath, $encode = 'utf-8');
                $sheet = $excel->getSheet(0);
                $row_num = $sheet->getHighestRow();//获取总行数
                $col_num = $sheet->getHighestColumn();//获取总列数
                $arr = array('A','B','C','D','E','F','G'); 
                $res_arr = array();// 一次读取一列 
                for ($row = 2; $row <= $row_num; $row++) {  
                    $row_arr = array();  
                    for ($column = 0; $arr[$column] != 'G'; $column++) {  
                        $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();  
                        $row_arr[] = $val;
                    }
                    $res_arr[] = $row_arr;
                }
                $result = [];
                foreach($res_arr as $k => $v){
                    // $result[$k]['id'] = $v[0];
                    $result[$k]['title'] = $v[1];
                    $result[$k]['option'] = $v[2];
                    $result[$k]['A'] = $v[3];
                    $result[$k]['B'] = $v[4];
                    $result[$k]['C'] = $v[5]; 
                }
                    $success = [];
                    $error = [];

                    Db::startTrans();
                try{
                    foreach ($result as $key => $v) {
                        $res = db('staff_info')->lock(true)->insert($v);
                        if($res){
                            array_push($success,$v);
                        }else{
                            array_push($error,$v);
                        }
                    }
                   
                    Db::commit();
                }catch(\Exception $e){
                   Db::rollback();
                }

                $data = [
                    'success'=>$success,
                    'error'=>$error
                ];
                $this->success('一共'.count($result).'条数据，成功导入'.count($success).'条数据，导入失败'.count($error).'条数据',$data);
               
                   
            }
     
        }