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