fastadmin实现导出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

            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);


        }

    }