Excel data import method, device, equipment and storage medium

Abstract

This application discloses an Excel data import method, apparatus, device, and storage medium, relating to the field of data processing technology. The method comprises: obtaining an Excel file and parsing the file to extract attribute information from the file's attribute worksheet; converting the attribute information into custom object properties to obtain attribute objects corresponding to the attribute information, and storing the attribute objects in column order in a global array variable; looping through each row and column of data in each worksheet in the Excel file and validating the attributes of each cell in the Excel file using the global array variable; and, after all data is validated, converting each row of data into a target format object for storage based on the Excel file and the global array variable. This method can improve the efficiency of Java developers in developing Excel data import functions.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to an Excel data import method, device, equipment and storage medium. Background Art

[0002] Currently, importing Excel data from Java often requires pre-defined import templates and independent validation rules for each module. This results in high duplication of acceptance code and the need to hard-code changes to the original code for even the slightest change in module information, resulting in poor adaptability and scalability. This makes it difficult to adapt to ever-changing business scenarios. Summary of the Invention

[0003] In view of this, the purpose of the present invention is to provide an Excel data import method, device, equipment and medium, which can improve the efficiency of Java developers in developing Excel data import functions. The specific scheme is as follows:

[0004] In a first aspect, the present application discloses a method for importing Excel data, comprising:

[0005] Obtaining an Excel file, and parsing the Excel file to extract attribute information from an attribute worksheet of the Excel file;

[0006] Convert the attribute information into custom object attributes to obtain attribute objects corresponding to the attribute information, and store the attribute objects in a global array variable in column order;

[0007] By looping through each row and column of each worksheet in the Excel file in sequence, and performing attribute verification on the data of each cell in the Excel file in combination with the global array variable;

[0008] After all data are verified, each row of data is converted into a target format object for storage based on the Excel file and the global array variable.

[0009] Optionally, the attribute information includes the attribute English name, the attribute Chinese name, the attribute type and the attribute verification rule.

[0010] Optionally, the attribute type includes a custom administrative region; the custom administrative region includes one or more of a name attribute of a string type for identifying the name of the administrative division, a code attribute of a string type for identifying the administrative region code, and an id attribute of a long integer type for identifying the administrative region id attribute;

[0011] The attribute type includes a custom dictionary; the custom dictionary includes one or more of a long integer id attribute for marking the dictionary item id and a string type display name attribute for identifying the Chinese value of the dictionary.

[0012] Optionally, the step of looping through each row and column of data in each worksheet in the Excel file in sequence and performing attribute verification on the data in each cell in the Excel file in combination with the global array variable includes:

[0013] Loop through each row and column of each worksheet in the Excel file in turn, and store each cell data in a two-dimensional array;

[0014] Loop through each row and column of the two-dimensional array, and during the traversal process, obtain a verification object with the same column value from the global array variable according to the column value of the target data currently traversed;

[0015] The verification object is verified using attribute verification rules.

[0016] Optionally, the attribute validation rule includes one or more of a requirement attribute of a Boolean type for identifying whether it can be empty, a type attribute of a string type for identifying the attribute data type, a regular expression attribute of a string type for identifying the regular expression validation rule, maximum and minimum value attributes of a string type for identifying the maximum and minimum values, a format attribute of a string type for identifying the data format, and maximum length and minimum length attributes of a basic integer type for identifying the maximum length and minimum length.

[0017] Optionally, after performing attribute verification on the data of each cell in the Excel file in combination with the global array variable, the method further includes:

[0018] If the verification fails, constructing error prompt information based on the data of the current cell, the verification rule for the current cell, and the attribute information corresponding to the data of the current cell;

[0019] The error prompt information is inserted into the current cell in the form of a comment, and the background color of the current cell is modified.

[0020] Optionally, converting each row of data into a target format object for storage based on the Excel file and according to the global array variable includes:

[0021] Loop through the Excel file and convert each row of data into a target format object according to the global array variable, using each column attribute name as the key of the target format object and the data as the value of the target format object;

[0022] The target format object is stored in the database, and a success prompt is fed back after the storage is successful.

[0023] In a second aspect, the present application discloses an Excel data import device, comprising:

[0024] An attribute information extraction module is used to obtain an Excel file and parse the Excel file to extract attribute information from an attribute worksheet of the Excel file;

[0025] An attribute information conversion module is used to convert the attribute information into a custom object attribute to obtain an attribute object corresponding to the attribute information, and store the attribute object in a global array variable in the order of column numbers;

[0026] A verification module is used to loop through each row and column of each worksheet in the Excel file in sequence, and perform attribute verification on the data of each cell in the Excel file in combination with the global array variable;

[0027] The storage module is used to convert each row of data into a target format object for storage based on the Excel file and the global array variable after all data are verified.

[0028] In a third aspect, the present application discloses an electronic device, comprising:

[0029] Memory, used to store computer programs;

[0030] A processor is used to execute the computer program to implement the aforementioned Excel data import method.

[0031] In a fourth aspect, the present application discloses a computer-readable storage medium for storing a computer program; wherein the computer program implements the aforementioned Excel data import method when executed by a processor.

[0032] In this application, an Excel file is obtained and parsed to extract attribute information from the attribute worksheet of the Excel file; the attribute information is converted into custom object attributes to obtain attribute objects corresponding to the attribute information, and the attribute objects are stored in a global array variable in column order; each row and column of data in each worksheet in the Excel file is looped through in sequence, and the attributes of the data in each cell in the Excel file are verified in combination with the global array variable; after all data are verified, each row of data is converted into a target format object for storage based on the Excel file and the global array variable. It can be seen that the attribute object obtained by converting the attribute information defined in the excel attribute worksheet is stored in the global array variable, and the attribute object is used for subsequent verification, rather than directly performing a one-to-one conversion on the javabean defined in java (a reusable component written in JAVA language, used to expose member properties of internal domains by providing public methods that conform to consistent design patterns). Therefore, when validating data, there is no need to define a validation class for each excel file module. Instead, a set of validation logic can be applied to all excel file modules according to the passed-in validation rules. This can effectively improve the efficiency of java developers in developing excel data import functions, has strong scalability, and the excel import method scheduling is abstract and flexible, enabling low-code rapid construction of excel data import functions. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are merely embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0034] Figure 1 A flow chart of an Excel data import method provided for this application;

[0035] Figure 2 This is a schematic diagram of the structure of an Excel data import device provided in this application;

[0036] Figure 3 This is a structural diagram of an electronic device provided in this application. DETAILED DESCRIPTION

[0037] To make the objectives, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts shall fall within the scope of protection of the present invention.

[0038] In the prior art, importing Excel data from Java often requires pre-defined fixed import template files for each module, along with independent validation rule code for each module. This results in high duplication of acceptance code and the need to hard-code modifications to the original code for even the slightest change in module information, making it difficult to adapt to ever-changing business scenarios. To overcome these technical issues, this application proposes an Excel data import method that improves the efficiency of Java developers in developing Excel data import functions.

[0039] This application embodiment discloses a method for importing Excel data. Figure 1 As shown, the method may include the following steps:

[0040] Step S11: obtaining an Excel file, and parsing the Excel file to extract attribute information from an attribute worksheet of the Excel file.

[0041] In this embodiment, the Excel file uploaded by the user is first obtained, and a corresponding file object is created, and the corresponding file stream is obtained; a file stream is a way to read data from a file or write data to a file. In a file stream, the file is transmitted in the form of a stream and can be read and written in the form of bytes or strings.

[0042] The attribute information includes the English name of the attribute, the Chinese name of the attribute, the attribute type and the attribute validation rules. Specifically, the org.apache.poi technology (ApachePOI, Apache Poor Obfuscation Implementation, a concise version of the fuzzy implementation) is used to parse the Excel file to extract the attribute information from the attribute worksheet of the Excel file. org.apache.poi is a free, open source, cross-platform Java API written in Java. Apache POI provides an API for Java programs to read and write Microsoft Office format files; parse the Excel data to be imported, and specifically obtain all the English names of the attributes, the Chinese names of the attributes, the attribute types, and the attribute validation rules from the attribute worksheet (i.e., the hidden sheet named bean). Attribute types include custom administrative areas (Organization) and custom dictionaries (PropertyDict).

