EasyExcel导入失败返回失败的文件批注+底色标识出错误数据

监听器

/**
 * ExcelImportReadListener
 * 监听器
 *
 * @author 6407003092@zte.intra
 * @date 2022年09月01日 上午10:07
 */

@Slf4j
public class ExcelImportReadListener<T> extends AnalysisEventListener<T> {

    /**
     * 记录错误
     */
    public Map<Integer, Map<Integer, FailRecordEntity>> failListMap = new HashMap<>(16);

    /**
     * excel中读取的数据
     */
    public List<T> excelDates = new ArrayList<T>();


    Class clazz;


    /**
     * 批注、超链接、合并单元格信息等
     *
     * @param extra
     * @param context
     * @author 6407003092@zte.intra
     * @date 2022/9/6 下午2:26
     */
    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        super.extra(extra, context);
    }

    public ExcelImportReadListener(Class clazz) {
        super();
        this.clazz = clazz;
    }

    /**
     * 读取表头内容
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        log.info("解析到一条头数据:" + headMap.toString());
        // count 记录模板表头有几个，用以判断用户导入的表格是否和模板完全一致
        // 如果用户导入表格较模板的表头多，但其余符合模板，这样不影响则不需要
        int count = 0;
        // 获取数据实体的字段列表
        Field[] fields = clazz.getDeclaredFields();
        // 遍历字段进行判断
        for (Field field : fields) {
            // 获取当前字段上的ExcelProperty注解信息
            ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class);
            // 判断当前字段上是否存在ExcelProperty注解
            if (fieldAnnotation != null) {
                ++count;
                // 存在ExcelProperty注解则根据注解的index索引到表头中获取对应的表头名
                String headName = headMap.get(fieldAnnotation.index());
                // 判断表头是否为空或是否和当前字段设置的表头名不相同
                if (StringUtils.isEmpty(headName) || !headName.equals(fieldAnnotation.value()[0])) {
                    // 如果为空或不相同，则抛出异常不再往下执行
                    throw new ExcelAnalysisException("MRM.Excel.Import.Template.Exception");
                }
            }
        }

        // 判断用户导入表格的标题头是否完全符合模板(字段确定后打开)
      /*  if (count != headMap.size()) {
             throw new ExcelAnalysisException("MRM.Excel.Import.Template.Exception");
        }*/
    }

    /**
     * 通过class获取类字段信息
     */
    public Map<Integer, String> getIndexNameMap(Class clazz) throws NoSuchFieldException {
        Map<Integer, String> result = new HashMap<>(16);
        Field field;
        //获取类中所有的属性
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            field = clazz.getDeclaredField(fields[i].getName());
            field.setAccessible(true);
            //获取根据注解的方式获取ExcelProperty修饰的字段
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            if (excelProperty != null) {
                //索引值
                int index = excelProperty.index();
                //字段值
                String[] values = excelProperty.value();
                StringBuilder value = new StringBuilder();
                for (String v : values) {
                    value.append(v);
                }
                result.put(index, value.toString());
            }
        }
        return result;
    }

    /**
     * easyExcel 每次读取一行数据
     */
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        excelDates.add(t);
        Map<String, ExcelCellBo> propertyNameMap = getPropertyNameMap(true, analysisContext);
        //获取注解的校验结果 记录
        validate(t, propertyNameMap);
    }


    /**
     * easyExcel 每次解析完成之后调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (!CollectionUtils.isNotEmpty(excelDates)) {
            throw new ExcelAnalysisException("MRM.Excel.Import.Template.NullNum");
        }
    }

    /**
     * 在解析excel时报错时抓取的信息
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
            FailRecordEntity failRecord = new FailRecordEntity();
            failRecord.setRow(convertException.getRowIndex());
            failRecord.setColumn(convertException.getColumnIndex());
            failRecord.setMsg(exception.getCause().getMessage());
            Map<Integer, FailRecordEntity> map = new HashMap<>(16);
            map.put(convertException.getColumnIndex(), failRecord);
            failListMap.put(convertException.getRowIndex(), map);
        }
    }

    /**
     * 返回错误信息
     */
    public Map<Integer, Map<Integer, FailRecordEntity>> getFailListMap() {
        return failListMap;
    }

    /**
     * 返回数据
     */
    public List<T> getExcelDates() {
        return this.excelDates;
    }

    /**
     * 注解校验结果
     */
    private boolean validate(Object e, Map<String, ExcelCellBo> propertyNameMap) {
        boolean validateResult = true;
        Set<ConstraintViolation<Object>> validateSet =
                Validation.buildDefaultValidatorFactory().getValidator().validate(e, Default.class);
        if (validateSet != null && !validateSet.isEmpty()) {
            validateResult = false;
            Map<Integer, FailRecordEntity> map = new HashMap<>(16);
            Integer row = 0;
            for (ConstraintViolation<Object> constraint : validateSet) {
                Path propertyPath = constraint.getPropertyPath();
                String propertyName = propertyPath.toString();
                ExcelCellBo bo = propertyNameMap.get(propertyName);
                FailRecordEntity failRecord = new FailRecordEntity();
                failRecord.setHeadName(bo.getHeadName());
                row = bo.getRowIndex();
                Object invalidValue = constraint.getInvalidValue();
                if (invalidValue != null) {
                    failRecord.setValue(invalidValue.toString());
                } else {
                    failRecord.setValue(null);
                }
                failRecord.setColumn(bo.getColumnIndex());
                failRecord.setRow(row);
                failRecord.setMsg(constraint.getMessage());
                map.put(bo.getColumnIndex(), failRecord);
            }
            failListMap.put(row, map);
        }
        return validateResult;
    }

    /**
     * 分析当前上下文 获取行列号
     */
    private Map<String, ExcelCellBo> getPropertyNameMap(boolean isSingleHeader, AnalysisContext analysisContext) {
        Map<String, ExcelCellBo> propertyNameMap = new HashMap<>(16);
        ReadRowHolder readRowHolder = analysisContext.readRowHolder();
        Integer rowIndex = readRowHolder.getRowIndex();
        ReadHolder readHolder = analysisContext.currentReadHolder();
        ExcelReadHeadProperty excelReadHeadProperty = readHolder.excelReadHeadProperty();
        Collection<ExcelContentProperty> values;
        if (isSingleHeader) {
            Map<Integer, ExcelContentProperty> contentPropertyMap = excelReadHeadProperty.getContentPropertyMap();
            values = contentPropertyMap.values();
        } else {
            //也适用于单行表头
            Map<String, ExcelContentProperty> fieldNameContentPropertyMap =
                    excelReadHeadProperty.getFieldNameContentPropertyMap();
            values = fieldNameContentPropertyMap.values();
        }
        ExcelCellBo bo;
        for (ExcelContentProperty contentProperty : values) {
            bo = new ExcelCellBo();
            bo.setRowIndex(rowIndex);
            bo.setColumnIndex(contentProperty.getHead().getColumnIndex());
            bo.setFieldName(contentProperty.getHead().getFieldName());
            //多行表头
            bo.setHeadName(String.join(",", contentProperty.getHead().getHeadNameList()));
            bo.setField(contentProperty.getField());
            propertyNameMap.put(contentProperty.getHead().getFieldName(), bo);
        }
        return propertyNameMap;
    }

    @Data
    public class ExcelCellBo {
        private Field field;
        private String fieldName;
        private String headName;
        private Integer columnIndex;
        private Integer rowIndex;
    }
}

