Tree spreadsheet generation method, spreadsheet, system and readable-writable medium

Abstract

The invention discloses a tree-shaped spreadsheet generation method, a spreadsheet, a system and a readable-writable medium, and the method comprises the steps: creating a form which is a grid containing a formula; creating a tree-shaped relationship, organizing the schema through the tree-shaped relationship to form a schema tree, and the schema tree being a tree-shaped spreadsheet; and creating one or more caches to memorize the calculation result of the formula of the formula. The method is used for solving the problem that an existing matrix type table cannot represent recursion frequently occurring in data.

Description

Technical Field

[0001] This application relates to the field of application software design for computer-aided knowledge work, and particularly to a method for generating a tree-shaped spreadsheet, a spreadsheet, a system, and a readable and writable medium. Background Art

[0002] A spreadsheet is a widely popular software that organizes formulas based on the row-column relationship inherent in a matrix structure. When users use a spreadsheet, they mainly perform operations such as moving, inserting, and deleting formulas in the table in the horizontal and vertical directions. On the other hand, the automation level of text documents has always been low, and it is difficult to add formula calculations. For the automated writing of text documents, machine learning methods that can generate natural language are mainly used at present.

[0003] Spreadsheets are often used to model data. There is almost always a functional correspondence between various parts of the data, which is exactly the problem that spreadsheets introduce formulas to solve. However, usually, a matrix-form table cannot represent the recursiveness that often appears inside the data. Considering that the tree relationship is a structure often used to organize data with recursive characteristics. Therefore, it is necessary to invent a tree-shaped spreadsheet. Therefore, it is necessary to first invent a tree-shaped spreadsheet as an observable and manipulable aspect of this type of automation problem. Summary of the Invention

[0004] This application proposes a method for generating a tree-shaped spreadsheet, a spreadsheet, a system, and a readable and writable medium, which solves the problem that the existing matrix-type table cannot represent the recursiveness that often appears inside the data.

[0005] In a first aspect, an embodiment of this application provides a method for generating a tree-shaped spreadsheet, including:

[0006] Create formula cells, where the formula cells are cells containing formulas;

[0007] Create a tree relationship, and organize the formula cells through the tree relationship to form a formula cell tree, where the formula cell tree is a tree-shaped spreadsheet;

[0008] Create one or more caches to memo the calculation results of the formulas of the formula cells.

[0009] In one embodiment, each formula cell is configured with a globally unique identifier, and the corresponding formula cell can be directly indexed through the globally unique identifier;

[0010] Each formula cell has a formula and an address, and the address corresponds to the position of the formula cell in the entire tree-shaped spreadsheet.

[0011] In one embodiment, the tree relationship contains or corresponds to a root node, and the organizational relationship between each node and its child nodes is also a tree relationship, forming a subtree with this node as the root node. The subtree is a sub-formula container tree.

[0012] In one embodiment, a structured value obtained by organizing the calculation results of the formulas of each formula container according to the tree relationship of the sub-formula container tree is called a subtree value, which is used to maintain the recursion between the calculation results of the formulas of each formula container during the calculation process.

[0013] In one embodiment, the root node of the sub-formula container tree is configured with a subtree address for referencing the subtree value, and the subtree address corresponds one-to-one with the position of the root node of the sub-formula container tree in the tree-shaped spreadsheet.

[0014] In one embodiment, the cache is also used to memorize the correspondence between the unique identifier of the formula container and the latest address of the formula container;

[0015] The cache is also used to memorize the latest values of the formulas of each formula container;

[0016] The cache is also used to memorize each sub-formula container tree and the latest values of each sub-formula container tree.

[0017] In one embodiment, the subtree address of the formula container is different from the address of the formula container itself.

[0018] In a second aspect, an embodiment of the present application provides a tree-shaped spreadsheet, including:

[0019] Multiple formula containers, where the formula containers are cells containing formulas;

[0020] A tree relationship for organizing the formula containers to form a formula container tree, and the formula container tree is a tree-shaped spreadsheet;

[0021] One or more caches for memorizing the calculation results of the formulas of the formula containers.

[0022] In a third aspect, an embodiment of the present application provides a system for assisting a user in processing information on a spreadsheet, including:

[0023] A graphical user interface;

[0024] A processor for executing the program of the method as described above;

[0025] A data memory for storing the program of the method as described above and related data during execution, so that the system can execute the program of the method as described above when there is a processor.

[0026] In a third aspect, an embodiment of the present application provides a non-transitory computer-readable and writable medium for storing the programs and related data of the above method in a long term. When the programs and data in the storage medium are read by the processor of the above system, any steps of the above method can be executed.

[0027] The above at least one technical solution adopted in the embodiments of the present application can achieve the following beneficial effects:

[0028] It solves the problem that the existing matrix-type table cannot represent the recursive nature that often appears inside the data,

[0029] and improves the automation level of text-type documents. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation to the present application. In the drawings:

[0031] Figure 1 It shows a system for assisting a user in processing information on a spreadsheet.

[0032] Figure 2 It shows a schematic diagram of the internal structure of the content of the tree-shaped spreadsheet of the present application.

[0033] Figure 3 It shows a flowchart (I) of the method for generating the tree-shaped spreadsheet of the present application.

[0034] Figure 4 It shows a flowchart (II) of the method for generating the tree-shaped spreadsheet of the present application. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0035] To make the objectives, technical solutions, and advantages of the present application clearer, the technical solutions of the present application will be clearly and completely described below in conjunction with the specific embodiments of the present application and the corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments in the present application without creative efforts shall fall within the protection scope of the present application.

[0036] The following will describe in detail the technical solutions provided by each embodiment of the present application in conjunction with the drawings.

[0037] In the prior art, spreadsheets are often used to model data, and there is almost always a functional correspondence between various parts of the data, which is exactly the problem that the introduction of formulas in spreadsheets aims to solve. However, usually, tabular forms in matrix form cannot represent the recursiveness that often appears within the data. From the perspective of mind maps, although mind maps can well segment, carry, and summarize some thoughts expressed through language, usually, they don't know how to incorporate the mapping relationships expressed by functional formulas. The difficulty lies in the method of referencing data with recursive structures contained therein. Therefore, it is necessary to propose the present invention.

[0038] The present invention provides a method for generating a tree-shaped spreadsheet, including:

[0039] Creating formula cells, where the formula cells are cells containing formulas.

[0040] Creating a tree-shaped relationship, and organizing the formula cells through the tree-shaped relationship to form a formula cell tree, where the formula cell tree is a tree-shaped spreadsheet;

[0041] Creating one or more caches to memorize the calculation results of the formulas of the formula cells.

[0042] The tree-shaped spreadsheet is still a spreadsheet. For the convenience of description, a cell containing a formula is defined as a "formula cell", and the tree-shaped spreadsheet is a set of formula cells organized in a tree-shaped relationship. In this application, the tree-shaped spreadsheet is also referred to as a "formula cell tree".

[0043] Figure 2 Give a schematic diagram of the internal structure of a formula cell. Each formula cell will be assigned a globally unique identifier (GUID) when created, and the corresponding formula cell can be directly indexed using this identifier. Generally, users do not need to use this globally unique identifier. Users mainly use addresses for easy reading and writing.

[0044] For the convenience of description, this application adopts a set of common terms related to the formula cell tree:

[0045] Given a formula cell, its absolute path refers to the unique path from its location to the root of the tree;

[0046] The formula cell closer to the root of the tree on the absolute path is the parent of the farther formula cell, and the reverse relationship is the child;

[0047] Two adjacent formula cells are in a parent-child relationship with each other;

[0048] Formula cells with the same parent formula cell are siblings to each other;

[0049] For each formula cell, we also assign it a sibling serial number, indicating its serial number among all sibling formula cells for easy searching.

[0050] In this application, each formula container is configured with a globally unique identifier, and the corresponding formula container can be directly indexed through the globally unique identifier. Each formula container has a formula and an address, and the address corresponds to the position of the formula container in the entire tree-shaped spreadsheet. Each formula of the formula container has a value, that is, the calculation result of the formula. Assigning an address to each formula container is used to reference the value of the formula, and the address of the formula container is in one-to-one correspondence with the absolute path of the formula container. When assigning an address to the formula container, this one-to-one correspondence relationship must be ensured.

