多级动态表头导出-easyexcel

导出如下动态表头

主要的构造tabCols和tableData，

注意表头的字段，基本构造出了该格式所有的都能适配

    @GetMapping("/exportData")
    public void excelExport(TbDtTargetHealthMon tbDtTargetHealthMon, HttpServletResponse response) throws IOException {
        response.reset();
        response.setCharacterEncoding("UTF-8");
        //响应内容格式
        response.setContentType("application/vnd.ms-excel");
        //设置文件名
//        String fileName =System.currentTimeMillis() + ".xlsx";

        try {
            //设置前端下载文件名
            String urlFileName = URLEncoder.encode(tbDtTargetHealthMon.getModuleName() + "健康度详情" + System.currentTimeMillis() , "UTF-8");
            response.setHeader("content-disposition", "attachment; filename=" + urlFileName + ".xlsx");

            //  *代表所有请求都可访问
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Allow-Methods", "GET");
            response.setHeader("Access-Control-Allow-Headers", "Origin, No-Cache, X-Requested-With, " +
                    "If-Modified-Since, Pragma, Last-Modified, Cache-Control, Expires, Content-Type, X-E4M-With");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        ExcelWriter excelWriter = null;
        try {
            Map<String, Object> result = tbDtTargetHealthMonService.getTabCols(tbDtTargetHealthMon);
            List<Map<String, String>> tabCols = (List<Map<String, String>>) result.get("tabCols");
            List<List<String>> excelHead = head(tabCols);

            // 第一页数据
            List<Map<String, String>> dataOne = (List<Map<String, String>>) result.get("tableData");
            //5w数据一个sheet
            int batchSize = AutoExportConstants.SHEET_PAGE_SIZE;
            int totalSize = dataOne.size();
            int sheetCount = (int) Math.ceil((double) totalSize / batchSize);

            excelWriter = EasyExcelFactory.write(response.getOutputStream()).build();

            for (int i = 0; i < sheetCount; i++) {
                int fromIndex = i * batchSize;
                int toIndex = Math.min((i + 1) * batchSize, totalSize);
                List<Map<String, String>> currentData = dataOne.subList(fromIndex, toIndex);

                WriteSheet writeSheet = EasyExcel
                        .writerSheet("sheet" + (i + 1))
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .registerWriteHandler(horizontalCellStyleStrategy())
                        .head(excelHead)
                        .build();
                excelWriter.write(dataList(tabCols, currentData), writeSheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } catch (Throwable throwable) {
            throwable.printStackTrace();
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if(response.getOutputStream() != null){
                response.getOutputStream().close();
            }
        }
    }

    private List<List<String>> head(List<Map<String, String>> tabCols) {
        return tabCols.stream()
                .map(e -> Stream.of(e.get("columnParent"), e.get("columnName")).filter(Objects::nonNull).collect(Collectors.toList()))
                .collect(Collectors.toList());
    }

    private List<List<Object>> dataList(List<Map<String, String>> tabCols, List<Map<String, String>> mapList) {
        List<List<Object>> list = new ArrayList<List<Object>>();
        for (int i = 0; i < mapList.size(); i++) {
            Map row = mapList.get(i);

            List<Object> data = new ArrayList<Object>();
            for (int j = 0; j < tabCols.size(); j++) {
                String filed = tabCols.get(j).get("columnField");
                Object objVal = row.get(filed);
                String val = Objects.toString(objVal, "");
                data.add(val);
            }
            list.add(data);
        }
        return list;
    }

    private HorizontalCellStyleStrategy horizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 14);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());

        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);

        contentWriteCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        contentWriteCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        contentWriteCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        contentWriteCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

tabCols的格式
这个tabCols的顺序就是导出表头的顺序，columnField相当于就是取值映射的key，columnName就是表头的中文，columnParent就是父级表头，这些名字可以自己定义

100

101

102

103

104

105

106

107

108

109

110

111

112

