A method for reverse engineering of spreadsheet formula calculation rules

Abstract

This invention provides a method for reverse engineering calculation rules of spreadsheet formulas, including: S1, generating formula instances; S2, pre-encoding to obtain category labels for each formula instance, initializing the feature list to consist of type features of each input cell, and marking them as explained type features; S3, performing feature encoding on each formula instance to obtain samples corresponding to each formula instance, classifying all samples, and when the classification accuracy is 100%, executing step S5; S4, updating the feature list and returning to execute step S3; S5, when all features in the feature list are explained, executing step S7; S6, selecting any unexplained function feature, interpreting it, marking it as an explained function feature, and returning to execute step S5; S7, outputting calculation rules based on a decision tree and classification function. This invention achieves accurate reverse engineering of spreadsheet formulas.

Description

Technical Field

[0001] This invention belongs to the field of data processing technology, and in particular relates to a reverse engineering method for spreadsheet formula calculation rules. Background Technology

[0002] Spreadsheet formulas are widely used both domestically and internationally in fields such as finance, administration, human resources, production, and marketing, for example, in creating accounting statements, summarizing course grades, and analyzing market data. In these scenarios, users often utilize popular modern spreadsheet software (such as Excel, LibreOffice, Google Sheets, and Tencent Docs). However, most of these software programs are closed-source, meaning the underlying calculation rules of spreadsheet formulas are not directly known, and official documentation and tutorials often lack precise definitions of these rules. Furthermore, existing spreadsheet software generally lacks strict type checking of formula input parameters, allowing unsafe cell types (such as numerical values ​​stored as text) to be used as formula input, leading to either correct or incorrect results. These circumstances mean that even users familiar with the basic functions of formulas and official documentation risk misusing them. Because the underlying implementation of formulas in most existing spreadsheet software is unknown, developers of later spreadsheet software may implement the same formula differently. This can lead to inconsistent calculation results for the same spreadsheet formula across different software, potentially causing serious consequences in collaborative scenarios due to differing interpretations of the results. Reverse engineering the calculation rules of spreadsheet formulas is an effective solution to this problem.

[0003] Existing spreadsheet formula calculation rule analysis techniques primarily employ a top-down formal approach, constructing a new, idealized specification for spreadsheet formula syntax, operational semantics, and type systems. This helps developers of new spreadsheet software follow this specification to complete their development tasks and analyze existing spreadsheets and related VBA programs based on this idealized specification, detecting runtime type-unsafe formula calculation behaviors. The drawback of these techniques is that they make insufficient assumptions about the underlying complexity of formula implementations in today's mainstream spreadsheet software (such as Microsoft Excel, LibreOfficeCalc, and Apple Numbers). They cannot accurately detect different calculation results when dealing with complex cell content (such as dates, times, currency symbols, special text), multiple basic cell types (such as Boolean, text, date, and error types), and diverse cell data formats (such as "#,##0" for thousands separators and "yyyy-mm-dd" for dates). Therefore, their accuracy in detecting defects related to unexpected formula calculation results and inconsistencies in calculation results across software is not high. Summary of the Invention

[0004] This invention addresses the shortcomings of existing technologies by providing a method for reverse engineering spreadsheet formula calculation rules.

[0005] This invention provides a method for reverse engineering spreadsheet formula calculation rules, comprising:

[0006] S1, Generate a formula instance based on the spreadsheet software and spreadsheet formulas specified by the user; the formula instance is a tuple consisting of a list of input cells and an output cell.

[0007] S2, based on the properties of the calculation results specified by the user, pre-encodes the output cells of each formula instance according to the properties of the calculation results to obtain the category label of each formula instance, and initializes the feature list to a feature list composed of the type features of each input cell, and marks it as the explained type feature;

