网站首页 > 博客文章正文

EasyExcel实现Excel验证标红备注下载

baijin 2024-09-29 08:57:20 博客文章 80 ℃ 0 评论

1.前言

在项目需求中产品小姐姐时常需要将上传的excel一次验证完成将不符合需求的单元格标红，以前也没有人去这样写过，所以基本上都推翻了这种实现方式，采用有错误就返回弹框提示的方式（可能是觉得用POI要完成这种方式比较耗费时间）。所以利用业余时间，也算是加深一下easyexcel，用easyexcel简单地实现了一个。不废话直接上代码。

2.依赖

<dependency>
    <groupId>org.hibernate.validator</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>6.1.5.Final</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

3.Java代码实现

@Data
public class ExcelBo implements Serializable {
    @ExcelProperty(index = 0)
    @NotBlank(message = "商家编码不能为空")
    private String venderId;

    @ExcelProperty(index = 1)
    @NotBlank(message = "商品名称不能为空")
    private String skuName;

}

上面的实体类用于接受excel内容，也就两个字段。并使用hibernate validator进行验证申明。

这里我使用easyexcel的监听器来实现数据验证机数据的读取工作，因为需要进行全部数据验证，所以这种方式不怎么适合数据量较大的excel文件验证，具体多大未进行验证。

@Slf4j
public abstract class AbstractExcelListener<T> extends AnalysisEventListener<T> {
    private List<ExcelCellValidateResultBo> errors = Lists.newArrayList();
    private List<T> results = Lists.newArrayList();

    private ImmutableBiMap<Integer, String> headerMap = ImmutableBiMap.of();

    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        log.info("获取头信息 {}", JSON.toJSONString(headMap));

        headerMap = ImmutableBiMap.copyOf(headMap);
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        System.out.println("------------- invoke -----------------------");

        final List<ExcelCellValidateResultBo> validateResult = getValidateResults(data, context);
        if (validateResult != null) {
            errors.addAll(validateResult);
        }
        results.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("------------- doAfterAllAnalysed -----------------------");
    }

    public List<ExcelCellValidateResultBo> getErrors() {
        return errors;
    }

    /**
     * 获取验证结果
     *
     * @param rowData
     * @param context
     * @return
     */
    public abstract List<ExcelCellValidateResultBo> getValidateResults(Object rowData, final AnalysisContext context);

    public List<T> getResults() {
        return results;
    }

    public ImmutableBiMap<Integer, String> getHeaderMap() {
        return headerMap;
    }

    /**
     * 是否验证通过
     *
     * @return
     */
    public boolean isValidation() {
        return CollectionUtils.isEmpty(errors) ? true : false;
    }

    public void outputFaultExcel(String name, Class clazz, HttpServletResponse response) throws Exception {
        if (StringUtils.isBlank(name)) {
            name = "default";
        }

        Assert.notNull(clazz, "导出实体类不能为空");
        Assert.notNull(response, "响应不能为空");

        if (response == null) {
            return;
        }
        if (isValidation()) {
            log.warn("无异常需要导出");
            return;
        }

        response.setContentType("application/octet-stream");
        response.addHeader("Content-Disposition", "attachment;fileName=" + new String(name.getBytes("UTF-8"), "ISO-8859-1"));

        final ServletOutputStream out = response.getOutputStream();

        final ImmutableTable<Integer, Integer, ExcelCellValidateResultBo> cellFaultTable = errors.stream()
                .collect(ImmutableTable.toImmutableTable(ExcelCellValidateResultBo::getRowIndex, ExcelCellValidateResultBo::getColumnIndex, t1 -> t1, (t1, t2) -> {
                    final String message1 = t1.getMessage();
                    final String message2 = t2.getMessage();
                    if (message1.indexOf(message2) < 0) {
                        final String join = String.join(";", message1, message2);
                        t1.setMessage(join);
                    }
                    return t1;
                }));

        EasyExcel.write(out, clazz).registerWriteHandler(new XXXWriteHandler(cellFaultTable)).head(getHeaders()).sheet().doWrite(results);
        out.flush();
        response.getOutputStream().close();
    }

    public void outputFaultExcel(String name, Class clazz, Resource template, HttpServletResponse response) throws Exception {
        if (StringUtils.isBlank(name)) {
            name = "default";
        }

        Assert.notNull(clazz, "导出实体类不能为空");
        Assert.notNull(response, "响应不能为空");
        Assert.notNull(template, "模板不能为空");

        if (isValidation()) {
            log.warn("无异常需要导出");
            return;
        }

        response.setContentType("application/octet-stream");
        response.addHeader("Content-Disposition", "attachment;fileName=" + new String(name.getBytes("UTF-8"), "ISO-8859-1"));

        final ServletOutputStream out = response.getOutputStream();

        final ImmutableTable<Integer, Integer, ExcelCellValidateResultBo> cellFaultTable = errors.stream()
                .collect(ImmutableTable.toImmutableTable(ExcelCellValidateResultBo::getRowIndex, ExcelCellValidateResultBo::getColumnIndex, t1 -> t1, (t1, t2) -> {
                    final String message1 = t1.getMessage();
                    final String message2 = t2.getMessage();
                    if (message1.indexOf(message2) < 0) {
                        final String join = String.join(";", message1, message2);
                        t1.setMessage(join);
                    }
                    return t1;
                }));

        ExcelWriter excelWriter = EasyExcel.write(out).registerWriteHandler(new XXXWriteHandler(cellFaultTable))
                .withTemplate(template.getInputStream()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();

        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(results, fillConfig, writeSheet);
        excelWriter.finish();
    }

    private List<List<String>> getHeaders() {
        final int size = headerMap.size();
        List<List<String>> exportHeaders = Lists.newArrayListWithCapacity(size);
        for (int i = 0; i < size; i++) {
            exportHeaders.add(i, Lists.newArrayList(headerMap.get(i)));
        }

        return exportHeaders;
    }
}

