7 行代码优雅地实现 Excel 文件生成&下载功能

欢迎关注博主公众号「小哈学Java」, 专注于分享Java领域干货文章, 关注回复「资源」, 免费领取全网最热的Java架构师学习PDF, 转载请注明出处 https://www.exception.site/essay/how-to-create-excel-by-seven-line-code

7 行代码优雅地实现 Excel 文件生成&下载功能

一、前言

关于导出 Excel 文件,可以说是大多数服务中都需要集成的功能。那么,要如何优雅快速地(偷懒地)去实现这个功能呢?

你可能第一想法是:这还不简单?用 Apache 开源框架 poi, 或者 jxl 都可以实现啊。面向百度编程,把代码模板 copy 下来,根据自己的业务再改改,能有多难?

你仿佛在逗我笑

嗯.. 的确不难,但是你的代码可能是下面这个熊样子的:

用传统excel框架生成文件的代码模板

上面这段代码看上去是不是又臭又长呢?今天,小哈将教您如何使用 7 行代码搞定 Excel 文件生成功能!

我要开始装逼了

二、Apache poi、jxl 的缺陷

在说如何实现之前,我们先来讨论一下传统 Excel 框架的不足!除了上面说的,Apache poi、jxl 都存在生成 excel 文件不够简单优雅快速外,它们都还存在一个严重的问题,那就是非常耗内存严重时会导致内存溢出

POI 虽然目前来说,是 excel 解析框架中被使用最广泛的,但这个框架并不完美。

为什么这么说呢?

开发者们大部分使用 POI,都是使用其 userModel 模式。而 userModel 的好处是上手容易使用简单,随便拷贝个代码跑一下,剩下就是写业务转换了,虽然转换也要写上百行代码,但是还是可控的。

然而 userModel 模式最大的问题是在于,对内存消耗非常大,一个几兆的文件解析甚至要用掉上百兆的内存。现实情况是,很多应用现在都在采用这种模式,之所以还正常在跑是因为并发不大,并发上来后,一定会OOM或者频繁的 full gc。

三、阿里出品的 EasyExcel,安利一波

什么是 EasyExcel? 见名知意,就是让你操作 Excel 异常的酸爽。先来看下 EasyExcel GitHub 官方截图:

easyExcel GitHub 截图

截止目前为止已有 5519 Star, 官方对其的简介是:

快速、简单避免OOM的java处理Excel工具!

以下是官方介绍:

esayExcel GitHub 简介

四、EasyExcel 解决了什么

主要来说,有以下几点:

  • 传统 Excel 框架,如 Apache poi、jxl 都存在内存溢出的问题;
  • 传统 excel 开源框架使用复杂、繁琐;
  • EasyExcel 底层还是使用的 poi, 但是做了很多优化,比如修复了并发情况下的一些 bug, 具体修复细节,可阅读官方文档 https://github.com/alibaba/easyexcel

好像很厉害的样子

五、快速上手

5.1 添加依赖

<!--alibaba easyexcel-->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>1.1.2-beta5</version>
</dependency>

5.2 七行代码搞定 Excel 生成

使用EasyExcel生成Excel模板代码

@Test
public void writeExcel1() throws Exception {
  // 文件输出位置
  OutputStream out = new FileOutputStream("/Users/a123123/Work/tmp_files/test.xlsx");

  ExcelWriter writer = EasyExcelFactory.getWriter(out);

  // 写仅有一个 Sheet 的 Excel 文件, 此场景较为通用
  Sheet sheet1 = new Sheet(1, 0, WriteModel.class);

  // 第一个 sheet 名称
  sheet1.setSheetName("第一个sheet");

  // 写数据到 Writer 上下文中
  // 入参1: 创建要写入的模型数据
  // 入参2: 要写入的目标 sheet
  writer.write(createModelList(), sheet1);

  // 将上下文中的最终 outputStream 写入到指定文件中
  writer.finish();

  // 关闭流
  out.close();
}

上面这段示例代码中,有两个点很重要,小哈已经重点标注标:

  • :WriteModel 这个对象就是要写入 Excel 的数据模型对象,**等等,你这好像不行吧?表头 head,以及每个单元格内的数据顺序都没指定,能达到想要的效果么?别急,后面会讨论这块!
  • :创建需要写入的数据集,当然了,正常业务中,这块都是从数据库中查询出来的。

