Springboot与easypoi（2）：合并单元格、二级表头、动态导出

一、纵向合并单元格

使用@Excel(needMerge = true)标记的属性表示此单元格需要合并。@ExcelCollection表示一对多的集合，下面是合并单元格案例。

实体类

企业类：

package com.ywz.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

@Data
@ApiModel(description = "企业")
public class Enterprise {

    @Excel(name = "企业ID", needMerge = true)
    @ApiModelProperty("企业ID")
    private String id;

    @Excel(name = "企业名称", needMerge = true)
    @ApiModelProperty("企业名称")
    private String name;

    @Excel(name = "企业地址", needMerge = true)
    @ApiModelProperty("企业地址")
    private String address;

    @ExcelCollection(name = "企业收益")
    private List<EnterpriseEarning> enterpriseEarnings;
}

企业收入类：

package com.ywz.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;

import java.math.BigDecimal;

@Data
@ApiModel(description = "企业收益")
@AllArgsConstructor
public class EnterpriseEarning {
    @ApiModelProperty("收益ID")
    private String id;

    @ApiModelProperty("企业ID")
    private String enterpriseId;

    @Excel(name = "年份")
    @ApiModelProperty("年份")
    private String year;

    @Excel(name = "收益")
    @ApiModelProperty("收益")
    private BigDecimal earning;
}

导出测试

@PostMapping("/export")
    public void exportTest(HttpServletResponse response) {
        // 企业A
        Enterprise enterprise = new Enterprise();
        enterprise.setId("1");
        enterprise.setName("企业A");
        enterprise.setAddress("北京市朝阳区");
        List<EnterpriseEarning> enterpriseEarnings = new ArrayList<>();
        enterpriseEarnings.add(new EnterpriseEarning("1", "1", "2023", new BigDecimal(200)));
        enterpriseEarnings.add(new EnterpriseEarning("2", "1", "2024", new BigDecimal(300)));
        enterprise.setEnterpriseEarnings(enterpriseEarnings);
        // 企业B
        Enterprise enterprise2 = new Enterprise();
        enterprise2.setId("2");
        enterprise2.setName("企业B");
        enterprise2.setAddress("北京市朝阳区");
        List<EnterpriseEarning> enterpriseEarnings2 = new ArrayList<>();
        enterpriseEarnings2.add(new EnterpriseEarning("3", "2", "2023", new BigDecimal(222)));
        enterpriseEarnings2.add(new EnterpriseEarning("4", "2", "2024", new BigDecimal(333)));
        enterprise2.setEnterpriseEarnings(enterpriseEarnings2);
        // 导出
        List<Enterprise> excelList = new ArrayList<>();
        excelList.add(enterprise);
        excelList.add(enterprise2);
        EasyPoiUtil.exportExcel(excelList, "企业数据", "第一页", Enterprise.class, "企业数据.xlsx", response);
    }

导入测试

需要保证对应的实体类存在无参构造器，否则将无法创建对象；并且因为合并单元格的原因需要注意导入标题的行数。

    @PostMapping("/import")
    public void importTest() {
        String filePath = "C:\\Users\\86176\\Desktop\\企业数据.xlsx";
        // 注意第三个参数：标题行数，因为合并单元格的原因会导致标题行数变化
        List<Enterprise> enterpriseList = EasyPoiUtil.importExcel(filePath, 1, 2, Enterprise.class);
        System.out.println(enterpriseList);
    }

二、二级表头

实体类

@Excel的groupName属性可以定义一级表头。

package com.ywz.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;

@Data
@ApiModel(description = "学生")
public class Student {
    @ApiModelProperty(value = "主键")
    private Integer id;
    @ApiModelProperty(value = "姓名")
    @Excel(name = "学生名字", width = 20)
    private String name;
    @ApiModelProperty(value = "语文")
    @Excel(name = "语文", groupName = "成绩", orderNum = "1", width = 15)
    private BigDecimal score1;
    @ApiModelProperty(value = "数学")
    @Excel(name = "数学", groupName = "成绩", orderNum = "2", width = 15)
    private BigDecimal score2;
    @ApiModelProperty(value = "英语")
    @Excel(name = "英语", groupName = "成绩", orderNum = "3", width = 15)
    private BigDecimal score3;
    @ApiModelProperty(value = "性别")
    @Excel(name = "性别", orderNum = "4", width = 15)
    private String sex;

}

