CN112949275A - Method, device, equipment and storage medium for importing electronic forms into database - Google Patents
Method, device, equipment and storage medium for importing electronic forms into database Download PDFInfo
- Publication number
- CN112949275A CN112949275A CN202110271144.0A CN202110271144A CN112949275A CN 112949275 A CN112949275 A CN 112949275A CN 202110271144 A CN202110271144 A CN 202110271144A CN 112949275 A CN112949275 A CN 112949275A
- Authority
- CN
- China
- Prior art keywords
- template
- import
- service
- database
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/186—Templates
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/258—Data format conversion from or to a database
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/177—Editing, e.g. inserting or deleting of tables; using ruled lines
- G06F40/18—Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Audiology, Speech & Language Pathology (AREA)
- General Health & Medical Sciences (AREA)
- Health & Medical Sciences (AREA)
- Artificial Intelligence (AREA)
- Mathematical Physics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides a method, a device, equipment and a storage medium for importing an electronic form into a database, wherein the method comprises the following steps: acquiring service table information corresponding to a target service, wherein the service table information comprises header information, content types of the header information and table names in an electronic table corresponding to the target service; generating an import template of the target service through a template making program according to the service table information, wherein the template making program is used for making the import template of a preset template structure; importing the service data of the target service into a database through an import template; and according to the cell row number corresponding to the data failed to be imported, importing the data through an import template after error correction. According to the method and the device, only a few parameters need to be provided, the import templates of different services can be automatically generated, and the development cost is reduced. After error correction, only data which fails to be imported is imported, so that the efficiency is high. And matching the fields required by the dynamic assembly of the imported template with the fields in the database to realize the dynamic response and flexible configuration of the system.
Description
Technical Field
The application belongs to the technical field of databases, and particularly relates to a method, a device, equipment and a storage medium for importing an electronic form into a database.
Background
In the work, the business scenes that the data of the spreadsheet needs to be imported into the database are more, for example, in the scene that an insurance company receives insurance for employees, the data of the spreadsheet with the insurance information of the employees of the enterprise needs to be imported into the database.
In the related art, it is common to develop an import program of a spreadsheet for a specific business scenario. The importing program firstly creates database table fields, reads data in the electronic form to a system memory with specified rules by using a tool, and then analyzes the data to the database according to the rules.
However, different application scenarios require different data, each time a new table attribute is added, a field of the table attribute needs to be added in each link, the change range is large, the change details are many, and omission is easy. And a new service needs to import different data and needs to rewrite a set of import program, the import program has low reusability and poor flexibility, and sometimes, a function which is just on-line cannot support a changed service requirement and cannot quickly respond to the change of the service.
Disclosure of Invention
The method, the device, the equipment and the storage medium for importing the electronic forms into the database can automatically generate import templates of different services only by providing a few parameters, and development cost is reduced. For the data which fails to be imported, only the data which fails to be imported last time is imported after the data is corrected, the data does not need to be imported again through the whole table, and the efficiency is high. And matching fields required by dynamically assembling the electronic form according to the import template of the fixed structure with fields in the database, so as to realize dynamic response and flexible configuration of the system.
An embodiment of a first aspect of the present application provides a method for importing a spreadsheet into a database, including:
acquiring service table information corresponding to a target service submitted by a user, wherein the service table information comprises header information in an electronic form corresponding to the target service, a content type corresponding to each header information and a table name of the electronic form;
generating an import template corresponding to the target service through a pre-developed template making program according to the service table information, wherein the template making program is used for making an import template of a preset template structure, and the preset template structure comprises a program mapping unit, a database mapping unit, a table file unit and a content type unit;
importing the data in the electronic form corresponding to the target service into a database through the generated import template;
and for the business data which fails to be imported, importing the error-corrected business data which fails to be imported into the database through the import template according to the row number and the column number of the cell corresponding to the business data which fails to be imported.
In some embodiments of the present application, the generating, according to the service table information, an import template corresponding to the target service through a pre-developed template making program includes:
determining program byte codes corresponding to all header information included in the service table information;
determining an sql character string corresponding to each program bytecode;
and generating an import template corresponding to the target service through a pre-developed template making program according to a preset template structure, the header information and the program byte codes corresponding to the header information, the sql character string corresponding to each program byte code, the content type corresponding to each header information and the table name of the electronic table.
In some embodiments of the present application, the generating, according to a preset template structure, the header information and the program bytecode corresponding to each header information, each program bytecode and the corresponding sql character string, the content type corresponding to each header information, and the table name of the electronic table, an import template corresponding to the target service through a pre-developed template making program includes:
creating an initial template according to the preset template structure;
storing each header information and each corresponding program byte code into a program mapping unit in the initial template through a pre-developed template making program;
storing each program byte code and the corresponding sql character string into a database mapping unit in the initial template;
storing the table name included in the service table information into a table file unit in the initial template;
and storing each piece of header information in the service table information and the content type in the initial template corresponding to the header information in a content type unit to obtain an import template corresponding to the target service.
In some embodiments of the present application, the importing, through the generated import template, service data in a spreadsheet corresponding to the target service into a database includes:
displaying the import template;
detecting a downloading request of the import template submitted by a user, and sending the import template to the user;
receiving an import template which is uploaded by a user and is recorded with service data;
and analyzing the import template with the business data, and importing the business data into a database.
In some embodiments of the present application, the parsing the import template into which the service data is entered, and importing the service data into a database includes:
creating a blank table in a database, and naming the blank table by using a table file unit including a table name in the imported template;
determining an sql character string corresponding to each header information according to contents included in a program mapping unit and a database mapping unit in the import template;
assembling the sql character strings corresponding to each header information into a first sql execution statement, and assembling the service data under each header information into a second sql execution statement;
calling an sql executor to execute the first sql execution statement, and inserting an sql character string corresponding to each piece of header information into a header position of the blank table;
and calling the sql executor to execute the second sql execution statement, and respectively inserting the business data under each header information into the corresponding header in the blank table to obtain the database table corresponding to the table name.
In some embodiments of the present application, the method further comprises:
generating and displaying an import result page according to the import result of each service data, wherein the import result page comprises the service data which is successfully imported, the service data which is failed to import and the corresponding failure reason information;
and displaying the import result page.
In some embodiments of the present application, the importing, according to the row and column number of the cell corresponding to the service data that fails to be imported, the service data that fails to be imported after error correction into the database through the import template includes:
receiving the import template uploaded by the user again, wherein the business data to be additionally recorded is input into the import template, and the business data to be additionally recorded is the business data which is failed in import after the error is corrected;
assembling the service data to be complemented and the row and column numbers of the corresponding cells into a third sql execution statement;
and calling an sql executor to execute the third sql execution statement, and inserting the business data to be complemented into the position corresponding to the row and column number in the database table.
An embodiment of a second aspect of the present application provides an apparatus for importing a spreadsheet into a database, comprising:
the system comprises an acquisition module, a processing module and a processing module, wherein the acquisition module is used for acquiring service table information corresponding to a target service submitted by a user, and the service table information comprises each table head information in an electronic table corresponding to the target service, a content type corresponding to each table head information and a table name of the electronic table;
the generating module is used for generating an import template corresponding to the target service through a pre-developed template making program according to the service table information, wherein the template making program is used for making an import template of a preset template structure, and the preset template structure comprises a program mapping unit, a database mapping unit, a table file unit and a content type unit;
the import module is used for importing the business data in the electronic form corresponding to the target business into a database through the generated import template; and for the business data which fails to be imported, importing the error-corrected business data which fails to be imported into the database through the import template according to the row number and the column number of the cell corresponding to the business data which fails to be imported.
Embodiments of the third aspect of the present application provide a computer device comprising a memory and a processor, the memory having stored therein computer-readable instructions, which, when executed by the processor, cause the processor to perform the steps of the method of the first aspect described above.
Embodiments of the fourth aspect of the present application provide a storage medium storing computer-readable instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of the method of the first aspect described above.
The technical scheme provided in the embodiment of the application at least has the following technical effects or advantages:
according to the embodiment of the application, a set of import program does not need to be rewritten every time, and only a few parameters need to be provided, the import templates of different services can be automatically generated, so that the development cost is greatly reduced. The electronic forms are automatically imported into the database through the import template, and the reason of import failure is prompted to the user for the data with import failure, so that the user can quickly correct the error. The specific position of the data needing to be reintroduced in the database table can be automatically determined when the data are reintroduced after being corrected, only the data which are failed to be introduced last time can be introduced based on the specific position, the data do not need to be reintroduced into the whole table, and the efficiency is high. The improved system achieves quick response and improves the data import efficiency. And fields required by the electronic form can be dynamically assembled according to the import template of the fixed structure and matched with fields in the database, so that the dynamic response and flexible configuration of the system are realized. The encryption is carried out before the blank import template and the import template with the input service data are subjected to network transmission, so that the structure or the content of the blank import template is prevented from being illegally tampered in the transmission process, the service data is prevented from being stolen, and the safety of service data transmission is improved.
Additional aspects and advantages of the present application will be set forth in part in the description which follows and, in part, will be obvious from the description, or may be learned by practice of the present application.
Drawings
Various other advantages and benefits will become apparent to those of ordinary skill in the art upon reading the following detailed description of the preferred embodiments. The drawings are only for purposes of illustrating the preferred embodiments and are not to be construed as limiting the application. Also, like reference numerals are used to refer to like parts throughout the drawings.
In the drawings:
FIG. 1 is a flow chart illustrating a method for importing a spreadsheet into a database according to an embodiment of the present application;
FIG. 2 is a diagram illustrating an apparatus for importing an electronic form into a database according to an embodiment of the present application;
FIG. 3 is a schematic diagram illustrating an exemplary configuration of a computer device according to an embodiment of the present disclosure;
fig. 4 is a schematic diagram of a storage medium provided in an embodiment of the present application.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
It will be understood that, as used herein, the terms "first," "second," and the like may be used herein to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish one element from another.
The embodiment of the present application provides a method for importing an electronic form into a database, and referring to fig. 1, the method specifically includes the following steps:
step 101: and acquiring service table information corresponding to the target service submitted by the user.
The service table information includes header information in the electronic table corresponding to the target service, a content type corresponding to each header information, a table name of the electronic table, and the like. The target service can be any service which needs to import data in the spreadsheet into the database, such as insurance service, school score statistics service, shopping mall purchase order service, and the like. The header information is the content of the first cell in any column or the content of the first cell in any row in the spreadsheet. The content type is the type of specific data included in the header information, such as characters, numbers, dates or money. The table name of the spreadsheet is the file name of the spreadsheet file.
For example, the spreadsheet shown in Table 1 is "applicant information Table.xlsx" in insurance services, and then "name", "age", "gender", "occupation" and "quota" in Table 1 are header information included in the spreadsheet. The content types corresponding to the header information of 'name', 'gender' and 'occupation' are character types, the content type corresponding to the header information of 'age' is a number type, and the content type corresponding to the header information of 'premium' is an amount type. The table name of the spreadsheet is "applicant information table".
TABLE 1
| Name (I) | Age (age) | Sex | Occupation of the world | Quota guarantee |
| Zhang San | 28 | Woman | Employee of company A | 1000 |
| … | … | … | … | … |
Step 102: and generating an import template corresponding to the target service through a pre-developed template making program according to the service table information.
The embodiment of the application designs a structure of an import template, and a preset template structure corresponding to the import template at least comprises four parts, namely a program mapping unit (exportModels), a database mapping unit (sql models), a form File unit (spreadsheet File) and a content type unit (typeCode). The exportModels are import header templates, and are used for defining the correspondence between header information in the electronic form and program bytecodes that can be recognized by the template creation program. For example, the program bytecode corresponding to the header information "name" may be "name". The sql models are sql string templates for specifying the correspondence between program bytecodes that can be recognized by the template creation program and sql strings that can be recognized by the database. For example, the sql string corresponding to the program bytecode "name" may be "name". The spreadsheet File is a spreadsheet File for indicating the table name of the spreadsheet to be imported. typeCode is the content type corresponding to the header information.
And developing a set of template making program for generating the import template by a developer based on the preset template structure, wherein the template making program is loaded in a computer, and the computer can be deployed as a server or a user terminal in an application scene corresponding to the target service. The execution subject of the embodiment of the application is the computer. For any service scene, the import template with the same structure can be generated through the template making program, and the data of different service scenes are filled in the import template with the same structure, so that the service data of the electronic form can be imported into the database under different service scenes.
When a user needs to import an electronic form of a target service into a database, the user may first submit service form information corresponding to the target service to a computer. By the template making program, the import template corresponding to the target service can be automatically generated only by submitting a few parameters by a user, different import programs do not need to be developed aiming at different services, and development work is greatly reduced. And the user can modify the generated import template by modifying the parameters included in the service table information so as to import different data, thereby realizing dynamic adjustment and flexible configuration of data import.
Firstly, determining program byte codes corresponding to all header information included in service table information, wherein the program byte codes are program codes which can be identified by a pre-developed template making program; determining an sql character string corresponding to each program bytecode, wherein the sql character string is a database language which can be identified by a database; and generating an import template corresponding to the target service through a pre-developed template making program according to a preset template structure, each header information and the corresponding program byte code thereof, the sql character string corresponding to each program byte code, the content type corresponding to each header information and the table name of the electronic table.
After obtaining the service table information corresponding to the target service through step 101, firstly, creating an initial template according to a preset template structure, wherein the initial template comprises a program mapping unit, a database mapping unit, a table file unit and a content type unit; storing each header information and each corresponding program byte code into a program mapping unit through a pre-developed template making program; storing each program byte code and the corresponding sql character string into a database mapping unit; storing the table name included in the service table information into a table file unit; and storing each piece of header information and the corresponding content type in the service table information into a content type unit to obtain an import template corresponding to the target service.
In other embodiments of the present application, the service table information obtained in step 101 may further include a validation rule corresponding to the header information. For example, the verification rule corresponding to the header information "gender" may be that no other words than "male" and "female" are present, the verification rule corresponding to the header information "name" may be that no number is present, and the like. The preset template structure of the imported template may further include a verification rule unit (verifyModel) in addition to the above four parts, where the verifyModel is a data verification model and is used to specify a verification rule corresponding to the header information. When the import template corresponding to the target service is generated, the verification rule corresponding to the header information included in the service table information may also be stored in the verifyModel.
Step 103: and importing the data in the electronic form corresponding to the target service into a database through the generated import template.
After the import template corresponding to the target service is generated through the step 102, displaying the import template; detecting a downloading request of an import template submitted by a user, and sending the import template to the user; after the user downloads the import template, the business data in the electronic form corresponding to the target business is recorded into the import template, and then the import template is uploaded to the system. Receiving an import template which is uploaded by a user and is recorded with service data; and analyzing the import template with the business data, and importing the business data into a database.
Specifically, a blank table is created in a database, and a table file unit in an import template is used for naming the blank table by using a table name; determining an sql character string corresponding to each header information according to contents included in a program mapping unit and a database mapping unit in an import template; assembling the sql character strings corresponding to each header information into a first sql execution statement, and assembling the service data under each header information into a second sql execution statement; calling an sql executor to execute a first sql execution statement, and inserting an sql character string corresponding to each piece of header information into a header position of a blank table; and calling an sql executor to execute a second sql execution statement, and respectively inserting the business data under each header information into the corresponding header in the blank table to obtain the database table corresponding to the table name.
If the import template includes the verifyModel, before the sql execution statement is assembled, the business data input by the user in the import template can be verified according to the verification rule corresponding to the header information included in the verifyModel. And if the verification is passed, inserting the data into the database according to the mode. If the verification fails, prompting the user to modify the data of the specified row and column, and re-importing the modified data into the database according to the mode.
For example, assuming that the user enters data "123" in the column of header information "name", the user is prompted to modify if the data "123" does not conform to the validation rule, according to the validation rule "including no number" corresponding to the header information "name".
In the process of importing the business data, the sql execution statements are automatically assembled according to the import template of the imported business data, and a table is automatically generated in the database through the sql execution statements, so that the automation degree of the whole process is high, the database can be automatically imported or manually written by technicians, and the data import efficiency is improved.
In other embodiments of the present application, after the business data entered in the import template is imported into the database in the above manner, an import result page may be generated according to the import result of the business data of each entry. The import result page includes data that has been successfully imported and data that has failed to be imported, and the import result page may further include an import failure reason corresponding to the data that has failed to be imported. After the import result page is generated, the import result page is displayed to the user, and the user can intuitively see which data import fails and the reason of the data import failure through the import result page. After the error of the data which fails to be imported can be corrected, the data is re-entered into the import template through the operation of step 104 and is re-imported into the database.
Step 104: and for the business data which fails to be imported, importing the error-corrected business data which fails to be imported into the database through the import template according to the row number and the column number of the cell corresponding to the business data which fails to be imported.
For the data which fails to be imported, the data which succeeds in import does not need to be repeatedly imported when the data is imported again. The user only needs to download the import template corresponding to the target service again, the import template is a blank template, the user firstly determines the row and column number of the cell corresponding to the data which is failed to import in the import template before, then the service data to be additionally recorded is recorded in the cell corresponding to the row and column number in the import template, and the service data to be additionally recorded is the service data which is failed to import after the error is corrected. And then uploading the import template to a server or a terminal which is the main body of the application execution. And the server or the terminal assembles a third sql execution statement corresponding to the service data to be supplemented at the cell corresponding to the row and column number according to the operation in step 103, where the third sql execution statement includes the service data to be supplemented, the identifier of the table to which the service data to be supplemented belongs, and the location information of the data in the corresponding table in the database. And calling an sql executor to execute a third sql execution statement, and positioning a table corresponding to the target service from a large number of tables in the database according to the identifier of the table to which the service data to be complemented belongs. And then according to the position information of the service data to be complemented and recorded in the table, determining an insertion position corresponding to the service data to be complemented and recorded from the table, and finally inserting the service data to be complemented and recorded into the determined insertion position.
In this way, the user is prompted about the reason for the failed import with respect to the data that failed to be imported, which helps the user to quickly correct the error. The specific position of the data needing to be reintroduced in the database table can be automatically determined when the data are reintroduced after being corrected, only the data which are failed to be introduced last time can be introduced based on the specific position, the data do not need to be reintroduced into the whole table, and the efficiency is high.
In other embodiments of the present application, when a user registers in the server or the terminal of the execution subject of the embodiments of the present application, the server or the terminal sends a key to the user, where the key may be a symmetric encryption key or an asymmetric encryption key. If the key is a symmetric key, the server or the terminal also stores the symmetric key. If the key sent to the user is an asymmetric encryption key, the server or the terminal needs to generate two key pairs by a preset asymmetric encryption algorithm: (first private key, first public key) and (second private key, second public key). The server or the terminal stores the first private key and the second public key and distributes the first public key and the second private key to the user. When the user requests to download the import template, the import template may be encrypted first, for example, by using the stored symmetric key, or by using the first private key. After the user receives the ciphertext of the import template, if the import template is encrypted by using the symmetric key, the user decrypts the import template by using the symmetric key stored by the user. If the import template is encrypted by adopting the first private key, the user decrypts by adopting the first public key stored by the user. The import template is encrypted in the above way, so that the structure or the content of the import template is prevented from being illegally tampered in the transmission process.
And after the user inputs the service data into the decrypted import template, encrypting the import template by adopting the symmetric key stored by the user. Or, the import template is encrypted by a second private key stored by the user. And then uploading the encrypted import template to a server or a terminal. After the server or the terminal receives the ciphertext of the import template, if the import template is encrypted by using the symmetric key, the import template is decrypted by using the symmetric key stored locally. And if the import template is encrypted by adopting a second private key, decrypting by adopting a second public key stored locally. By encrypting the import template of the input service data, the service data can be prevented from being stolen in the transmission process, and the safety of service data transmission is improved.
According to the embodiment of the application, the template is defined reversely through the template mode, the rule is set, and the template information is read according to the set rule. Under the same template, the services are different, the data are different, but the implementation mode is completely the same. In addition, the embodiment of the application can encrypt the parameters and the template identification in the template, so that the transmission of the imported template is safer. The placeholder (namely the sql statement corresponding to the header information) of the header information in the database and the parameter matching rule use the self-defined rule, so that sql injection and the like are prevented, and partial potential safety hazards are eliminated.
According to the embodiment of the application, a set of import program does not need to be rewritten every time, and only a few parameters need to be provided, the import templates of different services can be automatically generated, so that the development cost is greatly reduced. The electronic forms are automatically imported into the database through the import template, and the reason of import failure is prompted to the user for the data with import failure, so that the user can quickly correct the error. The specific position of the data needing to be reintroduced in the database table can be automatically determined when the data are reintroduced after being corrected, only the data which are failed to be introduced last time can be introduced based on the specific position, the data do not need to be reintroduced into the whole table, and the efficiency is high. The improved system achieves quick response and improves the data import efficiency. And fields required by the electronic form can be dynamically assembled according to the import template of the fixed structure and matched with fields in the database, so that the dynamic response and flexible configuration of the system are realized. The encryption is carried out before the blank import template and the import template with the input service data are subjected to network transmission, so that the structure or the content of the blank import template is prevented from being illegally tampered in the transmission process, the service data is prevented from being stolen, and the safety of service data transmission is improved.
The embodiment of the present application provides an apparatus for importing an electronic form into a database, where the apparatus is configured to perform the method for importing an electronic form into a database according to any of the foregoing embodiments, and as shown in fig. 2, the apparatus includes:
an obtaining module 201, configured to obtain service table information corresponding to a target service submitted by a user, where the service table information includes header information in an electronic form corresponding to the target service, a content type corresponding to each header information, and a table name of the electronic form;
the generation module 202 is configured to generate an import template corresponding to the target service through a pre-developed template making program according to the service table information, where the template making program is used to make an import template of a preset template structure, and the preset template structure includes a program mapping unit, a database mapping unit, a table file unit, and a content type unit;
the import module 203 is used for importing the data in the electronic form corresponding to the target service into the database through the generated import template; and for the business data which fails to be imported, importing the error-corrected business data which fails to be imported into the database through the import template according to the row number and the column number of the cell corresponding to the business data which fails to be imported.
A generating module 202, configured to determine program byte codes corresponding to header information included in the service table information; determining an sql character string corresponding to each program bytecode; and generating an import template corresponding to the target service through a pre-developed template making program according to a preset template structure, each header information and the corresponding program byte code thereof, the sql character string corresponding to each program byte code, the content type corresponding to each header information and the table name of the electronic table.
A generating module 202, configured to create an initial template according to a preset template structure; storing each header information and each corresponding program byte code into a program mapping unit through a pre-developed template making program; storing each program byte code and the corresponding sql character string into a database mapping unit; storing the table name included in the service table information into a table file unit; and storing each piece of header information and the corresponding content type in the service table information into a content type unit to obtain an import template corresponding to the target service.
An import module 203, configured to display an import template; detecting a downloading request of an import template submitted by a user, and sending the import template to the user; receiving an import template which is uploaded by a user and is recorded with service data; and analyzing the import template with the business data, and importing the business data into a database.
The import module 203 is used for creating a blank table in the database, and naming the blank table by using the table file unit in the import template, including the table name; determining an sql character string corresponding to each header information according to contents included in a program mapping unit and a database mapping unit in an import template; assembling the sql character strings corresponding to each header information into a first sql execution statement, and assembling the service data under each header information into a second sql execution statement; calling an sql executor to execute a first sql execution statement, and inserting an sql character string corresponding to each piece of header information into a header position of a blank table; and calling an sql executor to execute a second sql execution statement, and respectively inserting the business data under each header information into the corresponding header in the blank table to obtain the database table corresponding to the table name.
The device also includes: the result page generating module is used for generating and displaying an import result page according to the import result of each service data, wherein the import result page comprises the service data which is successfully imported, the service data which is failed to import and the corresponding failure reason information; and displaying an import result page.
The device also includes: the additional recording module is used for receiving the import template uploaded by the user again, inputting the service data to be additionally recorded into the import template, wherein the service data to be additionally recorded is the service data which is failed to be imported after the error is corrected; assembling the business data to be complemented and the row and column numbers of the corresponding cells into a third sql execution statement; and calling an sql executor to execute a third sql execution statement, and inserting the service data to be complemented into the position corresponding to the row and column numbers in the database table.
According to the embodiment of the application, a set of import program does not need to be rewritten every time, and only a few parameters need to be provided, the import templates of different services can be automatically generated, so that the development cost is greatly reduced. The electronic forms are automatically imported into the database through the import template, and the reason of import failure is prompted to the user for the data with import failure, so that the user can quickly correct the error. The specific position of the data needing to be reintroduced in the database table can be automatically determined when the data are reintroduced after being corrected, only the data which are failed to be introduced last time can be introduced based on the specific position, the data do not need to be reintroduced into the whole table, and the efficiency is high. The improved system achieves quick response and improves the data import efficiency. And fields required by the electronic form can be dynamically assembled according to the import template of the fixed structure and matched with fields in the database, so that the dynamic response and flexible configuration of the system are realized. The encryption is carried out before the blank import template and the import template with the input service data are subjected to network transmission, so that the structure or the content of the blank import template is prevented from being illegally tampered in the transmission process, the service data is prevented from being stolen, and the safety of service data transmission is improved.
The embodiment of the application provides computer equipment. As shown in fig. 3, the computer device includes a processor, a non-volatile storage medium, a memory, and a network interface connected through a system bus. Wherein the non-volatile storage medium of the computer device stores an operating system, a database having control information sequences stored therein, and computer readable instructions which, when executed by the processor, cause the processor to implement a method of importing a spreadsheet into the database. The processor of the computer device is used for providing calculation and control capability and supporting the operation of the whole computer device. The memory of the computer device may have stored therein computer readable instructions that, when executed by the processor, may cause the processor to perform a method of importing a spreadsheet into a database. The network interface of the computer device is used for connecting and communicating with the terminal. Those skilled in the art will appreciate that the architecture shown in fig. 3 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
The computer device comprises a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the following steps when executing the computer program: acquiring service table information corresponding to a target service submitted by a user, wherein the service table information comprises each table head information in an electronic table corresponding to the target service, a content type corresponding to each table head information and a table name of the electronic table; generating an import template corresponding to a target service through a pre-developed template making program according to service table information, wherein the template making program is used for making the import template of a preset template structure, and the preset template structure comprises a program mapping unit, a database mapping unit, a table file unit and a content type unit; importing the business data in the electronic form corresponding to the target business into a database through the generated import template; and for the business data which fails to be imported, importing the error-corrected business data which fails to be imported into the database through the import template according to the row number and the column number of the cell corresponding to the business data which fails to be imported.
The processor, when executing the computer program, may further perform the steps of: determining program byte codes corresponding to all header information included in the service table information; determining an sql character string corresponding to each program bytecode; and generating an import template corresponding to the target service through a pre-developed template making program according to a preset template structure, each header information and the corresponding program byte code thereof, the sql character string corresponding to each program byte code, the content type corresponding to each header information and the table name of the electronic table.
The processor, when executing the computer program, may further perform the steps of: creating an initial template according to a preset template structure; storing each header information and each corresponding program byte code into a program mapping unit through a pre-developed template making program; storing each program byte code and the corresponding sql character string into a database mapping unit; storing the table name included in the service table information into a table file unit; and storing each piece of header information and the corresponding content type in the service table information into a content type unit to obtain an import template corresponding to the target service.
The processor, when executing the computer program, may further perform the steps of: displaying the import template; detecting a downloading request of an import template submitted by a user, and sending the import template to the user; receiving an import template which is uploaded by a user and is recorded with service data; and analyzing the import template with the business data, and importing the business data into a database.
The processor, when executing the computer program, may further perform the steps of: creating a blank table in a database, and naming the blank table by using a table file unit in an import template, wherein the table file unit comprises a table name; determining an sql character string corresponding to each header information according to contents included in a program mapping unit and a database mapping unit in an import template; assembling the sql character strings corresponding to each header information into a first sql execution statement, and assembling the service data under each header information into a second sql execution statement; calling an sql executor to execute a first sql execution statement, and inserting an sql character string corresponding to each piece of header information into a header position of a blank table; and calling an sql executor to execute a second sql execution statement, and respectively inserting the business data under each header information into the corresponding header in the blank table to obtain the database table corresponding to the table name.
The processor, when executing the computer program, may further perform the steps of: generating a display import result page according to the import result of each service data, wherein the import result page comprises the service data which is successfully imported, the service data which is failed to import and the corresponding failure reason information; and displaying an import result page.
The processor, when executing the computer program, may further perform the steps of: receiving an import template uploaded by the user again, wherein the business data to be additionally recorded are input into the import template, and the business data to be additionally recorded are the business data which is failed to be imported after error correction; assembling the business data to be complemented and the row and column numbers of the corresponding cells into a third sql execution statement; and calling an sql executor to execute a third sql execution statement, and inserting the service data to be complemented into the position corresponding to the row and column numbers in the database table.
Embodiments of the present application also provide a storage medium storing computer-readable instructions, as shown in fig. 4, which when executed by one or more processors, cause the one or more processors to perform the following steps: acquiring service table information corresponding to a target service submitted by a user, wherein the service table information comprises each table head information in an electronic table corresponding to the target service, a content type corresponding to each table head information and a table name of the electronic table; generating an import template corresponding to a target service through a pre-developed template making program according to service table information, wherein the template making program is used for making the import template of a preset template structure, and the preset template structure comprises a program mapping unit, a database mapping unit, a table file unit and a content type unit; importing the business data in the electronic form corresponding to the target business into a database through the generated import template; and for the business data which fails to be imported, importing the error-corrected business data which fails to be imported into the database through the import template according to the row number and the column number of the cell corresponding to the business data which fails to be imported.
The processor may further perform the steps of: determining program byte codes corresponding to all header information included in the service table information; determining an sql character string corresponding to each program bytecode; and generating an import template corresponding to the target service through a pre-developed template making program according to a preset template structure, each header information and the corresponding program byte code thereof, the sql character string corresponding to each program byte code, the content type corresponding to each header information and the table name of the electronic table.
The processor may further perform the steps of: creating an initial template according to a preset template structure, wherein the initial template comprises a program mapping unit, a database mapping unit, a table file unit and a content type unit; storing each header information and each corresponding program byte code into a program mapping unit through a pre-developed template making program; storing each program byte code and the corresponding sql character string into a database mapping unit; storing the table name included in the service table information into a table file unit; and storing each piece of header information and the corresponding content type in the service table information into a content type unit to obtain an import template corresponding to the target service.
The processor may further perform the steps of: displaying the import template; detecting a downloading request of an import template submitted by a user, and sending the import template to the user; receiving an import template which is uploaded by a user and is recorded with service data; and analyzing the import template with the business data, and importing the business data into a database.
The processor may further perform the steps of: creating a blank table in a database, and naming the blank table by using a table file unit in an import template, wherein the table file unit comprises a table name; determining an sql character string corresponding to each header information according to contents included in a program mapping unit and a database mapping unit in an import template; assembling the sql character strings corresponding to each header information into a first sql execution statement, and assembling the service data under each header information into a second sql execution statement; calling an sql executor to execute a first sql execution statement, and inserting an sql character string corresponding to each piece of header information into a header position of a blank table; and calling an sql executor to execute a second sql execution statement, and respectively inserting the business data under each header information into the corresponding header in the blank table to obtain the database table corresponding to the table name.
The processor may further perform the steps of: generating a display import result page according to the import result of each service data, wherein the import result page comprises the service data which is successfully imported, the service data which is failed to import and the corresponding failure reason information; and displaying an import result page.
The processor may further perform the steps of: receiving an import template uploaded by the user again, wherein the business data to be additionally recorded are input into the import template, and the business data to be additionally recorded are the business data which is failed to be imported after error correction; assembling the business data to be complemented and the row and column numbers of the corresponding cells into a third sql execution statement; and calling an sql executor to execute a third sql execution statement, and inserting the service data to be complemented into the position corresponding to the row and column numbers in the database table.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by a computer program, which can be stored in a computer-readable storage medium, and can include the processes of the embodiments of the methods described above when the computer program is executed. The storage medium may be a non-volatile storage medium such as a magnetic disk, an optical disk, a Read-Only Memory (ROM), or a Random Access Memory (RAM).
The technical features of the embodiments described above may be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the embodiments described above are not described, but should be considered as being within the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the present application. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.
Claims (10)
1. A method of importing a spreadsheet into a database, comprising:
acquiring service table information corresponding to a target service submitted by a user, wherein the service table information comprises header information in an electronic form corresponding to the target service, a content type corresponding to each header information and a table name of the electronic form;
generating an import template corresponding to the target service through a pre-developed template making program according to the service table information, wherein the template making program is used for making an import template of a preset template structure, and the preset template structure comprises a program mapping unit, a database mapping unit, a table file unit and a content type unit;
importing the business data in the electronic form corresponding to the target business into a database through the generated import template;
and for the business data which fails to be imported, importing the error-corrected business data which fails to be imported into the database through the import template according to the row number and the column number of the cell corresponding to the business data which fails to be imported.
2. The method according to claim 1, wherein the generating an import template corresponding to the target service by a pre-developed template creation program according to the service table information comprises:
determining program byte codes corresponding to all header information included in the service table information;
determining an sql character string corresponding to each program bytecode;
and generating an import template corresponding to the target service through a pre-developed template making program according to a preset template structure, the header information and the program byte codes corresponding to the header information, the sql character string corresponding to each program byte code, the content type corresponding to each header information and the table name of the electronic table.
3. The method according to claim 2, wherein the generating an import template corresponding to the target service through a pre-developed template making program according to a preset template structure, the header information and the corresponding program bytecode thereof, each program bytecode and the corresponding sql string, the content type corresponding to each header information, and the table name of the electronic form includes:
creating an initial template according to the preset template structure;
storing each header information and each corresponding program byte code into a program mapping unit in the initial template through a pre-developed template making program;
storing each program byte code and the corresponding sql character string into a database mapping unit in the initial template;
storing the table name included in the service table information into a table file unit in the initial template;
and storing each piece of header information and the corresponding content type in the service table information into a content type unit in the initial template to obtain an import template corresponding to the target service.
4. The method according to claim 1, wherein the importing, through the generated import template, the service data in the electronic form corresponding to the target service into a database includes:
displaying the import template;
detecting a downloading request of the import template submitted by a user, and sending the import template to the user;
receiving an import template which is uploaded by a user and is recorded with service data;
and analyzing the import template with the business data, and importing the business data into a database.
5. The method according to claim 4, wherein the parsing the import template with the business data entered therein and importing the business data into the database comprises:
creating a blank table in a database, and naming the blank table by using a table file unit including a table name in the imported template;
determining an sql character string corresponding to each header information according to contents included in a program mapping unit and a database mapping unit in the import template;
assembling the sql character strings corresponding to each header information into a first sql execution statement, and assembling the service data under each header information into a second sql execution statement;
calling an sql executor to execute the first sql execution statement, and inserting an sql character string corresponding to each piece of header information into a header position of the blank table;
and calling the sql executor to execute the second sql execution statement, and respectively inserting the business data under each header information into the corresponding header in the blank table to obtain the database table corresponding to the table name.
6. The method of claim 5, further comprising:
generating and displaying an import result page according to the import result of each service data, wherein the import result page comprises the service data which is successfully imported, the service data which is failed to import and the corresponding failure reason information;
and displaying the import result page.
7. The method according to claim 6, wherein the importing, according to the row and column number of the cell corresponding to the service data that fails to be imported, the service data that fails to be imported after error correction into the database through the import template includes:
receiving the import template uploaded by the user again, wherein the business data to be additionally recorded is input into the import template, and the business data to be additionally recorded is the business data which is failed in import after the error is corrected;
assembling the service data to be complemented and the row and column numbers of the corresponding cells into a third sql execution statement;
and calling an sql executor to execute the third sql execution statement, and inserting the business data to be complemented into the position corresponding to the row and column number in the database table.
8. An apparatus for importing a spreadsheet into a database, comprising:
the system comprises an acquisition module, a processing module and a processing module, wherein the acquisition module is used for acquiring service table information corresponding to a target service submitted by a user, and the service table information comprises each table head information in an electronic table corresponding to the target service, a content type corresponding to each table head information and a table name of the electronic table;
the generating module is used for generating an import template corresponding to the target service through a pre-developed template making program according to the service table information, wherein the template making program is used for making an import template of a preset template structure, and the preset template structure comprises a program mapping unit, a database mapping unit, a table file unit and a content type unit;
the import module is used for importing the business data in the electronic form corresponding to the target business into a database through the generated import template; and for the business data which fails to be imported, importing the error-corrected business data which fails to be imported into the database through the import template according to the row number and the column number of the cell corresponding to the business data which fails to be imported.
9. A computer device comprising a memory and a processor, the memory having stored therein computer-readable instructions that, when executed by the processor, cause the processor to perform the steps of the method of any of claims 1 to 7.
10. A storage medium having stored thereon computer-readable instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of the method of any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110271144.0A CN112949275A (en) | 2021-03-12 | 2021-03-12 | Method, device, equipment and storage medium for importing electronic forms into database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110271144.0A CN112949275A (en) | 2021-03-12 | 2021-03-12 | Method, device, equipment and storage medium for importing electronic forms into database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112949275A true CN112949275A (en) | 2021-06-11 |
Family
ID=76229625
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110271144.0A Pending CN112949275A (en) | 2021-03-12 | 2021-03-12 | Method, device, equipment and storage medium for importing electronic forms into database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112949275A (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113723910A (en) * | 2021-08-05 | 2021-11-30 | 中核武汉核电运行技术股份有限公司 | Nuclear power plant parameter configuration method, device, equipment and computer readable storage medium |
| CN114661751A (en) * | 2022-03-22 | 2022-06-24 | 医渡云（北京）技术有限公司 | Data production method, device, system, equipment and medium based on SQL (structured query language) knowledge base |
| CN114861614A (en) * | 2022-03-01 | 2022-08-05 | 重庆波谷信息技术有限公司 | Method and device for filling data, electronic equipment and medium |
| CN117608536A (en) * | 2023-12-20 | 2024-02-27 | 杭州明佑电子有限公司 | Gap data online template customization and supplementary recording system and method thereof |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110046334A (en) * | 2019-03-21 | 2019-07-23 | 平安国际智慧城市科技股份有限公司 | Data lead-in method, device, computer equipment and storage medium |
| CN111680477A (en) * | 2020-04-28 | 2020-09-18 | 中国平安财产保险股份有限公司 | Method and device for exporting spreadsheet file, computer equipment and storage medium |
-
2021
- 2021-03-12 CN CN202110271144.0A patent/CN112949275A/en active Pending
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110046334A (en) * | 2019-03-21 | 2019-07-23 | 平安国际智慧城市科技股份有限公司 | Data lead-in method, device, computer equipment and storage medium |
| CN111680477A (en) * | 2020-04-28 | 2020-09-18 | 中国平安财产保险股份有限公司 | Method and device for exporting spreadsheet file, computer equipment and storage medium |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113723910A (en) * | 2021-08-05 | 2021-11-30 | 中核武汉核电运行技术股份有限公司 | Nuclear power plant parameter configuration method, device, equipment and computer readable storage medium |
| CN114861614A (en) * | 2022-03-01 | 2022-08-05 | 重庆波谷信息技术有限公司 | Method and device for filling data, electronic equipment and medium |
| CN114661751A (en) * | 2022-03-22 | 2022-06-24 | 医渡云（北京）技术有限公司 | Data production method, device, system, equipment and medium based on SQL (structured query language) knowledge base |
| CN117608536A (en) * | 2023-12-20 | 2024-02-27 | 杭州明佑电子有限公司 | Gap data online template customization and supplementary recording system and method thereof |
| CN117608536B (en) * | 2023-12-20 | 2024-05-28 | 杭州明佑电子有限公司 | Gap data online template customization and supplementary recording system and method thereof |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN112949275A (en) | Method, device, equipment and storage medium for importing electronic forms into database | |
| CN108156237B (en) | Product information pushing method and device, storage medium and computer equipment | |
| CN111478967B (en) | Request processing method and device | |
| CN107704479B (en) | File generation method and device | |
| CN110275861B (en) | Data storage method and device, storage medium and electronic device | |
| CA3070109A1 (en) | Systems and methods for encryption and authentication | |
| JP2006518499A (en) | Ordering program data for loading into the device | |
| CN111767704A (en) | Excel form template generation method and device | |
| CN112860749A (en) | Data processing method, data processing device, computer equipment and storage medium | |
| CN113330419A (en) | Equipment application installation method and device | |
| CN112528201A (en) | Method and device for calling third-party platform, computer equipment and storage medium | |
| US10956408B2 (en) | Data transformation tool | |
| CN114003510A (en) | Script testing method, device, equipment and medium based on Mock service | |
| CN112612833B (en) | Rule package updating method, device, equipment and storage medium | |
| US8493581B2 (en) | Electronic document having authentication function | |
| CN116302079B (en) | Service data processing method and device, electronic equipment and storage medium | |
| WO2021142335A1 (en) | Systems and methods for tracking data lineage and record lifecycle using distributed ledgers | |
| CN116126291A (en) | Quick development method, device, equipment and storage medium based on information management | |
| CN111143399B (en) | Data processing method, device, storage medium and computer equipment | |
| CN115758464A (en) | Data desensitization management method, device, medium and equipment based on configuration | |
| CN111198692A (en) | Installation package generation method and device | |
| CN115589316A (en) | Data encryption transmission method and device, electronic equipment and storage medium | |
| CN113703753B (en) | Method and device for product development and product development system | |
| US11722324B2 (en) | Secure and accountable execution of robotic process automation | |
| CN110636042B (en) | Method, device and equipment for updating verified block height of server |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |