CN111814442A

CN111814442A - 一种基于SpringBoot的excel数据处理方法

Info

Publication number: CN111814442A
Application number: CN202010604541.0A
Authority: CN
Inventors: 付晓锋
Original assignee: Sichuan Changhong Electric Co Ltd
Current assignee: Sichuan Changhong Electric Co Ltd
Priority date: 2020-06-29
Filing date: 2020-06-29
Publication date: 2020-10-23

Abstract

本发明专利公开了一种基于SpringBoot的excel数据处理方法，使用基于Java的poi包，可实现excel读写操作便捷化，同时使用JPA接口，实现对象与关系表的映射，并将运行期的实体对象持久化到数据库中。本发明解决了在庞大的数据需求下，传统工具简易一键导入excel处理数据存入数据库的方式无法解决多个excel工作簿多个sheet多种格式excel工作表同时进行处理的问题，可以实现多个excel工作簿多个sheet多种格式excel工作表同时进行处理，很大程度提高了数据处理的效率，也提高了数据处理的维度与广度。

Description

一种基于SpringBoot的excel数据处理方法

技术领域

本发明涉及数据处理方法技术领域，尤其涉及一种基于SpringBoot的excel数据处理方法。

背景技术

目前，excel数据处理已经成为从事IT互联网研发业务单元不可避免的一部分了，在人工智能发展浪潮下，随着计算机和信息技术的迅猛发展和普及应用，行业应用系统的规模迅速扩大，行业应用所产生的数据呈爆炸性增长。动辄达到数百TB甚至数十至数百PB规模的行业，企业大数据已远远超出了现有传统的计算技术和信息系统的处理能力，因此，寻求有效的大数据处理技术、方法和手段已经成为现实世界的迫切需求。当下互联网研发业务单元对于庞大数据量的处理，可以利用spark、kettle等工具，但是当有特定需求的时候，就需要单独去开发程序将数据导入后台数据库，这将费时费力，并且当下现有工具的简易excel导入功能，对源数据表格的格式要求非常严格，一个excel工作簿只能有一个sheet，并且一个sheet只能有一张单一格式的表，这样的话无法满足项目研发过程中庞大丰富多样的数据需求，实际遇到的往往是多个excel工作簿，并且每一个工作簿都有多个sheet，每个sheet都有多种格式的多个表。

发明内容

本发明提供了一种基于SpringBoot的excel数据处理方法，以解决现有技术中如何对多个excel工作簿、多个sheet、多种格式excel工作表同时进行处理的问题。

本发明采用的技术方案是：一种基于SpringBoot的excel数据处理方法，包括以下步骤：

步骤一：首先明确数据需求，将需求的实体字段与数据源excel表格进行对比，明确所需求的实体字段是否都能找到数据源，对于不能找到数据源的实体字段，直接设置null，在进行数据处理的过程中数据源的数据内容和excel表格格式均不能更改；

步骤二：基于SpringBoot新建maven项目，导入maven相应依赖，除了启动项目必须要的tomcat依赖，还有lombok依赖、poi依赖、mysql依赖以及jpa依赖；

步骤三：建立application.yml配置文件，配置数据库mysql连接的配置文件，配置JPA的配置信息，配置服务器端口；

步骤四：建立mysql数据库表，然后利用IntelliJ IDEA的数据持久化工具反向建立Entity实体类；

步骤五：创建repository资源库，通过用来访问领域对象的接口，在领域与数据映射层之间进行协调；

步骤六：建立excel数据处理class类，根据文件后缀名类型获取对应的工作簿对象，然后读取excel文件内容，根据解析规则解析excel数据，将单元格内容转换为字符串，然后提取每一行中需要的数据，构造成为一个结果数据对象，当该行中有单元格的数据为空或不合法时，忽略该行的数据，解析后的行数据对象或行数据错误时返回null；

步骤七：建立controller接口层，给定excel数据源文件的相对路径，将各种格式的解析规则方法分别写出对应接口。

优选地，步骤四中，使用工具反向建立Entity实体类之后，将id字段设置为数据库自动递增，实现方式为在id字段上加上下面的注解语句：@GeneratedValue(strategy＝GenerationType.IDENTITY)。

优选地，步骤六包括如下步骤：

