EasyExcel 相同行自动合并

1.excel 模版 

 2.自动合并代码

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.*;

public class OptimizedMergeCellStrategyHandler extends AbstractMergeStrategy{

    private final boolean alikeColumn;
    private final boolean alikeRow;
    private final int rowIndex;
    private final int rowIndexStart;
    private final Set<Integer> columns;
    private int currentRowIndex = 0;
    private Map<Integer,Map<Integer,Object>> map= new LinkedHashMap<>();

    public OptimizedMergeCellStrategyHandler(boolean alikeColumn, boolean alikeRow, int rowIndex, Set<Integer> columns) {
        this(alikeColumn, alikeRow, rowIndex, columns, 0);
    }

    public OptimizedMergeCellStrategyHandler(boolean alikeColumn, boolean alikeRow, int rowIndex, Set<Integer> columns, int rowIndexStart) {
        this.alikeColumn = alikeColumn;
        this.alikeRow = alikeRow;
        this.rowIndex = rowIndex;
        this.columns = columns;
        this.rowIndexStart = rowIndexStart;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        int rowId = cell.getRowIndex();
        currentRowIndex = rowId;

        if (rowIndex > rowId) {
            return;
        }

        int columnId = cell.getColumnIndex();

        if (alikeColumn && columnId > 0) {
            mergeCells(sheet, cell, columnId - 1, columnId, 0);
        }

        if (alikeRow && rowId > rowIndexStart && columns.contains(columnId)) {
            mergeCells(sheet, cell, rowId - 1, rowId, 1);
        }
    }

    private void mergeCells(Sheet sheet, Cell cell, int start, int end, int direction) {
        try{
            String cellValue = getCellVal(cell);
            String refCellValue="";
            Cell referenceCell = null;
            if(direction == 0){
                referenceCell=cell.getRow().getCell(start);
                refCellValue = getCellVal(referenceCell);
            }else  if (sheet.getRow(start)==null){
                refCellValue=map.get(start)==null?"":map.get(start).get(cell.getColumnIndex())==null?"":map.get(start).get(cell.getColumnIndex())+"";
            }else {
                referenceCell=sheet.getRow(start).getCell(cell.getColumnIndex());
                refCellValue = getCellVal(referenceCell);
            }
            if (Objects.equals(cellValue, refCellValue)) {
                CellRangeAddress rangeAddress = createRangeAddress(sheet, cell, start, end, direction);
                if (rangeAddress != null) {
                    sheet.addMergedRegion(rangeAddress);
                }
            }

            if(map.containsKey(cell.getRowIndex())){
                map.get(cell.getRowIndex()).put(cell.getColumnIndex(),cellValue);
            }else {
                Map<Integer,Object> sp= new HashMap<>();
                sp.put(cell.getColumnIndex(),cellValue);
                map.put(cell.getRowIndex(),sp);
            }

        }catch (Exception e){
            return;
        }

    }

    private CellRangeAddress createRangeAddress(Sheet sheet, Cell cell, int start, int end, int direction) {
        CellRangeAddress rangeAddress = direction == 0 ?
                new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), start, end) :
                new CellRangeAddress(start, end, cell.getColumnIndex(), cell.getColumnIndex());

        return findExistAddress(sheet, rangeAddress, getCellVal(cell));
    }

    private CellRangeAddress findExistAddress(Sheet sheet, CellRangeAddress rangeAddress, String currentVal) {
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (int i = mergedRegions.size() - 1; i >= 0; i--) {
            CellRangeAddress exist = mergedRegions.get(i);
            if (exist.intersects(rangeAddress)) {
                if (exist.getLastRow() < rangeAddress.getLastRow()) {
                    exist.setLastRow(rangeAddress.getLastRow());
                }
                if (exist.getLastColumn() < rangeAddress.getLastColumn()) {
                    exist.setLastColumn(rangeAddress.getLastColumn());
                }
                sheet.removeMergedRegion(i);
                return exist;
            }
        }
        return rangeAddress;
    }

    private String getCellVal(Cell cell) {
        try {
            return cell.getStringCellValue();
        } catch (Exception e) {
            // 使用日志框架代替 System.out.printf
            // Logger logger = LoggerFactory.getLogger(OptimizedMergeCellStrategyHandler.class);
            // logger.error("读取单元格内容失败:行{} 列{}", cell.getRowIndex() + 1, cell.getColumnIndex() + 1, e);
            System.out.printf("读取单元格内容失败:行%d 列%d %n", (cell.getRowIndex() + 1), (cell.getColumnIndex() + 1));
            return null;
        }
    }

}

