原 easyexcel（十）：easyexcel导出excel并动态合并单元格（合并列）

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/zhangwuji/article/details/1317

一：引入pom

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

二：代码

@GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        String fileName = System.currentTimeMillis() + ".xlsx";
        response.reset();
        response.addHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        OutputStream os=response.getOutputStream();

        List<UserInfo> dataList=new ArrayList<>();
        UserInfo u1=new UserInfo();
        u1.setName("张无忌");
        u1.setAge(18);
        u1.setAddress("上海火车站");

        UserInfo u2=new UserInfo();
        u2.setName("赵敏");
        u2.setAge(18);
        u2.setAddress("上海火车站");

        UserInfo u3=new UserInfo();
        u3.setName("赵敏");
        u3.setAge(22);
        u3.setAddress("上海火车站");

        UserInfo u4=new UserInfo();
        u4.setName("周芷若");
        u4.setAge(38);
        u4.setAddress("荆州站");

        UserInfo u5=new UserInfo();
        u5.setName("周芷若");
        u5.setAge(28);
        u5.setAddress("荆州站");

        dataList.add(u1);
        dataList.add(u2);
        dataList.add(u3);
        dataList.add(u4);
        dataList.add(u5);

        List<List<String>> headList=new ArrayList<>();
        headList.add(Lists.newArrayList("姓名"));
        headList.add(Lists.newArrayList("年龄"));
        headList.add(Lists.newArrayList("地址"));

        int mergeRowIndex=0;
        int[] mergeColumeIndex = new int[]{0,1};
        EasyExcel.write(os).head(headList).sheet("用户信息")
                .registerWriteHandler(new ExcelReportMergeStrategy(mergeRowIndex, mergeColumeIndex))
                .doWrite(dataList);
    }

三：动态合并列策略

package com.gaia.business.common.utils;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;

public class ExcelReportMergeStrategy implements CellWriteHandler {

    private int mergeRowIndex;

    private int[] mergeColumnIndex;

    public ExcelReportMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(CellWriteHandlerContext context) {

    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        WriteSheetHolder writeSheetHolder=context.getWriteSheetHolder();
        Cell cell=context.getCell();

        int curRowIndex = cell.getRowIndex();
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Row prevRow = cell.getSheet().getRow(curRowIndex - 1);
        Row curRow = cell.getSheet().getRow(curRowIndex);
        if (prevRow == null) {
            prevRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell prevFirstColCell = prevRow.getCell(0);
        Cell curFirstColCell = curRow.getCell(0);
        Object prevFirstColData = prevFirstColCell.getCellType() == CellType.STRING ? prevFirstColCell.getStringCellValue() : prevFirstColCell.getNumericCellValue();
        Object curFirstColData = curFirstColCell.getCellType() == CellType.STRING ? curFirstColCell.getStringCellValue() : curFirstColCell.getNumericCellValue();
        Cell prevCell = prevRow.getCell(curColIndex);
        Object prevData = prevCell.getCellType() == CellType.STRING ? prevCell.getStringCellValue() : prevCell.getNumericCellValue();
        boolean isDataSame = curData.equals(prevData) && curFirstColData.equals(prevFirstColData);
        if(isDataSame){
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for(int i = 0; i < mergeRegions.size(); i++) {
                CellRangeAddress cellRangeAddress = mergeRegions.get(i);
                if(cellRangeAddress.isInRange(curRowIndex - 1, curColIndex)){
                    sheet.removeMergedRegion(i);
                    cellRangeAddress.setLastRow(curRowIndex);
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                    isMerged = true;
                }
            }
            if(!isMerged){
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }


}

四：效果图

五：参考。

https://blog.csdn.net/weixin_50067580/article/details/111559637