CN115374128B

CN115374128B - Excel导入数据库的方法、装置、计算机设备及介质

Info

Publication number: CN115374128B
Application number: CN202211298977.7A
Authority: CN
Inventors: 赵世刚; 李可; 李季; 赵远杰; 胡维; 梁露露; 韩冰; 陈幼雷
Original assignee: Beijing Yuanbao Technology Co ltd
Current assignee: Beijing Yuanbao Technology Co ltd
Priority date: 2022-10-24
Filing date: 2022-10-24
Publication date: 2023-01-17
Anticipated expiration: 2042-10-24
Also published as: CN115374128A

Abstract

本发明实施例提供了一种Excel导入数据库的方法、装置、计算机设备及介质，涉及数据处理技术领域，其中，该方法包括：根据待导入的Excel表中的所有sheet列表，创建全局任务表，所述全局任务表包括每一个sheet列表对应的脚本生成任务；开启多个任务线程并行执行所述全局任务表中的所述脚本生成任务，生成每个sheet列表对应的创造表结构的脚本；通过程序自动执行每个sheet列表对应的创造表结构的脚本，将所述待导入的Excel表导入数据库。该方案可以极大提高建表效率，避免人工手动操作带来的失误等影响，有利于保证数据导入的完整性和准确性。

Description

Excel导入数据库的方法、装置、计算机设备及介质

技术领域

本发明涉及数据处理技术领域，特别涉及一种Excel导入数据库的方法、装置、计算机设备及介质。

背景技术

数据库表的创建，是IT系统建设的必要条件，也是第三方API数据落地的必要条件。通常数据库表结构的创建是通过数据库设计软件（比如，powerdesigner、pdmaner）、数据库管理软件（比如，navicat）等手动创建各表及字段，并将数据导入创建的表中。针对大量Excel数据保存至数据库的情况，传统做法是手动根据Excel表头创建数据库表结构，然后将Excel数据导入到数据库。如果Excel的sheet有数百甚至成千上万，且均为字段数量较多的表的情况下，带来的工作量是极大且枯燥的。

目前，Excel表结构创建及数据导入已有方法可以实现，现有技术公开了一种建表脚本的生成方法，其方法需要手动在Excel添加需要创建的表名称、字段及类型，然后通过程序读取Excel中定义的建表元素并构建表脚本，以及数据插入sql脚本。然后通过navicat等工具或者sql命令执行上述脚本。该方法需要手动设置建表名称、字段及类型，通过程序生成建表脚本，同时需要手动执行上述脚本。因此，该方法仍需要大量的人工操作，工作效率低。

发明内容

有鉴于此，本发明实施例提供了一种Excel导入数据库的方法、装置、计算机设备及介质，以解决现有技术中Excel表结构创建及数据导入方法存在的需要大量的人工操作、工作效率低。该方法包括：

根据待导入的Excel表中的所有sheet列表，创建全局任务表，所述全局任务表包括每一个sheet列表对应的脚本生成任务；

开启多个任务线程并行执行所述全局任务表中的所述脚本生成任务，生成每个sheet列表对应的创造表结构的脚本；

通过程序自动执行每个sheet列表对应的创造表结构的脚本，将所述待导入的Excel表导入数据库。

本发明实施例还提供了一种Excel导入数据库的装置，以解决现有技术中Excel表结构创建及数据导入方法存在的需要大量的人工操作、工作效率低的问题。该装置包括：

全局任务表生成模块，用于根据待导入的Excel表中的所有sheet列表，创建全局任务表，所述全局任务表包括每一个sheet列表对应的脚本生成任务；

多线程控制模块，用于开启多个任务线程并行执行所述全局任务表中的所述脚本生成任务，生成每个sheet列表对应的创造表结构的脚本；

数据导入模块，用于通过程序自动执行每个sheet列表对应的创造表结构的脚本，将所述待导入的Excel表导入数据库。

本发明实施例还提供了一种计算机设备，包括存储器、处理器及存储在存储器上并可在处理器上运行的计算机程序，所述处理器执行所述计算机程序时实现上述任意的Excel导入数据库的方法，以解决现有技术中Excel表结构创建及数据导入方法存在的需要大量人工操作、工作效率低的问题。

本发明实施例还提供了一种计算机可读存储介质，所述计算机可读存储介质存储有执行上述任意的验证系统的构建方法的计算机程序，以解决现有技术中Excel表结构创建及数据导入方法存在的需要大量人工操作、工作效率低的问题。

与现有技术相比，本说明书实施例采用的上述至少一个技术方案能够达到的有益效果至少包括：通过建立全局任务表，开启多个任务线程并行执行全局任务表中的脚本生成任务，来生成每个sheet列表对应的创造表结构的脚本，进而通过程序自动执行每个sheet列表对应的创造表结构的脚本，即可将待导入的Excel表导入数据库。多个任务线程并行执行全局任务表中的脚本生成任务，可以极大提高建表效率；同时，该Excel导入数据库的方法基于多个任务线程生成每个sheet列表对应的创造表结构的脚本，并通过程序自动执行每个sheet列表对应的创造表结构的脚本，实现Excel表导入数据库的过程无需人工手动操作，有利于进一步提高效率，避免了人工手动操作带来的失误等影响，有利于保证数据导入的完整性和准确性。

附图说明

为了更清楚地说明本申请实施例的技术方案，下面将对实施例中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图仅仅是本申请的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其它的附图。

图1是本发明实施例提供的一种Excel导入数据库的方法的流程图；

图2是本发明实施例提供的一种Excel导入数据库的方法的开启多个任务线程的流程示意图；

图3是本发明实施例提供的一种Excel导入数据库的方法的构建创造表结构脚本的流程示意图；

图4是本发明实施例提供的一种计算机设备的结构框图；

图5是本发明实施例提供的一种Excel导入数据库的装置的结构框图。

具体实施方式

下面结合附图对本申请实施例进行详细描述。

以下通过特定的具体实例说明本申请的实施方式，本领域技术人员可由本说明书所揭露的内容轻易地了解本申请的其他优点与功效。显然，所描述的实施例仅仅是本申请一部分实施例，而不是全部的实施例。本申请还可以通过另外不同的具体实施方式加以实施或应用，本说明书中的各项细节也可以基于不同观点与应用，在没有背离本申请的精神下进行各种修饰或改变。需说明的是，在不冲突的情况下，以下实施例及实施例中的特征可以相互组合。基于本申请中的实施例，本领域普通技术人员在没有作出创造性劳动前提下所获得的所有其他实施例，都属于本申请保护的范围。

在本发明实施例中，提供了一种Excel导入数据库的方法，如图1所示，该方法包括：

步骤S01：根据待导入的Excel表中的所有sheet列表，创建全局任务表，所述全局任务表包括每一个sheet列表对应的脚本生成任务；

具体实施时，将所有sheet列表的相关数据插入全局任务表，在全局任务表中形成每一个sheet列表对应的脚本生成任务，在全局任务表中还可以设置状态栏，并设置脚本生成任务的初始状态。通过遍历Excel中的sheet表格的元数据信息及数量，生成全局任务表，进而基于全局任务表统筹任务线程的个数和运行。

具体实施时，全局任务表的字段可以包括序号、sheet名称、sheet索引、任务开始时间、任务结束时间、执行该脚本生成任务的任务线程标识、任务状态（成功/失败/运行中）、失败原因、备注等字段。

步骤S02：开启多个任务线程并行执行所述全局任务表中的所述脚本生成任务，生成每个sheet列表对应的创造表结构的脚本。

具体实施时，如图2所示，步骤S02开启多个任务线程包括如下步骤：

步骤S021：从配置文件中获取多线程任务数，根据所述多线程任务数开启多个任务线程，每个任务线程各自独立运行；

