Spreadsheet data verification method, system, computer device and storage medium

Abstract

The application discloses an electronic form data checking method and system, a computer device and a storage medium. The method comprises the following steps: obtaining an electronic form file to be checked, and fusing and extracting data and formula information in the electronic form file to be checked; analyzing the formula information, constructing a dependency tree to track the calculation path of each total amount cell; establishing a mapping relationship between bid items of two parties based on the formula information; starting from the total amount of the two parties, inversely analyzing the mapping relationship, detecting inconsistent points to obtain abnormal points; analyzing the abnormal points to find out the missing mapping items, and generating an error report to obtain a checking result; and displaying the checking result in the form of a chart and a table. The method can automatically analyze the mapping relationship in a complex bid form, quickly locate and correct errors, thereby improving the efficiency and accuracy of data checking.

Description

TECHNICAL FIELD

[0001] The present application relates to a data processing method, in particular to an electronic form data checking method and system, a computer device and a storage medium. BACKGROUND

[0002] In today's business environment, a quotation sheet is used as a key tool for supply chain management, bidding and procurement, etc., to clarify the price terms between the two parties. As the business scale of enterprises expands, the quotation sheet becomes more and more complex, especially when more than 1000 quotation items are involved, the huge amount of data and complex structure put forward higher requirements for the processing of the quotation sheet. For example, in large engineering projects or equipment procurement, Party A such as the procurement party usually uses a quotation template of a specific format, which contains detailed classification, calculation rules and data format; Party B such as the supplier may need to quote according to their own template. Due to the differences between the templates of the two parties in terms of structural design, field naming and calculation logic, accurate mapping of quotation data becomes an important step.

[0003] The traditional processing method of the quotation sheet mainly relies on spreadsheet software such as Microsoft Excel or Google Sheets, which uses its flexible formula function to assist data mapping and checking. Although this method is feasible for a small number of quotation items, in the face of a large amount of data and significant template differences, the traditional method often finds it difficult to complete the task efficiently and accurately. Manually comparing quotation items one by one not only consumes time and effort, but also is prone to problems such as incorrect mapping or missing mapping, leading to inconsistent amounts and thus causing business disputes or project delays.

[0004] In order to solve these problems, several solutions have appeared in the market, such as manual checking and mapping, which is suitable for situations with fewer quotation items, but as the complexity of the quotation sheet increases, the efficiency and accuracy of manual operation decrease significantly. Rule-based automated tools achieve automatic quotation sheet mapping through predefined mapping rules, which improves efficiency, but has poor adaptability to dynamically changing templates. Third-party data processing software provides powerful data conversion capabilities, but due to its strong universality, it is not optimized for the quotation sheet mapping scenario, and the cost is high. Database-based checking methods are suitable for highly structured data environments, but they face challenges when dealing with unstructured data and are difficult to analyze complex formula dependencies.

[0005] However, the above-mentioned solutions have obvious limitations in processing complex quotation sheets, including but not limited to inefficient manual operation, lack of flexibility of automated tools, high cost, and insufficient ability to analyze complex formula logic.

[0006] Therefore, it is necessary to design a new method to realize automatic analysis of mapping relationship in complex quotation sheet, quickly locate and correct errors, so as to improve the efficiency and accuracy of data checking, and protect the interests of both parties. SUMMARY

[0007] The present application aims to overcome the defects of the prior art, and provide an electronic table data checking method, system, computer device and storage medium.

[0008] To achieve the above object, the present application adopts the following technical scheme: an electronic table data checking method, comprising:

[0009] Obtain the to-be-checked electronic table file, and fuse and extract data and formula information in the to-be-checked electronic table file;

[0010] Analyze the formula information, build a dependency tree, and track the calculation path of each total amount cell;

[0011] Based on the formula information, establish the mapping relationship between the two parties' quotation items;

[0012] Starting from the total amount of both parties, reverse analyze the mapping relationship, detect inconsistent points, and obtain abnormal points;

[0013] Analyze the abnormal points to find out the missing or missing mapping items, and generate an error report to obtain the checking result;

[0014] The checking result is displayed in the form of a chart and a table.

[0015] Further technical solutions are as follows: the to-be-checked electronic table file is obtained, and the data and formula information in the to-be-checked electronic table file are fused and extracted, comprising:

[0016] Import the electronic table files of both parties by using the API interface of the electronic table software to obtain the to-be-checked electronic table file;

[0017] The contents of the to-be-checked electronic table file are losslessly copied to a new workbook, the original formula, style and name are kept unchanged, a prefix is added before the workbook level name that exists conflict to distinguish the source, and a fusion result is obtained;

[0018] Amount selection and marking are performed on the fusion result to obtain formula information.

[0019] Further technical solutions are as follows: the formula information is analyzed, a dependency tree is built, and the calculation path of each total amount cell is tracked, comprising:

[0020] Determine the starting analysis point according to the formula information;

[0021] Obtaining a formula string in a specified cell by using a spreadsheet API;

[0022] Converting the formula string into an abstract syntax tree and recursively resolving all sub-formulas until a base numerical cell, resolving nested functions and cross-worksheet references by runtime simulation to determine reference paths; monitoring and reporting invalid formula or circular reference errors within the formula string to obtain a resolution result;

[0023] Organizing the resolution result into a multi-way tree structure, using a hash table to speed up queries and saving the result in a hidden worksheet.

[0024] Further technical solutions thereof are that the multi-way tree structure includes a plurality of nodes, and each node contains a cell address, a formula or value, a data type, and a list of child nodes.

[0025] Further technical solutions thereof are that the mapping relationship is resolved in reverse from the total amounts of both parties to detect inconsistent points to obtain abnormal points, including:

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

[0027] Further technical solutions thereof are that the abnormal point is analyzed to find the missing or missing mapping item, and an error report is generated to obtain a verification result, including:

[0028] A plurality of mappings of one of the nodes are identified and evaluated by using DAG in-degree analysis, a single mapping is recommended to eliminate redundancy; for the unmapped one of the nodes, a potential mapping similar to the field of the other party is suggested by using string matching technology; based on field similarity and numerical consistency, specific mapping correction or addition suggestions are generated to obtain a verification result.

[0029] The application also provides a spreadsheet data verification system, including:

[0030] An obtaining unit is configured to obtain a to-be-verified spreadsheet file and fuse and extract data and formula information in the to-be-verified spreadsheet file;

[0031] An analysis unit is configured to analyze the formula information and construct a dependency tree to track the calculation path of each total amount cell;

[0032] A mapping relationship establishing unit is configured to establish a mapping relationship between bid items of both parties based on the formula information;

[0033] A detection unit is configured to resolve the mapping relationship in reverse from the total amounts of both parties to detect inconsistent points to obtain abnormal points;

[0034] a checking unit configured to analyze the abnormal points to find out the erroneous or missing mapping items and generate an error report to obtain a checking result;

[0035] a display unit configured to display the checking result in the form of a chart and a table.

[0036] The application further provides a computer device comprising a memory and a processor, wherein the memory stores a computer program, and the processor implements the method when executing the computer program.

[0037] The application further provides a storage medium storing a computer program, wherein the computer program is executed by a processor to implement the method.

[0038] Compared with the prior art, the application has the following beneficial effects: the application realizes efficient analysis and error correction of the mapping relationship in a complex quotation sheet through an automatic process, first acquires and fuses data and formula information in an electronic sheet file to be checked, then analyzes the formula information to construct a dependency tree, traces a calculation path of each total amount cell, and establishes a mapping relationship between items of quotations of two parties based on the extracted data. Subsequently, the mapping relationship is analyzed in reverse from the total amounts of the two parties to detect inconsistent points, determine abnormal points, and analyze the abnormal points in depth to find out erroneous or missing mapping items, and generate a detailed error report as a checking result. Finally, the checking result is displayed in the form of an intuitive chart and table, so that a user can quickly understand and correct errors, thereby greatly improving the efficiency and accuracy of data checking and effectively protecting the interests of the two parties in a transaction. This method not only reduces the time cost and error rate of manual checking, but also enhances the transparency and traceability of the checking process.

[0039] The application will be further described below in combination with the drawings and specific embodiments. BRIEF DESCRIPTION OF DRAWINGS

[0040] In order to more clearly illustrate the technical solutions of the embodiments of the application, the drawings needed in the embodiment description will be briefly introduced. Obviously, the drawings in the following description are some embodiments of the application, and other drawings can be obtained by those skilled in the art without creative labor.

[0041] Figure 1 An application scenario diagram of the electronic sheet data checking method provided by the embodiments of the application;

[0042] Figure 2 A flowchart of the electronic sheet data checking method provided by the embodiments of the application;

[0043] Figure 3A sub-flow schematic diagram of the electronic table data checking method provided by the embodiment of the present application is shown in FIG. 1.

[0044] Figure 4 A sub-flow schematic diagram of the electronic table data checking method provided by the embodiment of the present application is shown in FIG. 1.

[0045] Figure 5 A schematic block diagram of the electronic table data checking system provided by the embodiment of the present application is shown in FIG. 2.

[0046] Figure 6 A schematic block diagram of the computer device provided by the embodiment of the present application is shown in FIG. 3. DETAILED DESCRIPTION

[0047] The technical solutions in the embodiments of the present application will be clearly and completely described with reference to the accompanying drawings in the embodiments of the present application. Obviously, the described embodiments are some but not all of the embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative work fall within the protection scope of the present application.

[0048] It should be understood that, when used in the specification and the appended claims, the terms "comprise" and "include" indicate the presence of the described features, integers, steps, operations, elements, and / or components, but do not exclude one or more other features, integers, steps, operations, elements, components, and / or groups thereof.

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

[0050] It should be further understood that the term "and / or" used in the present application specification and the appended claims means any combination of one or more of the associated listed items and all possible combinations thereof, and includes these combinations.

[0051] Please refer to Figure 1 and Figure 2 , Figure 1 An application scenario schematic diagram of the electronic table data checking method provided by the embodiment of the present application is shown in FIG. 4. Figure 2An illustrative flowchart of an electronic spreadsheet data verification method provided by an embodiment of the present application. The electronic spreadsheet data verification method is applied in a server. The server interacts with terminals for data, acquires and fuses data and formula information of electronic spreadsheet files through an automated tool, constructs a dependency tree to track the calculation path of each total amount cell, and establishes a mapping relationship between bid items of both parties based on data analysis. Then, the mapping relationship is analyzed in reverse from the total amount of both parties to detect inconsistent points, thereby locating the specific source of abnormal values. Repetitive mapping is identified using DAG in-degree analysis and the best option is recommended, while un-mapped items are suggested for potential mapping 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 displays the verification results in the form of charts and tables, realizes fast correction of errors, improves data verification efficiency and accuracy, and ensures the interests of both parties are protected.

[0052] Figure 2 is a flowchart of an electronic spreadsheet data verification method provided by an embodiment of the present application. As shown in Figure 2 , the method comprises the following steps S110 to S160.

[0053] S110, acquiring an electronic spreadsheet file to be verified, and fusing and extracting data and formula information in the electronic spreadsheet file to be verified.

[0054] In this embodiment, the electronic spreadsheet file to be verified refers to the electronic spreadsheet files of the bid sheets provided by Party A and Party B. These files usually contain data of bid items (such as unit price, quantity, total amount, etc.) and their calculation logic (expressed by formulas). Data refers to specific numerical or textual information in the electronic spreadsheet, such as the name, quantity, unit price, total price, etc. of the bid item. Formula information refers to mathematical expressions or functions used to calculate the total amount of bid items or other intermediate results, such as SUM, VLOOKUP, etc.

[0055] In an embodiment, referring to Figure 3 , the above step S110 can comprise steps S111-S113.

[0056] S111, importing the electronic spreadsheet files of both parties using the API interface of the electronic spreadsheet software to obtain the electronic spreadsheet file to be verified.

[0057] In this embodiment, this step involves using the application program interface (API) provided by the electronic spreadsheet software to automatically import the electronic spreadsheet files of Party A and Party B. The purpose of this is to be able to automatically process and analyze the data and formulas in these files without human intervention. In this way, the system can effectively obtain the original data source to be verified.

[0058] S112, non-destructively copy the contents of the electronic spreadsheet files to be checked into a new workbook, keeping the original formulas, styles, and names unchanged, and adding prefixes to workbook-level names that exist in conflict to distinguish the sources, to obtain the fusion result.

[0059] At this stage, the system creates a new spreadsheet workbook and copies the contents of the electronic spreadsheets obtained from both parties into this new workbook. In this process, all original formulas, formats, and naming conventions are preserved, ensuring data consistency and accuracy. If there is a name conflict (for example, both workbooks use the same name), a specific prefix (such as "CONFLICT_A_" or "CONFLICT_B_") will be added to the name to clearly distinguish the workbook-level name from different sources. This approach ensures that even in the presence of name conflicts, subsequent data parsing and mapping verification can be accurately and reliably performed.

[0060] In this embodiment, the fusion result refers to the new workbook generated after the above steps, which contains all the information from both parties' quotation sheets, including but not limited to data, formulas, styles, and names, while solving any potential name conflict problems. This fusion result provides a complete and structured data foundation for subsequent steps, making further formula parsing, mapping relationship construction, and verification possible.

[0061] S113, select and mark the amounts in the fusion result to obtain formula information.

[0062] The final step is to select and mark all key cells related to the calculation of the amount (such as the total amount cell) in the fused electronic spreadsheet content. This step also includes identifying the formulas in these cells and marking them appropriately to facilitate subsequent parsing and analysis. For example, by using the name manager of the electronic spreadsheet software, unique identifiers (such as "Party A_Total Amount" and "Party B_Total Amount") are assigned to the key total amount cells, ensuring that these cells can be stably referenced in subsequent operations without being affected by table modifications. In addition, the formula information corresponding to each amount cell is recorded as the basis for the next formula parsing.

[0063] In this embodiment, the electronic spreadsheet files of the Party A and Party B quotation sheets are imported and merged, and the total amount of the Party A and Party B is marked, to provide basic data for subsequent formula analysis and mapping verification. The module is integrated into the electronic spreadsheet software environment in the form of an electronic spreadsheet software plug-in, fully utilizes the native functions of the electronic spreadsheet software (such as workbook operation, cell management and name manager) to realize functions such as file fusion and total amount marking, and provides interactive support through a custom user interface (GUI). The module not only supports complex quotation sheets (more than 1000 quotation items), but also is compatible with multiple electronic spreadsheet file formats, and enhances system robustness and user experience through intelligent design.

[0064] The electronic spreadsheet files of the Party A and Party B are imported using the API interface provided by the electronic spreadsheet software. This step ensures automated processing without the need for manual intervention to obtain the data source to be analyzed.

[0065] After importing, the module copies all the worksheets in the Party A file and the worksheets in the Party B electronic spreadsheet into a new workbook, keeping the formulas, styles and names in the original files unchanged. This process is completed in the electronic spreadsheet environment, which can efficiently process workbooks with multiple worksheets and more than a thousand items of data, and generate a unified workbook in only 3-5 seconds, with an efficiency improvement of about 90% compared to manual copying and pasting. At the same time, in order to ensure data consistency, the system retains the native formula function of the electronic spreadsheet.

[0066] If there is a workbook-level name conflict in the Party A and Party B files, the module will automatically adjust these names, modifying them to “CONFLICT_A_original name” or “CONFLICT_B_original name”, i.e. adding a prefix to the conflicting name indicating the source (from Party A or Party B). This step ensures that even if there is a name conflict, subsequent data analysis and mapping verification can be accurately and correctly performed.

[0067] An interactive interface is provided, allowing users to select the total amount cells of Party A and Party B by mouse clicking or directly inputting cell addresses (such as C1, D2). The interface displays the value and formula of the selected cell in real time (such as “=SUM(A1:A1000)”), so that the user can confirm the accuracy of the selection.

[0068] To improve system robustness and avoid reference invalidation due to table modifications such as inserting rows / columns, the module uses the name manager of the electronic spreadsheet software to assign unique names to the selected total amount cells (such as “Party A_total amount” and “Party B_total amount”). This naming method is not only stable, but also stored in the workbook, making it effective across sessions.

[0069] The name references are saved as metadata in the Names collection of the Excel workbook, which means that they do not need to be re-labeled every time the audit is performed. The plugin checks the Names collection when it starts to automatically load existing labels, such as "Party A Total Amount" and "Party B Total Amount". This greatly reduces the need for repetitive configuration and improves audit efficiency by about 20%.

[0070] If the user does not select the total amount or selects an invalid cell (such as a text cell), the plugin will pop up a warning through the GUI, suggesting that the user reselect. In addition, intelligent recommendation functions are provided, such as scanning cells containing SUM or SUMIFS formulas, to assist users in making correct selections.

[0071] Through the above technical implementation, the stability of the total amount reference is ensured, and its robustness is better than that of traditional fixed address references. At the same time, the function of automatically loading labels reduces the time for repetitive configuration, further improving work efficiency.

[0072] S120, analyze the formula information and build a dependency tree to track the calculation path of each total amount cell.

[0073] In this embodiment, the dependency tree refers to a multi-way tree structure generated by recursively parsing formulas, which records the reference relationship between cells and is used to track the complete calculation path of each total amount cell.

[0074] In order to accurately analyze and audit the total amount cells of the offer sheet, the system uses a method based on directed acyclic graph (DAG) to build the dependency tree. This method not only efficiently parses complex nested formulas and cross-worksheet references, but also ensures accurate positioning of incorrect or missing mappings.

[0075] In an embodiment, please refer to Figure 4 The above step S120 can include steps S121-S124.

[0076] S121, determine the starting parsing point according to the formula information.

[0077] In this embodiment, this step first needs to identify and label the total amount cells in the Party A and Party B offer sheets. These cells usually contain complex formula references such as SUM, SUMIFS, etc., used to aggregate various fees. Through the form of an Excel plugin, users can select and label these total amount cells (such as "Party A Total Amount" and "Party B Total Amount") as the starting point for the subsequent parsing process.

[0078] S122, use the Excel API to obtain the formula string in the specified cell.

[0079] In this embodiment, once the starting parsing point is determined, the module will use the API interface provided by the spreadsheet software to extract the formula string in 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 will extract the entire formula string and its corresponding cell address.

[0080] S123, convert the formula string into an abstract syntax tree (AST) and recursively parse all sub-formulas until the base numerical cell. For nested functions and cross-sheet references, determine the reference path through runtime simulation. Monitor and report invalid formula or circular reference errors within the formula string to obtain the parsing result.

[0081] In this embodiment, the parsing result refers to the detailed information set containing cell addresses, formula strings or values, data types, and their dependencies obtained by recursively parsing all related sub-formulas from the total amount cell to the base numerical cell.

[0082] Convert the formula string into 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 the root node SUM and the child node A1:A1000.

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

[0084] Handle nested functions and cross-sheet references: For more complex formulas (such as SUMIFS, VLOOKUP, etc.), the module will determine its reference path through runtime simulation. 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".

