动态表头Excel实现

近日项目中需要实现动态表头的excel导入导出，记录一下。

这是前端页面实现的UI

这是后端存储的数据

我实现的导出Excel

只导出一半数据是因为这是一个对称矩阵。 可以看出我机位的表头没能实现成这样

存疑一：

我尝试过获取模板表的指定单元格的风格，然后渲染新workbook指定单元格风格，没有成功。希望能在评论中得到帮助。

下面是代码实现

导出EXCEL

/**
 * 导出
 */
public  void exportArronDistance(HttpServletResponse response) throws IOException {
    String nowTime = DateUtils.format(new Date(), DateUtils.yyyyMMddHHmmss);

    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    String tableName = "机位距离-" + nowTime+".xlsx";
    tableName = URLEncoder.encode(tableName, StandardCharsets.UTF_8.name());
    tableName = new String(tableName.getBytes(), StandardCharsets.ISO_8859_1.name());
    response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + tableName);

    // 创建 WriteHandler，并将样式传递给它
    WriteHandler writeHandler = new CustomHeadStyleStrategy();
    
    // 获取数据库数据
    List<WdrArronDistance> exportData = this.baseMapper.getExportData();
    
    // 设置动态表头
    List<List<String>> heads = setDynamicHead(exportData);
    
    // 设置单元格数据
    WriteHandler rowWriteHandler = new CustomWriteHandler(exportData);

    // 设置同等长度列表，避免数据污染
    List<String> emptyStringList = createEmptyStringList(exportData.size());

    EasyExcel.write(response.getOutputStream()).registerWriteHandler(writeHandler)
            .registerWriteHandler(rowWriteHandler)
            .head(heads).sheet("机位距离").doWrite(emptyStringList);
}

/**
 * 设置动态表头
 * @param distances
 * @return
 */
public static List<List<String>> setDynamicHead(List<WdrArronDistance> distances){
    List<List<String>> headList = new ArrayList<>();

    List<String> firstHead = new ArrayList<>();
    firstHead.add("机位");
    firstHead.add("机位");
    headList.add(firstHead);

    List<WdrArronDistance> nearApron = distances.stream().filter(f -> ObjUtil.equals(f.getType(), ApronTypeEnum.NEAR.getCode()))
            .sorted(Comparator.comparing(WdrArronDistance::getPortNo))
            .collect(Collectors.toList());

    for (WdrArronDistance wdrArronDistance : nearApron) {
        List<String> nearHead = new ArrayList<>();
        nearHead.add(ApronTypeEnum.NEAR.getDesc());
        nearHead.add(wdrArronDistance.getPortNo());
        headList.add(nearHead);
    }

    List<WdrArronDistance> farApron = distances.stream().filter(f -> ObjUtil.equals(f.getType(), ApronTypeEnum.FAR.getCode()))
            .sorted(Comparator.comparing(WdrArronDistance::getPortNo))
            .collect(Collectors.toList());
    for (WdrArronDistance wdrArronDistance : farApron) {
        List<String> farHead = new ArrayList<>();
        farHead.add(ApronTypeEnum.FAR.getDesc());
        farHead.add(wdrArronDistance.getPortNo());
        headList.add(farHead);
    }

    List<WdrArronDistance> otherApron = distances.stream().filter(f -> ObjUtil.equals(f.getType(), ApronTypeEnum.OTHER.getCode()))
            .sorted(Comparator.comparing(WdrArronDistance::getPortNo))
            .collect(Collectors.toList());
    for (WdrArronDistance wdrArronDistance : otherApron) {
        List<String> otherHead = new ArrayList<>();
        otherHead.add(ApronTypeEnum.OTHER.getDesc());
        otherHead.add(wdrArronDistance.getPortNo());
        headList.add(otherHead);
    }

    return headList;
}

/**
 * 设置空元素列表，避免脏数据录入
 * @param size
 * @return
 */
private static List<String> createEmptyStringList(int size) {
    List<String> emptyStringList = new ArrayList<>();
    for (int i = 0; i < size; i++) {
        emptyStringList.add("");
    }
    return emptyStringList;
}

CustomWriteHandler 方法

import cn.hutool.core.lang.TypeReference;
import cn.hutool.core.util.ObjUtil;
import com.alibaba.excel.event.WriteHandler;
import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.eunitedtech.automatic.entity.WdrArronDistance;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.type.TypeFactory;
import org.apache.poi.ss.usermodel.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CustomWriteHandler extends AbstractRowWriteHandler implements WriteHandler {

    private final List<WdrArronDistance> arronDistances;

    private  Map<String, Map<String, Long>> arronDistancesMap;

    private  WriteCellStyle writeCellStyle;

    private  CellStyle cellStyle;
    private String value;

    public CustomWriteHandler(List<WdrArronDistance> arronDistances) {
        this.arronDistances = arronDistances;
    }


    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
    }
    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
