Excel file dynamic analysis and verification method and system based on rule template and storage medium

Abstract

The invention belongs to the technical field of data processing, and discloses an Excel file dynamic analysis and verification method and system based on a rule template and a storage medium. Comprising the steps that a rule template is generated according to configuration information, and the rule template comprises one piece of table unit data and one or more pieces of column metadata; dynamically creating a database business data table for storing the analyzed Excel data based on the column metadata; reading the rule template, obtaining a corresponding form in the to-be-analyzed Excel file according to the table unit data, extracting Excel data in each row and each column in the form according to the column metadata, and storing the Excel data in a business data table; and querying a service data row in the database service data table, obtaining a verification rule according to the column metadata corresponding to the Excel data of each column in the service data row, and verifying the current Excel data according to the verification rule. According to the method, the adaptation capability of verification is enhanced, and meanwhile, customized development cost is not generated, so that the analysis and verification of the Excel are more flexible.

Description

Technical Field

[0001] The present invention belongs to the technical field of data processing, and in particular relates to a method, system and storage medium for dynamically parsing and verifying Excel (Microsoft Excel, electronic spreadsheet) files based on rule templates. Background Art

[0002] Large enterprises and government agencies often need to collect and report various types of Excel-based statistical data. This type of data has the characteristics of large total data volume, diverse statistical calibers, non-fixed data formats, and inability to effectively constrain the filling format. Although conventional web programs have good structured data collection capabilities, they can only use Excel import methods for large-scale statistical data collection, which brings great difficulties to the structured extraction and aggregation, standardized storage and global analysis of data.

[0003] Although the existing Excel parsing technology can extract and verify data, it is powerless in the face of complex verification tasks such as dependency verification between different cells, verification of administrative division standard names / codes, verification of complex address rules, etc. Therefore, it is necessary to enhance the adaptability of the verification without incurring customized development costs, so as to make Excel parsing and verification more flexible. Summary of the invention

[0004] One of the purposes of the present invention is to provide a method for dynamic parsing and verification of Excel files based on rule templates, which enhances the adaptability of verification and does not incur customized development costs, making the parsing and verification of Excel files more flexible.

[0005] To achieve the above object, the technical solution adopted by the present invention is: A method for dynamically parsing and verifying an Excel file based on a rule template, comprising: Generate a rule template according to the configuration information and save it to the database. The rule template includes a form metadata and one or more column metadata. The form metadata includes the form name, form index number, number of header rows, version number and whether it is valid. The column metadata includes the column name, column index number, data type, whether it is not empty, whether it is a primary key, enumeration value domain, verification script and database record ID corresponding to the form metadata. Dynamically create a database business data table for storing the parsed Excel data based on column metadata; Read the rule template, obtain the corresponding form in the Excel file to be parsed according to the form metadata, then extract the Excel data of each row and column in the form according to the column metadata, and save it to the database business data table; The business data rows in the business data table of the database are traversed and queried, and verification rules are obtained according to the column metadata corresponding to the Excel data in each column of the business data row, and the current Excel data is verified according to the verification rules, wherein the verification rules include data type verification, non-empty verification, uniqueness verification, enumeration value domain verification and verification script verification, among which non-empty verification is a necessary verification rule, and the rest are optional verification rules.

[0006] Several optional methods are also provided below, but they are not intended to be additional limitations on the above-mentioned overall solution, but are merely further supplements or preferences. Under the premise that there are no technical or logical contradictions, each optional method can be combined with the above-mentioned overall solution separately, and multiple optional methods can also be combined.

[0007] Preferably, the form index number stores one or more values, and the formats of the forms corresponding to the multiple values ​​are the same.

[0008] Preferably, the table fields of the database business data table include a database record ID corresponding to the form metadata and column fields having a number consistent with the column metadata, and the column fields adopt a variable-length TEXT data type.

[0009] Preferably, during verification, a full verification is performed in the order of non-empty verification, uniqueness verification, data type verification, enumeration value range verification, and verification script verification.

[0010] The second purpose of the present invention is to provide an Excel file dynamic parsing and verification system based on rule templates, which enhances the adaptability of the verification and does not incur customized development costs, making the parsing and verification of Excel files more flexible.

