Spreadsheet data checking method and system, computer equipment and storage medium

Abstract

The invention discloses a spreadsheet data checking method and system, computer equipment and a storage medium. The method comprises the steps of obtaining a to-be-checked spreadsheet file, and fusing and extracting data and formula information in the to-be-checked spreadsheet file; analyzing formula information, and constructing a dependency tree to track a calculation path of each total amount cell; establishing a mapping relation between the quotation items of the two parties based on formula information; reversely analyzing the mapping relation from the total sum of the two parties, and detecting inconsistent points to obtain abnormal points; analyzing the abnormal points to find out wrong or missing mapping items, and generating an error report to obtain a checking result; and displaying the checking result in the forms of charts and tables. By implementing the method provided by the invention, the mapping relationship in the complex quotation can be automatically analyzed, and errors can be quickly positioned and corrected, so that the efficiency and accuracy of data checking are improved.

Description

Technical Field

[0001] The present invention relates to a data processing method, and more particularly to an electronic spreadsheet data checking method, system, computer equipment and storage medium. Background Art

[0002] In today's business environment, quotations serve as a key tool in supply chain management, bidding, and procurement, clarifying the price terms between the two parties involved in a transaction. As businesses expand, quotations become increasingly complex, especially when involving more than 1,000 quotation items. The sheer volume of data and complex structure place higher demands on quotation processing. For example, in large-scale engineering projects or equipment procurement, the purchaser, such as the buyer, typically uses a specific quotation template format that includes detailed classifications, calculation rules, and data formats; while the supplier, such as the supplier, may need to submit a quotation based on their own template. Because the templates of the two parties differ in terms of structural design, field naming, and calculation logic, accurate mapping of quotation data becomes a crucial step.

[0003] Traditionally, quotations have primarily been processed using spreadsheet software like Microsoft Excel or Google Sheets, leveraging their flexible formula capabilities to facilitate data mapping and verification. While this approach works for a small number of quote items, it often struggles to efficiently and accurately handle large amounts of data and significant template discrepancies. Manually comparing quote items one by one is not only time-consuming and labor-intensive, but also prone to mis-mapping or omissions, leading to inconsistent amounts, potentially causing business disputes or project delays.

[0004] To address these issues, several solutions have emerged on the market, such as manual verification and mapping. This method is suitable for situations with fewer quotation items, but as the complexity of quotations increases, the efficiency and accuracy of manual operations decrease significantly. Rule-based automation tools automate quotation mapping through predefined mapping rules. Although this improves efficiency, it has poor adaptability to dynamically changing templates. Third-party data processing software provides powerful data conversion capabilities, but due to its high versatility, it is not optimized for quotation mapping scenarios and is also costly. Database-based verification methods are suitable for highly structured data environments, but face challenges when processing unstructured data and have difficulty parsing complex formula dependencies.

[0005] However, the above solutions all have obvious limitations when processing complex quotations, including but not limited to inefficient manual operations, inflexible automation tools, high costs, and insufficient logical analysis capabilities for complex formulas.

[0006] Therefore, it is necessary to design a new method to automatically analyze the mapping relationships in complex quotations, quickly locate and correct errors, thereby improving the efficiency and accuracy of data verification and protecting the interests of both parties to the transaction. Summary of the Invention

[0007] The purpose of the present invention is to overcome the defects of the prior art and provide a spreadsheet data verification method, system, computer equipment and storage medium.

[0008] To achieve the above object, the present invention adopts the following technical solution: a spreadsheet data verification method, comprising: Obtaining a spreadsheet file to be verified, and fusing and extracting data and formula information in the spreadsheet file to be verified; Analyze the formula information and construct a dependency tree to trace the calculation path of each total amount cell; Establishing a mapping relationship between the quotation items of both parties based on the formula information; Starting from the total amount of both parties, reversely analyze the mapping relationship, detect inconsistencies, and obtain anomalies; Analyze outliers to find incorrect or missing mapping items and generate error reports to obtain verification results; The verification results are presented in the form of charts and tables.

[0009] A further technical solution is: obtaining the electronic spreadsheet file to be checked, and fusing and extracting the data and formula information in the electronic spreadsheet file to be checked, including: Import the spreadsheet files of both parties using the spreadsheet software API interface to obtain the spreadsheet file to be verified; Copy the contents of the spreadsheet file to be verified into a new workbook without loss, keeping the original formulas, styles, and names unchanged, and add a prefix to the conflicting workbook-level names to distinguish the sources, so as to obtain the fusion result; The fusion result is subjected to amount selection and marking to obtain formula information.

[0010] A further technical solution is: analyzing the formula information and constructing a dependency tree to track the calculation path of each total amount cell, including: Determine a starting parsing point according to the formula information; Use the spreadsheet API to get the formula string in the specified cell; Convert the formula string into an abstract syntax tree and recursively parse all sub-formulas down to the underlying value cells. For nested functions and cross-worksheet reference resolution, determine the reference path through runtime simulation. Monitor and report invalid formulas or circular reference errors within the formula string to obtain a parsing result. The parsing results are organized into a multi-branch tree structure, a hash table is used to accelerate the query, and the results are stored in a hidden work table.

[0011] A further technical solution is: the multi-branch tree structure includes a plurality of nodes, each of which contains a cell address, a formula or value, a data type and a child node list.

[0012] A further technical solution is: starting from the total amount of both parties, reversely analyzing the mapping relationship, detecting inconsistencies, and obtaining abnormal points, including: When an abnormal mapping exists in the mapping relationship, the specific source of the abnormal value is located by tracing back the dependency tree in the DAG path, and the relevant path is recorded to obtain the abnormal point.

[0013] A further technical solution is: analyzing the abnormal points to find out the wrong or missing mapping items and generating an error report to obtain the verification results, including: DAG in-degree analysis is used to identify and evaluate multiple mappings of one of the nodes, and a single mapping is recommended to eliminate redundancy. For unmapped nodes, string matching technology is used to suggest potential mappings that are similar to the other node's fields. Based on field similarity and numerical consistency, specific mapping corrections or new additions are generated to obtain verification results.

[0014] The present invention also provides an electronic spreadsheet data verification system, comprising: an acquisition unit, configured to acquire a spreadsheet file to be checked, and merge and extract data and formula information in the spreadsheet file to be checked; an analysis unit, configured to analyze the formula information and construct a dependency tree to trace the calculation path of each total amount cell; A mapping relationship establishing unit, configured to establish a mapping relationship between the quotation items of both parties based on the formula information; A detection unit is used to reversely analyze the mapping relationship based on the total amount of both parties, detect inconsistencies, and obtain abnormal points; A verification unit is used to analyze abnormal points to find incorrect or missing mapping items and generate an error report to obtain verification results; The display unit is used to display the verification results in the form of charts and tables.

[0015] The present invention further provides a computer device, comprising a memory and a processor, wherein a computer program is stored in the memory, and the processor implements the above method when executing the computer program.

[0016] The present invention also provides a storage medium, wherein the storage medium stores a computer program, and the computer program implements the above method when executed by a processor.

