Method, system and equipment for automatically generating EXCEL data import template based on VBA and medium

Abstract

The invention discloses a method, a system, equipment and a medium for automatically generating an EXCEL data import template based on VBA, belongs to the technical field of databases, and aims to solve the technical problems of how to flexibly, reliably and quickly generate the EXCEL data import template, reduce manual errors, improve data processing efficiency and improve data processing efficiency. According to the technical scheme, the method comprises the steps that configuration information in a specified configuration file and information in field annotations are connected to a specified database, field information is extracted, and an EXCEL data import template is generated; tables, needing to be read, of a VBA program in the process of converting a database table into an EXCEL template file are determined, a template table is formed, various information of fields needing to be converted into EXCEL columns and fields needing dictionary information in any table is determined, and the EXCEL template requirement is met.

Description

Technical Field

[0001] The present invention relates to the field of database technology, in particular to a method, system, device and medium for automatically generating an EXCEL data import template based on VBA. Background Art

[0002] Currently, the EXCEL template for importing data needs to be manually set by the developer, including the setting of the title. In order to ensure the quality of the imported data, data verification needs to be manually set. Especially when the database table structure is complex or there are many tables, it takes a lot of time and effort to process.

[0003] Regarding the problem that information systems need to import data using EXCEL, especially when the database table structure is complex or the number of tables is large, how to flexibly, reliably and quickly generate EXCEL data import templates to reduce human errors and improve data processing efficiency is a technical problem that needs to be solved urgently. Summary of the invention

[0004] The technical task of the present invention is to provide a method, system, device and medium for automatically generating EXCEL data import templates based on VBA to solve the problem of how to flexibly, reliably and quickly generate EXCEL data import templates, reduce manual errors and improve data processing efficiency.

[0005] The technical task of the present invention is achieved in the following way: a method for automatically generating an EXCEL data import template based on VBA. The method specifies the configuration information in the configuration file and the information in the field comments, connects to the specified database, extracts the field information, and generates the EXCEL data import template. It clarifies the various types of information that the VBA program needs to read during the conversion process from the database table to the EXCEL template file, the tables that constitute a template, the fields in any table that need to be converted into EXCEl columns, and the fields that need dictionary information, so as to meet the EXCEL template requirements.

[0006] Preferably, the connection to the specified data is as follows:

[0007] The VBA program connects to the specified database via ADO;

[0008] It requires the driver of the relevant database and the configuration of the database IP address, database user name and password information, and stores the configuration information in the form of a configuration file on the disk;

[0009] Create a FileSystemObject object through the CreateObject function. The FileSystemObject object is used to read files.

[0010] Parse the configuration file to obtain the required configuration information.

[0011] As a preference, the field information is extracted as follows:

[0012] After the database connection is successful, create an ADO command object and execute the specified SQL statement to obtain the data structure information of any table. At the same time, for the table that needs to be parsed, configure the parsing table name sequence in the configuration file;

[0013] When it comes to any table field, if you do not need to convert all the fields of the table into EXCEL columns, add the identification keyword NOT_IMPORT in the column comment information. If the keyword NOT_IMPORT is in the field comment information, the EXCEL column conversion will not be performed.

[0014] The comment for the column that needs to be converted is name, and the comment for the column that is not needed is name|NOT_IMPORT.

[0015] Preferably, the data structure information is ['tableName1', 'tableName2', ...], and if any template is composed of multiple tables, the parsing indicates that the sequence configuration is: ['tableName1, tableName2', 'tableName3', ...];

[0016] Among them, tableName1 and tableName2 indicate that the two tables tableName1 and tableName2 form an EXCEL data import template.

[0017] As a preferred method, the EXCEL data import template is generated as follows:

[0018] According to the obtained field information, set the title and data verification rules for each column in EXCEL;

[0019] Based on the conversion of field information to EXCEL columns, in conjunction with relevant configuration information, a correct EXCEL template is generated and stored in the specified disk location.

[0020] Preferably, the field information includes comments, data type, length, and whether it is allowed to be empty;

[0021] Among them, the comment: is converted into the EXCEL column name and indicates whether the corresponding field needs to be converted into a column;

[0022] Data type: Convert to the data type that is allowed by the data validation setting;

[0023] Length: The data length allowed to be filled in when converted to data verification settings;

[0024] Allow empty values: Whether to ignore empty values ​​when converted to data validation settings.

[0025] Preferably, according to the acquired field information, the title and data verification rules of each column of EXCEL are set as follows:

