EasyPoi导出excel

使用EasyPoi导出报表一对多关系产生->Merged region A3 must contain 2 or more cells

1、产生异常原因
esaypoi在导出excel过程中若指定了合并关系，但是有单条数据不需要合并就会产生此问题，因为单个单元格是无法合并的。

2、解决方案
按照自己的业务自定义导出规则，参考以下案例：

实体类：

package com.example.entity;

import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.annotation.ExcelCollection;
import org.jeecgframework.poi.excel.annotation.ExcelTarget;

import java.util.List;
import java.util.Objects;

@ExcelTarget("school")
public class School {
    @Excel(name = "部门编号1", width = 30 , needMerge = true)
    private int num;
    @Excel(name = "部门编号2", width = 30 , needMerge = true)
    private String courseNum;
    @Excel(name = "部门编号3", width = 30 , needMerge = true)
    private String courseName;

    @ExcelCollection(name = "员工信息")
    private List<Info> infos;

    public int getNum() {
        return num;
    }

    public void setNum(int num) {
        this.num = num;
    }

    public String getCourseNum() {
        return courseNum;
    }

    public void setCourseNum(String courseNum) {
        this.courseNum = courseNum;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

    public List<Info> getInfos() {
        return infos;
    }

    public void setInfos(List<Info> infos) {
        this.infos = infos;
    }



    @Override
    public String toString() {
        return "School{" +
                "num=" + num +
                ", courseNum='" + courseNum + '\'' +
                ", courseName='" + courseName + '\'' +
                ", infos=" + infos +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        School school = (School) o;
        return this.num == school.num &&
                this.courseNum.equals(school.courseNum) &&
                this.courseName.equals(school.courseName);
    }

    @Override
    public int hashCode() {
        return Objects.hash(num, courseNum, courseName);
    }
}

package com.example.entity;

import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.annotation.ExcelTarget;

import java.util.Objects;

@ExcelTarget("info")
public class Info {
    @Excel(name = "序号", width = 30)
    private String infoName;
    @Excel(name = "序号", width = 30)
    private String infoContent;

    public String getInfoName() {
        return infoName;
    }

    public void setInfoName(String infoName) {
        this.infoName = infoName;
    }

    public String getInfoContent() {
        return infoContent;
    }

    public void setInfoContent(String infoContent) {
        this.infoContent = infoContent;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Info info = (Info) o;
        return infoName.equals(info.infoName) &&
                infoContent.equals(info.infoContent);
    }

    @Override
    public int hashCode() {
        return Objects.hash(infoName, infoContent);
    }
}

数据

    static {
        headerList = new ArrayList<>();
        headerList.add("序号");
        headerList.add("课程编号");
        headerList.add("课程名称");
        headerList.add("院系信息");

        for (int i = 0; i < 5; i++) {
            if(i == 0){
                School sq = new School();
                sq.setNum(10000);
                sq.setCourseNum("abc");
                sq.setCourseName("zhangsan!");
                List<Info> list1 = new ArrayList<>();
                Info is = new Info();
                is.setInfoName("dsaudhjkdhiufksafhuddfighidhwsiuy");
                is.setInfoContent("liejianjhfkbdnljsmdhbjsfnlnbfdshgvdfnasjhdvvbdfahfuebjhbuh");
                list1.add(is);
                sq.setInfos(list1);
                schoolList.add(sq);
            }
            List<Info> lists = new ArrayList<>();
            School sc = new School();
            sc.setCourseNum("course" + i);
            sc.setCourseName("abc" + i);
            sc.setNum(i);
            for (int j = 0; j < 6; j++) {
                Info info = new Info();
                info.setInfoName("infoTitle" + j);
                info.setInfoContent("abc123" + j + System.currentTimeMillis());
                lists.add(info);
            }
            sc.setInfos(lists);
            schoolList.add(sc);
        }
        School sq = new School();
        sq.setNum(10000);
        sq.setCourseNum("abc");
        sq.setCourseName("zhangsan!");
        List<Info> list1 = new ArrayList<>();
        Info is = new Info();
        is.setInfoName("dsaudhjkdhiufksafhuddfighidhwsiuy");
        is.setInfoContent("liejianjhfkbdnljsmdhbjsfnlnbfdshgvdfnasjhdvvbdfahfuebjhbuh");
        list1.add(is);
        sq.setInfos(list1);
        schoolList.add(sq);

        for (int i = 0; i < 5; i++) {
            List<Info> lists = new ArrayList<>();
            School sc = new School();
            sc.setCourseNum("course" + i);
            sc.setCourseName("abc" + i);
            sc.setNum(i);
            for (int j = 0; j < 7; j++) {
                Info info = new Info();
                info.setInfoName("infoTitle" + j);
                info.setInfoContent("abc123" + j + System.currentTimeMillis());
                lists.add(info);
            }
            sc.setInfos(lists);
            schoolList.add(sc);
        }
    }

业务

public static void fillData(List<String> headerList, List<School> schoolList) {
        String fileName = "院系数据导出";
        try {
            //创建工作簿
            Workbook workbook = new HSSFWorkbook();
            //创建sheet
            Sheet sheet = workbook.createSheet();
            setSheetSize(sheet);
            //定义表头excel样式
            CellStyle csTitle = setCellStyle(workbook, (short) 12, true, "宋体");
            //创建行写入表头
            Row row = sheet.createRow(0);
            //开始行初始值
            int firstRow = 1;
            //结束行初始值
            int lastRow = 0;
            //标记
            boolean isAdd = false;
            for (int i = 0; i < schoolList.size(); i++) {
                //一对多集合的长度
                int size = schoolList.get(i).getInfos().size();
                if (isAdd) {
                    lastRow = lastRow + size;
                } else {
                    lastRow = lastRow + size + 1;
                    isAdd = true;
                }
                if (size > 1) {
                    //合并单元格（起始行应该为第二行,结束行为对应的多出的集合长度 + 1）
                    CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow - 1, 0, 0);
                    sheet.addMergedRegion(cra);
                    firstRow = firstRow + size;
                } else {
                    firstRow = firstRow + size;
                }
            }
            for (int i = 0; i < headerList.size(); i++) {
                //创建单元格
                Cell cell = row.createCell(i);
                cell.setCellStyle(csTitle);
                cell.setCellValue(headerList.get(i));
            }
            CellStyle cs = setCellStyle(workbook, (short) 10, false, "宋体");
            //记录行号
            int rowNum = 0;
            //创建单元格,写入数据
            for (int i = 0; i < schoolList.size(); i++) {
                //行数应该为笛卡尔积
                School school = schoolList.get(i);
                List<Info> infos = school.getInfos();
                for (Info info : infos) {
                    Row rowData = sheet.createRow(++rowNum);
                    Cell cell1 = rowData.createCell(0);
                    cell1.setCellStyle(cs);
                    cell1.setCellValue(school.getNum());
                    Cell cell2 = rowData.createCell(1);
                    cell2.setCellStyle(cs);
                    cell2.setCellValue(school.getCourseNum());
                    Cell cell3 = rowData.createCell(2);
                    cell3.setCellStyle(cs);
                    cell3.setCellValue(school.getCourseName());
                    Cell cell4 = rowData.createCell(3);
                    cell4.setCellStyle(cs);
                    cell4.setCellValue(info.getInfoName());
                }
            }
            String tempDir = "D:\\Program Files\\Excel";
            File filePath = new File(tempDir + File.separator);
            if (!filePath.exists()) {
                filePath.mkdirs(); // 如果文件目录不存在就创建这个目录
            }
            System.out.println("导出成功~~~");
            FileOutputStream fos = new FileOutputStream("C:\\Users\\pc\\Desktop\\工作\\excelExport\\" + "test111.xlsx");
            workbook.write(fos);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

 /**
     * 设置sheet
     *
     * @param sheet
     */
    private static void setSheetSize(Sheet sheet) {
        sheet.setColumnWidth(0, 5000);
        sheet.setHorizontallyCenter(true);
        sheet.setVerticallyCenter(true);
        sheet.setDefaultRowHeight((short) 500);
        sheet.setColumnWidth(1, 5000);
        sheet.setColumnWidth(2, 5000);
        sheet.setColumnWidth(3, 5000);
    }

/**
     * 设置样式
     *
     * @param workbook
     * @param size
     * @param isBold
     * @param fontName
     * @return
     */
    private static CellStyle setCellStyle(Workbook workbook, short size, boolean isBold, String fontName) {
        workbook.setSheetName(0, "工作簿");
        //设置数据样式
        CellStyle csContent = workbook.createCellStyle();
        Font fonts = setFont(workbook, size, isBold, fontName);
        csContent.setVerticalAlignment(VerticalAlignment.CENTER);
        csContent.setAlignment(HorizontalAlignment.CENTER);
        csContent.setFont(fonts);
        return csContent;
    }

 /**
     * 设置字体
     *
     * @param workbook
     * @param size
     * @param isBold
     * @param fontName
     * @return
     */
    private static Font setFont(Workbook workbook, short size, boolean isBold, String fontName) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName(fontName);
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }

自定义业务之后，改变了合并规则，当产生单挑数据不进行合并，就可以完美的解决异常，如图所示，可以正确导出1对一和一对多关系。