EasyPoi导出导入(带校验)简单示例 EasyExcel

官方文档 : http://doc.wupaas.com/docs/easypoi

pom的引入:

<!--        easyPoi-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>5.4.1.Final</version>
        </dependency>
<!--        hutool-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-core</artifactId>
            <version>5.7.7</version>
        </dependency>

1.建立一个对象体

@Data
public class EasyPoiEntity {
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄")
    private int age;
    @Excel(name = "地址")
    private String addr;

}

2.导出代码

    @GetMapping("/exportExcel")
    public void test(HttpServletRequest request, HttpServletResponse response)throws IOException {
    //模拟数据  实际基本查数据
        EasyPoiEntity easyPoiEntity = new EasyPoiEntity();
        easyPoiEntity.setName("张三");
        easyPoiEntity.setAddr("中国");
        easyPoiEntity.setAge(18);
        List<EasyPoiEntity> list = new ArrayList<>();
        list.add(easyPoiEntity);
        //2.封装成表格
        //参数1：表格标题，参数2：sheet名称
//        ExportParams exportParams = new ExportParams("学生信息", "1班学生信息");
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("学生信息");
        exportParams.setStyle(EasyExcelStyleType.ONE.getCla());
        //参数1：表格参数  参数2：实体类  参数3：数据
        Workbook sheets = ExcelExportUtil.exportExcel(exportParams, EasyPoiEntity.class, list);

        //3.返回表格
        //设置表格文件名字
        // 生成时间戳
        String timestamp = DateUtil.format(LocalDateTime.now(), "yyyyMMddHHmmss");
        String fileName = "一班学生数据-"+timestamp;
        fileName = URLEncoder.encode(fileName,"UTF8");
        //设置返回数据类型
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");

        //将表格输出
        sheets.write(response.getOutputStream());

    }

导出Excel
在这里插入图片描述

导入例子

1.对象加上校验注解实现 IExcelDataModel, IExcelModel

@Data
public class EasyPoiEntity implements IExcelDataModel, IExcelModel {
    @Excel(name = "姓名")
    @Pattern(regexp = "[\u4E00-\u9FA5]*", message = "不是中文")
    private String name;
    @Max(value = 15,message = "max 最大值不能超过15")
    @Excel(name = "年龄")
    private int age;
    @NotNull(message = "地址不能为空")
    @Excel(name = "地址")
    private String addr;

    /**
     * 行号
     */
    private int rowNum;

    /**
     * 错误消息
     */
    private String errorMsg;

}

导入代码 params.setVerifyHandler(excelVerifyInfo);看后面

    @PostMapping("/importExcel")
    @ResponseBody
    public Object importUser(@RequestParam("uploadFile") MultipartFile multipartFile,HttpServletResponse resp) {
        ImportParams params = new ImportParams();

        /**
         * 这里需要注意表头的行数设置一定要正确！否则集合数据将无法读取，
         * 可以通过WPS或者office查看实际表头所占用的行数，
         * 一定要区分表头与标题的区别，表头是列名称，标题是表头上面的文字，
         * 本文示例文件中没有标题，所以setTitleRows为0
         */
        // 设置表头行数
        params.setHeadRows(1);
        // 标题行设置为0行，默认是0，可以不设置
        params.setTitleRows(0);
//        代表导入这里是需要验证的（根据字段上的注解校验）
        params.setNeedVerify(true);
        //设及一个自定义校验 （自定义校验名字不可重复）
        params.setVerifyHandler(excelVerifyInfo);
        try {
            ExcelImportResult<EasyPoiEntity> importResult = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(), EasyPoiEntity.class, params);
            boolean verfiyFail = importResult.isVerfiyFail();
            System.out.println(verfiyFail);
            System.out.println(importResult.getFailList().toString());
            System.out.println(importResult.getList().toString());

            //验证是否有失败的数据
            if (importResult.isVerfiyFail()) {
                ServletOutputStream fos = resp.getOutputStream();
                //mime类型
                resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                // 生成时间戳
                String timestamp = DateUtil.format(LocalDateTime.now(), "yyyyMMddHHmmss");

                resp.setHeader("Content-disposition", "attachment;filename=" + timestamp + "-error.xlsx");
                importResult.getFailWorkbook().write(fos);
                fos.close();
            }
            return importResult.getList();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

导入Excel例子
在这里插入图片描述
返回的异常Excel

对于想自定义异常的自己写一个实现类实现 IExcelVerifyHandler<>即可
代码如下:

@Component
public class  ExcelVerifyInfo implements IExcelVerifyHandler<EasyPoiEntity> {
    private final ThreadLocal<List<EasyPoiEntity>> threadLocal = new ThreadLocal<>();
    @Override
    public ExcelVerifyHandlerResult verifyHandler(EasyPoiEntity easyPoiEntity) {
        StringJoiner joiner = new StringJoiner(",");
        List<EasyPoiEntity> threadLocalVal = threadLocal.get();
        if (threadLocalVal == null) {
            threadLocalVal = new ArrayList<>();
        }

        /**
         * 所有的自定义校验 可以再这里写 比如查数据 比较之类
         */
//        if (easyPoiEntity.getName().equals("张三")){
//            joiner.add("eeeeee");
//        }


        threadLocalVal.forEach(e -> {
            if (e.getName().equals(easyPoiEntity.getName())) {
                int lineNumber = e.getRowNum() + 1;
                joiner.add("名字与" + lineNumber + "行重复");
            }
        });
        // 添加本行数据对象到ThreadLocal中
        threadLocalVal.add(easyPoiEntity);
        threadLocal.set(threadLocalVal);
        if (!Objects.equals(joiner.length(),0)) {
            return new ExcelVerifyHandlerResult(false, joiner.toString());
        }
        return new ExcelVerifyHandlerResult(true);
    }
}

关于空行问题

Excel经常会有空行但是有格式的行我们读取的时候会读到这些行然后对象就都是空的这可能会影响到数据的处理我是在校验类里面做了判断如下:

@Component
public class  ExcelVerifyInfo implements IExcelVerifyHandler<EasyPoiEntity> {
    private final ThreadLocal<List<EasyPoiEntity>> threadLocal = new ThreadLocal<>();
    @Override
    public ExcelVerifyHandlerResult verifyHandler(EasyPoiEntity easyPoiEntity) {
        if ( StringUtils.isAllBlank(easyPoiEntity.getName(), easyPoiEntity.getAddr()) && Objects.equals(easyPoiEntity.getAge(),0)) {
            // 空行数据，返回false表示无效，EasyPoi会过滤掉该行数据
            return new ExcelVerifyHandlerResult(false);
        } else {
            // 非空行数据，返回true表示有效
            StringJoiner joiner = new StringJoiner(",");
            List<EasyPoiEntity> threadLocalVal = threadLocal.get();
            if (threadLocalVal == null) {
                threadLocalVal = new ArrayList<>();
            }

            /**
             * 所有的自定义校验 可以再这里写 比如查数据 比较之类
             */
//        if (easyPoiEntity.getName().equals("张三")){
//            joiner.add("eeeeee");
//        }


            threadLocalVal.forEach(e -> {
                if (e.getName().equals(easyPoiEntity.getName())) {
                    int lineNumber = e.getRowNum() + 1;
                    joiner.add("名字与" + lineNumber + "行重复");
                }
            });
            // 添加本行数据对象到ThreadLocal中
            threadLocalVal.add(easyPoiEntity);
            threadLocal.set(threadLocalVal);
            if (!Objects.equals(joiner.length(),0)) {
                return new ExcelVerifyHandlerResult(false, joiner.toString());
            }
            return new ExcelVerifyHandlerResult(true);
        }

    }
}

假如你对象属性多不用全都判断是都空只要找几个必填属性然后判断为空就认为是空行就行了

数据翻译

1.简单的方式直接用replace 会在导出导入进行翻译比如你导出一般状态是数字会翻译成中文导入则相反

    @Excel(name = "状态",replace = {"进行中_1", "完成_2","结束_3"})
    private String status;

Excel导入校验并返回异常Excel

    @PostMapping("/importExcel")
    @ResponseBody
    public void importUser(@RequestParam("uploadFile") MultipartFile multipartFile, HttpServletResponse response) {
        try {
        //自己找一个读的utlis
            List<User> read = EasyExcelUtil.read(multipartFile.getInputStream(), User.class);
            //拿到数据 校验数据 有问题就set导msg 然后把流写出
            List<User> errList = new ArrayList<>();
            for (User user : read) {
                if (user.getName().equals("张三")){
                    user.setMsg("这是一个错误数据");
                    errList.add(user);
                }
            }
            if (CollectionUtil.isNotEmpty(errList)){
                // 生成时间戳
                String timestamp = DateUtil.format(LocalDateTime.now(), "yyyyMMddHHmmss");
                EasyExcelUtil.write(response,"err_"+timestamp,errList,"test");
            }
            System.out.println(read);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

对象

@Data
public class User {
    private String name;
    private String phone;
    private String addr;

    @ContentFontStyle(color = Font.COLOR_RED)
    @ColumnWidth(value = 25)
    private String msg;

}