2:FailRecordEntity3：

@Data
public class FailRecordEntity {
    /**
     *
     */
    private Integer row;
    /**
     *
     */
    private Integer column;
    /**
     *
     */
    private String headName;
    /**
     *
     */
    private String msg;
    /**
     *
     */
    private String value;
}

3 导出错误Excel的实现类

/**
 * 导入文件数据校验错误返回原Excel给用户实现类
 *
 * @date 2022年09月08日 上午10:19
 */
@Slf4j
@Component
public class ExcelImportErrExportMangerImpl implements ExcelImportErrExportManger {
    @Autowired
    private MessageSource messageSource;

    /**
     * 返回excel
     *
     * @param inputStream 文件流
     * @param failListMap 错误的数据
     * @param response    响应体
     */
    public void downloadExcel(MultipartFile file, Map<Integer, Map<Integer, FailRecordEntity>> failListMap,
                              HttpServletResponse response) throws IOException {
        OutputStream osOut = null;
        try {
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            compileExcel(workbook, failListMap);
            String fileName = file.getOriginalFilename().substring(0,file.getOriginalFilename().indexOf("."));
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 形成输出流
            osOut = response.getOutputStream();
            // 将指定的字节写入此输出流
            workbook.write(osOut);
            // 刷新此输出流并强制将所有缓冲的输出字节被写出
            osOut.flush();
            // 关闭流
            osOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (osOut != null) {
                osOut.close();
            }
        }
    }