同时我们需要一个处理器在下载的时候标红错误单元框并添加备注信息：

public class XXXWriteHandler extends AbstractCellStyleStrategy {
    private ImmutableTable<Integer, Integer, ExcelCellValidateResultBo> cellFaultTable;

    public XXXWriteHandler(ImmutableTable<Integer, Integer, ExcelCellValidateResultBo> cellFaultTable) {
        this.cellFaultTable = cellFaultTable;
    }

    @Override
    protected void initCellStyle(Workbook workbook) {

    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {

    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        final int columnIndex = cell.getColumnIndex();
        final int rowIndex = cell.getRowIndex();

        final ExcelCellValidateResultBo fault = cellFaultTable.get(rowIndex, columnIndex);
        if (fault == null) {
            return;
        }

        final Sheet sheet = cell.getSheet();
        final CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
        cellStyle.setBorderTop(BorderStyle.MEDIUM);
        cellStyle.setBorderBottom(BorderStyle.MEDIUM);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);
        cellStyle.setBorderRight(BorderStyle.MEDIUM);

        cellStyle.setTopBorderColor((short) 10);
        cellStyle.setBottomBorderColor((short) 10);
        cellStyle.setLeftBorderColor((short) 10);
        cellStyle.setRightBorderColor((short) 10);

        cell.setCellStyle(cellStyle);

        final Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)columnIndex, rowIndex, (short)columnIndex, rowIndex));
        // 输入批注信息
        comment.setString(new XSSFRichTextString(fault.getMessage()));
        cell.setCellComment(comment);

        System.out.println("设置样式成功");
    }
}

其中ExcelCellValidateResultBo对象是用来存放单元格错误信息：

@Data
@AllArgsConstructor
public class ExcelCellValidateResultBo {
    // 单元格所在行
    private int rowIndex;
    // 单元格列
    private int columnIndex;
    // 错误信息
    private String message;
}

上面的抽象类提供了一个抽象方法public abstract List<ExcelCellValidateResultBo> getValidateResults(Object rowData, final AnalysisContext context);主要是方便个人实现复杂的验证塞入结果。

缺省实现监听：

public class DefaultExcelListener<T> extends AbstractExcelListener<T> {
    @Override
    public List<ExcelCellValidateResultBo> getValidateResults(Object rowData, AnalysisContext context) {
        final Validator validator = SpringContextUtil.getBean(Validator.class);

        if (validator == null) {
            log.error("获取验证器失败");
            return null;
        }

        final Set<ConstraintViolation<Object>> validates = validator.validate(rowData, Default.class);
        if (CollectionUtils.isEmpty(validates)) {
            return null;
        }

        final Integer rowIndex = context.readRowHolder().getRowIndex();
        return validates.stream().map(va -> {
            final String fieldName = va.getPropertyPath().toString();
            final String message = va.getMessage();

            final Integer fieldCellIndex = getFieldCellIndex(fieldName, rowData);
            final ExcelCellValidateResultBo excelCellValidateResultBo = new ExcelCellValidateResultBo(rowIndex, fieldCellIndex, message);
            return excelCellValidateResultBo;
        }).collect(Collectors.toList());
    }

    private Integer getFieldCellIndex(String fieldName, Object rowData) {
        if (rowData == null) {
            return -1;
        }
        final Class<?> aClass = rowData.getClass();
        try {
            final Field declaredField = aClass.getDeclaredField(fieldName);
            final ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            final int index = annotation.index();
            if (index != -1) {
                return index;
            }

            final String[] headerNames = annotation.value();
            if (headerNames.length == 0) {
                return -1;
            }
            return getHeaderMap().inverse().get(headerNames[0]);
        } catch (Exception e) {
            log.error("获取下标异常");
            return -1;
        }

    }
}

