原 easyexcel（二十二）：easyexcel导出并动态合并行

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

需求：我们需要将第一列和第二列中行相同的内容进行合并

一：代码

@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.setUserName("张无忌");
        u1.setAge(18);
        u1.setAddress("上海火车站");

        UserInfo u2=new UserInfo();
        u2.setName("张无忌");
        u2.setUserName("张无忌");
        u2.setAge(18);
        u2.setAddress("上海火车站");

        UserInfo u3=new UserInfo();
        u3.setName("周芷若");
        u3.setUserName("周总");
        u3.setAge(22);
        u3.setAddress("上海火车站");

        UserInfo u4=new UserInfo();
        u4.setName("金毛狮王");
        u4.setUserName("金总");
        u4.setAge(38);
        u4.setAddress("荆州站");

        UserInfo u5=new UserInfo();
        u5.setName("杨肖");
        u5.setUserName("杨肖");
        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("年龄"));
        headList.add(Lists.newArrayList("地址"));

        List<CellLineRange> cellLineRanges=new ArrayList<>();
        cellLineRanges.add(new CellLineRange(0, 1));
        EasyExcel.write(os).head(headList).sheet("用户信息")
                .registerWriteHandler(new ExcelReportMultiMergeStrategy(cellLineRanges, dataList.size() - 1))
                .registerWriteHandler(setStyleStrategy())
                .doWrite(dataList);
    }

    public HorizontalCellStyleStrategy setStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

        // 设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

二：合并行策略

package com.mszl.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 com.gaia.business.model.vo.CellLineRange;
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 ExcelReportMultiMergeStrategy implements CellWriteHandler {

    // 自定义合并单元格的列，如果想合并第4列和第5例、第6列和第7例：[CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
    private List<CellLineRange> cellLineRangeList;

    // 自定义合并单元格的开始的行，一般来说填表头行高0表示从表头下每列开始合并：如表头行高位为3则int mergeRowIndex=2
    private int mergeRowIndex;

    public ExcelReportMultiMergeStrategy(List<CellLineRange> cellLineRangeList, int mergeRowIndex) {
        this.cellLineRangeList=cellLineRangeList;
        this.mergeRowIndex=mergeRowIndex;
    }

    @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();
        for(int i = 0; i < cellLineRangeList.size(); i++){
            if(curColIndex > cellLineRangeList.get(i).getFirstCol() && curColIndex<=cellLineRangeList.get(i).getLastCol()){
                // 单元格数据处理
                mergeWithLeftLine(writeSheetHolder, cell, curRowIndex, curColIndex);
                break;
            }
        }
    }

    /**
     * @description 当前单元格向左合并
     */
    private void mergeWithLeftLine(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex){
        // 当前单元格中数据
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        // 获取当前单元格的左面一个单元格
        Cell leftCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
        // 获取当前单元格的左面一个单元格中的数据
        Object leftData = leftCell.getCellTypeEnum() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue();
        // 将当前单元格数据与左侧一个单元格数据比较
        if(leftData.equals(curData)){
            // 获取当前sheet页
            Sheet sheet = writeSheetHolder.getSheet();
            // 得到所有的合并单元格
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            // 是否合并
            boolean isMerged = false;
            for(int i = 0; i < mergeRegions.size() && !isMerged; i++){
                // CellRangeAddress POI合并单元格
                // CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
                // 例子：CellRangeAddress(2, 6, 3, 3)
                // 第2行起,第6行终止,第3列开始,第3列结束
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // cellRangeAddr.isInRange(int rowInd, int colInd)确定给定坐标是否在此范围的范围内
                // 若左侧一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
                if(cellRangeAddr.isInRange(curRowIndex, curColIndex - 1)){
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastColumn(curColIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若左侧一个单元格未被合并,则新增合并单元
            if(!isMerged){
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex - 1, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }


}

三：合并列实体

package com.mszl.business.model.vo;

import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
public class CellLineRange {

    /**
     * 起始列
     */
    private int firstCol;

    /**
     * 结束列
     */
    private int lastCol;

}

四：效果图