步骤S022：通过行锁并发控制多个任务线程执行所述全局任务表中的所述脚本生成任务。

具体的，每个任务线程利用行锁，获得脚本生成任务并将任务状态改为运行中，同时设置任务开始时间。同时多个任务线程并发处理数组sheet表格，进一步提高表结构生成及数据导入效率。

具体实施时，为了实现可以有效、精准地控制多个任务线程并行，在本实施例中，上述步骤S022通过行锁并发控制多个任务线程执行所述全局任务表中的所述脚本生成任务的步骤包括：

每个任务线程执行完一个所述脚本生成任务后，在所述全局任务表中记录执行完的所述脚本生成任务的执行状态，所述执行状态包括：未执行、执行成功和执行失败；

每个任务线程对当前脚本生成任务执行完毕时，再次通过行锁从所述全局任务表中获得执行状态为未执行或执行失败的脚本生成任务，直到所述全局任务表中的所述脚本生成任务全部执行完毕。

具体实施时，例如，通过API（jxl.Workbook.getNumberOfSheets）获得Excel文件的sheet数量；根据sheet数量，构建循环逻辑。具体的，通过API（jxl.Workbook.getSheet）获得sheet的元数据对象，进而获得sheet名称，将当前sheet信息插入全局任务表中。根据配置文件中的多线程任务数，生成多个线程，每个线程各自独立运行。每个任务线程执行完毕一个任务后，在全局任务表中将任务状态改为执行成功或执行失败，并设置任务结束时间。再从全局任务表中获得任务，并执行构建创造表结构的脚本。直到全局任务表中的脚本生成任务全部执行完毕。

具体实施时，为了满足不同的控制任务线程的需求，在本实施例中，还提出了通过显式定义同步锁对象来开启多个任务线程；

设置线程控制专门的算法和原则，制定线程的优先级，根据优先级来开启多个任务线程；

在多个任务线程运行过程中，根据控制变量或者捕捉错误强行关闭对应的任务线程，其中，所述控制变量是根据所述待导入的Excel表存在的无法进行数据操作的不同情况确定的；

创建线程池并开启多个任务线程，通过显示定义的同步锁，获得全局任务表中的任务。

步骤S03：通过程序自动执行每个sheet列表对应的创造表结构的脚本，将所述待导入的Excel表导入数据库。

具体实施时，为了实现可以通过任务线程自动化、智能地生成创造表结构的脚本，在本实施例中，通过以下步骤生成每个sheet列表对应的创造表结构的脚本，例如，

每个任务线程从所述全局任务表中获取到所述脚本生成任务后，根据获取的所述脚本生成任务读取所述待导入的Excel表中对应的sheet表格，获取数据库表的名称；

遍历所述sheet表格内的表头字段，生成字段名称；

遍历所述sheet表格内第二行的数据，获取表格的数据类型；根据数据库类型将表格的数据类型转换为数据库字段类型；

根据所述数据库表的名称、所述字段名称和所述数据库字段类型，构造出所述sheet表格的建表语句；

解析所述sheet表格中的所有数据行，构造每行数据的insert数据语句，所述建表语句和每行数据的insert数据语句构成所述sheet表格对应的创造表结构的脚本。

具体的，由图3所示的流程可知，具体实施时，构建sheet表格的创造表结构的脚本的过程包括以下步骤：

步骤S031、各任务线程遍历从全局任务表中拿到的Excel中sheet表格，以sheet表格名称转换为系统前缀加拼音首字母，作为数据库表的名称；以sheet表格名称作为数据库表的备注。

步骤S032、在步骤S031的基础上，遍历每个sheet表格内的表头字段，将表头名称转换为拼音首字母，作为数据库表的字段名称；将Excel表格字段名称作为数据库表的字段描述。

步骤S033、在步骤S032的基础上，遍历当前Excel表格内第二行的数据，根据POI获得每个表格的数据类型，并通过基于步骤S01中的数据库类型（mysql、oracle、postgresql等常用数据库），转换为相应的数据库字段类型。

