An Excel File Import and Data Verification Method Based on the Spring Boot Framework

Abstract

The present invention relates to a method for importing Excel files and data verification based on the Spring Boot framework, belonging to the technical field of data processing. Based on the Spring Boot framework and using the principle of Java reflection, the present invention designs a method for importing Excel files and data verification based on the Spring Boot framework. Compared with the existing methods, it optimizes the software code, reduces the incidence of software problems, improves the efficiency of modifying code, simplifies the software development process, and can meet the requirement of batch adding Excel file data to the database at the same time.

Description

Technical Field

[0001] The present invention belongs to the technical field of data processing, and particularly relates to a method for importing Excel files and validating data based on the Spring Boot framework. Background Art

[0002] With the development of informatization, information systems are usually adopted in various fields to manage various types of data in the industry. When faced with the situation of adding a large number of data, the method of importing Excel files into the information system is usually adopted. However, when implementing the code for the function of importing Excel files, it is usually necessary to validate the data in each cell of the Excel file according to the actual requirements of the system, determine whether the data meets the requirements, and only after passing the validation can it be added to the database. The existing methods have the following disadvantages:

[0003] 1) If there are too many data items in the Excel file or too many requirements for the data, the code will be too long and more prone to errors;

[0004] 2) If the requirements of the information system change frequently, the workload of modifying the code will be too large;

[0005] 3) The code reuse efficiency is low. When adding the import function for other types of data, it cannot be implemented in the shortest time and requires too much time for debugging. Summary of the Invention

[0006] (1) Technical Problems to be Solved

[0007] The technical problem to be solved by the present invention is: how to design a method for importing Excel files and validating data, optimize the software code, reduce the incidence of software problems, improve the efficiency of modifying the code, and simplify the software development process.

[0008] (2) Technical Solutions

[0009] To solve the above technical problems, the present invention provides a method for importing Excel files and validating data based on the Spring Boot framework, including the following steps:

[0010] S1, Read the imported Excel file and save the data in list A;

[0011] S2, Initialize the list B object to save the data that passes the validation;

[0012] S3, Initialize the dictionary data;

[0013] S4, Obtain the attributes with the "file import" label in the entity class corresponding to the imported data and save them in list C. The "file import" label carries the validation rules for each attribute;

[0014] S5. Preprocess the data in list A;

[0015] S6. Read the first piece of data in list A;

[0016] S7. According to the verification rules of each attribute in list C, verify the current piece of data in list A;

[0017] S8. If the verification is passed, add the data to list B and then execute the next step; if the verification fails, directly execute S11;

[0018] S9. Determine whether the data in list A has been verified. If the data in list A has not been verified, read the next piece of data in list A and return to step S7; otherwise, execute the next step;

[0019] S10. Add the data in list B that has passed the verification to the database to complete the import of the Excel file data;

[0020] S11. Return the execution result; if the import of the Excel file data fails, give the corresponding prompt.

[0021] Preferably, in this method, the DataImport class is used to define the verification rule information of each attribute of the entity class. There are the following four cases of data that need to be verified:

[0022] (1) Whether the data is a required item;

[0023] (2) Whether the data is a dictionary data item;

[0024] (3) Whether the data needs to meet certain rules;

[0025] (4) Whether the data needs to be unique.

[0026] Preferably, in this method, the Entity class is used to import the entity class of the data, which is consistent with the attribute values in the database.

[0027] Preferably, in this method, the Controller class is used to receive the Excel file sent by the foreground and return the result information of the imported file. If the import fails because the file data does not meet the requirements and fails the verification, return the specific information that the data does not meet the requirements.

[0028] Preferably, steps S1 and S11 are implemented using the Controller class.

[0029] Preferably, in this method, the Service class is used to implement the specific business processing process of importing the Excel file, complete the data verification work, and the Service class is executed after being called by the Controller class.

[0030] Preferably, the steps S2 to S9 are implemented by using the Service class.

[0031] Preferably, in this method, the Mapper class is used to interact with the database, and after passing the verification in the Service class, the data in the Excel file is inserted into the database.

[0032] Preferably, the step S10 is implemented by using the Mapper class.

[0033] The present invention also provides a system for implementing the above method.

[0034] (III) Beneficial effects

[0035] Based on the Spring Boot framework and using the principle of java reflection, the present invention designs an Excel file import and data verification method based on the Spring Boot framework. Compared with the existing methods, the present invention optimizes the software code, reduces the incidence of software problems, improves the efficiency of modifying code, simplifies the software development process, and can meet the requirement of batch adding Excel file data to the database at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 It is the software code class structure diagram of the embodiment of the present invention;

[0037] Figure 2 It is the execution flow chart of the Controller class of the embodiment of the present invention;

[0038] Figure 3 It is the execution flow chart of the Service class of the embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0039] To make the objectives, contents and advantages of the present invention clearer, the following further describes in detail the specific embodiments of the present invention with reference to the drawings and embodiments.

[0040] The present invention provides an Excel file import and data verification method based on the Spring Boot framework, including the following steps:

[0041] S1, Read the imported Excel file and save the data in list A;

[0042] S2, Initialize the list B object to save the verified qualified data;

[0043] S3, Initialize the dictionary data;

[0044] S4. Obtain the attributes with the "file import" label in the entity class corresponding to the imported data, and save them in list C. The "file import" label carries the verification rules for each attribute;

[0045] S5. Preprocess the data in list A;

[0046] S6. Read the first piece of data in list A;

[0047] S7. Verify the current piece of data in list A according to the verification rules of each attribute in list C;

[0048] S8. If the verification is passed, add the data to list B and then execute the next step; if the verification fails, directly execute S11;