导出测试

    @PostMapping("/export")
    public void exportTest(HttpServletResponse response) {
        // 学生1
        Student student = new Student();
        student.setId(1);
        student.setName("张三");
        student.setScore1(new BigDecimal(100));
        student.setScore2(new BigDecimal(90));
        student.setScore3(new BigDecimal(80));
        student.setSex("男");
        // 学生2
        Student student2 = new Student();
        student2.setId(2);
        student2.setName("李四");
        student2.setScore1(new BigDecimal(120));
        student2.setScore2(new BigDecimal(92));
        student2.setScore3(new BigDecimal(82));
        student2.setSex("女");
        // 导出
        List<Student> studentList = new ArrayList<>();
        studentList.add(student);
        studentList.add(student2);
        // 创建导出参数对象，参数1：标题 参数2：sheet名
        ExportParams exportParams = new ExportParams("学生成绩", "学生成绩");
        EasyPoiUtil.defaultExport(studentList, Student.class, "学生成绩.xlsx", response, exportParams);
    }

三、动态导出

利用@Excel的 isColumnHidden 属性动态设置值，只是在导出时隐藏了该列，实际上也还是全部导出了。要彻底的不导出，官方也给出了解决方案：基于List< ExcelExportEntity> 的导出。

实体类

package com.ywz.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;

@Data
@ApiModel(description = "学生")
public class Student {
    @ApiModelProperty(value = "主键")
    private Integer id;
    @ApiModelProperty(value = "姓名")
    private String name;
    @ApiModelProperty(value = "语文")
    private BigDecimal score1;
    @ApiModelProperty(value = "数学")
    private BigDecimal score2;
    @ApiModelProperty(value = "英语")
    private BigDecimal score3;
    @ApiModelProperty(value = "性别")
    private String sex;
}

导出测试

@PostMapping("/export")
    public void exportTest(HttpServletResponse response) {
        // 学生1
        Student student = new Student();
        student.setId(1);
        student.setName("张三");
        student.setScore1(new BigDecimal(100));
        student.setScore2(new BigDecimal(90));
        student.setScore3(new BigDecimal(80));
        student.setSex("男");
        // 学生2
        Student student2 = new Student();
        student2.setId(2);
        student2.setName("李四");
        student2.setScore1(new BigDecimal(120));
        student2.setScore2(new BigDecimal(92));
        student2.setScore3(new BigDecimal(82));
        student2.setSex("女");
        // 导出
        List<Student> studentList = new ArrayList<>();
        studentList.add(student);
        studentList.add(student2);
        // 创建需要导出的列 ExcelExportEntity 参数1：表头 参数2：对应数据源属性 
        List<ExcelExportEntity> colList = new ArrayList<>();
        ExcelExportEntity name = new ExcelExportEntity("姓名", "name");
        name.setWidth(20);
        colList.add(name);
        ExcelExportEntity score1 = new ExcelExportEntity("语文", "score1");
        score1.setGroupName("成绩");
        score1.setWidth(15);
        colList.add(score1);
        ExcelExportEntity score2 = new ExcelExportEntity("数学", "score2");
        score2.setGroupName("成绩");
        score2.setWidth(15);
        colList.add(score2);
        ExcelExportEntity score3 = new ExcelExportEntity("英语", "score3");
        score3.setGroupName("成绩");
        score3.setWidth(15);
        colList.add(score3);
        // 导出参数
        ExportParams exportParams = new ExportParams("学生成绩表", "成绩表");
        EasyPoiUtil.dynamicExportExcel(studentList, "学生成绩表.xlsx", exportParams, colList, response);
    }