基于 easyExcel 3.1.5依赖的包 实现动态表头 动态表格内容

1.需求：需要导出的EXCEL示例：

 

2.依赖：

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.5</version>
        </dependency>

3.工具类：

package com.minex.web.device.utils;

import com.minex.web.device.entity.vo.ReadRecordsVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

@Component
public class ExcelExporter {

    private static final String DEFAULT_SHEET_NAME = "Sheet1";

    public void export(HttpServletResponse response, Map<String, List<ReadRecordsVO>> sheetDataMap,String fileName) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            // 创建并写入多个Sheet页
//            Map<String, List<ReadRecordsVO>> sheetDataMap = splitDataIntoSheets(data);
            for (String sheetName : sheetDataMap.keySet()) {
                Sheet sheet = createSheet(workbook, sheetName);
                writeDataToSheet(sheet, sheetDataMap.get(sheetName));
            }

            // 设置响应头以触发浏览器下载
            String utf8EncodedFilename = URLEncoder.encode(fileName, StandardCharsets.UTF_8);
            response.setHeader("Content-Disposition",
                    "attachment; filename*=utf-8''" + utf8EncodedFilename+".xlsx");
            // 响应类型,编码
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            // 将工作簿写入响应输出流
            try (OutputStream os = response.getOutputStream()) {
                workbook.write(os);
            }
        }
    }

    private Sheet createSheet(Workbook workbook, String sheetName) {
        return workbook.createSheet(sheetName);
    }

    private void writeDataToSheet(Sheet sheet, List<ReadRecordsVO> data) {
        // 获取所有列信息和它们对应的父级表头
        Map<String, String> headerToParentHeaderMap = getHeadersWithParent(data);
        // 写入表头
        Row headerRow = sheet.createRow(0);
        Row subHeaderRow = sheet.createRow(1);
        int columnIndex = 0;
        Map<String, Integer> startColumnIndexMap = new HashMap<>();
        Map<String, Integer> endColumnIndexMap = new HashMap<>();
        for (String header : headerToParentHeaderMap.keySet()) {
            String parentHeader = headerToParentHeaderMap.get(header);
            // 记录每个父级表头的起始列索引
            startColumnIndexMap.putIfAbsent(parentHeader, columnIndex);
            Cell cell = subHeaderRow.createCell(columnIndex++);
            cell.setCellValue(header);
            // 更新每个父级表头的最后一列索引
            endColumnIndexMap.put(parentHeader, columnIndex - 1);
        }
        // 合并第一行相同父级表头的单元格
        mergeCells(sheet, startColumnIndexMap, endColumnIndexMap);
        // 写入父级表头（第一行）
        columnIndex = 0;
        for (String parentHeader : startColumnIndexMap.keySet()) {
            Cell cell = headerRow.createCell(startColumnIndexMap.get(parentHeader));
            cell.setCellValue(parentHeader);
        }
        // 写入数据行
        writeDataRow(sheet, data, headerToParentHeaderMap.keySet(), 2);
        // 自动调整列宽
        autoSizeColumns(sheet, headerToParentHeaderMap.size());
    }

    private void writeDataRow(Sheet sheet, List<ReadRecordsVO> data, Set<String> allHeaders, int rowIndex) {
        for (ReadRecordsVO record : data) {
            Row row = sheet.createRow(rowIndex++);
            int columnIndex = 0;
            for (String header : allHeaders) {
                Cell cell = row.createCell(columnIndex++);
                record.getRecordItems().stream()
                        .filter(item -> item.getHeader().equals(header))
                        .findFirst()
                        .ifPresentOrElse(
                                item -> cell.setCellValue(item.getValue()),
                                () -> cell.setCellValue("")
                        );
            }
        }
    }

    private void autoSizeColumns(Sheet sheet, int columnCount) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    private Map<String, String> getHeadersWithParent(List<ReadRecordsVO> data) {
        Map<String, String> headerToParentHeaderMap = new LinkedHashMap<>();
        for (ReadRecordsVO record : data) {
            for (ReadRecordsVO.RecordItem item : record.getRecordItems()) {
                headerToParentHeaderMap.putIfAbsent(item.getHeader(), item.getParentHeader());
            }
        }
        return headerToParentHeaderMap;
    }

    private void mergeCells(Sheet sheet, Map<String, Integer> startColumnIndexMap, Map<String, Integer> endColumnIndexMap) {
        Set<String> mergedParents = new HashSet<>();
        for (String parentHeader : startColumnIndexMap.keySet()) {
            if (!mergedParents.contains(parentHeader)) {
                int startColumnIndex = startColumnIndexMap.get(parentHeader);
                int endColumnIndex = endColumnIndexMap.getOrDefault(parentHeader, startColumnIndex);
                if (startColumnIndex != endColumnIndex) {
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, startColumnIndex, endColumnIndex));
                }
                mergedParents.add(parentHeader);
            }
        }
    }

    private Map<String, List<ReadRecordsVO>> splitDataIntoSheets(List<ReadRecordsVO> data) {
        // 根据业务逻辑分割数据到不同的Sheet页中
        // 这里简单地将所有数据放入一个名为DEFAULT_SHEET_NAME的Sheet页中
        Map<String, List<ReadRecordsVO>> result = new HashMap<>();

        result.put(DEFAULT_SHEET_NAME, data);
        return result;
    }
}

4.controller调用工具类导出

@PostMapping("/export")
    public void export(@RequestBody SubstationMeterReadRecordListRO query,
                                                         HttpServletResponse response,
                                                         @ApiIgnore CurrentUser user) throws IOException {
        List<ReadRecordsVO> dataList = substationMeterReadRecordService.readRecords(query, user);
        if (dataList.isEmpty()) {
            throw ExceptionFactory.warnBizException("没有数据可导出");
        }
        Map<String, List<ReadRecordsVO>> listMap = dataList.stream().collect(Collectors.groupingBy(ReadRecordsVO::getRecordDate));
        excelExporter.export(response, listMap, dataList.get(0).getSubstationName() + "抄表记录");
    }

5.数据类

package com.minex.web.device.entity.vo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;
import java.util.List;

@Data
public class ReadRecordsVO {

    private String substationName;

    private String recordDate;

    private List<RecordItem> recordItems;

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class RecordItem {

        private Integer order = 0;

        private String parentHeader;

        private String header;

        private String value;
    }
}

 

6.运行效果