[0008] S3. Encode the features of each formula instance according to the feature list to obtain the sample corresponding to each formula instance. Classify all samples based on a decision tree with no height limit and no pruning. When the classification accuracy is 100%, execute the operation of step S5. When the classification accuracy is less than 100%, provide feedback to the user on the false positive instance set and false negative instance set that have been classified incorrectly.

[0009] S4, Update the feature list, and return to the operation of step S3;

[0010] S5, when all features in the feature list are explained, proceed with step S7;

[0011] S6, If there are unexplained features in the feature list, select any unexplained function feature, interpret it, mark it as an interpreted function feature, and return to execute step S5.

[0012] S7 outputs calculation rules based on decision trees and classification functions.

[0013] Further, S1 includes:

[0014] Select multiple cells from the spreadsheet dataset, parse each extracted cell to obtain three cell attributes: basic type, content, and data format. Combine these three cell attributes into a new cell, and then combine the new cells into a set of input cell lists. Each input cell list in the set contains the same number of cells as the input cells in the formula.

[0015] Select multiple words from the dictionary vocabulary set, use each word as the content of a cell, combine each word with the basic data type "text" and the data format "general" to form a cell, and enumerate all combinations of cells for the cell as the first input cell list set; the number of cells for each cell combination in the first input cell list set is the same as the number of input cells for the formula;

[0016] Select all characters from the pre-stored character set, enumerate all combinations of characters with target lengths to obtain a string set, use each string in the string set as the content of a cell, combine each string with the "text" basic type and the "general" data format to form a cell, enumerate all cell combinations for the cell as the second input cell list set; the number of cells for each cell combination in the second input cell list set is the same as the number of input cells for the formula;

[0017] Each input cell list in the first and second input cell lists is used as the corresponding parameter of a given spreadsheet formula. The formula is run in the given spreadsheet software to obtain the output cell corresponding to each input cell list. Each input cell list and its corresponding output cell are combined into a formula instance to obtain a formula instance set.

[0018] Further, S4 includes:

[0019] Remove features from the summary feature list that do not appear in any decision node of the decision tree;

[0020] Based on the set of false positive instances classified by the decision tree, correct the existing features in the current feature list that are related to false positives;

[0021] Based on the set of false negative instances classified by the decision tree, new features are added to the feature list. These new features include type features, pattern features, and function features. When adding type or pattern features, they are marked as explained features. When adding function features, they are marked as unexplained features.

[0022] Returns the updated feature list.

[0023] Further, S6 includes:

[0024] S61 initializes the classification function as a function that takes the attributes of each cell in the input cell list of unexplained function features as parameters and returns the Boolean value "true".

[0025] S62: Input the attributes of each cell in the input cell list of each instance in the formula instance set as parameters to the classification function, call the classification function, and obtain the function's return value; when the function return values ​​of all instances are consistent with the instance's category label, execute step S64; otherwise, report the set of instances that have been classified incorrectly to the user and execute step S63.

[0026] S63, based on the set of instances that have been misclassified, update the classification function and return to the operation of step S62;

[0027] S64 returns the classification function.

[0028] Further, S7 includes:

[0029] Construct general programming language program statements for the features corresponding to each decision node of the decision tree: construct statements for the type feature that match the basic type of the input cell with the given basic type; construct statements for the pattern feature that match the cell content or data format with the given regular expression; and construct classification functions for the function feature to interpret the feature.

[0030] To construct a Boolean value in a general programming language that is equivalent to the label of each leaf node in the decision tree;

[0031] Construct conditional statements for each decision node of the decision tree to determine whether the feature corresponding to the target decision node is satisfied, and combine the conditional statements constructed by each decision node into a program segment that is the same as the decision process of the decision tree, starting from the root node of the decision tree.

[0032] Returns a function that takes the properties of all cells referenced by the target formula as arguments, returns a boolean value, and has the resulting code snippet as its function body.

[0033] This invention provides a method for reverse engineering spreadsheet formula calculation rules, including: S1, generating formula instances based on user-spreadsheet software and spreadsheet formulas; the formula instance is a tuple consisting of an input cell list and an output cell list; S2, pre-encoding the output cells of each formula instance according to the calculation result properties specified by the user to obtain a category label for each formula instance, and initializing the feature list to a feature list consisting of type features of each input cell, and marking them as explained type features; S3, performing feature encoding on each formula instance based on the feature list to obtain samples corresponding to each formula instance, classifying all samples based on a decision tree with no height limit and no pruning, and executing step S5 when the classification accuracy is 100%; otherwise, providing the user with a set of false positive instances and a set of false negative instances that have been classified incorrectly; S4, updating the feature list and returning to execute step S3; S5, when all features in the feature list are explained features, executing step S7; S6, when there are unexplained features in the feature list, selecting any unexplained function feature, interpreting it, marking it as an explained function feature, and returning to execute step S5; S7, outputting calculation rules based on the decision tree and classification function.

[0034] This invention addresses the common problems in existing spreadsheet formula calculation rule analysis technologies, such as detecting unexpected calculation results, inconsistencies in calculation results across different software, and low accuracy. It promotes spreadsheet users' understanding of the formula calculation rules of existing spreadsheet software, thereby avoiding unsafe and incompatible formula calculation behaviors. Attached Figure Description

[0035] To more clearly illustrate the technical solution of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0036] Figure 1 A flowchart of a method for reverse engineering spreadsheet formula calculation rules provided in an embodiment of the present invention;

[0037] Figure 2 A flowchart generated for the formula example provided in the embodiments of the present invention;

[0038] Figure 3 A flowchart for updating the feature list provided in an embodiment of the present invention;

[0039] Figure 4 A flowchart illustrating the features provided in the embodiments of the present invention;

[0040] Figure 5A flowchart illustrating the construction of calculation rules provided in embodiments of the present invention;

[0041] Figure 6 A flowchart illustrating formula instance generation, precoding, feature encoding, and decision tree classification provided in embodiments of the present invention;

[0042] Figure 7 A flowchart for updating another feature list provided in an embodiment of the present invention;

[0043] Figure 8 A flowchart illustrating yet another feature provided in an embodiment of the present invention;

[0044] Figure 9 A flowchart illustrating another calculation rule construction provided in an embodiment of the present invention. Detailed Implementation

[0045] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. 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.

[0046] The spreadsheet described in this invention is a grid-style data storage format consisting of rows and columns. The calculation rule is a function that takes the attributes of all cells referenced by the formula as input and outputs whether the formula's calculation result satisfies a certain property (either "true" or "false"). The classification function is a function that takes the attributes of each cell in the input cell list as parameters and returns a Boolean value (either "true" or "false"). The calculation result property is a user-defined Boolean value in the cell containing the formula after the spreadsheet formula completes its calculation, taking the value either "true" or "false". The category label is the value of the calculation result property for a specific formula instance. A feature is a Boolean value in the list of input cells, taking the value of either "true" or "false". The specific value depends on the category to which the feature belongs. A feature can be categorized into one of the following three types: (1) Type feature: whether the cell type of an input cell is equal to the user-specified type; (2) Pattern feature: whether the cell content or cell data format of an input cell is matched by a user-specified regular expression; (3) Function feature: based on the current formula, only one or more input cells are retained as formula parameters, and the remaining input cells are replaced with user-specified constants or some attributes of the input cells are replaced with user-specified values. The result of calculating the input cell list of each formula instance is then calculated using these values ​​as parameters for the new formula. The feature list is an ordered list of features. Precoding assigns a Boolean value (either "true" or "false") to the output cell of each formula instance in the formula instance set based on the given result property. Feature encoding assigns the same number of feature values ​​(either "true" or "false") to each formula instance in the formula instance set based on the features in the feature list. A false positive is a set of instances labeled "false" and classified as "true". A false negative is a set of instances labeled "true" and classified as "false". Feedback displays information related to the current classification result (e.g., classification accuracy, the set of instances that were misclassified) to the user.

