apache.poi-excel多sheet导出，非模板方式合并单元格

一个对象集合（列表内容），一个对象（该对象后面需要我合并）
注释：这个只是一个参考，并不通用的，提供一种思路

工具类方法

 
    public static Workbook getWorkBookEntry(Map> maps, Map map2, int[] mergeIndex) {
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        int n = 0;
        for (Map.Entry> entry : maps.entrySet()) {
            Sheet sheet = null;
            try {
                sheet = workbook.createSheet();
                sheet.setColumnWidth(0, 20 * 256);
                workbook.setSheetName(n, entry.getKey());
                workbook.setSelectedTab(0);
                n++;
            } catch (Exception e) {
                e.printStackTrace();
            }
            
            List lists = entry.getValue();
            List list = null;
            list = lists.stream().map(nineClassSubclassVo -> {
                NineClassSubclass2Vo nineClassSubclass2Vo = new NineClassSubclass2Vo();
                //将userCheckPO拷贝给userCheckData
                BeanUtils.copyProperties(nineClassSubclassVo, nineClassSubclass2Vo);
                return nineClassSubclass2Vo;
            }).collect(Collectors.toList());

            // 获取实体所有属性
            Field[] fields = list.get(0).getClass().getDeclaredFields();
            // 列索引
            int index = 0;
            // 列名称
            String name = "";
            MyAnnotation myAnnotation;
            // 创建表头
            Row row = sheet.createRow(0);

            //设置每列的宽度
            sheet.setColumnWidth(1, 100 * 70);
            sheet.setColumnWidth(2, 100 * 40);
            sheet.setColumnWidth(3, 100 * 70);
            sheet.setColumnWidth(4, 100 * 40);
            sheet.setColumnWidth(6, 100 * 70);

            for (Field f : fields) {
                // 是否是注解
                if (f.isAnnotationPresent(MyAnnotation.class)) {
                    // 获取注解
                    myAnnotation = f.getAnnotation(MyAnnotation.class);
                    // 获取列索引
                    index = myAnnotation.columnIndex();
                    // 列名称
                    name = myAnnotation.columnName();
                    // 创建单元格
                    Cell cell = row.createCell(index, Cell.CELL_TYPE_STRING);
                    cell.setCellValue(name);

                    //处理单元格等样式问题
                    XSSFCellStyle style = handleCellStyle(workbook);
                    cell.setCellStyle(style);
                }
            }

            //根据第一列的内容判断后面的列内容需要合并 声明需要除了第一类外的合并的单元格数据
            List mergeCellIndexVos = new ArrayList<>();
            //有序的分组
            linkedHashMap> collect = list.stream().collect(Collectors.groupingBy(NineClassSubclass2Vo::getLabelParentName, linkedHashMap::new, Collectors.toList()));
            List strings = new ArrayList<>();
            collect.forEach((k, v) -> {
                strings.add(k);
            });
            int t = 1;
            for (int i = 0; i < strings.size(); i++) {
                if (i == 0) {
                    if (collect.get(strings.get(i)).size() == 1) {
                        t = t + 1;
                    } else {
                        MergeCellIndexVo mergeCellIndexVo = MergeCellIndexVo.builder()
                                .firsCol(6)
                                .lastCol(6)
                                .firstRow(t)
                                .lastRow(collect.get(strings.get(i)).size() + t -1)
                                .build();
                        t = collect.get(strings.get(i)).size() + t - 1;
                        mergeCellIndexVos.add(mergeCellIndexVo);
                    }
                } else {
                    if (collect.get(strings.get(i)).size() == 1) {
                        t = t + 1;
                    } else {
                        MergeCellIndexVo mergeCellIndexVo = MergeCellIndexVo.builder()
                                .firsCol(6)
                                .lastCol(6)
                                .firstRow(t + 1)
                                .lastRow(collect.get(strings.get(i)).size() + t)
                                .build();
                        t = t + collect.get(strings.get(i)).size();
                        mergeCellIndexVos.add(mergeCellIndexVo);
                    }
                }
            }
            
            List poiModels = new ArrayList();
            // 行索引  因为表头已经设置，索引行索引从1开始
            int rowIndex = 1;
            for (Object obj : list) {
                // 创建新行，索引加1,为创建下一行做准备
                row = sheet.createRow(rowIndex);
                for (int i = 0; i < fields.length; i++) {
                    // 设置属性可访问
                    fields[i].setAccessible(true);
                    // 判断是否是注解
                    if (fields[i].isAnnotationPresent(MyAnnotation.class)) {
                        // 获取注解
                        myAnnotation = fields[i].getAnnotation(MyAnnotation.class);
                        try {
                            String old = "";
                            
                            if (rowIndex > 1) {
                                old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();
                            }
                            
                            for (int j = 0; j < mergeIndex.length; j++) {
                                if (rowIndex == 1) {
                                    
                                    PoiModel poiModel = new PoiModel();
                                    poiModel.setOldContent(String.valueOf(fields[i].get(obj)));
                                    poiModel.setContent(String.valueOf(fields[i].get(obj)));
                                    poiModel.setRowIndex(1);
                                    poiModel.setCellIndex(i);
                                    poiModels.add(poiModel);
                                    break;
                                } else if (i > 0 && mergeIndex[j] == i) {
                                    
                                    
                                    if (!poiModels.get(i).getContent().equals(String.valueOf(fields[i].get(obj))) || poiModels.get(i).getContent().equals(String.valueOf(fields[i].get(obj))) && !poiModels.get(i - 1).getOldContent().equals(String.valueOf(fields[i - 1].get(obj)))) {
                                        //合并单元格区域只有一个单元格时，不合并
                                        if (poiModels.get(i).getRowIndex() == rowIndex - 1 && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()) {
                                            continue;
                                        }
                                        
                                        CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex(), rowIndex - 1, poiModels.get(i).getCellIndex(), poiModels.get(i).getCellIndex());
                                        //在sheet里增加合并单元格
                                        sheet.addMergedRegion(cra);
                                        
                                        poiModels.get(i).setContent(String.valueOf(fields[i].get(obj)));
                                        poiModels.get(i).setRowIndex(rowIndex);
                                        poiModels.get(i).setCellIndex(i);
                                    }
                                }
                                
                                if (mergeIndex[j] == i && i == 0 && !poiModels.get(i).getContent().equals(String.valueOf(fields[i].get(obj)))) {
                                    
                                    CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex(), rowIndex - 1, poiModels.get(i).getCellIndex(), poiModels.get(i).getCellIndex());
                                    //在sheet里增加合并单元格
                                    sheet.addMergedRegion(cra);
                                    
                                    poiModels.get(i).setContent(String.valueOf(fields[i].get(obj)));
                                    poiModels.get(i).setRowIndex(rowIndex);
                                    poiModels.get(i).setCellIndex(i);
                                }

                                
                                if (mergeIndex[j] == i && rowIndex == list.size()) {
                                    //合并单元格区域只有一个单元格时，不合并
                                    if (poiModels.get(i).getRowIndex() == rowIndex && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()) {
                                        continue;
                                    }
                                    CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex(), rowIndex, poiModels.get(i).getCellIndex(), poiModels.get(i).getCellIndex());
                                    //在sheet里增加合并单元格
                                    sheet.addMergedRegion(cra);
                                }
                            }
                            Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                            cell.setCellValue(String.valueOf(fields[i].get(obj)));
                            XSSFCellStyle style = handleCellStyle(workbook);
                            cell.setCellStyle(style);
                            
                            poiModels.get(i).setOldContent(old);

                        } catch (IllegalArgumentException e) {
                            e.printStackTrace();
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                }
                rowIndex++;
            }

            //合并每个大类的发现率的单元格数据
            if (!CollectionUtils.isEmpty(mergeCellIndexVos)) {
                for (int i = 0; i < mergeCellIndexVos.size(); i++) {
                    sheet.addMergedRegion(new CellRangeAddress(mergeCellIndexVos.get(i).getFirstRow(),mergeCellIndexVos.get(i).getLastRow(),mergeCellIndexVos.get(i).getFirsCol(),mergeCellIndexVos.get(i).getLastCol()));
                }
            }

            //计算总体发现率
            int lastRowNum = lists.size() + 1;
            Row lastrow = sheet.createRow(lastRowNum);
            //一会需要合并0到5
            Cell cellFirst1 = lastrow.createCell(0, Cell.CELL_TYPE_STRING);
            cellFirst1.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
            Cell cellFirst2 = lastrow.createCell(1, Cell.CELL_TYPE_STRING);
            cellFirst2.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
            Cell cellFirst3 = lastrow.createCell(2, Cell.CELL_TYPE_STRING);
            cellFirst3.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
            Cell cellFirst4 = lastrow.createCell(3, Cell.CELL_TYPE_STRING);
            cellFirst4.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
            Cell cellFirst5 = lastrow.createCell(4, Cell.CELL_TYPE_STRING);
            cellFirst5.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
            Cell cellFirst6 = lastrow.createCell(5, Cell.CELL_TYPE_STRING);
            cellFirst6.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());

            Cell cellLast = lastrow.createCell(6, Cell.CELL_TYPE_STRING);
            cellLast.setCellValue(map2.get(entry.getKey()).getAllFoundRateNum());
            //处理单元格等样式问题
            XSSFCellStyle style = handleCellStyle(workbook);
            cellFirst1.setCellStyle(style);
            cellFirst2.setCellStyle(style);
            cellFirst3.setCellStyle(style);
            cellFirst4.setCellStyle(style);
            cellFirst5.setCellStyle(style);
            cellFirst6.setCellStyle(style);

            cellLast.setCellStyle(style);
            sheet.addMergedRegion(new CellRangeAddress(lastRowNum,lastRowNum,0,5));
            lastrow.setHeight((short) (25 * 20));
        }
        return workbook;
    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }

    private static XSSFCellStyle handleCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        // 指定单元格居中对齐
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        style.setWrapText(true);
        // 设置字体
        XSSFFont font = workbook.createFont();
        //font.setBoldweight(fontbold);
        font.setFontName("宋体");
        font.setFontHeight((short) 300);
        style.setFont(font);
        setBorder(style, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));
        return style;
    }

    public static void setResponseStream(HttpServletResponse response, String fileName) {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception e) {
            new RuntimeException("导出失败，原因：" + e.getMessage());
        }
    }