验证器：

@Configuration
public class ValidatorConfig {
    @Bean
    public Validator validator() {
        final ValidatorFactory validatorFactory = Validation.byProvider(HibernateValidator.class)
                .configure()
                .addProperty("hibernate.validator.fail_fast", "false")
                .buildValidatorFactory();

        return validatorFactory.getValidator();
    }
}

服务类：

public HttpResult upload(MultipartFile file, org.springframework.core.io.Resource template, HttpServletResponse response) throws IOException {
        final DefaultExcelListener excelListener = new DefaultExcelListener<ExcelBo>();
        EasyExcel.read(file.getInputStream(), ExcelBo.class, excelListener)
                .sheet().doRead();

        if (!excelListener.isValidation()) {
            try {
                excelListener.outputFaultExcel(file.getName() + ".xlsx", UserSetCategoryExcelBo.class, template, response);
            } catch (Exception e) {
                log.error("导出异常 {}", e.getMessage(), e);
            }
            // 避免返回内容先于异常文件到达前端，导致页面显示成功
            return null;
        }

        final List<UserSetCategoryExcelBo> excelBos = excelListener.getResults();

        log.info("excel数据长度 {}", excelBos.size());
  
  		  // todo 业务逻辑

        return HttpResult.success();
    }

@PostMapping("upload")
    public HttpResult upload(MultipartFile file, HttpServletResponse response) {
        try {
            org.springframework.core.io.Resource template = resourceLoader.getResource("classpath:exportTemplate/template.xlsx");
            return userSetCategoryService.upload(file,template, response);
        } catch (Exception e) {
            log.error("上传用户设置类目失败 {}", e.getMessage(), e);
            return HttpResult.failure();
        }
    }

因为使用模板方式导出异常数据，所以这里会加载模板，可以看一下easyexcel模板导出，当然也可以不使用模板导出，直接使用对象进行导出即可。

前端

前端虽然使用的是vue，但是前端搭建的同学对于请求这块还是热衷于原生的ajax请求代码，所以我们这里的前端也是基于这个进行修改：

要实现前端异常导出excel，导入成功正确提示，主要使用的方式是设置ajax的responseType为blob类型来接收后端返回数据，收到数据后尝试将blob转成json，转化成功就直接使用json，转换失败就档成excel文件导出的方式来兼容两种情况：

后端json，处理json
后端excel，下载excel

这样省去了弹窗等麻烦的事情。

这里只贴出核心的代码：

var xhr = new XMLHttpRequest();
xhr.withCredentials = true;
xhr.responseType = 'blob';
xhr.timeout = timeout;
xhr.onload = function () {
  if (xhr.status === 200) {
    let response = xhr.response;
    let reader = new FileReader();
    reader.addEventListener("loadend", function () {
      try {
        let res = JSON.parse(reader.result);
        resolve(res)
      } catch (e) {
        resolve(xhr.response);
      }
    });
    reader.readAsText(response, 'utf-8');
  } else {
    // reject(new Error(xhr.response.msg || xhr.response.message || xhr.response.result));
  }
};

resolve将回调下面的代码：

// 我们这边json响应中都有code字段，如果没有就认为是文件，也可以判断是不是blob类型
if (!result.code) {
  // type要和后台写的类型一致，避免导出文件乱码等情况
  let type = "application/octet-stream";
  let blob = new Blob([result], {type: type});

  let link = document.createElement("a");
  link.href = window.URL.createObjectURL(blob);
  link.download = '异常数据.xlsx';
  link.click();
} else {
  // 使用自己的弹窗方式
  this.miniPopShow("上传成功");
}

总结

上面的实现只是测试使用，意在抛砖引玉，提供一种思路。最后感谢大家能看到这里。

上一篇： Excel和Python终于可以互通了（python怎么与excel衔接）
下一篇：太棒了!Python和Excel过了这么久终于可以互通了

网站首页 > 博客文章正文

EasyExcel实现Excel验证标红备注下载

1.前言

2.依赖

3.Java代码实现

前端

总结

猜你喜欢

本文暂时没有评论，来添加一个吧(●'◡'●)

取消回复欢迎你发表评论:

网站首页 > 博客文章 正文

EasyExcel实现Excel验证标红备注下载

1.前言

2.依赖

3.Java代码实现

前端

总结

猜你喜欢

本文暂时没有评论，来添加一个吧(●'◡'●)

取消回复欢迎 你 发表评论:

网站首页 > 博客文章正文

取消回复欢迎你发表评论: