阿里开源的这个库,让 Excel 导出不再复杂(简简单单的写)

该图片由dae jeung kimPixabay上发布

你好,我是看山。

导出是中后台常见的功能,Excel文件是常见的导出格式。

在Java栈中,常用的是JXL(目前改名为JExcel)Apache POI。其中jxl最后的更新时间是2012,除了老系统中能看到影子,几乎见不到踪迹了。目前基本上是POI一统天下。

今天要说的EasyExcel阿里巴巴开源的Excel导出类库,是对POI的封装,实现了很多高级功能,并且留出扩展口,支持扩展定制化功能。打个比喻,POI相当于乐高积木,一个个的小积木可以自由组装,只能动手能力强,就可以随心所欲的组装。EasyExcel更像是把这些小积木提前组装好,想要搭建房子,就直接找组装好的屋顶、围墙就行。如果仅仅如此,那EasyExcel仅仅就是简单的工具包,其更加吸引人的地方是对于内存的控制,它通过压缩文件、分批读取、抛弃不重要数据、文件缓存等多种方式,降低内存消耗。

内容比较多,文内只会列出关键代码,想要完整源码,可以关注公号「看山的小屋」回复“easyexcel”获取。

最简单的写表格

开始之前,先定义一下基础类,这个类将贯穿全文,所有的功能都是在这个类的简单变形。

@Data
public class Item {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    @ExcelIgnore
    private String ignore;
}

根据头对象和列表向一个工作表中写一个表格

这是最简单的一种实现,只需要定义一个对象类,然后读取数据列表即可。

/**
 * 借助{@link com.alibaba.excel.write.builder.ExcelWriterSheetBuilder}自动创建{@link com.alibaba.excel.ExcelWriter}写入数据。
 * <p>
 * 提供列表和函数作为数据源
 */
public static void writeAutoWriter() {
    final String fileName = defaultFileName("writeAutoWriter");
    EasyExcelFactory.write(fileName)
            .head(Item.class)
            .sheet("模板")
            .doWrite(WriteSample::sampleItems);
}

EasyExcel提供了EasyExcelFactory类,API方法也是fluent方式,可以如丝般顺滑的实现生成Excel文件。如果感觉EasyExcelFactory太长,还可以直接写作EasyExcel,这是EasyExcelFactory的子类,类似于别名。

不过,这种别名定义方式,在有些规范中属于smell code,所以,根据自己或者公司规范选择吧。

com.alibaba.excel.EasyExcelFactory#write(java.lang.String)方法的参数传的是导出文件的文件名,如果并不需要生成文件,只需要创建文件流,也可以传入一个输出流OutputStream,这样就可以更加灵活的实现生成逻辑了。

com.alibaba.excel.metadata.AbstractParameterBuilder#head()方法是定义表头,只要传入一个类,就会读取这个类的所有字段作为表头。如果字段上com.alibaba.excel.annotation.ExcelProperty注解,定义了value,就会取value的值作为表头。此处还有很多操作,比如,value是数组,可以定义多个,如果是相邻字段定义了相同的表头,会合并单元格,表体内容会选择第一个单元格的内容。这个注解还可以定义indexorderconverter等,后面会一一给出例子。

com.alibaba.excel.write.builder.ExcelWriterBuilder#sheet()方法定义工作表,有多个重载方法,可以定义sheetNo指明是第几个工作表,可以传入sheetName指明工作表名称。

com.alibaba.excel.write.builder.ExcelWriterSheetBuilder#doWrite()方法就是写Excel文件了,传入全部的列表数据,或者使用Java8+的Supplier函数。还可以实现分页写入,后面会给出例子。这个方法会自动关闭文件流,真是很贴心。

结果为:

根据头对象和列表向一个工作表中写一个表格

根据头对象和列表向多个工作表中写数据

上面是向一个工作表写数据,接下来我们向多个工作表写数据。

这个例子会涉及更多的内部对象,比如:ExcelWriter、WriteSheet。

