Cost model automatically identifies conversion method

Abstract

The application discloses a cost model automatic identification conversion method, and particularly relates to the technical fields of intelligent manufacturing, cost engineering, data modeling and automatic calculation, and comprises an Excel formula acquisition and standardization module, a dependency graph construction and optimization module, a Gscript field expression to QLExpression compilation module, an execution model generation and function library binding module, and an incremental linkage and multi-table calculation module. Through the cooperation of the modules, automatic identification, conversion, execution and incremental maintenance of a Java executable QLExpression algorithm model from an Excel file and a Gscript field expression are realized. The method has the advantages of high automation, dependence on visualized audit, cross-environment reuse, efficient incremental calculation, accurate error positioning and flexible expansion, and is suitable for the informatization migration and service landing of complex cost models and is easy to extend to new functions and new fields.

Description

Technical Field

[0001] This invention relates to the fields of intelligent manufacturing, cost engineering, data modeling and automated computing. More specifically, this invention relates to a method for automatic identification and conversion of cost models, which automatically extracts formula dependencies from Excel, constructs a computational graph (DAG), and automatically converts it into a QLExpression script in the Java ecosystem; it also supports the automatic compilation and execution of domain-specific languages ​​(Gscript) into QLExpression. Background Technology

[0002] Excel is widely used in engineering cost, parameter configuration and quotation processes, but its formulas are scattered, have complex dependencies, are prone to errors and are difficult to reuse. When business is migrated to information / service systems, it is necessary to solidify the calculation rules in Excel into maintainable, auditable, scalable and runnable algorithm models.

[0003] Traditional methods rely on manual translation of Excel formulas into code, which is costly and prone to errors. At the same time, they lack automated dependency identification, error location, and incremental calculation mechanisms, making it impossible to support complex and dynamic data links and multi-table linked calculations.

[0004] Therefore, we propose an automatic cost model identification and conversion method. Through ANTLR parsing, dependency graph construction, expression normalization, runtime function library binding, and incremental execution framework, we achieve a closed-loop capability for automatic identification, compilation, execution, and tracing from Excel / Gscript to QLExpression. Summary of the Invention

[0005] In order to overcome the above-mentioned defects of the prior art, the present invention provides an automatic cost model identification and conversion method to solve the problems mentioned in the background art.

[0006] To achieve the above objectives, the present invention provides the following technical solution: a method for automatic identification and conversion of cost models, comprising:

[0007] Excel Formula Acquisition and Standardization Module: Reads a specified sheet and cell range from an Excel file, identifies drop-down dictionary cells, parses cell formulas and performs syntax standardization, and generates a standardized script compatible with QLExpression, along with a set of input variables and output formula node information;

[0008] Dependency Graph Construction and Optimization Module: Based on the formula node information output by the Excel formula collection and standardization module, a directed acyclic graph (DAG) is constructed. The DAG is then subjected to equivalence reference folding, redundant node removal, and semantic verification to generate an optimized dependency computation graph.

[0009] The Gscript domain expression to QLExpression compilation module: Based on the default Gscript domain syntax rules, it parses the domain / field identifiers in Gscript expressions and converts the functions, logical operations and conditional constructs in Gscript into QLExpression syntax;

[0010] The execution model generation and function library binding module maps the standardized script output by the Excel formula collection and standardization module and the QLExpression syntax output by the compilation module into an executable QLExpression expression model and binds it to a preset runtime function library.

[0011] Incremental linkage and multi-table calculation module: Organize two-dimensional table variables and global parameter variables, establish table-external parameter association index, and trigger local calculation of specified table / row; if the calculation depends on parameters of other tables, the preceding dependent tables are updated first, and then the affected tables are refreshed in a cascading manner.

[0012] Error detection and observability module: Collects and accurately locates syntax / semantic errors during the Excel formula acquisition and Gscript compilation process, and records calculation process logs to support auditing and problem review;

[0013] Through the collaboration of the above modules, the system can automatically identify, convert, execute, and incrementally maintain the QLExpression algorithm model executable in the Java runtime environment, from Excel files and Gscript domain expressions.

[0014] Preferably, the Excel formula acquisition and standardization module includes:

[0015] The Apache POI tool is used to read a specified sheet and cell range from an Excel file, and DataValidationList is used to identify drop-down dictionary cells.

[0016] The ExcelLexer lexical analyzer and ExcelParser parser, based on the ANTLR tool, parse cell formulas one by one, and the ExcelDefaultListener performs the operations.

[0017] ExcelDefaultListener performs the following operations:

[0018] Remove format placeholders from cell references, such as converting A1 and $A$1 to ${A1};

[0019] Function mapping: Mapping SUM(x1, x2, ..., x...) nConvert the range to "x1+x2+...+x n "+range expansion sum of variables" will be used to expand AVG(x1, x2, ..., x... n ) is converted to "(x1+x2+...+x n ) / n" (where n is the number of arguments) converts the ROUNDUP, ROUNDDOWN, ROUND, CEILING, and FLOOR functions into QLExpression-compatible functions with the same lowercase name;

[0020] Expand cell range: Expand A m B k (m and k are positive integers) formatted range expanded to A m , {A m+1}, ...Ak, {B m}, B m+1 , ...{B k A list of variables, such as A1:B2, can be expanded to A1, {A2}, B1, {B2}.

[0021] Logical operator substitution: Replace "=" with "==". For example, AND(a1, a2, ..., a...)... n Replace ) with a1&&a2&&...&&an, and OR(a1, a2, ..., a) with a1&&a2&&...&&an. n Replace ) with (a1||a2||...||a n );

[0022] The ParserErrorListener collects formula parsing errors and records the row and column numbers of the erroneous cells, the original formula input, and the location of the error marked with an underline.

[0023] Preferably, the dependency graph construction and optimization module includes:

[0024] Based on the FormulaDto data structure (containing fields such as isFormula, script, target, inputIds, hasError, etc.), which stores formula node information, a directed acyclic graph is constructed with the formula's reference to the input cell as edges.

