springboot+easyExcel 动态列表导出

1.pom依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.0-beta2</version>
</dependency>

2.导出传参类 

/**
 * @author hbj
 * @Date 2021/12/9 18:21
 * @Description
 * @Modified By
 */
@ApiModel(description = "导出传参类")
@Data
public class ExportDto {
    //接口访问路径
    @ApiModelProperty("接口访问路径")
    private String url;
    //请求方法；get或者post
    @ApiModelProperty("请求方法")
    private String method;
    //导出参数
    @ApiModelProperty("导出数据参数")
    private List<ExportParamsDto> exportParams;
    //搜索参数
    @ApiModelProperty("查询参数")
    private Map<String,Object> queryParams;
    //导出数量
    @ApiModelProperty("导出数量")
    private Long total;
    //导出文件名
    @ApiModelProperty("导出文件名")
    private String fileName;
}

@ApiModel(description = "导出数据参数")
@Data
public class ExportParamsDto {
    @ApiModelProperty("参数名（表头名字）")
    private String headName;
    @ApiModelProperty("对应字段")
    private String cloumn;
    /*@ApiModelProperty("时间参数格式（用于设置时间格式）")
    private String dateFormat;*/
    @ApiModelProperty("需要除以的数")
    private BigDecimal divideNum;
}

3.导出处理类 ExportDeal

/**
 * @author hbj
 * @Date 2021/12/17 17:37
 * @Description
 * @Modified By
 */
@Log4j2
public class ExportDeal {
    private ExcelWriter excelWriter;
    private List<List<String>> heads;
    private WriteHandler writeHandler;
    private String fileName;
    private String sourName;
    private Long startTime;

    //初始化
    public ExportDeal(Map<String, ExportParamsDto> headMap, String fileName, HttpServletResponse response) throws Exception {
        this.sourName = fileName;
        startTime = System.currentTimeMillis();
        //添加响应头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        //表头数组
        this.heads = head(headMap);
        //样式
        if (writeHandler == null) {
            this.writeHandler = new StyleWriteHandler();
            //this.writeHandler=new LongestMatchColumnWidthStyleStrategy();
        }
        //创建表格
        this.excelWriter = EasyExcel.write(response.getOutputStream()).head(heads).build();
        this.fileName = fileName;
        //log.info("表格【{}】初始化成功",sourName);
    }

    private ExportDeal() {
    }