PS: 如果说写入的数据量很大,需要做分片查询再写入的处理,否则可能会 OOM(Out of Memory).

回过头来,我们来看看 WriteModel 这个对象内部到底有什么幺蛾子!

WriteModel对象

/**
 * @author 微信公众号: 小哈学Java
 * @Site: www.exception.site
 * @date 2019/5/9
 * @time 下午2:07
 * @discription 写入Excel模型对象
 **/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class WriteModel extends BaseRowModel {

    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    @ExcelProperty(value = "密码", index = 1)
    private String password;

    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;
}

ExayExcel 提供注解的方式, 来方便的定义 Excel 需要的数据模型:

  • :首先,定义的写入模型必须要继承自 BaseRowModel.java;
  • :通过 @ExcelProperty 注解来指定每个字段的列名称,以及下标位置

同时,上面定义的 createModelList() 方法也很简单,通过循环,创建一个写入模型的 List 集合:

createModleList()

废话不多说,这个快速接入的案例也介绍的差不多了,跑一跑单元测试看下实际效果:

测试结果

怎么样,效果还是挺棒棒的!

厉害了

六、特殊场景支持

在实际的业务中,我们还会有一些特需的需求,比如说下面这些。

6.1 动态生成 Excel 内容

上面的例子是基于注解的,也就是说表头 head, 以及内容都是写死的,换句话说,我定义好了一个数据模型,那么,生成的 Excel 文件也就是只能遵循这种模型来了,但是,实际业务中可能会存在动态变化的需求,要怎么做呢?

动态生成Excel

@Test
public void writeExcel2() throws Exception {
  // 文件输出位置
  OutputStream out = new FileOutputStream("/Users/a123123/Work/tmp_files/test2.xlsx");

  ExcelWriter writer = EasyExcelFactory.getWriter(out);

  // 动态添加表头,适用一些表头动态变化的场景
  Sheet sheet1 = new Sheet(1, 0);

  sheet1.setSheetName("第一个sheet");

  // 创建一个表格,用于 Sheet 中使用
  Table table1 = new Table(1);

  // 无注解的模式,动态添加表头
  table1.setHead(DataUtil.createTestListStringHead());
  // 写数据
  writer.write1(createDynamicModelList(), sheet1, table1);

  // 将上下文中的最终 outputStream 写入到指定文件中
  writer.finish();

  // 关闭流
  out.close();
}
  • :无注解模式,动态添加表头,也可自由组合复杂表头,代码如下:

动态生成Excel表头数据

public static List<List<String>> createTestListStringHead(){
    // 模型上没有注解,表头数据动态传入
    List<List<String>> head = new ArrayList<List<String>>();
    List<String> headCoulumn1 = new ArrayList<String>();
    List<String> headCoulumn2 = new ArrayList<String>();
    List<String> headCoulumn3 = new ArrayList<String>();
    List<String> headCoulumn4 = new ArrayList<String>();
    List<String> headCoulumn5 = new ArrayList<String>();

    headCoulumn1.add("第一列");headCoulumn1.add("第一列");headCoulumn1.add("第一列");
    headCoulumn2.add("第一列");headCoulumn2.add("第一列");headCoulumn2.add("第一列");

    headCoulumn3.add("第二列");headCoulumn3.add("第二列");headCoulumn3.add("第二列");
    headCoulumn4.add("第三列");headCoulumn4.add("第三列2");headCoulumn4.add("第三列2");
    headCoulumn5.add("第一列");headCoulumn5.add("第3列");headCoulumn5.add("第4列");

    head.add(headCoulumn1);
    head.add(headCoulumn2);
    head.add(headCoulumn3);
    head.add(headCoulumn4);
    head.add(headCoulumn5);
    return head;
}
  • :创建动态数据,注意这里的数据类型是 Object:

创建动态数据

跑一下单元测试,看下效果:

动态创建效果测试

6.2 自定义表头以及内容样式

我想自定义表头,内容样式,咋办?自定义表格样式

