easyExcel 3.x以上版本导入数据后，再把错误信息导出，外加自定义RGB背景色

背景

由于项目中用的easypoi导入的数据量大了，会导致OOM的问题，所以要求更换为easyExcel框架做导入。话不多说，这里只做一个导入的示例，还有把遇到的一些问题列出来，大家根据业务需要随机应变。文章参考了其他大佬写的博客，这里把参考的大佬博客列出来：

官方文档：https://easyexcel.opensource.alibaba.com/docs/3.0.x
https://blog.csdn.net/qq_36978700/article/details/123425954
https://blog.csdn.net/qq_29834241/article/details/133786536
https://blog.csdn.net/wjs_007/article/details/135118539
https://www.cnblogs.com/mike-mei/p/17732227.html

引入依赖

//我的项目用的是gradle implementation ('com.alibaba:easyexcel:3.0.5') //maven       com.alibaba      easyexcel      3.0.5  //可能会用到alibaba的fastjson implementation 'com.alibaba:fastjson:1.2.83' 

Controller代码

    @PostMapping("/import")     public JSONResult importExcel(@RequestParam(name = "file") MultipartFile file, HttpServletResponse response) {         try {            //实现easyExcel的解析对象             DemoDataListener demoDataListener = new DemoDataListener();             //读取excel文件             EasyExcel.read(file.getInputStream(), DemoData.class, demoDataListener).sheet().doRead();             List> failDataList = demoDataListener.getFailDataList();             //导出错误数据             export(dataList(failDataList), response);         } catch (Exception e) {             log.error("导入配置异常", e);         }         return JSONResult.ok("成功");     }     private void export(List dataList,HttpServletResponse response) {         // 头的策略         WriteCellStyle headWriteCellStyle = new WriteCellStyle();         // 背景设置为红色         headWriteCellStyle.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());         WriteFont headWriteFont = new WriteFont();         headWriteFont.setFontName("宋体");         headWriteFont.setFontHeightInPoints((short)11);         headWriteFont.setBold(true);         headWriteFont.setColor(IndexedColors.WHITE.getIndex());         headWriteCellStyle.setBorderRight(BorderStyle.THIN);         headWriteCellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());         headWriteCellStyle.setWriteFont(headWriteFont);         // 内容的策略         WriteCellStyle contentWriteCellStyle = new WriteCellStyle();         // 设置细边框         contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);         contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);         contentWriteCellStyle.setBorderRight(BorderStyle.THIN);         contentWriteCellStyle.setBorderTop(BorderStyle.THIN);         // 设置边框颜色 25灰度         contentWriteCellStyle.setBottomBorderColor(IndexedColors.GREEN.getIndex());         contentWriteCellStyle.setTopBorderColor(IndexedColors.GREEN.getIndex());         contentWriteCellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());         contentWriteCellStyle.setRightBorderColor(IndexedColors.GREEN.getIndex());         WriteFont contentWriteFont = new WriteFont();         contentWriteFont.setFontName("宋体");         // 字体大小         contentWriteFont.setFontHeightInPoints((short)12);         contentWriteCellStyle.setWriteFont(contentWriteFont);         // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现         HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);         try (ServletOutputStream outputStream = response.getOutputStream()) {             String fileName = "demo_error_data" + System.currentTimeMillis();             response.setContentType("application/vnd.ms-excel");             response.setCharacterEncoding("utf8");             response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");             response.setHeader("Pragma", "public");             response.setHeader("Cache-Control", "no-store");             response.addHeader("Cache-Control", "max-age=0");             EasyExcel.write(outputStream, DemoData.class)                     .registerWriteHandler(horizontalCellStyleStrategy)                     .registerWriteHandler(new CustomRgbCellStyle(contentWriteCellStyle))                     //自定义行高，宽度                     .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 21,(short) 19))                     //设置自动列宽                     .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())                     .head(head()).sheet("Sheet").doWrite(dataList);         } catch (IOException e) {             throw new RuntimeException("导出excel表格失败!", e);         }     } 	/** 	 * 自定义表头，如不需要自定义表头，直接在DemoData对象的注解@ExcelProperty配置即可 	 */     private List> head() {         List> list = new ArrayList<>();         List head0 = new ArrayList<>();         head0.add("标题");         List head1 = new ArrayList<>();         head1.add("创建时间");         List head2 = new ArrayList<>();         head2.add("价格");         List head3 = new ArrayList<>();         head3.add("名称");         List head4 = new ArrayList<>();         head4.add("规格");         List head5 = new ArrayList<>();         head5.add("失败原因");         list.add(head0);         list.add(head1);         list.add(head2);         list.add(head3);         list.add(head4);         list.add(head5);         return list;     }      private List dataList(List> failList) {         List list = ListUtils.newArrayList();         for (Map map : failList) {             list.add((DemoData) map.get("data"));         }         log.info("Data ===========> {}", JSON.toJSONString(list));         return list;     } 

实体类DemoData

@Data public class DemoData {      @ExcelProperty(index = 0)//index可不写，表示读取的列下标     private String title;      @ExcelProperty(index = 1)     private String createTime;      @ExcelProperty(index = 2)     private BigDecimal price;      @ExcelProperty(index = 3)     private String pname;      @ExcelProperty(index = 4)     private String spec;      @ExcelProperty(index = 5)     private String failReason; } 

解析对象DemoDataListener

@Slf4j public class DemoDataListener implements ReadListener {      /**      * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收      */     private static final int BATCH_COUNT = 100;     /**      * 错误数据上限，达到上限则停止解析数据      */     private static final int ERROR_COUNT = 100;     /**      * 缓存的数据      */     private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);     /**      * 失败的数据      */     private List> failDataList = new ArrayList<>();  	private DemoDao dao;      public DemoDataListener() {         // TODO 实际使用过程中可通过构造参数把dao层的对象传进来，写入数据(下面注释的构造参数)     } 	/**     public DemoDataListener(DemoDao dao) {         // TODO 实际使用过程中可通过构造参数把dao层的对象传进来，写入数据         this.dao = dao;     }     */      public List> getFailDataList() {         return failDataList;     }      @Override     public void onException(Exception exception, AnalysisContext context) throws Exception {         log.error("解析数据异常！", exception); //        if (failDataList.size() > 0) { //            exportErrorDataToExcel(); //        }         ReadListener.super.onException(exception, context);     }      @Override     public void invokeHead(Map> headMap, AnalysisContext context) {         ReadListener.super.invokeHead(headMap, context);     }      /**      * 这个每一条数据解析都会来调用      *      * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}      * @param context      */     @Override     public void invoke(DemoData data, AnalysisContext context) {         //去除空行，有些空行有格式但没有数据，也会被读取         if (lineNull(data)) {             return;         }         log.info("解析到一条数据:{}", JSON.toJSONString(data));         //TODO 这里做数据校验，失败的数据放到failDataList中         if (StringUtils.isBlank(data.getPname())) {             Map map = new HashMap<>(1);             data.setFailReason("第"+context.readRowHolder().getRowIndex()+"行：商品名称不能为空");             map.put("data", data);             failDataList.add(map);         } //        if (failDataList.size() > ERROR_COUNT) { //            throw new RuntimeException("错误数据过多，停止解析，请检查数据"); //        } 		         //校验数据完成后，添加到缓存中         cachedDataList.add(data);         // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM         if (cachedDataList.size() >= BATCH_COUNT) {             saveData();             // 存储完成清理 list             cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);         }     }      private boolean lineNull(Object line) {         if (line instanceof String) {             return StringUtils.isEmpty((String) line);         }         try {             Set fields = Arrays.stream(line.getClass().getDeclaredFields()).filter(f -> f.isAnnotationPresent(ExcelProperty.class)).collect(Collectors.toSet());             for (Field field : fields) {                 field.setAccessible(true);                 if (field.get(line) != null) {                     return false;                 }             }             return true;         } catch (Exception ignored) {             log.error(ignored.getMessage(), ignored);         }         return true;     }      @Override     public void extra(CellExtra extra, AnalysisContext context) {         ReadListener.super.extra(extra, context);     }      @Override     public void doAfterAllAnalysed(AnalysisContext context) {      }      @Override     public boolean hasNext(AnalysisContext context) {         return ReadListener.super.hasNext(context);     }      /**      * 加上存储数据库      */     private void saveData() {         log.info("{}条数据，开始存储数据库！", cachedDataList.size());         //TODO 这里执行存储数据库的代码逻辑 //        demoDAO.save(cachedDataList);         log.info("存储数据库成功！");     } } 

自定义RGB样式CustomRgbCellStyle

@Slf4j public class CustomRgbCellStyle extends AbstractCellStyleStrategy {      private List contentWriteCellStyleList;      public CustomRgbCellStyle(WriteCellStyle contentWriteCellStyle) {         if (contentWriteCellStyle != null) {             this.contentWriteCellStyleList = ListUtils.newArrayList(contentWriteCellStyle);         }     }       @Override     public void setHeadCellStyle(CellWriteHandlerContext context) {         Boolean head = context.getHead();         // 设置标题头样式，这里的判断可不要         if (head) {             log.info("afterCellCreate ====> {}", head);             // 获取和创建CellStyle             WriteCellData cellData = context.getFirstCellData();             CellStyle originCellStyle = cellData.getOriginCellStyle();             if (Objects.isNull(originCellStyle)) {                 originCellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();             }             // 设置背景颜色             ((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(186, 12, 47), new DefaultIndexedColorMap()));             originCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);             // 重点！！！ 由于在FillStyleCellWriteHandler，会把OriginCellStyle和WriteCellStyle合并，会已WriteCellStyle样式为主，所有必须把WriteCellStyle设置的背景色清空             // 具体合并规则请看WriteWorkbookHolder.createCellStyle方法             WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();             writeCellStyle.setFillForegroundColor(null);             // 重点！！！ 必须设置OriginCellStyle             cellData.setOriginCellStyle(originCellStyle);         }     }      @Override     public void setContentCellStyle(CellWriteHandlerContext context) {         if (stopProcessing(context) || CollectionUtils.isEmpty(contentWriteCellStyleList)) {             return;         }         WriteCellData cellData = context.getFirstCellData();         if (context.getRelativeRowIndex() == null || context.getRelativeRowIndex() <= 0) {             WriteCellStyle.merge(contentWriteCellStyleList.get(0), cellData.getOrCreateStyle());         } else {             WriteCellStyle.merge(                     contentWriteCellStyleList.get(context.getRelativeRowIndex() % contentWriteCellStyleList.size()),                     cellData.getOrCreateStyle());         }     }      protected boolean stopProcessing(CellWriteHandlerContext context) {         return context.getFirstCellData() == null;     } } 

到此结束，如有疑问，欢迎留言！