//                row.setHeightInPoints(30);
    }
    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {

        if(ObjUtil.isNull(arronDistancesMap)){
            arronDistancesMap = setDistancesMap(arronDistances);
        }
        if(ObjUtil.isNull(writeCellStyle)){
            setWriteCellStyle();
        }

        row.setHeightInPoints(30);

        // 排除表头行
        if (!isHead) {

            // 在这里实现对每个单元格的赋值逻辑
            // 第一列 是第二行表头的对应值
            Cell cell = row.createCell(0);
            String portNo = arronDistances.get(row.getRowNum()-2).getPortNo();
            
            cell.setCellValue(portNo);
            cell.setCellType(CellType.STRING);
            setCellStyle(cell);

            //其他列赋值
            for (int columnIndex = arronDistances.size(); columnIndex >= 1; columnIndex--) {
                Cell rowCell = row.getCell(columnIndex);
                if (rowCell == null) {
                    rowCell = row.createCell(columnIndex);
                }

               // 在这里实现对每个单元格的赋值逻辑，只导出对称矩阵的一半数据
                String rowPortNo = arronDistances.get(columnIndex-1).getPortNo();

                if(ObjUtil.equals(rowPortNo,portNo)){
                    rowCell.setCellValue("0");
                    rowCell.setCellType(CellType.STRING);
                    setCellStyle(rowCell);
                    break;

                } else {
                    if(ObjUtil.isNull(arronDistancesMap.get(portNo))){
                        value = "";
                    } else {
                        value = ObjUtil.isNull(arronDistancesMap.get(portNo).get(rowPortNo)) ? "":arronDistancesMap.get(portNo).get(rowPortNo).toString();
                    }
                    rowCell.setCellValue(value);
                    rowCell.setCellType(CellType.STRING);
                    setCellStyle(rowCell);
                }
            }
        }
    }


    @Override
    public void sheet(int i, Sheet sheet) {

    }

    @Override
    public void row(int i, Row row) {

    }

    @Override
    public void cell(int i, Cell cell) {

    }
    
    // 数据处理
    public Map<String, Map<String, Long>> setDistancesMap(List<WdrArronDistance> arronDistances){

        Map<String, Map<String, Long>> map = new HashMap<>();
        for (WdrArronDistance arronDistance : arronDistances) {
            Map<String, Long> valueMap;

            if (ObjUtil.isNotEmpty(arronDistance.getDistanceMatrix())) {
                ObjectMapper objectMapper = new ObjectMapper();

                try {
                    TypeReference<Map<String, Long>> typeReference = new TypeReference<Map<String, Long>>() {};
                    valueMap = objectMapper.readValue(arronDistance.getDistanceMatrix(),
                            TypeFactory.defaultInstance().constructType(typeReference.getType()));
                   map.put(arronDistance.getPortNo(), valueMap);
                } catch (JsonProcessingException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return map;
    }

    // 设置单元格样式
    public void setWriteCellStyle(){

        // 创建表头样式
        WriteCellStyle writeCellStyle = new WriteCellStyle();

        // 设置字体
        WriteFont font = new WriteFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);


        writeCellStyle.setWriteFont(font);

        // 设置对齐方式
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置边框
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);

        // 设置背景颜色
//        writeCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//        writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        this.writeCellStyle = writeCellStyle;
    }

    public void setCellStyle(Cell cell) {

        // 设置字体
        WriteFont writeFont = writeCellStyle.getWriteFont();

        // 获取 Workbook
        Workbook workbook = cell.getSheet().getWorkbook();

        // 创建新的样式
        CellStyle cellStyle = workbook.createCellStyle();

        // 创建字体
        Font font = workbook.createFont();

        font.setFontName(writeFont.getFontName());
        font.setFontHeightInPoints(writeFont.getFontHeightInPoints());
        cellStyle.setFont(font);

        // 设置对齐方式
        cellStyle.setAlignment(writeCellStyle.getHorizontalAlignment());
        cellStyle.setVerticalAlignment(writeCellStyle.getVerticalAlignment());

        // 设置边框
        cellStyle.setBorderTop(writeCellStyle.getBorderTop());
        cellStyle.setBorderBottom(writeCellStyle.getBorderBottom());
        cellStyle.setBorderLeft(writeCellStyle.getBorderLeft());
        cellStyle.setBorderRight(writeCellStyle.getBorderRight());

        // 设置背景颜色
//        cellStyle.setFillForegroundColor(writeCellStyle.getFillForegroundColor());
//        cellStyle.setFillPattern(writeCellStyle.getFillPatternType());

        cell.setCellStyle(cellStyle);
    }

}

导入EXCEL

/**
 * 导入机位距离
 * @param file
 */