[0026] For columns with sequence type data validation rules, import the configuration file, and the data structure type is: tableName.columnName.dictCode;

[0027] Among them, tableName represents the table name; columnName represents the column name; dictCode represents the dictionary code used by the corresponding column;

[0028] When processing each column, the VBA program checks whether there is corresponding dictionary usage information in the configuration file:

[0029] If it exists, the dictionary information is obtained from the file or API interface according to the read dictCode, and the dictionary information is used as the sequence data when setting the data verification.

[0030] A system for automatically generating an EXCEL data import template based on VBA, the system is used to implement the method for automatically generating an EXCEL data import template based on VBA as described above; the system comprises:

[0031] The specified data connection module is used to connect to the specified database through ADO using VBA program. It requires the driver of the relevant database and the configuration of the database IP address, database user name and password information. The configuration information is stored in the disk in the form of a configuration file, and a FileSystemObject object is created through the CreateObject function. The FileSystemObject object is used to read the file, parse the configuration file, and obtain the required configuration information.

[0032] The field information extraction module is used to create an ADO command object after the database connection is successful, and execute the specified SQL statement to obtain the data structure information of any table. At the same time, for the table that needs to be parsed, the parsing table name sequence is configured in the configuration file;

[0033] The EXCEL data import template generation module is used to set the title and data verification rules of each Excel column according to the obtained field information, and to generate the correct EXCEL template according to the conversion of field information to EXCEL columns and related configuration information, and store it in the specified disk location.

[0034] An electronic device comprising: a memory and at least one processor;

[0035] Wherein, the memory stores a computer program;

[0036] The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the method for automatically generating an EXCEL data import template based on VBA as described above.

[0037] A computer-readable storage medium stores a computer program, and the computer program can be executed by a processor to implement the method of automatically generating an EXCEL data import template based on VBA as described above.

[0038] The method, system, device and medium for automatically generating an EXCEL data import template based on VBA of the present invention have the following advantages:

[0039] (1) The present invention is mainly used in the development process of information management system, where it is necessary to use EXCEL to import data in batches, and in the scenario where a large amount of data and complex database structure need to be frequently processed, it can help system developers to perform simple configuration and realize the rapid generation of import data templates;

[0040] (ii) The present invention realizes flexible, reliable and rapid generation of EXCEL data import templates through simple configuration and based on the use of VBA language;

[0041] (III) The method of automatically generating Excel data import templates according to database tables in the present invention has many advantages over manually establishing templates, including reducing human errors, improving efficiency, maintaining consistency, facilitating updating and maintenance, scalability, supporting multiple databases, and enhancing data security. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] The present invention is further described below in conjunction with the accompanying drawings.

[0043] Attached Figure 1 A flowchart of a method for automatically generating an EXCEL data import template based on VBA;

[0044] Attached Figure 2 This is a schematic diagram of the main components of the configuration file;

[0045] Attached Figure 3 After obtaining the field information, a schematic diagram is generated for various types of information required by the Excel column based on various types of information in the field. DETAILED DESCRIPTION

[0046] The method, system, device and medium for automatically generating an EXCEL data import template based on VBA of the present invention are described in detail below with reference to the accompanying drawings and specific embodiments of the specification.

[0047] Embodiment 1:

[0048] As attached Figure 1 As shown, this embodiment provides a method for automatically generating an EXCEL data import template based on VBA. The method specifies the configuration information in the configuration file and the information in the field comments, connects to the specified database, extracts the field information, and generates the EXCEL data import template. It clarifies the various types of information that the VBA program needs to read during the conversion process from the database table to the EXCEL template file, the tables that make up a template, the fields in any table that need to be converted into EXCEl columns, and the fields that need dictionary information, so as to meet the EXCEL template requirements.

[0049] In this embodiment, connecting to the specified data is as follows:

[0050] ①The VBA program connects to the specified database through ADO;

[0051] ② The driver of the relevant database is required and the IP address, database user name and password information of the database are configured, and the configuration information is stored in the disk in the form of a configuration file;

[0052] ③Create a FileSystemObject object through the CreateObject function. The FileSystem Object object is used to read files;

[0053] ④Parse the configuration file to obtain the required configuration information.

[0054] As attached Figure 2 As shown, in this embodiment, the field information is extracted as follows:

[0055] ① After the database connection is successful, create an ADO command object and execute the specified SQL statement to obtain the data structure information of any table. At the same time, for the table that needs to be parsed, configure the parsing table name sequence in the configuration file; there are many ways for VBA to connect to the database, such as: DAO, ODBC, OLE DB, etc.;

