POI 自定义导入Excel

Controller代码：

 @ApiOperation("批量导入排课计划")
    @PostMapping("/excelBatchImportCourse")
    public Wrapper excelBatchImportCourse(@RequestHeader("token") String token,
                                          @ApiParam(value = "公司ID",required = false) @RequestParam(value = "companyId",required = false) String companyId,
                                          @ApiParam(value = "项目ID",required = false) @RequestParam(value = "organizationId",required = false) String organizationId,
                                          @ApiParam(value = "导入的excel文件", required = true)@RequestParam("file") MultipartFile file) throws Exception {

        return courseArrangementService.excelBatchImportCourse(token,companyId,organizationId,file);
    }

Service代码：

@Override
	public Wrapper excelBatchImportCourse(String token,  String companyId, String organizationId, MultipartFile file) throws Exception {
		UacUserVo userInfo = remoteApiService.getUserInfoByToken(token);
		Wrapper wrapper = null;
		Optional.ofNullable(file).orElseThrow(() -> new BusinessException(ExceptionMsg.INCOMPLETE.getCode(),ExceptionMsg.INCOMPLETE.getMsg()));

		String[] beanProperty = {"date", "startTime", "endTime", "courseTitle", "organizationName", "peopleNum", "classPlaceName","lecturer","personOnDuty","personOnDutyPhone","personOnFjr","personOnFjrPhone"};
		//数据组装，验证基础数据问题
		List<ExportCoursPlanDto> list = null;
		try {
			list = parserExcel(token,ExportCoursPlanDto.class, file, beanProperty);
		}catch (Exception e){
			log.error("parserExcel error",e);
			return WrapMapper.error(e.getMessage());
		}

        if (!CollectionUtils.isEmpty(list)) {
		    //校验实训室当前时段是否被引用
			wrapper = checkCoursePlanIsInUser(list);
			if(wrapper.getCode() == 500){
				return wrapper;
			}
            //批量插入数据
            List<CoursePlan> coursePlanList = new ArrayList<>();
			List<CourseUser> userList = new ArrayList<CourseUser>();

			List<String> phoneList = new ArrayList<>();
			list.stream().forEach(e -> {
				String dutyPhoneSM4 = SM4Tools.encryptEcb(e.getPersonOnDutyPhone());
				phoneList.add(dutyPhoneSM4);
			});

			//根据用户手机号批量查询用户信息
			Map<String,UacUserVo> userMap = new HashMap<>();
			List<UacUserVo> userVoList = uacUserApi.getUserByPhoneList(token,phoneList);
			userVoList.stream().forEach(e -> {userMap.put(e.getTelephone(),e);});

            for (ExportCoursPlanDto exportCoursPlanDto : list) {
                CoursePlan course = new CoursePlan();
				String courseId = SnowFlake.nextStrId();
				String projectIdentify = this.getProjectIdentify(token, organizationId);
				String courseNo = this.createTaskNo(projectIdentify);

				BeanUtils.copyProperties(course, exportCoursPlanDto);
				course.setId(courseId);
				course.setCourseNo(courseNo);
				course.setCreateId(userInfo.getUserId());
				course.setStatus(CourseEnum.StatusEnum.PLAN_STATUS_0.getKey());
				course.setCompanyId(companyId);
				course.setCreateTime(new Date());
				course.setUpdateTime(new Date());
				course.setUpdateId(userInfo.getUserId());
				coursePlanList.add(course);

				//组装值班员信息
				CourseUser fjrEntity = new CourseUser();
				fjrEntity.setId(SnowFlake.nextStrId());
				fjrEntity.setCourseId(courseId);
				fjrEntity.setUserId(userMap.get(exportCoursPlanDto.getPersonOnDutyPhone()).getUserId());
				fjrEntity.setUserType(1);
				fjrEntity.setOrganizationId(organizationId);
				userList.add(fjrEntity);

            }

			if(PublicUtil.isNotEmpty(coursePlanList)) {
				coursePlanMapper.batchInsert(coursePlanList);
			}
			if(PublicUtil.isNotEmpty(userList)) {
				courseUserMapper.batchInsert(userList);
			}
        }
		return null;
	}

val 方法：