[0017] The beneficial effects of the present invention compared to the prior art are as follows: the present invention realizes efficient analysis and error correction of mapping relationships in complex quotations through an automated process. First, the data and formula information in the spreadsheet file to be verified are obtained and integrated. Then, these formula information are analyzed to build a dependency tree, the calculation path of each total amount cell is traced, and the mapping relationship between the quotation items of both parties is established based on the extracted data. Subsequently, the mapping relationship is reversely parsed based on the total amount of both parties to detect inconsistencies, determine abnormal points, and deeply analyze these abnormal points to find incorrect or missing mapping items, and generate a detailed error report as the verification result. Finally, the verification results are displayed in the form of intuitive charts and tables, so that users can quickly understand and correct errors, thereby greatly improving the efficiency and accuracy of data verification and effectively protecting the interests of both parties to the transaction. This method not only reduces the time cost and error rate of manual verification, but also enhances the transparency and traceability of the verification process.

[0018] The present invention will be further described below with reference to the accompanying drawings and specific embodiments. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0020] Figure 1 A schematic diagram of an application scenario of the electronic spreadsheet data verification method provided by an embodiment of the present invention; Figure 2 A schematic diagram of a flow chart of a method for verifying spreadsheet data provided by an embodiment of the present invention; Figure 3 A schematic diagram of a sub-process of a method for verifying spreadsheet data provided by an embodiment of the present invention; Figure 4 A schematic diagram of a sub-process of a method for verifying spreadsheet data provided by an embodiment of the present invention; Figure 5 A schematic block diagram of an electronic spreadsheet data verification system provided by an embodiment of the present invention; Figure 6 A schematic block diagram of a computer device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0021] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. All other embodiments obtained by ordinary technicians in this field based on the embodiments of the present invention without making any creative efforts shall fall within the scope of protection of the present invention.

[0022] It will be understood that when used in this specification and the appended claims, the terms “comprises” and “comprising” indicate the presence of described features, integers, steps, operations, elements and / or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof.

[0023] It should also be understood that the terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to limit the present invention. As used in the specification and appended claims, the singular forms "a," "an," and "the" are intended to include the plural forms unless the context clearly indicates otherwise.

[0024] It should be further understood that the term "and / or" used in the present description and appended claims refers to and includes any and all possible combinations of one or more of the associated listed items.

[0025] See also Figure 1 and Figure 2 , Figure 1 A schematic diagram of an application scenario of the electronic spreadsheet data verification method provided by an embodiment of the present invention. Figure 2 The present invention provides a schematic flow chart of a spreadsheet data verification method according to an embodiment of the present invention. The spreadsheet data verification method is applied to a server. The server interacts with the terminal, obtains and integrates the data and formula information of the spreadsheet file through an automated tool, constructs a dependency tree to track the calculation path of each total amount cell, and establishes a mapping relationship between the quotation items of both parties based on data analysis. Next, the mapping relationship is reversely parsed based on the total amount of both parties to detect inconsistencies, thereby locating the specific source of the outliers. DAG in-degree analysis is used to identify duplicate mappings and recommend the best option, while potential mapping suggestions are proposed for unmapped items through string matching technology. The entire process not only monitors and reports invalid formulas or circular reference errors, but also generates specific correction suggestions. Finally, the verification results are presented in the form of charts and tables, enabling rapid error correction, improving data verification efficiency and accuracy, and ensuring that the interests of both parties to the transaction are protected.

[0026] Figure 2 FIG. 1 is a flow chart of a method for verifying electronic spreadsheet data provided by an embodiment of the present invention. Figure 2As shown, the method includes the following steps S110 to S160.

[0027] S110 , obtaining a spreadsheet file to be checked, and integrating and extracting data and formula information in the spreadsheet file to be checked.

[0028] In this embodiment, the spreadsheet files to be reviewed refer to the spreadsheet files of the quotations provided by Party A and Party B. These files typically contain the data for the quotation items (such as unit price, quantity, total amount, etc.) and their calculation logic (expressed through formulas). Data refers to specific numerical values ​​or text information in the spreadsheet, such as the contents of fields such as the name, quantity, unit price, and total price of the quotation item. Formula information refers to the mathematical expressions or functions used to calculate the total amount of the quotation items or other intermediate results, such as SUM and VLOOKUP.

[0029] In one embodiment, see Figure 3 The above-mentioned step S110 may include steps S111 to S113.

[0030] S111. Import the spreadsheet files of both parties using the spreadsheet software API interface to obtain the spreadsheet file to be verified.

[0031] In this embodiment, this step involves using the application programming interface (API) provided by the spreadsheet software to automatically import the spreadsheet files of both parties. This allows for automatic processing and analysis of the data and formulas in these files without manual intervention. In this way, the system can efficiently obtain the original data source to be verified.

[0032] S112. Copy the contents of the spreadsheet file to be verified to a new workbook without loss, keep the original formulas, styles and names unchanged, and add a prefix to the conflicting workbook-level names to distinguish the sources, so as to obtain a fusion result.

[0033] During this phase, the system creates a new spreadsheet workbook and copies the spreadsheet contents obtained from both parties into this new workbook. All original formulas, formatting, and naming are preserved during this process, ensuring data consistency and accuracy. If a name conflict occurs (e.g., if the same name is used in two workbooks), a specific prefix (e.g., "CONFLICT_A_" or "CONFLICT_B_") is added to the name to clearly distinguish workbook-level names from different sources. This ensures that subsequent data parsing and mapping verification can be performed accurately even in the presence of name conflicts.

[0034] In this example, the fusion result refers to the new workbook generated after the above steps. It contains all the information from both parties' quotations, including but not limited to data, formulas, styles, and names, while resolving any potential name conflicts. This fusion result provides a complete and structured data foundation for subsequent steps, enabling further formula parsing, mapping relationship construction, and verification.

[0035] S113. Select and mark the amount of the fusion result to obtain formula information.

[0036] The final step involves selecting and marking all key cells involved in amount calculations (such as total amount cells) within the merged spreadsheet. This step also involves identifying the formulas within these cells and appropriately labeling them for ease of subsequent parsing and analysis. For example, using the spreadsheet software's name manager, unique identifiers (such as "Party A_Total Amount" and "Party B_Total Amount") are assigned to key total amount cells, ensuring stable references to these cells in subsequent operations regardless of spreadsheet modifications. Furthermore, the formula information corresponding to each amount cell is recorded as a basis for further formula parsing.

[0037] In this example, the module imports and merges the spreadsheet files of Party A's and Party B's quotations, marking the total amount between them. This provides basic data for subsequent formula parsing and mapping verification. This module is integrated into the spreadsheet software environment as a plug-in, leveraging native spreadsheet functionality (such as workbook operations, cell management, and a name manager) to implement file merging and total amount marking. It also provides interactive support through a custom user interface (GUI). The module not only supports complex quotations (with over 1,000 items), but is also compatible with multiple spreadsheet file formats. Its intelligent design enhances system robustness and user experience.

[0038] Use the API provided by the spreadsheet software to import Party A and Party B's spreadsheet files. This step ensures automated processing and can obtain the data source for analysis without manual intervention.

[0039] After importing, the module copies all worksheets from Party A's file and the worksheets from Party B's spreadsheet into a new workbook, preserving the formulas, styles, and names from the original files. This process is completed within the spreadsheet environment, efficiently processing multi-sheet workbooks containing over a thousand items of data. Generating a unified workbook takes only 3-5 seconds, an approximately 90% improvement over manual copying and pasting. Furthermore, to ensure data consistency, the system retains the spreadsheet's native formula functionality.

[0040] If workbook-level name conflicts occur between Party A and Party B, the module automatically adjusts these names to "CONFLICT_A_Original Name" or "CONFLICT_B_Original Name," respectively, adding a prefix identifying the source (Party A or Party B) to the conflicting names. This step ensures that subsequent data parsing and mapping verification can be performed accurately even in the presence of name conflicts.

[0041] An interactive interface allows users to select the cells containing the total amount of Party A and Party B by clicking the mouse or directly entering the cell address (e.g., C1, D2). The interface displays the selected cell's value and formula (e.g., "=SUM(A1:A1000)") in real time, allowing users to confirm the accuracy of their selection.

[0042] To improve system robustness and prevent invalid references due to table modifications like inserting rows or columns, the module uses the spreadsheet software's name manager to assign unique names to selected total amount cells (e.g., "Party A_Total Amount," "Party B_Total Amount"). This naming scheme is not only stable but also stored in the workbook, making it valid across sessions.

[0043] Name references are stored as metadata in the spreadsheet workbook's Names collection, meaning there's no need to re-tag each verification. Upon startup, the add-in checks the Names collection and automatically loads existing tags, such as "Party A_Total Amount" and "Party B_Total Amount." This significantly reduces the need for repetitive configuration and improves verification efficiency by approximately 20%.

[0044] If the user doesn't select the total amount or selects an invalid cell (such as a text cell), the plug-in will pop up a warning through the GUI, suggesting that the user reselect. In addition, it provides intelligent recommendation functions, such as scanning cells containing SUM or SUMIFS formulas, to help users make the correct selection.

[0045] The above technology ensures the stability of the total amount reference, which is more robust than traditional fixed address references. At the same time, the automatic loading tag function reduces the time of repeated configuration and further improves work efficiency.

[0046] S120: Analyze the formula information and construct a dependency tree to track the calculation path of each total amount cell.

[0047] In this embodiment, the dependency tree refers to a multi-branch tree structure that is generated by recursively parsing a formula and records the reference relationships between cells, and is used to track the complete calculation path of each total amount cell.

[0048] To accurately parse and verify the total amount cells for Party A and Party B in quotations, the system uses a directed acyclic graph (DAG)-based approach to construct a dependency tree. This approach not only efficiently parses complex nested formulas and cross-worksheet references, but also ensures accurate identification of incorrect or missing mappings.

[0049] In one embodiment, see Figure 4 , the above-mentioned step S120 may include steps S121~S124.

[0050] S121. Determine a starting parsing point according to the formula information.

[0051] In this example, this step first requires identifying and marking the total amount cells in Party A's and Party B's quotations. These cells often contain references to complex formulas, such as SUM and SUMIFS, which summarize various expenses. Using a spreadsheet software plug-in, users can select and mark these total amount cells (e.g., "Party A_Total Amount," "Party B_Total Amount") as starting points for the subsequent parsing process.

[0052] S122. Obtain the formula string in the specified cell using the spreadsheet API.

[0053] In this example, once the starting parsing point is determined, the module uses the spreadsheet software's API to extract the formula string from the specified cell (i.e., the total amount cell). For example, if the total amount cell C1 contains the formula =SUM(A1:A1000)+IF(B1>0,C2,0), this step extracts the entire formula string and its corresponding cell address.

[0054] S123. Convert the formula string into an abstract syntax tree, and recursively parse all sub-formulas until the basic numeric cell. For nested functions and cross-worksheet reference parsing, determine the reference path through runtime simulation; monitor and report invalid formulas or circular reference errors in the formula string to obtain a parsing result.

[0055] In this embodiment, the parsing result refers to a detailed information set including the cell address, formula string or value, data type and its dependencies, obtained by recursively parsing all related sub-formulas starting from the total amount cell to the basic value cell.

[0056] Convert the formula string to an abstract syntax tree (AST): This step first decomposes the extracted formula string into an abstract syntax tree structure. For example, =SUM(A1:A1000) can be decomposed into a root node SUM and child nodes A1:A1000.

[0057] Recursively parse sub-formulas: For each reference range (such as A1:A1000), if other formulas are found in it, continue to recursively parse these sub-formulas until a cell without a formula (such as a specific value) is reached.

[0058] Handling nested functions and cross-sheet references: For more complex formulas (such as SUMIFS and VLOOKUP), the module uses runtime simulation to determine their reference path. For example, for SUMIFS(A1:A1000,B1:B1000,"Equipment Fee"), the module will parse the range A1:A1000, the condition B1:B1000, and the constant "Equipment Fee".

