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

于 2023-04-21 10:47:41 发布
阅读量1.4k 收藏 11
点赞数
分类专栏： 随笔 文章标签： excel java EasyExcel
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44861708/article/details/130282611
版权

一、背景

最近有一个需求是通过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模板)

这个类有两点作用

  1. 定义到excel对应的字段
  2. 定义了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 = "行业")
    @ExcelIgnore
    private String industry;

    @ApiModelProperty(value = "部门code")
    @ExcelIgnore
    private String departmentCode;

    @ApiModelProperty(value = "项目状态code")
    @ExcelIgnore
    private String projectStatus;


    @ApiModelProperty(value = "项目类型")
    @ExcelIgnore
    private String projectType;

    @ApiModelProperty(value = "是否为SL100")
    @ExcelIgnore
    private Boolean sl100;

    @ApiModelProperty(value = "项目开始时间")
    @ExcelIgnore
    private LocalDate startDate;

    @ApiModelProperty(value = "项目结束时间")
    @ExcelIgnore
    private LocalDate endDate;
    @ExcelIgnore
    private int projectNameColumn = 0;
    @ExcelIgnore
    private int startDateStrColumn = 1;
    @ExcelIgnore
    private int endDateStrColumn = 2;
    @ExcelIgnore
    private int departmentNameColumn = 3;
    @ExcelIgnore
    private int projectTypeNameColumn = 4;
    @ExcelIgnore
    private int industryNameColumn = 5;
    @ExcelIgnore
    private int projectStatusNameColumn = 6;
    @ExcelIgnore
    private int sl100StrColumn = 7;
}