[0025] Perform equivalent folding on the directed acyclic graph: if a formula node is only a direct reference to another cell (such as B). i ={A j If the formula node is removed and all references pointing to it are redirected to the referenced cell, i and j are positive integers.

[0026] Name labeling is performed on the directed acyclic graph: the text cell above the row where the formula node is located or the text cell to the left of the column where the formula node is located is selected as the node display name; if there is a display name conflict or no text cell is available, the cell address is used as the display name.

[0027] Perform semantic validation on the directed acyclic graph: if the input variable of the formula node points to a cell outside the range read by the Excel file, then mark the hasError field of that node as true.

[0028] Preferably, the compilation module for converting Gscript domain expressions to QLExpression includes: defining the Gscript syntax rules for this domain through the Gscript.g4 syntax file, and having the compilation operation performed by CostQLExpressListener;

[0029] Specifically, CostQLExpressListener performs the following compilation operations:

[0030] Domain / Field Resolution: The nameToScopeMap maps the domain name in the $Scope format in Gscript to the domain code (e.g., "raw materials" is mapped to CBD_RAW_MATERIAL). The ParamMetaDto data structure (which associates the domain code, parameter display name, and actual field name) resolves the field identifier in the $Scope!Field or !Field (current domain) format to the actual field name.

[0031] Function conversion: Convert SUM(a, b, c) and AVG(a, b, c) in Gscript to QLExpression's sum([a, b, c]) and avg([a, b, c]) array format functions respectively; convert IF(cond, then, else) to a ternary expression "cond?then:else"; convert the condition list of SUMIFS / GETIFS / MINIFS / MAxIFS to a condition mapping in the format of NewMap('field':'value', ...); convert the conditions of GETIFS2 / MINIFS2 / MAxIFS2 / VLOOKUP2 to a double mapping of "field → value" NewMap and "field → operator" NewMap; convert int(x) to getInt(x);

[0032] Unify logical operations: unify and / a&& and or / || in Gscript to "&&" and "||" in QLExpression respectively, and replace the comparison symbol "<>" with "!=".

[0033] Preferably, the execution model generation and function library binding module includes:

[0034] The standardized QLExpression script is constructed into an executable expression called DefaultExpression, and the input variables of the expression are automatically parsed through the getInputVars() method;

[0035] The default runtime function library is bound through the registerFunc method of the ExpressionRunner core execution class;

[0036] Specifically, the function library includes:

[0037] Mathematical / rounding functions: getInt(x), sqrt(x), roundup(x, n), rounddown(x, n), ceiling(x, n), floor(x, n), pow(x, y), ln(x), log(x, n) (where n is the logarithmic base);

[0038] Statistical / aggregate function: sum([x1, x2, ..., x...)) n ]), avg([x1, x2, ..., x n ]), sumifs(Scope, NewMap,...), maxifs(Scope, NewMap,...), minifs(Scope, NewMap,...), getifs(Scope, NewMap,...);

[0039] Search / sort / get value functions: vlookup(Scope, NewMap, queryFields, resultFields), vlookup2(Scope, NewMap1, NewMap2, queryFields, resultFields), sort(arr, idx, order) (arr is an array, idx is the sort index, order is the sort direction), getat(arr, i) (i is the array index), join(arr, sep) (sep is the join operator);

[0040] Trigonometric functions: sin(x), cos(x), tan(x), cot(x), sec(x), csc(x);

[0041] Using SpringExpressContext as the runtime context, the variable value is first obtained from the local context; if the variable does not exist in the local context, the corresponding Bean is obtained from the Spring container.

[0042] During execution, a topological "ready to execute" strategy is adopted: the calculation is triggered only when all input variables of the expression exist in the context. After the calculation is completed, the output variable is added to the context, and the expression is removed from the set to be calculated. The process is repeated recursively until there are no more expressions to be calculated.

[0043] Preferably, the incremental linkage and multi-table calculation module includes:

[0044] The CostRunnerContext module organizes the TableVariable (two-dimensional table variable) and the IVariable (global parameter variable), where the TableVariable carries a set of table-level expressions, a set of row-level expressions, and two-dimensional table values.

[0045] Scan the input and output variables of the expressions in each TableVariable, extract the set of external dependency parameters, and establish an association index from the table to the external dependency parameters;

[0046] When a partial update is triggered, specify the table identifier and row ID to be updated:

[0047] If the expression to be updated is a row-level expression, then the header-level expression of the output variable with the same name will be overridden first.

[0048] If the input of the expression to be updated depends on parameters calculated from other tables, then the expression in the preceding table is updated first according to the dependency relationship.

[0049] Based on the table-to-external dependency parameter index, locate other tables affected by the current update, perform cascading refresh calculations on them, and write the calculation results back to the context.

[0050] Preferably, the error detection and observability module includes:

[0051] Excel formula parsing error handling: The ParserErrorListener outputs error information, which includes the row and column numbers of the erroneous cell, the original formula input text, and the underlined location of the error.

[0052] Gscript compilation error handling: Errors are collected by GscriptErrorListener, and a summary of error information including error symbol ranges and text highlighting is output;

[0053] Calculation process logging: The ContextCalculateLog module records logs in a four-step format: original formula, descriptive replacement, numerical replacement, and calculation result. Descriptive replacement replaces variable placeholders with variable display names, and numerical replacement replaces variable placeholders with actual input values.

[0054] Preferably, it also includes core data structure support, specifically:

[0055] The FormulaDto data structure is used to abstract Excel cells. The FormulaDto contains the following fields: isFormula (whether it is a formula cell), isSumNode (whether it is a summation node), isDictionary (whether it is a dictionary cell), source (original formula), script (normalized script), target (target variable identifier), inputIds (set of input variable identifiers), errorDtoList (error message list), displayName (display name), hasError (whether an error exists), and options (dictionary options).

[0056] The ExcelRange data structure is used to parse and offset Excel cell coordinates. ExcelRange provides the fromStr(String rangeStr) method (e.g., to convert "A8:K" to "A9:K"). 74 "parse into coordinate range), shifting(in trows, intcols) method (implements row / column offset of coordinate range, trows is the row offset, tcols is the column offset);

[0057] The IVariable interface and its implementation classes are used to carry variable values: DecimalVariable carries numeric variables, ObjectVariable carries object variables, TableVariable carries two-dimensional table variables, and ExpressionVariable carries expression variables. All IVariable implementation classes support setting default values.

[0058] Preferably, it also includes an expansion capability module, specifically implemented as follows:

[0059] Function library extension: Add a Java method to the FormulaExecutor or ExcelFormulaExecutor class, and register the new method to the runtime function library using the registerFunc method of ExpressionRunner to extend function capabilities;

[0060] Domain syntax extension: By adding new syntax rules to the Gscript.g4 syntax file and implementing the QLExpression translation logic corresponding to the new rules in the CostQLExpressListener class, the domain syntax of Gscript can be extended.

[0061] Visualization extension: The DAG structure output by the dependency graph construction and optimization module is combined with the displayName and hasError fields of the nodes and directly used for front-end visualization of the computation graph, intuitively presenting dependency relationships and error nodes;

[0062] Validation extension: The ExpressionValidator module supports syntax checking and real-time calculation of pure literal expressions, and also supports configuring equivalent substitution rules for business-specific logical symbols to achieve expression validation for specific business scenarios.

[0063] The technical effects and advantages of this invention are as follows:

[0064] 1. It replaces the traditional manual translation of Excel formulas into code. Through modules such as automatic collection and standardization of Excel formulas, automatic compilation of Gscript domain expressions, and automatic construction of dependency graphs, it achieves end-to-end automation from input source parsing to the generation of executable models in the Java environment. Compared with manual operation, it can reduce labor costs by more than 90%, while avoiding problems such as syntax errors and dependency omissions caused by manual transcription, and significantly reducing the error rate.

[0065] 2. Visualize dependencies to improve auditability and maintainability. Through the dependency graph (DAG) construction and optimization module, discrete Excel formulas are transformed into structured directed acyclic graphs. Combined with node name labeling, complex dependencies are made intuitively visible. At the same time, the ContextCalculateLog module records logs in four steps: original formula, descriptive replacement, numerical replacement, and calculation result, completely preserving the calculation chain. This meets the compliance requirements of auditability and recapability in the cost domain and solves the problem of hidden and difficult-to-trace dependencies in traditional Excel models.

[0066] 3. Cross-environment compatibility and rule reuse: It supports the unified conversion of Excel formulas and cost domain-specific Gscript expressions into QLExpression models that can be directly executed in the Java ecosystem. This breaks down the barriers between local use of Excel and service-oriented information systems. The converted model can be repeatedly called in the Java runtime environment without the need for secondary development for different scenarios, greatly improving the reusability of cost calculation rules and solving the pain points of traditional models being difficult to migrate and unable to run across environments.

[0067] 4. Incremental calculation is highly efficient and supports complex multi-table linkage. The incremental linkage and multi-table calculation module establishes an association index of "table → external dependency parameter". When a partial update is triggered, only the table / row to be updated and the affected related tables are calculated. There is no need to recalculate the entire table. It can support the dynamic maintenance of complex cost models with multi-table linkage.

[0068] 5. Precise error location and improved system robustness: Error information is collected through ParserErrorListener and GscriptErrorListener for the Excel parsing and Gscript compilation processes, respectively, which can accurately locate the error location and reduce the troubleshooting time by 80% compared to the traditional fuzzy formula error prompts. At the same time, semantic validation intercepts invalid dependencies such as input variables exceeding the Excel range in advance, preventing errors from being passed to the execution stage and improving the overall stability of the system.

[0069] 6. Flexible expansion to adapt to dynamic business changes, supporting multi-dimensional expansion capabilities. Function library expansion only requires adding Java methods and registering them in FormulaExecutor. Domain syntax expansion can be achieved by modifying the Gscript.g4 syntax rules and CostQLExpressListener translation logic. Visualization expansion can directly implement front-end display based on DAG structure and node status (displayName / hasError). Validation expansion supports business-defined symbol equivalence replacement rules. The expansion process does not require modification of the core module code, conforms to the open / closed principle, and can quickly adapt to new industry cost calculation scenarios, extending the system lifecycle. Detailed Implementation

[0070] Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0071] The cost model automatic identification and conversion method provided by this invention includes:

[0072] Excel Formula Acquisition and Standardization Module: Reads a specified sheet and cell range from an Excel file, identifies drop-down dictionary cells, parses cell formulas and performs syntax standardization, and generates a standardized script compatible with QLExpression, along with a set of input variables and output formula node information;

[0073] Dependency Graph Construction and Optimization Module: Based on the formula node information output by the Excel formula collection and standardization module, a directed acyclic graph (DAG) is constructed. The DAG is then subjected to equivalence reference folding, redundant node removal, and semantic verification to generate an optimized dependency computation graph.

[0074] The Gscript domain expression to QLExpression compilation module: Based on the default Gscript domain syntax rules, it parses the domain / field identifiers in Gscript expressions and converts the functions, logical operations and conditional constructs in Gscript into QLExpression syntax;

[0075] The execution model generation and function library binding module maps the standardized script output by the Excel formula collection and standardization module and the QLExpression syntax output by the compilation module into an executable QLExpression expression model and binds it to a preset runtime function library.

[0076] Incremental linkage and multi-table calculation module: Organize two-dimensional table variables and global parameter variables, establish table-external parameter association index, and trigger local calculation of specified table / row; if the calculation depends on parameters of other tables, the preceding dependent tables are updated first, and then the affected tables are refreshed in a cascading manner.

[0077] Error detection and observability module: Collects and accurately locates syntax / semantic errors during the Excel formula acquisition and Gscript compilation process, and records calculation process logs to support auditing and problem review;

[0078] Through the collaboration of the above modules, the system can automatically identify, convert, execute, and incrementally maintain the QLExpression algorithm model executable in the Java runtime environment, from Excel files and Gscript domain expressions.

[0079] In practice, the problem of scattered Excel formulas and the inability to directly execute Gscript domain expressions is solved by using Excel formula collection and standardization and Gscript compilation as the "input processing layer".

[0080] Using dependency graph construction and optimization as an "intermediate abstraction layer," discrete formulas are transformed into structured computational graphs (DAGs) to clarify dependencies.

[0081] Using the execution model generation as the "landing layer," standardized scripts are mapped to QLExpression models executable in the Java environment;

[0082] Incremental linkage and error detection serve as the "optimization and protection layer" to achieve efficient computation and problem tracing;

[0083] Each module forms a closed loop through data flow (such as FormulaDto transmitting formula information and DAG supporting the calculation order), ultimately completing the automatic recognition, conversion, execution, and maintenance from Excel / Gscript to QLExpression.

[0084] This completely replaces the traditional "manual translation of Excel formulas into code" model, upgrading the level of automation from "human-led" to "full-chain automation," reducing labor costs and error rates by more than 90%.

[0085] Achieve cross-environment compatibility (from Excel / Gscript to Java+QLExpression), addressing the pain points of cost models being "difficult to reuse and impossible to audit";

[0086] Through a layered module design, a flexible architecture is provided for subsequent expansions (such as adding new functions and domain syntax), enabling easy expansion to new functions and new domains to meet the core requirement.

[0087] The Excel formula acquisition and standardization module includes:

[0088] The Apache POI tool is used to read a specified sheet and cell range from an Excel file, and DataValidationList is used to identify drop-down dictionary cells.

[0089] The ExcelLexer lexical analyzer and ExcelParser parser, based on the ANTLR tool, parse cell formulas one by one, and the ExcelDefaultListener performs the operations.

[0090] ExcelDefaultListener performs the following operations:

[0091] Remove format placeholders from cell references, such as converting A1 and $A$1 to ${A1};