[0051] Next, it is necessary to solve the representation of the recursiveness unique to the tree structure, that is, each branch (called a subtree) of the formula container tree is a formula container tree. For this reason, a subtree address is assigned to each formula container, allowing the user to reference the value of the subtree rooted at this formula container. Each subtree is in one-to-one correspondence with the absolute path of its root position. When assigning a subtree address to the formula container, this one-to-one correspondence relationship must be ensured. Of course, the subtree address of the formula container must also be different from the address of the formula container itself to avoid ambiguity when the user uses it.

[0052] The tree-shaped relationship of this application contains or corresponds to a root node, and the organizational relationship between each node and its child nodes is also a tree-shaped relationship, forming a subtree with this node as the root node, and the subtree is a sub-formula container tree.

[0053] A structured value obtained by organizing the calculation results of the formulas of each formula container on the sub-formula container tree according to the tree-shaped relationship is called a subtree value, which is used to maintain the recursiveness between the calculation results of the formulas of each formula container during the calculation process.

[0054] In one embodiment, the root node of the sub-formula container tree is configured with a subtree address for referencing the subtree value, and the subtree address is in one-to-one correspondence with the position of the root node of the sub-formula container tree in the tree-shaped spreadsheet.

[0055] In one embodiment, the cache is also used to memorize the correspondence between the unique identifier of the formula container and the latest address of the formula container;

[0056] The cache is also used to memorize the latest values of the formulas of each formula container;

[0057] The cache is also used to memorize each sub-formula container tree and the latest values of each sub-formula container tree.

[0058] In this application, for the values of the formulas of the formula container and the subtree values, the system will maintain a cache in the memory to memorize and track their latest calculation results, that is, the system will keep updating one or two indexed calculation result caches, which can be implemented by the following two methods:

[0059] For the first method, since the formula container's own address and the subtree address are different, an indexed cache can be maintained for both the formula value and the subtree value. The key names of the indexes should have a one-to-one correspondence with the formula containers, and usually, the globally unique identifier of the formula container is used for indexing. Since the user needs to reference the address or subtree address when writing a formula, it also needs to be translated to the index key. Usually, the translation method requires the system to maintain a mapping table from the formula container address to the index key (the globally unique identifier of the formula container) in memory, which is called the address mapping table. When the user uses the address or subtree address, the system will use the address mapping table to find the corresponding index key, and then find the latest calculation result corresponding to the index key in the cache.

[0060] For the second method, a globally unique identifier is also separately assigned to each subtree. In this way, only one indexed cache needs to be maintained, combining the formula value and the subtree value, and using their respective globally unique identifiers as keys. Compared with the first method, the step of distinguishing the formula address and the subtree address can be omitted when translating the address, but the step of assigning a globally unique identifier to each subtree is added, which may slightly increase the memory overhead and running time. The remaining implementation methods are similar and will not be repeated here.

[0061] In this application, when the formula of a formula container is modified, its formula value and the formula values of all formula containers that depend on it will be regularly updated. In addition, it will involve the subtree value caches of all its parent and elder ancestors, as well as the subtree value caches of all parent and elder ancestors of all formula containers that depend on it, and all these subtree value caches need to be updated.

[0062] In this application, when a formula container is selected and the tree relationship at that place is modified, the absolute paths of the formula container and the formula containers on its subtree may change. At this time, the address mapping table needs to be modified accordingly to keep consistent with the new structure; at the same time, it will also involve the subtree values of the old and new parent and elder ancestors, and the caches of these subtree values need to be updated. When updating, it is usually not necessary to recalculate each formula value, but mainly to update the connection relationships between the formula values in the subtree value structure, which is actually a typical update operation for the tree relationship in dynamic programming.

[0063] This application is illustrated by the following specific embodiments:

[0064] First, taking Figure 3 as an example, with 301 as the root of the tree, according to the following steps, the formula containers 302 - 307 are added to the tree in sequence. After the user selects the root 301 and requests to add a sub-node (such as through a button); after receiving the instruction, the system creates the formula container 302 (including creating its unique identifier and initializing its formula to an empty expression). The formula containers 303 - 308 can be created in a similar way. Then, the formulas are filled in for each formula container respectively, as shown in Table 1 and Figure 3 shown.

