A spreadsheet-oriented formal verification method

Abstract

The application discloses a kind of formalized checking method for electronic form, belong to electronic form technical field, solve the problem that existing method cannot process the data in general electronic form, and cannot be more flexible compatible various electronic form layout style.The checking method includes: the form of the electronic form includes basic item, composite item and list item;The basic item is described using the label simple-item;The simple-item includes sub-label value-ref;The sub-label value-ref is not used simultaneously with sub-label value;The sub-label value-ref establishes cross reference of data in electronic form;The composite item is described using the label complex-item;The list item is described using the label list-item.

Description

Technical Field

[0001] This invention relates to the field of spreadsheet technology, and more specifically to a method for describing formal validation rules for spreadsheets. Background Technology

[0002] Spreadsheets can be used to create complex electronic forms and have wide applications in many fields. Examples include: raw data forms for experiments, forms for collecting information in administrative documents, and data export forms in some specialized industrial software. Data within spreadsheets can be extracted and processed manually, semi-automatically, or fully automatically.

[0003] However, due to the different sources from which tables are generated, there may be issues with table format consistency and data validity. These problems can affect the success rate of table data extraction and processing. Therefore, before processing spreadsheet data, automated formal validation should be performed on the spreadsheet to improve the success rate of subsequent data extraction and processing.

[0004] Currently, there are some mature commercial solutions for validating electronic forms. However, these solutions can only handle spreadsheets with fixed layouts, and their validation capabilities for non-fixed layout forms are limited. Many information systems allow direct form filling, validation, and processing. However, these forms are validated and processed within dedicated, closed systems: the form formats are predefined, and the validation methods are fixed in the program. They cannot handle data in general spreadsheets. Furthermore, some research has shown that some specific spreadsheets can be validated automatically, but this lacks generalization ability.

[0005] Therefore, the existing methods have the following drawbacks: 1) Unable to process data in general spreadsheets; 2) It cannot be more flexible in terms of compatibility with various spreadsheet layouts.

[0006] Existing technology, patent document CN110347999A, discloses "a method and apparatus for validating tabular data." This method involves acquiring tabular data; creating a model object based on the tabular data, the model object including attribute names corresponding to the tabular data; adding annotations to the attribute names in the model object; and when the tabular data changes, only adding or deleting annotations on each attribute name in the model object is needed, and the corresponding validation rules are determined based on the annotations; finally, the tabular data is validated according to the validation rules corresponding to the annotations of the attribute names in the model object. This method eliminates the need to modify the core code logic for importing and exporting tabular data, reducing the workload in developing tabular data validation, saving development costs, and thus facilitating the import and export of tabular data. Patent document CN114510912A discloses a "method, system, and medium for classifying spreadsheets based on a distributed system." This method receives spreadsheets sent from various user terminals in a distributed system; filters each spreadsheet in a task list; parses the expression structure of the filtered spreadsheets; converts each sample data in the sample dataset into a corresponding sample structure; performs similarity matching between the expression structure of the spreadsheets and the sample structure set formed by the sample dataset; parses the sample data corresponding to the spreadsheet in the sample structure set based on the first sample structure; and distributes each spreadsheet to the spreadsheet classification library associated with its corresponding sample data. This invention can remove redundancy from invalid spreadsheets, quickly and effectively classify spreadsheet content, and effectively manage spreadsheets submitted by different terminals.

[0007] In conclusion, existing methods cannot handle data in general spreadsheets and are not flexible enough to accommodate various spreadsheet layouts. Summary of the Invention

[0008] This invention solves the problem that existing methods cannot process data in general spreadsheets and cannot be more flexible in terms of compatibility with various spreadsheet layouts.

[0009] The present invention provides a method for describing formal validation rules for spreadsheets, the method comprising: The spreadsheet forms include basic items, composite items, and list items; The basic items are described using the tag simple-item; The simple-item includes a sub-tag value-ref; the sub-tag value-ref is not used simultaneously with the sub-tag value. The sub-tag value-ref establishes cross-references between data within the spreadsheet; The composite item is described using the tag "complex-item"; The list items are described using the tag list-item.

[0010] Furthermore, in one embodiment of the present invention, the width of the basic item is 1 and the height is 1.

[0011] Furthermore, in one embodiment of the present invention, the tag simple-item has an attribute ID; The value of the attribute ID is a string consisting of English letters or numbers, with the first character being an English letter.

[0012] Furthermore, in one embodiment of the present invention, the label simple-item further includes sub-labels value, x, y, x-ref, x-bias, y-ref, and y-bias.

[0013] Furthermore, in one embodiment of the present invention, the composite item includes a basic item, a composite item, and a list item label; The composite item includes sub-tag x, sub-tag y, sub-tag x-ref, sub-tag y-bias, sub-tag simple-item, and sub-tag list-item.

[0014] Furthermore, in one embodiment of the present invention, the method for calculating the width of the composite item is as follows: Max 内部各项横坐标+宽度 –Min 内部各项横坐标 ; The method for calculating the height of the composite item is as follows: Max 内部各项纵坐标+高度 –Min 内部各项纵坐标 .

[0015] Furthermore, in one embodiment of the present invention, the list item includes sub-tag type, sub-tag orientation, sub-tag width, sub-tag height, sub-tag x-ref, sub-tag y-bias, and sub-tag template.

[0016] Furthermore, in one embodiment of the present invention, the cross-referencing of the data includes value references, list width and height references, and formula references.

[0017] Furthermore, in one embodiment of the present invention, the sub-tag value-ref includes sub-tag ID-ref, sub-tag type, and sub-tag Formulation.

[0018] Furthermore, in one embodiment of the present invention, if the ID referenced by the sub-tag Formula is located in the sub-tag template of the list item, then the ID cannot be directly used as a value for reference, but can be operated using aggregate functions; The aggregate functions include sum, average, min, max, and middle.

[0019] This invention solves the problems of existing methods being unable to process data in general spreadsheets and lacking flexibility in adapting to various spreadsheet layouts. Specific beneficial effects include: The present invention describes a method for describing formal validation rules for spreadsheets, which can validate currently used spreadsheet files and is more flexible in supporting various spreadsheet layouts (fixed and non-fixed), thereby improving the flexibility of computer programs in formal validation of spreadsheets. Attached Figure Description

[0020] The above and / or additional aspects and advantages of the present invention will become apparent and readily understood from the following description of the embodiments taken in conjunction with the accompanying drawings, wherein: Figure 1 This is the basic item sub-label diagram described in the specific implementation method.

[0021] Figure 2 This is the composite item sub-label diagram described in the specific implementation method.

[0022] Figure 3 This is the list item sub-label diagram described in the specific implementation method.

[0023] Figure 4 This is the sub-label image of the increment item as described in the specific implementation method.

[0024] Figure 5 This is a diagram of sub-tags that the value-ref sub-tag can contain, as described in the specific implementation.

[0025] Figure 6 This is the function graph described in the specific implementation method. Detailed Implementation

[0026] Various embodiments of the present invention will now be clearly and completely described with reference to the accompanying drawings. The embodiments described with reference to the drawings are exemplary and intended to explain the present invention, and should not be construed as limiting the present invention.

[0027] This embodiment describes a method for describing formal validation rules for spreadsheets. The description method includes: The spreadsheet forms include basic items, composite items, and list items; The basic items are described using the tag simple-item; The simple-item includes a sub-tag value-ref; the sub-tag value-ref is not used simultaneously with the sub-tag value. The sub-tag value-ref establishes cross-references between data within the spreadsheet; The composite item is described using the tag "complex-item"; The list items are described using the tag list-item.

[0028] In this embodiment, the width of the basic item is 1 and the height is 1.

[0029] In this embodiment, the tag simple-item has an attribute ID; The value of the attribute ID is a string consisting of English letters or numbers, with the first character being an English letter.

[0030] In this embodiment, the label simple-item further includes sub-labels value, x, y, x-ref, x-bias, y-ref, and y-bias.

[0031] In this embodiment, the composite item includes a basic item, a composite item, and a list item label; The composite item includes sub-tag x, sub-tag y, sub-tag x-ref, sub-tag y-bias, sub-tag simple-item, and sub-tag list-item.

[0032] In this embodiment, the width of the composite item is calculated as follows: Max 内部各项横坐标+宽度 –Min 内部各项横坐标 ; The method for calculating the height of the composite item is as follows: Max 内部各项纵坐标+高度 –Min 内部各项纵坐标 .

[0033] In this embodiment, the list items include sub-tag type, sub-tag orientation, sub-tag width, sub-tag height, sub-tag x-ref, sub-tag y-bias, and sub-tag template.

[0034] In this embodiment, the cross-referencing of data includes value references, list width and height references, and formula references.

[0035] In this embodiment, the sub-tag value-ref includes the sub-tag ID-ref, the sub-tag type, and the sub-tag Formulation.

[0036] In this embodiment, the ID referenced by the sub-tag Formula is located in the sub-tag template of the list item. Therefore, the ID cannot be directly used as a value for reference, but can be operated using aggregate functions. The aggregate functions include sum, average, min, max, and middle.

[0037] This embodiment, based on the formal validation rule description method for spreadsheets described in this invention, provides a practical implementation method: A set of formalized spreadsheet validation rules is defined to describe the format and content constraints of spreadsheets. These rules are then used as input to a formalized spreadsheet validation algorithm to validate the specified spreadsheet. The algorithm is implemented using Extensible Markup Language (XML).

[0038] 1. Basic Elements A spreadsheet form has three basic elements: basic items, compound items, and list items.

[0039] Basic items: refer to the content of a single table in a spreadsheet.

[0040] Composite items: Several related basic items or list items can form a composite item. For example, a personal information composite item consists of basic items such as name, gender, and age.

[0041] List items: These are a collection of parallel and equivalent items in a spreadsheet. Each item can be a basic item or a composite item. List items can be of fixed or variable length, and can expand horizontally or vertically.

[0042] 2. Description methods for basic items The `simple-item` tag is used to describe a basic item. This tag can have an `ID` attribute, which serves as a globally unique identifier for the tag. The value of the `ID` attribute must be a string consisting of English letters (uppercase or lowercase) or numbers, and the first character must be an English letter. `simple-item` contains several child tags, such as... Figure 1 As shown. The width of the basic item is 1, and the height is 1.

[0043] 3. Methods for describing composite items Use tags <complex-item>To describe a composite item, the tag can have an attribute ID, which serves as a globally unique identifier for that tag. For example... Figure 2 As shown, it contains several basic items, compound items, or list item labels. Its width and height are not explicitly given by the labels, but are dynamically calculated based on the position of the items it contains.

[0044] Width calculation method: Max(internal horizontal coordinates + width) – Min(internal horizontal coordinates); Height calculation method: Max(internal vertical coordinates + height) – Min(internal vertical coordinates).

[0045] 4. List item description methods Use tags <list-item>To describe a list item, this tag can have an attribute ID, which serves as a globally unique identifier for that tag. It contains several child tags, see... Figure 3 As shown.

[0046] If `template` is a `complex-item` tag, then the `complex` tag can contain an `increment` tag. In this case, the `increment` tag also represents a basic item. Except for the absence of a `value` sub-tag, the usage of the other sub-tags is the same as `simple-item`. `increment` is used to describe a special type of basic item, namely a list index, used to describe the index of each item in the list. `start` describes the initial value, and `step` describes the increment step. Sub-tags under the `increment` tag are as follows... Figure 4 As shown.

[0047] 5. Constraints on cross-referencing of data The `simple-item` element can contain a sub-tag `value-ref`. This tag cannot be used simultaneously with the `value` sub-tag. The `value-ref` tag is primarily used to create cross-references between data within a spreadsheet. Data cross-references mainly include: value references, references to list width and height, and formula references.

[0048] Value reference: The value of the current underlying item always remains consistent with the value of a certain underlying item.

[0049] Width / Height Reference: The value of the current base item is equal to the width / height of an item.

[0050] Formula reference: The value of the current base item can be obtained by using the values ​​of several lists and the width / height values, and then calculated using a mathematical formula.

[0051] The `value-ref` sub-tag can contain sub-tags such as... Figure 5 As shown.

[0052] Explanation of Formulation: ID.value: ID must be the underlying item ID, indicating that the value of the underlying item was used, and its value should be guaranteed to be convertible to a number type; ID.width: ID must be the ID of the list item, indicating the width value of the list item to be used; ID.height: ID must be the ID of the list item, indicating the height value of that list item; The operators that can be used include: +, -, *, / , %, and the functions that can be used are as follows: Figure 6 As shown.

[0053] If the ID referenced in the Formulation is within the template of a list item, this ID cannot be directly referenced as a value. However, aggregate functions can be used to manipulate it. Aggregate functions include: (ID.value): Summation; average(ID.value): Calculates the average value; min(ID.value): Finds the minimum value; max(ID.value): Finds the maximum value; middle(ID.value): Calculates the median value.

[0054] The content of the Formulation tag can use the above data referencing methods in conjunction with operators, functions, and aggregate functions to describe the complex reference constraints between the value of the current base item and the values ​​of other referenced items.

[0055] The foregoing has provided a detailed description of a formal validation rule method for spreadsheets proposed in this invention. Specific examples have been used to illustrate the principles and implementation methods of this invention. The descriptions of the above embodiments are only for the purpose of helping to understand the method and core ideas of this invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this invention. Therefore, the content of this specification should not be construed as a limitation of this invention.

Claims

1. A formal validation method for spreadsheets, characterized in that, The formal verification method includes: The spreadsheet forms include basic items, composite items, and list items; The basic items are described using the tag simple-item; The simple-item includes a sub-tag value-ref; the sub-tag value-ref is not used simultaneously with the sub-tag value. The sub-tag value-ref establishes cross-references between data within the spreadsheet; The composite item is described using the tag "complex-item"; The list items are described using the tag list-item; The list items include the sub-tag type, sub-tag orientation, sub-tag width, sub-tag height, sub-tag x-ref, sub-tag y-bias, and sub-tag template; The sub-tag value-ref includes the sub-tag ID-ref, the sub-tag type, and the sub-tag Formulation; If the ID referenced by the sub-tag Formulation is in the sub-tag template of the list item, then this ID cannot be directly referenced as a value, but can be operated on using aggregate functions; The cross-references to the data include value references, list width and height references, and formula references; Value reference: The value of the current base item always remains consistent with the value of a certain base item; Width / Height Reference: The value of the current base item is equal to the width / height of the list item; Formula reference: The value of the current base item can be obtained by using the values ​​of several lists and their width / height values, and then calculated using a mathematical formula; Basic items: refer to the content of a single table in a spreadsheet; Composite item: Several related basic items or list items can form a composite item; List item: refers to a collection of several parallel and equivalent items in a spreadsheet, wherein the items are basic items or composite items; The composite item includes a basic item, a composite item, and a list item label; The composite item includes sub-tags x, y, x-ref, y-bias, simple-item, and list-item; The method for calculating the width of the composite item is as follows: Max 内部各项横坐标+宽度 -My 内部各项横坐标 ; The method for calculating the height of the composite item is as follows: Max 内部各项纵坐标+高度 -My 内部各项纵坐标 The Formulation tag uses data references in conjunction with operators, functions, and aggregate functions to describe the complex reference constraints between the value of the current base item and the values ​​of other referenced items.

2. The formal validation method for spreadsheets according to claim 1, characterized in that, The width of the basic item is 1 and the height is 1.

3. The formal validation method for spreadsheets according to claim 1, characterized in that, The tag simple-item has an attribute ID; The value of the attribute ID is a string consisting of English letters or numbers, with the first character being an English letter.

4. The formal validation method for spreadsheets according to claim 1, characterized in that, The tag simple-item also includes sub-tags value, x, y, x-ref, x-bias, y-ref, and y-bias.

5. The formal validation method for spreadsheets according to claim 1, characterized in that, The aggregate functions include sum, average, min, max, and middle.

Images