步骤6.1：建立excel数据处理class类；根据文件后缀名类型获取对应的工作簿对象，excel工作簿类型分为xls或者xlsx，根据excel工作簿文件后缀名的类型确定工作簿属于xls还是xlsx，然后进行一个异常判断处理的过程，当文件类型为xls的时候，调用poi原生方法HSSFWorkbook读取文件的输入流，当文件类型为xlsx的时候，调用poi原生方法XSSFWorkbook读取文件的输入流，当文件不是excel文件类型或者目标路径下没有文件的时候，直接抛出异常，退出程序；

步骤6.2：读取excel文件名及文件内容；首先将文件名fileName作为参数进行读取，然后获取到excel后缀名，得到excel的文件类型，然后获取excel工作簿，进而读取excel中的数据；同时通过try catch异常控制，当指定的excel文件不存在时，或者当解析失败时，则返回文件名以及错误信息；当关闭数据流出错时，也返回相应的错误信息；

步骤6.3：对读取到的excel工作簿进行解析；针对同样的数据需求字段，分布在不同sheet中同样格式的excel表格中，则通过对sheet的序号即sheetNum进行循环，从而采用同样的解析规则；当同样的数据需求字段，分布在同一sheet中不同格式的excel表格中或者分布在不同sheet中不同格式的excel表格中时，则单独拆分开来写解析规则；

步骤6.4：将单元格内容转换为字符串，格式统一化处理，再根据所需要的数据需求字段中具体的字段类型进行分别转换；对于数字类型，位数精度做出自定义规则，字符串类型、布尔类型、空值、公式以及遇到ERROR单元格的处理方式单独进行自定义；

步骤6.5：进行解析规则的定义；根据所需要的数据需求字段，对单元格行列坐标、读取规则进行定义，不同的格式对应不同的解析规则，不同的解析规则使用不同的Java方法去进行定义；提取每一行中需要的数据，构造成为一个结果数据对象；当该行中有单元格的数据为空或不合法时，忽略该行的数据；解析后的行数据对象，行数据错误时返回null。

优选地，步骤七中，将各种格式的解析规则方法对应的接口进行归纳汇总成一个总接口。

本发明的有益效果是：本发明解决了在庞大的数据需求下，传统工具简易一键导入excel处理数据存入数据库的方式无法解决多个excel工作簿多个sheet多种格式excel工作表同时进行处理的问题，可以实现多个excel工作簿多个sheet多种格式excel工作表同时进行处理，很大程度提高了数据处理的效率，也提高了数据处理的维度与广度。

附图说明

图1为本发明公开的一种基于SpringBoot的excel数据处理方法流程图。

具体实施方式

为了使本发明的目的、技术方案和优点更加清楚，下面将结合附图对本发明作进一步详细描述，但本发明的实施方式不限于此。

实施例1：

参见图1，一种基于SpringBoot的excel数据处理方法，其实现的具体工作流程如下：

步骤一：首先明确数据需求，将需求的实体字段与数据源excel表格进行对比，明确所需求的实体字段是否都能找到数据源，对于不能找到数据源的实体字段，直接设置null，在进行数据处理的过程中数据源的数据内容和excel表格格式均不能更改。

步骤二：基于SpringBoot新建maven项目，导入maven相应依赖，除了启动项目必须要的tomcat依赖，还有lombok依赖、poi依赖、mysql依赖以及jpa依赖。

启动项目所必须的tomcat依赖如下：

Lombok依赖如下：

Poi依赖如下：

mysql依赖如下：

Jpa依赖如下：

步骤三：建立application.yml配置文件，配置数据库mysql连接的配置文件，配置JPA的配置信息，配置服务器端口等。

配置文件如下：

其中，driver-class-name表示使用的是mysql数据库连接驱动，username和password是所使用的mysql数据库的用户名和密码，url标准规范格式地址，localhost:3306/c表示当下使用的是本地mysql数据库，端口号为3306，数据库名为c；jpa后面的则是针对jpa的使用配置，update表示将数据可持久化到数据库执行更新操作即可。

步骤四：建立mysql数据库表，然后利用IntelliJ IDEA的数据持久化工具反向建立Entity实体类。在使用工具反向建立Entity实体类之后，将id字段设置为数据库自动递增，实现方式为在id字段上加上下面的注解语句：@GeneratedValue(strategy＝GenerationType.IDENTITY)

步骤五：创建repository资源库，通过用来访问领域对象的接口，在领域与数据映射层之间进行协调。

其实现代码为：

public interface对应的Repository接口类名extends JpaRepository<对应的Entity实体类名,Integer>{}