[0059] Anomaly Detection: During this process, the module also checks for invalid formulas (such as #REF!, #VALUE!) or circular references (such as A1 referencing itself). If such issues are detected, the system prompts the user to correct them through the graphical user interface (GUI) and records the relevant abnormal path.

[0060] S124: Organize the parsing results into a multi-branch tree structure, use a hash table to accelerate the query, and save the results in a hidden worksheet.

[0061] After parsing, the results are organized in the form of a multi-branch tree, where each node represents a cell or formula, and the edges represent the reference relationship between formulas. This structure allows for fast traversal and querying of dependencies. To further improve query efficiency, the system will also use a hash table to cache the mapping relationship between cell addresses and nodes, so that finding the dependency path of a specific cell only takes O(1) time. Finally, the generated dependency tree will be serialized and saved in a hidden worksheet (for example, named "DependencyTree") to facilitate subsequent verification and error location operations. This approach ensures consistency and continuity even between different sessions.

[0062] Through the above steps, this embodiment provides an efficient and accurate formula parsing method, which provides a solid foundation for the subsequent verification and analysis of mapping relationships. This method not only improves the efficiency of processing over a thousand complex quotations, but also enhances the robustness of the system and user experience.

[0063] In this embodiment, the formula logic within cells explicitly marked as "total amount" in Party A and Party B's quotations (typically named "Party A_Total Amount" and "Party B_Total Amount") is completely, losslessly, and end-to-end parsed. This analysis generates two independent, clearly structured formula dependency trees. These two trees serve as the underlying foundation for subsequent mapping verification, discrepancy location, and error tracking, and are the fundamental guarantee for the system to maintain high accuracy and robustness in highly complex scenarios. The module's design fully leverages the native capabilities of spreadsheet software. Starting from the marked total amount cell, it recursively descends along the formula chain until it encounters an "atomic" cell that no longer contains any formulas. Ultimately, the entire dependency chain is abstracted into a tree-like data structure that is highly queryable, persistent, and capable of exception backtracking. It also natively supports nested functions at any level, such as SUMIFS, VLOOKUP, and INDEX-MATCH, as well as dynamic cross-sheet and cross-file references, easily handling extreme scenarios with over 1,000 quotation items.

[0064] Specifically, the data entry module first retrieves the two cells "Party A_Total Amount" and "Party B_Total Amount" explicitly pointed to by the Excel name manager (for example, the name "Party A_Total Amount" points to Sheet1!$C$1). Then, the same recursive parsing process is performed on each of them, ultimately resulting in two independent dependency trees. The parsing process is rigorously structured into the following three steps: Read the complete formula string of cell C1 of "Party A_Total Amount" through the official spreadsheet software API, for example: =SUM(A1:A1000)+IF(B1>0,C2,0) At the same time, the absolute address of the cell Sheet1!$C$1 is recorded to establish the root node identifier for subsequent tree nodes.

[0065] The module has a built-in recursive descent parser (Recursive Descent Parser), which can complete the full link conversion of "formula string → abstract syntax tree AST → dependency tree" in one go. Its internal sub-steps are as follows: Split the formula string into an AST: the root node is a function or operator, and the child nodes are parameters or reference ranges. For example, the root node of "=SUM(A1:A1000)" is SUM, and the child nodes are the interval nodes A1:A1000.

[0066] For each reference range or cell, check again whether it contains a formula; if it does (such as "=B1+B2" in A1), continue recursively until a pure numeric value or text cell without a formula is found (such as B1 containing the value 100).

[0067] The parser also breaks down complex functions such as SUMIFS, VLOOKUP, and INDEX-MATCH layer by layer. Taking "=SUMIFS(A1:A1000,B1:B1000,"Equipment Fee")" as an example, it is decomposed into three sub-nodes: the data range A1:A1000, the conditional range B1:B1000, and the text constant "Equipment Fee".

[0068] For cross-sheet references (such as Sheet2!A1) or dynamic range functions (such as OFFSET and INDIRECT), the module calls a method similar to Excel's Evaluate method to calculate its actual reference range at runtime and solidifies the calculation results into the tree node to ensure that the tree structure is complete and reproducible.

[0069] If an invalid formula (#REF!, #VALUE!), circular reference (A1 referencing A1), or other unparseable syntax is detected during the parsing process, the module immediately prompts the user through a pop-up window in the GUI, such as "Circular reference detected, please check cell A1." The abnormal path is also logged, ensuring user traceability and correctability. The entire recursive parsing chain ensures 100% traceability from the total amount to the lowest-level cell, supporting arbitrarily complex formulas and scenarios with over a thousand items of data. Parsing a single quotation typically takes only a few seconds, an efficiency improvement of approximately 95% compared to manual analysis.

[0070] To ensure performance while taking into account persistence and easy query, the module uses a tree data structure to carry the analysis results of Party A and Party B respectively: The tree is a multi-branch tree, where each node corresponds to a cell or a formula fragment; nodes form directed edges through "references". Node attributes are fixed and include: Absolute cell address (such as Sheet1!$C$1); A formula string (such as "=SUM(A1:A1000)") or a plain value (if there is no formula); Data type tag (value / text / formula); A list of child nodes (an array of directly referenced cell or range addresses).

[0071] After parsing is complete, the entire tree exists as a memory object, with nodes organized as a hash table with "key = cell address, value = complete node attributes"; the entire tree is then serialized to a hidden sheet in the Excel workbook (named "DependencyTree" by default), with each row representing a node. The fields are, in sequence, address, formula, and child node address, achieving cross-session persistence without the need for secondary parsing.

[0072] For functions such as OFFSET and INDIRECT, whose boundaries can only be determined at runtime, the node additionally saves the actual reference range calculated by Evaluate (for example, A1:A100) to ensure that the tree structure can be completely restored at any time.

[0073] The system maintains a hash cache of "cell address-tree node pointer" internally, and any query on the cell dependency path can be completed in O(1) time.

[0074] The tree structure is saved by hiding the worksheet and saved with the workbook. It is valid across sessions and avoids performance loss caused by repeated parsing.

[0075] The tree structure is naturally suitable for expressing nested reference relationships. Compared with linear arrays or linked lists, it reduces memory usage by an average of about 30%. It also supports depth-first and breadth-first fast traversal, with an average query time of less than 0.1 seconds.

[0076] In this embodiment, the multi-branch tree structure includes a plurality of nodes, each of which includes a cell address, a formula or value, a data type, and a child node list.

[0077] Specifically, the multi-tree structure is an efficient data organization format specifically designed to represent and manage complex dependencies between cells in spreadsheets. Each node in this tree structure represents a specific cell or its corresponding formula logic, detailing the cell's address (e.g., Sheet1!CC1), the formula string (e.g., "=SUM(A1:A1000)") or direct value (if the cell does not contain a formula), and the data type (numeric, text, or formula). Furthermore, each node maintains a list of child nodes describing all reference paths from the current cell—that is, the cells directly or indirectly referenced by the formula in the current cell. This approach not only clearly displays the hierarchical relationship between each cell and its referenced cells, but also supports rapid traversal and querying of the entire formula's dependencies, providing powerful support for addressing potential error detection, mapping verification, and other issues. This structure is particularly well-suited for processing spreadsheets with complex nested relationships and large-scale references, improving analysis efficiency and accuracy.

[0078] S130: Establish a mapping relationship between the quotation items of both parties based on the formula information.

[0079] In this embodiment, establishing a mapping relationship involves parsing manual formulas in cells on one party (usually Party B) to extract and record the mapping relationships between the two parties' quotation items. For example, if a cell on Party B contains a VLOOKUP function that references a cell or range on Party A, this reference constitutes a mapping relationship. These mapping relationships are recorded and serve as the basis for subsequent verification. To enhance automation, the system also supports automatic matching, such as preliminary matching based on field name similarity, and allows users to manually adjust to ensure accuracy.

[0080] The manual formula of one of the cells is parsed based on the data, and the mapping relationship between the two parties is extracted and recorded.

[0081] S140 , starting from the total amount of both parties, reversely analyze the mapping relationship, detect inconsistencies, and obtain abnormal points.

[0082] In this embodiment, outlier detection is achieved by reversing the mapping relationship, starting from the total amount of Party A and Party B. This step utilizes the previously constructed Dependency Graph (DAG) to trace the value of each mapping node along the dependency path and compare the calculation results of the corresponding nodes on Party A and Party B. When a discrepancy is found, the system flags it as a potential outlier and conducts in-depth analysis of the dependency tree to locate the specific source of the outlier. For example, if Party A's total amount is 10,000 yuan and Party B's total amount is 9,500 yuan, a difference of 500 yuan. The system will decompose this difference along the dependency path to identify the specific cell causing the discrepancy and the cause.

[0083] When an abnormal mapping exists in the mapping relationship, the specific source of the abnormal value is located by tracing back the dependency tree in the DAG path, and the relevant path is recorded to obtain the abnormal point.

[0084] S150: Analyze the abnormal points to find out the wrong or missing mapping items, and generate an error report to obtain the verification results.

[0085] In this embodiment, verification results are generated through further analysis of outliers, including using DAG in-degree analysis to identify whether a node has multiple mappings (duplicate mappings) and unmapped nodes (missing mappings). For duplicate mappings, the system recommends retaining the most appropriate single mapping to eliminate redundancy. For missing mappings, the system uses string matching techniques (such as the Levenshtein distance algorithm) to suggest potential mappings that are similar to the other party's fields. Finally, based on field similarity and numerical consistency, the system generates detailed error reports, including specific suggestions for correcting or adding new mappings. These reports not only identify existing issues but also provide feasible solutions, helping users quickly correct errors and improve the consistency and accuracy of quotations.

[0086] DAG in-degree analysis is used to identify and evaluate multiple mappings of one of the nodes, and a single mapping is recommended to eliminate redundancy. For unmapped nodes, string matching technology is used to suggest potential mappings that are similar to the other node's fields. Based on field similarity and numerical consistency, specific mapping corrections or new additions are generated to obtain verification results.

[0087] In this embodiment, verification analysis is the core validation component of the system. It is built entirely on the formula dependency tree (presented as a DAG) generated by the formula parsing module for both parties. It systematically verifies the mapping relationships between Party A and Party B's quotation items, manually established by quoting personnel through formulas. It automatically detects incorrect mappings (such as duplicate mappings), missing mappings, and other potential mapping flaws, providing a detailed basis for subsequent error detection modules. The module further leverages the native calculation and data access capabilities of spreadsheet software, targeting large-scale scenarios with over 1,000 quotation items. It fully supports complex nested formulas (such as SUMIFS, VLOOKUP, INDEX-MATCH), as well as dynamic cross-sheet and even cross-workbook references. The module uses a visual graphical user interface (GUI) to intuitively view mapping relationships. A built-in difference analysis algorithm decomposes amount discrepancies layer by layer until the root cause is located, ensuring both efficiency and accuracy in the verification process.

[0088] The mapping relationship verification and analysis module uses DAG (Directed Acyclic Graph) as its core data structure to systematically verify all manual mapping relationships. The specific implementation is divided into the following two dimensions: The module takes the two formula dependency trees A and B (converted to DAG structures) output by the formula parsing module as input, and uses the DAG traversal algorithm and difference analysis algorithm to verify the manual mapping. It can automatically detect three types of anomalies: incorrect mapping, repeated mapping, and missing mapping; intermediate data is uniformly stored using DAG and hash tables.

[0089] Analyze the manual formulas in Party B's cells one by one to identify the implicit manual mapping relationships. For example: Party B's cell B1 contains the formula "=VLOOKUP(A1,Sheet1!A:B,2,FALSE)" - extracts the mapping pair (A1-Sheet1!B column cell); If the formula is a direct reference "=Sheet1!A1" - extract the mapping pair (A1-B1).

[0090] All extracted mapping pairs are written into the hash table in real time, with the key being Party A's node address and the value being Party B's node address and the associated formula.

[0091] The formula dependency tree stored in DAG format supports depth-first search (DFS). The module starts from the root node of the total amount of Party A and Party B, traverses down along the dependency path, and compares the mapping value node by node. For example: If Party A's node A1 (calculated value 100) points to Party B's node B1 (calculated value 90) through a mapping edge, it is marked as a potential incorrect mapping.

[0092] This is achieved through DAG in-degree analysis. If a node of Party A has multiple outgoing edges (i.e., in-degree > 1), such as A1-B1 and A1-B2 coexisting, it is immediately marked as a duplicate mapping; Through the access table maintained during the DFS process, if there is a node in Party A that still has no outgoing edges after the entire traversal, it is marked as a missing mapping.

[0093] The module starts from the difference between the total amount of Party A and Party B (Δ = Party A’s total amount – Party B’s total amount) and decomposes the sub-item contributions in reverse along the DAG. For example, Δ = 500 may be decomposed into The difference between A1 and B1 is 100 (incorrect mapping), and A2 is not mapped resulting in a missing value of 400 (missing mapping).

[0094] The node properties of the DAG structure include cell addresses, current values, and formula strings; directed edges represent formula references or manual mapping relationships; the entire structure is stored as an in-memory JSON object or VBACollection.

[0095] The hash table (Dictionary) key is the node address of Party A (such as Sheet1!$A1), and the value is the node address of Party B (such as Sheet2!B$1) and the associated formula.

[0096] The exception list includes a dynamic array to store all verification exceptions. Each exception includes the node address, exception type (error / duplicate / omission), difference amount, and the full text of the associated formula.

[0097] Starting from the total amount node of A and B, perform a full-link verification of the manual mapping relationship along the DAG structure, performing the following steps: Scan all Party B cell formulas in the Fusion workbook and extract explicit or implicit manual mappings.

[0098] Direct reference type: such as B1="=Sheet1!A1", record side (A1-B1); Lookup function type: For example, B1 = "=VLOOKUP(A1,Sheet1!A:B,2,FALSE)", and then record the edge (A1-corresponding matching cell) after parsing the search logic.

[0099] The ExcelRange.Value property is used to read "Party A_Total Amount" and "Party B_Total Amount" and calculate Δ = 10000 - 9500 = 500. The module traverses the DAG in reverse order, breaking the total amount into several sub-item contributions (for example, SUM(A1:A1000)).

[0100] Compare the values ​​of the mapping nodes in the DAG and log an exception if the difference is non-zero; If it is found that the out-degree of a Party A node is greater than 1, it is marked as duplicate; If it is found that Party A’s node has no outgoing edge, it is marked as missing.

[0101] Classify exceptions by source of difference and record the affected amount. Example: A1→B1 difference 100→record "Error mapping, impact amount 100, associated formula =VLOOKUP(...)." The error location module drills down further on the anomaly list output by the mapping relationship verification and analysis module to accurately point out the specific location and root cause of each anomaly in the DAG.

[0102] For each exception (e.g., incorrect mapping A1→B1, difference 100), the module backtracks along the DAG, expanding the formula dependencies hop by hop until it locates the bottom-level cell that causes the difference. For example: A1 (value 100) is mapped to B1 (value 90) via the formula "=VLOOKUP(A1,Sheet1!A:B,2,FALSE)".

[0103] The module continues to trace the dependency path of A1 and finds A1=SUM(C1:C10). It eventually locates the C5 value error and records the path A1-C5-Total Amount.

[0104] Through DAG in-degree analysis, the module identifies multiple outgoing edges from Party A's node (e.g., A1-B1 and A1-B2 coexist). The module compares the formula logic of B1 and B2 and provides retention recommendations (e.g., retain B1 and delete B2).

[0105] Scan Party A's DAG nodes and identify nodes without outgoing edges (such as A2). Combined with field name similarity (such as "equipment fee" vs. "equipment cost"), the Levenshtein distance algorithm is used to recommend the most likely matching fields for Party B.

[0106] Based on the contribution ratio of the exception to the total amount difference Δ (e.g. 100 / 500=20%), a heap sort algorithm is used to sort all exceptions in descending order to ensure that high-impact errors are displayed first.

[0107] The module starts with the exception list of the verification and analysis module and performs the following positioning steps: For the error mapping A1-B1 (difference 100), the module expands the formula dependencies of A1 and B1 along the DAG, drilling down layer by layer until it locates the cell where the difference originates (such as C5). Finally, the complete impact path A1-C5-total amount is recorded.

[0108] Use DAG in-degree analysis to check A1-B1 and A1-B2. The module analyzes the formula logic of B1 and B2 (B1=Sheet1!A1, B2=VLOOKUP(A1,...)) and recommends retaining A1-B1 and deleting A1-B2.

[0109] For Party A's node A2 with no outgoing edges, the module extracts its field name "equipment fee", calculates the string similarity with Party B's field "equipment cost", and recommends mapping A2-B3.

[0110] Module automatically generates executable fixes: Modify the B1 formula to "=Sheet1!A2"; Add a mapping from A2 to B3.

[0111] All recommendations are output after double verification based on field similarity and value consistency.

[0112] S160. Display the verification results in the form of charts and tables.

[0113] The result output module is implemented in the form of an Excel plug-in. It is responsible for presenting all verification results (anomaly lists, error reports, and correction suggestions) generated by the mapping relationship verification and analysis module and the error location module in a structured, visual, and interactive manner, and supports one-click report export.

[0114] The result output module directly consumes the exception list and DAG structure of the error location module, making full use of Excel's native tables, charts, and GUI capabilities to achieve the following four functions: The module automatically generates a "ResultTable" worksheet in the current workbook, displaying exception details row by row: Exception ID; Type (wrong mapping / duplicate mapping / missing mapping); Node address (e.g. Sheet1!$A$1); Difference amount (e.g. 100); The full text of the associated formula (such as "=VLOOKUP(...)"); Correction suggestions (e.g., "Change the B1 formula to =Sheet1!A2"); Data is written in batches through VBARange objects, supporting subsequent sorting and filtering by users (such as descending order by difference).

[0115] The module uses ExcelChart objects to dynamically generate two types of charts: Histogram: Shows the distribution of anomaly types, such as 10 incorrect mappings, 5 duplicate mappings, and 3 missing mappings; DAG subgraph: Visualizes the error propagation path (e.g., A1-C5-total amount) in a node-line manner.

[0116] The chart is embedded in the VBA form and supports zooming, filtering, and highlighting interactions, allowing users to instantly focus on the key path.

[0117] Provide GUI through Excel native controls: Click the abnormal row - automatically jump to the corresponding cell (such as B1); Double-click on a cell - allows to modify the formula directly; After modification - the module refreshes the ResultTable and chart in real time to immediately reflect the correction results.

[0118] All interactive events are driven by VBA events, with zero learning cost, and non-professional users can also quickly complete corrections.

[0119] The module supports one-click export: A copy of the Excel workbook (including the ResultTable and charts); PDF snapshots (including static views of tables and charts).

[0120] The export process is fully automated, ensuring reports are in a uniform format and ready for delivery.

[0121] The method of this embodiment is implemented in the form of a plug-in, integrating five major modules: data input, formula parsing, mapping relationship verification and analysis, error location, and result output. It uses the native functions of spreadsheet software to support the automated verification of more than a thousand complex quotations. Compared with traditional ERP systems or manual verification, the operation threshold is low and the efficiency is improved by about 95%.

[0122] A directed acyclic graph (DAG) is used to recursively parse the total amount formula between Party A and Party B to generate an independent dependency tree. The mapping relationship verification and analysis module verifies manual mapping formulas (such as VLOOKUP) based on DAG traversal, efficiently detecting incorrect mappings, duplicate mappings, and missing mappings, surpassing the limitations of traditional tools that only extract formula strings.

[0123] Through DAG path analysis and priority sorting, the root cause of the anomaly (such as the source cell of the difference) is accurately located, and a detailed report (including the impact path and correction suggestions) is generated. GUI interactive correction formulas are supported, significantly reducing the error handling cost of complex quotations.

[0124] Specifically designed for complex quotation mapping scenarios with more than 1,000 quotation items, it brings the following significant benefits: Significantly improves verification efficiency: By automatically parsing the formula logic and mapping relationships in spreadsheets based on the total amount of Party A and Party B, this method can quickly identify incorrect and missing mappings. Compared to traditional manual verification, verification efficiency is increased several times, reducing the time required to process over a thousand quotations by over 95%, significantly reducing the time and cost of manual verification.

[0125] Improved Mapping Accuracy: This solution systematically identifies mis-mappings and missing items by deeply analyzing complex nested formulas and data dependencies, improving verification accuracy by approximately 70% compared to existing solutions. This effectively avoids inconsistent amounts due to mismatched or missing fields in large-scale engineering projects or procurement quotations, reducing the risk of commercial disputes.

[0126] Enhanced flexibility and adaptability: There are no restrictions on mapping rules. The present invention automatically adapts to the dynamic changes of the templates of both parties A and B, overcoming the lack of flexibility of rule-based automation tools and being suitable for diverse and complex quotation scenarios.

[0127] Lower technical and cost barriers: Built on a widely used spreadsheet platform, it eliminates the need for expensive third-party software or complex database environments, significantly reducing application costs for small and medium-sized enterprises. Users can operate without specialized programming knowledge, resulting in a flat learning curve and easy adoption.

[0128] Improved transparency and traceability of verification: A systematic error location mechanism is provided, allowing users to intuitively identify the root causes of inconsistent amounts (such as specific incorrectly mapped items or missing items). This enhances the transparency and traceability of the verification process, facilitating subsequent audits and problem rectification.

[0129] The aforementioned spreadsheet data verification method achieves efficient analysis and error correction of mapping relationships within complex quotations through an automated process. It first obtains and integrates the data and formula information within the spreadsheet file to be verified. It then analyzes this formula information to construct a dependency tree, tracing the calculation path for each total amount cell. Based on the extracted data, it establishes mapping relationships between the two parties' quotations. Subsequently, it reverse-parses the mapping relationships based on the total amounts of both parties to detect inconsistencies and identify anomalies. These anomalies are then analyzed in depth to identify incorrect or missing mapping items, generating a detailed error report as the verification results. Finally, the verification results are presented in intuitive charts and tables, allowing users to quickly understand and correct errors. This significantly improves the efficiency and accuracy of data verification and effectively protects the interests of both parties to the transaction. This method not only reduces the time cost and error rate of manual verification but also enhances the transparency and traceability of the verification process.

[0130] Figure 5 FIG is a schematic block diagram of an electronic spreadsheet data verification system 300 provided by an embodiment of the present invention. Figure 5As shown, corresponding to the above electronic form data verification method, the present invention also provides an electronic form data verification system 300. The electronic form data verification system 300 includes a unit for executing the above electronic form data verification method, and the system can be configured in a server. Figure 5 The electronic form data checking system 300 includes an acquisition unit 301 , an analysis unit 302 , a mapping relationship establishment unit 303 , a detection unit 304 , a checking unit 305 and a display unit 306 .

[0131] An acquisition unit 301 is used to acquire a spreadsheet file to be verified, and to fuse and extract the data and formula information in the spreadsheet file to be verified; an analysis unit 302 is used to analyze the formula information and construct a dependency tree to trace the calculation path of each total amount cell; a mapping relationship establishment unit 303 is used to establish a mapping relationship between the quotation items of both parties based on the formula information; a detection unit 304 is used to reversely parse the mapping relationship based on the total amount of both parties, detect inconsistencies, and obtain abnormal points; a verification unit 305 is used to analyze the abnormal points to find out erroneous or missing mapping items, and generate an error report to obtain verification results; a display unit 306 is used to display the verification results in the form of charts and tables.

[0132] In one embodiment, the acquiring unit 301 includes: The import sub-unit is used to import the spreadsheet files of both parties using the spreadsheet software API interface to obtain the spreadsheet file to be verified; the fusion sub-unit is used to copy the contents of the spreadsheet file to be verified to the new workbook without loss, keeping the original formulas, styles and names unchanged, and adding a prefix to the conflicting workbook-level names to distinguish the sources to obtain the fusion result; the marking sub-unit is used to select and mark the amount of the fusion result to obtain the formula information.

[0133] In one embodiment, the analysis unit 302 includes: A determination subunit is used to determine the starting parsing point based on the formula information; a string acquisition subunit is used to use the spreadsheet API to obtain the formula string in the specified cell; a parsing subunit is used to convert the formula string into an abstract syntax tree and recursively parse all sub-formulas until the basic value cell. For nested functions and cross-worksheet reference parsing, the reference path is determined through runtime simulation; invalid formulas or circular reference errors in the formula string are monitored and reported to obtain parsing results; an organization subunit is used to organize the parsing results into a multi-branch tree structure, use a hash table to accelerate queries and save the results in a hidden worksheet.

[0134] In one embodiment, the detection unit 304 is configured to locate the specific source of the abnormal value by tracing back the dependency tree in the DAG path when an abnormal mapping exists in the mapping relationship, and record the relevant path to obtain the abnormal point.

[0135] In one embodiment, the verification unit 305 is used to use DAG in-degree analysis to identify and evaluate multiple mappings of one of the nodes, and recommend a single mapping to eliminate redundancy; for one of the unmapped nodes, suggest potential mappings similar to the other field through string matching technology; based on field similarity and numerical consistency, generate specific mapping correction or new addition suggestions to obtain a verification result.

[0136] It should be noted that those skilled in the art can clearly understand that the specific implementation process of the above-mentioned spreadsheet data verification system 300 and each unit can refer to the corresponding description in the aforementioned method embodiment. For the convenience and brevity of description, it will not be repeated here.

[0137] The electronic spreadsheet data checking system 300 can be implemented as a computer program. Figure 6 Runs on the computer equipment shown.

[0138] See also Figure 6 , Figure 6 1 is a schematic block diagram of a computer device provided in an embodiment of the present application. The computer device 500 may be a server, wherein the server may be an independent server or a server cluster composed of multiple servers.

[0139] See Figure 6 The computer device 500 includes a processor 502 , a memory, and a network interface 505 connected via a system bus 501 , wherein the memory may include a non-volatile storage medium 503 and an internal memory 504 .

[0140] The non-volatile storage medium 503 can store an operating system 5031 and a computer program 5032. The computer program 5032 includes program instructions, which, when executed, can cause the processor 502 to execute a spreadsheet data verification method.

[0141] The processor 502 is used to provide computing and control capabilities to support the operation of the entire computer device 500.

[0142] The internal memory 504 provides an environment for the operation of the computer program 5032 in the non-volatile storage medium 503. When the computer program 5032 is executed by the processor 502, the processor 502 can execute a spreadsheet data verification method.

[0143] The network interface 505 is used to communicate with other devices through the network. Figure 6 The structure shown in the figure is merely a block diagram of a portion of the structure related to the solution of the present application, and does not constitute a limitation on the computer device 500 to which the solution of the present application is applied. The specific computer device 500 may include more or fewer components than shown in the figure, or combine certain components, or have a different component arrangement.

[0144] The processor 502 is configured to execute a computer program 5032 stored in the memory to implement the following steps: Obtain a spreadsheet file to be verified, and merge and extract the data and formula information in the spreadsheet file to be verified; analyze the formula information and construct a dependency tree to track the calculation path of each total amount cell; establish a mapping relationship between the quotation items of both parties based on the formula information; starting from the total amount of both parties, reversely parse the mapping relationship and detect inconsistencies to obtain anomalies; analyze the anomalies to find erroneous or missing mapping items, and generate an error report to obtain verification results; present the verification results in the form of charts and tables.

[0145] In one embodiment, the processor 502 implements the following steps when implementing the steps of obtaining the electronic spreadsheet file to be verified and fusing and extracting the data and formula information in the electronic spreadsheet file to be verified: Use the spreadsheet software API interface to import the spreadsheet files of both parties to obtain the spreadsheet file to be verified; copy the contents of the spreadsheet file to be verified to the new workbook without loss, keep the original formulas, styles and names unchanged, add a prefix to the conflicting workbook-level names to distinguish the sources, and obtain the fusion result; select and mark the amount of the fusion result to obtain the formula information.

[0146] In one embodiment, the processor 502 implements the following steps when analyzing the formula information and constructing a dependency tree to track the calculation path steps of each total amount cell: The method includes determining a starting parsing point based on the formula information; obtaining a formula string in a specified cell using a spreadsheet API; converting the formula string into an abstract syntax tree, and recursively parsing all sub-formulas down to the underlying numeric cell. For nested functions and cross-worksheet reference parsing, the reference path is determined through runtime simulation; monitoring and reporting invalid formulas or circular reference errors within the formula string to obtain a parsing result; organizing the parsing result into a multi-tree structure, using a hash table to accelerate queries, and saving the result in a hidden worksheet.

[0147] The multi-branch tree structure includes a plurality of nodes, each of which includes a cell address, a formula or value, a data type and a child node list.

[0148] In one embodiment, when the processor 502 implements the step of reversely parsing the mapping relationship based on the total amount of both parties and detecting inconsistencies to obtain abnormal points, the processor 502 specifically implements the following steps: When an abnormal mapping exists in the mapping relationship, the specific source of the abnormal value is located by tracing back the dependency tree in the DAG path, and the relevant path is recorded to obtain the abnormal point.

[0149] In one embodiment, when the processor 502 implements the step of analyzing the abnormal point to find the erroneous or missing mapping item and generating an error report to obtain the verification result, the processor 502 specifically implements the following steps: DAG in-degree analysis is used to identify and evaluate multiple mappings of one of the nodes, and a single mapping is recommended to eliminate redundancy. For unmapped nodes, string matching technology is used to suggest potential mappings that are similar to the other node's fields. Based on field similarity and numerical consistency, specific mapping corrections or new additions are generated to obtain verification results.

[0150] It should be understood that in the embodiment of the present application, the processor 502 may be a central processing unit (CPU), and the processor 502 may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor may be a microprocessor or any conventional processor, etc.

[0151] Those skilled in the art will appreciate that all or part of the steps in the method of the above-described embodiment can be implemented by instructing the relevant hardware through a computer program. The computer program includes program instructions, which can be stored in a storage medium that is computer-readable. The program instructions are executed by at least one processor in the computer system to implement the steps in the method of the above-described embodiment.

[0152] Therefore, the present invention also provides a storage medium. The storage medium may be a computer-readable storage medium. The storage medium stores a computer program, wherein when the computer program is executed by a processor, the processor performs the following steps: Obtain a spreadsheet file to be verified, and merge and extract the data and formula information in the spreadsheet file to be verified; analyze the formula information and construct a dependency tree to track the calculation path of each total amount cell; establish a mapping relationship between the quotation items of both parties based on the formula information; starting from the total amount of both parties, reversely parse the mapping relationship and detect inconsistencies to obtain anomalies; analyze the anomalies to find erroneous or missing mapping items, and generate an error report to obtain verification results; present the verification results in the form of charts and tables.

[0153] In one embodiment, when the processor executes the computer program to implement the steps of obtaining the electronic spreadsheet file to be verified and fusing and extracting the data and formula information in the electronic spreadsheet file to be verified, the processor specifically implements the following steps: Use the spreadsheet software API interface to import the spreadsheet files of both parties to obtain the spreadsheet file to be verified; copy the contents of the spreadsheet file to be verified to the new workbook without loss, keep the original formulas, styles and names unchanged, add a prefix to the conflicting workbook-level names to distinguish the sources, and obtain the fusion result; select and mark the amount of the fusion result to obtain the formula information.

[0154] In one embodiment, when the processor executes the computer program to analyze the formula information and construct a dependency tree to track the calculation path steps of each total amount cell, the processor specifically implements the following steps: The method includes determining a starting parsing point based on the formula information; obtaining a formula string in a specified cell using a spreadsheet API; converting the formula string into an abstract syntax tree, and recursively parsing all sub-formulas down to the underlying numeric cell. For nested functions and cross-worksheet reference parsing, the reference path is determined through runtime simulation; monitoring and reporting invalid formulas or circular reference errors within the formula string to obtain a parsing result; organizing the parsing result into a multi-tree structure, using a hash table to accelerate queries, and saving the result in a hidden worksheet.

[0155] The multi-branch tree structure includes a plurality of nodes, each of which includes a cell address, a formula or value, a data type and a child node list.

[0156] In one embodiment, when the processor executes the computer program to implement the step of reversely parsing the mapping relationship based on the total amount of both parties, detecting inconsistencies, and obtaining abnormal points, the processor specifically implements the following steps: When an abnormal mapping exists in the mapping relationship, the specific source of the abnormal value is located by tracing back the dependency tree in the DAG path, and the relevant path is recorded to obtain the abnormal point.

[0157] In one embodiment, when the processor executes the computer program to implement the step of analyzing the abnormal point to find the erroneous or missing mapping item and generating an error report to obtain the verification result, the processor specifically implements the following steps: DAG in-degree analysis is used to identify and evaluate multiple mappings of one of the nodes, and a single mapping is recommended to eliminate redundancy. For unmapped nodes, string matching technology is used to suggest potential mappings that are similar to the other node's fields. Based on field similarity and numerical consistency, specific mapping corrections or new additions are generated to obtain verification results.

[0158] The storage medium may be any computer-readable storage medium that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a magnetic disk, or an optical disk.

[0159] Those skilled in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the above description has generally described the composition and steps of each example according to function. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.

[0160] In the several embodiments provided herein, it should be understood that the disclosed systems and methods can be implemented in other ways. For example, the system embodiments described above are merely illustrative. For example, the division of the various units is merely a logical functional division, and actual implementations may employ other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be omitted or not implemented.

[0161] The steps in the method of the embodiment of the present invention may be adjusted in order, combined, or deleted as needed. The units in the system of the embodiment of the present invention may be combined, divided, or deleted as needed. In addition, the functional units in the various embodiments of the present invention may be integrated into a single processing unit, each unit may exist physically separately, or two or more units may be integrated into a single unit.

[0162] If this integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a storage medium. Based on this understanding, the technical solution of the present invention, or the portion that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product, stored in a storage medium, includes instructions for enabling a computer device (such as a personal computer, terminal, or network device) to execute all or part of the steps of the method described in various embodiments of the present invention.

[0163] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in the present invention, and such modifications or substitutions are intended to be within the scope of protection of the present invention. Therefore, the scope of protection of the present invention shall be subject to the scope of protection of the claims.

Claims

1. A method for verifying electronic spreadsheet data, characterized in that: include: Obtaining a spreadsheet file to be verified, and fusing and extracting data and formula information in the spreadsheet file to be verified; Analyze the formula information and build a dependency tree to trace the calculation path of each total amount cell; Establishing a mapping relationship between the quotation items of both parties based on the formula information; Starting from the total amount of both parties, reversely analyze the mapping relationship, detect inconsistencies, and obtain anomalies; Analyze outliers to find incorrect or missing mapping items and generate error reports to obtain verification results; The verification results are presented in the form of charts and tables.

2. The electronic spreadsheet data verification method according to claim 1, characterized in that: The step of obtaining the electronic spreadsheet file to be checked, and fusing and extracting the data and formula information in the electronic spreadsheet file to be checked includes: Import the spreadsheet files of both parties using the spreadsheet software API interface to obtain the spreadsheet file to be verified; Copy the contents of the spreadsheet file to be verified into a new workbook without loss, keeping the original formulas, styles, and names unchanged, and add a prefix to the conflicting workbook-level names to distinguish the sources, so as to obtain the fusion result; The fusion result is subjected to amount selection and marking to obtain formula information.

3. The electronic spreadsheet data verification method according to claim 1, wherein: The step of analyzing the formula information and constructing a dependency tree to track the calculation path of each total amount cell includes: Determine a starting parsing point according to the formula information; Use the spreadsheet API to get the formula string in the specified cell; Convert the formula string into an abstract syntax tree and recursively parse all sub-formulas down to the underlying numeric cells, supporting nested functions and cross-worksheet reference parsing; monitor and report invalid formulas or circular reference errors within the formula string to obtain parsing results; The parsing results are organized into a multi-branch tree structure, a hash table is used to accelerate the query, and the results are stored in a hidden work table.

4. The electronic spreadsheet data verification method according to claim 3, characterized in that: The multi-branch tree structure includes a plurality of nodes, each of which includes a cell address, a formula or value, a data type, and a child node list.

5. The electronic spreadsheet data verification method according to claim 1, characterized in that: Starting from the total amount of both parties, reverse parsing the mapping relationship, detecting inconsistencies, and obtaining abnormal points, includes: When an abnormal mapping exists in the mapping relationship, the specific source of the abnormal value is located by tracing back the dependency tree in the DAG path, and the relevant path is recorded to obtain the abnormal point.

6. The electronic spreadsheet data verification method according to claim 1, wherein: The analysis of abnormal points finds out incorrect or missing mapping items and generates an error report to obtain verification results, including: DAG in-degree analysis is used to identify and evaluate multiple mappings of one of the nodes, and a single mapping is recommended to eliminate redundancy. For unmapped nodes, string matching technology is used to suggest potential mappings that are similar to the other node's fields. Based on field similarity and numerical consistency, specific mapping corrections or new additions are generated to obtain verification results.

7. Electronic spreadsheet data verification system, characterized in that, include: an acquisition unit, configured to acquire a spreadsheet file to be checked, and merge and extract data and formula information in the spreadsheet file to be checked; an analysis unit, configured to analyze the formula information and construct a dependency tree to trace the calculation path of each total amount cell; A mapping relationship establishing unit, configured to establish a mapping relationship between the quotation items of both parties based on the formula information; A detection unit is used to reversely analyze the mapping relationship based on the total amount of both parties, detect inconsistencies, and obtain abnormal points; A verification unit is used to analyze abnormal points to find incorrect or missing mapping items and generate an error report to obtain verification results; The display unit is used to display the verification results in the form of charts and tables.

8. A computer device, characterized in that: The computer device includes a memory and a processor, the memory stores a computer program, and the processor implements the method according to any one of claims 1 to 6 when executing the computer program.

9. A storage medium, characterized in that: The storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images