CN110738027B - Computer-implemented method, apparatus, and storage medium for generating spreadsheet formulas - Google Patents

Abstract

The application discloses a computer-implemented method, apparatus, and storage medium for generating a spreadsheet formula. The method comprises the following steps: obtaining a hierarchical formula for the spreadsheet, the hierarchical formula comprising operators and a hierarchical reference, wherein the hierarchical reference corresponds to one or more nodes in one or more levels in a hierarchical structure for the spreadsheet; determining a dependency relationship between one or more nodes based on the correspondence between the hierarchical reference and the one or more nodes; determining a computational relationship between one or more nodes based on the operators and the dependencies; and generating a spreadsheet formula based at least on the hierarchical structure and the calculated relationship for the spreadsheet.

Description

Computer-implemented method, apparatus, and storage medium for generating spreadsheet formulas

Technical Field

The present application relates to spreadsheet information processing, and more particularly, to a computer-implemented method, apparatus, and storage medium for generating spreadsheet formulas.

Background

In many industries, it is often desirable to write a Spreadsheet (Spreadsheet) containing a large number of formulas. Spreadsheets typically comprise tens of pages (sheets), thousands of rows (rows), tens of thousands of cells (cells), and there are very complex interrelationships between cells, with the contents of most cells being formulas.

Disclosure of Invention

In conventional spreadsheet processing software (e.g., microsoft Excel, google Sheet), if formulas are stored in cells, the spreadsheet defaults to show the results of the formulas and the formulas lack semantic information representing their true meaning. This makes editing formulas in a spreadsheet time consuming, laborious, and error-prone, difficult to debug, and difficult to understand or alter by others other than the spreadsheet author. There is currently no viable solution to these problems.

Accordingly, the present application addresses at least one of the above-mentioned problems by providing a computer-implemented method, apparatus, and storage medium for generating a spreadsheet formula.

According to a first aspect of the present application, there is provided a computer-implemented method for generating a spreadsheet formula, the method comprising: obtaining a hierarchical formula for the spreadsheet, the hierarchical formula comprising operators and hierarchical references, wherein the hierarchical references correspond to one or more nodes in one or more hierarchies in a hierarchical structure for a spreadsheet, wherein each hierarchy has at least one node and has a respective hierarchy type, each node corresponds to one or more cells in the spreadsheet, wherein the hierarchical structure further comprises membership between the one or more nodes; determining dependencies between the one or more nodes based on correspondence between the hierarchical references and the one or more nodes; determining a computational relationship between the one or more nodes based on the operator and the dependency relationship; and generating the spreadsheet formula based at least on the hierarchical structure for the spreadsheet and the calculated relationship.

According to a second aspect of the present application, there is provided an apparatus for generating a spreadsheet formula, comprising: means for performing the steps of the method of any of the preceding items.

According to a third aspect of the present application, there is provided an apparatus for generating a spreadsheet formula, comprising: at least one processor; and at least one storage device storing instructions that, when executed by the at least one processor, cause the at least one processor to perform the method of any of the preceding claims.

According to a fourth aspect of the present application, there is provided a non-transitory computer readable storage medium storing instructions which, when executed by a processor, cause performance of a method as in any of the preceding items.

According to a fifth aspect of the present application, there is provided an apparatus for generating a spreadsheet formula, the apparatus comprising: a hierarchical formula editor configured to obtain a hierarchical formula for the spreadsheet, the hierarchical formula comprising operators and hierarchical references, wherein the hierarchical references correspond to one or more nodes in one or more hierarchies for a spreadsheet, wherein each hierarchy has at least one node and has a respective hierarchy type, each node corresponds to one or more cells in the spreadsheet, wherein the hierarchy further comprises membership between the one or more nodes; a hierarchical formula parser configured to determine dependencies between the one or more nodes based on correspondence between the hierarchical references and the one or more nodes; the hierarchical formula parser is further configured to determine a computational relationship between the one or more nodes based on the operator and the dependency relationship; and a formula translator configured to generate the spreadsheet formula based at least on the hierarchical structure and the computational relationship for the spreadsheet.

According to some embodiments of the present application, spreadsheet formulas may be written intuitively. Thus, the meaning represented by the formula in the spreadsheet can be easily understood by reading the formula itself.

Additionally, formulas may be written automatically in electronic software according to some embodiments of the present application. Thus, intelligent hints or autocompletions can be performed when the spreadsheet formula is written.

In addition, formulas in the electronic form may be readily multiplexed according to some embodiments of the present application. The reusable formulas with semantic information can be automatically converted into formulas written in cell location information in a spreadsheet.

In addition, according to some embodiments of the present application, formula errors in the electronic form may be efficiently avoided or discovered. Therefore, even when many formulas are contained in the electronic form, errors in which some formulas exist can be easily found.

Those skilled in the art will appreciate that the above technical effects are exemplary and not limiting. Other features of the present application and its advantages will become apparent from the following detailed description of exemplary embodiments of the present application, which proceeds with reference to the accompanying drawings.

Drawings

The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments of the application and together with the description, serve to explain the principles of the application.

The present application will be more clearly understood from the following detailed description with reference to the accompanying drawings, in which:

FIG. 1 is a flow chart that schematically illustrates a computer-implemented method for generating a spreadsheet formula, in accordance with some embodiments of the present application.

Fig. 2 is a schematic diagram schematically illustrating a hierarchical structure for a spreadsheet according to some embodiments of the present application.

Fig. 3 is a schematic diagram schematically illustrating a hierarchical structure for a spreadsheet according to some embodiments of the present application.

FIG. 4 is a schematic diagram schematically illustrating a hierarchical computational graph for representing a hierarchical structure of a spreadsheet according to some embodiments of the present application.

Fig. 5 is a schematic diagram schematically illustrating nodes in a hierarchical computational graph according to some embodiments of the present application.

FIG. 6 is a flow chart that schematically illustrates a process for parsing text code to generate a hierarchical formula, in accordance with some embodiments of the present application.

FIG. 7 is a schematic diagram schematically illustrating a directed graph for hierarchical composite operators according to some embodiments of the present application.

FIG. 8 is a schematic diagram schematically illustrating a directed graph of composite operators for cells according to some embodiments of the present application.

FIG. 9 is a schematic diagram that schematically illustrates a process for specifying dependency cells for operators, in accordance with some embodiments of the present application.

FIG. 10 is a schematic diagram schematically illustrating a process for converting a composite operator for a hierarchy into a composite operator for a cell, according to some embodiments of the present application.

FIG. 11 is a schematic diagram schematically illustrating a directed graph of a transformed composite operator for a cell in accordance with some embodiments of the present application.

FIG. 12 is a schematic diagram schematically illustrating directed graphs of custom atomic operators according to some embodiments of the present application.

FIG. 13 is a block diagram schematically illustrating an apparatus for generating a spreadsheet formula according to some embodiments of the present application.

FIG. 14 is a schematic block diagram illustrating an information processing device that may be used to implement the generation of spreadsheet formulas according to some embodiments of the present application.

Detailed Description

Various exemplary embodiments of the present application will now be described in detail with reference to the accompanying drawings. It should be noted that: the relative arrangement of the modules and steps, numerical expressions and numerical values set forth in these embodiments do not limit the scope of the present application unless specifically stated otherwise.

Meanwhile, it should be understood that the sizes of the respective parts shown in the drawings are not drawn in actual scale for convenience of description.

The following description of at least one exemplary embodiment is merely exemplary in nature and is in no way intended to limit the application, its application, or uses.

Techniques, methods, and apparatus known to one of ordinary skill in the relevant art may not be discussed in detail, but are intended to be part of the specification where appropriate.

In all examples shown and discussed herein, any specific values should be construed as merely illustrative, and not a limitation. Thus, other examples of the exemplary embodiments may have different values.

It should be noted that like reference numerals and letters refer to like items in the following figures, and thus once an item is defined in one figure, no further discussion thereof is necessary in subsequent figures.

FIG. 1 is a flow chart schematically illustrating a computer-implemented method 100 for generating a spreadsheet formula according to some embodiments of the present application.

As shown in fig. 1, in step S101, a hierarchical formula for a spreadsheet may be obtained, the hierarchical formula including operators and hierarchical references, wherein the hierarchical references correspond to one or more nodes in one or more levels in a hierarchical structure for the spreadsheet.

According to some embodiments of the present application, each hierarchy may have at least one node and have a respective hierarchy type, and each node may correspond to one or more cells in the spreadsheet. In addition, for example, the hierarchy may also include membership between one or more nodes.

The hierarchical structure for the electronic form is described below with reference to fig. 2 and 3. Fig. 2 is a schematic diagram schematically illustrating a hierarchical structure for a spreadsheet according to some embodiments of the present application. Fig. 3 is a schematic diagram schematically illustrating a hierarchical structure for a spreadsheet according to some embodiments of the present application.

According to some embodiments of the present application, the hierarchy for the spreadsheet may include, for example, the following hierarchy that is the result of dividing the spreadsheet: a workbook (book), a page (sheet), a section (section), a table (table), a block (row), a column (column), an interval (interval), a cell (cell), and the like. Note that the above-described partitioning of the hierarchy for spreadsheets is merely exemplary, and other partitioning may also be employed.

The workbook may be a spreadsheet file (e.g., a file in the. Xls or. Xlsx format) and the workbook may include a plurality of pages. A page may include sections or tables, and each section may also include subsections and tables. The table may include rows, columns, or blocks. A block may be a collection of closely related rows or columns, each block may also include sub-blocks.

Pages may be divided into different sections by different levels of headings. FIG. 2 shows an example of a hierarchical structure of pages in a spreadsheet. As shown in fig. 2, the first level header 1 and the first level header 2 may divide pages in the electronic form into section 1 and section 2. Section 1 may further include a second level title 1.1 and a second level title 1.2, and the second level title 1.1 and the second level title 1.2 may divide section 1 into section 1.1 and section 1.2 (section 1.2 is empty in this example). Section 1.1 may include two tables, table 1.1.A and table 1.1.B, respectively. Similarly, section 2 may further include a second level title 2.1 and a second level title 2.2, and the second level title 2.1 and the second level title 2.2 may divide section 2 into section 2.1 and section 2.2. In addition, section 2 may also include table 2.A. Section 2.1 may include a third level title 2.1.1 and a third level title 2.1.2. Third level title 2.1.2 may include section 2.1.2. Section 2.1.2 may include table 2.1.2.A. In addition, section 2.2 may include table 2.2.A and table 2.2.B. Note that both the title and the table may be added with names.

A table may include rows, columns, or blocks, and a block may include multiple rows or columns. Fig. 3 shows an example of a hierarchical structure of tables in pages in a spreadsheet. As shown in fig. 3, the table may comprise, for example, 11 rows/columns, wherein there may be 4 first-stage rows/columns, first-stage row/column 1, first-stage row/column 2, first-stage row/column 4, and first-stage row/column 6, respectively. In addition, the second-stage row/column 3.1, the second-stage row/column 3.2, and the second-stage row/column 3.3 are second-stage rows/columns, which may constitute the first-stage block 3. In addition, third level rows/columns 5.2.1 and third level rows/columns 5.2.2 are third level rows/columns, which may constitute second level blocks 5.2. In addition, the second-stage row/column 5.1, the second-stage block 5.2, and the second-stage row/column 5.3 may constitute the first-stage block 5.

Cells at the intersection of a row (or a block of rows comprising a plurality of rows) and some columns (or a block of columns comprising a plurality of columns) may constitute an interval. For example, a row block, a column block may all comprise intervals.

According to some embodiments of the present application, the hierarchy of the spreadsheet may be represented using a hierarchical computational graph. The hierarchical computational graph may contain all of the information in the spreadsheet so that a one-to-one relationship may be formed with the spreadsheet.

A hierarchical computational graph for representing the hierarchical structure of a spreadsheet is described below with reference to fig. 4. Fig. 4 is a schematic diagram schematically illustrating a hierarchical computation graph 301 for representing a hierarchical structure of a spreadsheet according to some embodiments of the present application.

As shown in fig. 4, each node 302-311 in the hierarchical computation graph 301 may represent a respective level in a hierarchical structure for a spreadsheet. Each node 302-311 may have a corresponding type, which may be a respective level in a hierarchy for a spreadsheet, such as a workbook, page, section, table, block, row, column, section, cell, etc. In fig. 4, different types of nodes may be represented by different shapes. For example, nodes 307 through 311 are diamond shaped, which means that the type of node is a cell.

In addition, as shown in fig. 4, edges between respective nodes in the hierarchical computation graph 301 can be classified into two types, for example. For example, one type of edge in the hierarchical computation graph 301 may be a membership edge that represents membership between one or more nodes. Solid arrows may be used in fig. 4 to represent membership edges. For example, a membership edge 316 that points from node 302 to node 303 may represent that node 303 is a child of node 302, i.e., node 303 is affiliated with node 302. In addition, for example, another type of edge in the hierarchical computation graph 301 may be a computation relationship edge that represents a computation relationship between one or more nodes. Dashed arrows may be used in the figure to represent the calculated relationship edges. For example, a calculated relationship edge 314 pointing from node 303 to node 305 may represent that node 305 was calculated by node 303.

The nodes in the hierarchical computation graph are described below with reference to fig. 5. Fig. 5 is a schematic diagram schematically illustrating nodes 305 in a hierarchical computational graph according to some embodiments of the present application.

As shown in fig. 5, the nodes 305 in the hierarchical computation graph may include: the type 402 of the hierarchy to which the node corresponds, the child node 403 to which the node is connected by a membership edge (e.g., for node 305, there is a pointer to node 309 stored), the operator 404 of the node, the dependent node 405 on which the node depends (e.g., for node 305, there is a pointer to node 303 stored), the semantic information 406 of the node (e.g., name, label, etc. of the node), other information (optional) 407 about the node (e.g., modifier, attribute label, etc. of the node).

Those skilled in the art will appreciate that other nodes in the hierarchical computational graph may have a similar configuration to node 305 and will not be described in detail herein.

According to some embodiments of the present application, the step S101 of obtaining the hierarchical formula for the electronic table may include: providing a graphical user interface editor, wherein the graphical user interface editor comprises components for specifying nodes and/or operators of different levels; receiving input for components specifying nodes and/or operators of different levels; and generating a hierarchical formula based on the input.

For example, a user may specify nodes and/or operators of different levels by clicking and/or dragging visualization components provided in a graphical user interface editor, so that a hierarchical formula may be more intuitively constructed. The graphical user interface editor can provide the visual component, so that a user can construct a hierarchical formula more intuitively, and the convenience of the user is improved.

According to some embodiments of the present application, the step S101 of obtaining the hierarchical formula for the electronic table may also include: receiving a text code input by a user; and parsing the text code to generate a hierarchical formula.

For example, a user may input a hierarchical formula in plain text format through a text editor, and may generate the hierarchical formula by parsing the hierarchical formula in plain text format, so that the hierarchical formula may be generated more efficiently.

According to some embodiments of the present application, the operators may include atomic operators, which may include one or more of the following: mathematical operators, mathematical functions, constants, system defined functions, and user-defined atomic operators.

For example, the mathematical operator may be a "+", "-", "x", "/", etc., mathematical operator. In addition, for example, the mathematical function may be a mathematical function such as a summation function "SUM ()", an averaging function "AVERAGE ()", or the like. Further, the constant may be, for example, a constant of "10", "1 year", or the like. In addition, for example, the system definition function may be a system definition function such as a data SOURCE function "SOURCE ()", a lag (1 year) "which indicates an operation to lag data by 1 year, or the like, wherein the data SOURCE function" SOURCE () "indicates data input by a user. User-defined atomic operators are defined by a user by combining other types of atomic operators. For example, a user may customize a new custom atomic operator "growth rate ()" by entering "growth rate ({ x1}, { x2 }) = { x2}/{ x1} -1".

According to some embodiments of the present application, a hierarchical reference may correspond to one or more nodes in one or more levels in a hierarchy for a spreadsheet. For example, according to some embodiments of the present application, hierarchical references may be referred to using strings.

By way of example, some examples of hierarchical references are given below:

(a)“ROW！Revenue>>BOOK！DCF Model/SHEET！Financial Statement/TABLE！Income Statement/BLOCK！US”：