[0043] In this embodiment, the attribute type includes a custom administrative region; the custom administrative region includes one or more of a name attribute of a string type (String) for identifying the name of the administrative region, a code attribute of a string type for identifying the administrative region code, and an id attribute of a long integer type (Long) for identifying the id attribute of the administrative region. That is, the custom administrative region includes a name attribute of a String type for identifying the name of the administrative region, an org_code of a String type for identifying the administrative region code; and an id attribute of a Long type for identifying the id attribute of the administrative region. The attribute type includes a custom dictionary; the custom dictionary includes one or more of an id attribute of a long integer type for marking the id of a dictionary item and a display name attribute of a string type for identifying the Chinese value of the dictionary. That is, PropertyDict includes an id attribute of a Long type for marking the id of a dictionary item; and a displayName attribute of a String type for identifying the Chinese value of the dictionary.

[0044] Step S12: converting the attribute information into custom object attributes to obtain attribute objects corresponding to the attribute information, and storing the attribute objects in a global array variable in column sequence.

[0045] In this embodiment, the attribute information obtained is directly converted into custom object attributes to obtain the attribute object corresponding to the attribute information, and the attribute object is stored in the global array variable in the order of the column sequence. That is, the attribute parameters are converted into custom object attributes: object attributes (AttributeObject), and are placed in the global array variable (attributeArray) in the order of the corresponding column sequence. In this way, the imported data attributes and validation rules are defined in Excel and stored in the array, and correspond to the data, which is more scalable than pre-defining bean objects in the back end. The data is converted into key-value pair objects such as JSONObject, which does not require pre-definition of bean objects, to improve scalability.

[0046] Step S13: looping through each row and column of each worksheet in the Excel file in sequence, and performing attribute verification on the data of each cell in the Excel file in combination with the global array variable.

[0047] In this embodiment, validation is performed by sequentially traversing each row and column of data in the worksheet and performing attribute validation on each cell in the Excel file based on the attribute information stored in the global array variable. This shows that after obtaining the Excel data, this embodiment does not perform a one-to-one conversion based on the Java beans defined in Java. Instead, it converts the data based on the attribute names and attribute types defined in the Excel property page. This difference from the Java bean conversion eliminates the need to define a validation class for each module during data validation. Instead, a single set of validation logic applies to all modules based on the input rules.

[0048] In this embodiment, the method of sequentially looping through each row and column of each worksheet in the Excel file and performing attribute validation on the data of each cell in the Excel file in combination with the global array variable may include: sequentially looping through each row and column of each worksheet in the Excel file and storing each cell data in a two-dimensional array; looping through each row and column of the two-dimensional array and, during the looping process, obtaining a validation object with the same column value from the global array variable based on the column value of the target data currently being traversed; and validating the validation object using attribute validation rules. Specifically, the method obtains the parameter value of the starting row of the data, then obtains data from the starting row of the data of the first worksheet (sheet) using POI technology, loops through each row and column of the data, and stores each cell data in a two-dimensional array object (String[][]) for future use; loops through each row and column of the data in the two-dimensional array; when the corresponding column value is traversed, obtains the validation object with the same column value from the global array variable, and validates the data according to the validation rules defined by the custom validation object.