[0047] like Figure 1 As shown, this embodiment of the invention provides a reverse engineering method for spreadsheet formula calculation rules, including:

[0048] S1, Generate a formula instance based on the spreadsheet software and spreadsheet formulas specified by the user; the formula instance is a tuple consisting of a list of input cells and an output cell.

[0049] A spreadsheet formula is an equation stored in a cell, taking other cells or constants as parameters and using arithmetic symbols and spreadsheet functions as operators, which can be calculated to obtain a result. A spreadsheet dataset is a collection of a certain number of spreadsheets.

[0050] For example, such as Figure 2 As shown, multiple cells from a publicly available spreadsheet dataset are selected. Each extracted cell is parsed to obtain three cell attributes: basic type, content, and data format. These three cell attributes are combined into new cells, and these new cells are then sequentially combined into a set of input cell lists. The number of cells in each input cell list in the set is the same as the number of input cells in the formula. In this embodiment, the spreadsheet dataset can be the EUSES dataset, the spreadsheet software is a software application that provides spreadsheet services (e.g., Microsoft Excel, LibreOfficeCalc, GoogleSheets, Tencent Docs, etc.), the spreadsheet processing software can be the third-party Python library openpyxl, and the number of input cells for the formula can be two. Cell attributes are the three attributes: basic cell type (e.g., numeric, text, boolean, error), cell content (i.e., the value stored in the cell, represented as a string), and cell data format (e.g., "general", "#,##0", "year / month / day", etc., which affect the display of cell content). Input cells are the cells used as parameters in the spreadsheet formula (e.g., cells A1 and B1 in the formula "=A1+B1").

[0051] Multiple words are selected from the dictionary vocabulary set, and each word is used as the content of a cell. Each word is combined with the basic data type "text" and the data format "general" to form a cell. All combinations of cells are enumerated to form a first input cell list set. The number of cells for each cell combination in the first input cell list set is the same as the number of input cells for the formula. In this embodiment of the invention, the dictionary vocabulary set can be all the words in the Oxford 5000 dictionary, the spreadsheet processing software can be the third-party Python library openpyxl, and the number of input cells for the formula can be two. The input cell list is an ordered list composed of input cells.

[0052] All characters are selected from a pre-stored character set. A string set is obtained by enumerating all combinations of characters with a target length (e.g., enumerating combinations of two characters 'a' and 'b' with a length of 2 yields the string sets "aa", "ab", "ba", and "bb"). Each string in the string set is used as the content of a cell. Each string is combined with a "text" basic data type and a "general" data format to form a cell. All combinations of cells are enumerated for each cell, forming a second input cell list set. The number of cells for each cell combination in the second input cell list set is the same as the number of input cells for the formula. In this embodiment, the character set can be a set of characters with ASCII values ​​from 32 to 126 in the ASCII character set, the spreadsheet processing software can be the third-party Python library openpyxl, the number of input cells for the formula can be 2, and the length of the enumerated strings can be 3.

[0053] Each input cell list from the first and second input cell lists is used as a corresponding parameter in a given spreadsheet formula. The formula is run in the given spreadsheet software to obtain the output cell corresponding to each input cell list. Each input cell list and its corresponding output cell are combined into a formula instance, resulting in a formula instance set. In this embodiment, each input cell list can be used as the first parameter of an IF function. Running the formula in Microsoft Excel yields the output cell corresponding to each input cell list. Each input cell list and its corresponding output cell are combined into a formula instance, resulting in a formula instance set of size 2,000,000. For example, in... Figure 6 In Example 1 shown, the input cell is of Boolean type and contains "TRUE", while the cell containing the calculation result is of numeric type and contains "1". The output cell is the cell containing the formula.

[0054] S2, based on the properties of the calculation results specified by the user, pre-encodes the output cells of each formula instance according to the properties of the calculation results to obtain the category label of each formula instance, and initializes the feature list to a feature list composed of the type features of each input cell, and marks it as the explained type feature.

[0055] For example, based on the nature of the calculation result specified by the user, the output cell of each formula instance is pre-encoded according to the nature of the calculation result to obtain a category label for each formula instance. The feature list is initialized with a feature list consisting of the type features of each input cell, specifically whether the "basic type" is equal to numeric, text, boolean, or error type (e.g., 8 type features when the formula has 2 input cells), and these type features are marked as "explained". In embodiments of the present invention, such as... Figure 6 As shown, the user-specified nature of the calculation result can be "Is the basic type of the output cell error?", and the initialized feature list can include four type features: "Is the basic type of the input cell boolean?", "Is the basic type of the input cell numeric?", "Is the basic type of the input cell text?", and "Is the basic type of the input cell error?". After precoding, category labels are obtained, for example in... Figure 6 In Example 1, because the cell containing the calculation result is not an error type, the label is "No" ("Yes" is equivalent to "True", "No" is equivalent to "False", and so on).

[0056] S3. Encode the features of each formula instance according to the feature list to obtain the sample corresponding to each formula instance. Classify all samples based on a decision tree with no height limit and no pruning. When the classification accuracy is 100%, execute the operation of step S5. When the classification accuracy is less than 100%, provide the user with the set of false positive instances and false negative instances that were classified incorrectly.

[0057] In an embodiment of the present invention, Figure 6 This demonstrates the process of the first round of feature encoding and decision tree classification. For example, in Example 1, because the input cell type is Boolean, only feature 1 has a value of "Yes," while the other three features have values ​​of "No." Using 2,000,000 instances as the training set, and training with an unrestricted decision tree as the classifier, the resulting decision tree achieves a classification accuracy of 99.9971%. Because the accuracy is below 100%, a false positive instance set (empty set in this example) and a false negative instance set (58 false negatives in this example) are extracted.

[0058] S4, update the feature list, and return to the operation of step S3.

[0059] For example, such as Figure 3 As shown, features that do not appear in each decision node of the decision tree are deleted from the summary feature list. In this embodiment of the invention, the feature that does not appear in each decision node of the decision tree is feature 4, namely "Is the basic type of the input cell an error?", and it is deleted from the feature list.

[0060] Based on the set of false positive instances classified by the decision tree, the existing features in the current feature list that are related to false positives are corrected. In this embodiment of the invention, since there are no false positives, this step can be skipped.

[0061] Based on the false negative instance set classified by the decision tree, new features are added to the feature list. These new features include type features, pattern features, and function features. When adding type or pattern features, they are marked as explained features; when adding function features, they are marked as unexplained features. In this embodiment, the user adds new features based on the false negative instance set classified by the decision tree. The user observes that the input cells of the 58 negative examples are all text-type, and their content is in the form of the strings "TRUE" or "FALSE," and both are case-insensitive. In the current decision tree, any text-type input cell used as the first parameter of an IF formula will be classified as "the cell containing the calculation result is of error type." However, if the input cell content is in the form of the strings "TRUE" or "FALSE" (case-insensitive), the calculation result is not of error type. Therefore, the user adds feature 4: "Does the input cell content match the regular expression ^([Tt][Rr][Uu][Ee]|[Ff][Aa][Ll][Ss][Ee])?".

[0062] Return the updated feature list. In this embodiment of the invention, the returned feature list may be "Is the basic type of the input cell Boolean?", "Is the basic type of the input cell numeric?", "Is the basic type of the input cell text?", and "Is the content of the input cell matched by the regular expression ^([Tt][Rr][Uu][Ee]|[Ff][Aa][Ll][Ss][Ee])$?".

[0063] S5. When the feature list contains only explained features, proceed to step S7. In this embodiment of the invention, since the feature list of the Microsoft Excel IF function does not contain any unexplained features, the process proceeds directly to step S6.

