SpringBoot 整合Easyexcel，实现excel导出功能

最近在做业务报表，需要excel导出功能，就引进了easyexcel，具体的介绍这里就不写，更多的详细功能可以去 EasyExcel官网
这里就简单的写了个小demo

1. 导入easyexcel依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>

实体类

@Data
@HeadRowHeight(value = 35) // 表头行高
@ContentRowHeight(value = 25) // 内容行高
public class Student implements Serializable {
    @ExcelProperty(value = {"学生信息","学生基本信息","学生姓名"},order = 1)
    private String name;

    @ExcelProperty(value = {"学生信息","学生基本信息","学生年龄"},order = 2)
    private Integer age;

    @ExcelProperty(value = {"学生信息","学生基本信息","学生成绩"},order = 3)
    private BigDecimal score;

    @ExcelProperty(value = {"学生信息","学生结婚信息","婚姻情况"},order = 4)
    private String merry;
}

导出功能

 public class DownExcel {
    public static void download(HttpServletResponse response, Class t, List list) throws IOException, IllegalAccessException,InstantiationException {
        response.setContentType("application/vnd.ms-excel");// 设置文本内省
        response.setCharacterEncoding("utf-8");// 设置字符编码
        response.setHeader("Content-disposition", "attachment;filename="+"demo2.xlsx"); // 设置响应头
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), Student.class).build();
        // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
        WriteSheet writeSheet = EasyExcel.writerSheet("日报").needHead(Boolean.FALSE).build();
        // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
        WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();

        WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
        // 第一次写入会创建头
        excelWriter.write(list, writeSheet, writeTable0);
        // 第二次写如也会创建头，然后在第一次的后面写入数据
        excelWriter.write(list, writeSheet, writeTable1);
        // 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }
}

直接导出

private List<Student> data() {
    List<Student> list = new ArrayList<Student>();
    for (int i = 0; i < 10; i++) {
        Student student = new Student();
        student.setName("张三" + i);
        student.setAge(18 + i);
        student.setScore(new BigDecimal(i + ""));
        student.setMerry("已婚");
        list.add(student);
    }
    return list;
}
    //导出为Excel
    @RequestMapping("/downloadexcel.do")
    public void getExcel(HttpServletResponse response) throws IllegalAccessException, IOException,
            InstantiationException {
        List<Student> list = data();
        Student student = new Student();
        list.add(null);
        DownExcel.download(response, Student.class, list);
    }

结果如下：

因为写了两次表格所以有两个表格数据。

根据模板生成excel并导出

• 模板
  
• 代码

 public void complexFillWithTable(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");// 设置文本内省
        response.setCharacterEncoding("utf-8");// 设置字符编码
        response.setHeader("Content-disposition", "attachment;filename=" + "test2.xlsx"); // 设置响应头

        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量
        // 这里模板 删除了list以后的数据，也就是统计的这一行
        String resource = "D:/Study/springboot_excel/src/main/resources/static/model.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(resource).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        //横向填充;VERTICAL 纵向填充
        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
        excelWriter.fill(data(), fillConfig, writeSheet);
//        excelWriter.fill(data(), fillConfig, writeSheet);
        // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("date", "2021年10月9日13:28:28");
        excelWriter.fill(map, writeSheet);

        // 别忘记关闭流
        excelWriter.finish();
    }

• 结果