实体类

package com.jydw.drone.entity.statistics.respvo;

import com.jydw.drone.statistics.MyAnnotation;
import lombok.*;


@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class NineClassSubclassVo implements Comparable {

    
    private Integer factoryId;

    
    private String factoryName;

    
    private String labelId;

    
    @MyAnnotation(columnIndex=1,columnName="缺陷描述")
    private String labelName;

    
    private String labelParentId;

    
    @MyAnnotation(columnIndex=0,columnName="缺陷类别")
    private String labelParentName;

    
    private Integer yesNum;

    
    private Integer leakNum;

    
    private Integer errorNum;

    
    private Integer foundSon;

    
    @MyAnnotation(columnIndex=2,columnName="缺陷数量")
    private Integer foundMother;

    
    @MyAnnotation(columnIndex=4,columnName="发现率")
    private String foundRate;

    
    private Integer falseSon;

    
    private Integer falseMother;

    
    @MyAnnotation(columnIndex=5,columnName="误检比")
    private String falseRate;

    
    private Double totalFoundRate;

    
    private String totalFoundRateName;

    
    private String totalFalseRate;

    
    @MyAnnotation(columnIndex=3,columnName="占缺陷数量比例")
    private String oneTotalNumRate;

    
    private Integer orderParent;

    
    private Integer orderSon;

    
    private String foundAllRate;

    
    @Override
    public int compareTo(NineClassSubclassVo o) {
        if (this.getFactoryId() > o.getFactoryId()) {
            return 1;
        } else if (this.getFactoryId() < o.getFactoryId()) {
            return -1;
        } else {
            if (this.getOrderParent() > o.getOrderParent()) {
                return 1;
            } else if (this.getOrderParent() < o.getOrderParent()) {
                return -1;
            } else {
                if (this.getOrderSon() > o.getOrderSon()) {
                    return 1;
                } else if (this.getOrderSon() < o.getOrderSon()) {
                    return -1;
                } else {
                    return 0;
                }
            }
        }
    }

}

注解

package com.jydw.drone.statistics;

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;


@Retention(RetentionPolicy.RUNTIME)
public @interface MyAnnotation {

    
    public int columnIndex() default 0;

    
    public String columnName() default "";

}

中间转换类

package com.jydw.drone.entity.statistics.respvo;

import lombok.Data;


@Data
public class PoiModel {

    public String oldContent;
    public String content;
    public Integer rowIndex;
    public Integer cellIndex;
}

结果如下图