/**
 * 手动创建{@link com.alibaba.excel.ExcelWriter},指定sheet写入数据。
 * <p>
 * 提供列表和函数作为数据源
 */
public static void writeManualWither() {
    String fileName = defaultFileName("writeManualWriter");
    ExcelWriter excelWriter = null;
    try {
        excelWriter = EasyExcelFactory.write(fileName)
                .head(Item.class)
                .build();
        final WriteSheet writeSheet1 = EasyExcelFactory.writerSheet("模板1").build();
        excelWriter.write(WriteSample::sampleItems, writeSheet1);

        final WriteSheet writeSheet2 = EasyExcelFactory.writerSheet("模板2").build();
        excelWriter.write(sampleItems(), writeSheet2);
    } finally {
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}

因为是想多个工作表中写数据,我们就不能直接使用doWrite方法了。

com.alibaba.excel.ExcelWriter类是Excel写对象,用来创建Excel工作簿的。com.alibaba.excel.write.metadata.WriteSheet类是Sheet写对象,用来创建Sheet工作表的。通过com.alibaba.excel.ExcelWriter#write()方法,指定写入数据和写入的目标工作表,就可以实现向多个工作表中写数据的功能。

此处需要注意,我们在创建ExcelWriter对象时,调用了head()方法定义了表头,这是整个Excel的定义,sheet会继承这个定义。这样,整个Excel文件中的所有工作表,表头都是相同的。不要停,后面会给出不同工作表定义不同表头的示例。

结果为:

根据头对象和列表向多个工作表中写数据

按照定义指定导出列

在后台系统中,会有行列权限的控制。行权限,通过数据行实现,只导出有权限的行数据即可。列权限,可以通过只导出有权限的列,排除没有权限的列(通常是分等级的敏感数据)。

有时候需要定制化导出,导出所有列表格比较大,用户根据需要指定需要导出的列。

排除指定列

private static void writeExcludeColumn() {
    String fileName = defaultFileName("writeExcludeColumn");
    Set<String> excludeColumnFiledNames = new HashSet<>();
    excludeColumnFiledNames.add("date");

    EasyExcelFactory.write(fileName)
            .head(Item.class)
            .excludeColumnFiledNames(excludeColumnFiledNames)
            .sheet("模板")
            .doWrite(WriteSample::sampleItems);
}

这个需求,需要借助com.alibaba.excel.write.builder.AbstractExcelWriterParameterBuilder#excludeColumnFiledNames方法。这个方法是在ExcelWriterBuilderExcelWriterSheetBuilderExcelWriterTableBuilder的父类中定义,也就是说,可以是整个Excel工作簿都排除指定字段,也可以是某个sheet工作表排除指定字段,还可以是table表格排除指定字段。

除了excludeColumnFiledNames通过字段名排除字段,还可以使用excludeColumnIndexes指定字段下标排除列,如果需要控制下标,需要在字段上定义ExcelProperty指明index属性,这样也能够更好的固定字段下标。

结果为:

排除指定列

只导出指定列

private static void writeIncludeColumn() {
    String fileName = defaultFileName("writeIncludeColumn");
    Set<String> includeColumnFiledNames = new HashSet<>();
    includeColumnFiledNames.add("date");
    EasyExcelFactory.write(fileName)
            .head(Item.class)
            .includeColumnFiledNames(includeColumnFiledNames)
            .sheet("模板")
            .doWrite(WriteSample::sampleItems);
}

这个需求,需要借助com.alibaba.excel.write.builder.AbstractExcelWriterParameterBuilder#includeColumnFiledNames方法。与excludeColumnFiledNames是相似,都是可以分级定义,用起来也是一样的,只不过功能相反而已。

同样的,也可以使用includeColumnIndexes方法,通过指定字段下标指定列。

结果为:

只导出指定列

表头

表头的定义是比较关键的,会直接影响Excel文件的质量。所以,EasyExcel提供了比较丰富的表头定义方法。

原始表头

这里定义了一个新的基础类:EmptyItem,与Item的区别是移除了ExcelProperty的定义。

@Data
public class EmptyItem {
    private String string;
    private Date date;
    private Double doubleData;
}

在这种情况下,会直接使用EmptyItem对象的字段作为表头名称。

private static void writeNoAnnotation() {
    final String fileName = defaultFileName("writeNoAnnotation");
    EasyExcelFactory.write(fileName)
            .head(EmptyItem.class)
            .sheet("模板")
            .doWrite(WriteSample::sampleItems);
}

这里可以看到,EasyExcel对于表头对象、表体列表对象,没有强制要求必须是相同的对象,只要字段一致,就能够正常组装数据。

结果为:

原始表头

自定义表头

使用字段作为表头显然不是我们想要的,EasyExcel提供了ExcelProperty注解,可以定义表头的名称。这个注解还提供了indexorder两个属性,可以定义列的位置和顺序。

@Data
public class IndexItem {
    @ExcelProperty(value = "字符串标题", index = 1)
    private String string;
    @ExcelProperty(value = "日期标题", index = 3)
    private Date date;
    @ExcelProperty(value = "数字标题", index = 5)
    private Double doubleData;
}

使用起来也很简单:

private static void writeWithIndex() {
    final String fileName = defaultFileName("writeWithIndex");
    EasyExcelFactory.write(fileName)
            .head(IndexItem.class)
            .sheet("模板")
            .doWrite(WriteSample::sampleItems);
}

结果为:

自定义表头

这里需要注意一下,在使用ExcelProperty注解时,index表示字段放置第几列,order表示顺序。

根据index和order的不同语义,对两者的控制不同。如果index相同,直接会抛出异常,因为程序无法判断这个列放那个字段。如果index值中间有空的数字,就会出现空列。如果order和index同时使用,index优先占据位置,order做排序。index=-1的话,使用java默认排序,order值越小,列越靠前。

ExcelProperty的value属性是字符串数组,相当于一个字段可以定义多个头,这样就可以实现多级表头。同时,如果位置相邻的列定义列明相同,还会合并列。比如:

@Data
public class ComplexHeadItem {
    @ExcelProperty({"大标题", "字符串标题"})
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty({"大标题", "数字标题0"})
    private Double doubleData;
    @ExcelProperty({"数字标题"})
    private Double doubleData1;
    @ExcelProperty({"数字标题"})
    private Double doubleData2 = 0.0;
}

结果为:

自定义表头-合并

动态表头、表体

前面的例子中,表头定义都是传入一个对象,通过解析对象的属性字段,抽取表头定义。有的时候,我们没有办法提前定义表头对象,或者说,表头是根据条件、权限等因素动态变化的,这个时候,就可以使用EasyExcel提供的动态表头功能了。

/**
 * 动态表头,传入的是{@code List<List<String>>}格式数据。
 * <p>
 * 可以实现多层表头。
 */
private static void writeDynamicHead() {
    String fileName = defaultFileName("writeDynamicHead");
    EasyExcelFactory.write(fileName)
            .head(dynamicHead())
            .sheet()
            .doWrite(sampleItems());
}

private static List<List<String>> dynamicHead() {
    List<List<String>> heads = new ArrayList<>();
    final List<String> head0 = new ArrayList<>(Arrays.asList("头0", "字符串标题【动态】"));
    heads.add(head0);
    final List<String> head1 = new ArrayList<>(Arrays.asList("头0", "日期标题【动态】"));
    heads.add(head1);
    final List<String> head2 = new ArrayList<>(Collections.singletonList("数字标题【动态】"));
    heads.add(head2);
    return heads;
}

使用com.alibaba.excel.metadata.AbstractParameterBuilder#head(java.util.List<java.util.List<java.lang.String>>)方法,传入List<List<String>>类型的数据即可。

结果为:

动态表头、表体

当然,这样做还不是彻底的动态。我们可以使用com.alibaba.excel.write.builder.ExcelWriterSheetBuilder#doWrite(java.util.Collection<?>)实现动态表体。代码如下:

private static void writeDynamicData() {
    String fileName = defaultFileName("writeDynamicData");
    EasyExcelFactory.write(fileName)
            .head(dynamicHead())
            .sheet()
            .doWrite(dynamicData());
}

private static List<List<Object>> dynamicData() {
    List<List<Object>> list = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        List<Object> data = new ArrayList<>();
        data.add("字符串" + i);
        data.add(new Date());
        data.add((i + 1) * 0.1);
        list.add(data);
    }
    return list;
}