[0011] To achieve the above object, the technical solution adopted by the present invention is: A dynamic analysis and verification system for Excel files based on rule templates, comprising: A rule template configuration generation module is used to generate a rule template according to the configuration information and save it to the database. The rule template includes a form metadata and one or more column metadata. The form metadata includes the form name, form index number, number of header rows, version number and whether it is valid. The column metadata includes the column name, column index number, data type, whether it is not empty, whether it is a primary key, enumeration value domain, verification script and record ID of the corresponding form metadata; and dynamically create a database business data table for storing the parsed Excel data based on the column metadata; The parsing engine module is used to read the rule template, obtain the corresponding form in the Excel file to be parsed according to the form metadata, and then extract the Excel data of each row and column in the form according to the column metadata, and save it to the database business data table; The verification engine module is used to traverse and query the business data rows in the business data table of the database, obtain the verification rules according to the column metadata corresponding to the Excel data in each column of the business data row, and verify the current Excel data according to the verification rules. The verification rules include data type verification, non-empty verification, uniqueness verification, enumeration value domain verification and verification script verification, among which non-empty verification is a necessary verification rule, and the rest are optional verification rules.

[0012] Preferably, the form index number stores one or more values, and the formats of the forms corresponding to the multiple values ​​are the same.

[0013] Preferably, the table fields of the database business data table include a database record ID corresponding to the form metadata and column fields having a number consistent with the column metadata, and the column fields adopt a variable-length TEXT data type.

[0014] Preferably, the Excel dynamic parsing and verification system based on rule templates further includes a rendering engine module, and the rendering engine module is used to dynamically render a two-dimensional table and its columns according to the rule template on the front-end page to display the Excel data obtained by parsing.

[0015] Preferably, the Excel dynamic parsing and verification system based on rule templates further includes a storage engine module, which includes a form metadata table, a column metadata table and a database business data table, the form metadata table is used to store form metadata, the column metadata table is used to store column metadata, and the database business data table is used to store Excel data.

[0016] A third object of the present invention is to provide a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the steps of the Excel file dynamic parsing and verification method based on a rule template are implemented.

[0017] The Excel file dynamic parsing and verification method, system and storage medium based on rule template provided by the present invention have the following beneficial effects compared with the prior art: The present invention configures the rule template once to repeatedly extract and verify Excel table data of corresponding formats, and different Excel tables can be configured with different rule templates; the cell data verification method of the present invention is universal and extensible, and can adapt to complex business logic verification between fields and simple isolated field format verification; the use of the present invention avoids the system-level customization development cost when aggregating and processing complex and diverse Excel tables, so that non-professionals can easily collect standardized Excel data. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] Figure 1 It is a flow chart of a method for dynamic analysis and verification of Excel files based on rule templates of the present invention; Figure 2 The present invention is a block diagram of a rule-based template-based Excel file dynamic parsing and verification system. DETAILED DESCRIPTION

[0019] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions 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 creative work are within the scope of protection of the present invention.

[0020] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art of the present invention. The terms used in the specification of the present invention herein are only for the purpose of describing specific embodiments and are not intended to limit the present invention.

[0021] Example 1 In response to a series of problems in the existing Excel file reporting process, this embodiment provides a set of low-code, lightweight, configurable data reporting, collection and processing methods for Excel data sources, with the collection and management of "fragmented data" as the core, to promote the continuous accumulation of data. Through the dynamic rule template configuration of ordinary business personnel, various forms of Excel statistical data files are adapted and the user's reporting behavior is constrained, and the needs of a large number of data table classes are converted into data applications, which improves the processing efficiency of data collection and aggregation by data collectors. Compared with the existing applications based on programmatic parsing of Excel files, a large amount of code-level custom development is avoided, and the ability to programmatically process Excel files is extended to ordinary business personnel, further saving the R&D expenses generated by the need for continuous customization by enterprises. Compared with the manual processing of Excel file reporting and aggregation work, the templated rule constraints make the multi-source collected data more standardized, avoiding the time-consuming and labor-intensive back and forth modification caused by various data format problems.

[0022] like Figure 1 As shown, the Excel file dynamic parsing and verification method based on the rule template provided in this embodiment includes the following steps: Step S1: Generate form metadata in a rule template according to configuration information.

[0023] In order to reduce the difficulty of dynamic parsing and verification of Excel files and extend the ability of programmatic processing of Excel files to ordinary business personnel, this embodiment supports rule template configuration. After manual configuration, the form metadata used for Excel file parsing is saved to the database. The form metadata is configured according to the form (Sheet) characteristics of the Excel file to be parsed, including the form name, form index number, number of header rows, version number and whether it is valid, where:

[0024] Sheet name: represents the specific Excel file business category; Sheet index number: represents the Excel file sheet page that needs to be parsed, one or more; Number of header rows: represents the number of rows occupied by the header in the Excel file, which is used to ignore rows when actually parsing data; Version number: a version identification number used to identify different parsing rules for the same type of form; Validity: Indicates the validity of the current rule template. Only valid templates can be used to specify or automatically process Excel files. The option value is "Yes" or "No".

[0025] It is easy to understand that the above fields included in the form metadata are only one way of illustrating in this embodiment. In other embodiments, they can be added or deleted according to actual needs.

[0026] The form metadata needs to be saved in the form metadata table in the database. To efficiently process Excel files with multiple forms, the form index number supports configuring multiple forms at once, that is, the form index number can store one or more values. The formats of the forms corresponding to multiple values ​​are the same, and multiple values ​​are separated by Chinese or English comma separators, such as 1,2,3 or 1,2,3. The Excel file to be parsed is a fixed column and row batch processing data structure.

[0027] Step S2: Generate column metadata in the rule template according to the configuration information.

[0028] A rule template contains a form metadata and one or more column metadata. The column metadata is subordinate to the form metadata and corresponds to the form in the Excel file and the columns in the form. Configure the column metadata of each column under the specified form metadata, including the column name, column index number, data type, whether it is non-empty, whether it is a primary key, enumeration value range and verification script, and associate the database record corresponding to the form metadata. Among them:

[0029] Column name: describes the actual business meaning of the cell data; Column index number: represents the column number where the data cell is located; Data type: describes the data type of the cell data, including integer, floating point, string and time; Is it not empty: constrain whether the cell is a required item; Is it a primary key: constrains whether the value of this cell is used as the unique field of the row data; Enumeration range: constrains the data value range of a cell, expressed as a string separated by Chinese and English commas; Verification script: When the above data type verification, non-empty verification, uniqueness verification and enumeration threshold verification cannot be met, additional verification scripts are used to verify complex business rules, such as address verification, dependency verification of previous and next cells, standard administrative division name and coding verification, etc. The verification code can be a reference to existing code or a custom code, and a custom script code template is preset for quick configuration. The verification code is expressed based on the page string representation, and the exec code execution engine of the high-level programming language is called to implement the execution of the verification code. The writing rules of custom verification scripts comply with the high-level programming language syntax implemented by the system. Context variables can be referenced, such as the current row object variable, to obtain the data object variable of each cell. Some common data objects such as administrative division standard name / code, enterprise unified credit code, etc. can also be pre-queried in the context and the data object variable can be exposed to the verification script for reference, or directly queried and obtained by writing code in the verification script. The verification script is written in the form of a string text and saved to the database for use. When traversing each row and column of Excel data during verification, if it is determined that a verification script exists in the column metadata corresponding to the current column, the verification script is directly compiled and executed to obtain the verification result output.

[0030] The column name, column index, and whether it is not empty are required information. After the column metadata is configured, it is saved to the column metadata table of the database. In addition to the above attributes, the column metadata table must also be associated with the database record ID of the corresponding form metadata, forming a one-to-many relationship between the form and the column.

[0031] Step S3: dynamically create a database business data table for storing the parsed Excel data based on the column metadata.

[0032] When the rule template is created, a database business data table for storing actual Excel data is automatically created. The table fields include the database record ID of the form metadata and column fields for storing column data that are consistent with the number of column metadata. The column fields use the variable-length TEXT data type to ensure the successful storage of cell data of various categories and lengths. The column field names use the FIELD+column index number as the naming rule, such as FIELD(1)...FIELD(n), where n is the maximum value of the column index number.