[0064] S6. If there are unexplained features in the feature list, select any unexplained feature, explain it, mark it as an explained feature, and return to the operation of step S5.

[0065] like Figure 4 and Figure 7As shown in the embodiments of the present invention, there are no unexplained features in the feature list of the IF function in Microsoft Excel. Therefore, the method for feature interpretation is illustrated using embodiments of the present invention. The given spreadsheet software is replaced with LibreOfficeCalc, while the properties of the given spreadsheet formula and the given calculation result remain unchanged. In the embodiments of the present invention, the feature interpretation method is as follows: Figure 8 As shown, the steps include:

[0066] S61, the classification function is initialized as a function that takes the attributes of each cell in the input cell list of unexplained function features as parameters and returns a Boolean value of "true". In this embodiment of the invention, the classification function is initialized as follows based on the cell attribute "input cell content c" (where c is the symbol for the variable "input cell content") of the unexplained feature. Figure 8 The example shown is a Python function f(c) that takes a variable c as a parameter and returns a boolean value "true".

[0067] In step S62, the cell attributes from the input cell list of each instance in the formula instance set are input as parameters to the classification function, the classification function is called, and the function's return value is obtained. If the function's return value for all instances matches the instance's category label, step S64 is executed; otherwise, the user is fed back a set of instances with classification errors, and step S63 is executed. In this embodiment of the invention, the cell content from the input cell list of each instance is passed to the classification function f(c), the classification function is called, and the function's return value is obtained. The classification function's accuracy is 26.8796%, which is lower than 100%, so the user is fed back a set of 1,462,408 instances with classification errors.

[0068] S63: Based on the set of instances where classification errors occurred, update the classification function and return to the operation of step S62. In an embodiment of the present invention, based on feedback, the user observes that when the input cell content is in the form of an integer or a floating-point number, the formula calculation result label is "No". Therefore, Python language is used to implement the matching of integers and floating-point numbers, thereby updating the classification function and jumping to step S62.

[0069] S64, Return the classification function. In this embodiment of the invention, after several executions of steps S62 and S63, when the classification accuracy is 100%, the classification function f(c) can be returned.

[0070] S7 outputs calculation rules based on decision trees and classification functions.

[0071] For example, such as Figure 5As shown, program statements in a general programming language are constructed for the features corresponding to each decision node of the decision tree: for type features, statements are constructed to match the basic type of the input cell with the given basic type; for pattern features, statements are constructed to match the cell content or data format with the given regular expression; and for function features, classification functions are constructed to interpret the feature. In this embodiment of the invention, a Python equality statement `type == "BOOL"` can be constructed for the type feature "Is the basic type of the input cell Boolean?", a Python equality statement `type == "TEXT"` can be constructed for the type feature "Is the basic type of the input cell text?", a Python equality statement `type == "NUM"` can be constructed for the type feature "Is the basic type of the input cell numeric?", and a Python regular expression matching statement `re.match(r "^([Tt][Rr][Uu][Ee]|[Ff][Aa][Ll][Ss][Ee])$"`, content can be constructed for the pattern feature "Is the content of the input cell matched by the regular expression ^([Tt][Rr][Uu][Ee]|[Ff][Aa][Ll][Ss][Ee])$", content).

[0072] For each leaf node of the decision tree, construct a Boolean value in a general programming language that is equivalent to the label. In this embodiment of the invention, a Python Boolean value True can be constructed for the label "output cell basic type is error", and a Python Boolean value False can be constructed for the label "output cell basic type is not error".