@Transactional(rollbackFor = Exception.class)
public void importArronDistance(MultipartFile file) throws IOException {

    ExcelReader excelReader = EasyExcel.read(new BufferedInputStream(file.getInputStream())).build();

    // 2. 创建读取监听器
    ReadListener readListener = new ReadListener();
    ReadSheet readSheet = EasyExcel.readSheet(0).registerReadListener(readListener).build();
    excelReader.read(readSheet);
    List<DistanceBO> distanceBOS = readListener.getDistanceBOS();

    if(CollUtil.isNotEmpty(distanceBOS)){

        // 清除旧数据
        this.lambdaUpdate().remove();
    }
    
    // 数据处理，存入数据库
    Map<String, List<DistanceBO>> distanceMap = distanceBOS.stream().collect(Collectors.groupingBy(DistanceBO::getFormNo));

    List<WdrApron> wdrAprons = wdrApronService.lambdaQuery().select(WdrApron::getCode, WdrApron::getType).list();

    Map<String, String> wdrApronMap = wdrAprons.stream().collect(Collectors.toMap(WdrApron::getCode, WdrApron::getType));

    List<WdrArronDistance> wdrArronDistances = new ArrayList<>();
    for (String key : distanceMap.keySet()) {

        List<DistanceBO> arronDistanceBOS = distanceMap.get(key);

        Map<String, Long> distanceMatrixMap = arronDistanceBOS.stream()
                .collect(Collectors.toMap(DistanceBO::getToNo, bo -> ObjUtil.isNull(bo.getDistance()) ? 0L : Long.parseLong(bo.getDistance())));

        WdrArronDistance wdrArronDistance = new WdrArronDistance();
        wdrArronDistance.setPortNo(key);
        wdrArronDistance.setType(wdrApronMap.get(key));
        wdrArronDistance.setDistanceMap(distanceMatrixMap);
        wdrArronDistance.setDistanceMatrix(JsonUtils.toString(distanceMatrixMap));
        wdrArronDistances.add(wdrArronDistance);
    }
    this.saveBatch(wdrArronDistances);
}

ReadListener类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import com.eunitedtech.automatic.bo.DistanceBO;


import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class ReadListener extends AnalysisEventListener<Map<Integer, String>> {

    // 指定动态表头的行索引
    private int dynamicHeaderRowIndex = 1;

    Map<Integer,String> headMap = new HashMap<>();

    private List<DistanceBO> distanceBOS = new ArrayList<>();

    private static void completeSymmetricMatrix(List<DistanceBO> arronDistanceBOList) {
        for (DistanceBO a : arronDistanceBOList) {
            for (DistanceBO b : arronDistanceBOList) {

                // 对称矩阵性质，填充缺失的数据
                if (a.getFormNo().equals(b.getToNo()) && b.getFormNo().equals(a.getToNo())) {

                    // 补充对称矩阵的缺失数据，只有在 a 的 distance 为 null 时才需要补充
                    if (a.getDistance() == null) {
                        a.setDistance(b.getDistance());
                    }
                }
            }
        }
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // 本来想在这里获取第二行表头，但是一直获取不到，只能拿到第一行的，被迫在invoke方法获取数据
        // this.headMap = headMap
    }

    @Override
    public void invoke(Map<Integer, String> rowData, AnalysisContext context) {
        ReadRowHolder readRowHolder = context.readRowHolder();
        Integer rowIndex = readRowHolder.getRowIndex();
        
        // 获取第二行表头
        if(rowIndex == dynamicHeaderRowIndex){
            for (Integer i : rowData.keySet()) {
            
            // 从第二列开始
                if(i > 0){
                    headMap.put(i, rowData.get(i));
                }
            }
        }
        if(rowIndex > dynamicHeaderRowIndex){
            for (Integer i : rowData.keySet()) {
                if (i > 0) {
                    DistanceBO distanceBO = new DistanceBO();
                    distanceBO.setFormNo(rowData.get(0));
                    distanceBO.setToNo(headMap.get(i));
                    distanceBO.setDistance(rowData.get(i));
                    distanceBOS.add(distanceBO);
                }
            }
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        completeSymmetricMatrix(distanceBOS);
    }

    public List<DistanceBO> getDistanceBOS() {
        return distanceBOS;
    }
}

DistanceBO实体

@Data
public class DistanceBO {

    @ApiModelProperty(value = "起点")
    private String formNo;

    @ApiModelProperty(value = "目的")
    private String ToNo;

    @ApiModelProperty(value = "距离")
    private String distance;
}

存疑二

导入的时候在ReadListener的invokeHeadMap方法中，我想获取动态表格第二行的数据，但是一直拿不到，好在第二行表头的数据，跟第一列的数据对应，所以我绕道实现了，希望有人能提供帮助，解决这个问题。