    /**
     * 编辑excel
     *
     * @param workbook    workbook
     * @param failListMap 错误数据
     */
    private void compileExcel(Workbook workbook, Map<Integer, Map<Integer, FailRecordEntity>> failListMap) {
        Sheet sheet = workbook.getSheetAt(1);
        Set<Integer> rows = failListMap.keySet();
        //初始化所有字体颜色
        initFontColor(workbook, sheet);
        for (Integer row : rows) {
            Row row1 = sheet.getRow(row);
            Map<Integer, FailRecordEntity> map = failListMap.get(row);
            Set<Integer> cloumns = map.keySet();
            for (Integer cloumn : cloumns) {
                Cell cell = row1.getCell(cloumn);
                if (cell == null) {
                    Cell cell1 = row1.createCell(cloumn);
                    fillStyle(cell1, workbook, sheet, row, cloumn, map);
                } else {
                    fillStyle(cell, workbook, sheet, row, cloumn, map);
                }
            }
        }
    }

    /**
     * 初始化所有字体颜色
     */
    private void initFontColor(Workbook workbook, Sheet sheet) {
        Cell cell2 = sheet.getRow(2).getCell(0);
        CellStyle cellStyle = sheet.getRow(2).getCell(0).getCellStyle();
        //设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        //设置字体为正常
        font.setColor(Font.COLOR_NORMAL);
        cellStyle.setFont(font);
        cell2.setCellStyle(cellStyle);
    }

    /**
     * 填充错误的颜色和批注样式
     */
    private void fillStyle(Cell cell, Workbook workbook, Sheet sheet, Integer row, Integer cloumn, Map<Integer,
            FailRecordEntity> map) {
        cell.setCellValue(map.get(cloumn).getValue());
        CellStyle cellStyle = workbook.createCellStyle();
        //设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置背景色是红色
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        //背景色填充效果充满整个单元格
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置字体
        Font font = workbook.createFont();
        cellStyle.setFont(font);
        Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0,
                (short) (row + 0), cloumn, (short) (row + 1), cloumn + 1));
        try {
            comment.setString(new XSSFRichTextString(messageSource.getMessage(map.get(cloumn).getMsg(), null, LocaleContextHolder.getLocale())));
        } catch (Exception e) {
            comment.setString(new XSSFRichTextString(map.get(cloumn).getMsg()));
        }
        cell.setCellComment(comment);
        cell.setCellStyle(cellStyle);
    }

 4调用

excelImportErrExportManger.downloadExcel(file, failListMap, ThreadLocalUtil.get("response"));

5读取模板

 

 EasyExcelListener easyExcelListener = new EasyExcelListener(messageSource,dto);
            excelReader = EasyExcel.read(file.getInputStream(), dto, easyExcelListener).build();
            excelReader.read(EasyExcel.readSheet(1).build());