[0049] The above-mentioned attribute validation rules include one or more of the following: a requirement attribute of a Boolean type for identifying whether it can be empty, a type attribute of a string type for identifying the attribute data type, a regular expression attribute of a string type for identifying the regular expression validation rule, maximum and minimum value attributes of a string type for identifying the maximum and minimum values, a format attribute of a string type for identifying the data format, and maximum length and minimum length attributes of a basic integer type for identifying the maximum length and minimum length. That is, the above-mentioned custom validation type object CustomValidation: contains the required attribute of boolean type, which is used to identify whether it can be empty. The default value is false, indicating that it can be empty, and true indicates that it cannot be empty; it also contains the type attribute of String type, which is used to identify the attribute data type, and can be filled in with Java built-in types and custom types; it also contains the regex attribute of String type, which is used to identify the regular expression validation rule. The default value is empty, and verification is based on this if there is a value; it also contains the maxValue and minValue attributes of String type, which are used to identify the maximum and minimum values, mainly for numeric and date types; it also contains the format attribute of String type, which is used to identify the data format. If it is 00.00 of numeric type, 'yyyy-mm-dd hh24:mi:ss' of date type, etc., the default value is empty; it also contains the minLengh and maxLengh attributes of int type, which are used to identify the maximum length and minimum length. If it is empty, there is no limit.

[0050] Step S14: After all data are verified, each row of data is converted into a target format object for storage based on the Excel file and according to the global array variable.

[0051] In this embodiment, if the data attribute verification of the current cell passes, the verification of the next cell is executed. After all data is verified, or after all data in the current row is verified, each row of data can be converted into a target format object based on the Excel file and the global array variable for storage. The target format object is then stored in the corresponding storage, and a success prompt is returned to the front end after the storage is successful.

[0052] In this embodiment, after the data in each cell of the Excel file is attribute-validated in combination with the global array variable, the following steps may be further included: if the validation fails, an error message is constructed based on the data of the current cell, the validation rules for the current cell, and the attribute information corresponding to the data of the current cell; the error message is inserted into the current cell in the form of a comment, and the background color of the current cell is modified. Specifically, if the data validation fails, the error message is spliced ​​into the corresponding cell in combination with the validation rules, the Chinese name of the validation attribute, and the cell information corresponding to the validation data, and the annotation information is inserted into the corresponding cell while marking the background color of the error cell red and marking it with an error. That is, the information after the validation fails is returned and directly written to the cell corresponding to the original data import file, making the error prompt location more accurate and obvious. If the data validation fails, the data conversion and storage are skipped, the result information is directly returned, and an error data table is provided for download. Specifically, the original data cell can be marked with a red background and an error text mark to make the prompt more accurate, and the data can be modified directly in the error information table and imported again.

[0053] In this embodiment, the process of converting each row of data into a target format object for storage based on the Excel file and the global array variable may include: looping through the Excel file and, based on the global array variable, converting each row of data into a target format object using each column attribute name as the key and the data as the value of the target format object; storing the target format object in the database and providing a successful feedback upon successful storage. Specifically, the data is converted into key-value pairs. Therefore, if a column of data needs to be added or deleted, there is no need to modify the corresponding backend bean object code; only the Excel file needs to be modified. This saves work, is more flexible, and does not require republishing the backend program; only the Excel template file needs to be replaced.

[0054] That is to say, if the data validation passes, loop through all the data, and combine the global array variable (attributeArray) corresponding to the cell attribute name and attribute type to convert each row of data into a target format object. The above target format can be JSON or Map. Taking JSON as an example, the attribute name of each column is used as the key of the JSONObject object, and the data is used as the value of the JSONObject. Each row of data is converted into a JSONObject data. Converting to JSON data is easier to expand than defining bean objects. There is no need to modify the code to add or remove fields. You only need to add a column corresponding to the Excel field attributes and data.

[0055] As can be seen from the above, in this embodiment, an Excel file is obtained, and the Excel file is parsed to extract attribute information from the attribute worksheet of the Excel file; the attribute information is converted into custom object attributes to obtain attribute objects corresponding to the attribute information, and the attribute objects are stored in a global array variable in column order; each row and column of each worksheet in the Excel file is looped through in sequence, and the data of each cell in the Excel file is attribute verified in combination with the global array variable; after all data are verified, each row of data is converted into a target format object for storage based on the Excel file and the global array variable. It can be seen that the attribute object obtained by converting the attribute information defined in the excel attribute worksheet is stored in the global array variable, and the attribute object is used for subsequent verification, rather than directly performing a one-to-one conversion on the javabean defined in java (a reusable component written in JAVA language, used to expose member properties of internal domains by providing public methods that conform to consistent design patterns). Therefore, when validating data, there is no need to define a validation class for each excel file module. Instead, a set of validation logic can be applied to all excel file modules according to the passed-in validation rules. This can effectively improve the efficiency of java developers in developing excel data import functions, has strong scalability, and the excel import method scheduling is abstract and flexible, enabling low-code rapid construction of excel data import functions.