the hierarchical reference (a) represents a ROW (ROW) named "Revenue" within a BLOCK (BLOCK) named "US" under a TABLE (TABLE) named "Income status" under a page (green) named "Financial Statement (financial Statement)" under a workbook (BOOK) named "DCF Model".

(b)“Revenue>>DCF Model/Financial Statement/Income Statement/US”：

The hierarchical reference (b) represents the same meaning as the hierarchical reference (a) described above, except that the user may be allowed to omit hierarchical types such as ROW (ROW), workbook (BOOK), page (page), TABLE (TABLE), BLOCK (BLOCK), etc., without causing ambiguity.

(c)“Revenue>>./Income Statement/US”：

The hierarchical reference (c) represents the same meaning as the hierarchical reference (b) described above. Note that the character string following "> >" is referred to as a Path (Path), which represents a hierarchy passed in finding the referred hierarchy from top to bottom in the hierarchy representing the electronic form. The path may be an absolute path, such as "DCF Model/Financial Statement/Income State/US" in hierarchical reference (b) above; the path may also be a relative path, "/" denotes the current hierarchy, "/" denotes the hierarchy to which the current hierarchy belongs, e.g., when the user has been under a page (SHEET) named "Financial Statement (financial Statement)" under a workbook (BOOK) named "DCF Model", the relative path may be denoted as "/Income State/US".

(d)“Revenue”：

The path may also be omitted when ambiguity is not raised. For example, if there is only one row under the current table named "Revenue," then "Revenue" itself may refer to that row.

(e)“[[Europe]]Revenue”：

A label (label) may be added to further distinguish between the different rows, e.g. "[ [ Europe ] ] Revenue" represents a row named "Revenue", which has the label "Europe", to distinguish from other rows also named "Revenue", e.g. to distinguish from the row "[ [ US ] ] Revenue", which has the label "US".

(f)“ROW！Revenue:ROW！Profit”：

The hierarchical reference (f) represents all the ROWs from the ROW (ROW) of "Revenue" to the ROW (ROW) of "Profit".

(g)“BLOCK！US>>BOOK！DCF Model/SHEET！Financial Statement/TABLE！Income Statement”：

The hierarchical reference (g) represents a BLOCK (BLOCK) named "US", which is under a TABLE (TABLE) named "Income status (profit TABLE)" under a page (green) named "Financial Statement (financial Statement)" under a workbook (BOOK) named "DCF Model".

(h)“COLUMN！2018>>BOOK！DCF Model/SHEET！Financial Statement/TABLE！Income Statement”：

The hierarchical reference (h) represents a COLUMN (COLUMN) named "2018" under a TABLE (TABLE) named "Income status (profit TABLE)" under a page (she) named "Financial Statement (financial Statement)" under a workbook (BOOK) named "DCF Model".

(i)“COLUMN！2018：COLUMN！2020”：

The hierarchical reference (i) represents all COLUMNs between the COLUMN named "2018" in the current table (COLUMN) to the COLUMN named "2020" (COLUMN).

(j)“Revenue[2018]”：

The hierarchical reference (j) represents a cell with a column name of "2018" in the row named "Revenue".

(k)“Revenue[2018:2020]”：

The hierarchical reference represents the section of the row named "Revenue" with columns named "2018" to "2020".

(l)“Revenue[hist]”：

The section of all columns with a "hist" tag corresponding to cells in the row named "Revenue" is represented.

(m) "COLUMN-! 2018[ stock ] ".

The hierarchical reference represents an interval of cells corresponding to all rows with the label "stock" in the column named "2018".

Note that the above implementations of the hierarchical references are merely exemplary, and that the hierarchical references may also be implemented in other ways. For example, the hierarchy for the spreadsheet may be represented as elements in a visual view in a graphical user interface such that the hierarchical reference may be implemented directly through the elements in the visual view in the graphical user interface.

According to some embodiments of the present application, a hierarchical formula may be viewed as a list of parameters consisting of a series of atomic operators (or a composite operator consisting of a series of atomic operators) acting on top of a list of parameters consisting of hierarchical references, and assigning the result of the action to the left value of the hierarchical formula. As an example, some examples of hierarchical formulas are shown below. Note that in some implementations of the present application, the content within "{ }" may be utilized to represent hierarchical references.