[0033] Step S4, read the rule template, obtain the corresponding form in the Excel file to be parsed according to the form metadata, then extract the Excel data of each row and column in the form according to the column metadata, and save it to the database business data table.

[0034] After obtaining the Excel file to be parsed, match the corresponding rule template, obtain the specified Sheet form object in the Excel file to be parsed according to the form index number, and then extract all cell data by row and column number according to the column index number in the column metadata, and save it directly to the database business data table. In order to extract data more efficiently, multiple form index numbers can be configured at the same time using Chinese and English comma separators. After the background separates each index according to Chinese and English comma separators, it traverses and processes all specified Sheet pages in an Excel file.

[0035] Step S5, traverse the business data rows in the business data table of the query database, obtain the verification rules according to the column metadata corresponding to the Excel data in each column of the business data row, and verify the current Excel data according to the verification rules. The verification rules of this embodiment include data type verification, non-empty verification, uniqueness verification, enumeration value domain verification and verification of custom verification scripts, among which non-empty verification is a necessary verification rule, and the rest are optional verification rules.

[0036] After the data parsing is completed, a new thread is started to perform the verification task, query all business data rows, traverse the column metadata and business data rows, and expose the current row data object and cell data object. The exposed variables are used for reference in custom scripts to match the data of each column in each row for sequential verification.

[0037] The purpose of starting a new thread to process the validation task is to ensure that the main thread is not blocked in the case of large amounts of data. After starting a new thread, all data rows are traversed row by row, and then the validation rules are obtained according to the record ID query of the form metadata. Each cell data is traversed according to the column metadata to match its validation rules for validation operations. If the data type, whether it is not empty, whether it is a primary key, the enumeration value domain, and the validation script value contained in the column metadata are yes or not empty, the corresponding validation is performed. For example, if the value of the primary key is yes, a uniqueness check is performed. The validation process is fully validated in the order of non-empty validation, uniqueness validation, data type validation, enumeration domain validation, and custom validation script validation. All five types of validation are optional except for the null validation. If the first four types of validation cannot be met by simple configuration, a custom validation script can be used to complete complex validation purposes.

[0038] The exposed variables can be referenced in the custom validation code, such as the current traversal data row object row, the current traversal column metadata object col, row and col are json objects of key-value structure, and the cell data of a row and a column and its validation rules can be expressed by statements such as row[col["COL_CODE"], col["CHECK_RULE"], col[" COL_DTYPE"]. For complex validation, for example, the value of column A needs to be equal to the sum of column B + column C + column D, then the validation script can be customized as if row["A"]== row["B"]+ row["C"]+ row["D"] to complete the validation task. When verifying the dependency relationship between different cells, the dependency relationship formula is established based on the dependency relationship. For another example, if the name of column A needs to conform to the standard administrative division name, the administrative division object array obtained from the database dynamic query can be additionally exposed for comparison. For example, the address in column B needs to comply with the address rules. The address rules are expressed through a custom validation script, such as checking whether the address contains a specific administrative division name, or verifying whether the address format complies with a specific structure (such as province-city-district / county-street). The custom validation script is a page string that calls the exec function of a high-level programming language to dynamically execute simple string codes. The validation task of all cell data is completed under the two-layer traversal loop processing of data rows and column metadata. After the validation is completed, the validation success or failure flag and the failure reason are updated to the corresponding database record.

[0039] Example 2 like Figure 2 As shown, the Excel file dynamic parsing and verification system based on rule templates provided in this embodiment includes a rule template configuration generation module, a parsing engine module, and a verification engine module, wherein: (1) A rule template configuration generation module is used to generate a rule template according to the configuration information and dynamically create a database business data table for storing the parsed Excel data based on the column metadata.

[0040] First, the rule template configuration generation module generates the form metadata in the rule template according to the configuration information. In order to reduce the difficulty of dynamic parsing and verification of Excel files and extend the ability of programmatic processing of Excel files to ordinary business personnel, this embodiment supports rule template configuration. After manual configuration, the form metadata used for Excel file parsing is saved to the database. The form metadata is configured according to the form (Sheet) characteristics of the Excel to be parsed, including the form name, form index number, number of header rows, version number and whether it is valid, where:

[0041] Sheet name: represents the specific Excel file business category; Sheet index number: represents the Excel file sheet page that needs to be parsed, one or more; Number of header rows: represents the number of rows occupied by the header in Excel, which is used to ignore rows when actually parsing data; Version number: a version identification number used to identify different parsing rules for the same type of form; Validity: Indicates the validity of the current rule template. Only valid templates can be used to specify or automatically process Excel files. The option value is "Yes" or "No".

[0042] It is easy to understand that the above fields included in the form metadata are only one way of illustrating in this embodiment. In other embodiments, they can be added or deleted according to actual needs.

[0043] The form metadata needs to be saved in the form metadata table in the database. To efficiently process Excel files with multiple forms, the form index number supports configuring multiple forms at once, that is, the form index number can store one or more values. The formats of the forms corresponding to multiple values ​​are the same, and multiple values ​​are separated by Chinese or English comma separators, such as 1,2,3 or 1,2,3. The Excel file to be parsed is a fixed column and row batch processing data structure.

[0044] Then, the rule template configuration generation module generates the column metadata in the rule template according to the configuration information. A rule template contains a form metadata and one or more column metadata. The column metadata is subordinate to the form metadata and corresponds to the form in the Excel file and the columns in the form respectively. Configure the column metadata of each column under the specified form metadata, including the column name, column index number, data type, whether it is non-empty, whether it is a primary key, enumeration value domain and verification script, and associate the database record corresponding to the form metadata. Among them:

[0045] Column name: describes the actual business meaning of the cell data; Column index number: represents the column number where the data cell is located; Data type: describes the data type of the cell data, including integer, floating point, string and time; Is it not empty: constrain whether the cell is a required item; Is it a primary key: constrains whether the value of this cell is used as the unique field of the row data; Enumeration range: constrains the data value range of a cell, expressed as a string separated by Chinese and English commas; Verification script: When the above data type verification, non-empty verification, uniqueness verification and enumeration threshold verification cannot be met, additional verification scripts are used to verify complex business rules, such as address verification, dependency verification of previous and next cells, standard administrative division name and coding verification, etc. The verification code can be a reference to existing code or a custom code, and a custom script code template is preset for quick configuration. The verification code is expressed based on the page string representation, and the exec code execution engine of the high-level programming language is called to implement the execution of the verification code. The writing rules of custom verification scripts comply with the high-level programming language syntax implemented by the system. Context variables can be referenced, such as the current row object variable, to obtain the data object variable of each cell. Some common data objects such as administrative division standard name / code, enterprise unified credit code, etc. can also be pre-queried in the context and the data object variable can be exposed to the verification script for reference, or directly queried and obtained by writing code in the verification script. The verification script is written in the form of a string text and saved to the database for use. When traversing each row and column of Excel data during verification, if it is determined that a verification script exists in the column metadata corresponding to the current column, the verification script is directly compiled and executed to obtain the verification result output.

[0046] The column name, column index, and whether it is not empty are required information. After the column metadata is configured, it is saved to the column metadata table of the database. In addition to the above attributes, the column metadata table must also be associated with the database record ID of the corresponding form metadata, forming a one-to-many relationship between the form and the column.

[0047] Finally, the rule template configuration generation module dynamically creates a business data table for storing the parsed Excel data based on the column metadata. When the rule template is created, a business data table for storing the actual Excel data is automatically created. The table fields include the database record ID of the form metadata and the column fields for storing the column data that are consistent with the number of column metadata. The column fields use the variable-length TEXT data type to ensure the successful storage of cell data of various categories and lengths. The column field names use the FIELD+column index number as the naming rule, such as FIELD(1)...FIELD(n), where n is the maximum value of the column index number.

[0048] (2) The parsing engine module is used to read the rule template, obtain the corresponding form in the Excel file to be parsed according to the form metadata, and then extract the Excel data of each row and column in the form according to the column metadata and save it to the database business data table.

[0049] After the Excel file to be parsed is uploaded to the system, the parsing engine module obtains the Excel file to be parsed and matches the corresponding rule template. It obtains the Sheet form object specified in the Excel file to be parsed according to the form index number, and then extracts all cell data by row and column number according to the column index number in the column metadata, and saves it directly to the database business data table. In order to extract data more efficiently, multiple form index numbers can be configured at the same time using Chinese and English comma separators. After the background separates each index according to the Chinese and English comma separators, it traverses and processes all specified Sheet form pages in an Excel file.

[0050] (3) A verification engine module, which is used to traverse the business data rows in the business data table of the query database, and then traverse each column of each row, obtain the column metadata corresponding to the Excel data of each column to obtain the verification rules, and verify the current Excel data (that is, the data of the current cell) according to the verification rules. The verification rules of this embodiment include data type verification, non-empty verification, uniqueness verification, enumeration value domain verification and verification of custom verification scripts, among which non-empty verification is a necessary verification rule, and the rest are optional verification rules.

[0051] In order to handle the verification of millions of cell data, after the system completes the database saving of Excel records, the verification engine module creates an independent data verification thread for asynchronous processing of verification tasks.

[0052] After the data is parsed, a new thread is started to perform the verification task, query all business data rows, traverse the column metadata and business data rows, and expose the current row data object and cell data object at the same time. The exposed variables are used for reference in custom scripts to match the data in each column of each row for sequential verification. During verification, the verification thread first queries each cell data stored in the database into memory, traverses the cell data to the verification engine, and the verification engine performs full verification in sequence according to the cell verification rules in the order of non-empty verification, uniqueness verification, data type verification, enumeration value domain verification and verification script verification.

[0053] Opening a new thread to process the verification task is to ensure that the main thread is not blocked in the case of large data volume. Except for the null check, all of the five types of verification are optional. When the first four types of verification cannot be met by simple configuration, complex verification purposes can be completed by customizing the verification code. The system can have a built-in preset custom script code template for quick configuration of custom verification code.

[0054] In the custom validation code, you can obtain the current row / column data variables exposed by the validation engine, such as the current traversal data row object row, the current traversal column metadata object col, row and col are json objects of key-value structure, and the cell data and validation rules of a row and column can be expressed by row[col["COL_CODE"], col["CHECK_RULE"], col[" COL_DTYPE"] and other statements. For complex validation, for example, the value of column A needs to be equal to the sum of column B + column C + column D, then you can customize the validation script as if row["A"]== row["B"]+ row["C"]+ row["D"] to complete the validation task. For another example, if the name of column A needs to conform to the standard administrative division name, you can use the validation engine to additionally expose the administrative division object array obtained from the database dynamic query for comparison. The custom validation script is to call the exec and other functions of the high-level programming language separately in the form of a page string to dynamically execute simple string codes. The validation task of all cell data is completed under the two-layer traversal loop processing of data rows and column metadata. After the verification is completed, the verification success or failure flag and the reason for failure will be updated to the corresponding database record.

[0055] The system of this embodiment further introduces a storage engine module to provide a database storage function for storing form metadata tables, column metadata tables and business data tables. The form metadata table is used to store form metadata, and the column metadata table is used to store column metadata to implement the storage of rule templates and business data.

[0056] In another specific embodiment, the Excel dynamic parsing and verification system based on the rule template also includes a rendering engine module, which is used to dynamically render the two-dimensional data table column display on the front-end page according to the rule template for the Excel data obtained by parsing. The rendering engine module renders and displays the parsed and verified data according to the form metadata and column metadata in the rule template and provides for query. The specific rendering means is to construct the table column HTML code by dynamically querying the column configuration information based on the existing HTML two-dimensional table, and the specific construction method is not repeated in this embodiment.

[0057] Example 3 This embodiment provides a computer-readable storage medium on which a computer program is stored, characterized in that when the computer program is executed by a processor, the steps of the Excel file dynamic parsing and verification method based on a rule template described in Example 1 are implemented.

[0058] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0059] The technical features of the above-described embodiments may be arbitrarily combined. To make the description concise, not all possible combinations of the technical features in the above-described embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0060] The above-mentioned embodiments only express several implementation modes of the present invention, and the description thereof is relatively specific and detailed, but it cannot be understood as limiting the scope of the invention. It should be pointed out that, for a person of ordinary skill in the art, several modifications and improvements can be made without departing from the concept of the present invention, and these all belong to the protection scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the attached claims.