[0056] The present application also discloses an Excel data import device. Figure 2 As shown, the device includes:

[0057] The attribute information extraction module 11 is used to obtain an Excel file and parse the Excel file to extract attribute information from an attribute worksheet of the Excel file;

[0058] The attribute information conversion module 12 is used to convert the attribute information into a custom object attribute to obtain an attribute object corresponding to the attribute information, and store the attribute object in a global array variable in the order of column numbers;

[0059] A verification module 13 is used to loop through each row and column of each worksheet in the Excel file in sequence, and perform attribute verification on the data of each cell in the Excel file in combination with the global array variable;

[0060] The storage module 14 is used to convert each row of data into a target format object for storage based on the Excel file and the global array variable after all data are verified.

[0061] As can be seen from the above, in this embodiment, an Excel file is obtained, and the Excel file is parsed to extract attribute information from the attribute worksheet of the Excel file; the attribute information is converted into custom object attributes to obtain attribute objects corresponding to the attribute information, and the attribute objects are stored in a global array variable in column order; each row and column of each worksheet in the Excel file is looped through in sequence, and the data of each cell in the Excel file is attribute verified in combination with the global array variable; after all data are verified, each row of data is converted into a target format object for storage based on the Excel file and the global array variable. It can be seen that the attribute object obtained by converting the attribute information defined in the excel attribute worksheet is stored in the global array variable, and the attribute object is used for subsequent verification, rather than directly performing a one-to-one conversion on the javabean defined in java (a reusable component written in JAVA language, used to expose member properties of internal domains by providing public methods that conform to consistent design patterns). Therefore, when validating data, there is no need to define a validation class for each excel file module. Instead, a set of validation logic can be applied to all excel file modules according to the passed-in validation rules. This can effectively improve the efficiency of java developers in developing excel data import functions, has strong scalability, and the excel import method scheduling is abstract and flexible, enabling low-code rapid construction of excel data import functions.

[0062] In some specific embodiments, the attribute information may specifically include the attribute English name, the attribute Chinese name, the attribute type, and the attribute validation rule.

[0063] In some specific embodiments, the attribute type may specifically include a custom administrative region; the custom administrative region includes one or more of a name attribute of a string type for identifying the name of the administrative division, a code attribute of a string type for identifying the administrative region code, and an id attribute of a long integer type for identifying the id attribute of the administrative region; the attribute type may specifically include a custom dictionary; the custom dictionary includes one or more of an id attribute of a long integer type for marking the id of a dictionary item and a display name attribute of a string type for identifying the Chinese value of the dictionary.

[0064] In some specific embodiments, the verification module 13 may specifically include:

[0065] A two-dimensional array construction unit is used to loop through each row and column of data in each worksheet in the Excel file in sequence and store each cell data in a two-dimensional array;

[0066] A verification object acquisition unit is used to loop through each row and column of the two-dimensional array, and during the traversal process, obtain a verification object with the same column value from the global array variable according to the column value of the target data currently traversed;

[0067] The verification unit is used to verify the verification object using attribute verification rules.

[0068] In some specific embodiments, the attribute validation rules may specifically include a requirement attribute of a Boolean type for identifying whether it can be empty, a type attribute of a string type for identifying the attribute data type, a regular expression attribute of a string type for identifying the regular expression validation rule, maximum and minimum value attributes of a string type for identifying the maximum and minimum values, a format attribute of a string type for identifying the data format, and one or more of the maximum length and minimum length attributes of a basic integer type for identifying the maximum length and minimum length.

