easyExcel 条件合并和直接合并单元格

package com.example.workflow.controller;

import com.alibaba.excel.EasyExcel;
import com.example.workflow.model.Member;
import com.example.workflow.strategy.ExcelFillCellMergeStrategy;
import com.example.workflow.strategy.MergeStrategy;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@RestController
public class ExcelController {

    @RequestMapping("/export")
    public void exportMembers1(HttpServletResponse response) throws IOException {
        List<Member> members =
                Arrays.asList(
                    new Member("1","测试部","zhangsan","18"),
                        new Member("1","测试部","zhangsan","19"),
                        new Member("1","测试部","zhangsan","29"),
                        new Member("2","测试部","lisi","18"),
                        new Member("3","测试部","wangwu","18"),
                        new Member("4","开发部","zhaoliu","18"),
                        new Member("5","开发部","maqi","18"),
                        new Member("5","开发部","m11aqi","18")
                        );
        // 设置文本内省
        response.setContentType("application/vnd.ms-excel");
        // 设置字符编码
        response.setCharacterEncoding("utf-8");
        // 设置响应头
        response.setHeader("Content-disposition", "attachment;filename=demo.xlsx");
        EasyExcel.write(response.getOutputStream(), Member.class).
                //自定义多级表头
                head(getTitles()).
                //需要合并单元格的索引
                registerWriteHandler(new ExcelFillCellMergeStrategy(0,2,new ArrayList<>())).
                sheet("成员列表").doWrite(members);
    }

    private List<List<String>> getTitles() {
        List<String> title1 = Arrays.asList("ID");
        List<String> title2 = Arrays.asList("部门");
        List<String> title3 = Arrays.asList("信息","用户名");
        List<String> title4 = Arrays.asList("信息","年龄");
        return Arrays.asList(title1,title2,title3,title4);
    }


}

@Data
@ColumnWidth(20)
@NoArgsConstructor
@AllArgsConstructor
public class Member {

    @ExcelProperty(value = "ID",index = 0)
    private String id;
    @ExcelProperty(value = "部门",index = 1)
    private String depart;
    @ExcelProperty(value = "用户名",index = 2)
    private String name;
    @ExcelProperty(value = "年龄",index = 3)
    private String age;
}

条件合并

package com.example.workflow.strategy;



import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
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 ExcelFillCellMergeStrategy implements CellWriteHandler {
    // 需要从第几行开始合并，0表示第1行
    private final int mergeRowIndex;
    // 合并的哪些列，比如为4时，当前行id和上一行id相同则合并前五列
    private final int mergeColumnRegion;

    private final List<Integer> ignoreColumn;

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int mergeColumnRegion, List<Integer> ignoreColumn) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnRegion = mergeColumnRegion;
        this.ignoreColumn = ignoreColumn;
    }

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

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 隐藏id列
//        writeSheetHolder.getSheet().setColumnHidden(0, true);
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (!ignoreColumn.contains(curColIndex) && curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnRegion; i++) {
                if (curColIndex <= mergeColumnRegion) {
                    mergeWithPreviousRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并：当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列
     *
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPreviousRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // 当前行的第一个Cell
        Cell curFirstCell = cell.getSheet().getRow(curRowIndex).getCell(0);
        Object curFirstData = curFirstCell.getCellType() == CellType.STRING.getCode() ? curFirstCell.getStringCellValue() : curFirstCell.getNumericCellValue();
        // 上一行的第一个Cell
        Cell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
        Object preFirstData = preFirstCell.getCellType() == CellType.STRING.getCode() ? preFirstCell.getStringCellValue() : preFirstCell.getNumericCellValue();

        // 当前cell
        Object data = cell.getCellType() == CellType.STRING.getCode() ? cell.getStringCellValue() : cell.getNumericCellValue();
        // 上面的Cell
        Cell upCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object upData = upCell.getCellType() == CellType.STRING.getCode() ? upCell.getStringCellValue() : upCell.getNumericCellValue();

        // 当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列 且上一行值相同
        if (curFirstData.equals(preFirstData) && data.equals(upData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }
}

直接合并

package com.example.workflow.strategy;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.*;

public class MergeStrategy extends AbstractMergeStrategy {
    // 合并的列编号，从0开始，指定的index或自己按字段顺序数
    private Set<Integer> mergeCellIndex = new HashSet<>();

    // 数据集大小，用于区别结束行位置
    private Integer maxRow = 0;

    // 禁止无参声明
    private MergeStrategy() {
    }

    public MergeStrategy(Integer maxRow, int... mergeCellIndex) {
        Arrays.stream(mergeCellIndex).forEach(item -> {
            this.mergeCellIndex.add(item);
        });
        this.maxRow = maxRow;
    }

    // 记录上一次合并的信息
    private Map<Integer, MergeRange> lastRow = new HashMap<>();

    private Set<String> nos = new HashSet<>();

    // 每行每列都会进入，绝对不要在这写循环
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int currentCellIndex = cell.getColumnIndex();
        // 判断该行是否需要合并
        if (mergeCellIndex.contains(currentCellIndex)) {
            //当前单元格的值
            String currentCellValue = cell.getStringCellValue();
            int currentRowIndex = cell.getRowIndex();

            //lastRow put(0,new) put(1,new) put
            if (!lastRow.containsKey(currentCellIndex)) {
                // 记录首行起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
                return;
            }
            //有上行这列的值了，拿来对比.
            MergeRange mergeRange = lastRow.get(currentCellIndex);
            if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {
                // 结束的位置触发下合并.
                // 同行同列不能合并，会抛异常
                if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
                }
                // 更新当前列起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
            }
            // 合并行 + 1
            mergeRange.endRow += 1;
            // 结束的位置触发下最后一次没完成的合并
            if (relativeRowIndex.equals(maxRow - 1)) {
                MergeRange lastMergeRange = lastRow.get(currentCellIndex);
                // 同行同列不能合并，会抛异常
                if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));
                }
            }
        }
    }
}

class MergeRange {
    public int startRow;
    public int endRow;
    public int startCell;
    public int endCell;
    public String lastValue;

    public MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {
        this.startRow = startRow;
        this.endRow = endRow;
        this.startCell = startCell;
        this.endCell = endCell;
        this.lastValue = lastValue;
    }
}