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