步骤六：建立excel数据处理class类，根据文件后缀名类型获取对应的工作簿对象，然后读取excel文件内容，根据解析规则解析excel数据，将单元格内容转换为字符串，然后提取每一行中需要的数据，构造成为一个结果数据对象，当该行中有单元格的数据为空或不合法时，忽略该行的数据，解析后的行数据对象或行数据错误时返回null。

具体的，步骤六包括如下几个步骤：

步骤6.1：建立excel数据处理class类；根据文件后缀名类型获取对应的工作簿对象，excel工作簿类型分为xls或者xlsx，根据excel工作簿文件后缀名的类型确定该工作簿属于xls还是xlsx，然后通过一个异常判断处理的过程，当文件类型为xls的时候，调用poi原生方法HSSFWorkbook读取文件的输入流，当文件类型为xlsx的时候，调用poi原生方法XSSFWorkbook读取文件的输入流，当文件不是excel文件类型或者目标路径下没有文件的时候，直接抛出异常，退出程序。实现代码如下：

步骤6.2：读取excel文件名及文件内容；首先将文件名fileName作为参数进行读取，然后获取到excel后缀名，得到excel的文件类型，然后获取excel工作簿，进而读取excel中的数据；同时通过try catch异常控制，当指定的excel文件不存在时(即returnnull)，或者当解析失败时，则返回文件名以及错误信息；当关闭数据流出错时，也返回相应的错误信息。

步骤6.3：对读取到的excel工作簿进行解析；针对同样的数据需求字段，分布在不同sheet中同样格式的excel表格中，则可以通过对sheet的序号即sheetNum进行循环，从而采用同样的解析规则；当同样的数据需求字段，分布在同一sheet中不同格式的excel表格中或者分布在不同sheet中不同格式的excel表格中时，则单独拆分开来写解析规则。

步骤6.4：将单元格内容转换为字符串，格式统一化处理；避免在数据处理过程中出现因数据格式出现的意外问题，将单元格内容全部转换成字符串之后，再根据所需要的数据需求字段中具体的字段类型去分别转换，对于数字类型，位数精度可以做出自定义规则，字符串类型、布尔类型、空值、公式以及遇到ERROR单元格的处理方式单独进行自定义。实现代码如下：

步骤七：建立controller接口层，给定excel数据源文件的相对路径，将各种格式的解析规则方法分别写出对应接口；并将不同的接口进行归纳汇总成一个总接口，这样的话，在启动服务后，多个数据需求服务请求只需要请求一次即可将所需要数据全部处理完成。

本实施例需求分为线上线下以及分城市数据去进行处理，因而接口也可分为线上、线下以及分城市三个大类，代码结构如下：

@Transient

private void insertOnlineExcel(String onLineFileName){}

private void insertOfflineExcel(String offLineFileName){}

private void insertOfflineCityExcel(String cityFileName){}

最后，给定文件的相对路径，就可以启动并运行程序了，当程序启动后，在浏览器中访问127.0.0.1:8080/接口/相关传入参数即可开始处理excel数据并存入后台数据库。

以上实施例仅用以说明本发明的技术方案，而非对其限制；尽管参照前述实施例对发明进行了详细的说明，本领域的普通技术人员应当理解：其依然可以对前述各实施例所记载的技术方案进行修改，或者对其中部分技术特征进行等同替换；而这些修改或者替换，并不使相应技术方案的本质脱离本发明各实施例技术方案的精神和范围。

Claims

1.一种基于SpringBoot的excel数据处理方法，其特征在于包括以下步骤：

步骤六：建立excel数据处理class类，根据文件后缀名类型获取对应的工作簿对象，然后读取excel文件内容，根据解析规则解析excel数据，将单元格内容转换为字符串，然后提取每一行中需要的数据，构造成为一个结果数据对象，当行中有单元格的数据为空或不合法时，先忽略该行的数据；解析后的行数据对象或行数据错误时返回null；

2.根据权利要求1所述的基于SpringBoot的excel数据处理方法，其特征在于：步骤四中，使用工具反向建立Entity实体类之后，将id字段设置为数据库自动递增，实现方式为在id字段上加上下面的注解语句：@GeneratedValue(strategy＝GenerationType.IDENTITY)。

3.根据权利要求1所述的基于SpringBoot的excel数据处理方法，其特征在于：步骤六包括如下步骤：

4.根据权利要求1所述的基于SpringBoot的excel数据处理方法，其特征在于：步骤七中，将各种格式的解析规则方法对应的接口进行归纳汇总成一个总接口。