Excel导入

/**
 * Excel解析成 员工数据集合
 * @param file
 * @param allNations
 * @param allPoliticsstatus
 * @param allDepartment
 * @param allPositions
 * @param allJobLevels
 * @return
 */
public static List<Employee> excel2Employee(MultipartFile file, List<Nation> allNations, List<Politicsstatus> allPoliticsstatus, List<Department> allDepartment, List<Position> allPositions, List<JobLevel> allJobLevels) {
    List<Employee> list = new ArrayList<>();
    Employee employee = null;
    try {
        //1. 创建一个 workbook 对象
        HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
        //2. 获取 workbook 中表单的数量
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            //3. 获取表单
            HSSFSheet sheet = workbook.getSheetAt(i);
            //4. 获取表单中的行数
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < physicalNumberOfRows; j++) {
                //5. 跳过标题行
                if (j == 0) {
                    continue;//跳过标题行
                }
                //6. 获取行
                HSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue;//防止数据中间有空行
                }
                //7. 获取列数
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                employee = new Employee();
                for (int k = 0; k < physicalNumberOfCells; k++) {
                    HSSFCell cell = row.getCell(k);
                    switch (cell.getCellType()) {
                        case STRING:
                            String cellValue = cell.getStringCellValue();
                            switch (k) {
                                case 1:
                                    employee.setName(cellValue);
                                    break;
                                case 2:
                                    employee.setWorkid(cellValue);
                                    break;
                                case 3:
                                    employee.setGender(cellValue);
                                    break;
                                case 5:
                                    employee.setIdcard(cellValue);
                                    break;
                                case 6:
                                    employee.setWedlock(cellValue);
                                    break;
                                case 7:
                                    int nationIndex = allNations.indexOf(new Nation(cellValue));
                                    employee.setNationid(allNations.get(nationIndex).getId());
                                    break;
                                case 8:
                                    employee.setNativeplace(cellValue);
                                    break;
                                case 9:
                                    int politicstatusIndex = allPoliticsstatus.indexOf(new Politicsstatus(cellValue));
                                    employee.setPoliticid(allPoliticsstatus.get(politicstatusIndex).getId());
                                    break;
                                case 10:
                                    employee.setPhone(cellValue);
                                    break;
                                case 11:
                                    employee.setAddress(cellValue);
                                    break;
                                case 12:
                                    int departmentIndex = allDepartment.indexOf(new Department(cellValue));
                                    employee.setDepartmentid(allDepartment.get(departmentIndex).getId());
                                    break;
                                case 13:
                                    int jobLevelIndex = allJobLevels.indexOf(new JobLevel(cellValue));
                                    employee.setJoblevelid(allJobLevels.get(jobLevelIndex).getId());
                                    break;
                                case 14:
                                    int positionIndex = allPositions.indexOf(new Position(cellValue));
                                    employee.setPosid(allPositions.get(positionIndex).getId());
                                    break;
                                case 15:
                                    employee.setEngageform(cellValue);
                                    break;
                                case 16:
                                    employee.setTiptopdegree(cellValue);
                                    break;
                                case 17:
                                    employee.setSpecialty(cellValue);
                                    break;
                                case 18:
                                    employee.setSchool(cellValue);
                                    break;
                                case 20:
                                    employee.setWorkstate(cellValue);
                                    break;
                                case 21:
                                    employee.setEmail(cellValue);
                                    break;
                            }
                            break;
                        default: {
                            switch (k) {
                                case 4:
                                    employee.setBirthday(cell.getDateCellValue());
                                    break;
                                case 19:
                                    employee.setBegindate(cell.getDateCellValue());
                                    break;
                                case 23:
                                    employee.setBegincontract(cell.getDateCellValue());
                                    break;
                                case 24:
                                    employee.setEndcontract(cell.getDateCellValue());
                                    break;
                                case 22:
                                    employee.setContractterm(cell.getNumericCellValue());
                                    break;
                                case 25:
                                    employee.setConversiontime(cell.getDateCellValue());
                                    break;
                            }
                        }
                        break;
                    }
                }
                list.add(employee);
            }
        }

    } catch (IOException e) {
        e.printStackTrace();
    }
    return list;
}

/**
 * 导入数据
 * @return
 */
@PostMapping("/import")
public RespBean importData(MultipartFile file) throws IOException {
    List<Employee> list = POIUtils.excel2Employee(file, nationService.getAllNations(), politicsstatusService.getAllPoliticsstatus(), departmentService.getAllDepartmentWithOutChildren(), positionService.getAllPositions(), jobLevelService.getAllJobLevels());
    for (Employee employee : list) {
        System.out.println(employee);
    }
    if (employeeService.addEmps(list) == list.size()) {
        return RespBean.ok("上传成功");
    }
    return RespBean.error("上传失败");
}

<el-upload style="display: inline-flex;margin-right: 10px"
           :show-file-list="false"
           :before-upload="beforeUpload"
           :on-success="onSuccess"
           :on-error="onError"
           :disabled="importDataDisabled"
        action="/emp/basic/import"><!--@click="importData"-->
    <el-button type="success" :disabled="importDataDisabled" :icon="importDataBtnIcon">
        {{importDataBtnText}}
    </el-button>
</el-upload>

importDataBtnText:‘导入数据‘,
importDataBtnIcon:‘el-icon-upload2‘,
importDataDisabled:false,

onError(err, file, fileList) {
    this.importDataBtnText = ‘导入数据‘;
    this.importDataBtnIcon = ‘el-icon-upload2‘;
    this.importDataDisabled = false;
},
onSuccess(response, file, fileList) {
    this.importDataBtnText = ‘导入数据‘;
    this.importDataBtnIcon = ‘el-icon-upload2‘;
    this.importDataDisabled = false;
    this.initEmployee()
},
beforeUpload() {
    this.importDataBtnText = ‘正在导入‘;
    this.importDataBtnIcon = ‘el-icon-loading‘;
    this.importDataDisabled = true;
},