我们复用了上面的示例代码,并额外添加了设置自定义表格样式的代码, createTableStytle() 具体内容如下:

创建表格样式代码

public static TableStyle createTableStyle() {
    TableStyle tableStyle = new TableStyle();
    // 设置表头样式
    Font headFont = new Font();
    // 字体是否加粗
    headFont.setBold(true);
    // 字体大小
    headFont.setFontHeightInPoints((short)12);
    // 字体
    headFont.setFontName("楷体");
    tableStyle.setTableHeadFont(headFont);
    // 背景色
    tableStyle.setTableHeadBackGroundColor(IndexedColors.BLUE);


    // 设置表格主体样式
    Font contentFont = new Font();
    contentFont.setBold(true);
    contentFont.setFontHeightInPoints((short)12);
    contentFont.setFontName("黑体");
    tableStyle.setTableContentFont(contentFont);
    tableStyle.setTableContentBackGroundColor(IndexedColors.GREEN);
    return tableStyle;
}

我们可以通过 TableStyle 这个类来设置表头、表格主题的样式。

6.3 合并单元格

我们可以通过 merge() 方法来合并单元格:

合并单元格

注意下标是从 0 开始的,也就是说合并了第六行到第七行,其中的第一列到第五列,跑下代码,看下效果:

合并单元格效果图

6.4 自定义处理

对于更复杂的处理,EasyExcel 预留了 WriterHandler 接口来,允许你自定义处理代码:

WriterHandler

接口中定义了三个方法:

  • sheet(): 在创建每个 sheet 后自定义业务逻辑处理;
  • row(): 在创建每个 row 后自定义业务逻辑处理;
  • cell(): 在创建每个 cell 后自定义业务逻辑处理;

我们实现了该接口后,编写自定义逻辑处理代码,然后调用 getWriterWithTempAndHandler() 静态方法获取 ExcelWriter 对象时,传入 WriterHandler 的实现类即可。

传入WriterHandler

比如下面的示例代码:

ExcelWriter writer = EasyExcelFactory.getWriterWithTempAndHandler(null, out, ExcelTypeEnum.XLSX, true, new MyWriterHandler());

七、Web 下载示例代码

public class Down {
    @GetMapping("/a.htm")
    public void cooperation(HttpServletRequest request, HttpServletResponse response) {
	String fileName = new String(("UserInfo " + new SimpleDateFormat("yyyy-MM-dd").format(new Date()))
                .getBytes(), "UTF-8");
        ServletOutputStream out = response.getOutputStream();
         response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
        Sheet sheet1 = new Sheet(1, 0);
        sheet1.setSheetName("第一个sheet");
        writer.write0(getListString(), sheet1);
        writer.finish();
        out.flush();
        }
    }
}

八、需要注意的点

8.1 写入大数据时,需分片

比如说,我们需要从数据库中查询出数据量较大时,我们需要在业务层做分片处理,也就是,我们需要分多次查询,再写入,防止内存溢出 OOM.

8.2 Excel 最大行数问题

Excel 03, 07 版本均有行数、列数的限制:

版本最大行最大列
Excel 200365536256
Excel 2007104857616384

csv 由于是文本文件,实际上没有最大行数的限制,但是用 Excel 客户端打开还是多了不显示。

也就是说,如果你想写入更多的行数是不行的,强行这么做,程序会报类似如下异常

Invalid row number (1048576) outside allowable range (0..1048575)

如何解决呢?

  1. 分多个 Excel 文件写入;
  2. 同一个 Excel 文件,分多个 Sheet 写入;

九、总结

小哈今天主要给小伙伴介绍了 EasyExcel, 为什么要使用它,以及演示了相关示例代码。当然了,EasyExcel 除了写 Excel 文件外,它还有快速读取 Excel 的功能,由于本文主要介绍的是:如何优雅地实现 Excel 文件生成,所以就没有介绍了,有兴趣的小伙伴们,也可以去 GitHub 官网去去查看相关文档。

最后,祝您看完本文后有所收获,下期见!

十、GitHub 源码地址

https://github.com/weiwosuoai/spring-boot-tutorial/tree/master/spring-boot-excel

十一、Ref

https://github.com/alibaba/easyexcel