需求

根据前端传递的动态表头, 导出多级Excel

easyPoi依赖

    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-spring-boot-starter</artifactId>
        <version>4.1.2</version>
    </dependency>

定义样式工具类

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

/**
 * @author zbc
 * @Description easypoi导出样式工具类
 */
public class ExcelStyleUtil implements IExcelExportStyler {
    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 9;
    private static final short FONT_SIZE_ELEVEN = 10;
    private static final short FONT_SIZE_TWELVE = 10;
    /**
     * 大标题样式
     */
    private CellStyle headerStyle;
    /**
     * 每列标题样式
     */
    private CellStyle titleStyle;
    /**
     * 数据行样式
     */
    private CellStyle styles;

    public ExcelStyleUtil(Workbook workbook) {
        this.init(workbook);
    }

    /**
     * 初始化样式
     *
     * @param workbook
     */
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook);
        this.styles = initStyles(workbook);
    }

    /**
     * 大标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        return headerStyle;
    }

    /**
     * 每列标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getTitleStyle(short color) {
        return titleStyle;
    }

    /**
     * 数据行样式
     *
     * @param parity 可以用来表示奇偶行
     * @param entity 数据内容
     * @return 样式
     */
    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
        return styles;
    }

    /**
     * 获取样式方法
     *
     * @param dataRow 数据行
     * @param obj     对象
     * @param data    数据
     */
    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return getStyles(true, entity);
    }

    /**
     * 模板使用的样式设置
     */
    @Override
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return null;
    }

    /**
     * 初始化--大标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        return style;
    }

    /**
     * 初始化--每列标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initTitleStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
        //背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    /**
     * 初始化--数据行样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initStyles(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 基础样式
     *
     * @return
     */
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("宋体");
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }
}

核心代码

private void export(List<JSONObject> jsonObjectList, MonthlyRatingDTO monthlyRatingDTO, HttpServletResponse response) {
        // jsonObjectList为查询出的动态数据集, monthlyRatingDTO为入参
        // 定义一级表头key集合
        Map<String, Object> keyMap = new HashMap<>();
        // 处理表头
        List<ExcelExportEntity> headerList = new ArrayList<>();
        // 前端传递的动态表头
        String dynamicHeader = monthlyRatingDTO.getDynamicHeader();
        List<JSONObject> lists = JSONObject.parseArray(dynamicHeader, JSONObject.class);
        lists.forEach(LambdaUtils.consumerWithIndex((jsonObj, index) -> {
            String label = jsonObj.get("label").toString();
            if(null != jsonObj.get("prop")) {
                String prop = jsonObj.get("prop").toString();
                ExcelExportEntity excelExportEntity = new ExcelExportEntity(label, prop);
                headerList.add(excelExportEntity);
            } else {
                ExcelExportEntity excelExportEntity = new ExcelExportEntity(label, label); // 此处第二个label对应子集数据key
                List<ExcelExportEntity> excelExportEntityList = new ArrayList<>();
                String children = jsonObj.getString("children");
                JSONArray childrenList = JSONArray.parseArray(children);
                keyMap.put(label, childrenList);
                childrenList.forEach(o -> {
                    JSONObject c = (JSONObject)o;
                    String sonLabel = c.getString("label");
                    String sonProp = c.getString("prop");
                    ExcelExportEntity son = new ExcelExportEntity(sonLabel, sonProp);
                    excelExportEntityList.add(son);
                });
                excelExportEntity.setList(excelExportEntityList);
                headerList.add(excelExportEntity);
            }
        }));
        // 处理数据
        List<Map<String, Object>> dataList = new ArrayList<>();
        for(JSONObject o : jsonObjectList) {
            Map<String, Object> maps = new HashMap<>();

            maps.put("name", o.getString("name"));
            maps.put("customerServiceNo", o.getString("customerServiceNo"));
            maps.put("type", "0".equals(o.getString("type")) ? "语音" : "IM");
            maps.put("callNum", o.getString("callNum"));
            maps.put("autoNum", o.getString("autoNum"));
            maps.put("checkNum", o.getString("checkNum"));

            if(ObjectUtil.isNotEmpty(keyMap)) {
                // 储存表格中的每一行数据
                List<Map<String, Object>> childrenList = null;
                for(Map.Entry<String, Object> entry : keyMap.entrySet()) {
                    String children = entry.getValue().toString();
                    if(StringUtil.isNotBlank(children)) {
                        JSONArray childrens = JSONArray.parseArray(children);
                        childrenList = new ArrayList<>();
                        Map<String, Object> map = new HashMap<>();
                        childrens.forEach(x -> {
                            JSONObject c = (JSONObject)x;
                            String sonProp = c.get("prop").toString();
                            if(null != o.get(sonProp)) {
                                map.put(sonProp, o.get(sonProp).toString());
                            }
                        });
                        childrenList.add(map);
                        maps.put(entry.getKey(), childrenList);
                    }
                }
            }
            maps.put("autoTotalScore", o.getString("autoTotalScore"));
            maps.put("finalTotalScore", o.getString("finalTotalScore"));
            maps.put("deductPoints", o.getString("deductPoints"));
            maps.put("averageScore", o.getString("averageScore"));
            dataList.add(maps);
        }
        String fileName = "导出文件名称";
        try {
            String headerMeassge = "选择月份: " + monthlyRatingDTO.getYearAndMonth() + "   " + "质检方案: " + schemeName;
            ExportParams params = new ExportParams(headerMeassge, fileName);
            params.setStyle(ExcelStyleUtil.class);
            Workbook workbook = ExcelExportUtil.exportExcel(params, headerList, dataList);
            // 获取文件导出日期
            String yyyyMmDdHhMmSs = DateUtil.format(DateUtil.now(), DateUtil.PATTERN_DATETIME_MINI);
            fileName += yyyyMmDdHhMmSs;

            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
            response.setCharacterEncoding("UTF-8");
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            log.error("导出异常", e);
        }
    }

导出效果

1675741553(1).jpg