步骤S034、根据不同数据库类型构造出当前标签页的建表语句。

步骤S035、解析当前excle表格所有数据行，构造每行数据的insert数据语句。

具体实施时，开启多个任务线程并行执行全局任务表中的脚本生成任务的过程包括以下步骤：

S041、根据全局任务表中的状态字段判断全局任务表中的某一条任务是否正常执行完毕；

S042、未执行完毕时，等待所有任务执行完毕；

S043、当前任务线程执行完毕时，再次通过行锁从全局任务表中获得未执行的sheet，循环执行构建创造表结构的脚本的步骤，直到全局任务表的任务全部执行完毕，得到所有的Excel标签页的建表语句。

S044、如果全局任务表中有失败的任务，间隔设定时间，自动进行重试并执行构建创造表结构的脚本的步骤处理所有失败的sheet。具体的，删除全局任务表结构，重新构建全局任务表并将数据插入全局任务表。

具体实施时，自动执行构建好的所有sheet列表对应的创造表结构的脚本脚本。最后，可以通过navicat工具可以查看到对应数据库已经自动创建好的表结构。

具体实施时，为了便于上述任务线程读取相关数据、顺利实现创建创造表结构的脚本，在本实施例中，还可以设置配置文件，所述配置文件包括以下任意项或任意组合的执行参数：指定Excel文件所在目录、Excel路径、多线程任务数、系统前缀、数据配置、数据库类型、数据库地址、数据库用户名以及数据库密码。

具体实施时，为了实现Excel导入数据库的过程可以追溯、查询，在本实施例中，提出了在数据导入完成后，将导入日志信息记录在指定位置下的指定文件中。

具体实施时，上述Excel导入数据库的方法实现了以下功能：首先获得Excel的所有sheet名称，构建全局任务表，开启多个线程，并发处理Excel的各sheet表。解析Excel中每个sheet表格，以系统前缀加sheet名称翻译为拼音首字母的组合作为表名称，以sheet名称为表备注；然后解析表格的表头所有字段，以字段名称转换为拼音首字母作为数据库表字段，通过POI获得cell数据类型，进而确定数据库表字段类型，以字段名称作为数据库表字段备注，由此可以得到当前Excel表格的创建数据库表结构的脚本。然后解析当前Excel表格所有数据行，构造每行数据的insert数据语句，最终能够得到Excel各sheet表格的建表及插入数据脚本，最后自动执行上述脚本，进而成功创建的数据库结构以及数据库数据。

具体实施时，以下详细介绍实施上述Excel导入数据库的方法的过程，该过程包括以下步骤：

1)设置配置文件，配置文件包括以下内容：指定Excel文件所在目录、多线程任务数、系统前缀、数据库类型、数据库地址、用户名以及密码。

2)程序遍历获得Excel中的所有sheet表名称，构建全局任务表，根据配置文件中的多线程任务数，通过行锁开启相应的多个任务线程，并发执行。

3)各任务线程遍历从全局任务表中拿到的Excel中sheet表格，以sheet表格名称，经本发明转换为系统前缀加拼音首字母，作为数据库表的名称，表格名称作为表备注。

4)步骤3的基础上，遍历每个sheet表格内的表头字段，将表头名称转换为拼音首字母，作为数据库表的字段名称，Excel表格字段名称作为字段描述。

5)在步骤4的基础上，遍历当前Excel表格内第二行的数据，根据poi获得每个表格的数据类型，并通过配置文件中的数据库类型，将每个表格的数据类型转换为相应的数据库字段类型（mysql、oracle、postgresql等）。

6)通过步骤3、4、5，构造出当前标签页的建表语句。

7)解析当前excle表格所有数据行，构造每行数据的insert数据语句。

8)当前任务线程执行完毕后，再次通过行锁从全局任务表中获得未执行的sheet表对应的脚本生成任务，循环执行步骤3、4、5、6、7，直到全局任务表的脚本生成任务全部执行完毕，得到所有的Excel标签页的建表语句。