/**
	 * 校验实训室是否是有效时间段
	 * @param list
	 * @return
	 */
	public Wrapper checkCoursePlanIsInUser(List<ExportCoursPlanDto> list){
		String startTime = null;
		String endTime = null;
		StringBuilder stringBuilder = new StringBuilder();
		for (int i = 0; i < list.size(); i++) {
			ExportCoursPlanDto exportCoursPlanDto = list.get(i);
			startTime = exportCoursPlanDto.getDate() + " "+ exportCoursPlanDto.getStartTime();
			endTime = exportCoursPlanDto.getDate()+" " +exportCoursPlanDto.getEndTime();
			//根据上课地址查询出实训室id
			PracticalRoomStandBookDto record = new PracticalRoomStandBookDto();
			record.setPracticalRoomAddress(exportCoursPlanDto.getClassPlaceName());
			List<PracticalRoomStandBook> roomStandBooks = bookMapper.selectPracticalRoomStandBook(record);
			if(PublicUtil.isNotEmpty(roomStandBooks)){
				PracticalRoomStandBook roomStandBook = roomStandBooks.get(0);
				List<CoursePlanVo> plans = coursePlanMapper.selectExistsCourse(startTime, endTime, roomStandBook.getId());
				if (plans != null && plans.size()>0) {
					stringBuilder.append("第" + (i + 2) + "行，" + "实训室排课时间冲突;");
				}
			}
		}
		if(StringUtils.isNotBlank(stringBuilder.toString())){
			return WrapMapper.error(stringBuilder.toString());
		}
		return WrapMapper.ok();
	}

parserExcel

public <T> List<T> parserExcel(String token,Class<T> clazz,MultipartFile multipartFile, String[] beanPropertys) throws Exception{
        List<T> list = new ArrayList<T>();
        Workbook workbook = WorkbookFactory.create(multipartFile.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        int rowSize = sheet.getPhysicalNumberOfRows();
        if (rowSize > 1) {
            StringBuilder builder = new StringBuilder();
            for (int i = 1; i < rowSize; i++) {
                T t = clazz.newInstance();
                Row row = sheet.getRow(i);
                int cellSize = row.getPhysicalNumberOfCells();
                for (int j = 0; j < beanPropertys.length; j++) {
                    String beanProperty = beanPropertys[j];
                    Object cellValue  = ExcelUtil.getCellValue(row.getCell(j));
                    switch (beanProperty) {
                        case "date" :
                            if (cellValue == null) {
                                builder.append("第" + (i + 1) + "行，" + "日期不能为空;<br>");
                            }else{
                            	boolean isflag = valCheck(TrmConstants.REG_DATE_STR__yyyy_MM_dd,String.valueOf(cellValue));
                            	if(!isflag){
									builder.append("第" + (i + 1) + "行，" + "日期格式错误;<br>");
								}
                            }
                            break;
                        case "startTime" :
                            if (cellValue == null) {
                                builder.append("第" + (i + 1) + "行，" + "课程开始时间不能为空;<br>");
                            }else{
                            }
                            break;
                        case "endTime" :
                            if (cellValue == null) {
                                builder.append("第" + (i + 1) + "行，" + "课程结束时间不能为空;<br>");
                            }else{
                            }
                            break;
                        case "courseTitle" :
                            if (cellValue == null) {
                                builder.append("第" + (i + 1) + "行，" + "工种/课程名称不能为空;<br>");
                            }
                            break;
                        case "organizationName" :
                            if (cellValue == null) {
                                builder.append("第" + (i + 1) + "行，" + " 机构名称不能为空;<br>");
                            }else{
                            }
                            break;
                        case "peopleNum" :
                            if (cellValue == null) {
                                builder.append("第" + (i + 1) + "行，" + "上课人数不能为空;<br>");
                            }else{
                                boolean isflag = valCheck(TrmConstants.REG_STRING_NUMBER_STR,String.valueOf(cellValue));
                                if(isflag){
									builder.append("第" + (i + 1) + "行，" + "上课人数格式错误;<br>");
								}
                            }
                            break;
                        case "classPlaceName" :
                            if (cellValue == null) {
                                builder.append("第" + (i + 1) + "行，" + "上课地址全路径不能为空;<br>");
                            }else{
                                //查询上课地址
								PracticalRoomStandBookDto bookDto = new PracticalRoomStandBookDto();
								bookDto.setPracticalRoomAddress(String.valueOf(cellValue));
								List<PracticalRoomStandBook> book =  bookMapper.selectPracticalRoomStandBook(bookDto);
								if(PublicUtil.isEmpty(book)){
									builder.append("第" + (i + 1) + "行，" + "上课地址全路径没有对应的房间数据;<br>");
								}
                            }
                            break;
                        case "lecturer" :
                            if (cellValue == null) {
                                builder.append("第" + (i + 1) + "行，" + "主讲人姓名不能为空;<br>");