[0056] ② When it comes to the fields of any table, if it is not necessary to convert all the fields of the table into EXCEL columns, add the identification keyword NOT_IMPORT in the comment information of the column. If the keyword NOT_IMPORT is in the field comment information, the EXCEL column conversion will not be performed;

[0057] The comment for the column that needs to be converted is name, and the comment for the column that is not needed is name|NOT_IMPORT.

[0058] In this embodiment, the data structure information is ['tableName1', 'tableName2', ...]. If any template is composed of multiple tables, the analysis shows that the sequence configuration is: ['tableName1, tableName2', 'tableName3', ...].

[0059] Among them, tableName1 and tableName2 indicate that the two tables tableName1 and tableName2 form an EXCEL data import template.

[0060] In this embodiment, the EXCEL data import template is generated as follows:

[0061] ① According to the obtained field information, set the title and data verification rules for each column of EXCEL;

[0062] ② Based on the conversion of field information to EXCEL columns and related configuration information, generate the correct EXCEL template and store it in the specified disk location.

[0063] As attached Figure 3 As shown, in this embodiment, the field information includes comments, data type, length, and whether it is allowed to be empty;

[0064] Among them, the comment: is converted into the EXCEL column name and indicates whether the corresponding field needs to be converted into a column;

[0065] Data type: Convert to the data type that is allowed by the data validation setting;

[0066] Length: The data length allowed to be filled in when converted to data verification settings;

[0067] Allow empty values: Whether to ignore empty values ​​when converted to data validation settings.

[0068] In this embodiment, according to the acquired field information, the title and data verification rules of each column of EXCEL are set as follows:

[0069] ① For columns with sequence type data validation rules, import the configuration file, and the data structure type is: tableName.columnName.dictCode;

[0070] Among them, tableName represents the table name; columnName represents the column name; dictCode represents the dictionary code used by the corresponding column;

[0071] ②When processing each column, the VBA program checks whether there is corresponding dictionary usage information in the configuration file:

[0072] If it exists, the dictionary information is obtained from the file or API interface according to the read dictCode, and the dictionary information is used as the sequence data when setting the data verification.

[0073] Embodiment 2:

[0074] This embodiment provides a system for automatically generating an EXCEL data import template based on VBA, and the system is used to implement the method for automatically generating an EXCEL data import template based on VBA in Embodiment 1; the system includes:

[0075] The specified data connection module is used to connect to the specified database through ADO using VBA program. It requires the driver of the relevant database and the configuration of the database IP address, database user name and password information. The configuration information is stored in the disk in the form of a configuration file, and a FileSystemObject object is created through the CreateObject function. The FileSystemObject object is used to read the file, parse the configuration file, and obtain the required configuration information.

[0076] The field information extraction module is used to create an ADO command object after the database connection is successful, and execute the specified SQL statement to obtain the data structure information of any table. At the same time, for the table that needs to be parsed, the parsing table name sequence is configured in the configuration file;

[0077] The EXCEL data import template generation module is used to set the title and data verification rules of each Excel column according to the obtained field information, and to generate the correct EXCEL template according to the conversion of field information to EXCEL columns and related configuration information, and store it in the specified disk location.

[0078] Embodiment 3:

[0079] This embodiment also provides an electronic device, including: a memory and at least one processor;

[0080] Wherein, the memory stores computer-executable instructions;

[0081] The at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor executes the method for automatically generating an EXCEL data import template based on VBA in any embodiment of the present invention.

[0082] The processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The processor may be a microprocessor or any conventional processor, etc.

[0083] The memory can be used to store computer programs and / or modules. The processor realizes various functions of the electronic device by running or executing the computer programs and / or modules stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, at least one application required for a function, etc.; the data storage area can store data created according to the use of the terminal, etc. In addition, the memory can also include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (SMC), a secure digital (SD) card, a flash memory card, at least one disk storage period, a flash memory device, or other volatile solid-state storage devices.

[0084] Embodiment 4:

[0085] This embodiment also provides a computer-readable storage medium, in which a plurality of instructions are stored, and the instructions are loaded by a processor, so that the processor executes the method for automatically generating an EXCEL data import template based on VBA in any embodiment of the present invention. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code that implements the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0086] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0087] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RYM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer via a communication network.