[0092] Function mapping: Mapping SUM(x1, x2, ..., x...) n Convert the range to "x1+x2+...+x n "+range expansion sum of variables" will be used to expand AVG(x1, x2, ..., x... n ) is converted to "(x1+x2+...+x n ) / n" (where n is the number of arguments) converts the ROUNDUP, ROUNDDOWN, ROUND, CEILING, and FLOOR functions into QLExpression-compatible functions with the same lowercase name;

[0093] Expand cell range: Expand A m B k (m and k are positive integers) formatted range expanded to A m , {A m+1}, ...Ak, {B m}, B m+1 , ...{B k A list of variables, such as A1:B2, can be expanded to A1, {A2}, B1, {B2}.

[0094] Logical operator substitution: Replace "=" with "==". For example, AND(a1, a2, ..., a...)... n Replace ) with a1&&a2&&...&&an, and OR(a1, a2, ..., a) with a1&&a2&&...&&an. n Replace ) with (a1||a2||...||a n );

[0095] The ParserErrorListener collects formula parsing errors and records the row and column numbers of the erroneous cells, the original formula input, and the location of the error marked with an underline.

[0096] In practice, to address the issues of inconsistent Excel formula formats, hidden dependencies, and susceptibility to errors, a combination of toolchain and rule standardization is adopted:

[0097] Use Apache POI to read the specified sheet and range of the Excel file, and use DataValidationList to identify the drop-down dictionary cells to ensure complete coverage of the input source;

[0098] Lexical / syntactic analysis is performed based on ANTLR (ExcelLexer / ExcelParser), transforming unstructured Excel formulas into parsable syntax trees;

[0099] Standardized rules are executed by ExcelDefaultListener:

[0100] Cell references should be standardized to "cell address placeholders" to eliminate the format differences between A$1 and A1;

[0101] Function mapping (such as SUM to sum of variables, AVG to summation ÷ number of parameters) and range expansion (such as A1:B2 to 4 variables) solve the compatibility issues between Excel-specific syntax and QLExpression;

[0102] Logical sign substitution (=→==, AND→&&) unifies arithmetic rules;

[0103] ParserErrorListener is used to record row and column errors, original formula errors, and underline errors to achieve precise error location.

[0104] Thus, the output standardized script is 100% compatible with QLExpression, requiring no manual modification, solving the problem of traditional Excel formulas being difficult to migrate to information systems;

[0105] Range expansion and function mapping make complex dependencies in Excel explicit, providing a clear set of input variables for subsequent DAG construction;

[0106] Error location is accurate to the cell row and column and specific character position, making it easier to troubleshoot problems than the traditional method of only indicating formula errors, thus improving model building efficiency.

[0107] The dependency graph construction and optimization module includes:

[0108] Based on the FormulaDto data structure (containing fields such as isFormula, script, target, inputIds, hasError, etc.), which stores formula node information, a directed acyclic graph is constructed with the formula's reference to the input cell as edges.

[0109] Perform equivalent folding on the directed acyclic graph: if a formula node is only a direct reference to another cell (such as B). i ={A j If the formula node is removed and all references pointing to it are redirected to the referenced cell, i and j are positive integers.

[0110] Name labeling is performed on the directed acyclic graph: the text cell above the row where the formula node is located or the text cell to the left of the column where the formula node is located is selected as the node display name; if there is a display name conflict or no text cell is available, the cell address is used as the display name.

[0111] Perform semantic validation on the directed acyclic graph: if the input variable of the formula node points to a cell outside the range read by the Excel file, then mark the hasError field of that node as true.

[0112] In practice, based on structured abstraction and redundancy elimination logic, the standardized formula is transformed into an optimizable computational graph:

[0113] Using FormulaDto (including formula type, script, input / output identifier, and error status) as the smallest unit, a DAG (Directed Acyclic Graph) is constructed with the formula to the input cell as the edge to intuitively present the dependency relationship;

[0114] Perform equivalent folding: For nodes that directly reference (such as B0={A0}), remove redundant nodes and redirect the reference to the source cell to reduce the computation link length;

[0115] Execution name labeling: Prioritize using the text cell above the same row / to the left of the same column as the node display name to avoid the obscurity of using only cell address (such as A1);

[0116] Perform semantic validation: If the input variable points to a cell outside the range that Excel reads, mark the node as an error (hasError=true) to intercept invalid dependencies in advance.

[0117] By using a DAG structure to make dependencies visible, the problem of hidden and difficult-to-trace dependencies in traditional Excel formulas is solved, making manual auditing and maintenance easier.

[0118] Equivalent folding eliminates 30% to 50% of redundant nodes (depending on the complexity of Excel), reducing the repetitive overhead of subsequent calculations;

[0119] Name labeling and semantic verification make nodes easy to understand and error-free, reducing the processing cost of subsequent modules and improving the robustness of the entire algorithm.

[0120] The compilation module for converting Gscript domain expressions to QLExpression includes: defining the Gscript syntax rules for this domain through the Gscript.g4 syntax file, and having the compilation operation performed by CostQLExpressListener;

[0121] Specifically, CostQLExpressListener performs the following compilation operations:

[0122] Domain / Field Resolution: The nameToScopeMap maps the domain name in the $Scope format in Gscript to the domain code (e.g., "raw materials" is mapped to CBD_RAW_MATERIAL). The ParamMetaDto data structure (which associates the domain code, parameter display name, and actual field name) resolves the field identifier in the $Scope!Field or !Field (current domain) format to the actual field name.

[0123] Function conversion: Convert SUM(a, b, c) and AVG(a, b, c) in Gscript to QLExpression's sum([a, b, c]) and avg([a, b, c]) array format functions respectively; convert IF(cond, then, else) to a ternary expression "cond?then:else"; convert the condition list of SUMIFS / GETIFS / MINIFS / MAxIFS to a condition mapping in the format of NewMap('field':'value', ...); convert the conditions of GETIFS2 / MINIFS2 / MAxIFS2 / VLOOKUP2 to a double mapping of "field → value" NewMap and "field → operator" NewMap; convert int(x) to getInt(x);

[0124] Unify logical operations: unify and / a&& and or / || in Gscript to "&&" and "||" in QLExpression respectively, and replace the comparison symbol "<>" with "!=".

[0125] In practical implementation, to address the issue that cost domain-specific expressions (Gscript) cannot be directly executed, a combined approach of syntax definition and semantic translation is adopted:

[0126] The Gscript.g4 syntax file defines expression rules specific to the cost domain (such as "$Scope!Field" domain field identifiers and SUMIFS multi-condition aggregation).

[0127] Semantic translation was performed by CostQLExpressListener:

[0128] Domain / Field Resolution: Using nameToScopeMap (domain name → domain code) and ParamMetaDto (parameter name → actual field name), "$raw material!unit price" is resolved to "CBD_RAW_MATERIAL.price";

[0129] Function conversion: Convert Gscript-specific functions (such as SUMIFS, VLOOKUP2) into QLExpression-compatible formats (such as SUMIFS→NewMap conditional mapping), and convert IF expressions into ternary expressions;

[0130] Unify logical operations: Replace "and / or" and "<>" with "&& / ||" and "!=" respectively to eliminate syntactic differences.

[0131] This enables one-click translation of domain-specific expressions, eliminating the need for manual rewriting of Gscript and addressing the pain point that domain-specific languages ​​are difficult to reuse in general execution environments;

[0132] Domain / field resolution ensures the accuracy of the translation and avoids errors from manual mapping;

[0133] The unification of functions and operation rules allows compiled scripts to be directly connected to subsequent execution modules, improving the migration efficiency of domain models.

[0134] The execution model generation and function library binding module includes:

[0135] The standardized QLExpression script is constructed into an executable expression called DefaultExpression, and the input variables of the expression are automatically parsed through the getInputVars() method;

[0136] The default runtime function library is bound through the registerFunc method of the ExpressionRunner core execution class;

[0137] Specifically, the function library includes:

[0138] Mathematical / rounding functions: getInt(x), sqrt(x), roundup(x, n), rounddown(x, n), ceiling(x, n), floor(x, n), pow(x, y), ln(x), log(x, n) (where n is the logarithmic base);

[0139] Statistical / aggregate function: sum([x1, x2, ..., x...)) n ]), avg([x1, x2, ..., x n ]), sumifs(Scope, NewMap,...), maxifs(Scope, NewMap,...), minifs(Scope, NewMap,...), getifs(Scope, NewMap,...);