结果为:

动态表头、表体

表头国际化

互联网无国界,很多时候,我们需要实现国际化。这个时候,我们可以使用动态表头功能,传入不同的表头定义,生成不同的Excel文件。有时候,我们还需要提前定义表体的格式,使用动态表体可以实现。有没有更加简单方法呢?必须有。

先定义格式化表头对象:

@Data
public class FormatContentItem {
    @ExcelProperty(value = "字符串标题", converter = TitleFormatConverter.class)
    private String string;
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(value = "日期标题")
    private Date date;
    @NumberFormat("0.000%")
    @ExcelProperty("数字标题")
    private Double doubleData;
}

再实现国际化:

/**
 * 可以同时设置head参数:
 * {@link AbstractParameterBuilder#head(java.util.List)}
 * {@link AbstractParameterBuilder#head(java.lang.Class)}
 * <p>
 * 对于表头设置,最终起作用的是{@link AbstractParameterBuilder#head(java.util.List)},这样的话,我们可以实现国际化的配置。
 */
private static void writeDynamicMultiHead() {
    String fileName = defaultFileName("writeDynamicMultiHead");
    EasyExcelFactory.write(fileName)
            .head(dynamicHead())
            .head(FormatContentItem.class)
            .sheet()
            .doWrite(sampleItems());
}