[0049] S9. Determine whether the data in list A has been verified. If the data in list A has not been verified, read the next piece of data in list A and return to step S7; otherwise, execute the next step;

[0050] S10. Add the data in list B that has passed the verification to the database to complete the import of Excel file data;

[0051] S11. Return the execution result; if the import of Excel file data fails, give a corresponding prompt, such as "The XX attribute in row XX does not conform to the XX rule and needs to be modified and imported again."

[0052] As Figure 1 shown, an Excel file import and data verification method based on the Spring Boot framework provided by an embodiment of the present invention uses the following software code classes:

[0053] DataImport class: Used to define the verification rule information of each attribute of the entity class. For example, in the embodiment of the present invention, there are the following four cases where the data needs to be verified:

[0054] (5) Whether the data is a required item;

[0055] (6) Whether the data is a dictionary data item. For example, the gender can only be "male" or "female";

[0056] (7) Whether the data needs to meet certain rules, such as data like mobile phone numbers and ID card numbers;

[0057] (8) Whether the data needs to be unique. For example, the ID card number cannot be repeated.

[0058] The core code of the DataImport class is as follows (the function of each piece of code can be referred to the comments therein):

[0059]

[0060]

[0061] Entity class: The entity class for importing data, which is basically consistent with the attribute values in the database. It should be noted that for each attribute that needs to import data through an Excel file, the @DataImport ("file import" label) needs to be added before the attribute to define the verification rules for this attribute.

[0062] The core code of the Entity class is as follows:

[0063]

[0064]

[0065] private String sfzh; / / ID number, this attribute is required, meets the format requirements, and is unique

[0066] Controller class: Used to receive the Excel file sent by the front end and return the result information of the imported file. If the import fails because the file data does not meet the requirements and fails the verification, the specific information that the data does not meet the requirements will be returned.

[0067] Specifically, the Controller class implements steps S1 and S11, and the execution process of the Controller class is as Figure 2 shown.

[0068] Service class: Used to implement the specific business processing process of importing the Excel file, mainly completing the data verification work, and executed after being called by the Controller class.

[0069] Specifically, the Service class implements steps S2 to S9, and the execution process of the Service class is as Figure 3 shown.

[0070] The core code of the Service class is as follows:

[0071]

[0072]

[0073]

[0074] Mapper class: Used to interact with the database. After passing the verification by the Service class, the data in the Excel file will be inserted into the database.

[0075] Specifically, the Mapper class implements step S10.

[0076] The solution of the present invention has the following characteristics:

[0077] (1) Construct the DataImport class to provide support for configuring the data item import rules of the Excel file, and use this class as the "file import" label, that is, define the rules for each field of the Entity class. During the process of performing data verification on the Excel file, the rule information carried in the "file import" label can be read through formalized code, and the data in the Excel file can be verified according to these rule information;

[0078] (2) If the requirements of the software change, only the values in the "file import" label need to be simply modified, without modifying or adding entire code segments, reducing development problems and R & D costs;

[0079] (3) The code reuse rate is increased. For any data, when it is necessary to batch add it to the database by means of Excel file import, there is no need to re-develop, and only a small amount of modification needs to be made on the basis of the original code, simplifying the development process.

[0080] The above are only the preferred embodiments of the present invention. It should be noted that for those of ordinary skill in the art, without departing from the technical principle of the present invention, several improvements and deformations can be made, and these improvements and deformations should also be regarded as the protection scope of the present invention.

Claims

1. A method for importing Excel files and validating data based on the Spring Boot framework, characterized in that, it includes the following steps: S1, Read the imported Excel file and save the data in list A; S2, Initialize the list B object to save the data that passes the validation; S3, Initialize the dictionary data; S4, Obtain the attributes with the "file import" label in the entity class corresponding to the imported data and save them in list C, and the "file import" label carries the validation rules for each attribute; S5, Preprocess the data in list A; S6, Read the first piece of data in list A; S7, Validate the current piece of data in list A according to the validation rules of each attribute in list C; S8, If the validation is passed, add the data to list B and then execute the next step; If the validation fails, directly execute S11; S9, Determine whether the data in list A has been validated. If the data in list A has not been validated, read the next piece of data in list A and return to step S7, otherwise execute the next step; S10, Add the data in list B that passes the validation to the database to complete the import of Excel file data; S11, Return the execution result; If the import of Excel file data fails, give a corresponding prompt; In this method, the DataImport class is used to define the validation rule information of each attribute of the entity class. There are the following four cases of data that need to be validated: (1) Whether this data is a required item; (2) Whether this data is a dictionary data item; (3) Whether this data needs to meet certain rules; (4) Whether this data needs to be unique; In this method, the Controller class is used to receive the Excel file sent by the front end and return the import file result information. If the import fails because the file data does not meet the requirements and fails the validation, return the specific information that the data does not meet the requirements.

2. The method according to claim 1, characterized in that, in this method, the Entity class is used to import the entity class of the data, which is consistent with the attribute values in the database.

3. The method according to claim 1, characterized in that, Steps S1 and S11 are implemented using the Controller class.

4. The method according to claim 1, characterized in that, in this method, the Service class is used to implement the specific business processing process of importing Excel files, complete the data validation work, and the Service class is executed after being called by the Controller class.

5. The method according to claim 4, characterized in that, Steps S2 to S9 are implemented using the Service class.

6. The method according to claim 1, characterized in that, in this method, the Mapper class is used to interact with the database. After the Service class passes the verification, the data in the Excel file is inserted into the database.

7. The method according to claim 6, characterized in that, Step S10 is implemented using the Mapper class.

8. A system for implementing the method according to any one of claims 1 to 7.

Images