[0140] Search / sort / get value functions: vlookup(Scope, NewMap, queryFields, resultFields), vlookup2(Scope, NewMap1, NewMap2, queryFields, resultFields), sort(arr, idx, order) (arr is an array, idx is the sort index, order is the sort direction), getat(arr, i) (i is the array index), join(arr, sep) (sep is the join operator);

[0141] Trigonometric functions: sin(x), cos(x), tan(x), cot(x), sec(x), csc(x);

[0142] Using SpringExpressContext as the runtime context, the variable value is first obtained from the local context; if the variable does not exist in the local context, the corresponding Bean is obtained from the Spring container.

[0143] During execution, a topological "ready to execute" strategy is adopted: the calculation is triggered only when all input variables of the expression exist in the context. After the calculation is completed, the output variable is added to the context, and the expression is removed from the set to be calculated. The process is repeated recursively until there are no more expressions to be calculated.

[0144] In practice, based on executable model construction, function capability support, and efficient execution strategies, standardized scripts are implemented and run.

[0145] The standardized script is encapsulated as DefaultExpression, and input variables are automatically extracted using getInputVars(), simplifying model building;

[0146] By using ExpressionRunner.registerFunc, four categories of runtime function libraries (mathematical / rounding, statistical / aggregative, retrieval / sorting, and trigonometric functions) are bound, covering the core needs of cost calculation;

[0147] It uses SpringExpressContext as the context: it prioritizes reading local variables, and retrieves beans from the Spring container when they are missing, supporting integration with business systems;

[0148] During execution, a topology-based ready-to-execute strategy is adopted: the expression is only evaluated when all input variables are ready, and after evaluation, the output variable is added to the context and the expression is removed to avoid repeated execution.

[0149] This allows the generated model to run directly in a Java environment, solving the problem that traditional Excel formulas cannot be integrated into information systems, and enabling one-time conversion and reuse in multiple environments;

[0150] The function library covers more than 95% of cost calculation scenarios, eliminating the need for additional development of basic functions;

[0151] The topology execution strategy reduces redundant computations by more than 60% (depending on dependency complexity), improves execution efficiency, and meets the requirements for runnable and high-performance computing.

[0152] The incremental linkage and multi-table calculation module includes:

[0153] The CostRunnerContext module organizes the TableVariable (two-dimensional table variable) and the IVariable (global parameter variable), where the TableVariable carries a set of table-level expressions, a set of row-level expressions, and two-dimensional table values.

[0154] Scan the input and output variables of the expressions in each TableVariable, extract the set of external dependency parameters, and establish an association index from the table to the external dependency parameters;

[0155] When a partial update is triggered, specify the table identifier and row ID to be updated:

[0156] If the expression to be updated is a row-level expression, then the header-level expression of the output variable with the same name will be overridden first.

[0157] If the input of the expression to be updated depends on parameters calculated from other tables, then the expression in the preceding table is updated first according to the dependency relationship.

[0158] Based on the table-to-external dependency parameter index, locate other tables affected by the current update, perform cascading refresh calculations on them, and write the calculation results back to the context.

[0159] In practice, for scenarios involving multiple table interactions and partial updates, a combination of variable organization, dependent indexes, and incremental triggering is adopted:

[0160] Use TableVariable (to carry table-level / row-level expressions and two-dimensional table data) and IVariable (to carry global parameters) to uniformly organize multi-table and parameter data;

[0161] Scan the input / output variables of the expressions in the table, create an index for "Table → External Dependency Parameters", and locate the scope of influence of parameter changes;

[0162] When a partial update is triggered:

[0163] Row-level expressions override table header-level expressions with the same name to ensure data consistency.

[0164] If the table depends on parameters from other tables, update the preceding dependent tables first.

[0165] Cascading refresh of affected tables based on indexes avoids full recalculation.

[0166] This allows for partial updates that only trigger calculations in the relevant tables / rows, resulting in a 50% to 80% improvement in efficiency compared to traditional full recalculation. The larger the table data volume, the more significant the improvement.

[0167] Multi-table linkage mechanisms solve the problem of difficult maintenance of cross-table dependencies and ensure the consistency of data updates;

[0168] The index design enables precise location of the impact range, avoids invalid calculations, and supports the dynamic maintenance of complex cost models.

[0169] The error detection and observability module includes:

[0170] Excel formula parsing error handling: The ParserErrorListener outputs error information, which includes the row and column numbers of the erroneous cell, the original formula input text, and the underlined location of the error.

[0171] Gscript compilation error handling: Errors are collected by GscriptErrorListener, and a summary of error information including error symbol ranges and text highlighting is output;

[0172] Calculation process logging: The ContextCalculateLog module records logs in a four-step format: original formula, descriptive replacement, numerical replacement, and calculation result. Descriptive replacement replaces variable placeholders with variable display names, and numerical replacement replaces variable placeholders with actual input values.

[0173] In practice, based on layered error collection and full-link logging, the problem can be accurately located and traced:

[0174] Excel parsing errors: ParserErrorListener records row and column errors, original formula errors, and underline errors to locate syntax errors;

[0175] Gscript compilation errors: GscriptErrorListener collects error information about symbol ranges and text highlighting to locate syntax problems in the relevant area;

[0176] Calculation Log: Records the calculation process in four steps through ContextCalculateLog: “Original Formula → Description Replacement → Numerical Replacement → Result”, presenting the complete calculation process (e.g., “SUM(A1:A2)” → “SUM(Unit Price 1:Unit Price 2)” → “SUM(10:20)” → “30”).

[0177] By locating errors with character-level precision, it reduces troubleshooting time by 80% compared to traditional vague prompts;

[0178] The four-step logging enables full-chain auditability, facilitating the tracing of calculation logic and data sources, and meeting the compliance requirements of auditability and retrospective capability in the cost field;

[0179] Intercepting parsing / compilation errors in advance prevents errors from being propagated to the execution stage, thus improving system robustness.

[0180] It also includes core data structure support, specifically:

[0181] The FormulaDto data structure is used to abstract Excel cells. The FormulaDto contains the following fields: isFormula (whether it is a formula cell), isSumNode (whether it is a summation node), isDictionary (whether it is a dictionary cell), source (original formula), script (normalized script), target (target variable identifier), inputIds (set of input variable identifiers), errorDtoList (error message list), displayName (display name), hasError (whether an error exists), and options (dictionary options).

[0182] The ExcelRange data structure is used to parse and offset Excel cell coordinates. ExcelRange provides the fromStr(String rangeStr) method (e.g., to convert "A8:K" to "A9:K"). 74"parse into coordinate range), shifting(in trows, intcols) method (implements row / column offset of coordinate range, trows is the row offset, tcols is the column offset);

[0183] The IVariable interface and its implementation classes are used to carry variable values: DecimalVariable carries numeric variables, ObjectVariable carries object variables, TableVariable carries two-dimensional table variables, and ExpressionVariable carries expression variables. All IVariable implementation classes support setting default values.

[0184] In practice, a unified data interaction carrier is provided for each module through a dedicated data structure abstraction:

[0185] FormulaDto: Abstracts Excel cells (including formula type, script, input / output, error status, etc.), unifying the description format of formula nodes;

[0186] ExcelRange: Provides a formStr (parses "A8:K") function. 74 "Equal range" and "shifting" (row / column offset) methods allow for flexible handling of Excel coordinates;

[0187] IVariable and its implementation classes: DecimalVariable (numerical), ObjectVariable (object), TableVariable (two-dimensional table), ExpressionVariable (expression), cover the needs of carrying different types of variables, and support setting default values.

[0188] By abstracting data structures, the data interaction between modules is standardized, avoiding adaptation costs caused by inconsistent formats;

[0189] ExcelRange's flexible handling supports parsing and offsetting any cell range, adapting to different Excel cost models.

[0190] The multiple implementation classes of IVariable meet the needs of managing complex variables such as "numerical values / tables / expressions", and provide stable data support for incremental linkage and execution models.

[0191] It also includes an extended capability module, specifically implemented as follows:

[0192] Function library extension: Add a Java method to the FormulaExecutor or ExcelFormulaExecutor class, and register the new method to the runtime function library using the registerFunc method of ExpressionRunner to extend function capabilities;

[0193] Domain syntax extension: By adding new syntax rules to the Gscript.g4 syntax file and implementing the QLExpression translation logic corresponding to the new rules in the CostQLExpressListener class, the domain syntax of Gscript can be extended.

[0194] Visualization extension: The DAG structure output by the dependency graph construction and optimization module is combined with the displayName and hasError fields of the nodes and directly used for front-end visualization of the computation graph, intuitively presenting dependency relationships and error nodes;

[0195] Validation extension: The ExpressionValidator module supports syntax checking and real-time calculation of pure literal expressions, and also supports configuring equivalent substitution rules for business-specific logical symbols to achieve expression validation for specific business scenarios.

[0196] In practical implementation, based on loosely coupled design and standardized extension interfaces, flexible iteration of the algorithm is supported:

[0197] Function library extension: Add a new Java method to FormulaExecutor / ExcelFormulaExecutor and register it using ExpressionRunner.registerFunc to extend the function;

[0198] Domain syntax extension: Add syntax rules to Gscript.g4 and implement translation logic in CostQLExpressListener to extend Gscript capabilities;

[0199] Visualization extension: Combine the DAG with the nodes displayName (display name) and hasError (error status) for direct front-end display;

[0200] Validation extension: Supports "pure literal validation" and "custom symbol replacement validation" through ExpressionValidator, adapting to business-specific rules.

[0201] This allows the expansion process to proceed without modifying the core module code, adhering to the open / closed principle and reducing iteration risks;

[0202] Function / syntax extensions support new cost calculation scenarios (such as custom functions for specific industries), visualization extensions improve user experience, and validation extensions meet business compliance requirements.

[0203] It enables algorithms to adapt to future business changes, extends the system's lifecycle, and demonstrates its core advantage of easy scalability.

[0204] The automatic cost model identification and conversion method provided by this invention has the following specific implementation process:

[0205] Preliminary preparations: Environment and dependency configuration

[0206] Include the core dependency packages:

[0207] Apache POI: Used to read Excel files;

[0208] ANTLR: Lexical and syntactic analysis for Excel / Gscript;

[0209] QLExpress: Used for expression execution;

[0210] SpringContext: Used for runtime context and bean management.

[0211] Deploy the core code module:

[0212] Syntax files: com / gantang / prd / commons / antlr / excel / Excel.g4 (Excel syntax), com / gantang / prd / commons / antlr / gscript / Gscript.g4 (Gscript syntax);

[0213] Parsing modules: FormulaReader.java (Excel reading and formula parsing), ExcelDefaultListener.java (Excel standardization), CostQLExpressListener.java (Gscript compilation);

[0214] Model modules: Graph.java (DAG construction), FormulaDto.java (formula node abstraction), TableVariable.java (table variables);

[0215] Execution modules: ExpressionRunner.java (core execution), CostRunnerContext.java (incremental linkage);

[0216] The protection modules are: ParserErrorListener.java (Excel error collection) and ContextCalculateLog.java (calculation log).

[0217] Step 1: Excel Cost Model Analysis and Standardization

[0218] Reading Excel files:

[0219] Call FormulaReader.load(StringexcelPath,StringsheetName,ExcelRangerange,List <dictionarydto>The `dictionaries` method uses Apache POI to read the "target sheet" (e.g., "cost calculation sheet") and cell range (e.g., `ExcelRange.fromStr("A8:K")`) of a specified Excel file. 74 It identifies the dropdown dictionary cell (DataValidationList) and stores the dictionary options in dictionaries.

[0220] Formula Analysis and Standardization:

[0221] Call FormulaReader.parse(Map)<String,Cell> cellMap, List <dictionarydto>(dictionaries), based on ExcelLexer and ExcelParser generated by ANTLR, parses cell formulas one by one:

[0222] ExcelDefaultListener will convert A1 / $A to {A1}, SUM(A1:B2) to A1+{A2}+B1+{B2}, and AND(A1>0,B1>0) to {A1}>0&&{B1}>0`.

[0223] Errors (such as "Cell C10: Formula is missing a right parenthesis") are collected by ParserErrorListener, recording the row and column, the original formula, and the underline error position;

[0224] Output: Generates a List <formuladto>It includes the standardized script for each cell, input variables (inputIds), error messages (errorDtoList), etc.

[0225] Step 2: Dependency Graph (DAG) Construction and Optimization

[0226] DAG initialization:

[0227] Create a new Graph instance, passing in the List output in step 1. <formuladto>A Directed Acyclic Graph (DAG) is constructed with "FormulaDto.target(output variable)" as the node and "FormulaDto.inputIds(input variable)" as the edge.

[0228] DAG optimization:

[0229] Equivalent folding: Traverse the DAG. If the script of a FormulaDto is ${B0}=${A0} and A0 is valid, remove the B0 node and redirect all references pointing to B0 to A0.

[0230] Name labeling: For each node, prioritize reading the text cell value of "one row above in the same row" or "one column to the left in the same column" as the displayName (e.g., the displayName of A1 is "Raw Material Unit Price"). In case of conflict, use the cell address (e.g., A1).

[0231] Semantic validation: If a node's inputIds contains "Z100" (exceeding the range "A8:K" read in step 1), ... 74 (range), set the hasError of that node to true;

[0232] Output: The optimized DAG (including node display names and error status) can be used for front-end visualization (such as drawing nodes and dependent edges using ECharts).

[0233] Step 3: Compiling Gscript Domain Expressions

[0234] Compilation and initialization:

[0235] Create a new CostQLExpressListener instance and pass in currentScope (current domain, such as "CBD_RAW_MATERIAL"), nameToScopeMap (domain name → domain code, such as "raw materials" → "CBD_RAW_MATERIAL"), and paramMetaList (parameter metadata, including paramName → fieldName mapping).

[0236] Gscript parsing and compilation:

[0237] For Gscript expressions in the cost domain (such as "sumifs($raw materials!quantity,$raw materials!unit price>100)"), the parsing syntax tree is generated by GscriptLexer and GscriptParser using ANTLR, and then executed by CostQLExpressListener.

[0238] Domain resolution: Resolve "$raw materials!quantity" to "CBD_RAW_MATERIAL.quantity";

[0239] Function conversion: Convert SUMIFS to "sumifs("CBD_RAW_MATERIAL",NewMap("price":">100"),[])";

[0240] Symbol consistency: ">" will be retained to ensure compatibility with QLExpression;

[0241] Output: The compiled QLExpression script (e.g., "sumifs("CBD_RAW_MATERIAL",NewMap("price":">100"),[])"). If there are syntax errors (e.g., unmapped fields), the error information will be recorded by GscriptErrorListener.

[0242] Step 4: Perform model generation and incremental calculation

[0243] Execution model building:

[0244] For the Excel standardization script in step 1 and the Gscript compilation script in step 3, construct a DefaultExpression instance (e.g., newDefaultExpression("${A1}+${A2}", "C1")) and automatically extract the input variables (e.g., [A1,A2]) using expression.getInputVars().

[0245] Call ExpressionRunner.registerFunc() to bind the runtime function library:

[0246] Mathematical functions: getInt(12.3) → 12, sqrt(16) → 4;

[0247] Statistical functions: sum([10,20]) → 30, sumifs(...) → sum of values ​​that meet the conditions;

[0248] The lookup function `vlookup("CBD_RAW_MATERIAL",NewMap("id":1),["price"],["price"])` retrieves the unit price for id=1.

[0249] Incremental calculation trigger:

[0250] Create a new CostRunnerContext instance and organize the variables: use TableVariable to hold the "Raw Materials Table" (containing the row-level expression "Amount = Quantity × Unit Price"), and use DecimalVariable to hold the global parameter "Tax Rate = 0.13";

[0251] Triggering a partial update: Calling costRunnerContext.updateTable("Raw Materials Table", 1) (updating the first row of the "Raw Materials Table"):

[0252] The row-level expression "Amount = Quantity × Unit Price" in the first row will be executed first (overriding the header-level expression with the same name).

[0253] If the expression depends on "tax rate" (from global parameters), read the parameter value directly; if it depends on "total cost from other tables", update the expression in "other tables" first.

[0254] Based on the "Table → External Parameters" index, locate the "Total Cost Table" affected by the change in "Amount" and trigger its cascading refresh;

[0255] Result record:

[0256] The calculation process (e.g., "Amount = Quantity × Unit Price" → "Amount = 10 × 20" → "Amount = 200") is recorded by ContextCalculateLog, and the log is stored in the database to support subsequent auditing and review.

[0257] Step 5: Error Detection and Verification

[0258] Troubleshooting:

[0259] If there is a node with hasError=true in the DAG in step 2, the node will be marked in red when the front-end is visualized, and an error message will be displayed when the mouse hovers over it (such as "Input variable Z100 is out of range").

[0260] If a Gscript compilation error occurs in step 3, the output will be "Symbol range [15,20]: Field 'auxiliary' is not mapped", which will help users modify the Gscript.

[0261] Expression validation:

[0262] Call ExpressionValidator.validate(Stringscript) to perform immediate calculation on pure literal expressions (such as "10+20×3") (resulting in 70). For expressions containing custom symbols (such as "${tax rate}×0.9"), validate whether "tax rate" is registered to ensure there are no syntax / semantic errors before execution.

[0263] Implementation effect verification

[0264] By following the steps above, the following can be achieved:

[0265] From Excel "Cost Calculation Sheet" (A8:K) 74 (Scope) Automatically generates QLExpression executable models without manual coding;

[0266] The Gscript expression "sumifs($raw materials!quantity,$raw materials!unit price>100)" is automatically compiled into an executable script, which is 10 times more efficient than manual rewriting.

[0267] When partially updating the first row of the "Raw Materials Table", only the relevant table / row is calculated, reducing the calculation time from 500ms for a full recalculation to 50ms, improving efficiency by 90%.

[0268] Error location accuracy is down to the cell character level, and logs can trace every step of the calculation logic, meeting the core requirements of automation, maintainability, and auditability.

[0269] The above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.< / formuladto> < / formuladto> < / dictionarydto> < / dictionarydto>

Claims

1. A cost model automatic recognition conversion method, characterized in that, Comprise: An Excel formula acquisition and standardization module: reads a specified sheet and cell range in an Excel file, identifies a pull-down dictionary cell, parses a cell formula and performs syntax standardization, generates a standardized script compatible with QLExpression, and an input variable set and output formula node information; A dependency graph construction and optimization module: based on the formula node information output by the Excel formula acquisition and standardization module, constructs a directed acyclic graph, performs equivalent reference folding, redundant node removal, and semantic verification on the directed acyclic graph, and generates an optimized dependency computation graph; The dependency graph construction and optimization module comprises: Based on the formula node information stored in the FormulaDto data structure, a directed acyclic graph is constructed with the reference of the input cell to the formula as the edge; Equivalent folding is performed on the directed acyclic graph: if a formula node is only a direct reference to another cell and the reference source is legal, the formula node is removed and all references to the node are redirected to the referenced cell; Name labeling is performed on the directed acyclic graph: preferentially selecting a text cell above the formula node or a text cell to the left of the formula node as the node display name; if the display name conflicts or there is no available text cell, the cell address is used as the display name; Semantic verification is performed on the directed acyclic graph: if the input variable of a formula node points to a cell outside the read range of the Excel file, the hasError field of the node is marked as true; A Gscript domain expression to QLExpression compilation module: according to the preset Gscript domain syntax rules, the domain / field identifier in the Gscript expression is parsed, and the function, logical operation and condition structure in Gscript are converted into QLExpression syntax; An execution model generation and function library binding module: maps the standardized script output by the Excel formula acquisition and standardization module and the QLExpression syntax output by the compilation module to an executable QLExpression expression model, and binds a preset runtime function library; Incremental linkage and multi-table calculation module: organizes two-dimensional table variables and global parameter variables, establishes a table-external parameter association index, and triggers local calculation of a specified table / row; if the calculation depends on parameters of other tables, the pre-requisite dependent tables are updated first, and then the affected tables are refreshed in cascade; Error detection and observability module: collects syntax / semantic errors in the Excel formula acquisition and Gscript compilation processes and accurately locates them, and records calculation process logs to support auditing and problem review; Through the cooperation of the above modules, automatic recognition, conversion, execution and incremental maintenance of a Java runtime environment executable QLExpression algorithm model from an Excel file and a Gscript domain expression are realized.

2. The cost model automatic identification conversion method of claim 1, wherein, The Excel formula acquisition and standardization module comprises: Apache POI is used to read the specified sheet and cell range in the Excel file, and DataValidationList is used to identify the pull-down dictionary cell; ANTLR-based ExcelLexer and ExcelParser are used to parse the cell formula one by one, and ExcelDefaultListener is used to execute the operation.

3. The cost model automatic identification conversion method of claim 1, wherein, The Gscript domain expression to QLExpression compilation module includes: The Gscript.g4 grammar file is used to define the Gscript grammar rules in the cost domain, and the CostQLExpressListener is used to perform the compilation operation.

4. The cost model automatic identification conversion method of claim 1, wherein, The execution model generation and function library binding module includes: The standardized QLExpression script is constructed into a DefaultExpression executable expression, and the getInputVars() method is used to automatically parse the input variables of the expression; The registerFunc method of the ExpressionRunner core execution class is used to bind the preset runtime function library.

5. The cost model automatic identification conversion method of claim 1, wherein, The incremental linkage and multi-table calculation module includes: The CostRunnerContext module is used to organize the TableVariable two-dimensional table variable and IVariable global parameter variable, wherein the TableVariable carries the table-level expression set, the row-level expression set, and the two-dimensional table value; The input and output variables of the expressions in each TableVariable are scanned, the external dependent parameter set is extracted, and the association index of the table and the external dependent parameter is established; When a local update is triggered, the table identifier and row ID to be updated are specified: If the expression to be updated is a row-level expression, the table header-level expression of the same name output variable is preferentially overwritten; If the input dependent parameter of the expression to be updated is obtained by calculating other tables, the expression of the pre-table is updated according to the dependency relationship first; Based on the table to external dependent parameter index, the other tables affected by the current update are located, cascaded refresh calculation is performed, and the calculation results are written back to the context.

6. The cost model automatic identification conversion method of claim 1, wherein, The error detection and observability module includes: Excel formula parsing error processing: the ParserErrorListener outputs error information, which includes the row and column number of the error cell, the original formula input text, and the error position marked with an underline; Gscript compilation error processing: GscriptErrorListener collects errors and outputs error summary information containing error symbol intervals and text highlights; Calculation process log recording: the ContextCalculateLog module records logs in the original formula, descriptive replacement, numerical replacement, and calculation result four-step format, wherein the descriptive replacement replaces the variable placeholder with the variable display name, and the numerical replacement replaces the variable placeholder with the actual input value.

7. The cost model automatic identification conversion method of claim 1, wherein, It also includes core data structure support, specifically: The FormulaDto data structure is used to abstract Excel cells, which includes whether it is a formula cell, whether it is a sum node, whether it is a dictionary cell, an original formula, a standardized script, a target variable identifier, a set of input variable identifiers, an error information list, a display name, whether there is an error, a dictionary option field; The ExcelRange data structure is used to parse and offset Excel cell coordinates, which provides the fromStr(String rangeStr) method to parse the coordinate range and the shifting(in trows, in tcols) method to realize the row / column offset of the coordinate range, where trows is the row offset and tcols is the column offset. The IVariable interface and its implementation classes are used to carry variable values: DecimalVariable carries numerical variables, ObjectVariable carries object variables, TableVariable carries two-dimensional table variables, and ExpressionVariable carries expression variables, and all IVariable implementation classes support default value setting.

8. The cost model automatic identification conversion method of claim 3, wherein, It also includes an extension capability module, which is specifically implemented as: Function library extension: add Java methods in the FormulaExecutor or ExcelFormulaExecutor class, register the added methods to the runtime function library through the registerFunc method of ExpressionRunner, which can extend the function capability; Domain syntax extension: add new syntax rules in the Gscript.g4 syntax file and implement the QLExpression translation logic corresponding to the added rules in the CostQLExpressListener class, which can extend the domain syntax of Gscript; Visualization extension: use the directed acyclic graph structure output by the dependency graph construction and optimization module, combined with the displayName and hasError fields of the nodes, to directly visualize the calculation graph in the front end, and intuitively present the dependency relationship and error nodes; Verification extension: support syntax checking and instant calculation of literal expression through the ExpressionValidator module, and support configuration of business custom logic symbol equivalence replacement rules to realize expression verification in business-specific scenarios.