网站建设完成确认书,培训页面设计师,学php网站开发,建设企业网站技术解决方案easyExcel 3.x以上版本导入数据后#xff0c;再把错误信息导出#xff0c;外加自定义RGB背景色
背景
由于项目中用的easypoi导入的数据量大了#xff0c;会导致OOM的问题#xff0c;所以要求更换为easyExcel框架做导入。话不多说#xff0c;这里只做一个导入的示例…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
dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.0.5/version
/dependency
//可能会用到alibaba的fastjson
implementation com.alibaba:fastjson:1.2.83Controller代码 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();ListMapString, Object failDataList demoDataListener.getFailDataList();//导出错误数据export(dataList(failDataList), response);} catch (Exception e) {log.error(导入配置异常, e);}return JSONResult.ok(成功);}private void export(ListDemoData 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-age0);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 ListListString head() {ListListString list new ArrayList();ListString head0 new ArrayList();head0.add(标题);ListString head1 new ArrayList();head1.add(创建时间);ListString head2 new ArrayList();head2.add(价格);ListString head3 new ArrayList();head3.add(名称);ListString head4 new ArrayList();head4.add(规格);ListString 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 ListDemoData dataList(ListMapString, Object failList) {ListDemoData list ListUtils.newArrayList();for (MapString, 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 ReadListenerDemoData {/*** 每隔5条存储数据库实际使用中可以100条然后清理list 方便内存回收*/private static final int BATCH_COUNT 100;/*** 错误数据上限达到上限则停止解析数据*/private static final int ERROR_COUNT 100;/*** 缓存的数据*/private ListDemoData cachedDataList ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);/*** 失败的数据*/private ListMapString, Object failDataList new ArrayList();private DemoDao dao;public DemoDataListener() {// TODO 实际使用过程中可通过构造参数把dao层的对象传进来写入数据(下面注释的构造参数)}/**public DemoDataListener(DemoDao dao) {// TODO 实际使用过程中可通过构造参数把dao层的对象传进来写入数据this.dao dao;}*/public ListMapString, 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(MapInteger, 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())) {MapString, 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 {SetField 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 ListWriteCellStyle 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;}
}到此结束如有疑问欢迎留言