3.调用 

Set<Integer> set=new HashSet<>();
set.add(0);//列
WriteSheet writeSheet = EasyExcel.writerSheet(i,item.getAreaName()).registerWriteHandler(new OptimizedMergeCellStrategyHandler(true, true, 0, set)).build();

 public String SExportInspectionRecord(int ID,int ProjectID){
        try {
            List<MInspection> map=dInspection.DExportInspectionRecord(ID);
            if (map.size() == 0) {
                return "";
            }
            String FilePath = General.Instance().getWebPath() + "/Inspection/Excel/"+ProjectID+"/";
            String templateFileName=General.Instance().getWebPath()+"/templates/Template_InspectionReport.xlsx";
            General.Instance().CreateDirectory(FilePath);
            DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
            Calendar calendar = Calendar.getInstance();
            String dateName = df.format(calendar.getTime());
            String FileName= map.get(0).getProjectName()+"物联巡检记录"+ dateName + ".xlsx";
            String modeFileName= map.get(0).getProjectName()+"物联巡检记录Model"+ dateName + ".xlsx";
            Map<String, List<MInspection>> listInspectionGroup=map.stream().collect(Collectors.groupingBy(MInspection::getAreaName));
            List<String> linkNames=new LinkedList<>();
            for (String item:listInspectionGroup.keySet()) {
                linkNames.add(item);
            }
            //复制模板
            EasyExcelUtil.copyModel(templateFileName,FilePath+modeFileName,linkNames);
            ExcelWriter excelWriter = EasyExcel.write(FilePath+FileName).withTemplate(FilePath+modeFileName).build();
            Set<Integer> set=new HashSet<>();
            set.add(0);
            //.registerWriteHandler(new OptimizedMergeCellStrategyHandler(true, true, 3, set))
            for (int i=0;i<map.size();i++){
                MInspection item=map.get(i);
//                int index=3;
//                List<CellRangeAddress> cellRangeAddresss = new ArrayList<>();
//                //例如：从firstRow行到lastRow行的1列到1列合并 跨行策略
//                List<MInspectionRecord> Area= item.getInspectionRecord();
//                Map<String, List<MInspectionRecord>> listAreaGroup=Area.stream().collect(Collectors.groupingBy(MInspectionRecord::getInspectionObject,
//                        Collectors.collectingAndThen(
//                                Collectors.toList(),
//                                list -> {
//                                    list.sort(Comparator.comparing(MInspectionRecord::getInspectionObject));
//                                    return list;
//                                }
//                        )));


//                for (String model :listAreaGroup.keySet()) {
//                    cellRangeAddresss.add(new CellRangeAddress(index, index+listAreaGroup.get(model).size(), 0, 0));
//                    index=index+listAreaGroup.get(model).size()+1;
//                }
//                //注册跨行策略
//                MyMergeStrategy myMergeStrategy=new MyMergeStrategy(cellRangeAddresss);
                WriteSheet writeSheet = EasyExcel.writerSheet(i,item.getAreaName()).registerWriteHandler(new OptimizedMergeCellStrategyHandler(true, true, 0, set)).build();
                writeSheet.setSheetName(item.getAreaName());
                excelWriter.fill(item, writeSheet);
                excelWriter.fill(item.getInspectionRecord(), writeSheet);

            }

            excelWriter.finish();
            File file =new File(FilePath+modeFileName);
            file.delete();

            return "/Inspection/Excel/"+ProjectID+"/"+FileName;
        } catch (Exception ex) {
            General.Instance().Log("Error", java.util.logging.Level.WARNING,
                    "SInspection.SExportInspectionRecord:" + ex.getMessage());
            return "";

        }finally {

        }
    }

4.运行得到的结果

 

 

6代码来自
https://blog.csdn.net/weixin_50348837/article/details/132778982 

在这个基础上做了一些修改