阅读量:0
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; } @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<Integer, ReadCellData<?>> 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<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了，需要去存储一次数据库，防止数据几万条数据在内存，容易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<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; } @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<WriteCellStyle> 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; } } 到此结束，如有疑问，欢迎留言！