[{
    "columnField": "monthId",
    "columnName": "月份"
}, {
    "columnField": "latnName",
    "columnName": "本地网"
}, {
    "columnField": "销售费用整体效能",
    "columnName": "销售费用整体效能得分"
}, {
    "columnParent": "销售费用占收比",
    "columnField": "targetValue_xsfy_001",
    "columnName": "指标值"
}, {
    "columnParent": "销售费用占收比",
    "columnField": "targetGrades_xsfy_001",
    "columnName": "指标得分"
}, {
    "columnParent": "百元基础费拉动增量基础业务收入",
    "columnField": "targetValue_xsfy_002",
    "columnName": "指标值"
}, {
    "columnParent": "百元基础费拉动增量基础业务收入",
    "columnField": "targetGrades_xsfy_002",
    "columnName": "指标得分"
}, {
    "columnParent": "百元销售费用拉动增量主营收入",
    "columnField": "targetValue_xsfy_003",
    "columnName": "指标值"
}, {
    "columnParent": "百元销售费用拉动增量主营收入",
    "columnField": "targetGrades_xsfy_003",
    "columnName": "指标得分"
}, {
    "columnParent": "新增用户户均销售费用",
    "columnField": "targetValue_xsfy_004",
    "columnName": "指标值"
}, {
    "columnParent": "新增用户户均销售费用",
    "columnField": "targetGrades_xsfy_004",
    "columnName": "指标得分"
}, {
    "columnField": "渠道费用效能",
    "columnName": "渠道费用效能得分"
}, {
    "columnParent": "价值积分平均单价",
    "columnField": "targetValue_xsfy_008",
    "columnName": "指标值"
}, {
    "columnParent": "价值积分平均单价",
    "columnField": "targetGrades_xsfy_008",
    "columnName": "指标得分"
}, {
    "columnParent": "建店12月以上门店月均价值积分",
    "columnField": "targetValue_xsfy_009",
    "columnName": "指标值"
}, {
    "columnParent": "建店12月以上门店月均价值积分",
    "columnField": "targetGrades_xsfy_009",
    "columnName": "指标得分"
}, {
    "columnParent": "价值积分同比增长",
    "columnField": "targetValue_xsfy_010",
    "columnName": "指标值"
}, {
    "columnParent": "价值积分同比增长",
    "columnField": "targetGrades_xsfy_010",
    "columnName": "指标得分"
}, {
    "columnParent": "千分店占比",
    "columnField": "targetValue_xsfy_011",
    "columnName": "指标值"
}, {
    "columnParent": "千分店占比",
    "columnField": "targetGrades_xsfy_011",
    "columnName": "指标得分"
}, {
    "columnField": "客户服务费效能",
    "columnName": "客户服务费效能得分"
}, {
    "columnParent": "客户服务费占收比",
    "columnField": "targetValue_xsfy_012",
    "columnName": "指标值"
}, {
    "columnParent": "客户服务费占收比",
    "columnField": "targetGrades_xsfy_012",
    "columnName": "指标得分"
}, {
    "columnParent": "单位用户客户服务费",
    "columnField": "targetValue_xsfy_013",
    "columnName": "指标值"
}, {
    "columnParent": "单位用户客户服务费",
    "columnField": "targetGrades_xsfy_013",
    "columnName": "指标得分"
}, {
    "columnParent": "客户保有率",
    "columnField": "targetValue_xsfy_014",
    "columnName": "指标值"
}, {
    "columnParent": "客户保有率",
    "columnField": "targetGrades_xsfy_014",
    "columnName": "指标得分"
}, {
    "columnParent": "收入保有率",
    "columnField": "targetValue_xsfy_015",
    "columnName": "指标值"
}, {
    "columnParent": "收入保有率",
    "columnField": "targetGrades_xsfy_015",
    "columnName": "指标得分"
}]

tableData的格式
这个顺序无所谓，key就对应的就是tabCols的columnField

[{
    "targetValue_xsfy_008": "2.6900",
    "targetGrades_xsfy_012": "86.67",
    "targetValue_xsfy_009": "3542.0100",
    "targetGrades_xsfy_013": "93.33",
    "targetGrades_xsfy_010": "6.67",
    "targetGrades_xsfy_011": "86.67",
    "targetValue_xsfy_004": "227.9600",
    "targetValue_xsfy_002": "0.0000",
    "targetGrades_xsfy_014": "0.00",
    "targetValue_xsfy_003": "73.8400",
    "渠道费用效能": "58.67",
    "targetGrades_xsfy_015": "0.00",
    "monthId": "202401",
    "销售费用整体效能": "21.67",
    "targetValue_xsfy_011": "0.8081",
    "targetValue_xsfy_012": "1.64%",
    "targetValue_xsfy_010": "76.18%",
    "targetGrades_xsfy_001": "13.33",
    "targetGrades_xsfy_002": "0.00",
    "targetValue_xsfy_015": "0.00%",
    "targetGrades_xsfy_003": "0.00",
    "targetValue_xsfy_013": "0.7500",
    "客户服务费效能": "54.00",
    "targetGrades_xsfy_004": "73.33",
    "targetValue_xsfy_014": "0.00%",
    "targetGrades_xsfy_009": "40.00",
    "targetGrades_xsfy_008": "80.00",
    "targetValue_xsfy_001": "10.19%",
    "latnName": "合肥"
}]