[0073] For each decision node in the decision tree, conditional statements are constructed to determine whether the feature corresponding to the target decision node is satisfied. Starting from the root node of the decision tree, the conditional statements constructed from each decision node are combined into a program fragment identical to the decision process of the decision tree. In this embodiment of the invention, Python conditional statements such as iftype == "BOOL": (newline) else: (newline)... can be constructed for decision nodes containing the type feature "Is the basic type of the input cell Boolean?". Starting from the root node of the decision tree, the conditional statements of each decision node are combined into a program fragment identical to the decision process of the decision tree, such as... Figure 9 The segment in the rule function from iftype == "BOOL" to return True.

[0074] This function returns a boolean value, taking the properties of all cells referenced by the target formula as parameters, and using the resulting code snippet as its function body. In this embodiment of the invention, the returned function is as follows: Figure 9The function shown in the box below is the output (i.e. the solution) of the problem of reverse engineering the spreadsheet formula calculation rules solved by this invention.

[0075] This invention automatically generates instances based on reverse engineering tasks according to specified spreadsheet formula calculation rules. After encoding, it classifies the instances using a decision tree, and interactively adds, deletes, and modifies the feature list based on classification feedback to obtain calculation rules that match all instances. It also proposes a feature interpretation method. The basic idea is to classify uninterpreted features in the feature list based on a classification function, and interactively update the classification function based on classification feedback to complete the feature interpretation. This invention overcomes the common problem in existing spreadsheet formula calculation rule analysis techniques that struggle to handle the complexity of modern spreadsheet software's underlying implementation. It greatly enhances users' understanding of modern spreadsheet formula calculation rules and facilitates developers in creating more compatible and user-friendly spreadsheet software.

[0076] The present invention has been described in detail above with reference to specific embodiments and exemplary examples; however, these descriptions should not be construed as limiting the present invention. Those skilled in the art will understand that various equivalent substitutions, modifications, or improvements can be made to the technical solutions and embodiments of the present invention without departing from the spirit and scope of the invention, and all such modifications and improvements fall within the scope of the present invention. The scope of protection of the present invention is defined by the appended claims.

Claims

1. An electronic spreadsheet formula calculation rule reverse engineering method, characterized by, The method comprises the following steps: S1, generating a formula instance according to the user-specified spreadsheet software and spreadsheet formula; The formula instance is a tuple composed of an input cell list and an output cell group; Step S1 specifically comprises: Selecting multiple cells of the spreadsheet from the spreadsheet data set, parsing each extracted cell to obtain three cell attributes of each cell, namely the basic type, content and data format of the cell; combining the three cell attributes into a new cell, and sequentially combining the new cell into an input cell list set, wherein each input cell list in the set contains the same number of cells as the input cells of the formula; Selecting multiple vocabularies from the dictionary vocabulary set, taking each vocabulary as the content of a cell, combining each vocabulary with the basic type of "text type" and the data format of "regular" into a cell, and enumerating all combinations of the cell as a first input cell list set; each cell combination in the first input cell list set contains the same number of cells as the input cells of the formula; Selecting all characters from the pre-stored character set, enumerating all combinations of the target length of the characters to obtain a string set, taking each string in the string set as the content of a cell, combining each string with the basic type of "text type" and the data format of "regular" into a cell, and enumerating all combinations of the cell as a second input cell list set; each cell combination in the second input cell list set contains the same number of cells as the input cells of the formula; Taking each input cell list in the first input cell list set and the second input cell list set as the corresponding parameter of the given spreadsheet formula, running the formula in the given spreadsheet software to obtain the output cell corresponding to each input cell list; combining each input cell list and the corresponding output cell into a formula instance to obtain a formula instance set; S2, according to the user-specified calculation result property, pre-encoding the output cell of each formula instance according to the calculation result property to obtain the category label of each formula instance, and initializing the feature list as a feature list composed of the type features of each input cell, and marking it as an explained type feature; S3, according to the feature list, feature-encoding each formula instance to obtain a sample corresponding to each formula instance, and classifying all samples based on a decision tree without height limitation and pruning; when the classification accuracy is 100%, executing the operation of step S5; when the classification accuracy is lower than 100%, feeding back the false positive instance set and the false negative instance set in which classification errors occur to the user; S4, updating the feature list and returning to execute the operation of step S3; S5, when all features in the feature list are explained features, executing the operation of step S7; S6, when there are unexplained features in the feature list, selecting an arbitrary unexplained function feature to be explained and marked as an explained function feature, and returning to execute the operation of step S5; S7, outputting the calculation rule based on the decision tree and the classification function.