[0069] In some specific embodiments, the Excel data import device may specifically include:

[0070] an error prompt information construction unit, configured to perform attribute verification on the data of each cell in the Excel file in combination with the global array variable, and construct error prompt information based on the data of the current cell, the verification rule for the current cell, and the attribute information corresponding to the data of the current cell if the verification fails;

[0071] The annotation unit is used to insert the error prompt information into the current cell in the form of an annotation and modify the background color of the current cell.

[0072] In some specific embodiments, the storage module 14 may specifically include:

[0073] A data conversion unit, configured to loop through the Excel file and convert each row of data into a target format object according to the global array variable, using each column attribute name as a key of the target format object and the data as a value of the target format object;

[0074] The storage unit is used to store the target format object into a database and provide a success prompt after the storage is successful.

[0075] Furthermore, the present application also discloses an electronic device, see Figure 3 The contents in the drawings should not be considered as any limitation on the scope of use of the present application.

[0076] Figure 3This is a schematic diagram of the structure of an electronic device 20 provided in an embodiment of the present application. The electronic device 20 may include: at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input / output interface 25, and a communication bus 26. The memory 22 is used to store a computer program, which is loaded and executed by the processor 21 to implement the relevant steps of the Excel data import method disclosed in any of the aforementioned embodiments.

[0077] In this embodiment, the power supply 23 is used to provide operating voltage for each hardware device on the electronic device 20; the communication interface 24 can create a data transmission channel between the electronic device 20 and the external device. The communication protocol it follows is any communication protocol that can be applied to the technical solution of this application and is not specifically limited here; the input and output interface 25 is used to obtain external input data or output data to the outside world. Its specific interface type can be selected according to specific application needs and is not specifically limited here.

[0078] In addition, the memory 22, as a carrier for resource storage, can be a read-only memory, random access memory, disk or CD, etc. The resources stored thereon include an operating system 221, a computer program 222 and data 223 including attribute information, etc. The storage method can be temporary storage or permanent storage.

[0079] The operating system 221 is used to manage and control the hardware devices and computer program 222 on the electronic device 20, so as to enable the processor 21 to calculate and process the massive amount of data 223 in the memory 22. The operating system 221 can be Windows Server, NetWare, Unix, Linux, etc. In addition to including a computer program capable of implementing the Excel data import method executed by the electronic device 20 as disclosed in any of the aforementioned embodiments, the computer program 222 can further include computer programs capable of performing other specific tasks.

[0080] Furthermore, an embodiment of the present application also discloses a computer storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are loaded and executed by a processor, the Excel data import method steps disclosed in any of the aforementioned embodiments are implemented.

[0081] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from the other embodiments. Reference can be made to the descriptions of the identical or similar parts between the various embodiments. For the devices disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the descriptions are relatively simple, and the relevant parts can be referred to the descriptions of the methods.

[0082] The steps of the methods or algorithms described in conjunction with the embodiments disclosed herein may be implemented directly using hardware, a software module executed by a processor, or a combination of the two. The software module may be placed in a random access memory (RAM), internal memory, read-only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.

[0083] Finally, it should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of additional identical elements in the process, method, article, or device comprising the element.

[0084] The above describes in detail the Excel data import method, device, equipment, and medium provided by the present invention. Specific examples are used herein to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only intended to help understand the method and core concept of the present invention. At the same time, for those skilled in the art, according to the concept of the present invention, there may be changes in the specific implementation methods and application scope. In summary, the contents of this specification should not be understood as limiting the present invention.

Claims