[0088] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0089] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0090] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for automatically generating an EXCEL data import template based on VBA, characterized in that: This method specifies the configuration information in the configuration file and the information in the field comments, connects to the specified database, extracts the field information, and generates an EXCEL data import template. It clarifies the tables that the VBA program needs to read during the conversion process from the database table to the EXCEL template file, the tables that make up a template, the fields in any table that need to be converted into EXCEl columns, and the various types of information on the fields that require dictionary information, so as to meet the EXCEL template requirements.

2. The method for automatically generating an EXCEL data import template based on VBA according to claim 1, characterized in that: Connect to the specified data as follows: The VBA program connects to the specified database via ADO; It requires the driver of the relevant database and the configuration of the database IP address, database user name and password information, and stores the configuration information in the form of a configuration file on the disk; Create a FileSystemObject object through the CreateObject function. The FileSystemObject object is used to read files. Parse the configuration file to obtain the required configuration information.

3. The method for automatically generating an EXCEL data import template based on VBA according to claim 1, characterized in that: The extracted field information is as follows: After the database connection is successful, create an ADO command object and execute the specified SQL statement to obtain the data structure information of any table. At the same time, for the table that needs to be parsed, configure the parsing table name sequence in the configuration file; When it comes to any table field, if you do not need to convert all the fields of the table into EXCEL columns, add the identification keyword NOT_IMPORT in the column comment information. If the keyword NOT_IMPORT is in the field comment information, the EXCEL column conversion will not be performed. The comment for the column that needs to be converted is name, and the comment for the column that is not needed is name|NOT_IMPORT.

4. The method for automatically generating an EXCEL data import template based on VBA according to claim 3, characterized in that: The data structure information is ['tableName1', 'tableName2', ...]. If any template is composed of multiple tables, the parsing shows that the sequence configuration is: ['tableName1, tableName2', 'tableName3', ...]. Among them, tableName1 and tableName2 indicate that the two tables tableName1 and tableName2 form an EXCEL data import template.

5. The method for automatically generating an EXCEL data import template based on VBA according to claim 1, characterized in that: Generate an EXCEL data import template as follows: According to the obtained field information, set the title and data verification rules for each column in EXCEL; Based on the conversion of field information to EXCEL columns, in conjunction with relevant configuration information, a correct EXCEL template is generated and stored in the specified disk location.

6. The method for automatically generating an EXCEL data import template based on VBA according to claim 5, characterized in that: Field information includes comments, data type, length, and whether it is allowed to be empty; Among them, the comment: is converted into the EXCEL column name and indicates whether the corresponding field needs to be converted into a column; Data type: Convert to the data type that is allowed by the data validation setting; Length: The data length allowed to be filled in when converted to data verification settings; Allow empty values: Whether to ignore empty values ​​when converted to data validation settings.

7. The method for automatically generating an EXCEL data import template based on VBA according to claim 5, characterized in that: According to the obtained field information, the title and data verification rules of each column of EXCEL are set as follows: For columns with sequence type data validation rules, import the configuration file, and the data structure type is: tableName.columnName.dictCode; Among them, tableName represents the table name; columnName represents the column name; dictCode represents the dictionary code used by the corresponding column; When processing each column, the VBA program checks whether there is corresponding dictionary usage information in the configuration file: If it exists, the dictionary information is obtained from the file or API interface according to the read dictCode, and the dictionary information is used as the sequence data when setting the data verification.

8. A system for automatically generating EXCEL data import templates based on VBA, characterized in that: The system is used to implement the method for automatically generating an EXCEL data import template based on VBA as described in any one of claims 1 to 7; the system comprises: The specified data connection module is used to connect to the specified database through ADO using VBA program. It requires the driver of the relevant database and the configuration of the database IP address, database user name and password information. The configuration information is stored in the disk in the form of a configuration file, and a FileSystemObject object is created through the CreateObject function. The FileSystemObject object is used to read the file, parse the configuration file, and obtain the required configuration information. The field information extraction module is used to create an ADO command object after the database connection is successful, and execute the specified SQL statement to obtain the data structure information of any table. At the same time, for the table that needs to be parsed, the parsing table name sequence is configured in the configuration file; The EXCEL data import template generation module is used to set the title and data verification rules of each Excel column according to the obtained field information, and to generate the correct EXCEL template according to the conversion of field information to EXCEL columns and related configuration information, and store it in the specified disk location.

9. An electronic device, characterized in that: include: memory and at least one processor; Wherein, the memory stores a computer program; The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the method for automatically generating an EXCEL data import template based on VBA as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which can be executed by a processor to implement the method for automatically generating an EXCEL data import template based on VBA as described in any one of claims 1 to 7.

Images