[0065] For convenience of explanation, the unique identifiers assigned to the formula contents are guid302, guid303, …, guid308 respectively. To facilitate users' reading and writing, it is necessary to design the address format of the nodes to serve as the node names. Different nodes on the same tree should have different addresses, and the address format should be convenient for reading and writing. The address format is designed as a natural number string prefixed with an underscore (_) (in the form of _2_1), where each natural number corresponds to the sibling number of a parent formula content of the formula content. Thus, Figure 3 The addresses of formula contents 302 - 307 are as follows:

[0066] 302: _1. Except for the root node, it has no parent formula content and it has a sibling formula content with the address _2.

[0067] 303: _1_1. Except for the root node, it has only one parent formula content with the address _1 and it has no sibling formula content.

[0068] 304: _1_1_1. Except for the root node, it has two parent formula contents with the addresses _1_1 and _1 respectively, and it has a sibling formula content with the address _1_1_2.

[0069] 305: _1_1_2. Except for the root node, it has two parent formula contents with the addresses _1_1 and _1 respectively, and it has a sibling formula content with the address _1_1_1.

[0070] 306: _2. Except for the root node, it has no parent formula content and it has a sibling formula content with the address _1.

[0071] 307: _2_1. Except for the root node, it has only one parent formula content with the address _2 and it also has a sibling formula content with the address _2_2.

[0072] 308: _2_2. Except for the root node, it has only one parent formula content with the address _2 and it also has a sibling formula content with the address

[0073] _2_1.

[0074] Table 1

[0075]

[0076] Next, it is necessary to design the address format of the subtrees of the formula contents.

[0077] The address format of the subtrees is designed as the formula content address plus an underscore suffix (in the form of _2_1_). Thus, Figure 3 The subtree addresses at formula contents 302 - 308 are respectively:

[0078] The subtree at 302: _1_. The subtree value of 302 contains the formula value and subtree value of 303.

[0079] The subtree at 303: _1_1_, and the subtree value at 303 contains the formula values of 304 and 305.

[0080] The subtree at 304: _1_1_1_, 304 is a leaf node, and its subtree value is a null value or a zero value.

[0081] The subtree at 305: _1_1_2_, 305 is a leaf node, and its subtree value is a null value or a zero value.

[0082] The subtree at 306: _2_, and the subtree value at 306 contains the formula values of 307 and 308.

[0083] The subtree at 307: _2_1_, 307 is a leaf node, and its subtree value is a null value or a zero value.

[0084] The subtree at 308: _2_2_, 308 is a leaf node, and its subtree value is a null value or a zero value.

[0085] To simply illustrate recursion, we use the join function to operate on the subtree. The join function takes a subtree value and then returns the result of concatenating all the formula values on the first layer of this subtree value. For example, Figure 3 the formula for the content at 303 = join(_1_1_). Here, _1_1_ represents the value of the subtree with the root at _1_1. As described above, this value currently equals ["blue", "sky"]. After passing it into the function join, join will concatenate the two words to get "blue sky". Similarly, the formula value at 306 equals "floating white clouds".

[0086] The formula for the content at 302 simply references the formula value of 303 through the address of 303. That is, currently the formula value of 302 also equals "blue sky".

[0087] Meanwhile, the system allocates 3 caches in memory for formula value caching, subtree value caching, and address mapping, and initializes these 3 data structures according to the above method. At this time, the user selects 306 and requests to move its connected subtree into the subtree of 302, making 306 become the sibling content of 303. Reflected in the change of the tree relationship, this is to first delete an edge 309 ( Figure 3 ), and then add an edge 309b ( Figure 4 ). Then, the formula for the content 302 is also changed to the formula shown as 302b.

[0088] Table 2 - Changes in the content formula before and after

[0089] Unique identifier Previous formula Subsequent formula guid302 ＝_1_1 = join(_1_) guid303 = join(_1_1_) = join(_1_1_) guid304 "blue" "blue" guid305 "sky" "sky" guid306 = join(_2_) = join(_1_2_) guid307 "floating" "floating" guid308 "white clouds" "white clouds"

[0090] Table 3 - Changes in the address mapping before and after

[0091]

[0092]