    /**
     * 录入数据
     *
     * @param datas     录入的数据
     * @param sheetNo   第几页
     * @param sheetName 页名称
     * @param <T>
     */
    public <T> void loadData(List<List<Object>> datas, int sheetNo, String sheetName) {
        //录入数据
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo).sheetName(sheetName).head(heads)
                .registerWriteHandler(writeHandler).build();
        excelWriter.write(datas, writeSheet);
    }

    /**
     * 根据http获取数据导出
     * @param dto
     * @param sheetNo
     * @param sheetName
     * @param <T>
     * @return
     */
    public <T> Result loadDataByHttp(ExportDto dto, int sheetNo, String sheetName) {
        String res;
        Map<String, String> reqHeadMap = new HashMap<>();
        reqHeadMap.put("Content-Type", "application/json");
        reqHeadMap.put("Accept", "application/json");
        //最大导出10万
        if (dto.getTotal() > 10 * 10000) {
            dto.setTotal(10 * 10000L);
        }
        Map<String, Object> params = dto.getQueryParams();
        if (params == null) {
            params = new HashMap<>();
            params.put("pageSize", dto.getTotal());
            params.put("pageNo", 1);
        }
        if ("GET".equals(dto.getMethod())) {
            res = HttpsClient.doGet(dto.getUrl(), params, reqHeadMap);
        } else {
            res = HttpsClient.doPost(dto.getUrl(), JSONObject.toJSONString(params), reqHeadMap);
        }
        if (res != null) {
            Result<JSONObject> result = JSON.parseObject(res, Result.class);
            if (result.getCode() == 0L && result.getMsg() == null) {
                JSONObject obj = JSON.parseObject(res);
                result = Result.restResult(obj.getLong("status"), obj.getString("error"));
            }
            JSONObject obj = ResultDealUtil.getData(result);
            if (obj != null && CollectionUtils.isNotEmpty(dto.getExportParams())) {
                PageVO<JSONObject> page = JSON.parseObject(obj.toString(), PageVO.class);
                if (CollectionUtils.isNotEmpty(page.getRecords())) {
                    //表头map，key: 表头，value：字段
                    LinkedHashMap<String, ExportParamsDto> headMap = new LinkedHashMap<>();
                    dto.getExportParams().forEach(a -> headMap.put(a.getHeadName(), a));
                    List<List<Object>> datas = this.dataList(headMap, page.getRecords());
                    //录入导出数据
                    this.loadData(datas, sheetNo, sheetName);
                }
            } else if (ResultCode.SUCCESS.getCode() != result.getCode()) {
                log.error("导出失败：查询数据有误，{}", result);
                return result;
            }
        }
        return Result.ok();
    }

    //结束关闭数据
    public void close() {
        //刷新流
        excelWriter.finish();
        log.info("表格【{}】导出处理结束,耗时：{}ms", sourName, System.currentTimeMillis() - startTime);
    }

    private List<List<String>> head(Map<String, ExportParamsDto> headMap) {
        if (headMap == null) {
            return null;
        }
        List<List<String>> list = new ArrayList<>();
        for (String name : headMap.keySet()) {
            List<String> head = new ArrayList<>();
            head.add(name);
            list.add(head);
        }
        return list;
    }

    public ExportDeal writeHandler(WriteHandler handler) {
        this.writeHandler = handler;
        return this;
    }

    private List<List<Object>> dataList(Map<String, ExportParamsDto> headMap, List<JSONObject> datas) {
        if (headMap == null) {
            return null;
        }
        Long start = System.currentTimeMillis();
        List<List<Object>> list = new ArrayList<>();
        for (JSONObject json : datas) {
            List<Object> objs = new ArrayList<>();
            for (ExportParamsDto param : headMap.values()) {
                Object obj = param == null ? null : json.get(param.getCloumn());
                if (obj != null && param.getDivideNum() != null) {
                    obj = new BigDecimal(obj.toString()).divide(param.getDivideNum(), 6, BigDecimal.ROUND_HALF_UP);
                }
                objs.add(obj);
            }
            list.add(objs);
        }
        log.info("耗时：{}", System.currentTimeMillis() - start);
        return list;
    }

    //错误信息
    public void error(String errorMsg, String fileName, HttpServletResponse response) throws Exception {
        sourName = sourName+"_"+fileName;
        //添加响应头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        fileName = URLEncoder.encode(sourName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        this.fileName = fileName;
        excelWriter.writeContext().currentSheet(new WriteSheet(), WriteTypeEnum.FILL);
        List<List<Object>> datas = new ArrayList<>();
        datas.add(Arrays.asList(errorMsg));
        this.heads=null;
        loadData(datas, 0, "错误页");
    }

}

4.自定义样式

/**
 * @author hbj
 * @Date 2021/12/16 16:28
 * @Description
 * @Modified By
 */