2. The electronic spreadsheet formula computation rule reverse engineering method of claim 1, wherein, The S4 comprises: delete the features in the summary feature list which do not appear in the decision nodes of the decision tree; correct the existing features in the current feature list related to the false positives according to the false positive instance set classified by the decision tree; add new features to the feature list according to the false negative instance set classified by the decision tree, the new features including type features, pattern features and function features; when a type feature or a pattern feature is added, mark the type feature or the pattern feature as an explained feature; when a function feature is added, mark the function feature as an unexplained feature; return the updated feature list.

3. The electronic spreadsheet formula computation rule reverse engineering method of claim 1, wherein, The S6 comprises: S61, initialize the classification function as a function with the cell attribute of each cell in the input cell list of the unexplained function feature as the parameter and the Boolean value "true" as the return value; S62, input the cell attribute of each cell in the input cell list of each instance in the formula instance set into the classification function as the parameter, call the classification function, and obtain the return value of the function; when the function return value of all instances is consistent with the category label of the instance, perform the operation of step S64; otherwise, feed back the instance set in which classification error occurs to the user, and perform the operation of step S63; S63, update the classification function according to the instance set in which classification error occurs, and return to perform the operation of step S62; S64, return the classification function.

4. The spreadsheet formula computation rule reverse engineering method of claim 1, wherein, The S7 comprises: construct a program statement of a general programming language for the feature corresponding to each decision node of the decision tree: for a type feature, construct an equal statement of the basic type of the input cell and the given basic type, for a pattern feature, construct a regular expression matching statement of the cell content or data format and the given regular expression, and for a function feature, construct a classification function for explaining the feature; construct a Boolean value in the general programming language equal to the label for the label corresponding to each leaf node of the decision tree; construct a conditional statement for judging whether the feature corresponding to the target decision node is satisfied for each decision node of the decision tree, and combine the conditional statements constructed from each decision node into a program fragment identical to the decision process of the decision tree from the root node of the decision tree; return a function with the attributes of all cells referenced by the target formula as the parameter and a Boolean value as the return value, and the obtained program fragment as the function body. The S6 comprises: S61, initialize the classification function as a function with the cell attribute of each cell in the input cell list of the unexplained function feature as the parameter and the Boolean value "true" as the return value; S62, input the cell attribute of each cell in the input cell list of each instance in the formula instance set into the classification function as the parameter, call the classification function, and obtain the return value of the function; when the function return value of all instances is consistent with the category label of the instance, perform the operation of step S64; otherwise, feed back the instance set in which classification error occurs to the user, and perform the operation of step S63; S63, update the classification function according to the instance set in which classification error occurs, and return to perform the operation of step S62; S64, return the classification function. The S7 comprises: construct a program statement of a general programming language for the feature corresponding to each decision node of the decision tree: for a type feature, construct an equal statement of the basic type of the input cell and the given basic type, for a pattern feature, construct a regular expression matching statement of the cell content or data format and the given regular expression, and for a function feature, construct a classification function for explaining the feature; construct a Boolean value in the general programming language equal to the label for the label corresponding to each leaf node of the decision tree; construct a conditional statement for judging whether the feature corresponding to the target decision node is satisfied for each decision node of the decision tree, and combine the conditional statements constructed from each decision node into a program fragment identical to the decision process of the decision tree from the root node of the decision tree; return a function with the attributes of all cells referenced by the target formula as the parameter and a Boolean value as the return value, and the obtained program fragment as the function body.

Images