官方文档 : 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;}