{ profit = { income } { cost }

{ rate of increase in revenue = { revenue }/{ revenue }. Lag (1 year) -1

{ average revenue growth rate = { revenue growth rate }. Average ()

{ revenue } [ forecast period ] = { revenue: ../profit margin lag (1) ×1+ { average rate of revenue increase }

{ year round } [ stock ] = { Q1} + { Q2} + { Q3 + { Q4}

Total revenue } = SUM ({ us revenue: chinese revenue })

{ BLOCK-! Profit = { BLOCK-! Revenue } { BLOCK-! Cost }

As described above, the user-entered hierarchical formula includes operators and hierarchical references therein. According to different input methods of the hierarchical formula, different complexities of operators and hierarchical references included in the hierarchical formula are identified. If the user builds a hierarchical formula by clicking and/or dragging a visual component in the graphical user interface, only the hierarchical reference or operator of the spreadsheet corresponding to the graphical user interface component need be identified because the hierarchical reference or operator of the spreadsheet corresponding to each component is deterministic. If the user enters the hierarchical formula (i.e., enters a string) in the form of a text code, the text code needs to be parsed to identify operators and hierarchical references in the string.

A process for parsing text codes to generate hierarchical formulas is described below with reference to fig. 6. Fig. 6 is a flow diagram schematically illustrating a process 600 for parsing text code to generate a hierarchical formula according to some embodiments of the present application.

As shown in fig. 6, in step S601, text codes may be segmented to obtain a segmented word stream.

For example, for the following hierarchical formula:

{ revenue } [ forecast period ] = { revenue: ../profit sheet }. Lag (1) × (1 + { average revenue growth rate }) the result of word segmentation is a word stream comprising a list of word segments:

"{", "income", "}", "[", "forecast period", "]", "=", "{", "income", and ": the term ","/"," profit sheet "," } ",".tag "," ("," 1",") "," x "," ("," 1"," + "," { "," average rate of increase in revenue "," } ",") ".

Note that the above word segmentation is merely exemplary, and other word segmentation approaches are possible.

Referring back to fig. 6, after step S601, the process 600 for parsing the text code to generate a hierarchical formula may proceed to step S603 and step S605.

In step S603, the tokens in the token stream that are related to the operator may be identified to determine the operator.

For example, in the above example, the word segmentation related to the operator in the word segmentation stream includes: "=", tag "," (", 1", ")", "," (", 1", "+", "".

From the identified operator-related word segment, an operator may be determined. According to some embodiments of the present application, an atomic operator may be determined from the identified operator-related word segment.

For example, in the above example, from the partial words "=", tag "," ("," 1",") "," ("," "1", "+", ")", related to the operator, it can be determined that the atomic operator includes: ". lag (1)", "+".

In step S605, the tokens in the token stream that are associated with one or more nodes in one or more levels in the hierarchy of the spreadsheet may be identified to determine a level reference.

For example, in the above example, the above-described word segmentation in the word stream associated with one or more nodes in one or more levels in the hierarchy of the spreadsheet comprises: "{", "revenue", "[", "forecast period", "", "{", "revenue", and ": ","/"," "profit margin", "}", "{", "averagerevenue growth rate", "}".

From the identified tokens associated with one or more nodes in one or more levels in the hierarchy of the spreadsheet, a level reference may be determined. Each hierarchical reference may be referred to by a succession of one or more segmentations.

For example, in the above example, "{", "revenue", "}", "[", "forecast period", "]" refers to an interval of cells in a row named "revenue" where a column label includes "forecast period". For another example, "{", "revenue", ": ","/"," profit sheet "," } "refers to the row named" revenue "below the table named" profit sheet ". For another example, "{", "average revenue growth rate", "" refers to a cell named "average revenue growth rate".

Note that the matching process from word segmentation flow to hierarchical references may support fuzzy matching according to some implementations of the present application. For example, if the "revenue" is written as "Sales", it can also be matched to the same hierarchical references. In addition, in other implementations, hierarchical references may be supported that follow a particular syntax, but supported syntax referring to hierarchical references may be arbitrary.

Referring back to fig. 6, after step S603 and step S605, the process 600 for parsing the text code to generate a hierarchical formula may proceed to step S607. In step S607, a hierarchical formula may be generated based on the determined operator and the hierarchical reference.

In the event that an operator and a hierarchical reference are determined, a hierarchical formula may be generated based on the determined operator and hierarchical reference. According to some embodiments of the present application, a hierarchical formula may be generated from the determined atomic operators and hierarchical references.

For example, in the above example, according to the determined atomic operators ". Lag (1)", "x", "+" and the determined hierarchical references "{ revenue } [ prediction period ]," { revenue: ../profit sheet "," { average revenue growth rate } ", a hierarchical formula can be identified and generated: { revenue } [ forecast period ] = { revenue: ../profit margin lag (1) × (1+ { average revenue growth rate }).

According to some embodiments of the present application, a composite operator may be constructed from the atomic operators in the hierarchical formula, where the composite operator may include a combination of all the atomic operators in the hierarchical formula.

For example, in the case of an identified atomic operator, a composite operator may be constructed from the identified atomic operator and its position (e.g., relative position) in the word stream, which may include a combination of all the atomic operators in the hierarchical formula.

For example, in the above example, one expression of the compound operator constructed from the identified atomic operators ". Lag (1)", "x", "+", is the following string:

{y}＝{x1}.lag(1)*(1+{x2})

Where { y }, { x1}, { x2} are placeholders, representing that parameters are required to be filled in here.

In the case where the composite operator is constructed from the atomic operators, the above step S607 may include generating a hierarchical formula from the determined composite operator and the hierarchical reference.

For example, in the above example, according to the constructed composite operator "{ y } = { x1}," lag (1) × (1+ { x2 }) ", and the determined hierarchy references" { revenue } [ prediction period ], "{ revenue: ../profit sheet "," { average revenue growth rate } ", a hierarchical formula can be identified and generated: { revenue } [ forecast period ] = { revenue: ../profit margin lag (1) × (1+ { average revenue growth rate }).

According to some embodiments of the present application, the composite operator may be stored with one or more of the following: a character string; a computer-implemented function pointed to by a function pointer; and directed graphs.

For example, in the above example, the composite operator may be stored in the form of the string "{ y } = { x1}. Lag (1) ×1+ { x2 }".

For example, in the example described above, the composite operator may also be stored in the form of a computer-implemented function pointed to by a function pointer, where this function receives two parameters { x1} and { x2 }.

For example, in the above example, the composite operator may also be stored in the form of a directed graph.

According to some embodiments of the present application, each node in the directed graph may represent an operator in the hierarchical formula, and directed edges between each node in the directed graph may represent a combined relationship between operators in the hierarchical formula.

For example, each node in the directed graph may represent an atomic operator, a placeholder for a parameter, or a constant operator, and directed edges between various nodes in the directed graph may represent a combined relationship, such as an operational relationship, between various nodes.

A directed graph of compound operators for a hierarchy is described below with reference to fig. 7. FIG. 7 is a schematic diagram schematically illustrating a directed graph for hierarchical composite operators according to some embodiments of the present application.

Fig. 7 shows a directed graph for storing the composite operator "{ y } = { x1}, lag (1) × (1+ { x2 }" for the hierarchy in the above example. As shown in FIG. 7, nodes 802 through 804 in the directed graph represent atomic operators, nodes 805 and 807 in the directed graph represent placeholders for parameters, and node 806 in the directed graph represents constant operators.

In addition, as shown in fig. 7, the uppermost atomic operator is an atomic operator "×" in the node 802 in the directed graph, which takes the calculation results of two atomic operators, that is, an atomic operator ". Lag (1)" in the node 803 and an atomic operator "+" in the node 804, as input variables, an atomic operator ". Lag (1)" in the node 803, in turn, a parameter "x1" in the node 805, as input variables, and "+" in the node 804, a constant operator "1" in the node 806, and a parameter "x2" in the node 807, as input variables.

Referring back to fig. 1, after step S101, the computer-implemented method 100 for generating a spreadsheet formula proceeds to step S103. In step S103, a dependency relationship between one or more nodes may be determined based on the correspondence relationship between the hierarchical reference and the one or more nodes.

According to some embodiments of the present application, the hierarchical references may include a first hierarchical reference to the left of the equal sign in the hierarchical formula and a second hierarchical reference to the right of the equal sign in the hierarchical formula, wherein the first hierarchical reference (also referred to as the left value) corresponds to a first node of the one or more nodes and the second hierarchical reference corresponds to a second node of the one or more nodes (the second hierarchical reference may be an empty set).

As described above, a hierarchical formula can be viewed as a list of parameters consisting of a series of atomic operators (or a composite operator consisting of a series of atomic operators) acting on top of a list of parameters consisting of hierarchical references, and assigning the result of the action to the left value of the hierarchical formula.

In the case of representing a hierarchical formula in a hierarchical computational graph, each hierarchical reference in the hierarchical formula may uniquely correspond to a certain level in the hierarchy of the spreadsheet and a certain node in the hierarchical computational graph. The hierarchical reference to the left of the equal sign in the hierarchical formula may be referred to as a first hierarchical reference, and the node in the hierarchical computation graph to which the first hierarchical reference corresponds may be referred to as the first node (or left value node) of this hierarchical formula. The hierarchical reference to the right of the equal sign in the hierarchical formula may be referred to as a second hierarchical reference, and the node in the hierarchical computation graph to which the second hierarchical reference corresponds may be referred to as the second node (or dependency node) of this hierarchical formula.

Each hierarchical formula may uniquely correspond to a compound operator, a first node (or left value node), and a set of second nodes (or dependent nodes). Alternatively, each composite operator and a first node (or left value node) and a set of second nodes (or dependent nodes) that match it may also uniquely specify a hierarchical formula. The hierarchical formula may be of the meaning of passing the second node (or dependent node) to the parameter placeholder in the composite operator and assigning the resulting result to the first node (or left value node).

For example, for the hierarchy formula "{ revenue } [ prediction period ] = { revenue: ../profit sheet }. Lag (1) × (1+ { average revenue growth rate }) ", the hierarchical reference to the left of the equal sign in the hierarchical formula" { revenue } [ prediction period ] "may be referred to as the first hierarchical reference, while the hierarchical reference to the right of the equal sign in the hierarchical formula" { revenue: ../profit sheet "and" { average revenue growth rate } "may be referred to as a second tier reference.

For example, the first node (or left value node) to which the first hierarchy reference "{ revenue } [ prediction period ]" corresponds may be a node in a hierarchy computation graph to which an interval consisting of cells in a row of which column labels in a column named "revenue" in the spreadsheet include "prediction period". For example, the second hierarchy references "{ revenue: ../profit sheet } "the second node (or dependent node) corresponding to" profit sheet "may be a node in the hierarchical computational graph corresponding to a row of" revenue "in a table of" profit sheet "and the second hierarchical reference" { average revenue growth rate } "the second node (or dependent node) corresponding to" average revenue growth rate "may be a node in the hierarchical computational graph corresponding to a cell of" average revenue growth rate "in the spreadsheet.

According to some embodiments of the present application, the step S103 of determining the dependency relationship between the one or more nodes based on the correspondence relationship between the hierarchical reference and the one or more nodes may include: a dependency relationship between the first node and the second node is determined based at least on a correspondence between the first hierarchical reference and the first node and a correspondence between the second hierarchical reference and the second node.

For example, in the above example, the correspondence between the nodes in the hierarchical computation graph corresponding to the interval consisting of the cells in the row of which the column label in the column of the name "revenue" includes "prediction period" and "{ revenue" may be based at least on the first hierarchical reference "{ revenue } [ prediction period ]", the second hierarchical reference "{ revenue: ../profit sheet } "correspondence between nodes in the hierarchical computation graph corresponding to the row named" revenue "in the table named" profit sheet "in the electronic table and correspondence between the second hierarchical reference" { average revenue growth rate } "and nodes in the hierarchical computation graph corresponding to the cells named" average revenue growth rate "in the electronic table, and determining dependency relationships between nodes in the hierarchical computation graph corresponding to the section consisting of the cells in the row named" revenue "and the column label including the" forecast period "in the electronic table and nodes in the hierarchical computation graph corresponding to the row named" revenue "in the table named" profit sheet "in the electronic table, and nodes in the hierarchical computation graph corresponding to the cells named" average revenue growth rate "in the electronic table.

Referring back to fig. 1, after step S103, the computer-implemented method 100 for generating a spreadsheet formula proceeds to step S105. In step S105, a computational relationship between one or more nodes may be determined based on the operators and the dependencies.

For example, in the above example, the node corresponding to the atomic operator ". Lag (1)", "x", "+" and hierarchy reference "{ income } [ prediction period ]" and hierarchy reference "{ income: ../profit sheet } "and" { average revenue growth rate } "to determine the calculated relationship between the above-mentioned nodes.

According to some embodiments of the present application, the step S105 of determining the computing relationship between the one or more nodes based on the operator and the dependency relationship may further include: a computational relationship between one or more nodes is determined based at least on the composite operator and the dependency relationship.

For example, in the above example, the node and hierarchy reference corresponding to the "prediction period" of the "income" and the "income" may be based on the composite operator "{ y } = { x1}, (1+ { x2 })", which is composed of the atomic operator ", lag (1)", "+", and { income: ../profit sheet } "and" { average revenue growth rate } "to determine the calculated relationship between the above-mentioned nodes.

According to some embodiments of the present application, for each of one or more nodes, a computing relationship between that node and other nodes may be stored via a hierarchical computing graph.

For example, referring to fig. 4, in the case where the computational relationship between each of the one or more nodes and the other node is determined, the computational relationship between the node and the other node may be stored in the hierarchical computational graph shown in fig. 4. For example, with respect to node 305 shown in FIG. 4, where it is determined that node 305 is calculated by node 303, a calculated relationship edge 314 pointing from node 303 to node 305 may be utilized to store the dependency relationship between node 305 and node 303, and store the composite operator of the calculated relationship in node 305, as well as the correspondence between the dependent node of node 305 and the placeholder in the composite operator.

According to some embodiments of the present application, the step S105 of determining a computation relationship between one or more nodes based on operators and dependencies may include: a computational relationship between the first node and the second node is determined based on the operator and the dependency relationship between the first node and the second node.

For example, in the above example, the first node and the second hierarchy reference "{ revenue" corresponding to the atomic operator ". Lag (1)", "+", or the composite operator made up of the above atomic operators "{ y } = { x1}, (1+ { x2 })," and the first hierarchy reference "{ revenue } [ prediction period ]": ../profit sheet } "and" { average revenue growth rate } "respectively, to determine a calculated relationship between the first node and the second node.

According to some embodiments of the present application, a computing relationship between a first node and a second node may be stored via a hierarchical computational graph for the first node.

For example, referring to fig. 4, in the case where the calculation relationship between the first node and the second node is determined, the calculation relationship between the first node and the second node may be stored in the hierarchical calculation diagram shown in fig. 4. For example, with respect to the first node 305 shown in fig. 4, in the case where it is determined that the first node 305 is calculated by the second node 303, a dependency relationship between the first node 305 and the second node 303 may be stored with a calculation relationship edge 314 directed from the second node 303 to the first node 305, and a composite operator of the calculation relationship (404) and a correspondence relationship between the dependency node of the node 305 and a placeholder in the composite operator may be stored in the node 305.

Referring back to fig. 1, after step S105, the computer-implemented method 100 for generating a spreadsheet formula proceeds to step S107. In step S107, a spreadsheet formula may be generated based at least on the hierarchical structure and the calculated relationship for the spreadsheet.

Each hierarchical formula may correspond to one or more spreadsheet formulas. For example, for the hierarchy formula "{ revenue } [ prediction period ] = { revenue: ../profit sheet }. Lag (1) × (1+ { average revenue growth rate }), "refer to the following" table: the aggregate table ", assuming that the row" revenue "of the first node (or left value node) in the hierarchy formula is at row 10 in the aggregate table in the spreadsheet, and the columns in the aggregate table at year 2019," 2020, "2021" are "F", "G", "H", respectively. If the three columns of "2019," 2020, "2021" are labeled "forecast periods," then the spreadsheet formulas need to be generated in the cells (i.e., cells F10, G10, and H10) at the intersections of the row "revenue" (i.e., row 10) and "2019" (i.e., column F), the year "2020" (i.e., column G), and the year "2021" (i.e., column H) in the aggregate table in the target spreadsheet.

Table: totaling meter

In addition, for example, refer to the following "table: profit sheet ", assuming" revenue "for the second node (dependent node) in the hierarchy formula: ../profit sheet "this row is the 30 th row in the profit sheet in the spreadsheet.

Table: profit meter

Further, for example, refer to the following "table: aggregate table ", assume that the cell corresponding to the value" 10% "of the" average revenue growth rate "of another second node (dependent node) in the hierarchical formula is in the 12 th row, column C, in the aggregate table in the electronic form.

Table: totaling meter

Thus, the following three spreadsheet formulas may be generated for cells F10, G10, and H10 in the total table in the spreadsheet, respectively, according to the hierarchical structure of the spreadsheet (such as the total table, profit table, etc., as described above), and the atomic operators ". Lag (1)", "+,", or the composite operator made up of the atomic operators described above "{ y } = { x1}. Lag (1) ({ 1+ { x 2)").

For cell F10 in the aggregate table, generate the spreadsheet formula "=e$ 30 (1+$c $ 12)"; for cell G10 in the aggregate table, generate the spreadsheet formula "=f$ 30 (1+$c $ 12)"; for cell H10 in the aggregate table, a spreadsheet formula "=g $30 (1+$c $ 12)" is generated.

According to some embodiments of the present application, the step S107 of generating the spreadsheet formula based at least on the hierarchical structure and the calculated relationship for the spreadsheet may include: for each current node of the one or more nodes, determining a calculated relationship between each of one or more first cells corresponding to the current node and each of one or more second cells corresponding to other nodes based on the calculated relationship between the current node and other nodes; and generating a spreadsheet formula for each of the one or more first cells based at least on a calculated relationship between each of the one or more first cells and each of the one or more second cells.

According to some embodiments of the present application, each node in the hierarchical computation graph corresponding to the spreadsheet may be traversed according to various graph traversal algorithms. Note that various graph traversal algorithms known in the art may be employed in traversing the hierarchical computation graph, which is not limited in this application and will not be described in detail.

When traversing to each current node in one or more nodes in the hierarchical computational graph, the computational relationship between the current node and other nodes may be converted into a computational relationship between each first cell corresponding to the current node and each second cell corresponding to other nodes.

For example, in the above example, when traversing to the current node "{ revenue } [ prediction period ]" in the hierarchical computational graph, the current node "{ revenue } [ prediction period ]" and other nodes "{ revenue: the calculation relation between../profit sheet "and" { average income growth rate } "is converted into the first cell F10, G10 and H10 in the total sheet corresponding to the current node" { income } [ prediction period ] ", and other nodes" { income: ../profit sheet } "the second cell E30, F30, G30 in the profit sheet corresponding to and the other nodes" { average revenue growth rate } "the second cell C12 in the aggregate sheet corresponding to the values of" the second cell E30, F30, G30 in the profit sheet.

In the case where the calculated relationship between each first cell and each second cell has been obtained, a spreadsheet formula for each first cell may be generated based at least on the calculated relationship between each first cell and each second cell.

For example, in the above example, for cell F10 in the aggregate table, a spreadsheet formula "=e $30 (1+$c $ 12)" may be generated; for cell G10 in the aggregate table, a spreadsheet formula "=f $30 (1+$c $ 12)" may be generated; for cell H10 in the aggregate table, a spreadsheet formula "=g $30 x (1+$c $ 12)" may be generated.

According to some embodiments of the present application, the step of determining the calculated relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to the other node may include: determining an operator corresponding to each of one or more first cells corresponding to the current node based at least on an operator corresponding to the current node of the one or more nodes; and determining a dependency relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to the other node based at least on the dependency relationship between the current node and the other node.

A directed graph of the composite operator for a cell is described below with reference to fig. 8. FIG. 8 is a schematic diagram schematically illustrating a directed graph of composite operators for cells according to some embodiments of the present application.

According to some embodiments of the present application, the operator corresponding to the current node of the one or more nodes is a first level compound operator corresponding to one of the one or more nodes in the hierarchy for the spreadsheet, the first level compound operator comprising one or more first level atomic operators; the operator corresponding to each of the one or more first cells is a second level compound operator corresponding to one of the one or more cells in the spreadsheet, the second level compound operator comprising one or more second level atomic operators; and determining a second-level composite operator corresponding to each of the one or more first cells corresponding to the current node based at least on the first-level composite operator corresponding to the current node of the one or more nodes.

The composite operator for a cell (which is also referred to herein as a second level composite operator) and the composite operator for a level in a hierarchical computational graph (which is also referred to herein as a first level composite operator) are very similar in form, are both a directed graph, but are of different significance. Since each cell in the spreadsheet can only store one number or other variable, each parameter of the composite operator for the cell can only be a scalar (scaler), whose calculation is also a scalar. In contrast, the parameter for the composite operator of a hierarchy is a hierarchy, which is typically a set of multiple variables, whose computation results are also a set of multiple computation results.

For example, in the above example, since the left value node contains three cells F10, G10, H10, one second-level composite operator needs to be generated for each of the three cells F10, G10, H10. In this example, the generated composite operators for the three cells F10, G10, H10 are all the same (but the dependent cells passed to the placeholders are different), as shown in FIG. 8.

In fig. 8, the hierarchical atomic operators of fig. 7, "" 802 "are converted to atomic operators of cells" "1002; the atomic operator of the hierarchy ". Lag (1)" 803 is converted into the atomic operator "id (equivalent)" 1003 of the cell; the atomic operators of the hierarchy "+"804 are converted into atomic operators of the cells "+"1004; the hierarchical constant atom operator "1"806 is converted to the constant atom operator "1"1006 of the cell; two parameter placeholders "x1"805 and "x2"807 of the hierarchy are converted into two parameter placeholders "x1"1005 and "x2"1007 of the type cell references.

The atomic operators "+", "1" in fig. 7 and 8 are objects of different classes, although the names are the same. Likewise, the parameter placeholders "x1" and "x2" are also objects of different classes in fig. 7 and 8, the former being nodes in the hierarchical computational graph, the latter for storing references to cells in the spreadsheet.

According to some embodiments of the present application, the dependency relationship between the current node and other nodes in one or more nodes may be converted into the dependency relationship between each first cell corresponding to the current node and each second cell corresponding to other nodes.

The process for specifying a dependency cell for an operator is described below with reference to FIG. 9. FIG. 9 is a schematic diagram that schematically illustrates a process for specifying dependency cells for operators, in accordance with some embodiments of the present application.

As shown in fig. 9, in the case where composite operators have been built for the cells F10, G10, H10, respectively, in the aggregate table, it can be determined which cells the respective parameter placeholders in these composite operators depend on, respectively. The node corresponding to the level "income [ prediction period ]" in the level computation graph stores the dependency node as "{ income: ../profit sheet "and" { average revenue growth rate }, the former is a row and the latter is a cell. Due to the { income: ../profit sheet } "do". Lag (1) "the position of the dependent cell needs to be shifted 1 bit to the left. Therefore, the dependent node "{ revenue: ../profit sheet } "into dependent cell E$30 for cell F10, dependent cell F$30 for cell G10, dependent cell G$30 for cell H10; the dependency node "{ average revenue growth rate }" may be converted to the dependent cell $c $12 for cell F10, the dependent cell $c $12 for cell G10, the dependent cell $c $12 for cell H10. Thus, the three cells F10, G10, H10 are dependent cells [ E$30, $C$12], [ F$30, $C$12], [ G$30, $C$12] respectively.

According to some embodiments of the present application, the step of generating the spreadsheet formula for each of the one or more first cells may include: a spreadsheet formula for each of the one or more first cells is generated based at least on an operator corresponding to each of the one or more first cells corresponding to the current node and a dependency relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to other nodes.

For example, in the example described above, for cell F10, the spreadsheet formula for cell F10 may be generated as "= e$30 x (1+$c$12)", from the composite operator and dependent cells [ e$30, $c$12] shown in fig. 8. Similarly, for cell G10, the spreadsheet formula for cell G10 can be generated as "=f$ 30 (1+$c$ 12)", according to the composite operator and dependent cells [ f$30, $c$12] shown in fig. 8. Similarly, for cell H10, the spreadsheet formula for cell H10 can be generated as "=g $30 (1+$c $ 12)", according to the composite operator and dependent cells [ g$30, $c $12] shown in fig. 8.

According to some embodiments of the present application, the first-level composite operator is stored using a first-level directed graph, each directed graph node in the first-level directed graph representing each first-level atomic operator in the first-level composite operator, and a directed edge between each directed graph node in the first-level directed graph representing a combined relationship between each first-level atomic operator in the first-level composite operator; the second-level compound operators are stored by using second-level directed graphs, each directed graph node in the second-level directed graph represents each second-level atom operator in the second-level compound operators, and directed edges among each directed graph node in the second-level directed graph represent a combination relation among each second-level atom operator in the second-level compound operators; and determining a second level directed graph by traversing the first level directed graph corresponding to the first level composite operator, and determining the second level composite operator from the second level directed graph.

The process for converting a composite operator for a hierarchy into a composite operator for a cell is described below with reference to fig. 10 and 11. FIG. 10 is a schematic diagram schematically illustrating a process for converting a composite operator for a hierarchy into a composite operator for a cell, according to some embodiments of the present application. FIG. 11 is a schematic diagram schematically illustrating a directed graph of a transformed composite operator for a cell in accordance with some embodiments of the present application.

For example, consider the complex operator "{ y } = SUM (({ x1} + { x2}. Lag (1)). SUM (), { x3}, { x4}, { x5 })" in the hierarchical formula as an example, where the parameter placeholders { y }, { x1}, { x2}, and { x3} represent first level directed graph nodes of which the types are rows, and the parameter placeholders { x4}, { x5} represent first level directed graph nodes of which the types are cells.

The left side of fig. 10 shows a directed graph (referred to herein as a first-level directed graph) of the composite operator (referred to herein as a first-level composite operator) for the hierarchy in the above-described hierarchy formula "{ y } = SUM (({ x1} + { x2}. Lag (1)). SUM (), { x3}, { x4} { x5 })". FIG. 11 shows a directed graph (referred to herein as a second-level directed graph) of a composite operator (referred to herein as a second-level composite operator) for a cell labeled "2018" in row "{ y }" in FIG. 10 after conversion.

According to some embodiments of the present application, the determining the second level directed graph by traversing the first level directed graph corresponding to the first level composite operator may include: traversing the first-level directed graph according to the sequence of subsequent traversal, so as to traverse child directed graph nodes in the first-level directed graph first and then traverse parent directed graph nodes in the first-level directed graph.

According to some embodiments of the present application, when traversing to a current first-level directed graph node of one or more first-level directed graph nodes in the first-level directed graph, attribute information of the current first-level directed graph node may be determined according to a first-level atomic operator represented by the current first-level directed graph node and attribute information of a first-level sub-directed graph node of the current first-level directed graph node, wherein the attribute information of each first-level directed graph node includes one or more of: the method may further include determining, based on the location information of the cells in the spreadsheet, tag information corresponding to the cells in the spreadsheet and intermediate-level directed graph nodes, intermediate-level directed graph nodes corresponding to the cells in the spreadsheet, and attribute information of each of the one or more first-level directed graph nodes in the intermediate-level directed graph, determining respective intermediate-level directed graph node sets in the intermediate-level directed graph, and determining, based on the dependency between the one or more first-level directed graph nodes in the first-level directed graph, a dependency between each intermediate-level directed graph node in each intermediate-level directed graph node set and each intermediate-level directed graph node in other intermediate-level directed graph node sets in the intermediate-level directed graph.

Each node in the first level directed graph for the first level compound operator of the hierarchy stores attribute information (1212-1219, 1231-1233 as shown on the right side in fig. 10) in addition to the atomic operators. The attribute information is used to record an intermediate calculation process for converting the first level directed graph into the second level directed graph. For each node in the first level directed graph in the hierarchical formula for a row, the attribute information may be a tagged row (a list of column tags (referred to as a column tag set) and a list of intermediate level directed graph nodes that store the location of a cell or a cell atom operator (referred to as an intermediate level atom operator), and other intermediate nodes that the atom operator depends on), or a scalar (an intermediate level directed graph node without a tag). For each node in the first-level directed graph of the hierarchical formula for other hierarchical types, the attribute information may also be a column with labels (a list of row labels (referred to as a row label group) and a list of intermediate-level directed graph nodes), or may be a two-dimensional list of row labels, a column label group and an intermediate-level directed graph node corresponding thereto. All intermediate-level directed graph nodes corresponding to all first-level directed graph nodes constitute an intermediate-level directed graph. For a hierarchical formula for a row, attribute information of a leaf node of a compound operator of a hierarchy can be directly obtained from the position of a row or cell corresponding to the leaf node, and if the leaf node corresponds to a row (e.g., 1202, 1203, 1207 shown on the left side in fig. 10), the attribute information of the leaf node is obtained from a column label group of the row and an intermediate-level directed graph node (e.g., 1212, 1213, 1217 shown on the right side in fig. 10) storing the position of each cell in the row; if this leaf node corresponds to a cell (e.g., 1221, 1222 shown on the left side of fig. 10), then its attribute information is the mid-level directed graph node (e.g., 1231, 1232 shown on the right side of fig. 10) where this cell is stored.

As described above, a method of subsequent traversal may be employed to traverse the directed graph of compound operators for the hierarchy (traversing child nodes first and then parent nodes). When traversing to each node, the attribute information of the node can be determined according to the atomic operator of the node and the attribute information of the child nodes of the node. For example, by performing a subsequent traversal of the directed graph for the composite operator of the hierarchy shown on the left side of FIG. 10, an intermediate-level directed graph shown on the right side of FIG. 10 may be obtained, wherein each set of intermediate-level directed graph nodes in the intermediate-level directed graph may be determined from attribute information for each of the one or more first-level directed graph nodes in the first-level directed graph, and dependencies between each intermediate-level directed graph node in each intermediate-level directed graph node set and each intermediate-level directed graph node in other sets of intermediate-level directed graph nodes in the intermediate-level directed graph may be determined from at least dependencies between the one or more first-level directed graph nodes in the first-level directed graph.

According to some embodiments of the present application, the first level atomic operators are divided into one of element-by-element atomic operators (Elementwise Atomic Operators), dimension-reduction atomic operators (Dimension-Reduction Atomic Operators), dislocation atomic operators (Transposition Atomic Operators), user-defined atomic operators.

Element-by-element atomic operators may include mathematical operators of "+, -,/" and the like, and functions represented by capital letters, such as "SUM (), AVERAGE (), and the like. When an element-by-element atomic operator acts on two rows, cells in the two rows that have the same column label correspondence may be added.

The dimension reduction atomic operator may be expressed in lowercase letters and connected to variables with "." excluding dislocation atomic operators.

The shift atomic operators may refer to atomic operators such as "lag (1), lead (1), shift (1)", and when determining tag information in attribute information of nodes of the shift atomic operators, it is necessary to shift tag information of child nodes of the shift operators.

According to some embodiments of the present application, when the first level atomic operator represented by the current first level directed graph node in the traversed first level directed graph is an element-by-element atomic operator, the following operations may be performed.

If the attribute information of each first-stage directed graph node of the current first-stage directed graph node contains label information corresponding to a cell in the electronic table, setting the label information in the attribute information of the current first-stage directed graph node according to the intersection of the label information contained in the attribute information of each first-stage directed graph node and the intermediate-stage directed graph node, setting an intermediate-stage atom operator in the attribute information of the current first-stage directed graph node according to a first-stage atom operator represented by the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information and the intermediate-stage atom operator in the attribute information of the current first-stage directed graph node, and determining a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to at least the dependency relationship between the current first-stage directed graph node and the first-stage directed graph node.

For example, for a hierarchical formula for a row, when traversing to an element-by-element atom operator (e.g., when traversing to 1205, 1208, 1223 shown on the left side in fig. 10): if the attribute information of the child nodes are all labeled rows, the column label sets of the rows are intersected as the column label set of the node. For example, when traversing to 1205 shown on the left side in fig. 10, the attribute information 1212 and 1214 of its child nodes are labeled rows, the column label groups are "[2017,2018,2019]" and "[2018,2019,2020]", respectively, and the intersection of these two column label groups "[2018,2019]" is taken as the label of the attribute information (1215) of 1205. Setting the intermediate-level directed graph node intermediate-level atomic operators in 1215 to be cell atom operators "+" corresponding to element-by-element atomic operators of 1205, and setting intermediate nodes on which intermediate-level directed graph nodes in the positions corresponding to the "2018" labels depend to be intermediate-level directed graph nodes in the positions corresponding to the "2018" labels in 1212 and 1214; the intermediate-level directed graph node on which the intermediate-level directed graph node depends at the position corresponding to the "2019" tag is set as the intermediate-level directed graph node at the position corresponding to the "2019" tag in 1212 and 1214.

If the attribute information of each first-stage directed graph node of the current first-stage directed graph node does not contain tag information, the attribute information of each first-stage sub-directed graph node of the current first-stage directed graph node does not contain tag information, an intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node is set according to a first-stage atomic operator represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node.

If the attribute information of the child node is all the intermediate-level directed graph node without the label (for example, node 1223), the attribute information (1233) of the node is also set as an intermediate-level directed graph node without the label, the atomic operator of the intermediate-level directed graph node of 1233 is a cell atomic operator ("x") corresponding to the element-by-element atomic operator, and the dependent intermediate-level directed graph node is the intermediate-level directed graph node (1231, 1232) in the attribute information of the child node.

If the attribute information of a portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node contains label information corresponding to cells in the electronic table and the attribute information of another portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node does not contain label information corresponding to cells in the electronic table, setting the label information in the attribute information of the current first-stage directed graph node according to an intersection of the label information contained in the attribute information of the first-stage sub-directed graph nodes containing label information, setting intermediate-stage atom operators in the attribute information of the current first-stage directed graph node according to first-stage atom operators represented by the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information and intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and determining a respective intermediate-stage directed graph node set corresponding to the current intermediate-stage directed graph node according to at least a dependency relationship between the current first-stage directed graph node and its first-stage directed graph node and respective intermediate-stage directed graph node in the respective intermediate-stage directed graph node.

If the attribute information of the child node has both a row with a label and a scalar (such as a node 1208), firstly taking the intersection of a column label group ([ 2017,2018,2019 ]) of the attribute information of the child node as a column label group ([ 2017,2018,2019 ]) of the attribute information (1218) of the node, setting an atomic operator of an intermediate node in 1218 as a cell atomic operator 'sum' corresponding to a first-level atomic operator of 1208, setting an intermediate node on which the intermediate node in a position corresponding to a '2017' label depends as an intermediate node in a position corresponding to a '2017' label in 1217, and setting the intermediate node 1216 and the intermediate node 1233; in addition, the operations of the intermediate nodes corresponding to the "2018" and "2019" labels in 1218 are similar, and will not be described here again.

According to some embodiments of the present application, when the first-level atomic operator represented by the current first-level directed graph node in the traversed first-level directed graph is a dimension-reduction atomic operator, the following operations may be performed.

The method comprises the steps that tag information is not contained in attribute information of a current first-stage directed graph node or a subset of tag information in attribute information of a first-stage sub-directed graph node of the current first-stage directed graph node is contained in the attribute information of the current first-stage directed graph node, intermediate-stage atom operators in the attribute information of the current first-stage directed graph node are set at least according to first-stage atom operators represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and dependency relationships between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets are determined at least according to dependency relationships between the current first-stage directed graph node and first-stage sub-directed graph nodes.

For example, when traversing to a dimension reduction operator (such as node 1206), the attribute information of the node is set to a scalar, the operator of the intermediate-level directed graph node (1216) corresponding to the scalar is set to a cell atom operator ("sum") corresponding to the dimension reduction operator, and all intermediate-level directed graph nodes in the attribute information (1215) of the child node (1205) whose dependency node is 1206 are intermediate-level directed graph nodes.

According to some embodiments of the present application, when the first level atomic operator represented by the current first level directed graph node in the traversed first level directed graph is a dislocated atomic operator, the following operations may be performed.

In the case that the attribute information of the first-stage directed graph node of the current first-stage directed graph node contains tag information corresponding to cells in a spreadsheet, the tag information after dislocation obtained after dislocation of the tag information contained in the attribute information of the first-stage directed graph node is set as the tag information in the attribute information of the current first-stage directed graph node, an 'equivalent' atomic operator is set as an intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined according to the tag information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node.

For example, when traversing to 1204, since the atomic operator of 1204 is ". Lag (1)", the column tag group "[2017,2018,2019]" wrong 1 bit of the attribute information (1213) of the child node 1203 of 1204 is changed to "[2018,2019,2020]". Setting the cell atom operator of the intermediate node at the "2018", "2019" position in 1214 to "id (equivalent)", the dependent node is set to the intermediate node corresponding to the "2018", "2019" position in 1213.

According to some embodiments of the present application, sub-directed graphs having each intermediate-level directed graph root node in the set of intermediate-level directed graph root nodes as a root node may be extracted from the intermediate-level directed graphs, respectively, and the extracted sub-directed graphs are used as second-level directed graphs to determine a second-level composite operator from the second-level directed graphs.

According to some embodiments of the present application, in the case of obtaining the intermediate-stage directed graph root node set, the positions of the respective intermediate-stage directed graph root nodes in the intermediate-stage directed graph root node set may be corresponding to the positions of the respective intermediate-stage directed graph left value nodes in the intermediate-stage directed graph left value node set. For example, the position of each intermediate-stage directed graph root node may be corresponding to the position of each intermediate-stage directed graph left-value node by matching the tag information in the attribute information of each intermediate-stage directed graph root node with the tag information in the attribute information of each intermediate-stage directed graph left-value node. Then, the sub-directed graph with the intermediate-level directed graph root node at the corresponding matching location as the root node may be extracted as a second-level directed graph to determine a second-level composite operator of the cell corresponding to the intermediate-level left-value node from the second-level directed graph.

For example, in the case of obtaining the attribute information 1218 of the node 1208, each intermediate node in 1218 corresponds to a composite operator for a cell, which only requires extracting the sub-directed graph with the intermediate node as the root node. For example, the composite operator for the cell corresponding to the tag "2018" position in 1218 is shown in FIG. 11. The composite operator of these cells is assigned to the cell with the same label position as the left value. For example, the composite operator of FIG. 11 may be assigned to the cell corresponding to the intermediate node labeled "2018" in the row corresponding to 1219 in FIG. 10. Specifically, for example, in the case where the attribute information 1218 of the node 1208 is obtained, the position of each attribute information 1218 may be corresponded to the position of each attribute information 1219 of the node 1209. For example, the intermediate-level directed graph node position corresponding to the tag information "2018" in the attribute information 1218 may be corresponding to the intermediate-level directed graph node position corresponding to the tag information "2018" in the attribute information 1219, and the intermediate-level directed graph node position corresponding to the tag information "2019" in the attribute information 1218 may be corresponding to the intermediate-level directed graph node position corresponding to the tag information "2019" in the attribute information 1219 by matching the tag information "2017", "2018", "2019" in the respective attribute information 1219 with the tag information "2018", "2019", "2020", and the like. Then, the child directed graph with node "2018" in the attribute information 1218 as the root node may be extracted as a second-level compound operator and assigned to the cell corresponding to the intermediate-level directed graph node in the tag "2018" position in the attribute information 1219.

Note that for hierarchical formulas for different hierarchies such as columns, blocks, bins, cells, etc., similar steps may be used to convert a first level directed graph to a second level directed graph. For example, for a hierarchical formula for a column, a row tag group in the first level directed graph node attribute information may be operated on in the manner described above for a column tag group. For another example, for a hierarchical formula for a block, when traversing to each element-wise atomic operator, setting an intersection of column label groups contained in attribute information of a first-level child directed graph node of the current first-level directed graph node as column label groups in attribute information of the current first-level directed graph node, and setting an intersection of row label groups contained in attribute information of the first-level child directed graph node of the current first-level directed graph node as row label groups in attribute information of the current first-level directed graph node; when traversing to the dimension reduction atom operator, enabling the attribute information of the current first-stage directed graph node to contain no label information or only a subset (for example, only one row label group or only one column label group) of the label information contained in the attribute information of the first-stage sub-directed graph node of the current first-stage directed graph node; when traversing to the dislocation atom operator, setting a dislocation post-row label group obtained after dislocation of a row label group contained in the attribute information of the first-stage directed graph node as a row label group in the label information of the attribute information of the current first-stage directed graph node, or setting a dislocation post-column label group obtained after dislocation of a column label group contained in the attribute information of the first-stage directed graph node as a column label group in the label information of the attribute information of the current first-stage directed graph node, or executing dislocation of the row label group and the column label group at the same time. For another example, for a hierarchical formula for intervals and cells, steps similar to those for rows and columns may also be employed. For brevity, specific details are not described in detail herein.

Referring back to FIG. 1, after step S107, the computer-implemented method 100 for generating the spreadsheet formula may end.

According to some embodiments of the present application, the generated spreadsheet formula for each of the one or more first cells may be stored into a corresponding cell in a spreadsheet file.

For example, in the example shown in fig. 9 above, the spreadsheet formula strings of the three cells F10, G10, H10 generated may be written to the corresponding cells in a spreadsheet file of a common format (e.g., a file of an. Xls or. Xlsx format), respectively. In addition, if opened with the general spreadsheet processing software, the results of the three cells F10, G10, H10 can be calculated.

According to some embodiments of the present application, the step of storing the generated spreadsheet formula for each of the one or more first cells into the respective first cell may further comprise: automatically naming cell names of a portion or all of the one or more first cells and the one or more second cells in the spreadsheet; and overwriting the spreadsheet formula with the automatically named cell name and storing the overwritten spreadsheet formula.

For example, some or all cells in a spreadsheet may be automatically named when writing to a spreadsheet file (cell naming in a file in the format of xls or xlsx), and spreadsheet formulas of the same meaning may be written. For example, cell C12 may be named "average revenue growth rate" and the formulas written in cells F10, G10, H10 are rewritten to "=e $30×1+average revenue growth rate)", "=f $30×1+average revenue growth rate)", "=g $30×1+average revenue growth rate".

According to some embodiments of the present application, an intelligent suggestion knowledge base may be updated based on the hierarchical formula that has been acquired, the intelligent suggestion knowledge base including information related to the hierarchical formula or hierarchy; and intelligent advice may be made when a new hierarchical formula is to be obtained based on the intelligent advice knowledge base.

For example, the knowledge base of intelligent cues may be updated based on what has been entered. For example, if a user adds three rows of "cell phones", "personal computers", "tablet computers" under the block named "revenue", then these three nouns can be added to the intelligent hint knowledge base and associated with "revenue". For another example, a user creates a table named "Income status" under a page named "Financial Statement" under a workbook named "evaluation Model" and adds a line named "revenues" therein, and then adds a path named "evaluation Model/Financial Statement/Income status/revenues" to the knowledge base of the hierarchical path. Other prompt information related to the hierarchical formula and/or the hierarchical computational graph may also be included in the knowledge base according to different desired intelligent prompt information, which is not limited in this application.

The intelligent prompt can be carried out on the user according to the intelligent prompt knowledge base in the user editing hierarchical formula. For example, in the above example, after updating the knowledge base of intelligent cues, "cell phone", "personal computer", "tablet" in the knowledge base has been associated with "revenue", when the user edits a hierarchical formula for a row named "revenue", the user is presented with intelligent cues "= SUM ({ cell phone }, { personal computer }, { tablet })", and other hierarchical formulas that occur with high probability for the user to select. For another example, when a user wishes to refer to the "revenue" row under a "profit sheet" table when editing the hierarchical formula under a "SUMMARY sheet" table, the "revenue" is entered and then the path of all nodes in the hierarchical computation graph, named "revenue" (or similar in name to "revenue"), is prompted for the user to select from.

According to some embodiments of the present application, a user may combine custom atomic operators through atomic operators preset by the system.

The custom atomic operator is described below with reference to FIG. 12. FIG. 12 is a schematic diagram schematically illustrating directed graphs of custom atomic operators according to some embodiments of the present application.

For example, a user may customize a new custom atomic operator "growth rate ()" by entering "growth rate ({ x1}, { x2 }) = { x2}/{ x1} -1". According to some embodiments of the present application, the custom atomic operator "growth rate ()" may also be represented by a directed graph. For example, as shown in fig. 12, the custom atomic operator "growth rate ()" can be represented by a directed graph 1511.

In the case where a new custom atomic operator is customized, the customized custom atomic operator may be used when entering a hierarchical formula for the spreadsheet. For example, with the custom atom operator "growth rate ()" described above, the following hierarchical formula for the spreadsheet may be entered:

"{ rate of increase in revenue } = rate of increase ({ revenue }. Lag (1), { revenue })",

the compound operator for the hierarchical formula of the spreadsheet may also be represented by a directed graph. For example, referring to FIG. 12, the hierarchical formula for a spreadsheet may be represented by an original directed graph 1512, where the original directed graph 1512 includes an operator 1508 and two child directed graphs with operators 1508 as parent nodes: the first sub-directed graph "{ x1}. Lag (1)" 1504 and the second sub-directed graph "{ x2}"1506, the parameter placeholders "x1" and "x2" correspond to the node "revenue" of the same hierarchy computation graph. Where operator 1508 is the custom atomic operator "growth rate".

If custom atomic operators are included in the hierarchical formulas for the electronic forms, then the custom atomic operators need to be converted to other types of atomic operators preset by the system. For example, for each custom atomic operator, the custom atomic operator may be replaced in the original directed graph of the hierarchical formula for the spreadsheet with the root node of the directed graph representing the custom atomic operator, and the leaf nodes in the directed graph of the custom atomic operator may be replaced with child directed graphs of the original directed graph of the hierarchical formula that have the custom atomic operator as a parent node. The steps shown in FIG. 10 may then be performed using the directed graph of the transformed composite operator.

For example, referring to FIG. 12, for the custom atomic operator "growth rate" 1508 in the original directed graph 1512 of the hierarchical formula described above, it may be replaced with the root node 1517 of the directed graph 1511 representing the custom atomic operator "growth rate" (in the replaced directed graph 1513, the subgraph 1503 corresponds to the subgraph 1502 of the directed graph 1511), and the placeholder representing the leaf node "x1" in the directed graph 1511 (which becomes the subgraph 1505 of the directed graph 1513) may be replaced with the first sub-directed graph "{ x1 }" 1504 of the custom atomic operator "growth rate" 1508 in the original directed graph 1512 of the hierarchical formula, and the placeholder representing the leaf node "x2" in the directed graph 1502 (which becomes the sub-directed graph 1507 of the directed graph 1513) may be replaced with the second sub-directed graph "{ x2}"1506 of the custom atomic operator "growth rate" 1508, thereby resulting in the converted directed graph 1513 shown in FIG. 12. The steps shown in FIG. 10 may then be performed using the directed graph 1513 of the transformed composite operator.

According to some embodiments of the present application, defects existing in the acquired hierarchical formula may be automatically detected; and may automatically generate debug information based on the defect and prompt the debug information, wherein the debug information includes an error or warning.

The user may make various degrees of errors in inputting the hierarchical formula or the input format may be non-standard. In some implementations of the present application, the system may automatically generate debug information. Debug information includes errors (Error) and warnings (Warning). An error means that the input hierarchical formula does not conform to the syntax of the hierarchical formula, and a correct spreadsheet model cannot be generated; the alert refers to the input hierarchical formula conforming to the syntax of the hierarchical formula, but the generated spreadsheet model is likely not conforming to the true intent of the hierarchical formula writer. For example, in some implementations of the present application, a user is allowed to reference a "Revenue" line under the "Income status" table in the hierarchical formula before defining this line, which is referred to as an "undefined reference" (Undefined Reference). This is just a warning message before the user has finished entering, as the user may define these temporary undefined references later. But this becomes an error if these undefined references have not been defined by the time the user has completed the input of the hierarchical formulas in the entire spreadsheet model. Since the debug information is constantly changing as the user inputs, the debug information needs to be updated after the user inputs a new hierarchical formula. In different implementations of the present application, different kinds of debug information may be included. For example, in implementations for financial models, accounting equations may be included in the knowledge base of debug information, and if the entered hierarchical formulas and accounting equations are contradictory, warning information is generated. For another example, algorithms for determining correction of erroneous hierarchical formulas may be included in the debug information knowledge base, and when erroneous hierarchical formulas are entered, erroneous information may be generated and possible correction suggestions automatically generated. In other implementations of the present application, more complex debug information may also be included, which is not limiting in this application.

The generated debug information may be prompted to the user in some form. For example, when undefined references appear in the hierarchy formula, they are highlighted, when a hierarchy that is likely to match is defined after the hierarchy formula is entered, the designation of the number "n" at the undefined reference indicates that there are n levels that are likely to match, and when one of the levels is selected, the highlighting of the undefined reference is canceled. For another example, if "= { cell phone } ++ { personal computer }" is input in the hierarchical formula, the error message "nonsensical operator++" is prompted, and is considered to be changed to + ". For another example, if the user enters the hierarchy formula "= { revenue } + { cost }" in the line of "profit", the alert message "possibly wrong formula" is prompted, considering the modification to = { revenue } { cost }).

According to some embodiments of the present application, a spreadsheet model may be stored that includes one or more of the following: a hierarchical structure for a spreadsheet, a hierarchical computational graph for a spreadsheet, and a spreadsheet file.

According to some embodiments of the present application, the spreadsheet model may be imported and/or exported.

According to another aspect of the present application, there may be provided an apparatus for generating a spreadsheet formula, comprising: means for performing the steps of the methods as described above.

According to another aspect of the present application, there may be provided an apparatus for generating a spreadsheet formula, comprising: at least one processor; and at least one storage device storing instructions that, when executed by the at least one processor, cause the at least one processor to perform the method as previously described.

According to another aspect of the present application, a non-transitory computer readable storage medium may be provided, storing instructions which, when executed by a processor, cause performance of the method as previously described.

According to another aspect of the present application, an apparatus for generating a spreadsheet formula may be provided. An apparatus for generating a spreadsheet formula is described below with reference to FIG. 13. Fig. 13 is a block diagram schematically illustrating an apparatus 1300 for generating a spreadsheet formula according to some embodiments of the present application.

As shown in fig. 13, an apparatus 1300 for generating a spreadsheet formula may include: a hierarchical formula editor 1301 configured to obtain a hierarchical formula for the spreadsheet, the hierarchical formula comprising operators and hierarchical references, wherein the hierarchical references correspond to one or more nodes in one or more hierarchies for a spreadsheet, wherein each hierarchy has at least one node and has a corresponding hierarchy type, each node corresponds to one or more cells in the spreadsheet, wherein the hierarchy further comprises membership between the one or more nodes; a hierarchical formula parser 1303, the hierarchical formula parser 1303 configured to determine a dependency relationship between the one or more nodes based on a correspondence relationship between the hierarchical reference and the one or more nodes; the hierarchical formula parser is further configured to determine a computational relationship between the one or more nodes based on the operator and the dependency relationship; and a formula translator 1305, the formula translator 1305 configured to generate the spreadsheet formula based at least on the hierarchical structure and the computational relationship for the spreadsheet.

According to some embodiments of the present application, the hierarchical formula editor 1301 may include a graphical user interface editor, wherein the graphical user interface editor includes components for specifying nodes and/or operators of different hierarchies, and wherein the graphical user interface editor may be further configured to: receiving input for components specifying nodes and/or operators of different levels; and generating the hierarchical formula based on the input.

According to some embodiments of the present application, the hierarchical formula editor 1301 may include a text editor that may be configured to: receiving a text code input by a user; and parsing the text code to generate the hierarchical formula.

According to some embodiments of the present application, the operator may include an atomic operator including one or more of: mathematical operators, mathematical functions, constants, system defined functions, and user-defined atomic operators.

According to some embodiments of the present application, the hierarchical formula parser 1303 may be further configured to: constructing a composite operator from the atomic operators in the hierarchical formula, wherein the composite operator comprises a combination of all atomic operators in the hierarchical formula.

According to some embodiments of the present application, the hierarchical formula parser 1303 may be further configured to: a computational relationship between the one or more nodes is determined based at least on the composite operator and the dependency relationship.

According to some embodiments of the present application, the hierarchical references may include a first hierarchical reference to the left of the equal sign in the hierarchical formula and a second hierarchical reference to the right of the equal sign in the hierarchical formula, wherein the first hierarchical reference corresponds to a first node of the one or more nodes and the second hierarchical reference corresponds to a second node of the one or more nodes.

According to some embodiments of the present application, the hierarchical formula parser 1303 may be further configured to: a dependency relationship between the first node and the second node is determined based at least on a correspondence between the first hierarchical reference and the first node and a correspondence between the second hierarchical reference and the second node.

According to some embodiments of the present application, the apparatus 1300 for generating a spreadsheet formula may further comprise a hierarchical computational graph memory 1307, the hierarchical computational graph memory 1307 may be configured to: for each of the one or more nodes, a computing relationship between the node and other nodes is stored via a hierarchical computing graph.

According to some embodiments of the present application, the hierarchical formula parser 1303 may be further configured to: a computational relationship between the first node and the second node is determined based on the operator and the dependency relationship between the first node and the second node.

According to some embodiments of the present application, the hierarchical computational graph memory 1307 may be further configured to: for a first node, a computing relationship between the first node and a second node is stored via a hierarchical computation graph.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: for each current node of the one or more nodes, determining a calculated relationship between each first cell of the one or more first cells corresponding to the current node and each second cell of the one or more second cells corresponding to the other node based on the calculated relationship between the current node and the other nodes; and generating a spreadsheet formula for each of the one or more first cells based at least on a calculated relationship between each of the one or more first cells and each of the one or more second cells.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: determining an operator corresponding to each of one or more first cells corresponding to the current node based at least on the operator corresponding to the current node in the one or more nodes; and determining a dependency relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to the other node based at least on the dependency relationship between the current node and the other node.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: a spreadsheet formula for each of the one or more first cells is generated based at least on an operator corresponding to each of the one or more first cells corresponding to the current node and a dependency relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to other nodes.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: the operator corresponding to the current node of the one or more nodes is a first level compound operator corresponding to one of the one or more nodes in the hierarchy for the spreadsheet, the first level compound operator comprising one or more first level atomic operators; the operator corresponding to each of the one or more first cells is a second level compound operator corresponding to one of the one or more cells in the spreadsheet, the second level compound operator comprising one or more second level atomic operators; and determining a second-level composite operator corresponding to each of the one or more first cells corresponding to the current node based at least on the first-level composite operator corresponding to the current node of the one or more nodes.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: the first-level compound operators are stored by adopting a first-level directed graph, each directed graph node in the first-level directed graph represents each first-level atomic operator in the first-level compound operators, and each directed edge among each directed graph node in the first-level directed graph represents the combination relation among each first-level atomic operator in the first-level compound operators; the second-level compound operators are stored by using second-level directed graphs, each directed graph node in the second-level directed graph represents each second-level atom operator in the second-level compound operators, and directed edges among each directed graph node in the second-level directed graph represent a combination relation among each second-level atom operator in the second-level compound operators; and determining a second level directed graph by traversing the first level directed graph corresponding to the first level composite operator, and determining the second level composite operator from the second level directed graph.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: traversing the first-level directed graph according to the sequence of subsequent traversal, so as to traverse child directed graph nodes in the first-level directed graph first and then traverse parent directed graph nodes in the first-level directed graph.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: determining attribute information of one or more first-level directed graph nodes in the first-level directed graph according to first-level atomic operators represented by the first-level directed graph nodes and attribute information of first-level sub-directed graph nodes of the first-level directed graph nodes, wherein the attribute information of each first-level directed graph node comprises one or more of the following: the method comprises the steps of (1) carrying out position information of cells in a spreadsheet, tag information corresponding to the cells in the spreadsheet and position information of the cells in the spreadsheet, tag information corresponding to the cells in the spreadsheet and intermediate-level directed graph nodes, and intermediate-level directed graph nodes corresponding to the cells in the spreadsheet; determining respective intermediate-level directed graph node sets in the intermediate-level directed graph according to attribute information of each of the one or more first-level directed graph nodes in the first-level directed graph, and determining dependency relationships between respective intermediate-level directed graph nodes in each of the intermediate-level directed graph and respective intermediate-level directed graph nodes in other intermediate-level directed graph node sets in the intermediate-level directed graph according to dependency relationships between the one or more first-level directed graph nodes in the first-level directed graph.

According to some embodiments of the present application, the first level atomic operators are divided into one of element-by-element atomic operators, dimension-reduction atomic operators, dislocation atomic operators, user-defined atomic operators.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: when the first-level atomic operator represented by the current first-level directed graph node in the traversed first-level directed graph is an element-by-element atomic operator, performing the following operations:

if the attribute information of each first-stage directed graph node of the current first-stage directed graph node contains label information corresponding to a cell in the electronic table, setting the label information in the attribute information of the current first-stage directed graph node according to the intersection of the label information contained in the attribute information of each first-stage directed graph node and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and determining a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information of the current first-stage directed graph node;

If the attribute information of each first-stage directed graph node of the current first-stage directed graph node does not contain label information, an intermediate-stage atom operator in the attribute information of the current first-stage directed graph node is set according to a first-stage atom operator represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to the intermediate-stage atom operator in the attribute information of the current first-stage directed graph node, and the dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets in the intermediate-stage directed graph node set is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node;

if the attribute information of a portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node contains tag information corresponding to cells in the electronic table and the attribute information of another portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node does not contain tag information, setting the tag information in the attribute information of the current first-stage directed graph node according to an intersection of the tag information contained in the attribute information of the first-stage sub-directed graph nodes containing tag information, setting intermediate-stage atom operators in the attribute information of the current first-stage directed graph node according to first-stage atom operators represented by the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the tag information and intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and determining respective intermediate-stage directed graph node sets corresponding to the current intermediate-stage directed graph nodes in the current first-stage directed graph node and respective intermediate-stage directed graph nodes according to at least a dependency relationship between the current first-stage directed graph node and its first-stage sub-directed graph node and respective intermediate-stage directed graph node.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: when the first-level atomic operator represented by the current first-level directed graph node in the traversed first-level directed graph is a dimension-reduction atomic operator, executing the following operations:

the method comprises the steps that tag information is not contained in attribute information of a current first-stage directed graph node or a subset of tag information in attribute information of a first-stage sub-directed graph node of the current first-stage directed graph node is contained in the attribute information of the current first-stage directed graph node, intermediate-stage atom operators in the attribute information of the current first-stage directed graph node are set at least according to first-stage atom operators represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and dependency relationships between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets are determined at least according to dependency relationships between the current first-stage directed graph node and first-stage sub-directed graph nodes.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: when the first-level atomic operator represented by the current first-level directed graph node in the traversed first-level directed graph is a dislocation atomic operator, executing the following operations:

in the case that the attribute information of the first-stage directed graph node of the current first-stage directed graph node contains tag information corresponding to cells in the electronic table, setting tag information in the attribute information of the current first-stage directed graph node according to the tag information after dislocation obtained after dislocation of the tag information contained in the attribute information of the first-stage directed graph node, setting an 'equivalent' atomic operator as an intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the tag information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and determining a respective intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and a respective intermediate-stage directed graph node set in the current first-stage directed graph node according to at least the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node thereof and the tag information in the attribute information of the first-stage directed graph node thereof.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: sub-directed graphs with each intermediate-level directed graph root node in the set of intermediate-level directed graph root nodes as a root node are extracted from the intermediate-level directed graphs, respectively, and the extracted sub-directed graphs are used as second-level directed graphs to determine a second-level composite operator according to the second-level directed graphs.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: matching the label information in the attribute information of each intermediate-stage directed graph root node with the label information in the attribute information of each intermediate-stage directed graph left value node, and corresponding the positions of each intermediate-stage directed graph root node in the intermediate-stage directed graph root node set with the positions of each intermediate-stage directed graph left value node in the intermediate-stage directed graph left value node set; and extracting the sub-directed graph taking the root node of the intermediate-level directed graph at the corresponding matching position as a root node as a second-level directed graph to determine a second-level compound operator of a cell corresponding to the intermediate-level left-value node according to the second-level directed graph.

According to some embodiments of the present application, the apparatus 1300 for generating a spreadsheet formula may further comprise a spreadsheet formula writer 1309, the spreadsheet formula writer 1309 may be configured to: the generated spreadsheet formula for each of the one or more first cells is stored into a corresponding cell in a spreadsheet file.

According to some embodiments of the present application, the custom atomic operators may be defined by combining atomic operators preset by the system.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: and under the condition that the self-defined atomic operators are included in the hierarchical formula for the electronic table, converting the self-defined atomic operators into atomic operators preset by a system.

According to some embodiments of the present application, the formula translator 1305 may be further configured to: replacing the custom atomic operator in the original directed graph of the hierarchical formula with a root node of the directed graph representing the custom atomic operator; and replacing leaf nodes in the directed graph of the custom atomic operator with child directed graphs of the original directed graph of the hierarchical formula having the custom atomic operator as a parent node.

According to some embodiments of the present application, the apparatus 1300 for generating a spreadsheet formula may further include an intelligent suggestion module 1311, the intelligent suggestion module 1311 may be configured to: updating an intelligent suggestion repository based on the hierarchical formula that has been acquired, the intelligent suggestion repository including information related to the hierarchical formula or hierarchy; and based on the intelligent suggestion knowledge base, performing intelligent suggestion when a new hierarchical formula is to be acquired.

According to some embodiments of the present application, the apparatus 1300 for generating a spreadsheet formula may further include a defect detection module 1313, the defect detection module 1313 may be configured to: automatically detecting defects in the acquired hierarchical formula; and automatically generating debug information based on the defect and prompting the debug information, wherein the debug information comprises an error or warning.

According to some embodiments of the present application, the apparatus 1300 for generating a spreadsheet formula may further comprise a spreadsheet model storage module 1315, the spreadsheet model storage module 1315 may be configured to store a spreadsheet model comprising one or more of the following: a hierarchical structure for a spreadsheet, a hierarchical computational graph for a spreadsheet, and a spreadsheet file.

According to some embodiments of the present application, the apparatus 1300 for generating a spreadsheet formula may further comprise a spreadsheet model import-export module 1317, which spreadsheet model import-export module 1317 may be configured to import and/or export the spreadsheet model.

According to some embodiments of the present application, the text editor may be configured to: word segmentation is carried out on the text codes to obtain word segmentation streams; identifying a word segment in the word segment stream that is related to the operator to determine the operator; identifying, in the stream of tokens, the tokens associated with the one or more nodes in the one or more levels in the hierarchy of the spreadsheet to determine the level reference; and generating the hierarchical formula according to the determined operator and hierarchical reference.

According to some embodiments of the present application, the composite operator may be stored with one or more of the following: a character string; a computer-implemented function pointed to by a function pointer; and directed graphs.

According to some embodiments of the present application, each node in the directed graph may represent the operator in the hierarchical formula, and directed edges between each node in the directed graph may represent a combined relationship between the operators in the hierarchical formula.

According to some embodiments of the present application, the spreadsheet formula writer 1309 may be further configured to: automatically naming cell names of a portion or all of the one or more first cells and the one or more second cells in the spreadsheet; and overwriting the spreadsheet formula with the automatically named cell name and storing the overwritten spreadsheet formula.

Note that the hierarchical computational graph memory 1307, the spreadsheet formula writer 1309, the intelligent suggestion module 1311, the defect detection module 1313, the spreadsheet model storage module 1315, and the spreadsheet model import-export module 1317 in fig. 13 are optional, and thus their blocks are dashed lines.

Note that the details of the apparatus 1300 for generating a spreadsheet formula may be referred to the detailed description above for the computer-implemented method for generating a spreadsheet formula, the details of which are not described here again.

The method and the device support the calculation relation among the layers in the electronic form model to be input in the form of the layer formula, and generate the electronic form formula in batches according to the calculation relation, and have the advantages of at least one of intuitiveness, automation, reusability and difficult error. The left value and the parameter used for the hierarchical formula are all the hierarchy of the electronic table, the hierarchy reference has clear semantics, and the meaning of the hierarchy can be visually seen through the name of the hierarchy reference, so the hierarchical formula is more visual than the formula in the electronic table format. The hierarchical formulas may be automatically generated in batches of spreadsheet formulas, and in some implementations, even automatically generated or prompted to the user, so the method is automatic and intelligent. Because the hierarchical formulas have clear semantics that are relatively fixed, the hierarchical formulas may be multiplexed, and the hierarchical formulas in one spreadsheet model may be multiplexed to other spreadsheet models without logical errors. The electronic form formula generation method and the electronic form model generation device can also comprise a debugging function, and possible errors in the electronic form model can be identified, and corresponding debugging information is given, so that errors are not prone to being generated by using the electronic form formula generation method and the electronic form model generation device.

FIG. 14 is a schematic block diagram illustrating an information processing device that may be used to implement the generation of spreadsheet formulas according to some embodiments of the present application.

In fig. 14, a Central Processing Unit (CPU) 1401 executes various processes according to a program stored in a Read Only Memory (ROM) 1402 or a program loaded from a storage section 1408 to a Random Access Memory (RAM) 1403. In the RAM1403, data required when the CPU1401 executes various processes and the like is also stored as needed. The CPU1401, ROM 1402, and RAM1403 are connected to each other via a bus 1404. An input/output interface 1405 is also connected to the bus 1404.

The following components are connected to the input/output interface 1405: an input section 1406 (including a keyboard, a mouse, and the like), an output section 1407 (including a display such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker, and the like), a storage section 1408 (including a hard disk, and the like), and a communication section 1409 (including a network interface card such as a LAN card, a modem, and the like). The communication section 1409 performs communication processing via a network such as the internet. The drive 1410 may also be connected to the input/output interface 1405 as needed. A removable medium 1411 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is installed on the drive 1410 as needed, so that a computer program read out therefrom is installed into the storage portion 1408 as needed.

In the case of implementing the above-described series of processes by software, a program constituting the software is installed from a network such as the internet or a storage medium such as the removable medium 1411.

It will be understood by those skilled in the art that such a storage medium is not limited to the removable medium 1411 shown in fig. 14, in which the program is stored, and which is distributed separately from the apparatus to provide the program to the user. Examples of the removable medium 1411 include a magnetic disk (including a floppy disk (registered trademark)), an optical disk (including a compact disk read only memory (CD-ROM) and a Digital Versatile Disk (DVD)), a magneto-optical disk (including a Mini Disk (MD) (registered trademark)), and a semiconductor memory. Alternatively, the storage medium may be a ROM 1402, a hard disk contained in the storage section 1408, or the like, in which a program is stored, and distributed to the user together with a device containing them.

The instruction codes, when read and executed by a machine, may perform the methods according to embodiments of the present application described above.

Computer program code for carrying out operations for aspects of the present application may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, smalltalk, C ++ or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any kind of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or may be connected to an external computer (for example, through the Internet using an Internet service provider).

It will be understood that each block of the flowchart illustrations and/or block diagrams, and combinations of blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the computer program instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable medium that can direct a computer, other programmable data processing apparatus, or other devices to function in a particular manner, such that the instructions stored in the computer-readable medium produce an article of manufacture including instructions which implement the function/act specified in the flowchart and/or block diagram block or blocks (article of manufacture).

The computer program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other devices to cause a series of operational steps to be performed on the computer, other programmable apparatus or other devices to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide processes for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks.

The flowcharts and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present application. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

Note that some details known in the art are not described in order to avoid obscuring the concepts of the present application. How to implement the solutions disclosed herein will be fully apparent to those skilled in the art from the above description.

The present application may also be configured as follows:

(1) A computer-implemented method for generating a spreadsheet formula, the method comprising:

obtaining a hierarchical formula for the spreadsheet, the hierarchical formula comprising operators and hierarchical references, wherein the hierarchical references correspond to one or more nodes in one or more hierarchies in a hierarchical structure for a spreadsheet, wherein each hierarchy has at least one node and has a respective hierarchy type, each node corresponds to one or more cells in the spreadsheet, wherein the hierarchical structure further comprises membership between the one or more nodes;

determining dependencies between the one or more nodes based on correspondence between the hierarchical references and the one or more nodes;

determining a computational relationship between the one or more nodes based on the operator and the dependency relationship; and

the spreadsheet formula is generated based at least on the hierarchical structure for the spreadsheet and the calculated relationship.

(2) The method of (1), wherein the step of obtaining a hierarchical formula for the spreadsheet comprises:

Providing a graphical user interface editor, wherein the graphical user interface editor includes components for specifying nodes and/or operators of different levels;

receiving input for components specifying nodes and/or operators of different levels; and

the hierarchical formula is generated based on the input.

(3) The method of (1), wherein the step of obtaining a hierarchical formula for the spreadsheet comprises:

receiving a text code input by a user; and

the text code is parsed to generate the hierarchical formula.

(4) The method of (1), wherein the operator comprises an atomic operator comprising one or more of: mathematical operators, mathematical functions, constants, system defined functions, and user-defined atomic operators.

(5) The method according to (4), further comprising:

constructing a composite operator from the atomic operators in the hierarchical formula, wherein the composite operator comprises a combination of all atomic operators in the hierarchical formula.

(6) The method of (5), wherein the step of determining a computational relationship between the one or more nodes based on the operator and the dependency relationship further comprises:

A computational relationship between the one or more nodes is determined based at least on the composite operator and the dependency relationship.

(7) The method of any one of (1) to (6), wherein the hierarchical references comprise a first hierarchical reference to the left of an equal sign in the hierarchical formula and a second hierarchical reference to the right of an equal sign in the hierarchical formula, wherein the first hierarchical reference corresponds to a first node of the one or more nodes and the second hierarchical reference corresponds to a second node of the one or more nodes.

(8) The method of (7), wherein the step of determining the dependencies between the one or more nodes based on the correspondence between the hierarchical references and the one or more nodes comprises:

a dependency relationship between the first node and the second node is determined based at least on a correspondence between the first hierarchical reference and the first node and a correspondence between the second hierarchical reference and the second node.

(9) The method according to any one of (1) to (6), further comprising:

for each of the one or more nodes, a computing relationship between the node and other nodes is stored via a hierarchical computing graph.

(10) The method of (8), wherein the determining a computational relationship between the one or more nodes based on the operator and the dependency relationship comprises:

a computational relationship between the first node and the second node is determined based on the operator and the dependency relationship between the first node and the second node.

(11) The method of (10), further comprising:

for a first node, a computing relationship between the first node and a second node is stored via a hierarchical computation graph.

(12) The method of any one of (1) to (6), wherein the generating the spreadsheet formula based at least on the hierarchical structure and the computational relationship for the spreadsheet comprises:

for each current node of the one or more nodes, determining a calculated relationship between each first cell of the one or more first cells corresponding to the current node and each second cell of the one or more second cells corresponding to the other node based on the calculated relationship between the current node and the other nodes; and

a spreadsheet formula for each of the one or more first cells is generated based at least on a calculated relationship between each of the one or more first cells and each of the one or more second cells.

(13) The method of (12), wherein the step of determining the calculated relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to the other node comprises:

determining an operator corresponding to each of one or more first cells corresponding to the current node based at least on the operator corresponding to the current node in the one or more nodes; and

and determining the dependency relationship between each first cell of the one or more first cells corresponding to the current node and each second cell of the one or more second cells corresponding to the other node at least based on the dependency relationship between the current node and the other nodes in the one or more nodes.

(14) The method of (13), wherein the step of generating a spreadsheet formula for each of the one or more first cells comprises:

a spreadsheet formula for each of the one or more first cells is generated based at least on an operator corresponding to each of the one or more first cells corresponding to the current node and a dependency relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to other nodes.

(15) The method according to (14), wherein,

the operator corresponding to the current node of the one or more nodes is a first level compound operator corresponding to one of the one or more nodes in the hierarchy for the spreadsheet, the first level compound operator comprising one or more first level atomic operators;

the operator corresponding to each of the one or more first cells is a second level compound operator corresponding to one of the one or more cells in the spreadsheet, the second level compound operator comprising one or more second level atomic operators; and

and determining a second-level compound operator corresponding to each of the one or more first cells corresponding to the current node based at least on the first-level compound operator corresponding to the current node in the one or more nodes.

(16) The method according to (15), wherein,

the first-level compound operators are stored by adopting a first-level directed graph, each directed graph node in the first-level directed graph represents each first-level atomic operator in the first-level compound operators, and each directed edge among each directed graph node in the first-level directed graph represents the combination relation among each first-level atomic operator in the first-level compound operators;

The second-level compound operators are stored by using second-level directed graphs, each directed graph node in the second-level directed graph represents each second-level atom operator in the second-level compound operators, and directed edges among each directed graph node in the second-level directed graph represent a combination relation among each second-level atom operator in the second-level compound operators; and

a second level directed graph is determined by traversing a first level directed graph corresponding to the first level composite operator, and the second level composite operator is determined from the second level directed graph.

(17) The method of (16), wherein the determining a second level directed graph by traversing a first level directed graph corresponding to a first level composite operator comprises:

traversing the first-level directed graph according to the sequence of subsequent traversal, so as to traverse child directed graph nodes in the first-level directed graph first and then traverse parent directed graph nodes in the first-level directed graph.

(18) The method according to (17), further comprising:

determining attribute information of one or more first-level directed graph nodes in the first-level directed graph according to first-level atomic operators represented by the first-level directed graph nodes and attribute information of first-level sub-directed graph nodes of the first-level directed graph nodes, wherein the attribute information of each first-level directed graph node comprises one or more of the following: the method comprises the steps of obtaining position information of cells in a spreadsheet, tag information corresponding to the cells in the spreadsheet and position information of the cells in the spreadsheet, tag information corresponding to the cells in the spreadsheet and intermediate level nodes, and intermediate level nodes corresponding to the cells in the spreadsheet, wherein the intermediate level nodes are used for storing intermediate calculation results;

Determining respective intermediate-level directed graph node sets in the intermediate-level directed graph according to attribute information of each of the one or more first-level directed graph nodes in the first-level directed graph, and determining dependency relationships between respective intermediate-level directed graph nodes in each of the intermediate-level directed graph and respective intermediate-level directed graph nodes in other intermediate-level directed graph node sets in the intermediate-level directed graph according to dependency relationships between the one or more first-level directed graph nodes in the first-level directed graph.

(19) The method of (18), wherein the first level atomic operators are divided into one of element-by-element atomic operators, dimension-reduction atomic operators, dislocation atomic operators, and user-defined atomic operators.

(20) The method of (19), wherein when the first-level atomic operator represented by the current first-level directed graph node in the traversed first-level directed graph is an element-by-element atomic operator,

if the attribute information of each first-stage directed graph node of the current first-stage directed graph node contains label information corresponding to a cell in the electronic table, setting the label information in the attribute information of the current first-stage directed graph node according to the intersection of the label information contained in the attribute information of each first-stage directed graph node and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and determining a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information of the current first-stage directed graph node;

If the attribute information of each first-stage directed graph node of the current first-stage directed graph node does not contain label information, an intermediate-stage atom operator in the attribute information of the current first-stage directed graph node is set according to a first-stage atom operator represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to the intermediate-stage atom operator in the attribute information of the current first-stage directed graph node, and the dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets in the intermediate-stage directed graph node set is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node;

if the attribute information of a portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node contains tag information corresponding to cells in the electronic table and the attribute information of another portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node does not contain tag information, setting the tag information in the attribute information of the current first-stage directed graph node according to an intersection of the tag information contained in the attribute information of the first-stage sub-directed graph nodes containing tag information, setting intermediate-stage atom operators in the attribute information of the current first-stage directed graph node according to first-stage atom operators represented by the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the tag information and intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and determining respective intermediate-stage directed graph node sets corresponding to the current intermediate-stage directed graph nodes in the current first-stage directed graph node and respective intermediate-stage directed graph nodes according to at least a dependency relationship between the current first-stage directed graph node and its first-stage sub-directed graph node and respective intermediate-stage directed graph node.

(21) The method of (19), wherein when the first-level atomic operator represented by the current first-level directed graph node in the traversed first-level directed graph is a dimension-reduction atomic operator,

the method comprises the steps that tag information is not contained in attribute information of a current first-stage directed graph node or a subset of tag information in attribute information of a first-stage sub-directed graph node of the current first-stage directed graph node is contained in the attribute information of the current first-stage directed graph node, intermediate-stage atom operators in the attribute information of the current first-stage directed graph node are set at least according to first-stage atom operators represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and dependency relationships between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets are determined at least according to dependency relationships between the current first-stage directed graph node and first-stage sub-directed graph nodes.

(22) The method of (19), wherein when the first-level atomic operator represented by the current first-level directed graph node in the traversed first-level directed graph is a misplaced atomic operator,

in the case that the attribute information of the first-stage directed graph node of the current first-stage directed graph node contains tag information corresponding to cells in a spreadsheet, the tag information after dislocation obtained after dislocation of the tag information contained in the attribute information of the first-stage directed graph node is set as the tag information in the attribute information of the current first-stage directed graph node, an 'equivalent' atomic operator is set as an intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined according to the tag information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node.

(23) The method of any one of (18) to (22), further comprising:

sub-directed graphs with each intermediate-level directed graph root node in the set of intermediate-level directed graph root nodes as a root node are extracted from the intermediate-level directed graphs, respectively, and the extracted sub-directed graphs are used as second-level directed graphs to determine a second-level composite operator according to the second-level directed graphs.

(24) The method of (23), wherein the step of extracting comprises:

matching the label information in the attribute information of each intermediate-stage directed graph root node with the label information in the attribute information of each intermediate-stage directed graph left value node, and corresponding the positions of each intermediate-stage directed graph root node in the intermediate-stage directed graph root node set with the positions of each intermediate-stage directed graph left value node in the intermediate-stage directed graph left value node set; and

and extracting the sub-directed graph taking the root node of the intermediate-level directed graph at the corresponding matching position as a root node as a second-level directed graph to determine a second-level compound operator of a cell corresponding to the intermediate-level left value node according to the second-level directed graph.

(25) The method of (12), further comprising:

The generated spreadsheet formula for each of the one or more first cells is stored into a corresponding cell in a spreadsheet file.

(26) The method according to (4), wherein,

the custom atomic operators are defined by combining atomic operators preset by the system.

(27) The method according to (26), wherein,

and under the condition that the self-defined atomic operators are included in the hierarchical formula for the electronic table, converting the self-defined atomic operators into atomic operators preset by a system.

(28) The method of (27), wherein the step of expanding comprises:

replacing the custom atomic operator in the original directed graph of the hierarchical formula with a root node of the directed graph representing the custom atomic operator; and

replacing leaf nodes in the directed graph of the custom atomic operator with child directed graphs in the original directed graph of the hierarchical formula that have the custom atomic operator as a parent node.

(29) The method of (1), further comprising:

updating an intelligent suggestion repository based on the hierarchical formula that has been acquired, the intelligent suggestion repository including information related to the hierarchical formula or hierarchy; and

Based on the intelligent suggestion knowledge base, intelligent suggestions are made when a new hierarchical formula is to be acquired.

(30) The method of (1), further comprising:

automatically detecting defects in the acquired hierarchical formula; and

automatically generating debug information based on the defect and prompting the debug information,

wherein the debug information includes errors or warnings.

(31) The method of (1), further comprising:

storing a spreadsheet model comprising one or more of: a hierarchical structure for a spreadsheet, a hierarchical computational graph for a spreadsheet, and a spreadsheet file.

(32) The method of (31), further comprising:

importing and/or exporting the spreadsheet model.

(33) The method of (3), wherein the parsing the text code to generate the hierarchical formula comprises:

word segmentation is carried out on the text codes to obtain word segmentation streams;

identifying a word segment in the word segment stream that is related to the operator to determine the operator;

identifying, in the stream of tokens, the tokens associated with the one or more nodes in the one or more levels in the hierarchy of the spreadsheet to determine the level reference; and

The hierarchical formula is generated from the determined operators and hierarchical references.

(34) The method of (5), wherein the composite operator is stored using one or more of:

a character string;

a computer-implemented function pointed to by a function pointer; and

directed graph.

(35) The method of (34), wherein each node in the directed graph represents the operator in the hierarchical formula, and directed edges between each node in the directed graph represent a combined relationship between the operators in the hierarchical formula.

(36) The method of (25), wherein the step of storing the generated spreadsheet formula for each of the one or more first cells into the respective first cell further comprises:

automatically naming cell names of a portion or all of the one or more first cells and the one or more second cells in the spreadsheet; and

the spreadsheet formula is rewritten with the automatically named cell name and the rewritten spreadsheet formula is stored.

(37) An apparatus for generating a spreadsheet formula, comprising: means for performing the steps of the method of any one of (1) - (36).

(38) An apparatus for generating a spreadsheet formula, comprising:

at least one processor; and

at least one storage device storing instructions that, when executed by the at least one processor, cause the at least one processor to perform the method of any one of (1) - (36).

(39) A non-transitory computer-readable storage medium storing instructions that when executed by a processor cause the method of any one of (1) - (36) to be performed.

(40) An apparatus for generating a spreadsheet formula, the apparatus comprising:

a hierarchical formula editor configured to obtain a hierarchical formula for the spreadsheet, the hierarchical formula comprising operators and hierarchical references, wherein the hierarchical references correspond to one or more nodes in one or more hierarchies for a spreadsheet, wherein each hierarchy has at least one node and has a respective hierarchy type, each node corresponds to one or more cells in the spreadsheet, wherein the hierarchy further comprises membership between the one or more nodes;

A hierarchical formula parser configured to determine dependencies between the one or more nodes based on correspondence between the hierarchical references and the one or more nodes; the hierarchical formula parser is further configured to determine a computational relationship between the one or more nodes based on the operator and the dependency relationship; and

a formula translator configured to generate the spreadsheet formula based at least on the hierarchy for the spreadsheet and the computational relationship.

(41) The apparatus of (40), wherein the hierarchical formula editor comprises a graphical user interface editor, wherein the graphical user interface editor comprises components for specifying nodes and/or operators of different hierarchies, and wherein the graphical user interface editor is further configured to:

receiving input for components specifying nodes and/or operators of different levels; and

the hierarchical formula is generated based on the input.

(42) The apparatus of (40), wherein the hierarchical formula editor comprises a text editor configured to:

Receiving a text code input by a user; and

the text code is parsed to generate the hierarchical formula.

(43) The apparatus of (40), wherein the operator comprises an atomic operator comprising one or more of: mathematical operators, mathematical functions, constants, system defined functions, and user-defined atomic operators.

(44) The apparatus of (43), wherein the hierarchical formula parser is further configured to:

constructing a composite operator from the atomic operators in the hierarchical formula, wherein the composite operator comprises a combination of all atomic operators in the hierarchical formula.

(45) The apparatus of (44), wherein the hierarchical formula parser is further configured to:

a computational relationship between the one or more nodes is determined based at least on the composite operator and the dependency relationship.

(46) The apparatus of any one of (40) to (45), wherein the hierarchical references comprise a first hierarchical reference to the left of an equal sign in the hierarchical formula and a second hierarchical reference to the right of an equal sign in the hierarchical formula, wherein the first hierarchical reference corresponds to a first node of the one or more nodes and the second hierarchical reference corresponds to a second node of the one or more nodes.

(47) The apparatus of (46), wherein the hierarchical formula parser is further configured to:

a dependency relationship between the first node and the second node is determined based at least on a correspondence between the first hierarchical reference and the first node and a correspondence between the second hierarchical reference and the second node.

(48) The apparatus of any one of (40) to (45), further comprising a hierarchical computational graph memory configured to:

for each of the one or more nodes, a computing relationship between the node and other nodes is stored via a hierarchical computing graph.

(49) The apparatus of (48), wherein the hierarchical formula parser is further configured to:

a computational relationship between the first node and the second node is determined based on the operator and the dependency relationship between the first node and the second node.

(50) The apparatus of (49), further comprising a hierarchical computational graph memory configured to:

for a first node, a computing relationship between the first node and a second node is stored via a hierarchical computation graph.

(51) The apparatus of any one of (40) to (45), wherein the formula translator is further configured to:

For each current node of the one or more nodes, determining a calculated relationship between each first cell of the one or more first cells corresponding to the current node and each second cell of the one or more second cells corresponding to the other node based on the calculated relationship between the current node and the other nodes; and

a spreadsheet formula for each of the one or more first cells is generated based at least on a calculated relationship between each of the one or more first cells and each of the one or more second cells.

(52) The apparatus of (51), wherein the formula translator is further configured to:

determining an operator corresponding to each of one or more first cells corresponding to the current node based at least on the operator corresponding to the current node in the one or more nodes; and

and determining the dependency relationship between each first cell of the one or more first cells corresponding to the current node and each second cell of the one or more second cells corresponding to the other node at least based on the dependency relationship between the current node and the other nodes in the one or more nodes.

(53) The apparatus of (52), wherein the formula translator is further configured to:

a spreadsheet formula for each of the one or more first cells is generated based at least on an operator corresponding to each of the one or more first cells corresponding to the current node and a dependency relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to other nodes.

(54) The apparatus of (53), wherein the formula translator is further configured to:

the operator corresponding to the current node of the one or more nodes is a first level compound operator corresponding to one of the one or more nodes in the hierarchy for the spreadsheet, the first level compound operator comprising one or more first level atomic operators;

the operator corresponding to each of the one or more first cells is a second level compound operator corresponding to one of the one or more cells in the spreadsheet, the second level compound operator comprising one or more second level atomic operators; and

And determining a second-level compound operator corresponding to each of the one or more first cells corresponding to the current node based at least on the first-level compound operator corresponding to the current node in the one or more nodes.

(55) The apparatus of (54), wherein the formula translator is further configured to:

the first-level compound operators are stored by adopting a first-level directed graph, each directed graph node in the first-level directed graph represents each first-level atomic operator in the first-level compound operators, and each directed edge among each directed graph node in the first-level directed graph represents the combination relation among each first-level atomic operator in the first-level compound operators;

the second-level compound operators are stored by using second-level directed graphs, each directed graph node in the second-level directed graph represents each second-level atom operator in the second-level compound operators, and directed edges among each directed graph node in the second-level directed graph represent a combination relation among each second-level atom operator in the second-level compound operators; and

a second level directed graph is determined by traversing a first level directed graph corresponding to the first level composite operator, and the second level composite operator is determined from the second level directed graph.

(56) The apparatus of (55), wherein the formula translator is further configured to:

traversing the first-level directed graph according to the sequence of subsequent traversal, so as to traverse child directed graph nodes in the first-level directed graph first and then traverse parent directed graph nodes in the first-level directed graph.

(57) The apparatus of (56), wherein the formula translator is further configured to:

determining attribute information of one or more first-level directed graph nodes in the first-level directed graph according to first-level atomic operators represented by the first-level directed graph nodes and attribute information of first-level sub-directed graph nodes of the first-level directed graph nodes, wherein the attribute information of each first-level directed graph node comprises one or more of the following: the method comprises the steps of obtaining position information of cells in a spreadsheet, tag information corresponding to the cells in the spreadsheet and position information of the cells in the spreadsheet, tag information corresponding to the cells in the spreadsheet and intermediate level nodes, and intermediate level nodes corresponding to the cells in the spreadsheet, wherein the intermediate level nodes are used for storing intermediate calculation results;

Determining respective intermediate-level directed graph node sets in the intermediate-level directed graph according to attribute information of each of the one or more first-level directed graph nodes in the first-level directed graph, and determining dependency relationships between respective intermediate-level directed graph nodes in each of the intermediate-level directed graph and respective intermediate-level directed graph nodes in other intermediate-level directed graph node sets in the intermediate-level directed graph according to dependency relationships between the one or more first-level directed graph nodes in the first-level directed graph.

(58) The apparatus of (57), wherein the first level atomic operators are divided into one of element-by-element atomic operators, dimension-reduction atomic operators, dislocation atomic operators, and user-defined atomic operators.

(59) The apparatus of (58), wherein the formula translator is further configured to: when the first level atomic operator represented by the current first level directed graph node in the traversed first level directed graph is an element-by-element atomic operator,

if the attribute information of each first-stage directed graph node of the current first-stage directed graph node contains label information corresponding to a cell in the electronic table, setting the label information in the attribute information of the current first-stage directed graph node according to the intersection of the label information contained in the attribute information of each first-stage directed graph node and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and determining a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information of the current first-stage directed graph node;

If the attribute information of each first-stage directed graph node of the current first-stage directed graph node does not contain label information, an intermediate-stage atom operator in the attribute information of the current first-stage directed graph node is set according to a first-stage atom operator represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to the intermediate-stage atom operator in the attribute information of the current first-stage directed graph node, and the dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets in the intermediate-stage directed graph node set is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node;

if the attribute information of a portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node contains tag information corresponding to cells in the electronic table and the attribute information of another portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node does not contain tag information, setting the tag information in the attribute information of the current first-stage directed graph node according to an intersection of the tag information contained in the attribute information of the first-stage sub-directed graph nodes containing tag information, setting intermediate-stage atom operators in the attribute information of the current first-stage directed graph node according to first-stage atom operators represented by the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the tag information and intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and determining respective intermediate-stage directed graph node sets corresponding to the current intermediate-stage directed graph nodes in the current first-stage directed graph node and respective intermediate-stage directed graph nodes according to at least a dependency relationship between the current first-stage directed graph node and its first-stage sub-directed graph node and respective intermediate-stage directed graph node.

(60) The apparatus of (58), wherein the formula translator is further configured to: when the first level atomic operator represented by the current first level directed graph node in the traversed first level directed graph is a dimension reduction atomic operator,

the method comprises the steps that tag information is not contained in attribute information of a current first-stage directed graph node or a subset of tag information in attribute information of a first-stage sub-directed graph node of the current first-stage directed graph node is contained in the attribute information of the current first-stage directed graph node, intermediate-stage atom operators in the attribute information of the current first-stage directed graph node are set at least according to first-stage atom operators represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and dependency relationships between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets are determined at least according to dependency relationships between the current first-stage directed graph node and first-stage sub-directed graph nodes.

(61) The apparatus of (58), wherein the formula translator is further configured to: when the first level atomic operator represented by the current first level directed graph node in the traversed first level directed graph is a dislocation atomic operator,

in the case that the attribute information of the first-stage directed graph node of the current first-stage directed graph node contains tag information corresponding to cells in a spreadsheet, the tag information after dislocation obtained after dislocation of the tag information contained in the attribute information of the first-stage directed graph node is set as the tag information in the attribute information of the current first-stage directed graph node, an 'equivalent' atomic operator is set as an intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined according to the tag information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node.

(62) The apparatus of any one of (57) to (61), wherein the formula translator is further configured to:

sub-directed graphs with each intermediate-level directed graph root node in the set of intermediate-level directed graph root nodes as a root node are extracted from the intermediate-level directed graphs, respectively, and the extracted sub-directed graphs are used as second-level directed graphs to determine a second-level composite operator according to the second-level directed graphs.

(63) The apparatus of (62), wherein the formula translator is further configured to:

matching the label information in the attribute information of each intermediate-stage directed graph root node with the label information in the attribute information of each intermediate-stage directed graph left value node, and corresponding the positions of each intermediate-stage directed graph root node in the intermediate-stage directed graph root node set with the positions of each intermediate-stage directed graph left value node in the intermediate-stage directed graph left value node set; and

and extracting the sub-directed graph taking the root node of the intermediate-level directed graph at the corresponding matching position as a root node as a second-level directed graph to determine a second-level compound operator of a cell corresponding to the intermediate-level left value node according to the second-level directed graph.

(64) The apparatus of (51), further comprising a spreadsheet formula writer configured to:

the generated spreadsheet formula for each of the one or more first cells is stored into a corresponding cell in a spreadsheet file.

(65) The apparatus according to (43), wherein,

the custom atomic operators are defined by combining atomic operators preset by the system.

(66) The apparatus of (65), wherein the formula translator is further configured to:

and under the condition that the self-defined atomic operators are included in the hierarchical formula for the electronic table, converting the self-defined atomic operators into atomic operators preset by a system.

(67) The method of (66), wherein the formula translator is further configured to:

replacing the custom atomic operator in the original directed graph of the hierarchical formula with a root node of the directed graph representing the custom atomic operator; and

replacing leaf nodes in the directed graph of the custom atomic operator with child directed graphs in the original directed graph of the hierarchical formula that have the custom atomic operator as a parent node.

(68) The apparatus of (40), further comprising an intelligent suggestion module configured to:

updating an intelligent suggestion repository based on the hierarchical formula that has been acquired, the intelligent suggestion repository including information related to the hierarchical formula or hierarchy; and

based on the intelligent suggestion knowledge base, intelligent suggestions are made when a new hierarchical formula is to be acquired.

(69) The apparatus of (40), further comprising a defect detection module configured to:

automatically detecting defects in the acquired hierarchical formula; and

automatically generating debug information based on the defect and prompting the debug information,

wherein the debug information includes errors or warnings.

(70) The apparatus according to (40), further comprising:

a spreadsheet model storage module configured to store a spreadsheet model comprising one or more of: a hierarchical structure for a spreadsheet, a hierarchical computational graph for a spreadsheet, and a spreadsheet file.

(71) The apparatus according to (70), further comprising:

A spreadsheet model import and export module configured to import and/or export the spreadsheet model.

(72) The apparatus of (42), wherein the text editor is configured to:

word segmentation is carried out on the text codes to obtain word segmentation streams;

identifying a word segment in the word segment stream that is related to the operator to determine the operator;

identifying, in the stream of tokens, the tokens associated with the one or more nodes in the one or more levels in the hierarchy of the spreadsheet to determine the level reference; and

the hierarchical formula is generated from the determined operators and hierarchical references.

(73) The apparatus of (44), wherein the composite operator is stored using one or more of:

a character string;

a computer-implemented function pointed to by a function pointer; and

directed graph.

(74) The apparatus of (73), wherein each node in the directed graph represents the operator in the hierarchical formula, and directed edges between each node in the directed graph represent a combined relationship between the operators in the hierarchical formula.

(75) The apparatus of (64), wherein the spreadsheet formula writer is configured to:

automatically naming cell names of a portion or all of the one or more first cells and the one or more second cells in the spreadsheet; and

the spreadsheet formula is rewritten with the automatically named cell name and the rewritten spreadsheet formula is stored.

Claims (37)

1. A computer-implemented method for generating a spreadsheet formula, the method comprising:

obtaining a hierarchical formula for the spreadsheet, the hierarchical formula comprising operators and hierarchical references, wherein the hierarchical references correspond to one or more nodes in one or more hierarchies in a hierarchical structure for a spreadsheet, wherein each hierarchy has at least one node and has a respective hierarchy type, each node corresponds to one or more cells in the spreadsheet, wherein the hierarchical structure further comprises membership between the one or more nodes;

determining dependencies between the one or more nodes based on correspondence between the hierarchical references and the one or more nodes;

Determining a computational relationship between the one or more nodes based on the operator and the dependency relationship; and

generating the spreadsheet formula based at least on the hierarchical structure for the spreadsheet and the computational relationship, wherein,

the operator corresponding to the current node of the one or more nodes is a first level compound operator corresponding to one of the one or more nodes for use in the hierarchy of the spreadsheet, the first level compound operator comprising one or more first level atomic operators;

the operator corresponding to each of the one or more first cells corresponding to the current node is a second level compound operator corresponding to one of the one or more cells in the spreadsheet, the second level compound operator comprising one or more second level atomic operators; and

determining a second-level composite operator corresponding to each of one or more first cells corresponding to the current node based at least on the first-level composite operator corresponding to the current node of the one or more nodes, and wherein,

The first-level compound operators are stored by adopting a first-level directed graph, each directed graph node in the first-level directed graph represents each first-level atomic operator in the first-level compound operators, and each directed edge among each directed graph node in the first-level directed graph represents the combination relation among each first-level atomic operator in the first-level compound operators;

the second-level compound operators are stored by using second-level directed graphs, each directed graph node in the second-level directed graph represents each second-level atom operator in the second-level compound operators, and directed edges among each directed graph node in the second-level directed graph represent a combination relation among each second-level atom operator in the second-level compound operators; and

a second level directed graph is determined by traversing a first level directed graph corresponding to the first level composite operator, and the second level composite operator is determined from the second level directed graph.

2. The method of claim 1, wherein the step of obtaining a hierarchical formula for the spreadsheet comprises:

providing a graphical user interface editor, wherein the graphical user interface editor includes components for specifying nodes and/or operators of different levels;

Receiving input for components specifying nodes and/or operators of different levels; and

the hierarchical formula is generated based on the input.

3. The method of claim 1, wherein the step of obtaining a hierarchical formula for the spreadsheet comprises:

receiving a text code input by a user; and

the text code is parsed to generate the hierarchical formula.

4. The method of claim 1, wherein the operator comprises an atomic operator comprising one or more of: mathematical operators, mathematical functions, constants, system defined functions, and user-defined atomic operators.

5. The method of claim 4, further comprising:

constructing a composite operator from the atomic operators in the hierarchical formula, wherein the composite operator comprises a combination of all atomic operators in the hierarchical formula.

6. The method of claim 5, wherein the determining a computational relationship between the one or more nodes based on the operator and the dependency relationship further comprises:

a computational relationship between the one or more nodes is determined based at least on the composite operator and the dependency relationship.

7. The method of any of claims 1-6, wherein the hierarchical references comprise a first hierarchical reference to the left of an equal sign in the hierarchical formula and a second hierarchical reference to the right of an equal sign in the hierarchical formula, wherein a first hierarchical reference corresponds to a first node of the one or more nodes and a second hierarchical reference corresponds to a second node of the one or more nodes.

8. The method of claim 7, wherein the determining the dependencies between the one or more nodes based on the correspondence between the hierarchical references and the one or more nodes comprises:

a dependency relationship between the first node and the second node is determined based at least on a correspondence between the first hierarchical reference and the first node and a correspondence between the second hierarchical reference and the second node.

9. The method of any one of claims 1 to 6, further comprising:

for each of the one or more nodes, a computing relationship between the node and other nodes is stored via a hierarchical computing graph.

10. The method of claim 8, wherein the determining a computational relationship between the one or more nodes based on the operator and the dependency relationship comprises:

A computational relationship between the first node and the second node is determined based on the operator and the dependency relationship between the first node and the second node.

11. The method of claim 10, further comprising:

for a first node, a computing relationship between the first node and a second node is stored via a hierarchical computation graph.

12. The method of any of claims 1-6, wherein the generating the spreadsheet formula based at least on the hierarchical structure and the computational relationship for the spreadsheet comprises:

for each current node of the one or more nodes, determining a calculated relationship between each first cell of the one or more first cells corresponding to the current node and each second cell of the one or more second cells corresponding to the other node based on the calculated relationship between the current node of the one or more nodes and the other nodes; and

a spreadsheet formula for each of the one or more first cells is generated based at least on a calculated relationship between each of the one or more first cells and each of the one or more second cells.

13. The method of claim 12, wherein the determining a calculated relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to the other node comprises:

determining an operator corresponding to each first cell of the one or more first cells corresponding to the current node based at least on the operator corresponding to the current node of the one or more nodes; and

and determining the dependency relationship between each first cell of the one or more first cells corresponding to the current node and each second cell of the one or more second cells corresponding to the other node at least based on the dependency relationship between the current node and the other nodes in the one or more nodes.

14. The method of claim 13, wherein the step of generating a spreadsheet formula for each of the one or more first cells comprises:

generating a spreadsheet formula for each of the one or more first cells based at least on an operator corresponding to each of the one or more first cells corresponding to the current node and a dependency relationship between each of the one or more first cells corresponding to the current node and each of the one or more second cells corresponding to the other nodes.

15. The method of claim 14, wherein the determining a second level directed graph by traversing a first level directed graph corresponding to a first level composite operator comprises:

traversing the first-level directed graph according to the sequence of subsequent traversal, so as to traverse child directed graph nodes in the first-level directed graph first and then traverse parent directed graph nodes in the first-level directed graph.

16. The method of claim 15, further comprising:

determining attribute information of one or more first-level directed graph nodes in the first-level directed graph according to first-level atomic operators represented by the first-level directed graph nodes and attribute information of first-level sub-directed graph nodes of the first-level directed graph nodes, wherein the attribute information of each first-level directed graph node comprises one or more of the following: the method comprises the steps of obtaining position information of cells in a spreadsheet, tag information corresponding to the cells in the spreadsheet and position information of the cells in the spreadsheet, tag information corresponding to the cells in the spreadsheet and intermediate level nodes, and intermediate level nodes corresponding to the cells in the spreadsheet, wherein the intermediate level nodes are used for storing intermediate calculation results;

Determining respective intermediate-level directed graph node sets in the intermediate-level directed graph according to attribute information of each of the one or more first-level directed graph nodes in the first-level directed graph, and determining dependency relationships between respective intermediate-level directed graph nodes in each of the intermediate-level directed graph and respective intermediate-level directed graph nodes in other intermediate-level directed graph node sets in the intermediate-level directed graph according to dependency relationships between the one or more first-level directed graph nodes in the first-level directed graph.

17. The method of claim 16, wherein the first level atomic operators are divided into one of element-by-element atomic operators, dimension-reduction atomic operators, dislocation atomic operators, and user-defined atomic operators.

18. The method of claim 17, wherein when the first level atomic operator represented by the current first level directed graph node in the traversed first level directed graph is an element-by-element atomic operator,

if the attribute information of each first-stage directed graph node of the current first-stage directed graph node contains label information corresponding to a cell in the electronic table, setting the label information in the attribute information of the current first-stage directed graph node according to the intersection of the label information contained in the attribute information of each first-stage directed graph node and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and determining a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the label information of the current first-stage directed graph node;

If the attribute information of each first-stage directed graph node of the current first-stage directed graph node does not contain label information, an intermediate-stage atom operator in the attribute information of the current first-stage directed graph node is set according to a first-stage atom operator represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to the intermediate-stage atom operator in the attribute information of the current first-stage directed graph node, and the dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets in the intermediate-stage directed graph node set is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node;

if the attribute information of a portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node contains tag information corresponding to cells in the electronic table and the attribute information of another portion of the first-stage sub-directed graph nodes of the current first-stage directed graph node does not contain tag information, setting the tag information in the attribute information of the current first-stage directed graph node according to an intersection of the tag information contained in the attribute information of the first-stage sub-directed graph nodes containing tag information, setting intermediate-stage atom operators in the attribute information of the current first-stage directed graph node according to first-stage atom operators represented by the current first-stage directed graph node, determining a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node according to the tag information and intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and determining respective intermediate-stage directed graph node sets corresponding to the current intermediate-stage directed graph nodes in the current first-stage directed graph node and respective intermediate-stage directed graph nodes according to at least a dependency relationship between the current first-stage directed graph node and its first-stage sub-directed graph node and respective intermediate-stage directed graph node.

19. The method of claim 17, wherein when the first level atomic operator represented by the current first level directed graph node in the traversed first level directed graph is a dimension reduction atomic operator,

the method comprises the steps that tag information is not contained in attribute information of a current first-stage directed graph node or a subset of tag information in attribute information of a first-stage sub-directed graph node of the current first-stage directed graph node is contained in the attribute information of the current first-stage directed graph node, intermediate-stage atom operators in the attribute information of the current first-stage directed graph node are set at least according to first-stage atom operators represented by the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined at least according to intermediate-stage atom operators in the attribute information of the current first-stage directed graph node, and dependency relationships between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in other intermediate-stage directed graph node sets are determined at least according to dependency relationships between the current first-stage directed graph node and first-stage sub-directed graph nodes.

20. The method of claim 17, wherein when the first level atomic operator represented by the current first level directed graph node in the traversed first level directed graph is a misplaced atomic operator,

in the case that the attribute information of the first-stage directed graph node of the current first-stage directed graph node contains tag information corresponding to cells in a spreadsheet, the tag information after dislocation obtained after dislocation of the tag information contained in the attribute information of the first-stage directed graph node is set as the tag information in the attribute information of the current first-stage directed graph node, an 'equivalent' atomic operator is set as an intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, a current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node is determined according to the tag information and the intermediate-stage atomic operator in the attribute information of the current first-stage directed graph node, and a dependency relationship between each intermediate-stage directed graph node in the current intermediate-stage directed graph node set corresponding to the current first-stage directed graph node and each intermediate-stage directed graph node in the intermediate-stage directed graph node set is determined at least according to the dependency relationship between the current first-stage directed graph node and the first-stage sub-directed graph node.

21. The method of any of claims 16 to 20, further comprising:

sub-directed graphs with each intermediate-level directed graph root node in the set of intermediate-level directed graph root nodes as a root node are extracted from the intermediate-level directed graphs, respectively, and the extracted sub-directed graphs are used as second-level directed graphs to determine a second-level composite operator according to the second-level directed graphs.

22. The method of claim 21, wherein the step of extracting comprises:

matching the label information in the attribute information of each intermediate-stage directed graph root node with the label information in the attribute information of each intermediate-stage directed graph left value node, and corresponding the positions of each intermediate-stage directed graph root node in the intermediate-stage directed graph root node set with the positions of each intermediate-stage directed graph left value node in the intermediate-stage directed graph left value node set; and

and extracting the sub-directed graph taking the root node of the intermediate-level directed graph at the corresponding matching position as a root node as a second-level directed graph to determine a second-level compound operator of a cell corresponding to the intermediate-level left value node according to the second-level directed graph.

23. The method of claim 12, further comprising:

The generated spreadsheet formula for each of the one or more first cells is stored into a corresponding cell in a spreadsheet file.

24. The method of claim 4, wherein,

the user-defined atomic operators are defined by combining atomic operators preset by the system.

25. The method of claim 24, wherein,

and under the condition that the user-defined atomic operators are included in the hierarchical formula for the electronic table, converting the user-defined atomic operators into atomic operators preset by a system.

26. The method of claim 25, wherein the step of converting comprises:

replacing the user-defined atomic operator in the original directed graph of the hierarchical formula with a root node of the directed graph representing the user-defined atomic operator; and

replacing leaf nodes in the directed graph of the user-defined atomic operator with child directed graphs of the original directed graph of the hierarchical formula having the user-defined atomic operator as a parent node.

27. The method of claim 1, further comprising:

updating an intelligent suggestion repository based on the hierarchical formula that has been acquired, the intelligent suggestion repository including information related to the hierarchical formula or hierarchy; and

Based on the intelligent suggestion knowledge base, intelligent suggestions are made when a new hierarchical formula is to be acquired.

28. The method of claim 1, further comprising:

automatically detecting defects in the acquired hierarchical formula; and

automatically generating debug information based on the defect and prompting the debug information,

wherein the debug information includes errors or warnings.

29. The method of claim 1, further comprising:

storing a spreadsheet model comprising one or more of: a hierarchical structure for a spreadsheet, a hierarchical computational graph for a spreadsheet, and a spreadsheet file.

30. The method of claim 29, further comprising:

importing and/or exporting the spreadsheet model.

31. A method according to claim 3, wherein the step of parsing the text code to generate the hierarchical formula comprises:

word segmentation is carried out on the text codes to obtain word segmentation streams;

identifying a word segment in the word segment stream that is related to the operator to determine the operator;

identifying, in the stream of tokens, the tokens associated with the one or more nodes in the one or more levels in the hierarchy of the spreadsheet to determine the level reference; and

The hierarchical formula is generated from the determined operators and hierarchical references.

32. The method of claim 5, wherein the composite operator is stored using one or more of:

a character string;

a computer-implemented function pointed to by a function pointer; and

directed graph.

33. The method of claim 32, wherein each node in the directed graph represents the operator in the hierarchical formula, and directed edges between each node in the directed graph represent a combined relationship between the operators in the hierarchical formula.

34. The method of claim 23, wherein the step of storing the generated spreadsheet formula for each of the one or more first cells into the respective cell further comprises:

automatically naming cell names of a portion or all of the one or more first cells and the one or more second cells in the spreadsheet; and

the spreadsheet formula is rewritten with the automatically named cell name and the rewritten spreadsheet formula is stored.

35. An apparatus for generating a spreadsheet formula, comprising: means for performing the steps of the method of any one of claims 1-34.

36. An apparatus for generating a spreadsheet formula, comprising:

at least one processor; and

at least one storage device storing instructions that, when executed by the at least one processor, cause the at least one processor to perform the method of any of claims 1-34.

37. A non-transitory computer readable storage medium storing instructions which, when executed by a processor, cause performance of the method recited in any one of claims 1-34.

Images