1. A method for importing Excel data, characterized in that: include: Obtaining an Excel file, and parsing the Excel file to extract attribute information from an attribute worksheet of the Excel file, the attribute information including an attribute English name, an attribute Chinese name, an attribute type, and an attribute validation rule, wherein the attribute type includes a custom administrative region, the custom administrative region including one or more of a name attribute of a string type for identifying an administrative division name, a code attribute of a string type for identifying an administrative region code, and an id attribute of a long integer type for identifying an administrative region id attribute; the attribute type includes a custom dictionary, the custom dictionary including one or more of an id attribute of a long integer type for marking a dictionary item id and a display name attribute of a string type for identifying a dictionary Chinese value; Convert the attribute information into custom object attributes to obtain attribute objects corresponding to the attribute information, and store the attribute objects in a global array variable in column order; By looping through each row and column of each worksheet in the Excel file in sequence, and combining the global array variable to perform attribute verification on the data of each cell in the Excel file, specifically, looping through each row and column of each worksheet in the Excel file in sequence, and storing each cell data in a two-dimensional array, looping through each row and column of the two-dimensional array, and during the traversal process, obtaining a verification object with the same column value from the global array variable according to the column value of the currently traversed target data, and verifying the verification object using the attribute verification rule; After all data are verified, each row of data is converted into a target format object for storage based on the Excel file and the global array variable.

2. The Excel data import method according to claim 1, characterized in that: The attribute validation rule includes one or more of a requirement attribute of a Boolean type for identifying whether it can be empty, a type attribute of a string type for identifying the attribute data type, a regular expression attribute of a string type for identifying the regular expression validation rule, maximum and minimum value attributes of a string type for identifying the maximum and minimum values, a format attribute of a string type for identifying the data format, and maximum length and minimum length attributes of a basic integer type for identifying the maximum length and minimum length.

3. The Excel data import method according to claim 1, characterized in that: After verifying the properties of the data in each cell of the Excel file in combination with the global array variable, the method further includes: If the verification fails, constructing error prompt information based on the data of the current cell, the verification rule for the current cell, and the attribute information corresponding to the data of the current cell; The error prompt information is inserted into the current cell in the form of a comment, and the background color of the current cell is modified.

4. The Excel data import method according to claim 1, characterized in that: The converting each row of data into a target format object for storage based on the Excel file and according to the global array variable includes: Loop through the Excel file and convert each row of data into a target format object according to the global array variable, using each column attribute name as the key of the target format object and the data as the value of the target format object; The target format object is stored in the database, and a success prompt is fed back after the storage is successful.

5. An Excel data import device, characterized in that: include: an attribute information extraction module, configured to obtain an Excel file and parse the Excel file to extract attribute information from an attribute worksheet of the Excel file, wherein the attribute information includes an attribute English name, an attribute Chinese name, an attribute type, and an attribute validation rule, wherein the attribute type includes a custom administrative region, wherein the custom administrative region includes one or more of a name attribute of a string type for identifying an administrative division name, a code attribute of a string type for identifying an administrative region code, and an id attribute of a long integer type for identifying an administrative region id attribute; and wherein the attribute type includes a custom dictionary, wherein the custom dictionary includes one or more of an id attribute of a long integer type for marking a dictionary item id and a display name attribute of a string type for identifying a dictionary Chinese value; An attribute information conversion module is used to convert the attribute information into a custom object attribute to obtain an attribute object corresponding to the attribute information, and store the attribute object in a global array variable in the order of column numbers; a verification module, configured to sequentially loop through each row and column of data in each worksheet in the Excel file, and perform attribute verification on the data in each cell in the Excel file in combination with the global array variable; specifically, loop through each row and column of data in each worksheet in the Excel file, store each cell data in a two-dimensional array, loop through each row and column of data in the two-dimensional array, and during the traversal process, obtain a verification object with the same column value from the global array variable according to the column value of the currently traversed target data, and verify the verification object using attribute verification rules; The storage module is used to convert each row of data into a target format object for storage based on the Excel file and the global array variable after all data are verified.

6. An electronic device, characterized in that: include: Memory, used to store computer programs; A processor, configured to execute the computer program to implement the Excel data import method according to any one of claims 1 to 4.

7. A computer-readable storage medium, characterized in that Used to store a computer program; wherein when the computer program is executed by a processor, the Excel data import method according to any one of claims 1 to 4 is implemented.

Images