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")@ResponseBodypublic 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<>();@Overridepublic 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<>();@Overridepublic 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")@ResponseBodypublic void importUser(@RequestParam("uploadFile") MultipartFile multipartFile, HttpServletResponse response) {try {//自己找一个读的utlisList<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;}