Claims

1. A method for dynamic parsing and verification of Excel files based on rule templates, characterized in that: The Excel file dynamic parsing and verification method based on rule template includes: Generate a rule template according to the configuration information and save it to the database. The rule template includes a form metadata and one or more column metadata. The form metadata includes the form name, form index number, number of header rows, version number and whether it is valid. The column metadata includes the column name, column index number, data type, whether it is not empty, whether it is a primary key, enumeration value domain, verification script and database record ID corresponding to the form metadata. Dynamically create a database business data table for storing the parsed Excel data based on column metadata; Read the rule template, obtain the corresponding form in the Excel file to be parsed according to the form metadata, then extract the Excel data of each row and column in the form according to the column metadata, and save it to the database business data table; The business data rows in the business data table of the database are traversed and queried, and verification rules are obtained according to the column metadata corresponding to the Excel data in each column of the business data row, and the current Excel data is verified according to the verification rules, wherein the verification rules include data type verification, non-empty verification, uniqueness verification, enumeration value domain verification and verification script verification, among which non-empty verification is a necessary verification rule, and the rest are optional verification rules.

2. The Excel file dynamic parsing and verification method based on rule template according to claim 1 is characterized in that: The form index number stores one or more values, and the formats of the forms corresponding to the multiple values ​​are the same.

