一、需求背景
在实际的开发过程中,我们经常能够遇到Excel相关的应用场景:用户或者订单批量导入、BI报表导出等等,EasyExcel由于其简洁、易用性,深得大家的喜爱!
二、框架介绍
-
文档地址:https://alibaba-easyexcel.github.io/
-
官网简介:EasyExcel是Alibaba一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。
-
常见应用场景
- 读Excel:用户、订单等通过Excel文件批量导入
- 写Excel:BI报表数据以Excel文件形式导出
三、应用实践
-
读Excel,以下代码以账户账户批量导入为例
-
controller层
/** * 创建账户批量导入 * * @param request {@link OperationAccountBatchRequest} * @return {@link OperationAccountBatchResponse} */ @PostMapping(value = "/batch-import") public CommonResponse<OperationAccountBatchResponse> batchImport(@RequestBody @Valid OperationAccountBatchRequest request) { try { URL httpUrl = new URL(request.getFilePath()); InputStream inputStream = httpUrl.openStream(); return operationAccountService.batchImport(inputStream); } catch (IOException e) { return CommonResponse.failureWithErrorCode(ErrorCodeConstant.READ_FILE_ERROR); } }
-
service层
@Override public CommonResponse<OperationAccountBatchResponse> batchImport(InputStream inputStream) { List<AccountExcelDto> requestList = new ArrayList<>(); List<AccountExcelDto> failList = new ArrayList<>(); try { EasyExcel.read(inputStream, AccountExcelDto.class, new AnalysisEventListener<AccountExcelDto>() { @Override public void invoke(AccountExcelDto accountExcelDto, AnalysisContext analysisContext) { if (Arrays.stream(accountExcelDto.getClass().getDeclaredFields()).allMatch(field -> { field.setAccessible(true); try { return Objects.isNull(field.get(accountExcelDto)); } catch (IllegalAccessException e) { log.error("反射获取accountExcelDto属性失败:{}",e.getMessage()); return true; } })) { return; } //业务代码省略...... } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.info("[账户创建批量导入]解析完成"); } }).doReadAll(); } catch (Exception e) { log.error("[账户创建批量导入]解析异常:{}",e.getMessage()); return CommonResponse.failureWithErrorCode(ErrorCodeConstant.EXCEL_READ_ERR); } List<String> mobiles = requestList.stream().map(AccountExcelDto::getMobile).collect(Collectors.toList()); //业务代码省略...... response.setSuccessList(resultOptional.get().stream().map(accountDto -> { AccountExcelDto accountExcelDto = new AccountExcelDto(); accountExcelDto.setMobile(accountDto.getMobile()); accountExcelDto.setName(accountDto.getAccountName()); accountExcelDto.setPassword(accountDto.getPassword()); accountExcelDto.setMsg("注册成功"); return accountExcelDto; }).collect(Collectors.toList())); return CommonResponse.successWithData(response); }
-
-
写Excel,以下代码以某报表导出为例
-
controller层
/** * 导出Excel * @param response servlet响应 */ @GetMapping(value = "/export", produces = "application/json; charset=utf-8") public CommonResponse<Void> exportExcel(HttpServletResponse response, PurchaseOrderRequest request) { //参数校验 if (Objects.nonNull(request.getOrderSybFlag()) || Objects.nonNull(request.getOrderXjqFlag()) || Objects.nonNull(request.getOrderYfyFlag()) || Objects.nonNull(request.getOrderHktFlag())) { if (Objects.isNull(request.getUnionType())) { return CommonResponse.failureWithErrorCode(ErrorCodeConstant.PURCHASE_UNION_TYPE_NULL); } } try { //导出 purchaseOrderService.exportPurchaseOrder(response, request); } catch (IOException e) { log.error("[认购单明细报表导出]异常:errMsg -> {}", e.getMessage()); return CommonResponse.failureWithErrorCode(ErrorCodeConstant.BI_EXPORT_ERROR); } return CommonResponse.successWithData(null); }
-
service层
/** * 导出认购单报表 * * @param response httpServlet response * @param request {@link PurchaseOrderRequest} 导出请求参数 */ public void exportPurchaseOrder(HttpServletResponse response, PurchaseOrderRequest request) throws IOException { try { excelBuilderBiz.exportPageData(response, "认购订单明细", this::pagePurchaseOrder, PurchaseOrderResponse.class, request); } catch (NoSuchFieldException e) { log.error("[字段属性异常]errMsg -> {}", e.getMessage()); } catch (IllegalAccessException e) { log.error("[导出反射异常]errMsg -> {}", e.getMessage()); } }
-
函数接口
/** * 导出分页业务数据的接口函数 * * @author zhongjianhong * @date 2022/06/16 */ @FunctionalInterface public interface ExportPageService<T,R> { /** * 获取分页数据的函数 * @param r 请求参数 * @return 分页数据结果 */ PagedResult<T> exportPage(R r); }
-
通用业务层
/** * 导出分页数据 * * @param response httpServerResponse * @param sheetName 报表名称 * @param exportPageService 获取分页数据的接口函数 * @param exportClass 导出对象 * @param <R> request 请求参数,请求参数里面一定要有pageNo和pageSize属性 */ public <T, R> void exportPageData(HttpServletResponse response, String sheetName, ExportPageService<T, R> exportPageService, Class exportClass, R r) throws IOException, NoSuchFieldException, IllegalAccessException { response.reset(); //默认sheet WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build(); ExcelWriter excelWriter = getExcelWriter(response, sheetName, exportClass); PagedResult<T> pagedResult; //初始页 int pageNo = 1; //当前sheet数据量记录值 int sheetLimitRecord = 0; //sheet记录 int sheetPage = 1; //反射获取pageNo和pageSize Class requestClazz = r.getClass(); Field pageNoFiled = requestClazz.getDeclaredField("pageNo"); Field pageSizeFiled = requestClazz.getDeclaredField("pageSize"); pageNoFiled.setAccessible(true); pageSizeFiled.setAccessible(true); pageSizeFiled.set(r, CommonConstant.SINGLE_PAGE_DATA_LIMIT); do { pageNoFiled.set(r, pageNo++); pagedResult = exportPageService.exportPage(r); //如果超过单页sheet限制,新增sheet if (sheetLimitRecord >= CommonConstant.MAX_SHEET_SIZE) { writeSheet = EasyExcel.writerSheet(sheetName + "-" + (++sheetPage)).build(); sheetLimitRecord = 0; } excelWriter.write(pagedResult.getPageData(), writeSheet); PageInfo pageInfo = pagedResult.getPageInfo(); if (pagedResult.getPageData().size() != 0) { log.info(sheetName + "导出进度:{}/{}", (pageInfo.getPageNo() - 1) * pageInfo.getPageSize() + pagedResult.getPageData().size(), pageInfo.getTotal()); } sheetLimitRecord += pagedResult.getPageData().size(); } while (pagedResult.getPageData().size() != 0); excelWriter.finish(); }
-
四、拓展
- 写Excel时,有几个坑需要特别注意
- 首先是数据量问题,Excel中单个sheet是有数量上限的,excel一个sheet最多1048576行,所以一般数据量超过某个值(自定义),我们需要新增sheet;
- 二是性能问题,由于报表系统一般数据量会很大(例如从数仓获取),如果一次性从数据库读取大量数据(几十万行),那么即使没有并发请求,单机内存依然会容易溢出,所以建议读取时分页读取,读一页,写一页,可以解决数据量大时内存溢出的问题;
- 三是需要对此类接口进行限流,原因与第二点差不多,防止同时读取的数据量过大,导致即使小并发但仍然发生内存溢出的问题。
- 四是如果返回前端的response和导出excel的DTO基本一致,但存在小差异性时,可以使用convert实现兼容
- 以上是工作业务中实际使用到的代码,掺杂了部分业务逻辑,仅供参考,第一次使用需要结合参考官方入门文档