超级好用的 excel 导入导出框架：excel-import-export

原创

诗水人间 2022-10-11 11:55:35 博主文章分类：我的开源项目 ©著作权

文章标签 java 面试开发语言数据字段 文章分类 运维

©著作权归作者所有：来自51CTO博客作者诗水人间的原创作品，请联系作者获取转载授权，否则将追究法律责任

前言：

早在一年前，因工作的原因用到了excel导入。
于是就了解到了POI，然后当时的我看到了项目中一段老代码：一段非常冗余，且非常晦涩难懂的代码。

一个方法中有好几百行代码，因为一些业务需求，代码中有很多很多if-else-if-else是那种你看一眼都头疼的代码。绝大部分判空的逻辑很多。

当时的我就吐槽，这谁写的垃圾代码，鬼一样的东西！

又因为当时工作中与excel的相关工作还比较多，为了提高开发效率，于是就有打算自己写一个工具。

于是也就有了它：excel-import-export

excel-import-export 能帮你做什么？

引入依赖

<repositories>
    <repository>
        <id>alimaven</id>
        <name>aliyun maven</name>
        <!-- 新版本的aliyun镜像仓库地址建议mirrors中也修改，
             如果已经改好了，则可以去掉这个repositories -->
        <url>https://maven.aliyun.com/repository/central</url>
    </repository>
</repositories>

<dependencies>

    <!-- https://mvnrepository.com/artifact/top.yumbo.excel/excel-import-export -->
    <dependency>
        <groupId>top.yumbo.excel</groupId>
        <artifactId>excel-import-export</artifactId>
        <version>1.3.21</version>
    </dependency>

</dependencies>

一、常规的导入

@Data
@ExcelTableHeader(height = 8, sheetName = "项目总表")
public class ImportForInveProj {

    /** 审批监管平台代码 */
    @ExcelTitleBind(title = "审批监管平台代码",nullable = false)
    private String supervisionCode;

    /** 项目名称 */
    @ExcelTitleBind(title = "项目名称")
    private String projectName;

    /** 建设地点 */
    @ExcelTitleBind(title = "建设地点")
    private String constructionAddress;

    /** 所属批次 */
    @ExcelTitleBind(title = "所属批次")
    private String belongBatch;
}

详细的测试用例代码见项目：https://github.com/1015770492/excel-import-export-test-case/

当然@ExcelTableHeader注解中还提供了很多功能，这只是最基本的功能，其主要功能是存放一些共有的属性，比如要操作的sheetName，生成excel的模板文件相对路径，绝对路径，流，http资源文件。
更多功能此处省略，自行探索。

超级好用的 excel 导入导出框架：excel-import-export_开发语言

测试用例代码：可以拉去整个项目，后续的测试用例都维护在这个项目中

https://github.com/1015770492/excel-import-export-test-case/blob/master/src/main/java/top/yumbo/excel/test/importDemo/ImportForInveProj_Demo.java

二、复杂的导入场景

情形1、标题头中存在重复标题

请先观看下面这张截图说明

超级好用的 excel 导入导出框架：excel-import-export_java_02

对应具体的代码

以 w3 到 w6字段上的注解信息为例子。
因为"中央预算内投资资金情况"这个标题在整个8行标题头中不重复，
"中央预算内投资资金情况"是一个合并单元格，合并单元格有一个特点，就是文本信息存储在合并前的第一个单元格。也就是 excel 中的R列。

而"下达年份批次"标题也在R列，所以可以用title = "中央预算内投资资金情况" 代替。

我们知道"下达年份批次" 是一个重复的标题。分别在 R列和V列，同样存在重复标题的还有

"支付进度"：U、Z、AD3列
"支付资金（万元）"：Y、AC
"下达资金（万元）"：X、AB

以此类推，在我们的excel中可能存在很多重复的标题名称。那么该如何导入呢？

我们可以利用不重复的标题来辅助我们解决这个问题，如何做呢？
好比你知道 A超市的位置，并且知道张三的家与A超市相邻 那么我们可以利用已知的位置（A超市） + 相对位置找到张三家（左边、右边）

对于导入而言，你不需要关心上下位置，你只需要关心java中的属性对应单元格那一列即可，以及excel中从哪一行开始是数据行。所以你可以利用注解中提供的2个属性positionTitle = A超市的位置（不重复的标题）, offset = 右边一个格子(1)，左边1列用(-1)表示

对应的javaBean注解信息如下：

从注解信息我们可以知道：

height = 8，表头占据了8行，行号从9开始都是数据行。
sheetName = "项目总表" 该javaBean的信息操作的是excel中名称为 “项目总表” 的sheet
w3 这个字段对应着标题头为title = "中央预算内投资资金情况" 这1列,实际上目的是收集第一个重复标题"下达年份批次"数据
w3 的nullable =true 表示该列中的数据可以为空，如果设为false 则单元格为空会收集为空的单元格信息，在控制台中打印，输出的信息是行号（对应excel左侧的行号，从1开始的）+ 列信息：对应最上方的 A、B、C… AA、AB…
w4 可以用相对位置来表示，positionTitle = "中央预算内投资资金情况" 相对于标题为 “中央预算内投资资金情况” 这列
w4 的offset = 1说明w4，相对于w3(title = "中央预算内投资资金情况") 右边一列。

同意的道理可以利用相对位置来处理其他重复的标题

@Data
@ExcelTableHeader(height = 8, sheetName = "项目总表")
public class ImportForInveProj {
    // 1.下达年份批次
    @ExcelTitleBind(title = "中央预算内投资资金情况",nullable = true)
    private String w3;
    // 1.下达资金
    @ExcelTitleBind(positionTitle = "中央预算内投资资金情况",offset = 1,nullable = true)
    private String w4;
    // 1.支付资金
    @ExcelTitleBind(positionTitle = "中央预算内投资资金情况",offset = 2,nullable = true)
    private String w5;
    // 1.支付进度
    @ExcelTitleBind(positionTitle = "中央预算内投资资金情况",offset = 3,nullable = true)
    private String w6;

    // 2.下达年份批次
    @ExcelTitleBind(title = "省预算内投资资金情况",nullable = true)
    private String w7;
    // 资金类型 ：该标题在标题头中唯一，可以直接用title,也可以使用注释掉的注解获取，取决于你的个人理解（用那种都随意）
    @ExcelTitleBind(title = "资金类型",nullable = true)
    //@ExcelTitleBind(positionTitle = "省预算内投资资金情况",offset = 1,nullable = true)
    private String w8;
    // 2.下达资金（万元）
    @ExcelTitleBind(positionTitle = "省预算内投资资金情况",offset = 2,nullable = true)
    private String w9;
    // 2.支付资金（万元）
    @ExcelTitleBind(positionTitle = "省预算内投资资金情况",offset = 3,nullable = true)
    private String w10;
    // 2.支付进度
    @ExcelTitleBind(positionTitle = "省预算内投资资金情况",offset = 4,nullable = true)
    private String w11;


    // 3.下达年份批次
    @ExcelTitleBind(title = "来地方政府专项债券资金情况",nullable = true)
    private String w12;
    // 3.下达资金（万元）
    @ExcelTitleBind(positionTitle = "来地方政府专项债券资金情况",offset = 1,nullable = true)
    private String w13;
    // 3.支付资金（万元）
    @ExcelTitleBind(positionTitle = "来地方政府专项债券资金情况",offset = 2,nullable = true)
    private String w14;
    // 3.支付进度
    @ExcelTitleBind(positionTitle = "来地方政府专项债券资金情况",offset = 3,nullable = true)
    private String w15;
}

用例代码：
https://github.com/1015770492/excel-import-export-test-case/blob/master/src/main/java/top/yumbo/excel/test/importDemo/ImportForInveProj_Demo.java

`重复标题的另外一种写法：`

例如下面的excel，存在一些重复标题，如果存在多级标题的情形，允许你使用多级标题组成唯一的标题来解决重复标题的问题。

例如：中央预算内投资资金情况 合并单元格下面有一个下达年份批次 可以组成中央预算内投资资金情况_下达年份批次

使用的方式很简单：@ExcelTitleBind(title = "中央预算内投资资金情况_下达年份批次") 即可

超级好用的 excel 导入导出框架：excel-import-export_开发语言_03

titleSplit：如果标题中存在下划线则，可以通过它来修改自定义的分隔符。
globalTitleSplit ：用于设置全局默认的分隔符。如果没有在字段上配置titleSplit则会默认使用全局的默认分隔符。

@Data
@ExcelTableHeader(height = 8, sheetName = "项目总表",globalTitleSplit = "_")
public class ImportForInveProj_For_multiLevelTitle {

    // 1.下达年份批次  默认titleSplit 使用的是下划线，为了不写死，因为可能有些标题本身带下划线，可以换成@或者其他字符串
    @ExcelTitleBind(title = "中央预算内投资资金情况_下达年份批次",titleSplit = "_")
    private String w3;
    // 1.下达资金
    @ExcelTitleBind(title = "中央预算内投资资金情况_下达资金")
    private String w4;
    // 1.支付资金
    @ExcelTitleBind(title = "中央预算内投资资金情况_支付资金")
    private String w5;
    // 1.支付进度
    @ExcelTitleBind(title = "中央预算内投资资金情况_支付进度")
    private String w6;

    // 2.下达年份批次
    @ExcelTitleBind(title = "省预算内投资资金情况_下达年份批次")
    private String w7;
    @ExcelTitleBind(title = "省预算内投资资金情况_资金类型")
    private String w8;
    // 2.下达资金（万元）
    @ExcelTitleBind(title = "省预算内投资资金情况_下达资金（万元）")
    private String w9;
    // 2.支付资金（万元）
    @ExcelTitleBind(title = "省预算内投资资金情况_支付资金（万元）")
    private String w10;
    // 2.支付进度
    @ExcelTitleBind(title = "省预算内投资资金情况_支付进度")
    private String w11;


    // 3.下达年份批次
    @ExcelTitleBind(title = "来地方政府专项债券资金情况_下达年份批次")
    private String w12;
    // 3.下达资金（万元）
    @ExcelTitleBind(title = "来地方政府专项债券资金情况_下达资金（万元）")
    private String w13;
    // 3.支付资金（万元）
    @ExcelTitleBind(title = "来地方政府专项债券资金情况_支付资金（万元）")
    private String w14;
    // 3.支付进度
    @ExcelTitleBind(title = "来地方政府专项债券资金情况_支付进度")
    private String w15;

}

解析excel 的结果如下。支持公式的excel 导入

超级好用的 excel 导入导出框架：excel-import-export_面试_04

点我查看完整的测试案例

情形2、合并单元格的情况

在某些场景中我们经常会遇到一些合并单元格的场景，
大致的意思就是：java中的属性字段可以来自相邻的单元格。

假设地区是由市州-区县组成，在数据库中市州和区县都属于数据字典内容。
也就是这些地区都各自有自己的编号，假设编号我们根据拼音首字母来定义，如果出现重复的拼音首字母则在后面加一个数字。

假设贵阳市的编号是：GYS 南明区：NMQ、云岩区：YYQ、花溪区：HXQ、白云区：BYQ、观山湖区：GSHQ

在实际情况中我们会将地区信息用一个字段存起来，于是地区就对应着我们想要操作的数据库字段reginCode。
例如第5行在数据库中的值是：
GYS-GYS或者GYS,GYS 需求：我们需要将A，B单元格合并，并且将其转换为字典项

根据excel的信息，存到数据库的格式应该是：第6行：GYS-NMQ 以及第7行：GYS-YYQ，第8行：GYS-YYQ 以此类推。

那么这个时候该如何快速得到我们想要的数据呢？

超级好用的 excel 导入导出框架：excel-import-export_java_05

在这个excel中，如果我们想要将 A5与B5单元格进行合并的话，有很多种写法都可以完成这种效果。

写法一：

利用合并单元格属性 width=2，因为标题：“地区” 对应的列是A列与"市州"同一列，那么我们就可以利用这个特点取title="市州" 作为起始列, width=2囊括A和B 2个列。

其中的join="-"是将合并的结果用"-"拼接起来。
@MapEntry配的是字典项，后续会考虑从数据库中取出字典。
这样就更加完美些

@Data
@ExcelTableHeader(height = 4, sheetName = "sheet1")// 表头占4行
public class ImportForYear {

    /**
     * 地区代码，存储最末一级的地区代码就可以
     */
    @ExcelTitleBind(title = "地区", width = 2,join = "-")
    @MapEntry(key = "贵阳市", value = "GYS")
    @MapEntry(key = "南明区", value = "NMQ")
    @MapEntry(key = "云岩区", value = "YYQ")
    @MapEntry(key = "花溪区", value = "HXQ")
    @MapEntry(key = "白云区", value = "BYQ")
    @MapEntry(key = "观山湖区", value = "GSHQ")
    private String regionCode;
}

写法二：

可以利用重复标题完成合并，合并的顺序与注解顺序一致。

@Data
@ExcelTableHeader(height = 4, sheetName = "sheet1")// 表头占4行
public class ImportForYear {

    /**
     * 地区代码，存储最末一级的地区代码就可以
     */
    @ExcelTitleBind(title = "市州",join="-")
    @ExcelTitleBind(title = "区县")
    @MapEntry(key = "贵阳市", value = "GYS")
    @MapEntry(key = "南明区", value = "NMQ")
    @MapEntry(key = "云岩区", value = "YYQ")
    @MapEntry(key = "花溪区", value = "HXQ")
    @MapEntry(key = "白云区", value = "BYQ")
    @MapEntry(key = "观山湖区", value = "GSHQ")
    private String regionCode;
}

用例代码：https://github.com/1015770492/excel-import-export-test-case/blob/master/src/main/java/top/yumbo/excel/test/importDemo/ImportForYear_Demo.java

就得到了我们想要的数据

超级好用的 excel 导入导出框架：excel-import-export_字段_06

情形3、取任意不相邻单元格内容进行合并

情形2中的写法二就是一种写法。
在现实中的需求是这样的，数据库的某个字段的数据会被拆分成多个不相邻列单元格中展示。

好比java有一个 a字段，它可能需要结合excel中的 A列，M列，Z列。
甚至我们还可能只是取A列的部分信息，M的部分信息，Z列可能还需要计算

前3个单元格是字符串类型的，最后一个单元格是数字类型的，我们希望将其转化为

利用横杠分开
某某市罗泊河水库（清水海二期）工程-某某市-2022年第一批-500000000

超级好用的 excel 导入导出框架：excel-import-export_开发语言_07

@Data
@ExcelTableHeader(height = 8, sheetName = "项目总表")
public class ImportForInveProj_ForAnyTitle {

    /** 项目名称 */
    @ExcelTitleBind(title = "项目名称",join = "-")
    @ExcelTitleBind(title = "建设地点")
    @ExcelTitleBind(title = "下达年份批次")
    @ExcelTitleBind(title = "下达资金（万元）",size = "10000")
    private String projectName;
}

用例代码：

https://github.com/1015770492/excel-import-export-test-case/blob/master/src/main/java/top/yumbo/excel/test/importDemo/ImportForInveProj_ForAnyTitle_Demo.java

超级好用的 excel 导入导出框架：excel-import-export_java_08

9-10-11
3行的数据结果如下。其中11行的 "下达资金（万元）"没有数据所以没有值

projectName=某某市罗泊河水库（清水海二期）工程-某某市-2022年第一批-5.0E8
projectName=滇中新区生命科创产业园区再生水厂及配套管网等基础设施项目-空港经济区-2021年第一批-5.0E8
projectName=某某省某某县城市供水工程项目-富民县-2021年第一批/2022年第一批

内置了很多功能，空校验，jsr303校验，逻辑校验（比如A单元格值为X的时候，要求B单元格值为Y）

需要注意的是，如果有合并单元格等操作，或者字典转换操作建议将字段类型设置为String。
一方面用于导出excel的时候做为载体。

发布了版本1.3.21
提供了分段处理数据。避免forkjoin合并导致大量bean被引用导致不可以回收的问题。

新的方法传入一个Consumer<List<T>>接口，批量处理bean
参考
github demo1github demo2

下一个版本准备解决poi本身使用用户模式导致的内存问题。