public class StyleWriteHandler extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    private final Map<Integer, Map<Integer, Integer>> cache = MapUtils.newHashMapWithExpectedSize(8);

    private int maxCloumnWidth = 30;//自动适应宽最大值
    private Workbook workbook;
    private CellStyle headStyle;//头样式
    private CellStyle dataStyle;//数据样式

    private short headFontSize = 12;
    private short dataFontSize = 12;
    private boolean isAutoWidth = false;//自动适应宽

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        if (workbook == null) {
            // 拿到poi的workbook
            workbook = context.getWriteWorkbookHolder().getWorkbook();
            // 这里千万记住 想办法能复用的地方把他缓存起来 一个表格最多创建6W个样式
            // 不同单元格尽量传同一个 cellStyle

            //设置头样式
            if (context.getHead() && headStyle == null) {
                headStyle = workbook.createCellStyle();
                Font headFont = workbook.createFont();
                headFont.setFontHeightInPoints(headFontSize);
                headStyle.setFont(headFont);
            }
            //设置数据样式
            if (dataStyle == null) {
                dataStyle = workbook.createCellStyle();
                Font headFont = workbook.createFont();
                headFont.setFontHeightInPoints(dataFontSize);
                dataStyle.setFont(headFont);
            }
        }
        this.setColumnWidth(context);
    }

    @Override
    public void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //设置自适应长度
        if (isHead || isAutoWidth) {
            /*int columnWidth = dataLength(cellDataList.get(0), cell, isHead);
            // 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到
            // cell里面去 会导致自己设置的不一样
            cellDataList.get(0).setWriteCellStyle(null);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth);*/
            autoWidth(writeSheetHolder, cellDataList, cell, isHead);
        }
        if (cell.getCellType().equals(CellType.BLANK)) {
            cell.setCellValue("");
        }
        if (isHead && headStyle != null) {
            cell.setCellStyle(headStyle);
        } else if (dataStyle != null) {
            cell.setCellStyle(dataStyle);
        }
    }

    private void autoWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, boolean isHead) {
        Map<Integer, Integer> maxColumnWidthMap = (Map) this.cache.get(writeSheetHolder.getSheetNo());
        if (maxColumnWidthMap == null) {
            maxColumnWidthMap = new HashMap(16);
            this.cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
        }

        Integer columnWidth = this.dataLength(cellDataList.get(0), cell, isHead);
        if (columnWidth >= 0) {
            if (columnWidth > maxCloumnWidth) {
                columnWidth = maxCloumnWidth;
            } else {
                columnWidth = columnWidth < 4 ? 4 : columnWidth;
                columnWidth = columnWidth + 1;
            }
            Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
            if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                cellDataList.get(0).setWriteCellStyle(null);
                writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
            }
        }
    }

    //获取单元格值长度
    private Integer dataLength(WriteCellData<?> cellData, Cell cell, Boolean isHead) {
        int columnWidth = 0;
        if (isHead) {
            columnWidth = cell.getStringCellValue().getBytes().length;
        } else {
            Object value;
            switch (cellData.getType()) {
                case NUMBER:
                    value = cellData.getNumberValue();
                    break;
                case STRING:
                    value = cellData.getStringValue();
                    break;
                case BOOLEAN:
                    value = cellData.getBooleanValue();
                    break;
                case DATE:
                    value = "yyyy-MM-dd HH:mm:ss";
                    break;
                case RICH_TEXT_STRING:
                    value = cellData.getRichTextStringDataValue();
                    break;
                default:
                    value = "";
            }
            columnWidth = value == null ? 0 : value.toString().length();
        }

        return columnWidth;
    }

    public void setHeadFontSize(short headFontSize) {
        this.headFontSize = headFontSize;
    }

    public void setDataFontSize(short dataFontSize) {
        this.dataFontSize = dataFontSize;
    }

    public void setmaxCloumnWidth(int maxCloumnWidth) {
        this.maxCloumnWidth = maxCloumnWidth;
    }

    public void setAutoWidth(boolean autoWidth) {
        isAutoWidth = autoWidth;
    }

    public StyleWriteHandler autoWidth(boolean autoWidth) {
        isAutoWidth = autoWidth;
        return this;
    }
}

5.调用controller

@PostMapping("/export")
    public Result export(@RequestBody ExportDto dto, HttpServletResponse response) throws Exception {
        if (dto.getTotal() == null) {
            return Result.failed("导出数量不能为空");
        }
        //最大导出10万
        if (dto.getTotal() > 10 * 10000) {
            dto.setTotal(10 * 10000L);
        }
        //表头map，key: 表头，value：字段
        LinkedHashMap<String, ExportParamsDto> headMap = new LinkedHashMap<>();
        dto.getExportParams().forEach(a -> headMap.put(a.getHeadName(), a));
        //创建表格
        ExportDeal deal = new ExportDeal(headMap, dto.getFileName(), response);
        //自动适应宽
        deal.writeHandler(new StyleWriteHandler().autoWidth(true));
        try {
            //录入数据
            Result result = deal.loadDataByHttp(dto, 0, "sheet");
            if (ResultCode.SUCCESS.getCode() != result.getCode()) {
                response.reset();
                deal.error(JSONObject.toJSONString(result), "导出失败", response);
            }
        } catch (Exception e) {
            log.error("导出【{}】报错：{}", dto.getFileName(), e);
            response.reset();
            deal.error(e.getMessage(), "导出失败", response);
            throw e;
        } finally {
            deal.close();
        }
        return null;
    }

6.http方法就不贴出来了，自己百度一个就好