Excel读写操作抽象简述

1、背景

很多项目里面都需要对Excel进行操作，这个时候我们一般使用POI工具，其中使用最多的三个场景如下：

• 生成导入模板：
  • 需要为生成的Excel增加各种校验，如下拉、长度限制、单元格类型等；
  • 只需要增加标题行，无数据行；
• 导入数据：
  • 需要从单元格中读取各种类型的数据，如数字类型、文本类型、日期类型等；
  • 用户上传的文件格式可能比较混乱，如预期是文本格式的单元格实际格式是数字，在读取时不做特殊处理会报错；
• 下载数据；
  • 根据数据及标题生成表格

比较麻烦的就是需要增加的校验以及数据读取兼容，校验和兼容性不好的话用户体验非常差。

而且在实际开发项目过程中，我们经常是来了一个类似的需求就写一遍读写的代码，工作量大而且易出问题，这种方式当然不可取，最好的仍然是将相关的功能抽取成公共的工具以供调用。于是这个Excel增强工具就诞生了。

它主要包含以下特性：

• 生成模板时自动增加各种校验：包括字符串、数字、日期、下拉等；同时对于必填项列，其标题背景会显示成红色，以和非必填项进行区分
• 下拉列表支持超多选项：一般情况下，如果我们给某列做下拉限制时，下拉项太多会导致生成的Excel文件不可用；在此工具中，如果发现设置的下拉列表数据量多于一定数目，那么会自动创建一个隐藏的Sheet，原本主Sheet中下拉列的数据来源设置成这个隐藏的Sheet。
• 自动校验失败后生成附加错误列的Sheet：当执行非空检测、长度检测或者其它开发人员自定义的检测失败时，在原始表格后附加一列做错误信息列，说明当前行出现了什么样的错误，需要用户如何处理。
• 读取数据时兼容性高：考虑到即使我们模板里面限制了类型，如只能填写字符串，但实际用户可能会通过Excel进行各种处理，或者说从其它的Excel或者单元格中复制数据过来，导致模板中单元格的校验相关信息被破坏；后端在读取这些数据时，如果按预期的数据去进行读取，可能会抛出异常。而使用此处抽象的工具类则没有这个担忧，对于多数场景，如文本单元格中实际填写的是数字、预期的日期单元格中填写的是字符串等，都做了兼容性处理，尽量保证用户体验。
• 使用简单：这点见下文的示例即可看到；
• 代码复用，减少出问题概率：相对于每次都重复写代码进行Excel读写的情况来说，提取成工具后最大的好处是代码复用，提取后的代码经过很多实际应用场景的检测，存在的问题能够尽早被发现，当有新的功能需要使用该工具的时候能够减少问题出现的概率。

2、使用示例

2.1 Excel列信息定义

使用该工具进行Excel的读写时，需要先定义好Excel的列信息，列信息的定义支持两种方式

2.1.1 显示定义

示例代码如下所示：

// Excel列定义
private static final List<ExcelColumn<UserReturnedDTO>> columns = Arrays.asList(
        new ExcelColumn<UserReturnedDTO>("街道", "street", 64L).nullable(false),
        new ExcelColumn<UserReturnedDTO>("社区", "community", 64L).nullable(false),
        new ExcelColumn<UserReturnedDTO>("详细住址（楼栋、楼号或酒店、宿舍名称、房号）", "address", 64L).nullable(false),
        new ExcelColumn<UserReturnedDTO>("在长居住地属性：集体宿舍\n" +
                "在外租房\n" +
                "其他、居家", "apodePlace", 20L).setNullable(false).setSelectedValues(Arrays.asList("集体宿舍", "在外租房", "其他", "居家")),
        new ExcelColumn<UserReturnedDTO>("姓名", "name", 32L).setNullable(false),
        new ExcelColumn<UserReturnedDTO>("性别", "sex", 4L).setNullable(false).setSelectedValues(Arrays.asList("男", "女")),
        new ExcelColumn<UserReturnedDTO>("身份证", "idCard", 32L).nullable(false),
        new ExcelColumn<UserReturnedDTO>("联系方式\n（手机号）", "phone", 20L).setNullable(false),
        new ExcelColumn<UserReturnedDTO>("用工类型\n" +
                "企业返岗\n" +
                "门店务工\n" +
                "求职", "laborType", 10L).setNullable(false).setSelectedValues(Arrays.asList("企业返岗", "门店务工", "求职")),
        new ExcelColumn<UserReturnedDTO>("有发热、咳嗽、胸闷、其他、无", "abnormalCondition", 20L)
                .nullable(false).setSelectedValues(Arrays.asList("发热", "咳嗽", "胸闷", "其他", "无"))
);

其中ExcelColumn即为Excel的列信息，此处定义包含有10列，其中使用了ExcelColumn的几个功能：

• 通过构造方法指定Excel列标题、Excel列标题对应的实体类的属性名称以及字符串类型的长度限制；
• 通过nullable方法指定列是否可为空；
• 通过setSelectedValues指定列的下拉数据源；

但这还是麻烦，需要写这么多代码，而且像长度校验、非空校验，是否可以和DTO对象中使用的Hibernate校验框架一起联合起来使用呢？因为原本DTO对象中就需要定义这些校验规则，在Excel这边如果又要定义，相当于两边要做重复的事情，写起代码来总是不美。于是第二种方式就诞生了。

2.1.2 自动加载

第二种方式就是基于DTO定义信息自动加载Excel列信息了，这时需要使用到ExcelField注解，使用后的DTO对象定义如下所示：

/**
 * 测试对象
 *
 * @author  LiuQi 2020/2/13-11:45
 * @version V1.0
 **/
public class TestDTO {
    @NotBlank
    @Length(max = 10)
    @ExcelField(value = "名称")
    private String name;

    @Phone
    @ExcelField(value = "电话")
    private String phone;

    @NotNull
    @Max(10)
    @ExcelField(value = "大小")
    private Integer size;
    ...
}

主要是使用了ExcelField来标识这一属性对应于Excel列中的标题名称。当然还可以通过ExcelField做些其它事情，如定义下拉的数据源、定义Excel列顺序等；

然后根据DTO的定义自动生成ExcelColumn列表：

    private List<ExcelColumn<TestDTO>> getColumns() {
        return ExcelUtils.initColumnsFromClass(TestDTO.class);
    }

代码看起来比2.1.1中的方式要简单多了，而且关键是校验规则不需要在两个地方重复定义，节省了不少的工作量与出错的机率。

通过以上两种方式生成了Excel列信息，这只是第一步，接下来就是基于这些列信息的模板生成及上传下载的代码示例；

2.2 模板生成

使用工具复用2.1中生成的列信息生成模板的代码如下：

    @Override
    public Workbook getDownload() {
        return ExcelUtils.createGenerator(getColumns())
                .getWorkbook();
    }

是的，没看错，就这么简单，通过这一步，就生成了一个Excel的导入模板，自动将列信息填写了进去，并且还增加了很多校验。

有些小伙伴可能会说模板直接使用Excel文件不就可以了？为什么要自动生成？使用Excel文件也是一种方式，但这种方式有一个限制，就是如果想给列做动态的下拉条件时，就还是需要代码特殊处理了。

而且这种方式还会有另外一个风险，就是定义读取的代码与模板可能会存在修改不一致的问题。如模板修改了类型或者其它，会导致代码报错。

而使用这个动态生成的方式就不会有问题，因为模板生成与数据读取这两个步骤，使用的是同一套预先定义好的ExcelColumn列表！

模板生成就这些了，当然还有很多的其它生成参数可以配置，具体参考ExcelUtils中的类，在此不赘述，接下来看数据读取示例

2.3 Excel数据读取

根据2.1中生成列信息读取数据的代码如下：

    public void upload(MultipartFile file) {
        ExcelUtils.createReader(file, getColumns(), TestDTO.class)
                .setErrorProcessor(sheet -> {
                    // 校验失败时（如非空校验、长度校验等或者其它自定义的校验）生成的错误表格（在原始表格后增加一列错误信息）
                    // 此处可以将错误的workbook直接生成一个文件下载路径供前端应用调用
                })
                .read(dataList -> {
                    // 成功时读取到的数据清单s
                });
    }

几行代码就搞定，而且考虑到批量导入时如果校验失败用户很难定位到哪行哪列出现了问题，当出现错误时将生成附加错误信息列的新的Sheet，这时就可以将这个Sheet通过下载的方式提供给前端用户查看，能够非常方便的定位到问题在哪。

2.4 数据下载

假设我们现在有一个列表的数据需要导出到Excel中，具体代码如下：

    public Workbook download(List<TestDTO> list) {
        return ExcelUtils.createGenerator(getColumns(), list)
                .getWorkbook();  
    }

两行代码搞定。

当然以上所说的只是这个工具的最初级的使用，具体使用需要参考其API，不再细说。后续有空再详细描述其API及使用场景，以及此工具的一些设计思路。