Alibaba EasyExcel 异步导出excel

简单描述

使用EasyExcel操作Excel文件可以避免很多POI api的繁琐操作，可以较大程度的简化导出Excel代码，当然EasyExcel也是对POI的封装，如果遇到较为复杂的场景可以使用原生的poi进行操作。

异步导出思路

异步导出是将导出拆分为生成excel文件和下载excel文件两步操作，实现方式很多可根据场景进行选择，如果是单应用可以直接将生成的excel发到应用内存中比如map，下载请求直接从map中拿取生成好的对象；如果是多应用可以将生成的excel保存到一个公共目录中，下载请求直接读取生成好的excel。

生成excel

	private static Map<String, ExcelWriter> workbookMap = new ConcurrentHashMap();
    private static Map<String, String> workbookName = new ConcurrentHashMap();

    @RequestMapping("/export/async")
    @ResponseBody
    public JSONObject AsyncExport(UserVo user) {
        JSONObject returnJson = new JSONObject();
        String fileName = FILE_NAME_PREFIX + "用户.xlsx";
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(UploadFileCst.EXPORTFILE + fileName, UserVo.class).build();
			List<UserUserVo> dataList = userService.getUserList(user);
  			WriteSheet writeSheet = EasyExcel.writerSheet("user").build();
            excelWriter.write(dataList, writeSheet);                        
            workbookMap.put(key, excelWriter);
            workbookName.put(key, fileName);
            returnJson.put("Status", "OK");
            returnJson.put("key", key);
        } catch (Exception ex) {
            returnJson.put("Status", "ERROR");
            returnJson.put("Message", ex.getMessage());
            log.error(ex.getMessage(), ex);
        }
        return returnJson;
    }

ps:

1. 生成excel的请求返回的是json数据且数据中必须包含生成excel的结果状态，前端会根据该请求返回的结果决定是发送下载请求还是抛出下载失败。
2. Sheet最大行数为1048575行，超出会抛出异常

java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575)

下载excel请求

 	@RequestMapping("/export/async/download")
    public void downloadSync(String name, HttpServletResponse response) {
        ExcelWriter excelWriter = null;
        WriteContext writeContext = null;
        WriteSheetHolder writeSheetHolder = null;
        WriteWorkbookHolder writeWorkbookHolder = null;
        Workbook workbook = null;
        String fileName = null;
        try {
            if (StringUtils.isBlank(name)) {
                throw new RuntimeException("文件key不能为空");
            }
            if (!workbookName.containsKey(name) || !workbookMap.containsKey(name)) {
                throw new RuntimeException("查找文件失败");
            }
            fileName = workbookName.get(name);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileNameCode = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileNameCode);

            excelWriter = workbookMap.get(name);

            writeContext = excelWriter.writeContext();
            writeSheetHolder = writeContext.writeSheetHolder();
            writeWorkbookHolder = writeSheetHolder.getParentWriteWorkbookHolder();
            workbook = writeWorkbookHolder.getCachedWorkbook();

            workbook.write(response.getOutputStream());

        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            if (writeContext != null) {
                writeContext.finish(true);
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (workbookMap.containsKey(name)) {
                workbookMap.remove(name);
            }
            if (workbookName.containsKey(name)) {
                workbookName.remove(name);
            }
            //清除文件
            if (StringUtils.isNotBlank(fileName)) {
                File file = new File(UploadFileCst.EXPORTFILE + fileName);
                if (file != null) {
                    file.delete();
                }
            }
        }
    }

ps:下载请求需要注意的是删除资源，关闭流等操作。

前端代码

function exprot(type) {
        var params = $("#file_form").serialize();
        $("#download_plane").window('close');
        var url = "${url}/export/async?" + params;
        //提示弹出框
        $("#download_model").window('open');
        $.ajax({
            url:url,
            type:"GET",
            dataType:"json",
            async:true,
            success:function (data) {
                if (data.Status == 'OK') {
                    $("#download_model").window('close');
                    window.location.href = "${url}/export/async/download?name=" + data.key;
                } else {
                    $('#download_message').empty().text("文件导出错误，请稍后重试！")
                }
            }
        });
    }

ps:注意设置async:true

知识点

EasyExcel 使用cglib动态代理将目标对象进行代码，通过注解定义的规则对数据进行校验封装

 WriteHolder currentWriteHolder = this.writeContext.currentWriteHolder();
        BeanMap beanMap = BeanMap.create(oneRowData);
        Set<String> beanMapHandledSet = new HashSet();
        int cellIndex = false;
        Map ignoreMap;
        Cell cell;
        int cellIndex;
        if (HeadKindEnum.CLASS.equals(this.writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadKind())) {
            ignoreMap = this.writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadMap();
            Map<Integer, ExcelContentProperty> contentPropertyMap = this.writeContext.currentWriteHolder().excelWriteHeadProperty().getContentPropertyMap();
            Iterator var11 = contentPropertyMap.entrySet().iterator();

            while(var11.hasNext()) {
                Entry<Integer, ExcelContentProperty> entry = (Entry)var11.next();
                cellIndex = (Integer)entry.getKey();
                ExcelContentProperty excelContentProperty = (ExcelContentProperty)entry.getValue();
                String name = excelContentProperty.getField().getName();
                if (beanMap.containsKey(name)) {
                    Head head = (Head)ignoreMap.get(cellIndex);
                    WriteHandlerUtils.beforeCellCreate(this.writeContext, row, head, cellIndex, relativeRowIndex, Boolean.FALSE);
                    cell = WorkBookUtil.createCell(row, cellIndex);
                    WriteHandlerUtils.afterCellCreate(this.writeContext, cell, head, relativeRowIndex, Boolean.FALSE);
                    Object value = beanMap.get(name);
                    CellData cellData = this.converterAndSet(currentWriteHolder, excelContentProperty.getField().getType(), cell, value, excelContentProperty, head, relativeRowIndex);
                    WriteHandlerUtils.afterCellDispose(this.writeContext, cellData, cell, head, relativeRowIndex, Boolean.FALSE);
                    beanMapHandledSet.add(name);
                }
            }
        }

总结

EasyExcel是对poi的封装如果场景比较特殊，可以直接获取ExcelWriter对象底层的Workbook进行操作，具体操作可以查看下载excel导出代码示例，只是要注意的是需要关闭三次流，如果前两次流没有关闭或关闭失败会导致excelWriter.finish()抛出流关闭异常。
ps:如有建议或疑问可在评论区沟通