9)步骤8执行完毕，如果全局任务表中有失败的任务，自动重试执行失败的sheet表对应的脚本生成任务。

10)自动执行步骤9得到的建表语句，然后可以通过navicat查看到对应数据库已经自动创建好的表结构。

在本实施例中，提供了一种计算机设备，如图4所示，包括存储器401、处理器402及存储在存储器上并可在处理器上运行的计算机程序，所述处理器执行所述计算机程序时实现上述任意的Excel导入数据库的方法。

具体的，该计算机设备可以是计算机终端、服务器或者类似的运算装置。

在本实施例中，提供了一种计算机可读存储介质，所述计算机可读存储介质存储有执行上述任意的Excel导入数据库的方法的计算机程序。

具体的，计算机可读存储介质包括永久性和非永久性、可移动和非可移动媒体可以由任何方法或技术来实现信息存储。信息可以是计算机可读指令、数据结构、程序的模块或其他数据。计算机可读存储介质的例子包括，但不限于相变内存(PRAM)、静态随机存取存储器(SRAM)、动态随机存取存储器(DRAM)、其他类型的随机存取存储器(RAM)、只读存储器(ROM)、电可擦除可编程只读存储器(EEPROM)、快闪记忆体或其他内存技术、只读光盘只读存储器(CD-ROM)、数字多功能光盘(DVD)或其他光学存储、磁盒式磁带，磁带磁磁盘存储或其他磁性存储设备或任何其他非传输介质，可用于存储可以被计算设备访问的信息。按照本文中的界定，计算机可读存储介质不包括暂存电脑可读媒体(transitory media)，如调制的数据信号和载波。

基于同一发明构思，本发明实施例中还提供了一种Excel导入数据库的装置，如下面的实施例所述。由于Excel导入数据库的装置解决问题的原理与Excel导入数据库的方法相似，因此Excel导入数据库的装置的实施可以参见Excel导入数据库的方法的实施，重复之处不再赘述。以下所使用的，术语“单元”或者“模块”可以实现预定功能的软件和/或硬件的组合。尽管以下实施例所描述的装置较佳地以软件来实现，但是硬件，或者软件和硬件的组合的实现也是可能并被构想的。

图5是本发明实施例的Excel导入数据库的装置的一种结构框图，如图5所示，该装置包括：

全局任务表生成模块501，用于根据待导入的Excel表中的所有sheet列表，创建全局任务表，所述全局任务表包括每一个sheet列表对应的脚本生成任务；

多线程控制模块502，用于根据所述全局任务表内的配置信息，创建相应线程数，并对每个线程进行开始时间、运行状态等的控制；

数据导入模块503，用于通过程序自动执行每个sheet列表对应的创造表结构的脚本，将所述待导入的Excel表导入数据库。

在一个实施例中，全局任务表生成模块，包括：

配置文件读取模块单元，读取配置文件取得需要的配置等参数；

全局任务表插入删除单元，向全局任务表内插入或者删除数据；

全局任务表读取单元，读取全局任务表内的数据；

全局任务表更新单元，更新全局任务表中的状态时间等数据；

Excel信息读取单元，包括：Excel的sheet表读取，调用API函数读取所有sheet表；Sheet内的数据读取，调用API函数读取每个sheet表内的所有数据；数据转换，按照一定的规则，将取得的sheet名称转换为数据库表的名称和数据库表备注，将表头字段转换为数据库表的字段名称，将表格内第二行的数据转换为表的数据类型。

在一个实施例中，多线程控制模块，包括：

线程开启单元，通过显式定义同步锁对象来实现同步。同步锁使用Lock对象充当；

线程控制调度单元，制定线程控制专门的算法、原则，制定线程的优先级，通过显式定义同步锁对象来实现线程的同步，线程的任务结束时，自动关闭当前执行的线程；

线程关闭单元，为了避免出错时线程仍在占用系统，通过使用控制变量或者捕捉错误强行关闭线程。

在一个实施例中，数据导入模块，包括：

脚本生成单元，根据数据库的种类构造每行数据的insert数据语句；

脚本生成单元，执行脚本将数据导入数据库。

在一个实施例中，上述装置还包括：

日志管理模块，用于在数据导入完成后，将导入日志信息记录在指定位置下的指定文件中。

本发明实施例实现了如下技术效果：自动从Excel中导入数据至任意类型的数据库，无需人工手动操作，同时多任务并发处理，进一步提升建表和数据导入效率，同时采用失败重试机制，保证数据导入的完整性和准确性。

以上所述仅为本发明的优选实施例而已，并不用于限制本发明，对于本领域的技术人员来说，本发明实施例可以有各种更改和变化。凡在本发明的精神和原则之内，所作的任何修改、等同替换、改进等，均应包含在本发明的保护范围之内。

Claims

1.一种Excel导入数据库的方法，其特征在于，包括：

通过程序自动执行每个sheet列表对应的创造表结构的脚本，将所述待导入的Excel表导入数据库；

生成每个sheet列表对应的创造表结构的脚本，包括：

遍历所述sheet表格内的表头字段，生成数据库表的字段名称；

根据所述数据库表的名称、所述数据库表的字段名称和所述数据库字段类型，构造出所述sheet表格的建表语句；

2.如权利要求1所述的Excel导入数据库的方法，其特征在于，开启多个任务线程并行执行所述全局任务表中的所述脚本生成任务，包括：

从配置文件中获取多线程任务数，根据所述多线程任务数开启多个任务线程，每个任务线程各自独立运行；

通过行锁并发控制多个任务线程执行所述全局任务表中的所述脚本生成任务。

3.如权利要求1所述的Excel导入数据库的方法，其特征在于，通过行锁并发控制多个任务线程执行所述全局任务表中的所述脚本生成任务，包括：

4.如权利要求1所述的Excel导入数据库的方法，其特征在于，还包括：

通过显式定义同步锁对象来开启多个任务线程；

5.如权利要求1所述的Excel导入数据库的方法，其特征在于，还包括：

设置配置文件，所述配置文件包括以下任意项或任意组合的执行参数：

指定Excel文件所在目录、Excel路径、多线程任务数、系统前缀、数据配置、数据库类型、数据库地址、数据库用户名以及数据库密码。

6.如权利要求1所述的Excel导入数据库的方法，其特征在于，还包括：

数据导入完成后，将导入日志信息记录在指定位置下的指定文件中。

7.一种Excel导入数据库的装置，其特征在于，包括：

数据导入模块，用于通过程序自动执行每个sheet列表对应的创造表结构的脚本，将所述待导入的Excel表导入数据库；

其中，生成每个sheet列表对应的创造表结构的脚本，包括：

每个任务线程从所述全局任务表中获取到所述脚本生成任务后，根据获取的所述脚本生成任务读取所述待导入的Excel表中对应的sheet表格，获取数据库表的名称；遍历所述sheet表格内的表头字段，生成数据库表的字段名称；遍历所述sheet表格内第二行的数据，获取表格的数据类型；根据数据库类型将表格的数据类型转换为数据库字段类型；根据所述数据库表的名称、所述数据库表的字段名称和所述数据库字段类型，构造出所述sheet表格的建表语句；解析所述sheet表格中的所有数据行，构造每行数据的insert数据语句，所述建表语句和每行数据的insert数据语句构成所述sheet表格对应的创造表结构的脚本。

8.一种计算机设备，包括存储器、处理器及存储在存储器上并可在处理器上运行的计算机程序，其特征在于，所述处理器执行所述计算机程序时实现权利要求1至6中任一项所述的Excel导入数据库的方法。

9.一种计算机可读存储介质，其特征在于，所述计算机可读存储介质存储有执行权利要求1至6中任一项所述的Excel导入数据库的方法的计算机程序。