使用组件
composer.json中添加该组件，执行composer update命令
"require": { "phpoffice/phpspreadsheet": "^1.9" },1、导出列表数据
use PhpOffice\PhpSpreadsheet\Cell\DataValidation; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Shared\Date; use PhpOffice\PhpSpreadsheet\Spreadsheet; public function export($list){ $title = '职位招聘信息列表'; $title_arr = ['职位类别','职位名称','城市','部门','类型','性质','薪资','人数','投递邮箱','截止时间', '岗位职责','任职资格','发布状态']; $cell_arr = range('A','Z'); $spreadsheet = new Spreadsheet(); $spreadsheet->getActiveSheet()->setTitle($title); foreach ($title_arr as $k => $v) { $spreadsheet->getActiveSheet()->getStyle($cell_arr[$k] . 1)->getFont()->setBold(true);//标题字段加黑加粗 $spreadsheet->getActiveSheet()->getColumnDimension($cell_arr[$k])->setWidth($cell_arr[$k] > 'H' ? 25 : 15);//设置宽度 $spreadsheet->getActiveSheet()->setCellValue($cell_arr[$k] . 1, $v);//设置标题 } $spreadsheet->setActiveSheetIndex(0); //超过8位数的纯数字字符串，导出表格会默认为科学计数法。解决办法是在字段末尾拼接一个" "，空格字符串即可。 foreach ($list as $key => $val) { $tmp = $key + 2; $spreadsheet->getActiveSheet()->setCellValue('A' . $tmp, $val['category']); $spreadsheet->getActiveSheet()->setCellValue('B' . $tmp, $val['position']); $spreadsheet->getActiveSheet()->setCellValue('C' . $tmp, $val['city']); $spreadsheet->getActiveSheet()->setCellValue('D' . $tmp, $val['department']); $spreadsheet->getActiveSheet()->setCellValue('E' . $tmp, $val['type']); $spreadsheet->getActiveSheet()->setCellValue('F' . $tmp, $val['nature']); $spreadsheet->getActiveSheet()->setCellValue('G' . $tmp, $val['salary']); $spreadsheet->getActiveSheet()->setCellValue('H' . $tmp, $val['num']); $spreadsheet->getActiveSheet()->setCellValue('I' . $tmp, $val['email']); $spreadsheet->getActiveSheet()->setCellValue('J' . $tmp, $val['end_date']); $spreadsheet->getActiveSheet()->getColumnDimension('K')->setAutoSize(true); $spreadsheet->getActiveSheet()->setCellValue('K' . $tmp, $val['task']); $spreadsheet->getActiveSheet()->getColumnDimension('L')->setAutoSize(true); $spreadsheet->getActiveSheet()->setCellValue('L' . $tmp, $val['ask']); $spreadsheet->getActiveSheet()->setCellValue('M' . $tmp, $val['status']); } //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GB2312'); header("Pragma: public"); header('Content-Disposition: attachment;filename="'. $title .'.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx'); return $objWriter->save('php://output'); }2、导出模板
public function template(){ $title = '职位录入模板'； $title_arr = ['职位类别','职位名称','城市','部门','类型','性质','薪资','人数','投递邮箱','截止时间,'岗位职责','任职资格']; $cell_arr = range('A','Z'); $spreadsheet = new Spreadsheet(); $spreadsheet->getActiveSheet()->setTitle($title); $spreadsheet->setActiveSheetIndex(0); foreach ($title_arr as $k => $v) { $cell_num = $cell_arr[$k] . 1; $spreadsheet->getActiveSheet()->getStyle($cell_num)->getFont()->setBold(true); //加黑加粗 $spreadsheet->getActiveSheet()->getColumnDimension($cell_arr[$k])->setWidth($cell_arr[$k] > 'H' ? 25 : 15);//设置宽度 $spreadsheet->getActiveSheet()->setCellValue($cell_num, $v);//设置标题 } $dropdown_arr = [ //表格下拉选项 'A' => ['研发类','行政类','销售类'], 'C' => ['深圳市','广州市','上海市'], 'D' => ['研发部','人事部','财务部'], 'E' => ['校招','社招'], 'F' => ['全职','兼职'], ]; foreach ($dropdown_arr as $column => $item) { //渲染下拉列表 $objValidation = $spreadsheet->getActiveSheet()->getDataValidation('$'.$column.':$'.$column);//$1:$1 $objValidation->setType(DataValidation::TYPE_LIST) ->setErrorStyle(DataValidation::STYLE_INFORMATION) ->setAllowBlank(false) ->setShowInputMessage(true) ->setShowErrorMessage(true) ->setShowDropDown(true) ->setErrorTitle('输入的值有误') ->setError('您输入的值不在下拉框列表内.') ->setPromptTitle('请选择') ->setFormula1('"'.implode(',', $item).'"'); } header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GB2312'); header("Pragma: public"); header('Content-Disposition: attachment;filename="'. $title .'.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx'); return $objWriter->save('php://output'); }3、批量导入
public function import(){ set_time_limit(60); ini_set('memory_limit', '256M'); $file_size = $_FILES['file']['size']; if ($file_size > 5 * 1024 * 1024) { throw new \Exception('文件大小不能超过5M'); } $suffix = substr(strrchr($_FILES['file']["name"], '.'), 1); if ($suffix != 'xlsx') { throw new \Exception('必须为excel表格，且必须为xlsx格式！'); } $filename = $_FILES['file']['tmp_name']; if (is_uploaded_file($filename)) { $objReader = IOFactory::createReader('Xlsx'); $objReader->setReadDataOnly(true);//忽略任何格式的信息 $objPHPExcel = $objReader->load($filename); $sheet = $objPHPExcel->getSheet(0); //excel中的第一张sheet $highestRow = $sheet->getHighestRow(); // 取得总行数 // $highestColumn = $sheet->getHighestColumn(); // 取得总列数 $cates = ['1' => '研发类', '2' => '行政类', '3' => '销售类']; $cites = ['1' => '深圳市','2' => '广州市', '3' => '上海市']; $departments =['1' => '研发部', '2' => '人事部', '3' => '财务部']; $types = ['1' => '校招', '2' => '社招']; $natures = ['1' => '全职', '2' => '兼职']; $data = []; for ($j = 2; $j <= $highestRow; $j++) { $category = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(); if(empty($category)) { throw new \Exception('A列第'.$j.'行职位类别不能为空'); } $cate_id = array_search($category, $cates); if(empty($cate_id)) { throw new \Exception('A列第'.$j.'行职位类别不存在，请先在系统中添加该类型'); } $position = $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue(); if(empty($position)) { throw new \Exception('B列第'.$j.'职位名称不能为空'); } $city = $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue(); if(empty($city)) { throw new \Exception('C列第'.$j.'行城市信息不能为空'); } $city_id = array_search($city, $cites); if(empty($city_id)) { throw new \Exception('C列第'.$j.'行城市信息不存在，请先在系统中添加该类型'); } $department = $objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue(); if(empty($department)) { throw new \Exception('D列第'.$j.'行部门信息不能为空'); } $department_id = array_search($department, $departments); if(empty($department_id)) { throw new \Exception('D列第'.$j.'行部门信息不存在，请先在系统中添加该类型'); } $type = $objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue(); if(empty($type)) { throw new \Exception('E列第'.$j.'行招聘类型信息不能为空'); } $type_id = array_search($type, $types); if(empty($type_id)) { throw new \Exception('E列第'.$j.'行招聘类型信息错误'); } $nature = $objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue(); if(empty($nature)) { throw new \Exception('F列第'.$j.'行招聘性质信息不能为空'); } $nature_id = array_search($nature, $natures); if(empty($nature_id)) { throw new \Exception('F列第'.$j.'行招聘性质信息错误'); } $salary = $objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue(); if(empty($salary)) { throw new \Exception('G列第'.$j.'薪资不能为空'); } $num = $objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue(); if(empty($num)) { throw new \Exception('H列第'.$j.'招聘人数不能为空'); } $email = $objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue(); if(empty($email)) { throw new \Exception('I列第'.$j.'投递邮箱不能为空'); } $end_date = $objPHPExcel->getActiveSheet()->getCell("J" . $j)->getValue();//获取的时间为浮点型 $end_date = date('Y-m-d', Date::excelToTimestamp($end_date));//转化为时间类型 $task = $objPHPExcel->getActiveSheet()->getCell("K" . $j)->getValue(); if(empty($task)) { throw new \Exception('K列第'.$j.'岗位职责描述不能为空'); } $ask = $objPHPExcel->getActiveSheet()->getCell('L' . $j)->getValue(); if(empty($ask)) { throw new \Exception('L列第'.$j.'任职资格描述不能为空'); } $data[] = [ 'cate_id' => $cate_id, 'position' => $position, 'city_id' => $cate_id, 'department_id' => $department_id, 'type' => $type_id, 'nature' => $nature_id, 'salary' => $salary, 'num' => $num, 'email' => $email, 'end_date' => $end_date, 'task' => $task, 'ask' => $ask, 'status' => 1 ]; } $model = new JobModel(); $ret = $model->saveAll($data); return true; } }