结果为:

表头国际化

多次写入

一般来说,中后台的数据量都不小,有时候需要一次导出几十万行数据,甚至更多,而这种操作并不是常态,如果内存配置比较大,那大多数时间内存都是闲置状态。此时,就可以借助EasyExcel的文件缓存能力,分批写入数据。

向同一个excel同一个sheet中多次写入

首先是比较常见的场景,向一个sheet工作表中分批写入数据。

private static void writeOneSheet() {
    String fileName = defaultFileName("writeOneSheet");
    ExcelWriter excelWriter = null;
    try {
        excelWriter = EasyExcelFactory.write(fileName)
                .head(Item.class)
                .build();
        final WriteSheet writeSheet = EasyExcelFactory.writerSheet("模板").build();
        for (int i = 0; i < 5; i++) {
            excelWriter.write(sampleItems(), writeSheet);
        }
    } finally {
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}

首先定义ExcelWriter可以操作整个Excel工作簿,然后定义ExcelWritersheet工作表,接下来就是循环调用com.alibaba.excel.ExcelWriter.write(java.util.Collection<?>, com.alibaba.excel.write.metadata.WriteSheet)方法,将数据写入到指定的sheet工作表中。这里需要注意的是,最后一定要调用com.alibaba.excel.ExcelWriter.finish方法,表示停止写入并关闭流。

结果为:

向同一个excel同一个sheet中多次写入

向同一个excel不同sheet中多次写入(表头相同)

还有一种场景是按月导出全年的订单数据,每个月一个sheet工作表,这种导出的方式,表头都是相同的。根据上面的经验,我们需要定义多个WriteSheet工作表对象,然后向该对象中写数据。

这里还可能有一种情况,就是我们需要在多个WriteSheet工作表对象之间切换写入,为了不会找错对象,可以指定sheetNo

代码如下:

private static void writeDiffSheetWithSameHead() {
    String fileName = defaultFileName("writeDiffSheetWithSameHead");
    ExcelWriter excelWriter = null;
    try {
        excelWriter = EasyExcelFactory.write(fileName)
                .head(Item.class)
                .build();
        for (int i = 0; i < 5; i++) {
            final WriteSheet writeSheet = EasyExcelFactory.writerSheet(i, "模板" + i)
                    .build();
            excelWriter.write(sampleItems(), writeSheet);
        }
    } finally {
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}

结果为:

向同一个excel不同sheet中多次写入(表头相同)

向同一个excel不同sheet中多次写入(表头相同)

向同一个excel不同sheet中多次写入(表头不同)

还有一种场景,导出指定时间范围的订单信息,一个sheet工作表放具体的商品信息,另一个sheet工作表放订单收货地址信息。这种场景就需要不同的表头,根据前面的讲解,我们知道,只需要在定义WriteSheet对象时指定不同的表头对象即可。

代码如下:

private static void writeDiffSheetWithDiffHead() {
    String fileName = defaultFileName("writeDiffSheetWithDiffHead");
    ExcelWriter excelWriter = null;
    try {
        excelWriter = EasyExcelFactory.write(fileName)
                .build();

        final WriteSheet writeSheet0 = EasyExcelFactory.writerSheet(0, "模板1")
                .head(Item.class)
                .build();
        excelWriter.write(sampleItems(), writeSheet0);

        final WriteSheet writeSheet1 = EasyExcelFactory.writerSheet(1, "模板2")
                .head(ComplexHeadItem.class)
                .build();
        excelWriter.write(sampleItems(), writeSheet1);
    } finally {
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}

结果为:

向同一个excel不同sheet中多次写入(表头不同)

向同一个excel不同sheet中多次写入(表头不同)

实现多表

EasyExcel对于Excel文件定义分成了三层,

  1. 工作簿,也就是Excel文件
  2. 工作表,对应是Excel文件中的Sheet
  3. 表格,对应是Sheet中的有表头、表体的组合

这里所说的功能就是在一个Sheet中创建多个表格。

同一表单中创建表格

这个是开胃菜,演示一下怎么单独指定表格。

private static void writeTable() {
    String fileName = defaultFileName("writeTable");
    final ExcelWriter excelWriter = EasyExcelFactory.write(fileName)
            .head(Item.class)
            .build();
    try {
        // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
        WriteSheet writeSheet = EasyExcelFactory.writerSheet()
                .needHead(Boolean.FALSE)
                .build();

        // 这里必须指定需要头,table 会继承sheet的配置,sheet配置了不需要,table 默认也是不需要
        WriteTable writeTable0 = EasyExcelFactory.writerTable(0)
                .needHead(Boolean.TRUE)
                .build();

        excelWriter.write(sampleItems(), writeSheet, writeTable0);
    } finally {
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}

可以看到,除了前面提过的ExcelWriterWriteSheet,这里还用到了WriteTable,这个就是表格的写对象。有了这个对象,我们只要多创建几个,就能够实现在一个Sheet工作表中,创建多个表格的功能。

结果为:

同一表单中创建表格

与前面的例子完全没有差别,条条大路通罗马。

同一表单中创建不同表格(相同表头)

有了上面的铺垫,我们直接上代码:

private static void writeTables() {
    String fileName = defaultFileName("writeTables");
    final ExcelWriter excelWriter = EasyExcelFactory.write(fileName)
            .build();
    try {
        // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
        WriteSheet writeSheet = EasyExcelFactory.writerSheet()
                .head(Item.class)
                .needHead(Boolean.FALSE)
                .build();

        // 这里必须指定需要头,table 会继承sheet的配置,sheet配置了不需要,table 默认也是不需要
        WriteTable writeTable0 = EasyExcelFactory.writerTable(0)
                .needHead(Boolean.TRUE)
                .build();

        WriteTable writeTable1 = EasyExcelFactory.writerTable(1)
                .needHead(Boolean.TRUE)
                .build();
        // 第一次写入会创建头
        excelWriter.write(sampleItems(), writeSheet, writeTable0);
        // 第二次写如也会创建头,然后在第一次的后面写入数据
        excelWriter.write(sampleItems(), writeSheet, writeTable1);
    } finally {
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}

因为使用的是相同的表头,我们可以直接在WriteSheet中定义表头对象,通过继承的方式,实现两个表格的表头是相同的。这里需要注意一下,WriteSheet构建时,设置needHead(Boolean.FALSE),如果不设置或者设置为true,那第一个表格就会有两个表头。

结果为:

同一表单中创建不同表格(相同表头)

同一表单中创建不同表格(不同表头)

举一反三,对于不同表头,我们只需要为WriteTable对象设置不同表头即可:

private static void writeTablesWithDiffHead() {
    String fileName = defaultFileName("writeTablesWithDiffHead");
    final ExcelWriter excelWriter = EasyExcelFactory.write(fileName)
            .build();
    try {
        WriteSheet writeSheet = EasyExcelFactory.writerSheet()
                .build();

        WriteTable writeTable0 = EasyExcelFactory.writerTable(0)
                .head(Item.class)
                .build();
        excelWriter.write(sampleItems(), writeSheet, writeTable0);

        WriteTable writeTable1 = EasyExcelFactory.writerTable(1)
                .head(ComplexHeadItem.class)
                .build();
        excelWriter.write(sampleItems(), writeSheet, writeTable1);
    } finally {
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}

结果为:

同一表单中创建不同表格(不同表头)

写入模板文件(非填充)

有时候,我们需要按照某种模板导出数据,这类模板文件属于固定样式,没有动态数据。比如前面几行是标题、权限声明、责任声明之类的,紧跟着就是列表数据。如果全靠手工拼写数据比较繁琐,EasyExcel提供了写入模板文件的方式。先看代码:

private static void writeByTemplate() {
    String fileName = defaultFileName("writeByTemplate");
    String templateFile = getPath() + File.separator + "template_write_after_fill.xlsx";
    EasyExcelFactory.write(fileName)
            .withTemplate(templateFile)
            .head(Item.class)
            .sheet()
            .doWrite(sampleItems());
}

需要使用com.alibaba.excel.write.builder.ExcelWriterBuilder#withTemplate(java.lang.String)指定模板文件路径。withTemplate方法有几个重载实现:

  1. 指定模板文件路径ExcelWriterBuilder#withTemplate(java.lang.String)
  2. 指定模板文件对象ExcelWriterBuilder#withTemplate(java.io.File)
  3. 指定模板文件输入流ExcelWriterBuilder#withTemplate(java.io.InputStream)

指定模板文件和模板文件对象都是操作文件的,需要有文件信息。

指定模板文件输入流是只要文件流,这个可操作性空间就比较大了。比如,模板文件是可变的,我们可以基于一个带变量的模板文件,使用填充写入的方式初始化模板文件,然后再用模板写入的方式,写入列表。(这个会在技巧篇中详细说明)

代码中的模板文件内容:

写入模板文件(非填充)

导出文件的内容:

写入模板文件(非填充)

可以看出,这种方式写入的列表是以追加的方式写入,原有的模板内容不会修改,会从第一行空白行开始写列表信息。

文末总结

本文从实战角度说了一下 EasyExcel 如果实现写表格,接下来会讲解一下如何更好看的写。


你好,我是看山。游于码界,戏享人生。如果文章对您有帮助,请点赞、收藏、关注。我还整理了一些精品学习资料,关注公众号「看山的小屋」,回复“资料”即可获得。

个人主页:https://www.howardliu.cn
个人博文:阿里开源的这个库,让 Excel 导出不再复杂(简简单单的写)
CSDN 主页:https://kanshan.blog.csdn.net/
CSDN 博文:阿里开源的这个库,让 Excel 导出不再复杂(简简单单的写)

公众号:看山的小屋