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

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

来源地址：https://blog.csdn.net/nh2r1114/article/details/129367094