导入Excel时校验内容并将错误的单元格标红，生成标记错误的excel提供给用户下载

一、背景

最近有一个需求是通过excel导入数据，然后经过校验，将错误的数据所在的单元格标红，然后生成一个新的标记错误的excel提供给用户下载。

二、技术选型

阿里推出的EasyExcel
链接地址：https://easyexcel.opensource.alibaba.com/docs/current/

三、代码如下

1、编写上传接口

@ApiOperation("上传(贾：已完成)")@PostMapping("/upload")public Result upload(@RequestParam("file") MultipartFile file) throws IOException {// 这是我前期的一些准备，是供校验数据用的Map<String, String> projectStatusNameCodeMap = dictItemService.nameCodeMap(ProjectManageConstants.PROJECT_STATUS);Map<String, String> projectTypeNameCodeMap = dictItemService.nameCodeMap(ProjectManageConstants.PROJECT_TYPES);Map<String, String> industryNameCodeMap = dictItemService.nameCodeMap(ProjectManageConstants.INDUSTRY);Set<String> projectNameSet = pjProjectService.projectNameSet();// ProjectExcelListener 是用来对上传的excel进行解析的// ProjectExcelData是我上传的excel的模板类ProjectExcelListener projectExcelListener = ProjectExcelListener.builder().CurrentErp(erp).projectStatusNameCodeMap(projectStatusNameCodeMap).projectTypeNameCodeMap(projectTypeNameCodeMap).industryNameCodeMap(industryNameCodeMap).projectNameSet(projectNameSet).projectService(pjProjectService).organizationNameCodeMap(organizationNameCodeMap).ossService(ossService).build();EasyExcel.read(file.getInputStream(), ProjectExcelData.class, projectExcelListener).sheet().doRead();return Result.success();}

2.ProjectExcelData(我定义的导入excel模板)

这个类有两点作用

定义到excel对应的字段
定义了excel对应字段的列的位置，就是下边有column的成员变量，excel的列是从0开始的

@Data
@HeadRowHeight(20)
@ColumnWidth(25)
@ContentRowHeight(20)
public class ProjectExcelData {@ApiModelProperty(value = "项目名称")@ExcelProperty("项目名称(必填)")private String projectName;@ApiModelProperty(value = "项目开始时间")@ExcelProperty("项目开始时间(必填)")private String startDateStr;@ApiModelProperty(value = "项目结束时间")@ExcelProperty("项目结束时间(必填)")private String endDateStr;@ApiModelProperty(value = "部门名称")@ExcelProperty("交付部门(必填)")private String departmentName;@ExcelProperty("项目类型(必填)")private String projectTypeName;@ExcelProperty("行业类型(必填)")private String industryName;@ApiModelProperty(value = "是否为SL100")@ExcelProperty("SL100项目(必填)")private String sl100Str;@ApiModelProperty(value = "项目简介")@ExcelProperty("备注")private String projectDesc;@ApiModelProperty(value = "项目状态名称")@ExcelProperty("项目状态(必填)")private String projectStatusName;@ApiModelProperty(value = "主项目经理，用户的erp账号")@ExcelProperty("主项目经理")private String pm;@ApiModelProperty(value = "行业")@ExcelIgnoreprivate String industry;@ApiModelProperty(value = "部门code")@ExcelIgnoreprivate String departmentCode;@ApiModelProperty(value = "项目状态code")@ExcelIgnoreprivate String projectStatus;@ApiModelProperty(value = "项目类型")@ExcelIgnoreprivate String projectType;@ApiModelProperty(value = "是否为SL100")@ExcelIgnoreprivate Boolean sl100;@ApiModelProperty(value = "项目开始时间")@ExcelIgnoreprivate LocalDate startDate;@ApiModelProperty(value = "项目结束时间")@ExcelIgnoreprivate LocalDate endDate;@ExcelIgnoreprivate int projectNameColumn = 0;@ExcelIgnoreprivate int startDateStrColumn = 1;@ExcelIgnoreprivate int endDateStrColumn = 2;@ExcelIgnoreprivate int departmentNameColumn = 3;@ExcelIgnoreprivate int projectTypeNameColumn = 4;@ExcelIgnoreprivate int industryNameColumn = 5;@ExcelIgnoreprivate int projectStatusNameColumn = 6;@ExcelIgnoreprivate int sl100StrColumn = 7;
}

3.定义导入的Listener(ProjectExcelListener)

errorUnitMap 这个map用于存储我错误单元格的行列
check()方法用于校验每一个单元格（这个根据自己需要进行编写）
hasError用于标注有没有错误的单元格
RedCellWriteHandler 自定义的单元格样式处理器（我主要是将错误的单元格字体标红）

@Builder
public class ProjectExcelListener implements ReadListener<ProjectExcelData> {private String CurrentErp;private Map<String, String> projectStatusNameCodeMap;private Map<String, String> projectTypeNameCodeMap;private Map<String, String> industryNameCodeMap;private Set<String> projectNameSet;private IPjProjectService projectService;private Map<String, String> organizationNameCodeMap;private final Map<Integer, HashSet<Integer>> errorUnitMap = new HashMap<>();private final List<ProjectExcelData> excelDataList = new ArrayList<>();private IOSSService ossService;private final String YES = "是";private final String NO = "否";@Builder.Defaultprivate boolean hasError = false;@Overridepublic void invoke(ProjectExcelData data, AnalysisContext context) {excelDataList.add(data);check(data, context);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {if (hasError) {File file = new File("项目" + LocalDateTime.now() + ".xlsx");FileOutputStream fileOutputStream = null;FileInputStream fileInputStream = null;MultipartFile multipartFile = null;try {fileOutputStream = new FileOutputStream(file);// 生成新的excel// RedCellWriteHandler对新的excel进行个性化样式处理EasyExcel.write(fileOutputStream, ProjectExcelData.class).registerWriteHandler(new RedCellWriteHandler(errorUnitMap)).sheet().doWrite(excelDataList);fileInputStream = new FileInputStream(file);multipartFile = new MockMultipartFile(file.getName(), file.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);} catch (Exception e) {throw new RuntimeException(e);}// 调用封装的oss上传接口进行文件上传ossService.upload( multipartFile);throw new BadRequestException(ResultEnum.EXCEL_UPLOAD_FAIL, multipartFile.getName());} else {//把数据存储数据库projectService.batchSave(ProjectExcelDataEntityMapper.INSTANCE.toEntity(excelDataList));}}private void check(ProjectExcelData projectExcelData, AnalysisContext analysisContext) {Integer row = analysisContext.readRowHolder().getRowIndex();errorUnitMap.put(row, new HashSet<>());// 校验名称if (StrUtil.isBlank(projectExcelData.getProjectName())) {hasError = true;projectExcelData.setProjectName("(项目名称不能为空)");errorUnitMap.get(row).add(projectExcelData.getProjectNameColumn());} else if (projectNameSet.contains(projectExcelData.getProjectName())) {hasError = true;projectExcelData.setProjectName(projectExcelData.getProjectStatusName() + "(该项目已存在)");errorUnitMap.get(row).add(projectExcelData.getProjectNameColumn());} else {projectNameSet.add(projectExcelData.getProjectName());}// 校验开始时间if (StrUtil.isEmpty(projectExcelData.getStartDateStr())) {hasError = true;projectExcelData.setStartDateStr("项目开始时间不能为空");errorUnitMap.get(row).add(projectExcelData.getStartDateStrColumn());} else if (!DateFormatUtil.isLegalDate(projectExcelData.getStartDateStr().length(), projectExcelData.getStartDateStr(), DateFormatUtil.YYYY_MM_DD)) {hasError = true;errorUnitMap.get(row).add(projectExcelData.getStartDateStrColumn());} else {projectExcelData.setStartDate(LocalDate.parse(projectExcelData.getStartDateStr()));}// 校验结束时间if (StrUtil.isEmpty(projectExcelData.getEndDateStr())) {hasError = true;projectExcelData.setEndDateStr("项目开始时间不能为空");errorUnitMap.get(row).add(projectExcelData.getEndDateStrColumn());} else if (!DateFormatUtil.isLegalDate(projectExcelData.getEndDateStr().length(), projectExcelData.getEndDateStr(), DateFormatUtil.YYYY_MM_DD)) {hasError = true;errorUnitMap.get(row).add(projectExcelData.getEndDateStrColumn());} else {projectExcelData.setEndDate(LocalDate.parse(projectExcelData.getEndDateStr()));}// 校验交付部门if (organizationNameCodeMap.containsKey(projectExcelData.getDepartmentName())) {projectExcelData.setDepartmentCode(organizationNameCodeMap.get(projectExcelData.getDepartmentName()));} else {hasError = true;projectExcelData.setDepartmentName(projectExcelData.getDepartmentName() + "(无该部门)");errorUnitMap.get(row).add(projectExcelData.getDepartmentNameColumn());}// 校验项目类型if (projectTypeNameCodeMap.containsKey(projectExcelData.getProjectTypeName())) {projectExcelData.setProjectType(projectTypeNameCodeMap.get(projectExcelData.getProjectTypeName()));} else {hasError = true;projectExcelData.setProjectTypeName(projectExcelData.getProjectTypeName() + "(无该项目类型)");errorUnitMap.get(row).add(projectExcelData.getProjectTypeNameColumn());}// 校验行业类型if (industryNameCodeMap.containsKey(projectExcelData.getIndustryName())) {projectExcelData.setIndustry(industryNameCodeMap.get(projectExcelData.getIndustryName()));} else {hasError = true;projectExcelData.setIndustryName(projectExcelData.getIndustry() + "(无该行业类型)");errorUnitMap.get(row).add(projectExcelData.getIndustryNameColumn());}// 校验SL100项目if (ObjectUtil.notEqual(YES, projectExcelData.getSl100Str()) && ObjectUtil.notEqual(NO, projectExcelData.getSl100Str())) {hasError = true;errorUnitMap.get(row).add(projectExcelData.getSl100StrColumn());} else {projectExcelData.setSl100(ObjectUtil.equals(YES, projectExcelData.getSl100Str()));}}
}

4.RedCellWriteHandler错误的单元格样式处理器

RedCellWriteHandler 继承了CellWriteHandler（CellWriteHandler可以在EasyExcel官网查到）
可以再这个afterCellDispose（）方法中，处理单元格的样式
order()方法必须设置，这个相当于你设置的样式的层级，easyExcel设置样式的层级是5000，所有你设置的order一定要大于5000，不然你设置的样式可能被覆盖

@Slf4j
public class RedCellWriteHandler implements CellWriteHandler {public RedCellWriteHandler(Map<Integer, HashSet<Integer>> unitMap) {this.unitMap = unitMap;}private  Map<Integer, HashSet<Integer>> unitMap;@Overridepublic int order() {return 1000000;}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {Workbook workbook = writeSheetHolder.getSheet().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();//根据校验结果设置单元格文字颜色unitMap.forEach((key, value) -> {if (key.equals(cell.getRowIndex()) && value.contains(cell.getColumnIndex())) {//设置单元格背景色Font font = workbook.createFont();font.setColor(IndexedColors.RED.getIndex());//设置背景色cellStyle.setFont(font);}});cell.setCellStyle(cellStyle);}
}