3. The Excel file dynamic parsing and verification method based on rule template according to claim 1 is characterized in that: The table fields of the database business data table include a database record ID corresponding to the form metadata and column fields consistent with the number of column metadata, and the column fields adopt a variable-length TEXT data type.

4. The Excel file dynamic parsing and verification method based on rule template according to claim 1 is characterized in that: During verification, a full verification is performed in the order of non-empty verification, uniqueness verification, data type verification, enumeration value range verification, and verification script verification.

5. A dynamic analysis and verification system for Excel files based on rule templates, characterized in that: The Excel file dynamic parsing and verification system based on rule templates includes: A rule template configuration generation module is used to generate a rule template according to the configuration information and save it to the database. The rule template includes a form metadata and one or more column metadata. The form metadata includes the form name, form index number, number of header rows, version number and whether it is valid. The column metadata includes the column name, column index number, data type, whether it is not empty, whether it is a primary key, enumeration value domain, verification script and record ID of the corresponding form metadata; and dynamically create a database business data table for storing the parsed Excel data based on the column metadata; The parsing engine module is used to read the rule template, obtain the corresponding form in the Excel file to be parsed according to the form metadata, and then extract the Excel data of each row and column in the form according to the column metadata, and save it to the database business data table; The verification engine module is used to traverse and query the business data rows in the business data table of the database, obtain the verification rules according to the column metadata corresponding to the Excel data in each column of the business data row, and verify the current Excel data according to the verification rules. The verification rules include data type verification, non-empty verification, uniqueness verification, enumeration value domain verification and verification script verification, among which non-empty verification is a necessary verification rule, and the rest are optional verification rules.

6. The Excel file dynamic parsing and verification system based on rule template according to claim 5 is characterized in that: The form index number stores one or more values, and the formats of the forms corresponding to the multiple values ​​are the same.

7. The Excel file dynamic parsing and verification system based on rule template according to claim 5 is characterized in that: The table fields of the database business data table include a database record ID corresponding to the form metadata and column fields consistent with the number of column metadata, and the column fields adopt a variable-length TEXT data type.

8. The Excel file dynamic parsing and verification system based on rule template according to claim 5 is characterized in that: The Excel dynamic parsing and verification system based on rule templates also includes a rendering engine module, which is used to dynamically render a two-dimensional table and its columns according to the rule template on the front-end page to display the Excel data obtained by parsing.

9. The Excel file dynamic parsing and verification system based on rule template according to claim 5 is characterized in that: The Excel dynamic parsing and verification system based on rule templates also includes a storage engine module, which includes a form metadata table, a column metadata table and a database business data table. The form metadata table is used to store form metadata, the column metadata table is used to store column metadata, and the database business data table is used to store Excel data.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the Excel file dynamic parsing and verification method based on a rule template described in any one of claims 1 to 4 are implemented.

Images