[0085] Exception detection: During this process, the module will also check for invalid formulas (such as #REF!, #VALUE!) or circular references (such as A1 referencing itself). Once such problems are found, the system will prompt the user to correct them through the graphical user interface (GUI) and record the relevant exception path.

[0086] S124, organize the parsing result into a multi-way tree structure, use a hash table to speed up the query and save the result in a hidden worksheet.

[0087] The results after parsing will be organized in the form of a multi-way tree, where each node represents a cell or formula, and edges represent the reference relationship between formulas. This structure allows fast traversal and query of dependencies. To further improve query efficiency, the system will also use a hash table to cache the mapping of cell addresses to 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 (e.g. named "DependencyTree") to facilitate subsequent verification and error positioning operations. This way ensures consistency and continuity even between different sessions.

[0088] Through the above steps, the embodiment provides an efficient and accurate formula parsing method, which provides a solid foundation for subsequent mapping relationship verification analysis. This method not only improves the efficiency of processing complex quote sheets with more than 1000 items, but also enhances the robustness and user experience of the system.

[0089] In this embodiment, the formula logic inside the cells marked as "total amount" in the buyer's and seller's quote sheets (usually named "Buyer_TotalAmount" and "Seller_TotalAmount") is completely, losslessly, and end-to-end parsed, and two independent, clear structure formula dependency trees are generated accordingly. These two trees are the underlying foundation for subsequent mapping verification, difference positioning, and error tracking, and are the fundamental guarantee for the system to maintain high precision and robustness in high complexity scenarios. The module is completely dependent on the native capabilities of the spreadsheet software in design, starting from the marked total amount cells, recursively descending along the formula chain until it encounters "atomic" cells that no longer contain any formulas. Finally, the entire dependency link is abstracted into a tree-shaped data structure that can be queried, persisted, and backtracked, naturally supporting SUMIFS, VLOOKUP, INDEX-MATCH, and any level of nested functions, as well as dynamic references across sheets and files, easily handling extreme scenarios with more than 1000 quote items.

[0090] Specifically, first, get the two cells pointed by Excel Name Manager, "Buyer_TotalAmount" and "Seller_TotalAmount" (example: name "Buyer_TotalAmount" points to Sheet1!$C$1), then perform the same recursive parsing process on both, and finally get two independent dependency trees. The parsing process consists of the following three steps:

[0091] Read the complete formula string of the "Buyer_TotalAmount" cell C1 through the official API of the spreadsheet software, for example: =SUM(A1:A1000)+IF(B1>0,C2,0)

[0092] At the same time, the absolute address of the cell Sheet1! $C$1 is recorded as the root node identifier for subsequent tree nodes.

[0093] The module is built-in with a self-developed Recursive Descent Parser, which can complete the full-link conversion of "formula string -> Abstract Syntax Tree AST -> dependency tree" at one time. The internal sub-steps are as follows:

[0094] Split the formula string into AST: the root node is a function or operator, and the child node is a parameter or reference interval. For example, the root node of "=SUM(A1:A1000)" is SUM, and the child node is the interval node A1:A1000.

[0095] For each reference interval or cell, detect again whether it contains a formula; if it contains a formula (such as A1 inside "=B1+B2"), continue to recurse until it hits a pure numerical or text cell that does not contain a formula (such as B1 being a numerical value 100).

[0096] For complex functions such as SUMIFS, VLOOKUP, INDEX-MATCH, the parser also disassembles layer by layer: take "=SUMIFS(A1:A1000, B1:B1000, "equipment fee")" as an example, it is divided into three sub-nodes: data interval A1:A1000, condition interval B1:B1000, and text constant "equipment fee".

[0097] For cross-sheet references (such as Sheet2!A1) or dynamic range functions (such as OFFSET, INDIRECT), the module calls a method similar to Excel Evaluate to calculate its real reference interval at runtime, and fixes the calculation result to the tree node, ensuring that the tree structure is complete and reproducible.

[0098] During the parsing process, if an invalid formula (#REF!, #VALUE!), circular reference (A1 refers to A1), or other unparsable syntax is detected, the module immediately prompts the user through a GUI popup, such as "circular reference detected, please check cell A1"; at the same time, the exception path is written into the log, ensuring that the user can trace and correct it. The entire recursive parsing link ensures 100% traceability from the total amount to the bottommost cell, supporting any complex formula and over a thousand data scenarios. The parsing time of a single quotation is usually controlled within a few seconds, and the efficiency is improved by about 95% compared with manual visual analysis.

[0099] In order to ensure performance while considering persistence and easy query, the module uses a tree data structure to carry the analysis results of Party A and Party B:

[0100] The tree is a multi-way tree, each node corresponds to a cell or a formula fragment; nodes are connected by "reference" to form directed edges. Node attributes include:

[0101] Cell absolute address (such as Sheet1! $C$1);

[0102] Formula string (such as "=SUM(A1:A1000)" or pure value (if no formula));

[0103] Data type label (numeric / text / formula);

[0104] Sub-node list (directly referenced cell or interval address array).

[0105] After parsing, the entire tree exists in the form of a memory object, and the nodes are organized in a hash table with "key = cell address, value = node complete attribute"; then the entire tree is serialized to the hidden Sheet (default name "DependencyTree") of the Excel workbook, each row represents a node, and the fields are address, formula, and sub-node address sequence in turn, realizing cross-session persistence without secondary parsing.

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

[0107] The system maintains a "cell address-tree node pointer" hash cache internally, and any query for cell dependency paths can be completed in O(1) time.

[0108] The tree structure is saved through a hidden worksheet and stored with the workbook, which is effective across sessions and avoids performance loss caused by repeated parsing.

[0109] The tree structure is naturally suitable for expressing nested reference relationships, and compared with linear arrays or linked lists, the average memory usage is reduced by about 30%; at the same time, it supports fast traversal in depth-first and breadth-first manner, and the average query time is less than 0.1 second.

[0110] In the embodiment, the multi-way tree structure includes a plurality of nodes, and each node includes a cell address, a formula or a value, a data type, and a sub-node list.

[0111] Specifically, the multi-ary tree structure is a highly efficient data organization form, which is specially used to represent and manage the complex dependency relationship between cells in a spreadsheet. Each node in this tree structure represents a specific cell or its corresponding formula logic, which records the address of the cell (e.g. Sheet1!CC1), the contained formula string (such as "=SUM(A1:A1000)" or direct value (if the cell does not contain any formula), and data type (numeric, text or formula). In addition, each node also maintains a list of child nodes, which describes all the reference paths from the current cell, i.e. other cells that are directly or indirectly referenced by the formula in the current cell. In this way, not only can the hierarchical relationship between each cell and its reference cells be clearly shown, but also the dependency relationship of the entire formula can be quickly traversed and queried, thereby providing strong support for solving potential error positioning, mapping verification and other problems. This structure is particularly suitable for processing spreadsheets with complex nested relationships and large-scale references, improving analysis efficiency and accuracy.

[0112] S130, based on the formula information, establishing a mapping relationship between the two bid items.

[0113] In this embodiment, the establishment of the mapping relationship means that the mapping relationship between the two bid items is extracted and recorded by analyzing the manual formula in the cells of one party (usually Party B). For example, if the cells of Party B contain a VLOOKUP function, which references a cell or range of Party A, then this reference is a mapping relationship. These mapping relationships are recorded as basic data for subsequent verification. In order to enhance the degree of automation, the system also supports automatic matching function, such as preliminary pairing based on field name similarity, and allows users to manually adjust to ensure accuracy.

[0114] Based on the data analysis of the manual formula in the cells of one party, the mapping relationship between the two parties is extracted and recorded.

[0115] S140, starting from the total amount of both parties, reverse analyzing the mapping relationship to detect inconsistent points to obtain abnormal points.

[0116] In this embodiment, the detection of abnormal points is achieved by reversing the mapping relationship from the total amount of both parties. This step utilizes the previously constructed formula dependency tree (DAG), tracing the value of each mapping node along the dependency path, and comparing the calculation results of the corresponding nodes of both parties. When a difference is found, the system will mark it as a potential abnormal point and analyze its dependency tree in depth to locate the specific source of the abnormal value. For example, if the total amount of Party A is 10,000 yuan and the total amount of Party B is 9,500 yuan, the difference is 500 yuan. The system will decompose this difference along the dependency path to find the specific cell and its reason that causes the difference.

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

[0118] S150, analyze the abnormal points to find the error or missing mapping items, and generate an error report to obtain the verification result.

[0119] In this embodiment, the generation of the verification result involves further analysis of the abnormal points, including using DAG in-degree analysis to identify whether a party node has multiple mappings (repeated mapping) and whether there is an unmapped node (missing mapping). For the case of repeated mapping, the system recommends retaining the most suitable single mapping to eliminate redundancy; for missing mapping, the system suggests a potential mapping similar to the other party's field through string matching technology (such as Levenshtein distance algorithm). Finally, based on field similarity and numerical consistency, the system generates detailed error reports, including specific mapping correction or addition suggestions. These reports not only point out the existing problems, but also provide feasible solutions to help users quickly correct errors and improve the consistency and accuracy of the quotation sheet.

[0120] Using DAG in-degree analysis to identify and evaluate multiple mappings of a party node, recommending a single mapping to eliminate redundancy; for unmapped party nodes, suggesting a potential mapping similar to the other party's field through string matching technology; based on field similarity and numerical consistency, generating specific mapping correction or addition suggestions to obtain the verification result.

[0121] In this embodiment, the verification analysis is the central verification component of the system, which is completely based on the formula dependency trees (in the form of DAG) generated by the formula parsing module. It performs systematic verification on the mapping relationship between the offer items of Party A and Party B established by the offer personnel through manual formula, can automatically find incorrect mapping (such as repeated mapping), missing mapping and other potential mapping defects, and provide detailed basis for the subsequent error positioning module. The module continues to make full use of the native calculation and data access capabilities of the spreadsheet software, and fully supports complex nested formulas (SUMIFS, VLOOKUP, INDEX-MATCH, etc.) and dynamic references across sheets or even across workbooks in large-scale scenarios with more than 1000 offer items. The module allows users to intuitively view the mapping relationship through a visual graphical user interface (GUI), while embedding a difference analysis algorithm to decompose the amount difference layer by layer until the root cause cell is located, thereby ensuring the efficiency and accuracy of the verification process.

[0122] The mapping relationship verification analysis module uses DAG (Directed Acyclic Graph) as the core data structure to systematically verify all manual mapping relationships. The specific implementation is divided into the following two dimensions:

[0123] The module takes the two formula dependency trees (converted to DAG structure) of Party A and Party B output by the formula parsing module as input, uses DAG traversal algorithm and difference analysis algorithm to verify the manual mapping, can automatically detect three types of abnormalities: incorrect mapping, repeated mapping, and missing mapping; the intermediate data is uniformly stored in DAG and hash table.

[0124] The manual formula in the cell of Party B is parsed piece by piece to identify the implicit manual mapping relationship. For example:

[0125] The cell B1 of Party B contains the formula "=VLOOKUP(A1,Sheet1!A:B,2,FALSE)" - extract the mapping pair (A1 - some cell in Sheet1!B column);

[0126] If the formula is a direct reference "=Sheet1!A1" - extract the mapping pair (A1 - B1).

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

[0128] The formula dependency tree stored in the form of DAG supports depth-first search (DFS). The module starts from the root nodes of the total amount of Party A and Party B, respectively traverses down the dependency path, and compares the mapping values node by node. For example:

[0129] If Party A node A1 (100) points to Party B node B1 (90) through a mapping edge, it is marked as a potential error mapping.

[0130] Through DAG in-degree analysis. If there are multiple outgoing edges for a Party A node (i.e., in-degree > 1), such as A1-B1 and A1-B2 coexist, it is immediately marked as a duplicate mapping.

[0131] Through the access table maintained during the DFS process. If there is a Party A node that has no outgoing edges at the end of the entire traversal, it is marked as a missing mapping.

[0132] The module starts from the difference Δ between the total amounts of Party A and Party B (Δ = total amount of Party A - total amount of Party B), and decomposes the contributions of sub-items in reverse along the DAG. For example: Δ = 500 can be split into

[0133] A1-B1 difference 100 (error mapping), A2 missing 400 (missing mapping).

[0134] The node properties of the DAG structure include cell address, current value, and formula string; directed edges represent formula references or manual mapping relationships; the whole is stored in memory JSON objects or VBACollection.

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

[0136] The exception list includes a dynamic array storing all verification exceptions, and each exception contains node address, exception type (error / duplicate / missing), difference amount, and associated formula text.

[0137] Starting from the total amount nodes of Party A and Party B, the manual mapping relationship is checked along the DAG structure, and the following steps are performed:

[0138] Scan all Party B cell formulas in the merged workbook to extract explicit or implicit manual mappings.

[0139] Direct reference type: such as B1 = "=Sheet1!A1", record edge (A1-B1);

[0140] Lookup function type: such as B1 = "=VLOOKUP(A1,Sheet1!A:B,2,FALSE)", after analyzing the lookup logic, record edge (A1 - corresponding matching cell).

[0141] Read "Party A Total Amount" and "Party B Total Amount" through Excel Range.Value property, calculate Δ = 10000 - 9500 = 500. Module traverses DAG reversely, splits total amount into several sub-contribution (such as SUM(A1:A1000) etc.).

[0142] Compare the values of the mapping nodes in the DAG. If the difference is not zero, record the exception;

[0143] If it is found that the out-degree of a Party A node is > 1, mark it as a duplicate;

[0144] If it is found that a Party A node has no outgoing edges, mark it as missing.

[0145] Classify and record the impact amount according to the source of the difference. Example:

[0146] A1→B1 difference 100→Record "Error mapping, impact amount 100, associated formula =VLOOKUP(...)".

[0147] The error positioning module further drills down on the exception list output by the mapping relationship verification and analysis module, accurately pointing out the specific location and root cause of each exception in the DAG.

[0148] For each exception (such as error mapping A1→B1, difference 100), the module traces back along the DAG, expanding the formula dependency hop by hop, until it locates the bottom-level cell that produces the difference. For example:

[0149] A1 (value 100) is mapped to B1 (value 90) through the formula "=VLOOKUP(A1,Sheet1!A:B,2,FALSE)".

[0150] The module continues to track the dependency path of A1 and finds that A1 = SUM(C1:C10), finally locates the C5 value error, and records the path A1-C5-Total Amount.

[0151] Identify multiple outgoing edges of Party A nodes (such as A1-B1 and A1-B2) through DAG in-degree analysis. The module compares the formula logic of B1 and B2 and gives retention suggestions (such as retaining B1 and deleting B2).

[0152] Scan Party A DAG nodes to identify nodes with no outgoing edges (such as A2). Combine field name similarity (such as "equipment fee" vs "equipment cost") to recommend the most likely Party B matching field through the Levenshtein distance algorithm.

[0153] According to the contribution proportion of the exception to the total amount difference Δ (such as 100 / 500 = 20%), use the heap sorting algorithm to sort all exceptions in descending order, ensuring that high-impact errors are displayed first.

[0154] The module starts from the exception list of the verification analysis module and performs the following positioning steps:

[0155] For the error mapping A1-B1 (difference 100), the module expands the formula dependencies of A1 and B1 along the DAG, layer by layer, until it locks the difference source cell (such as C5). Finally, the complete impact path A1-C5-Total Amount is recorded.

[0156] A1-B1, A1-B2 are detected by DAG in-degree analysis. The module analyzes the formula logic of B1 and B2 (B1=Sheet1!A1, B2=VLOOKUP(A1,...)), and gives a correction suggestion to keep A1-B1 and delete A1-B2.

[0157] For the A-party node A2 with no out-edge, the module extracts its field name "Equipment Fee" and calculates the string similarity with the B-party field "Equipment Cost", and suggests mapping A2-B3.

[0158] The module automatically generates executable corrections:

[0159] Modify the B1 formula to "=Sheet1!A2";

[0160] Add a mapping from A2 to B3.

[0161] All suggestions are output based on the double verification of field similarity and numerical consistency.

[0162] S160, in the form of charts and tables, displays the verification results.

[0163] The result output module is in the form of an Excel plug-in, responsible for presenting all the verification results (exception list, error report, correction suggestion) generated by the mapping relationship verification analysis module and the error positioning module in a structured, visual, and interactive manner, and supports one-key report export.

[0164] The result output module directly consumes the exception list and DAG structure of the error positioning module, fully utilizes the original table, chart, and GUI capabilities of Excel, and realizes the following four functions:

[0165] The module automatically generates a "ResultTable" worksheet in the current workbook, which displays the exception details row by row:

[0166] Exception ID;

[0167] Type (error mapping / repeated mapping / missing mapping);

[0168] Node address (such as Sheet1!$A$1);

[0169] Difference amount (such as 100);

[0170] Full text of the associated formula (such as "=VLOOKUP(...)");

[0171] Revision suggestion (such as "modify B1 formula to =Sheet1!A2");

[0172] Data is written in bulk through VBARange object, supporting subsequent sorting and filtering by users (such as descending order by difference).

[0173] The module uses ExcelChart object to dynamically generate two types of charts:

[0174] Bar chart: show the distribution of abnormal types, such as 10 error mappings, 5 duplicate mappings, and 3 missing mappings;

[0175] DAG subgraph: visualize the error propagation path in node-link manner (such as A1-C5-total amount).

[0176] The chart is embedded in the VBA form, supporting zooming, filtering, and highlighting interactions, and users can focus on key paths in real time.

[0177] GUI is provided through Excel native controls:

[0178] Click the abnormal row to automatically jump to the corresponding cell (such as B1);

[0179] Double-click the cell to allow direct modification of the formula;

[0180] After modification, the module refreshes the ResultTable and chart in real time, immediately reflecting the correction results.

[0181] All interactive events are driven by VBA events, with zero learning cost, and non-professional users can quickly complete the correction.

[0182] The module supports one-key export:

[0183] Excel workbook copy (including ResultTable and chart);

[0184] PDF snapshot (including static view of table and chart).

[0185] The export process is fully automated, ensuring uniform report format and timely deliverability.

[0186] The method of the embodiment is implemented in the form of a plug-in, integrating five modules of data input, formula analysis, mapping relationship verification and analysis, error positioning, and result output. It uses the native functions of spreadsheet software to support the automatic verification of more than one thousand complex quotation sheets, and has a lower operation threshold and an efficiency improvement of about 95% compared with traditional ERP systems or manual verification.

[0187] The total amount formula of Party A and Party B is parsed by using a directed acyclic graph (DAG) recursion to generate an independent dependency tree; the mapping relationship checking and analyzing module checks the manual mapping formula (such as VLOOKUP) based on DAG traversal, efficiently detects incorrect mapping, repeated mapping and missing mapping, and is superior to the limitation of the traditional tool of only extracting formula strings.

[0188] By DAG path analysis and priority sorting, the abnormal root cause (such as the difference source cell) is accurately located, a detailed report (including the influence path and correction suggestion) is generated, GUI interactive formula correction is supported, and the error processing cost of a complex quotation sheet is significantly reduced.

[0189] The present application is specially designed for the mapping scene of a complex quotation sheet with more than 1000 items, and brings the following significant benefits:

[0190] Significantly improve the checking efficiency: by starting from the total amount of Party A and Party B, automatically analyzing the formula logic and mapping relationship in the electronic table, the present application can quickly locate incorrect mapping and missing mapping. Compared with traditional manual checking, the checking efficiency is improved by several times, the time for processing more than 1000 items of quotation sheet is shortened by more than 95%, and the time cost of manual checking is greatly reduced.

[0191] Improve the mapping accuracy: the present application systematically identifies incorrect mapping and missing items by deeply analyzing complex nested formulas and data dependency relationships, and the checking accuracy is improved by about 70% compared with existing solutions. In large engineering projects or procurement quotations, the present application can effectively avoid the inconsistency of the amount caused by misplacement or omission of fields, and reduce the risk of commercial disputes.

[0192] Enhance flexibility and adaptability: there is no limit to the mapping rules, and the present application automatically adapts to the dynamic changes of the templates of Party A and Party B, overcoming the defect of insufficient flexibility of rule-based automation tools, and being suitable for diversified complex quotation sheet scenarios.

[0193] Reduce the technical and cost threshold: based on the widely used electronic table platform, no need to rely on expensive third-party software or complex database environment, significantly reduce the application cost of small and medium-sized enterprises. Users do not need professional programming knowledge to operate, the learning curve is gentle, and it is easy to promote.

[0194] Improve the checking transparency and traceability: provide a systematic error positioning mechanism, users can intuitively view the root cause of the amount inconsistency (such as specific incorrect mapping items or missing items), enhance the transparency and traceability of the checking process, and facilitate subsequent audit and problem rectification.

[0195] The above electronic table data checking method realizes efficient analysis and error correction of the mapping relationship in a complex quotation sheet through an automated process. First, data and formula information in the electronic table file to be checked are acquired and fused. Then, the formula information is analyzed to construct a dependency tree, track the calculation path of each total amount cell, and establish a mapping relationship between the quotation items of the two parties based on the extracted data. Subsequently, the mapping relationship is analyzed in reverse from the total amounts of the two parties to detect inconsistent points, determine abnormal points, and analyze the abnormal points to find out the missing or erroneous mapping items, and generate a detailed error report as the checking result. Finally, the checking result is displayed in the form of intuitive charts and tables, so that the user can quickly understand and correct the errors, thereby greatly improving the efficiency and accuracy of data checking and effectively protecting the interests of the two parties. This method not only reduces the time cost and error rate of manual checking, but also enhances the transparency and traceability of the checking process.

[0196] Figure 5 is a schematic block diagram of an electronic table data checking system 300 provided by an embodiment of the present application. As shown in Figure 5 Corresponding to the above electronic table data checking method, the present application also provides an electronic table data checking system 300. The electronic table data checking system 300 includes units for executing the above electronic table data checking method, and the system can be configured in a server. Specifically, please refer to Figure 5 The electronic table data checking system 300 includes an acquisition unit 301, an analysis unit 302, a mapping relationship establishing unit 303, a detection unit 304, a checking unit 305, and a display unit 306.

[0197] The acquisition unit 301 is configured to acquire an electronic table file to be checked, and fuse and extract data and formula information in the electronic table file to be checked. The analysis unit 302 is configured to analyze the formula information, construct a dependency tree, and track the calculation path of each total amount cell. The mapping relationship establishing unit 303 is configured to establish a mapping relationship between the quotation items of the two parties based on the formula information. The detection unit 304 is configured to analyze the mapping relationship in reverse from the total amounts of the two parties, detect inconsistent points, and obtain abnormal points. The checking unit 305 is configured to analyze the abnormal points to find out the missing or erroneous mapping items, and generate an error report to obtain a checking result. The display unit 306 is configured to display the checking result in the form of charts and tables.

[0198] In an embodiment, the acquisition unit 301 includes:

[0199] The importing subunit is configured to import the electronic spreadsheet files of the two parties by using an electronic spreadsheet software API interface to obtain a to-be-checked electronic spreadsheet file; the fusion subunit is configured to nondestructively copy the content of the to-be-checked electronic spreadsheet file into a new workbook, keep the original formula, style and name unchanged, add a prefix before a workbook level name that exists in conflict to distinguish the source, and obtain a fusion result; and the marking subunit is configured to select and mark an amount in the fusion result to obtain formula information.

[0200] In an embodiment, the analysis unit 302 includes:

[0201] The determining subunit is configured to determine a starting parsing point according to the formula information; the string obtaining subunit is configured to obtain a formula string in a specified cell by using an electronic spreadsheet API; the parsing subunit is configured to convert the formula string into an abstract syntax tree, recursively parse all sub-formulas until a basic numerical cell, and determine a reference path by running-time simulation for nested functions and cross-worksheet references; monitor and report an invalid formula or a circular reference error in the formula string to obtain a parsing result; and the organizing subunit is configured to organize the parsing result into a multi-way tree structure, use a hash table to speed up the query, and save the result in a hidden worksheet.

[0202] In an embodiment, the detecting unit 304 is configured to, when there is an abnormal mapping in the mapping relationship, locate a specific source of an abnormal value by tracing a dependency tree in a DAG path, and record a relevant path to obtain an abnormal point.

[0203] In an embodiment, the checking unit 305 is configured to identify and evaluate a plurality of mappings of a node of one party by using DAG in-degree analysis, recommend a single mapping to eliminate redundancy, suggest a potential mapping similar to a field of the other party by using a string matching technique for a node of the one party that is not mapped, generate a specific mapping correction or addition suggestion based on field similarity and numerical consistency, and obtain a checking result.

[0204] It should be noted that a person skilled in the art can clearly understand the specific implementation process of the above-described electronic spreadsheet data checking system 300 and each unit, which can refer to the corresponding description in the foregoing method embodiments. For the convenience and brevity of description, the details are not described herein.

[0205] The above-described electronic spreadsheet data checking system 300 can be implemented in the form of a computer program, which can run on a computer device as shown in Figure 6 .

[0206] Please refer to Figure 6 , Figure 6is a schematic block diagram of a computer device provided by an embodiment of the present application. The computer device 500 can be a server, wherein the server can be a standalone server or a server cluster composed of multiple servers.

[0207] Referring to Figure 6 The computer device 500 includes a processor 502, a memory, and a network interface 505 connected through a system bus 501, wherein the memory can include a non-volatile storage medium 503 and an internal memory 504.

[0208] The non-volatile storage medium 503 can store an operating system 5031 and a computer program 5032. The computer program 5032 includes program instructions that, when executed, cause the processor 502 to perform an electronic form data checking method.

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

[0210] The internal memory 504 provides an environment for the execution of the computer program 5032 in the non-volatile storage medium 503, which, when executed by the processor 502, causes the processor 502 to perform an electronic form data checking method.

[0211] The network interface 505 is configured to perform network communication with other devices. Those skilled in the art can understand that Figure 6 The structure shown in the figure is only a block diagram of part of the structure related to the scheme of the present application, and does not constitute a limitation on the computer device 500 to which the scheme of the present application is applied. The specific computer device 500 can include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0212] The processor 502 is configured to run the computer program 5032 stored in the memory to implement the following steps:

[0213] Obtain a to-be-checked electronic form file, and fuse and extract data and formula information in the to-be-checked electronic form file; analyze the formula information, construct a dependency tree to track the calculation path of each total amount cell; establish a mapping relationship between bid items based on the formula information; starting from the total amount of both parties, reverse analyze the mapping relationship, detect inconsistent points to obtain abnormal points; analyze the abnormal points to find out the missing or missing mapping items, and generate an error report to obtain a checking result; and display the checking result in the form of a chart and a table.

[0214] In an embodiment, the processor 502, in implementing the step of obtaining the to-be-checked electronic spreadsheet file, fusing and extracting data and formula information in the to-be-checked electronic spreadsheet file, implements the following steps:

[0215] Importing the electronic spreadsheet files of both parties by using the API interface of the electronic spreadsheet software to obtain the to-be-checked electronic spreadsheet file; losslessly copying the content of the to-be-checked electronic spreadsheet file to a new workbook, keeping the original formula, style and name unchanged, adding a prefix before the workbook level name that exists in conflict to distinguish the source, to obtain a fusion result; selecting and marking the amount of the fusion result to obtain formula information.

[0216] In an embodiment, the processor 502, in implementing the step of analyzing the formula information and constructing a dependency tree to track the calculation path of each total amount cell, implements the following steps:

[0217] Determining a starting parsing point according to the formula information; obtaining a formula string in a specified cell by using the API of the electronic spreadsheet; converting the formula string into an abstract syntax tree and recursively parsing all sub-formulas until a basic numerical cell, for nested functions and cross-worksheet reference parsing, determining the reference path by runtime simulation; monitoring and reporting invalid formulas or circular reference errors in the formula string to obtain a parsing result; organizing the parsing result into a multi-way tree structure, using a hash table to speed up the query and saving the result in a hidden worksheet.

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

[0219] In an embodiment, the processor 502, in implementing the step of starting from the total amount of both parties, inversely analyzing the mapping relationship, detecting inconsistent points to obtain abnormal points, implements the following steps:

[0220] When there is an abnormal mapping in the mapping relationship, the specific source of the abnormal value is located by tracing the dependency tree in the DAG path, and the related path is recorded to obtain abnormal points.

[0221] In an embodiment, the processor 502, in implementing the step of analyzing the abnormal points to find out the error or missing mapping item and generating an error report to obtain a checking result, implements the following steps:

[0222] Using DAG in-degree analysis to identify and evaluate a plurality of mappings of one party node, recommending a single mapping to eliminate redundancy; for the un-mapped one party node, suggesting a potential mapping similar to the field of the other party by using string matching technology; based on field similarity and numerical consistency, generating specific mapping correction or addition suggestions to obtain a checking result.

[0223] It should be understood that, in the embodiments of the present application, the processor 502 can be a central processing unit (CPU), and can 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 gates or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor.

[0224] It can be understood by those skilled in the art that all or part of the processes in the above-mentioned embodiments can be completed by a computer program instructing related hardware. The computer program includes program instructions, and the computer program can be stored in a storage medium, which is a computer-readable storage medium. The program instructions are executed by at least one processor in the computer system to implement the process steps of the above-mentioned embodiments.

[0225] Therefore, the present application further provides a storage medium. The storage medium can be a computer-readable storage medium. The storage medium stores a computer program, wherein the computer program is executed by a processor to make the processor perform the following steps:

[0226] obtaining an electronic spreadsheet file to be checked, fusing and extracting data and formula information in the electronic spreadsheet file to be checked, analyzing the formula information, constructing a dependency tree to track the calculation path of each total amount cell, establishing a mapping relationship between bid items based on the formula information, starting from the total amount of both parties, inversely analyzing the mapping relationship, detecting inconsistent points to obtain abnormal points, analyzing abnormal points to find out the missing or erroneous mapping items, and generating an error report to obtain a checking result, and displaying the checking result in the form of a chart and a table.

[0227] In an embodiment, when the processor executes the computer program to implement the step of obtaining an electronic spreadsheet file to be checked, fusing and extracting data and formula information in the electronic spreadsheet file to be checked, the processor specifically implements the following steps:

[0228] Importing the electronic spreadsheet files of both parties by using the API interface of the electronic spreadsheet software to obtain a to-be-checked electronic spreadsheet file; losslessly copying the content of the to-be-checked electronic spreadsheet file into a new workbook while keeping the original formula, style and name unchanged, adding a prefix before the workbook level name that exists in conflict to distinguish the source, to obtain a fusion result; selecting and marking the amount of the fusion result to obtain formula information.

[0229] In an embodiment, when the processor implements the analysis of the formula information, constructs a dependency tree to track the calculation path of each total amount cell, and implements the following steps when executing the computer program:

[0230] Determining a starting parsing point according to the formula information; obtaining a formula string in a specified cell by using an electronic spreadsheet API; converting the formula string into an abstract syntax tree and recursively parsing all sub-formulas until a basic numerical cell, and determining a reference path by running-time simulation for nested functions and cross-worksheet references; monitoring and reporting invalid formulas or circular reference errors in the formula string to obtain a parsing result; organizing the parsing result into a multi-way tree structure, using a hash table to speed up the query and saving the result in a hidden worksheet.

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

[0232] In an embodiment, when the processor implements the analysis of the formula information, constructs a dependency tree to track the calculation path of each total amount cell, and implements the following steps when executing the computer program:

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

[0234] In an embodiment, when the processor implements the analysis of the formula information, constructs a dependency tree to track the calculation path of each total amount cell, and implements the following steps when executing the computer program:

[0235] Using DAG in-degree analysis to identify and evaluate a plurality of mappings of one party node, recommending a single mapping to eliminate redundancy; for the unmapped one party node, suggesting a potential mapping similar to the field of the other party by using string matching technology; based on field similarity and numerical consistency, generating specific mapping correction or addition suggestions to obtain a checking result.

[0236] The storage medium can be a U disk, a mobile hard disk, a read-only memory (ROM), a magnetic disk or an optical disk, and various computer readable storage media that can store program codes.

[0237] Those skilled in the art can realize that the units and algorithm steps of each example described in combination with the embodiments disclosed herein can be realized in electronic hardware, computer software or a combination of both. In order to clearly illustrate the interchangeability of hardware and software, the components and steps of each example have been described in the above description in a general manner. Whether the functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. A person skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present application.

[0238] In several embodiments provided by the present application, it should be understood that the disclosed system and method can be implemented in other ways. For example, the system embodiments described above are only illustrative. For example, the division of each unit is only a logical functional division, and actual implementation can have another division manner. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed.

[0239] The steps in the method embodiments of the present application can be adjusted, combined and deleted in sequence according to actual needs. The units in the system embodiments of the present application can be combined, divided and deleted according to actual needs. In addition, each functional unit in each embodiment of the present application can be integrated in one processing unit, or each unit can exist physically, or two or more units can be integrated in one unit.

[0240] The integrated unit, if realized in the form of a software functional unit and sold or used as an independent product, can be stored in a storage medium. Based on such understanding, the technical solutions of the present application essentially or the parts that make contributions to the prior art, or the whole or part of the technical solutions can be embodied in the form of a software product. The computer software product is stored in a storage medium, and includes a plurality of instructions for causing a computer device (which can be a personal computer, a terminal or a network device, etc.) to execute all or part of the steps of the method described in each embodiment of the present application.

[0241] The above description is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art can easily think of various equivalent modifications or replacements within the technical range disclosed by the present application, and these modifications or replacements should be covered in the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A method of electronic spreadsheet data verification, characterized by, The method comprises the following steps: obtaining an electronic spreadsheet file to be checked, and fusing and extracting data and formula information in the electronic spreadsheet file to be checked; analyzing the formula information, and constructing a dependency tree to track the calculation path of each total amount cell; the dependency tree is a multi-way tree structure recording the reference relationship between cells generated by recursively analyzing the formula, and is used to track the complete calculation path of each total amount cell; based on the formula information, a mapping relationship between the two bid items is established; the mapping relationship between the two bid items is extracted and recorded by analyzing the manual formula in which one party cell references the cell of the other party; starting from the total amount of the two parties, the mapping relationship is analyzed in reverse to detect inconsistent points to obtain abnormal points; starting from the total amount of the two parties, the value of each mapping node is compared by tracing back along the dependency path using the constructed dependency tree to detect inconsistent points and obtain abnormal points; analyzing the abnormal points to find the error or missing mapping item, and generating an error report to obtain the checking result; the checking result is displayed in the form of a chart and a table.

2. The electronic spreadsheet data verification method of claim 1, wherein, The method comprises the following steps: importing the electronic spreadsheet files of the two parties through the API interface of the electronic spreadsheet software to obtain the electronic spreadsheet file to be checked; copying the content of the electronic spreadsheet file to be checked into a new workbook without loss, keeping the original formula, style and name unchanged, adding a prefix before the workbook level name that exists in conflict to distinguish the source, and obtaining a fusion result; selecting and marking the amount of the fusion result to obtain formula information.

3. The method of claim 1, wherein, The method comprises the following steps: determining the starting analysis point according to the formula information; obtaining the formula string in the specified cell by using the electronic spreadsheet API; converting the formula string into an abstract syntax tree, and recursively analyzing all sub-formulas until the basic numerical value cell, supporting nested functions and cross-worksheet reference analysis; monitoring and reporting invalid formulas or circular reference errors in the formula string to obtain an analysis result; organizing the analysis result into a multi-way tree structure, using a hash table to speed up the query, and saving the result in a hidden worksheet.

4. The method of claim 3, wherein, The multi-way tree structure comprises a plurality of nodes, and each node comprises a cell address, a formula or a value, a data type and a list of child nodes.

5. The method of claim 1, wherein, The method comprises the following steps: when there is an abnormal mapping 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 related path is recorded to obtain abnormal points.

6. The method of claim 1, wherein, The method comprises the following steps: analyzing the abnormal points to find the error or missing mapping item, and generating an error report to obtain the checking result. A plurality of mappings of one party node are identified and evaluated using DAG in-degree analysis, and a single mapping is recommended to eliminate redundancy; for the unmapped one party node, a potential mapping similar to the other party field is suggested through string matching technology; based on field similarity and numerical consistency, specific mapping correction or addition suggestions are generated to obtain the verification result.

7. A spreadsheet data verification system, characterized by, The method comprises the following steps: An acquisition unit is configured to acquire a to-be-verified electronic spreadsheet file and fuse and extract data and formula information in the to-be-verified electronic spreadsheet file; An analysis unit is configured to analyze the formula information and construct a dependency tree to track a calculation path of each total amount cell; The dependency tree is a multi-way tree structure recording the reference relationship between cells generated by recursively analyzing the formula, and is used to track the complete calculation path of each total amount cell; A mapping relationship establishment unit is configured to establish a mapping relationship between bid offer items based on the formula information; analyze a hand formula in which one party cell references a cell of the other party, and record the mapping relationship between the bid offer items; A detection unit is configured to start from the total amount of both parties, analyze the mapping relationship in reverse, detect inconsistent points, and obtain abnormal points; The abnormal points are obtained by starting from the total amount of both parties, using the constructed dependency tree, tracing the value of each mapping node along the dependency path, and comparing the corresponding nodes of both parties to detect inconsistent points. A verification unit is configured to analyze the abnormal points to find out the missing or erroneous mapping items, generate an error report, and obtain a verification result. A display unit is configured to display the verification result in the form of a chart and a table.

8. A computer device, comprising: The computer device comprises 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 by The storage medium stores a computer program, and the processor implements the method according to any one of claims 1 to 6 when executing the computer program.

Images