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

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
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version>
</dependency>
//可能会用到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<Map<String, Object>> failDataList = demoDataListener.getFailDataList();//导出错误数据export(dataList(failDataList), response);} catch (Exception e) {log.error("导入配置异常", e);}return JSONResult.ok("成功");}private void export(List<DemoData> 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<List<String>> head() {List<List<String>> list = new ArrayList<>();List<String> head0 = new ArrayList<>();head0.add("标题");List<String> head1 = new ArrayList<>();head1.add("创建时间");List<String> head2 = new ArrayList<>();head2.add("价格");List<String> head3 = new ArrayList<>();head3.add("名称");List<String> head4 = new ArrayList<>();head4.add("规格");List<String> 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<DemoData> dataList(List<Map<String, Object>> failList) {List<DemoData> list = ListUtils.newArrayList();for (Map<String, Object> 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<DemoData> {/*** 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收*/private static final int BATCH_COUNT = 100;/*** 错误数据上限，达到上限则停止解析数据*/private static final int ERROR_COUNT = 100;/*** 缓存的数据*/private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);/*** 失败的数据*/private List<Map<String, Object>> failDataList = new ArrayList<>();private DemoDao dao;public DemoDataListener() {// TODO 实际使用过程中可通过构造参数把dao层的对象传进来，写入数据(下面注释的构造参数)}/**public DemoDataListener(DemoDao dao) {// TODO 实际使用过程中可通过构造参数把dao层的对象传进来，写入数据this.dao = dao;}*/public List<Map<String, Object>> getFailDataList() {return failDataList;}@Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {log.error("解析数据异常！", exception);
//        if (failDataList.size() > 0) {
//            exportErrorDataToExcel();
//        }ReadListener.super.onException(exception, context);}@Overridepublic void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {ReadListener.super.invokeHead(headMap, context);}/*** 这个每一条数据解析都会来调用** @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}* @param context*/@Overridepublic void invoke(DemoData data, AnalysisContext context) {//去除空行，有些空行有格式但没有数据，也会被读取if (lineNull(data)) {return;}log.info("解析到一条数据:{}", JSON.toJSONString(data));//TODO 这里做数据校验，失败的数据放到failDataList中if (StringUtils.isBlank(data.getPname())) {Map<String, Object> 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了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (cachedDataList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}private boolean lineNull(Object line) {if (line instanceof String) {return StringUtils.isEmpty((String) line);}try {Set<Field> 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;}@Overridepublic void extra(CellExtra extra, AnalysisContext context) {ReadListener.super.extra(extra, context);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {}@Overridepublic 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<WriteCellStyle> contentWriteCellStyleList;public CustomRgbCellStyle(WriteCellStyle contentWriteCellStyle) {if (contentWriteCellStyle != null) {this.contentWriteCellStyleList = ListUtils.newArrayList(contentWriteCellStyle);}}@Overridepublic void setHeadCellStyle(CellWriteHandlerContext context) {Boolean head = context.getHead();// 设置标题头样式，这里的判断可不要if (head) {log.info("afterCellCreate ====> {}", head);// 获取和创建CellStyleWriteCellData<?> 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);// 重点！！！ 必须设置OriginCellStylecellData.setOriginCellStyle(originCellStyle);}}@Overridepublic 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;}
}

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