3.定义导入的Listener(ProjectExcelListener)

  1. errorUnitMap 这个map用于存储我错误单元格的行列
  2. check()方法用于校验每一个单元格（这个根据自己需要进行编写）
  3. hasError用于标注有没有错误的单元格
  4. 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.Default
    private boolean hasError = false;

    @Override
    public void invoke(ProjectExcelData data, AnalysisContext context) {
        excelDataList.add(data);
        check(data, context);
    }

    @Override
    public 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错误的单元格样式处理器

  1. RedCellWriteHandler 继承了CellWriteHandler（CellWriteHandler可以在EasyExcel官网查到）
  2. 可以再这个afterCellDispose（）方法中，处理单元格的样式
  3. 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;

    @Override
    public int order() {
        return 1000000;
    }

    @Override
    public 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);
    }
}
ぃ陌上烟雨
关注
  • 0
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
POI导入Excel并返回校验后的错误文件（原样）下载以及校验错误信息，同时加进度条
01-22
POI导入Excel并返回校验后的错误文件（原样数据文件，并添加批注，注：由于批注只能加1000条，会在Excel后面添加一栏错误信息）下载以及页面展示校验错误信息，同时添加导入进度条，提供页面js和css代码，后端controller和service,提供xml模板和Excel模板，由于项目导入地方较多，编写的是公共代码，供多处调用。（注：解析xlsx文档出错，已在前端页面js控制，项目紧，后期优化）
easyExcel-errorExport:对easyExcel的封装实现对导入错误数据背景标红批注提示导出
05-26
easyExcel的封装，实现导入数据，对错误信息添加批注提示导出 Maven 坐标 <dependency> <groupId>com.github.duanyashu</groupId> <artifactId>easyExcel-errorExport</artifactId> <version>1.1.1</version> </dependency> 使用说明 @RequestMapping("/file") @ResponseBody public ReslutData uploadFile(HttpServletResponse response,MultipartFile file){ try { // 参数1 response 错误导出使用 参数2 导入文件 参数3 导入对应的实体对象
使用easyexcel做数据校验错误返回excel表格批注+背景
MRchen009的博客
03-27 6635
使用easyexcel做数据校验错误返回excel表格批注+背景 效果入这样 想要实现,每行错误单元格,写入错误批注还是很难得,因为easyexcel在2.2.0.bate1 版本才支持写入批注. 所有还没研究能不能实现. 现在按照模板只能实现了单个(又研究了下是可以批量的) package com.alibaba.easyexcel.test.demo.write; import org.ap...
EasyExcel导入失败返回失败的文件批注+底色标识出错误数据
qq_31236849的博客
09-20 1215
EasyExcel导入失败返回失败的文件批注+底色标识出错误数据
若依导入Excel时添加必填字段校验
maoge1430的博客
07-01 5614
若依导入Excel添加必填字段校验
EasyExcel实战:实现导入文件参数校验并记录异常信息
weixin_43401380的博客
05-01 5014
亲测使用Easy Excel实现文件导入对指定字段进行参数校验完成入表处理并日志记录参数异常数据。
excel导入动态校验，自定义注解动态校验
06-09
5. **异常处理**：如果校验失败，应捕获异常并提供用户友好的错误消息，以便用户能够快速定位和修正问题。 在实际项目中，这一功能常与Java的Spring框架结合，利用其提供的`@Validated`注解和`Validator`接口。此外...
easyexcel+validation excel导入导出下载
01-19
本文将深入探讨如何结合EasyExcel和Validation实现高效且准确的Excel导入导出与数据校验。 EasyExcel的优势在于其简洁的API设计和对内存的高效利用。它无需预先加载整个Excel文件到内存，而是采用流式读写，降低了...
我的导入校验项目excel导入校验
08-31
"我的导入校验项目excel导入校验"这个标题揭示了一个专注于使用Excel文件进行数据导入，并对数据进行复杂校验的项目。在这个过程中，用户或系统会将Excel表格中的数据提取出来，经过一系列验证规则的检查，然后将...
springboot 导出excel 导入excel 生成excel 内容有点多
最新发布
03-27
导入Excel数据并将其映射为Java对象，需要创建对应的实体类，并使用`@ExcelImport`注解标记字段。例如，创建`User`类： ```java @Data public class User { @ExcelImport(name = "姓名") private String name; @...
EasyExcel新增一列数据，用于导入后标记行错误提示信息
田间稻草人
12-05 6712
EasyExcel新增一列数据，用于导入后标记行错误提示信息
导入excel，校验数据，标记错误数据
weixin_43972615的博客
07-19 1062
需求是导入excel表，后台校验数据后，把不正确（不符合需求）的数据标记成红色，返回表格下载链接（也可以返回表格），代码如下（代码不能全贴，见谅）： 大致就是这么回事，主要就是在//循环，处理数据，那里面写逻辑处理，然后正确的返回值和错误的返回值都保存一下，毕竟有的场景需要，不需要的话，也可以不加，在不符合需求的那个单元格设置下颜色就行了...
EasyExcel生成导入模板方案
BUG指挥课堂
07-30 4356
1、模板实体类 @Data public class SysUserImportExcel { private static final String bigTitle= "填写须知：\n" + "1.第1、2行为固定结构，不可更改；以下示例行，导入前请先删除\n" + "2.请严格按照填写规则输入数据，不合规的数据无法成功导入"; @ExcelProperty(value = {bigTitle,"姓名（必填）"}, index =.
easyExcel实现导入，关于每行数据的处理异常信息返回到前端
旋转丶木马的博客
05-24 5454
文章目录前言一、EasyExcel二、背景及解决主要问题导入excel校验返回文件导出总结 前言 JAVA解析Excel工具EasyExcel的初次使用 easyExcel git地址：https://github.com/alibaba/easyexcel 一、EasyExcel Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Exc
java导入excel文件校验，将错误的数据标红并下载的代码
weixin_35749796的博客
02-13 1157
用 Java 导入 Excel 文件并校验数据，将错误的数据标红并下载，需要使用一些第三方库，比如 Apache POI。下面是一个简单的代码示例： import java.io.FileInputStream; import java.io.FileOutputStream; import org.apache.poi.xssf.usermodel.XSSFCell; import org.ap...
JAVA操作Excel（POI、easyPOI、easyExcel）
我认不到你的博客
07-27 4954
一、支持文档类型不同1、Excel03：只支持xls类型的文档。2、Excel07：除了支持xls类型文档，还支持xlsx类型的文档。二、功能不同1、Excel03：将智能标记操作与部分电子表格的特定内容关联，并使适当的智能标记操作仅在用户将鼠标悬停在关联的单元格区域上时出现。2、Excel07：菜单、工具条已经成为历史，取而代之的是功能区，大量图标和命令组织到多个选项卡中，形成带状区域，还有快速访问工具栏、画廊等。三、特点不同1、Excel03：使用 Excel 2003 在任何客户定义的 XML 架构中
alibaba easyexcel 官方教程，附详细说明、例子、源码
热门推荐
akjw008的专栏
03-25 1万+
原文链接地址：https://alibaba-easyexcel.github.io/index.html 是个非常好的参考文档，比网上其他网有写的都靠谱、动态更新、详细 以下是一个写列子，建议点击目录，跳转到官方链接，体验更好 Easy Excel 首页 快速开始 常见问题（建议先阅读这个） 读Excel 写Excel 示例代码 最简单的写 根据参数只导出指定...
SpringBoot+POI实现导入Excel时验证并返回错误Cell标红的文件
BADAO_LIUMANG_QIZHI的博客
04-29 6442
场景 SpringBoot中使用POI实现Excel导入到数据库（图文教程已实践）： https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/88660466 在Excel导入到数据库后进行格式验证，如果有错误则将错误的Cell标红然后验证完成后 将标红的Excel下载到客户端。 实现 js中发送请求 //解析Excel操...
EasyExcel文件导入错误信息导出操作
zh_404的博客
08-08 1577
easyexcel
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值