[0093] Table 4 - State Changes of Formula Value Cache Before and After

[0094] Unique identifier Previous formula value Subsequent formula value guid302 "blue sky" "White clouds floating in the blue sky" guid303 "blue sky" "blue sky" guid304 "blue" "blue" guid305 "sky" "sky" guid306 "floating white clouds" "floating white clouds" guid307 "floating" "floating" guid308 "white clouds" "white clouds"

[0095] Table 5 - State Changes of Subtree Value Cache Before and After

[0096]

[0097] Next, if the user references _1_, the system will first literally map _1_ to address _1_. Note that, thanks to the addresses being designed in a naturally corresponding format, this step is extremely efficient and takes almost no time. Then the system finds the corresponding formula content identifier guid302 in the latest state of the address mapping, and finally the system finds the corresponding subtree value in the latest state of the subtree value cache.

[0098] Of course, it is also possible to assign a separate GUID to each subtree address instead of using the GUID corresponding to the formula content. In this way, since the GUIDs of the subtree values and formula values are different from each other, the formula value cache and the subtree value cache can be merged into a single cache. The rest of the implementation is similar and will not be elaborated here. It should be noted that doing so may slightly increase the memory overhead due to the extra GUIDs, and may also slightly increase the running time due to the increased number of keys in the merged cache.

[0099] The above description is only for the embodiments of the present application and is not intended to limit the present application. For those skilled in the art, various changes and modifications can be made to the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included within the scope of the claims of the present application.

Claims

1. A method for generating a tree-shaped electronic table, characterized in that: include: Create a formula container, which is a grid containing a formula. Creating a tree relationship, organizing the formula contents through the tree relationship to form a formula content tree, wherein the formula content tree is a tree-shaped electronic table; Create one or more caches to record the calculation results of the formula in the formula container.

2. The method for generating a tree-shaped electronic table according to claim 1, wherein: Each container is configured with a globally unique identifier, and the corresponding container is directly indexed by the globally unique identifier; Each formula has a formula and an address, and the address corresponds to the position of the formula in the entire tree-shaped electronic form.

3. The method for generating a tree-shaped electronic table according to claim 2, wherein: The tree relationship contains or corresponds to a root node, and the organizational relationship between each node and its child nodes is also a tree relationship, forming a subtree with the node as the root node, and the subtree is a sub-type capacity tree.

4. The method for generating a tree-shaped electronic table as claimed in claim 3, characterized in that: A structured value obtained by organizing the calculation results of the formulas of various formulas on the subtree in the tree relationship of the sub-formula tree is called a subtree value, which is used to maintain the recursion between the calculation results of the formulas of various formulas during the calculation process.

5. The tree-shaped electronic form as claimed in claim 4, characterized in that: The root node of the sub-formula tree is configured with a sub-tree address for referencing the sub-tree value, and the sub-tree address corresponds to the position of the root node of the sub-formula tree in the tree-shaped electronic table.

6. The method for generating a tree-shaped electronic table according to claim 5, characterized in that: The cache is also used to memorize the correspondence between the unique identifier of the format container and the latest address of the format container; The cache is also used to memorize the latest values ​​of formulas of various contents; The cache is also used to memorize each sub-expression tree and the latest value of each sub-expression tree.

7. The method for generating a tree-shaped electronic table according to claim 6, wherein: The subtree address of the container is different from the address of the container itself.

8. A tree-shaped electronic spreadsheet, characterized in that: include: A plurality of formula containers, each of which is a grid containing a formula; A tree relationship, used to organize the formulas to form a formula tree, wherein the formula tree is a tree-shaped electronic table; One or more buffers for memorizing the calculation results of the formula in the formula container.

9. A system for assisting a user in processing information on an electronic form, characterized in that: include: a graphical user interface; A processor for executing a program of the method according to claims 1 to 7; A data memory for storing the method program and related data of claims 1-7 during execution, so that the system can execute the method program of claims 1-7 when there is a processor.

10. A non-transitory computer readable and writable medium, characterized in that: Used to store the program and related data of the above method for a long time, so that when the program and data in the storage medium are read by the processor of the system described in claim 9, any step of the method described in any one of claims 1 to 7 can be executed.

Images