CN110728124A - Method, apparatus, device and storage medium for visualizing electronic forms - Google Patents

Abstract

The present disclosure relates to a method, apparatus, device, and storage medium for visualizing spreadsheets. One embodiment relates to a computer-implemented method for visualizing a spreadsheet, the method comprising: obtaining a hierarchy of a spreadsheet, wherein the hierarchy comprises one or more levels having a plurality of nodes, wherein each level has at least one node and has a corresponding level type, each node corresponding to one or more cells in the spreadsheet, wherein the hierarchy further comprises membership between the plurality of nodes; extracting metadata for each of the plurality of nodes; establishing a dependency relationship between the plurality of nodes based at least on the calculated relationships between the plurality of nodes in the hierarchy; and generating a visual representation of the hierarchy of the spreadsheet based on the hierarchical structure of the spreadsheet, the metadata, and the dependencies between the plurality of nodes.

Description

Method, apparatus, device and storage medium for visualizing electronic forms

Technical Field

The present disclosure relates to a method, apparatus, device, and storage medium for visualizing spreadsheets.

Background

In many industry areas, it is often desirable to build complex spreadsheet models. A spreadsheet model is a spreadsheet that may include values, text, formulas, etc. to calculate the value of a variable or variables. For example, during a financial analysis, a financial analyst typically needs to build a spreadsheet model for finance to calculate the intrinsic value of a financial asset. Spreadsheet models for finance typically include tens of pages, thousands of rows, tens of thousands of cells, with complex inter-computational relationships between cells, where a vast majority of cells may be computed from other cells. Cells in a spreadsheet may store text, time, numbers, or formulas, among others. If a formula is stored in a cell, the spreadsheet typically displays the calculation of the formula by default, either hidden or in an additional area, which requires further action by the user to trace the cell referenced by the formula. The complexity of the spreadsheet model and the non-explicit computational relationships between cells make the spreadsheet model very error-prone, difficult to debug, and difficult to understand or modify by others than the spreadsheet model builder. Accordingly, there is a need for an improved method, apparatus, device, and storage medium for visualizing spreadsheets.

Disclosure of Invention

One embodiment of the present disclosure is directed to a computer-implemented method for visualizing a spreadsheet, the method comprising: obtaining a hierarchy of a spreadsheet, wherein the hierarchy comprises one or more levels having a plurality of nodes, wherein each level has at least one node and has a corresponding level type, each node corresponding to one or more cells in the spreadsheet, wherein the hierarchy further comprises membership between the plurality of nodes; extracting metadata for each of the plurality of nodes; establishing a dependency relationship between the plurality of nodes based at least on the calculated relationships between the plurality of nodes in the hierarchy; and generating a visual representation of the hierarchy of the spreadsheet based on the hierarchical structure of the spreadsheet, the metadata, and the dependencies between the plurality of nodes.

Another embodiment of the present disclosure is directed to an apparatus for visualizing spreadsheets, the apparatus comprising: at least one processor; and at least one storage device storing instructions that, when executed by at least one processor, cause the at least one processor to perform the above-described method.

Yet another embodiment of the present disclosure is directed to a non-transitory computer-readable storage medium having stored thereon instructions that, when executed by a processor, cause the above-described method to be performed.

Yet another embodiment of the present disclosure is directed to an apparatus for visualizing spreadsheets including means for performing the steps of the above-described method.

Other features and advantages of the present disclosure will become apparent from the following description with reference to the accompanying drawings.

Drawings

The accompanying drawings, which are incorporated in and constitute a part of the specification, illustrate embodiments of the disclosure and together with the description, serve to explain, without limitation, the principles of the disclosure. In the drawings, like numbering is used to indicate like items.

Fig. 1 is a block diagram of an exemplary apparatus for visualizing a spreadsheet according to some embodiments of the present disclosure.

Fig. 2 is a flow diagram illustrating an exemplary method for visualizing a spreadsheet according to some embodiments of the present disclosure.

FIG. 3 is a schematic diagram illustrating an exemplary spreadsheet according to some embodiments of the present disclosure.

Fig. 4A and 4B are schematic diagrams illustrating an exemplary spreadsheet hierarchy and dependencies between nodes according to some embodiments of the present disclosure.

Fig. 5 is a flow diagram illustrating an exemplary method for establishing dependencies between nodes, according to some embodiments of the present disclosure.

Fig. 6 is a schematic diagram illustrating nodes in an exemplary spreadsheet hierarchy according to some embodiments of the present disclosure.

7A-7C illustrate visual representations of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure.

8A-8C illustrate visual representations of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure.

FIG. 9 illustrates a visual representation of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure.

FIG. 10 illustrates a visual representation of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure.

11A-11B illustrate visual representations of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure.

12A-12C illustrate a visual representation of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure.

Fig. 13 is a schematic diagram illustrating additional information in a visual representation of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure.

FIG. 14 is a flow diagram illustrating an exemplary method for modifying a spreadsheet through a visual representation according to some embodiments of the present disclosure.

FIG. 15 illustrates a general hardware environment in which the present disclosure may be applied, according to some embodiments of the present disclosure.

Detailed Description

In the following detailed description, numerous specific details are set forth in order to provide a thorough understanding of the described exemplary embodiments. It will be apparent, however, to one skilled in the art, that the described embodiments may be practiced without some or all of these specific details. In the described exemplary embodiments, well-known structures or processing steps have not been described in detail in order to avoid unnecessarily obscuring the concepts of the present disclosure.

The blocks within each block diagram shown below may be implemented by hardware, software, firmware, or any combination thereof to implement the principles of the present disclosure. It will be appreciated by those skilled in the art that the blocks described in each block diagram can be combined or divided into sub-blocks to implement the principles of the disclosure.

The steps of the methods presented in this disclosure are intended to be illustrative. In some embodiments, the method may be accomplished with one or more additional steps not described and/or without one or more of the steps discussed. Further, the order in which the steps of the method are shown and described is not intended to be limiting.

As described above, the complexity of conventional spreadsheet models and the non-explicit computational relationships between cells make spreadsheet models very error-prone, difficult to debug, and difficult to understand or modify. Specifically, existing common spreadsheet editing software (such as Microsoft Excel) includes some visualization tools, but these visualization tools are generally only used for visualizing numerical values, such as generating charts like bar charts, pie charts, and the like. Some independent visualization software (such as Data Visualizer in Microsoft Visio, Analytica in lumine precision Systems) can generate a flow chart from a spreadsheet file with specific fields and associate nodes in the flow chart with Data in the spreadsheet, but cannot visualize the computational relationships between cells in the spreadsheet well. The DPL developed by the synchronization software includes an influence graph function, and can be used to visualize calculation relationships in a spreadsheet. However, if a spreadsheet contains more than 100 formulas, the influence graph may contain too much information, resulting in an influence graph that becomes very complex and from which it is difficult for a user to find the desired information, and thus the influence graph may be difficult for the user to understand and debug the complex spreadsheet model.

In addition, spreadsheet models typically store a large amount of textual and formatting information in addition to numeric and formula information. Most of the existing spreadsheet visualization methods only visualize numerical information or calculation relationship information, but do not display semantic information and hierarchical structure information in spreadsheets. This is disadvantageous to the user's understanding and debugging of the spreadsheet.

Embodiments of the present application may divide a spreadsheet into nodes of different hierarchies according to text information and format information in the spreadsheet and the locations of cells, and may identify semantic information of cells storing numerical and formula information, thereby generating a visual representation of the spreadsheet having a hierarchical structure and semantic information, such visual representation also referred to herein as a visual representation of a hierarchy (hierarchical visual representation).

Apparatus and methods for visualizing spreadsheets in accordance with some embodiments of the present disclosure are described below in conjunction with fig. 1-2.

Fig. 1 is a block diagram of an exemplary apparatus 100 for visualizing a spreadsheet according to some embodiments of the present disclosure. As shown in FIG. 1, apparatus 100 can include a hierarchy obtaining component 110, a metadata extraction component 120, a dependency establishment component 130, and a visual representation generation component 140. Although fig. 1 illustrates the above 4 units, this is only a schematic structure of the apparatus for visualizing a spreadsheet, and the apparatus 100 for visualizing a spreadsheet may also include other possible units, or may include a combination unit of any of the above four units.

The hierarchy obtaining component 110 can be configured to obtain a hierarchy of spreadsheets. According to one embodiment, a hierarchy of a spreadsheet may include one or more levels having a plurality of nodes, where each level has at least one node and has a corresponding level type (e.g., workbook, page, section, table, block, row, column, section, cell, etc.), each node corresponding to one or more cells in the spreadsheet, where the hierarchy also includes membership between the plurality of nodes (e.g., a table node may have several hierarchy type row children, that is, the hierarchy type row children are affiliated with the hierarchy type table node). According to one embodiment, node a is a child of, or is affiliated with, node B if and only if the cell or cells corresponding to node a are contained within the cell or cells corresponding to node B.

According to one embodiment, obtaining the hierarchical structure of the spreadsheet may include one or more of: identifying one or more of location, content, and format information (e.g., font size, whether bolded, background color, etc.) of cells in the spreadsheet, and identifying a hierarchical structure of the spreadsheet based at least on the one or more of location, content, and format information of the cells; and receiving a hierarchy specified by the user.

According to one embodiment, the hierarchy type of the one or more levels of the spreadsheet comprises one or more of: workbook, page, section, table, block, row, column, section, and cell. In one embodiment, obtaining the hierarchical structure of the spreadsheet may include one or more of: detecting a title in each page of the workbook, determining a level of the title based on one or more of location, content, and format information of the title, and dividing each page into sections or tables of corresponding levels based on the level of the title; determining a rank of the rows and columns based on one or more of position, content, and format information of the rows and columns of each table; dividing the rows and columns into blocks based on one or more of a correlation between rows, a correlation between columns, a level of rows, and a level of columns in each table; and dividing the cells into bins based on the correlation between the cells in each row and column.

The metadata extraction component 120 may be configured to extract metadata for each node of the plurality of nodes in the hierarchy of spreadsheets. According to one embodiment, the metadata for each node may include semantic information describing the specific meaning of the node. According to one embodiment, the semantic information may include one or more of a name, a title, a label, and an annotation. For example, semantic information for a workbook may be the name of the workbook and/or the text describing the entire workbook, semantic information for a page may be the name of the page and/or the text describing the entire page, semantic information for a section may be the title of the section, semantic information for a table may be the name of the table, semantic information for a row or column may be the label of the row or column, semantic information for a block may be the text label for all rows or columns of the block, and semantic information for a cell may be the row label of the cell and the column label of the cell. The semantic information for workbooks, pages, sections, tables, blocks, rows, columns, sections, and cells may include annotations, which are descriptive words for the nodes of these levels. According to one embodiment, when the node is displayed in the visual representation of the hierarchy of the spreadsheet, semantic information for the node is also displayed in the visual representation, thereby facilitating the user's understanding of the structure and content of the spreadsheet.

According to one embodiment, the metadata for each node may include an operator in the computational formula for that node, e.g., plus "+" or multiply "×", or a combination of multiple operators, etc. According to one embodiment, semantic information for each of a plurality of nodes in a hierarchy of spreadsheets may be extracted or generated from textual content of one or more cells corresponding to the node, or may be user-defined.

According to one embodiment, the metadata of each node may further include one or more of the following additional information: the generation time of the one or more cells corresponding to the node, the number of child nodes of the node, the numerical value in the one or more cells corresponding to the node, the unit of the numerical value, the point in time or time period corresponding to the numerical value, the format of the one or more cells corresponding to the node, the location of the one or more cells corresponding to the node in the spreadsheet, and the path from the root node in the hierarchy to the node.

The dependency establishment component 130 can be configured to establish dependencies between a plurality of nodes in a hierarchy based at least on computational relationships between the plurality of nodes. It should be noted that each node in the hierarchy may have a dependency relationship with other nodes. For any two cells A and B in the spreadsheet, defining cell A depends on cell B and only if cell A is computed from at least cell B (or from cell B along with other cells). For nodes (e.g., row nodes) C and D in the hierarchy, it is defined that node C depends on node D if and only if at least one cell that node C contains depends, directly or indirectly, on at least one cell that node D contains. In one embodiment, establishing dependencies between a plurality of nodes in a hierarchy may also be based on membership between the plurality of nodes. In some embodiments, establishing the dependencies between the nodes in the hierarchy may not require knowledge of membership between the nodes, e.g., for the case where the computational relationships between all nodes can be obtained directly (e.g., based on user input), the dependencies between the nodes can be obtained directly through the computational relationships between the nodes without further derivation based on membership between the nodes. The method of establishing dependencies between nodes is described further below.

In one embodiment, the hierarchy type of the hierarchy in which the node is located, the child nodes of the node, and the metadata of the node may be stored for each node. In one embodiment, the nodes on which the node depends may also be stored for each node. This stored information may be subsequently used to generate or update a visual representation of the spreadsheet.

The visual representation generation component 140 can be configured to generate a visual representation of a hierarchy of the spreadsheet based on the hierarchical structure of the spreadsheet, the metadata, and the dependencies between the plurality of nodes. As will be described further below, the visual representation of the hierarchy of the spreadsheet may have a variety of forms and may be updated or modified in response to user operations.

In one embodiment, a visual representation of a hierarchy of a spreadsheet may be expanded in stages to facilitate a user to intuitively understand the structure and logical relationships of the spreadsheet. In one embodiment, the visual representation generation component 140 can be configured to receive an input to expand a third node in the visual representation of the hierarchy of the spreadsheet (e.g., double-click the third node or click an icon indicating expansion of the third node, etc.). In one embodiment, in response to determining that the hierarchy type of the hierarchy in which the third node is located is not a cell (e.g., the third node is a row node or a table node), the visual representation is updated to display the descendant node of the third node, other ones of the plurality of nodes that have dependencies on the descendant node of the third node, and dependencies between the descendant node of the third node and the other nodes. According to one embodiment, updating the visual representation to display the descendant node of the third node includes displaying a portion of the descendant node of the third node (e.g., a predetermined number of nodes, nodes having a hierarchy type of a particular hierarchy, or nodes identified as being of high importance, etc.). According to one embodiment, the descendant node of the third node comprises a first plurality of descendant nodes, and wherein displaying the descendant node of the third node comprises displaying the first plurality of descendant nodes in a manner such that the relative location of the first plurality of descendant nodes in the visual representation coincides with the relative location in the spreadsheet of the one or more cells respectively corresponding to each of the descendant nodes in the first plurality of descendant nodes, thereby facilitating a user to conveniently and intuitively obtain information regarding the relative location of the one or more cells in the spreadsheet in the visual representation. In one embodiment, in response to determining that the hierarchy type of the hierarchy in which the third node is located is a cell, the visual representation is updated to display other nodes that have dependencies with the third node and dependencies between the third node and the other nodes. According to one embodiment, displaying other nodes having a dependency relationship with the third node includes displaying a part of all nodes having a dependency relationship with the third node (e.g., a predetermined number of nodes, nodes having a hierarchy type of a specific hierarchy, or nodes identified as having a high importance, etc.). According to one embodiment, updating the visual representation to display other nodes having a dependency relationship with the third node includes: when the hierarchical type of the other node is a cell, the other node is displayed in such a manner that the relative positions of the other node in the visual representation coincide with the relative positions of the cells respectively corresponding to the other node in the spreadsheet.

In one embodiment, generating a visual representation of a hierarchy of a spreadsheet may include displaying all nodes of the hierarchy that are of the hierarchy type of the page and all dependencies between nodes of the hierarchy that are of the hierarchy type of the page in the hierarchy, thereby facilitating a user to intuitively understand the logical relationships between the various pages of the spreadsheet.

In one embodiment, the visual representation generation component 140 can be configured to display, in response to receiving a request to visualize a node corresponding to a page in the spreadsheet, a first set of nodes that the node depends on corresponding to partial descendants of other pages in the spreadsheet, a second set of nodes that the node depends on corresponding to partial descendants of other pages in the spreadsheet, and dependencies between the node and the first and second sets of nodes. In one embodiment, in response to determining that the number of first set of nodes on which the node depends corresponding to partial descendant nodes of other pages in the spreadsheet exceeds a first threshold, hiding or collapsing the partial nodes in the first set of nodes; and in response to determining that the number of second set of nodes corresponding to partial descendant nodes of other pages in the spreadsheet that depend on the node exceeds a second threshold, hiding or collapsing the partial nodes in the second set of nodes. By hiding or collapsing, too much information may be avoided from being displayed, preventing the visual representation of the spreadsheet from being so complex that it is difficult for the user to obtain useful information.

In one embodiment, the visual representation generation component 140 can be configured to display, in response to receiving a request to visualize a node corresponding to a row in the spreadsheet, the respective nodes corresponding to the cells included in the row and the fifth set of nodes corresponding to the cells in the other rows in the spreadsheet that have dependencies on each of the respective nodes and the dependencies between the fifth set of nodes and each of the respective nodes. It is to be appreciated that the visual representation generation component 140 can also be configured to visualize a node corresponding to a column (or block, etc.) in a spreadsheet in a similar manner.

In one embodiment, generating a visual representation of a hierarchy of a spreadsheet may include displaying a hierarchy of a spreadsheet in the form of a hierarchical directory, which may be expanded or collapsed stage by stage. In one embodiment, nodes belonging to different pages in a spreadsheet may be represented in a visual representation in different display formats (e.g., background color, fill pattern, etc.), and different hierarchical types of nodes may also be represented in different node display formats (e.g., node shape, node background color, etc.). In one embodiment, the visual representation generation component 140 can be configured to highlight the selected node and nodes that have dependencies on the selected node in response to receiving input selecting a node in the visual representation (e.g., clicking on the node or double clicking on the node, etc.).

In one embodiment, the visual representation generation component 140 can be configured to display additional information for a node in the visual representation in response to receiving input to view the additional information for the node (e.g., the cursor remains at the node for a predetermined time or double-clicking on the node, etc.). According to one embodiment, displaying additional information for the node includes displaying thumbnails of one or more cells corresponding to the node. According to one embodiment, displaying additional information for the node includes displaying links representing one or more other nodes affiliated or dependent with the node. In one embodiment, the visual representation generation component 140 can be configured to update or newly create a visual representation to display one or more other nodes in response to receiving input to click on a link to the one or more other nodes.

In one embodiment, the visual representation of the hierarchy of spreadsheets may be packed up stage by stage. In one embodiment, the visual representation generation component 140 can be configured to receive an input to collapse a third node in the visual representation of the hierarchy of spreadsheets (e.g., select an icon indicating collapse of the third node, double click the third node, etc.). In one embodiment, the visual representation generation component 140 can be configured to, in response to determining that the hierarchical type of the hierarchy in which the third node is located is not a cell, update the visual representation to hide the child nodes of the third node, a first set of other nodes (which can be one or more nodes) of the plurality of nodes in the hierarchical structure of the spreadsheet that have dependencies on the child nodes of the third node, and dependencies between the child nodes of the third node and the first set of other nodes, and display a second set of other nodes that have dependencies on the third node and dependencies between the third node and the second set of other nodes. That is, the child node of the third node and its dependency are no longer displayed, but the third node and its dependency are displayed. In one embodiment, the visual representation generation component 140 can be configured to update the visual representation to hide other nodes having a dependency relationship with the third node and dependencies between the third node and other nodes in response to determining that the hierarchy type of the hierarchy in which the third node is located is a cell.

Fig. 2 is a flow diagram illustrating an exemplary method 200 for visualizing a spreadsheet according to some embodiments of the present disclosure. Although fig. 2 illustrates 4 steps of the method 200, this is merely an exemplary flow of a method for visualizing a spreadsheet, and the method 200 for visualizing a spreadsheet may also include other possible steps, or may include a combination of any of the four steps described above.

The method 200 begins with step S210, which step S210 may be performed by the hierarchy acquisition component 110 of fig. 1. At step S210, a hierarchical structure of the spreadsheet is obtained, wherein the hierarchical structure comprises one or more levels having a plurality of nodes, wherein each level has at least one node and has a corresponding level type, each node corresponding to one or more cells in the spreadsheet, wherein the hierarchical structure further comprises membership between the plurality of nodes.

The method 200 proceeds to step S220, and step S220 may be performed by the metadata extraction component 120 of fig. 1. At step S220, metadata for each of a plurality of nodes in the hierarchy is extracted.

The method 200 proceeds to step S230, and step S230 may be performed by the dependency establishment component 130 of fig. 1. At step S230, dependencies between the plurality of nodes in the hierarchy are established based at least on the calculated relationships between the plurality of nodes.

The method 200 proceeds to step S240, which step S240 may be performed by the visual representation generation component 140 of fig. 1. At step S240, a visual representation of a hierarchy of the spreadsheet is generated based on the hierarchical structure of the spreadsheet, the metadata, and the dependencies between the plurality of nodes.

Exemplary embodiments according to the present disclosure will be described below in conjunction with fig. 3-15.

Fig. 3 is a schematic diagram illustrating an example spreadsheet 300 according to some embodiments of the present disclosure. Although the spreadsheet shown in FIG. 3 is based on Microsoft Excel, it should be understood that embodiments of the present disclosure are not so limited, but may be applied to spreadsheets made based on any suitable software.

As shown in FIG. 3, the spreadsheet 300 is a workbook (book), i.e., a spreadsheet file (e.g., xls file, xlsx file). Shown in FIG. 3 is a page (Sheet1)371 of the workbook, which may also have one or more other pages (e.g., pages 372, etc.), where each page may have one or more cells, such as cell 313. Each cell may store a number, text, formula, or the like. For example, the result "9.9%" of the formula is displayed by default at cell 313, and the formula "═ G12/B12-1" of cell 313 is displayed in other areas of the spreadsheet file to indicate that the value of cell 313 ═ G12 cell value/B12 cell value-1. As described above, this manner of display makes it difficult for a user to debug and understand the spreadsheet model.

As described above, an exemplary method for visualizing a spreadsheet according to embodiments of the present disclosure may obtain a hierarchical structure of a spreadsheet. According to one embodiment, obtaining the hierarchical structure of the spreadsheet may include identifying one or more of location, content, and format information of cells in the spreadsheet, and identifying the hierarchical structure of the spreadsheet based at least on the one or more of the location, content, and format information of the cells. According to another embodiment, obtaining the hierarchy of the spreadsheet may include receiving a hierarchy specified by a user. According to yet another embodiment, the hierarchical structure of the spreadsheet may be automatically identified in part by the above method, and in part by a user designation; or may be provided to the user after automatic recognition and then refined and supplemented according to user input. A machine learning algorithm may also be used to obtain the hierarchy of the spreadsheet. For example, a machine learning algorithm (e.g., a support vector machine, a decision tree, a neural network, etc.) may be trained using a certain number (e.g., tens of thousands) of spreadsheets and corresponding hierarchies obtained by manual or semi-manual means, and then the trained machine learning algorithm may be utilized to obtain the hierarchy of the given spreadsheet.

According to one embodiment, the hierarchy type of one or more levels of the spreadsheet may include one or more of the following: workbook, page, section, table, block, row, column, section, and cell. It should be appreciated that the above hierarchy types are merely examples, and that any other suitable hierarchy type may also be defined for the hierarchy. According to embodiments of the present disclosure, a page may be composed of sections or tables, and each section may further include a section or table. For example, in FIG. 3, page 371 may include two sections ("Chinese" and "United states"), section "United states" including two tables 330 and 340. A table may be made up of rows, columns, or blocks. For example, in FIG. 3, table 330 includes row 311, block 321 "electronics", etc., and table 340 includes row 316 "sales", column 312, block 314 "2017". A block may be a set of closely related rows or columns, with the row or column labels of the cells in the block being closely related. For example, in FIG. 3, the column labels of the cells in block 314 are "quarter first", "quarter second", "quarter third", "quarter fourth", and "year around", respectively, and are all under the column label of "2017" and thus may be identified as being closely related because they are contiguous. The blocks may also include blocks. The rows and columns may include cells or intervals (intervals). An interval may be a set of closely related cells. For example, the interval 315 in fig. 3 includes four cells belonging to the same row, column labels are closely related, and the formulas are similar and thus can be identified as one interval. The definition of rows and columns in a hierarchy according to embodiments of the present disclosure differs from the definition of rows and columns in a traditional spreadsheet: a row (column) defined in a traditional spreadsheet refers to all cells at the same horizontal position (vertical position) (e.g., column G includes all cells at that vertical position); while the rows and columns in the hierarchy are defined within the scope of the tables, i.e., if there are multiple tables arranged horizontally (vertically) in a page, the same horizontal (vertical) position may have multiple rows (columns) belonging to different tables, respectively. For example, cells G9-G13 may be considered columns belonging to table 330, while cells G18-G20 may be considered columns belonging to table 340.

In one embodiment, obtaining the hierarchy of the spreadsheet comprises one or more of: detecting a title in each page of the workbook, determining a level of the title based on one or more of location, content, and format information of the title, and dividing each page into sections or tables of corresponding levels based on the level of the title; determining a rank of the rows and columns based on one or more of position, content, and format information of the rows and columns of each table; dividing the rows and columns into blocks based on one or more of a correlation between rows, a correlation between columns, a level of rows, and a level of columns in each table; and dividing the cells into bins based on the correlation between the cells in each row and column. For example, for page 371 of FIG. 3, the title 310 "financial report," the title 320 "USA" and 350 "China," and the title 322 "sales detail" and the title 323 "profit sheet," the title 332 "sales detail" and the title 333 "profit sheet," may first be detected based on lines 1, 3-5, 15, 22-24, 34 including only textual content. The title 310 may then be determined to be textual information describing the contents of the entire page 371 based on the title 310 being in the top row of the page 371 and having blank lines with the title 320 "usa" and/or may be determined to be textual information describing the contents of the entire page 371 based on the format of the titles 310 and 320 (e.g., font size, font color, background color, indentation, whether bold/underlined), and the title 310 "financial report" being a primary title, the title 320 "usa" and 350 "china" being a secondary title. For the title "sales detail" 322, 332 and the title "profit sheet" 323, 333, the title 322 "sales detail" and the title 323 "profit sheet" may be determined to be the names of the sheet 330 and the sheet 340, respectively, based on the contiguous cells containing text and numbers under them and/or based on their format (e.g., font size, font color, background color, indentation, whether bold/underlined). Similarly, it may also be determined that title 332 "sales detail" and title 333 "profit tables" are the names of the two tables in section "china", respectively. The level of the row or column may then be determined, for example, for row 311 "total" in table 330, since it is more prominent (bold, and less indented) than the visual display of row "cell phone", row 311 "total" may be determined to have a higher level than row "cell phone". For column 312(F18-F20) in Table 340, the column may be identified by the column label "year round" as the summarized data for the year round, while columns B18-B20, columns C18-C20, columns D18-D20, columns E18-E20 represent the data for the first quarter through the fourth quarter, respectively. Thus, columns F18-F20 may be determined to have a higher rank than columns B18-B20, columns C18-C20, columns D18-D20, columns E18-E20. According to another embodiment, columns F18-F20 may also be assigned the same rank as columns B18-B20, columns C18-C20, columns D18-D20, columns E18-E20. The rows and columns in the table may be divided into blocks, for example, they may be divided into one block 321 belonging to the table 330 based on the rows "laptop", "tablet", "cell phone" having the same row level (or correlation between row labels). In addition, the cells may also be divided into intervals, e.g., cells L20-O20 may be divided into one interval 315 of table 340 based on the correlation between column labels of cells L20-O20 and the similarity of their calculation formulas.

As described above, an exemplary method for visualizing a spreadsheet according to embodiments of the present disclosure may extract metadata for each of a plurality of nodes in a hierarchy. According to one embodiment, the metadata for each node may include semantic information describing the specific meaning of the node. According to one embodiment, the semantic information may include one or more of a name, a title, a label, and an annotation. For example, semantic information for a workbook may be the name of the workbook and/or text describing the entire workbook and/or annotations for the entire workbook, semantic information for a page may be the name of the page and/or text describing the entire page and/or annotations for the entire page, semantic information for a section may be the title of the section and/or annotations for the entire section, semantic information for a table may be the name of the table and/or annotations for the entire table, semantic information for a row or column may be the label of the row or column and/or annotations for the row/column, semantic information for a block may be the text label for all rows or columns of the block and/or annotations for the block, semantic information for a cell may be the row label of the cell and the column label of the cell and/or annotations for the cell The remarking of (1). For example, for the spreadsheet shown in FIG. 3, the semantic information for page 371 may be identified as title 310, i.e., "financial report". For example, the semantic information of table 330 may be identified as "U.S. sales detail" which is a combination of title 320 "U.S." and title 322 "sales detail", the semantic information of column 312 may be identified as "2017 whole year", the semantic information of cell 313 may be identified as "2018 first quarter comparable growth rate", the semantic information of interval 315 may be identified as "2019 first quarter, second quarter, third quarter, fourth quarter gross profit", or may be identified as "2019 gross profit".

According to one embodiment, the metadata for each node may include operators in the computational formula for that node, e.g., a single operator or a combination of multiple operators, etc. For example, the cell 313 is calculated as G12/B12-1, and cell nodes G12 and B12 may be extracted from the calculation to determine that the cell has a calculation relationship with cells G12 and B12, and specifically, cell 313 is calculated from cells G12 and B12. Further, extracting the metadata of cell node 313 may include extracting the operator ". times./. times. -1" in the calculation formula G12/B12-1, where ". times." represents a placeholder. It should be understood that a calculation formula in a spreadsheet may include not only a calculation formula between cells, but a spreadsheet may also include a calculation formula between higher level nodes (e.g., rows, columns, etc.). For example, the cells B12 through P12 in the column 311 "total" may include an array formula "{ ═ B9: P9+ B10: P10+ B11: P11 }", which means that the column "total" has a calculation relationship with three rows of "notebook computer", "tablet computer", and "mobile phone". According to one embodiment, semantic information for each of a plurality of nodes in a hierarchy of spreadsheets may be extracted or generated from textual content of one or more cells corresponding to the node, or may be user-defined. For example, the user may manually input semantic information for a certain node, or modify semantic information that is automatically extracted or generated.

According to one embodiment, the metadata for each node (e.g., table node, row node, etc.) in the hierarchy may also include one or more of the following additional information: the generation time of the one or more cells corresponding to the node, the number of child nodes of the node (e.g., how many row nodes are affiliated with a table node), the value in the one or more cells corresponding to the node, the unit of the value (e.g., the unit "million" extracted from table 330), the point in time or time period to which the value corresponds (e.g., the time period corresponding to cell node 313 is 2018 quarterly), the format of the one or more cells corresponding to the node (e.g., font size, font color, background color, etc.), the location of the one or more cells corresponding to the node in the spreadsheet (e.g., cell node 313 is located at row 13, column G of page 1 of the spreadsheet), and the path from the root node in the hierarchy to the node. For example, for the spreadsheet shown in FIG. 3, the root node in the hierarchy may be the workbook 300 and the path from the root node to the cell node may be "workbook 300 → page 371 → table 330 → row 317 → cell 313".

The spreadsheet hierarchy and how dependencies between nodes are established according to exemplary embodiments of the present disclosure are further described below in conjunction with fig. 4A and 4B.

Fig. 4A and 4B are schematic diagrams illustrating an exemplary spreadsheet hierarchy 400 and dependencies between nodes according to some embodiments of the present disclosure. It should be understood that fig. 4A is exemplary, and the hierarchy shown in fig. 4A and 4B is only for ease of understanding, and is not intended to limit the storage format of the hierarchy. The hierarchy in fig. 4A may be obtained by the hierarchy obtaining section 110 of fig. 1, for example.

Nodes of different hierarchical types in the hierarchy are shown in fig. 4A with different node shapes, membership between nodes is shown with solid arrows, and dependencies between nodes are shown with dashed arrows. Assuming node a is subordinate to node B, the solid arrow points from B to a and node B is said to be the parent node of node a, which is the child node of node B. A node without a parent node is defined as a root node, and a node without a child node is defined as a leaf node. All nodes except the root node and the leaf nodes have one and only one father node, all nodes on a branch from the root node to one node are called ancestor nodes of the node, and any node in the subgraph taking one node as the root is called a descendant node of the node. The node 401, shown with an oval, is the root node, whose hierarchical type is a workbook, and the node 401 corresponds to the entire workbook of the spreadsheet. The hierarchy type of the node 403, shown as a rounded rectangle, is a table with two sub-nodes 404 and 405, shown as octagons, of the hierarchy type row. The hierarchy type of node 402 is also a row, but it is not affiliated with node 403, but directly affiliated with root node 401. That is, the workbook includes a total of three rows, where the rows corresponding to nodes 404 and 405 belong to the table corresponding to node 403. Nodes of the hierarchical type cell are shown with diamonds, where cell nodes 406 and 407 are attached to row node 402, cell node 408 is attached to row node 404, and cell nodes 409 and 410 are attached to row node 405. It should be understood that FIG. 4A is an example of a hierarchy of a spreadsheet, that actual hierarchies may include more or fewer or different hierarchy types than FIG. 4A, and that each hierarchy may include any suitable number of nodes.

Assuming that the calculation formula in the cell corresponding to node 408 indicates that the cell corresponding to node 408 is calculated from the row corresponding to node 402 (e.g., the value of the cell corresponding to node 408 is SUM (the row corresponding to node 402)), it may be determined that node 408 is dependent on node 402, which is represented by dashed arrow 434 between node 402 and node 408. Assuming that the calculation formula in the cell corresponding to the node 407 indicates that the cell corresponding to the node 407 is calculated by the cell corresponding to the node 410 (e.g., the value of the cell corresponding to the node 407 is equal to the value of the cell corresponding to the node 410 × 2), it may be determined that the node 407 depends on the node 410, which is represented by a dashed arrow 442 between the node 410 and the node 407. A method for establishing a dependency relationship between nodes according to an exemplary embodiment of the present disclosure will be described below with further reference to fig. 4A, 4B and 5.

Fig. 5 is a flow diagram illustrating an exemplary method 500 for establishing dependencies between nodes, according to some embodiments of the present disclosure. Although fig. 5 illustrates 3 steps of the method 500, this is merely an exemplary flow of a method for establishing dependencies between nodes, and the method 500 may also include other possible steps, or may include a combination of any of the three steps described above. The method 500 may be performed, for example, by the dependency establishment component 130 of FIG. 1.

The method 500 begins at step S510, where for each node in the hierarchy, it is determined that the node is computed by another node or nodes, at step S510. According to one embodiment, it may be determined that the node is calculated by another node or nodes by reading the contents of the cell(s) corresponding to the node, including the formula. For example, for cell 313 in FIG. 3, based on its content being the formula G12/B12-1, it may be determined that the node corresponding to cell 313 is computed from the node corresponding to cell G12 and the node corresponding to cell B12. For another example, referring to fig. 4A, it may be determined that the cell corresponding to node 408 is calculated from the row corresponding to node 402 and the cell corresponding to node 407 is calculated from the cell corresponding to node 410.

The method 500 proceeds to step S520, where, for each of the one or more first cells corresponding to the node, it is determined that the first cell is computed from a first set of one or more second cells corresponding to another one or more nodes. Referring back to fig. 4A, for the cell corresponding to node 408, it may be determined that the cell is calculated from the cell corresponding to node 406 and the cell corresponding to node 407, for example, according to SUM (the row corresponding to node 402) which is the value of the cell corresponding to node 408. It should be appreciated that since the hierarchical type of node 408 is itself a cell, only one cell's calculated relationship corresponding to node 408 need be determined here. Whereas if the computational relationship of the node 405 is initially determined, the computational relationships of the nodes 409 and 410 belonging to the node 405 and having a hierarchical type of cell, respectively, need to be determined. In addition, depending on the specific computational relationship, the cell corresponding to node 408 being computed from the row corresponding to node 402 does not necessarily mean that the cell corresponding to node 408 is computed from each cell in the row corresponding to node 402. For example, assuming that the calculation formula for row a, which includes three cells, is a value equal to row B, which includes three cells, it may be determined that the first cell in row a is calculated from the first cell in row B, rather than from each cell in row B. Through step S520, the calculation relationship between the nodes that may have a higher hierarchical type than the cell may be decomposed into the calculation relationship of the node whose hierarchical type is the cell in the descendant node of the node. Whereas for the calculated relationship between the cell corresponding to the node 407 and the cell corresponding to the node 410 determined in step S510, this step S520 may actually be omitted since the hierarchy type of the hierarchy in which the nodes 407 and 410 are located is already a cell.

The method 500 proceeds to step S530, where at step S530 the following dependencies are established: the first node corresponding to the first cell and all ancestor nodes of the first node depend on the first set of second nodes and all ancestor nodes of the first set of second nodes corresponding to the first set of second cells, respectively. Referring to fig. 4B, for node 408, for example, it has been determined at step S520 that the cell corresponding to node 408 is calculated from the cell corresponding to node 406 and the cell corresponding to node 407. The following dependencies can thus be established: nodes 408 and all ancestors of node 408 that are not the ancestor of node 406 depend on nodes of all ancestors of node 406 and 406 that are not the ancestor of node 408, and nodes of all ancestors of node 408 that are not the ancestor of node 407 depend on nodes of all ancestors of node 407 and 407 that are not the ancestor of node 408. FIG. 4B illustrates the dependencies constructed for the hierarchy shown in FIG. 4A. Note that the dependencies between node 408 and its ancestor and node 407 and its ancestor are omitted from FIG. 4B for clarity of illustration. That is, the following dependencies may be established: dependency 431 indicating that node 408 depends on node 406; dependency 432, indicating that node 404 depends on node 406; dependency 433, indicating that node 403 is dependent on node 406; dependency 434, indicating that node 408 depends on node 402; dependency 435, indicating that node 404 depends on node 402; dependency 436, indicating that node 403 is dependent on node 402. For another example, for node 407, it has been determined at step S520 that the cell corresponding to node 407 was calculated from the cell corresponding to node 410. The following dependencies can thus be established: node 407 and all ancestors of node 407 that are not the ancestor of node 410 depend on node 410 and all ancestors of node 410 that are not the ancestor of node 407. That is, the following dependencies may be established: a dependency 442 indicating that node 407 depends on node 410; dependency 443, indicating that node 407 depends on node 405; dependency 445, indicating that node 407 depends on node 403; dependency 441, indicating that node 402 depends on node 410; dependency 444, indicating that node 402 depends on node 405; dependency 446, indicating that node 402 is dependent on node 403.

Fig. 6 is a schematic diagram illustrating a node 600 in an exemplary spreadsheet hierarchy according to some embodiments of the present disclosure. It should be understood that node 600 may be any one of the nodes in the hierarchy in fig. 4A-4B. A data structure storing node information in a hierarchical structure as shown in fig. 4A-4B according to an exemplary embodiment of the present disclosure may be referred to as a hierarchy map (hierarchy map). It should be understood that the tree hierarchy shown in fig. 4A and 4B is only for convenience of understanding, and is not intended to limit the manner in which the hierarchy is stored. The hierarchical map may be stored using any suitable data structure, such as a linked list, a directed graph, and the like.

According to one embodiment, a hierarchy type 602 of the hierarchy in which the node is located, child nodes 604 of the node, and metadata 606 of the node may be stored for each node. Referring to fig. 4B, for example, for node 404, a hierarchy type of the hierarchy in which node 404 is located may be stored as a row, a child node of node 404 may be node 408 (e.g., a list of numbers of child nodes or a list of pointers to child nodes may be stored, etc.), and metadata of node 404 may be stored (e.g., a row label of the row corresponding to node 404, a location of the cell corresponding to node 404 in the spreadsheet, etc.). The metadata of the nodes has already been described above with reference to fig. 3 and will not be described in detail here.

According to one embodiment, the nodes 608 on which the node depends may also optionally be stored for each node (e.g., the number of the dependent node may be stored or a pointer to the dependent node may be stored, etc.). Referring to FIG. 4B, for node 404, for example, it may be stored that the nodes on which node 404 depends include node 402 and node 406. It should be noted that storing the nodes on which each node depends is not necessary, and the nodes on which each node depends may be determined in real time without storage prior to generating the visual representation of the hierarchy of the spreadsheet.

A method of generating a visualization of a hierarchy of a spreadsheet according to an embodiment of the present disclosure is described below.

7A-7C illustrate visual representations of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure. For clarity, spreadsheets corresponding to these visual representations are not shown. In the visual representation 700A of fig. 7A, all nodes of a hierarchy having a hierarchy type of page and dependencies between all nodes of the hierarchy having a hierarchy type of page in the hierarchy are shown. According to one embodiment, the visual representation 700A of FIG. 7A may be displayed in response to receiving a request to visualize all nodes of a hierarchy type as a page. For example, the request may be a selection (e.g., a single click, a double click, a swipe, a long press (for a display device with a touch-sensitive surface), etc.) of an icon (e.g., text, a button, or a graphic, etc.) indicating that the visualization hierarchy type is all nodes of a page. As shown in FIG. 7A, the visual representation 700A includes nodes 701-707 and arrows indicating the dependencies between them, and the semantic information (e.g., page names) of the nodes 701-707 is also displayed on the corresponding nodes. In visual representation 700A, nodes belonging to different pages in the spreadsheet are represented by different fill patterns, and nodes 701-707 are all represented by hexagons, indicating that the nodes all have the same hierarchical type (i.e., page). It should be appreciated that visual representation 700A is merely exemplary, and that nodes belonging to different pages in a spreadsheet may also be represented in other different display formats (e.g., fill color, node shape, node size, etc.), and that nodes belonging to the same hierarchical level may also be represented in the same other node formats (e.g., fill color, fill pattern, node size, etc.). Thus, a visual representation of the hierarchy of such a spreadsheet may clearly and intuitively present to the user which pages are included in the spreadsheet, the meaning of the pages, and the dependencies between the pages.

According to one embodiment, as shown in FIG. 7B, in response to receiving input selecting a node 701 in the visual representation 700A, the selected node 701 and nodes 702 and 705 having a dependency relationship with the node 701 may be highlighted. An input selecting node 701 in visual representation 700A may be an input clicking on node 701, an input double clicking on node 701, or a selection of an icon (e.g., text, graphics, buttons, etc.) indicating that node 701 is highlighted. As shown in fig. 7B, the font color and fill color of the selected node 701 are reversed, and the nodes 706 and 707 having no dependency relationship with the selected node 701 are faded out. It should be appreciated that this is merely an example of a way to highlight the selected node 701 and the nodes 702 and 705 having a dependency relationship with the node 701, and that any other suitable way of highlighting may be used, such as enlarging the node to be highlighted, adding a predefined background color to the node to be highlighted, and so forth. Thus, the visual representation 700B may help the user more clearly view the pages of the spreadsheet that are of interest to the user, while avoiding interference with information that is not of interest to the user. This is particularly advantageous when the size of the spreadsheet is large.

According to one embodiment, the nodes in the visual representation 700A of FIG. 7A may be expanded to their descendant nodes (i.e., nodes of a lower hierarchical type) in a stepwise manner. FIG. 7C shows the visual representation 700C after the nodes 701 have been expanded. The visual representation 700C may be displayed in response to receiving an input to expand the node 701 in the visual representation 700A or 700B. The input to expand node 701 may be a single click of node 701, a double click of node 701, or a selection of an icon (e.g., text, graphics, an option in a menu that pops up after node 701 is selected) indicating to expand node 701, and so on. As shown in FIG. 7C, node 701 is expanded into its children nodes 711 and 712, with nodes 711 and 712 shown with rectangles to indicate that their hierarchical types are different from those of nodes 701 and 707. For example, the hierarchy type of nodes 711 and 712 may be a table. Also displayed in the visual representation 700C are the semantic information "2014-profit prediction" and "2015-profit prediction" for node 711 and node 712, which are, for example, the titles of the tables corresponding to nodes 711 and 712, respectively. In addition, the dependencies between node 701 and other nodes are also replaced with dependencies between nodes 711 and 712 and other nodes. The dependency relationship between node 711 and node 712 may also be displayed, e.g., the arrow between nodes 711 and 712 in FIG. 7C may indicate that node 712 depends on node 711. It should be understood that although the expansion of the node 701 of hierarchy type page to its two child nodes, table nodes 711 and 712, is shown in FIG. 7C, the present invention is not so limited and descendants of other hierarchies of the node 701, such as descendant nodes of a hierarchy type row and dependencies between these descendant nodes and other nodes of hierarchy type pages, etc., may also be displayed in response to receiving an input to expand the node 701 in the visual representation 700A or 700B.

According to one embodiment, visual representation 700A may also be displayed in response to receiving an input to collapse a node 711 or 712 in visual representation 700C of a hierarchy of spreadsheets. For example, the input to stow node 711 may be a single click of node 711, a double click of node 711, or a selection of an icon (e.g., text, graphics, an option in a menu that pops up after selecting node 711, etc.) indicating stow node 711, and so on. In particular, the visual representation 700C can be updated to hide the dependencies between nodes 711 and 712, nodes 711 and 712 and other nodes 702 and 707, and between nodes 711 and 712, and to display the parent 701 of nodes 711 and 712, and the dependencies between node 701 and other nodes 702 and 707. Therefore, after the nodes in the visual representation 700A are expanded according to the hierarchy, the expanded descendant nodes can be collected according to the hierarchy, and a user can conveniently check information of any hierarchy according to the requirement of the user.

8A-8C illustrate visual representations of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure. For clarity, spreadsheets corresponding to these visual representations are not shown. In the visual representation 800A of fig. 8A, the node 801 of which the hierarchical type of the hierarchy where the node 801 is located in the hierarchical structure is a page, the first group of nodes 811 and 814 corresponding to partial descendant nodes (or all descendant nodes) of other page nodes in the hierarchical structure on which the node 801 depends, and the second group of nodes 821 and 826 corresponding to partial descendant nodes (or all descendant nodes) of other page nodes in the hierarchical structure on which the node 801 depends are displayed, and the dependencies between the node 801 and the first group of nodes 811 and 814 and the second group of nodes 821 and 826 are also displayed by arrows. According to one embodiment, the visual representation 800A of fig. 8A may be displayed in response to receiving a request to visualize a node 801 corresponding to a page in a spreadsheet. For example, the request can be a selection (e.g., a single click, a double click, a swipe, a long press (for display devices with touch-sensitive surfaces), etc.) of an icon (e.g., text, a button, a graphic, etc.) of a node 801 indicating that the visualization hierarchy type is a page or can be an input (e.g., a single click, a double click, etc.) that visualizes the node 701 in the visual representation 700A shown in fig. 7A. As shown in FIG. 8A, in the visual representation 800A, the semantic information (e.g., the name of the page, the label of the row or column, etc.) of the nodes 801, 811-814, and 821-826 are also displayed on the corresponding nodes. For example, the nodes 811-814 and 821-826 may be nodes at any level lower than the level of the page, e.g., their level types may be sections, tables, rows, columns, etc. For clarity, the nodes 801, 811-814 and 821-826 are shown in the same format in FIG. 8A, but it should be understood that nodes belonging to different pages in the spreadsheet may be represented in the visual representation 800A in different display formats (e.g., fill color, fill pattern, node shape, node size, etc.) and different hierarchical types of nodes may be represented in different node display formats (e.g., fill color, fill pattern, node shape, node size, etc.).

In one embodiment, in response to determining that the number of first set of nodes on which node 801 depends corresponding to partial descendant nodes of other page nodes in the spreadsheet exceeds a first threshold (e.g., 10, 20, etc.), partial nodes in the first set of nodes may be hidden or collapsed such that displayed nodes in the first set of nodes do not exceed the first threshold; and in response to determining that the number of second set of nodes corresponding to partial descendant nodes of other page nodes in the spreadsheet that depend on node 801 exceeds a second threshold (e.g., 10, 20, etc.), partial nodes in the second set of nodes may be hidden or collapsed such that displayed nodes in the second set of nodes do not exceed the second threshold. The selection of nodes to be hidden or collapsed may be based on any suitable criteria. For example, a hidden or collapsed node may be a less dependent node, a node that is positioned later in a spreadsheet, a node with a smaller number of corresponding cells, and so on. Therefore, especially under the condition that the electronic form is large in scale and the dependency relationship is complex, the visual representation can be simplified, and the information which is possibly not important can be hidden.

Triangle 851 is shown in node 801 to indicate that node 801 may be further expanded to its descendant nodes (i.e., nodes of a lower hierarchical type). Triangle 851 is exemplary and any other suitable text, graphics, icons, buttons, etc. may be used to indicate to the user that node 801 may be further expanded.

FIG. 8B shows the visual representation 800B after the node 801 is expanded. The visual representation 800B may be displayed in response to receiving an input to expand the node 801 in the visual representation 800A. The input to expand node 801 may be a single click of node 801, a double click of node 801, or selection of an icon (e.g., text, a graphic such as triangle 851 in FIG. 8A, an option in a menu that pops up upon selection of node 801, etc.) indicating to expand node 801, and so forth. As shown in FIG. 8B, node 801 is expanded into its child nodes 831 and 832. For example, the hierarchy type of nodes 831 and 832 can be a table (or section). The semantic information "2014-for-profit forecast" and "2015-for-profit forecast" for node 831 and node 832, e.g., the headings of the tables corresponding to nodes 831 and 832, respectively, may also be displayed in the visual representation 800B. Furthermore, the dependencies between node 801 and other nodes are also replaced with dependencies between nodes 831 and 832 and other nodes. The dependency relationship between nodes 831 and 832 may also be displayed, for example, an arrow may be included in visual representation 800B from node 831 to node 832 to indicate that node 832 is dependent on node 831. It should be understood that although the expansion of the node 801 having a hierarchical type of page to its two child nodes, table nodes 831 and 832, is shown in FIG. 8B, the present invention is not so limited and descendants of other hierarchies of the node 801, such as descendant nodes having a hierarchical type of row and dependencies between these descendant nodes and other nodes, may also be displayed in response to receiving an input to expand the node 801 in the visual representation 800A. It should be noted that in visual representation 800B, the shape of triangle 851 may be rotated relative to FIG. 8A to indicate that node 801 has been unfolded. According to one embodiment, nodes 831 and 832 are displayed in a manner such that the relative locations of nodes 831 and 832 in visual representation 800B coincide with the relative locations of one or more cells (tables) corresponding to nodes 831, 832, respectively, in a spreadsheet. For example, the table corresponding to node 831 is above the table corresponding to node 832 in the spreadsheet, so node 831 may be displayed above node 832 in visual representation 800B. It should be understood that node 831 may also be displayed to the left or above the left of node 832, etc.

According to one embodiment, visual representation 800A may also be displayed in response to receiving an input to collapse a node 801 in visual representation 800B of a hierarchy of spreadsheets. For example, the input to stow node 801 may be a single click of node 801, a double click of node 801, or selection of an icon (e.g., text, a graphic such as triangle 851, an option in a menu that pops up after node 801 is selected, etc.) indicating stow node 801, and so forth. Specifically, in response to determining that the hierarchical type of the hierarchy in which the node 801 is located is not a cell, the visual representation 800B is updated to hide the nodes 831 and 832 and the dependencies between the two nodes, the other nodes 811-. It should be understood that although the other nodes dependent on node 801 and dependent on nodes 831 and 832 are shown here as nodes 811-814, 821-826, they may be different. For example, if the number of other nodes having dependencies on node 801 and other nodes having dependencies on nodes 831, 832 exceeds the first or second threshold described above, some of the nodes may be hidden or collapsed, whereas in the case of displaying only node 801, the hidden or collapsed nodes may be different than in the case of displaying nodes 831 and 832, such that the other nodes having dependencies on node 801 shown in visual representation 800A may be different from the other nodes having dependencies on nodes 831, 832 shown in visual representation 800B.

As shown in fig. 8B, a triangle is also displayed in the node 831 to indicate that the node 831 may be further expanded to its descendant nodes (i.e., nodes of lower hierarchical type).

FIG. 8C shows the visual representation 800C after the nodes 831 are unfolded. The visual representation 800C may be displayed in response to receiving an input to expand the node 831 in the visual representation 800B. The input of the expanded node 831 may be an input of clicking the node 831, an input of double-clicking the node 831, or a selection of an icon (e.g., text, a graphic such as a triangle in the node 831, an option in a menu popped up after selecting the node 831) indicating the expanded node 831, or the like. As shown in fig. 8C, node 831 is expanded into its child nodes. For example, the hierarchy type of these child nodes may be rows (or columns). The semantic information "premium income", "interest and commission net income", "revenue subtotal", "profit" and "gross profit" for these sub-nodes may also be displayed in the visual representation 800C, such as row labels for the rows corresponding to these sub-nodes, respectively. Furthermore, the dependencies between the node 831 and other nodes are also replaced with dependencies between these child nodes and other nodes. Meanwhile, the dependency relationships inside these child nodes may be displayed, for example, an arrow pointing from "revenue subtotal", "expenditure on business" to "profit" may be included in the visual representation 800C to indicate that profit depends on "revenue subtotal", "expenditure on business". Additionally, an arrow may also be included in the visual representation 800C from "premium revenue" to "2015 earnings" 832 to indicate that 2015 earnings are dependent on the premium revenue in 2014 earnings. It should be understood that although the expansion of the node 831 having a hierarchy type as a table to its child nodes having six hierarchy type rows is shown in fig. 8C, the present invention is not limited thereto, and it is also possible to display the descendant nodes of other hierarchies of the node 831, such as the descendant nodes having a hierarchy type as a cell or a block, and the dependencies between these descendant nodes and other nodes, etc., in response to receiving an input to expand the node 831 in the visual representation 800B. According to one embodiment, the displayed descendant nodes of node 831 are displayed in a manner such that their relative locations in visual representation 800C coincide with the relative locations in the spreadsheet of one or more cells (rows) that correspond, respectively, to each of the descendant nodes of node 831.

According to one embodiment, the visual representation 800B may also be displayed in response to receiving an input to collapse a node 831 in the visual representation 800C of the hierarchy of spreadsheets. This process is similar to the process of collapsing the node 801 described above with reference to fig. 8B and will not be described in detail here. It should be appreciated that dependencies between children of node 831 can also be displayed in visual representation 800C, and that dependencies between these children can also be hidden in response to receiving input to collapse node 831 in visual representation 800C.

FIG. 9 illustrates a visual representation of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure. In the visual representation 900 of fig. 9, the nodes 901 and 902 of which the hierarchy type of the hierarchy where the node 901 is located in the hierarchy is a page, the third group node 921 and 926 corresponding to partial descendant nodes (or all descendant nodes) of other page nodes in the hierarchy which have dependency relationship with the node 901, and the fourth group node 911 and 913 corresponding to partial descendant nodes (or all descendant nodes) of other page nodes in the hierarchy which have dependency relationship with the node 902 are displayed, the dependency relationship between the node 901 and the third group node 921 and the dependency relationship between the node 902 and the fourth group node 911 and 913 are also displayed by arrows, and the dependency relationship between the nodes 901 and 902 or the dependency relationship inside or between the respective child nodes of the nodes 901 and 902 can also be displayed. According to one embodiment, the visual representation 900 of FIG. 9 may be displayed in response to receiving a request to visualize a node 901 corresponding to a first page and a node 902 corresponding to a second page in a spreadsheet. For example, the request may be a selection (e.g., a single click, a double click, a swipe, a long press (for a display device with a touch-sensitive surface), etc.) of an icon (e.g., text, a button, a graphic, etc.) indicating the visual nodes 901 and 902 or may be an input (e.g., a simultaneous selection of the two nodes, etc.) visualizing the nodes 701 and 703 in the visual representation 700A shown in fig. 7A. As shown in FIG. 9, in the visual representation 900, the semantic information of the nodes 901, 911, 913 and 921, 926 are also displayed on the corresponding nodes. For example, nodes 911-. For clarity, the nodes 901, 902, 911, 913, and 921, 926 are shown in the same format in FIG. 9, but it should be understood that nodes belonging to different pages in the spreadsheet may be represented in the visual representation 900 in different display formats (e.g., fill color, fill pattern, node shape, node size, etc.) and different hierarchical types of nodes may be represented in different node display formats (e.g., fill color, fill pattern, node shape, node size, etc.). It should be noted that although one node 921 on which the node 901 depends is shown in fig. 9, other nodes on which the node 901 depends, or other nodes that depend on the node 901 may also be displayed. In addition, although only the node 901 that depends on the node 902 is shown in fig. 9 for clarity, nodes that depend on the node 902 or other hierarchical types on which the node 902 depends as pages and/or descendant nodes thereof may also be displayed. The visual representation 900 may also display (some or all) descendant nodes of the node 901, (some or all) descendant nodes of the node 902, and dependencies between the descendant nodes of the node 901 and the descendant nodes of the node 902. For example, visual representation 900 may include an arrow from the descendant node "total" of node 902 to the descendant node "revenue totals" of node 901 to indicate that node "revenue totals" depends on node "total". It should be understood that although FIG. 9 shows the sub-nodes of the sub-nodes 931, 932 of the node 901 and the sub-node of the node 931 and the sub-nodes of the sub-nodes 941, 942 of the node 902 and the sub-node of the node 941, this is not required. According to one embodiment, only the nodes 901 and 902 and their dependencies with other nodes may be displayed without displaying descendant nodes of the nodes 901 and 902 in response to receiving a request to visualize the nodes 901 and 902. Nodes 931 and 941 and 942 are then displayed in response to receiving the requests to expand nodes 901 and 902, followed by displaying the descendant node of node 931 and the descendant node of node 941 in response to receiving the request to expand node 931. According to one embodiment, the visual representation 900 may also be displayed directly in response to receiving requests to visualize node 901 and node 902, i.e., displaying the child nodes of the child nodes 931, 932 and node 931 of node 901 and the child nodes of the child nodes 941, 942 and node 941 of node 902.

In one embodiment, in response to determining that the number of third set of nodes corresponding to partial descendant nodes of other page nodes in the spreadsheet that have dependencies with node 901 exceeds a certain threshold (e.g., 10, 20, etc.), partial nodes in the third set of nodes may be hidden or collapsed such that displayed nodes in the third set of nodes do not exceed the threshold; and in response to determining that the number of fourth set of nodes corresponding to partial descendant nodes of other page nodes in the spreadsheet that have dependencies with the node 902 exceeds a certain threshold (e.g., 10, 20, etc.), partial nodes in the fourth set of nodes may be hidden or collapsed such that displayed nodes in the fourth set of nodes do not exceed the threshold. As described above, the nodes to be hidden or collapsed may be selected based on any suitable criteria. In a manner similar to that described with reference to fig. 8A-8C, the nodes in the visual representation 900 of fig. 9 may also be expanded or collapsed in a hierarchy, which is not described again. It should be appreciated that the visual representations of three or more pages in the spreadsheet may be displayed in a manner similar to the visual representation 900 shown in FIG. 9 and will not be described in further detail herein.

Fig. 10 illustrates a visual representation 1000 of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure. In the visual representation 1000 of fig. 10, a node 1001 of which the hierarchical type of the hierarchy where the node is located is a row (or column), a node 1031-. According to one embodiment, the visual representation 1000 of FIG. 10 may be displayed in response to receiving a request to visualize a node 1001 corresponding to a row in a spreadsheet. For example, the request may be a selection (e.g., a single click, a double click, a swipe, a long press (for a display device with a touch-sensitive surface), etc.) of an icon (e.g., text, a button, a graphic, etc.) indicating the visualization node 1001 or may be an input (e.g., a single click, a double click, etc.) of a node that visualizes "business profit" as the semantic information in the visualization representation shown in fig. 8C or fig. 9. As shown in FIG. 10, in the visual representation 1000, the semantic information of the nodes 1001, 1031-. Nodes 1011-. For clarity, in fig. 10, the hierarchical types of the nodes 1011-.

In one embodiment, in response to determining that the number of fifth set of nodes corresponding to cells in other rows in the spreadsheet that have dependencies on nodes 1031 and 1032 exceeds a threshold (e.g., 10, 20, etc.), a portion of the nodes in the fifth set of nodes may be hidden or collapsed such that the displayed nodes in the fifth set of nodes do not exceed the threshold. As described above, the nodes to be hidden or collapsed may be selected based on any suitable criteria. In a manner similar to that described with reference to fig. 8A-8C, the nodes in the visual representation 1000 of fig. 10 may also be expanded or collapsed in a hierarchy, which is not described again.

11A-11B illustrate visual representations of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure. In the visual representation 1100A of fig. 11A, a node 1101 having a cell as a hierarchical type of a hierarchy where the node 1101 is located and other nodes 1102 and 1105 having a dependency relationship with the node 1101 are displayed. While the visual representation 1100A of fig. 11A shows only the nodes upon which the node 1101 depends for clarity, it should be understood that nodes that depend upon the node 1101 may also be displayed. According to one embodiment, the visual representation 1100A of fig. 11A may be displayed in response to receiving a request to visualize a node 1101 corresponding to one cell in a spreadsheet. For example, the request may be a selection (e.g., a click, a double click, a swipe, a long press (for a display device with a touch-sensitive surface), etc.) of an icon (e.g., text, a button, a graphic, etc.) indicating the visualization node 1101 or may be an input (e.g., a click, a double click, etc.) that visualizes a node in the visualization whose semantic information is "predicted net profit 12 months 2014". As shown in FIG. 11A, in the visual representation 1100A, the semantic information (e.g., the combination of the row label and column label of the cell) of the nodes 1101-1105 is also displayed on the corresponding nodes. Nodes 1102-1105 may be nodes having a hierarchical type of cell.

In one embodiment, in response to determining that the number of other nodes having a dependency relationship with node 1101 exceeds a certain threshold (e.g., 10, 20, etc.), some of these other nodes may be hidden or collapsed such that the displayed ones of the other nodes do not exceed the threshold.

A triangle is shown in node 1105 to indicate that node 1105 (which is actually a dependency of node 1105) may be further expanded. This triangle is exemplary, and any other suitable text, graphics, icons, buttons, etc. may be used to indicate to the user that node 1105 may be further expanded.

FIG. 11B shows the visual representation 1100B after the node 1105 has been expanded. The visual representation 1100B can be displayed in response to receiving an input to expand the node 1105 in the visual representation 1100A. The input to expand node 1105 may be an input to click on node 1105, an input to double click on node 1105, or a selection of an icon (e.g., text, a graphic such as a triangle in FIG. 11A, an option in a menu that pops up after node 1105 is selected, etc.) indicating to expand node 1105, etc. As shown in fig. 11B, the node 1105 is expanded to show the nodes 1111 and 1112 having a dependency relationship with it, and also shows the dependency relationship between the node 1105 and the nodes 1111 and 1112 with arrows. For example, the hierarchy type of nodes 1111 and 1112 may be cells. Semantic information for nodes 1111 and 1112, e.g., a combination of row labels and column labels for the cells corresponding to nodes 1111 and 1112, respectively, may also be displayed in the visual representation 1100B. According to one embodiment, displaying other nodes that have dependencies on node 1105 includes displaying a portion of all nodes that have dependencies on node 1105 (e.g., a predetermined number of nodes or nodes that are identified as being of high importance, etc.). According to one embodiment, nodes 1111 and 1112 are displayed in a manner such that the relative positions of nodes 1111 and 1112 in visual representation 1100B coincide with the relative positions of the cells in the spreadsheet that correspond to nodes 1111, 1112, respectively. For example, the cell corresponding to node 1111 is above the cell corresponding to node 1112 in the spreadsheet, so node 1111 may be displayed above node 1112 in visual representation 1100B. It should be understood that node 1111 may also be shown to the left or above the left of node 1112, etc. According to one embodiment, the nodes 1102-1105 may be similarly displayed in a manner such that the relative locations of the nodes 1102-1105 in the visual representation 1100B are consistent with the relative locations of the cells in the spreadsheet that correspond to the nodes 1102-1105, respectively.

According to one embodiment, the visual representation 1100A may also be displayed in response to receiving an input to collapse a node 1105 in the visual representation 1100B of the hierarchy of spreadsheets. For example, the input to stow node 1105 may be an input to click on node 1105, an input to double click on node 1105, or a selection of an icon (e.g., text, a graphic such as a triangle in FIG. 11B, an option in a menu that pops up after node 1105 is selected, etc.) indicating stow node 1105, and so forth. In particular, in response to determining that the hierarchy type of the hierarchy in which node 1105 is located is a cell, visual representation 1100B is updated to hide nodes 1111 and 1112 and the dependencies between node 1105 and nodes 1111 and 1112.

12A-12C illustrate a visual representation of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure. In fig. 12A, the hierarchy of the spreadsheet is shown in the form of a hierarchical directory that can be expanded or collapsed stage by stage. The visual representation 1200A of FIG. 12A may include nodes 1210-1270 of hierarchical type pages that are arranged in order from top to bottom and whose semantic information is also displayed in the visual representation 1200A. Symbol 1282 shows that the hierarchy type of node 1210 is page, symbol 1281 may indicate that node 1210 may be expanded.

FIG. 12B shows the visual representation 1200B after the node 1210 has been expanded. According to one embodiment, the visual representation 1200B may be displayed in response to an input of a click symbol 1281. The input to expand node 1210 may also be an input to click node 1210, an input to double click node 1210, or a selection of an option in a menu that pops up after node 1210 is selected (e.g., clicked, right clicked, or long pressed), etc. Visual representation 1200B can display child nodes 1211 and 1212 of a hierarchical type of node 1210 as a table. According to one embodiment, the children of the display node 1210 include some of the children of the display node 1210 (e.g., a predetermined number of nodes or nodes with corresponding cells in a spreadsheet that are positioned first, etc.). According to one embodiment, nodes 1211 and 1212 are displayed in a manner such that the relative positions of nodes 1211 and 1212 in visual representation 1200B coincide with the relative positions of the cells in the spreadsheet that correspond to nodes 1211 and 1212, respectively. For example, the table corresponding to node 1211 is above the table corresponding to node 1212 in the spreadsheet, so node 1211 may be displayed above node 1212 in visual representation 1200B.

According to one embodiment, visual representation 1200A may also be displayed in response to receiving an input to stow node 1210. For example, the input of stow node 1210 may be a single click of node 1210, a double click of node 1210, or selection of an icon (e.g., text, a graphic such as symbol 1281 in front of node 1210, an option in a menu that pops up after selecting node 1210, etc.) indicating stow node 1210, and so forth. In particular, in response to determining that the hierarchy type of the hierarchy at which node 1210 is located is not a cell, visual representation 1200B is updated to hide nodes 1211-1212. In addition, nodes 1211 and 1212 in fig. 12B may be further expanded.

FIG. 12C shows the visual representation 1200C after the node 1211 has been expanded. According to one embodiment, the visual representation 1200C may be displayed in response to the input of a symbol prior to clicking on node 1211. The input to expand node 1211 may also be an input to click on node 1211, an input to double click on node 1211, or a selection of an option in a menu that pops up after node 1211 is selected (e.g., clicked, right clicked, or long pressed), etc. The visual representation 1200C may display the child nodes 1213- & 1218 of the hierarchy type row of node 1211. According to one embodiment, the child nodes of the display node 1211 include a portion of the child nodes of the display node 1211 (e.g., a predetermined number of nodes or nodes with corresponding cells in a spreadsheet that are positioned first, etc.). According to one embodiment, the nodes 1213-. For example, the row in the spreadsheet corresponding to node 1213 is above the row corresponding to node 1214, so node 1213 may be displayed above node 1214 in visual representation 1200C.

According to one embodiment, the visual representation 1200B may also be displayed in response to receiving an input of the retract node 1211. For example, the input of the stow node 1211 may be an input of a single click of the node 1211, an input of a double click of the node 1211, or a selection of an icon (e.g., text, a graphic such as a symbol in front of the node 1211, an option in a menu that pops up after selecting the node 1211, etc.) indicating the stow node 1211, or the like. In particular, in response to determining that the hierarchy type of the hierarchy in which node 1211 is located is not a cell, visual representation 1200C is updated to hide nodes 1213 and 1218.

Fig. 13 is a schematic diagram illustrating additional information 1300 in a visual representation of a hierarchy of an exemplary spreadsheet according to some embodiments of the present disclosure. According to one embodiment, in response to receiving input to view additional information for a node in a visual representation (e.g., any of the visual representations shown in fig. 7A-12C), the additional information for the node may be displayed. For example, an input to view additional information for a node in the visual representation can be a hover of a cursor over the node, a selection of the node, and so forth. Additional information for one node a of hierarchy type table is shown in fig. 13, where the semantic information for this node a is "2014 profitability prediction".

According to one embodiment, displaying additional information for the node a includes displaying a thumbnail 1301 of one or more cells (e.g., here tables) corresponding to the node a. In the thumbnail image 1301, the numerical values of the cells in the table corresponding to the node a and the row coordinates and column coordinates of the cells in the spreadsheet are shown. Semantic information of the node a may also be displayed, for example, as a name 1302 "2014 profit prediction" of the table shown in fig. 13. The additional information may also include the directory 1303 of node A (all nodes on the path from the root node to the node in the hierarchy) "financial statements/Main tables/2014 profitability prediction". For example, a "financial statement" may be the name of a workbook, and a "main table" may be the title of the page on which the table resides. The additional information may also include the external node (the node corresponding to one or more cells of other pages) 1304 that the node a depends on, as well as the external node that depends on the node ("earnings prediction in 2015", "guaranteed fee revenue"), the row 1305 the table corresponding to the node a includes, and the column 1306 the table corresponding to the node a includes. It should be noted that the nodes shown in 1303-. According to one embodiment, in response to receiving input to click on a link, a visual representation is updated or created to visualize one or more nodes corresponding to the link. For example, in response to receiving the click link "operating profit," a line having the semantic information "operating profit" may be visualized, e.g., a visual representation 1000 as shown in FIG. 10 may be displayed. According to one embodiment, the additional information 1300 may also be updated to display additional information for nodes having the semantic information "operating profit". As described above, the additional information of one node is not limited thereto, but may include one or more of the following: the calculated relationship of the node to other nodes (which may be displayed in the form of a formula), the time of generation of the one or more cells corresponding to the node, the number of child nodes of the node, the numerical value in the one or more cells corresponding to the node, the unit of the numerical value, the point in time or period of time corresponding to the numerical value, the format of the one or more cells corresponding to the node, the location of the one or more cells corresponding to the node in the spreadsheet, and the path from the root node in the hierarchy to the node, etc.

Further, embodiments of the present disclosure are also directed to modifying a spreadsheet by modifying the visual representation. FIG. 14 is a flowchart 1400 illustrating an exemplary method for modifying a spreadsheet through a visual representation according to some embodiments of the present disclosure. While FIG. 14 illustrates 2 steps of the method 1400, this is merely an exemplary flow of a method for modifying a spreadsheet by modifying a visual representation, and the method 1400 for modifying a spreadsheet by a visual representation may include other possible steps as well, or may include a combination of any of the two steps described above.

The method 1400 begins at step S1410, where a modification to the visual representation is received at step S1410. Any of the visual representations shown in fig. 7A-12C may be modified. According to one embodiment, a user interaction component may be provided in the graphical user interface such that the user may make any modifications to the visual representation. For example, the user performs one or more of the following operations: modifying metadata (e.g., semantic information, operators, additional information, etc.) of any node in the visual representation, adding descendant or ancestor nodes to a node, adding nodes to which the node depends or nodes that depend on the node to a node, or changing membership of a node (e.g., moving node a, which is inherently affiliated with node B, below node C so that node a becomes a child of node C), etc. According to one embodiment, a dialog box may be further provided in response to a user operation to add a node or modify a node to request the user to enter further information about the node, such as a numerical value of one or more cells corresponding to the node, a calculation formula of one or more cells corresponding to the node, a display format of one or more cells corresponding to the node, and the like.

The method 1400 proceeds to step S1420, at step S1420, the spreadsheet is modified such that the modified spreadsheet corresponds to the modified visual representation. According to one embodiment, a spreadsheet corresponding to the visual representation may be first determined. For example, the visual representations generated for a spreadsheet may be stored in association with the spreadsheet, e.g., they may be stored at related locations, or they may have the same identifier, etc. According to one embodiment, the spreadsheet may be modified accordingly based on modifications to one or more of the hierarchy in the visual representation, the metadata, and the dependencies between the nodes. For example, in response to modifying the semantic information of node 814 of visual representation 800A in FIG. 8A from "aggregate" to "aggregate revenue invest", the row label of the row in the spreadsheet corresponding to node 814 may be modified to "aggregate revenue invest". For another example, in response to adding a new child node "actual 12 months 2014" for node 1001 of visual representation 1000 in FIG. 10, a new cell may be added to the right (or bottom) of the cell corresponding to nodes 1031 and 1032 in the row (or column) corresponding to node 1001 in the spreadsheet.

As described above, the spreadsheet is visualized hierarchically according to the embodiments of the present disclosure, and information of multiple dimensions, such as related semantic information and dependency relationships, is displayed, so that a user can more easily understand or change the organizational structure and logical relationship of the spreadsheet. The user can also expand or retract different nodes in the visual representation according to the hierarchy, so that information which is not concerned by the user can be effectively filtered, and the user can find the required information more easily. In addition, the electronic form is more intuitive and convenient to modify through visual representation.

Hardware implementation

FIG. 15 illustrates a general hardware environment in which the present disclosure may be applied, according to some embodiments of the present disclosure.

Referring to fig. 15, a computing device 1500 will now be described as an example of a hardware device applicable to aspects of the present disclosure. Computing device 1500 may be any machine configured to perform processing and/or computing, and may be, but is not limited to, a workstation, a server, a desktop computer, a laptop computer, a tablet computer, a personal digital assistant, a smart phone, a portable camera, or any combination thereof. The apparatus 100 described above may be implemented in whole or at least in part by a computing device 1500 or similar device or system.

Computing device 1500 may include components capable of connecting to bus 1502 or communicating with bus 1502 via one or more interfaces. For example, computing device 1500 may include a bus 1502, one or more processors 1504, one or more input devices 1506, and one or more output devices 1508. The one or more processors 1504 may be any type of processor and may include, but are not limited to, one or more general purpose processors and/or one or more special purpose processors (such as special purpose processing chips). Input device 1506 may be any type of device capable of inputting information to a computing device and may include, but is not limited to, a mouse, a keyboard, a touch screen, a microphone, and/or a remote control. Output device 1508 may be any type of device capable of presenting information and may include, but is not limited to, a display, speakers, a video/audio output terminal, and/or a printer. Computing device 1500 may also include or be connected with a non-transitory storage device 1510, the non-transitory storage device 1510 may be any storage device that is non-transitory and that may implement a data storage library, and may include, but is not limited to, disk drives, optical storage devices, solid state storage, floppy disks, flexible disks, hard disks, tape, or any other magnetic medium, compact disks or any other optical medium, ROM (read only memory), RAM (random access memory), cache memory, and/or any other memory chip or cartridge, and/or any other medium from which a computer may read data, instructions, and/or code. The non-transitory storage device 1510 may be detachable from the interface. Non-transient storageThe device 1510 may have data/instructions/code for implementing the methods and steps described above. Computing device 1500 may also include communications device 1512. The communication device 1512 may be any type of device or system capable of communicating with external apparatus and/or with a network, and may include, but is not limited to, a modem, a network card, an infrared communication device, wireless communication equipment, and/or a device such as bluetooth^TMDevices, 802.11 devices, WiFi devices, WiMax devices, cellular communications facilities, and the like.

The bus 1502 may include, but is not limited to, an Industry Standard Architecture (ISA) bus, a Micro Channel Architecture (MCA) bus, an enhanced ISA (eisa) bus, a Video Electronics Standards Association (VESA) local bus, and a Peripheral Component Interconnect (PCI) bus.

Computing device 1500 may also include a working memory 1514, which may be any type of working memory 1514 that can store instructions and/or data useful to the operation of processor 1504 and may include, but is not limited to, a random access memory and/or a read only memory device.

Software elements may reside in the working memory 1514 including, but not limited to, an operating system 1516, one or more application programs 1518, drivers, and/or other data and code. Instructions for performing the above-described methods and steps may be included in one or more applications 1518, and the above-described components of the apparatus 100 may be implemented by the processor 1504 reading and executing the instructions of the one or more applications 1518. More specifically, the hierarchy obtaining means 110 may be implemented, for example, by the processor 1504 when executing the application 1518 having instructions to perform step S210. The metadata extraction component 120 may be implemented, for example, by the processor 1504 when executing the application 1518 with instructions to perform step S220. The dependency establishment section 130 can be implemented, for example, by the processor 1504 when executing the application 1518 having instructions to perform step S230. The visual representation generation component 140 can be implemented, for example, by the processor 1504 when executing the application 1518 with instructions to perform step S240. Executable or source code for the instructions of the software elements may be stored in a non-transitory computer-readable storage medium, such as the storage device(s) 1510 described above, and may be read into the working memory 1514, where possible compiled and/or installed. Executable code or source code for the instructions of the software elements may also be downloaded from a remote location.

The present disclosure also relates to the following embodiments.

Embodiment 1, a computer-implemented method for visualizing a spreadsheet, the method comprising:

obtaining a hierarchy of a spreadsheet, wherein the hierarchy comprises one or more levels having a plurality of nodes, wherein each level has at least one node and has a corresponding level type, each node corresponding to one or more cells in the spreadsheet, wherein the hierarchy further comprises membership between the plurality of nodes;

extracting metadata for each of the plurality of nodes;

establishing a dependency relationship between the plurality of nodes based at least on the calculated relationships between the plurality of nodes in the hierarchy; and

generating a visual representation of a hierarchy of the spreadsheet based on a hierarchical structure of the spreadsheet, the metadata, and dependencies between the plurality of nodes.

Embodiment 2 the method of embodiment 1, wherein establishing dependencies between the plurality of nodes is further based on membership between the plurality of nodes.

Embodiment 3 the method of embodiment 1, wherein the metadata of each node includes semantic information describing a specific meaning of the node, and when the node is displayed in a visual representation of a hierarchy of the spreadsheet, the semantic information is also displayed in the visual representation.

Embodiment 4 the method of embodiment 1, wherein the metadata of each node includes an operator in the computational formula for the node.

Embodiment 5 the method of embodiment 1, wherein obtaining the hierarchy of spreadsheets comprises one or more of:

identifying one or more of location, content, and format information of cells in the spreadsheet, and identifying a hierarchy of the spreadsheet based at least on the one or more of location, content, and format information of the cells;

a hierarchy specified by a user is received.

Embodiment 6 the method of embodiment 1, wherein the hierarchy type of the one or more levels of the spreadsheet comprises one or more of: workbook, pages, sections, tables, blocks, rows, columns, sections, and cells, and wherein obtaining the hierarchy of the spreadsheet comprises one or more of:

detecting a title in each page of the workbook, determining a level of the title based on one or more of location, content, and format information of the title, and dividing each page into sections or tables of corresponding levels based on the level of the title;

determining a rank of the rows and columns based on one or more of position, content, and format information of the rows and columns of each table;

dividing the rows and columns into blocks based on one or more of a correlation between rows, a correlation between columns, a level of a row, a level of a column in each table; and

the cells are divided into bins based on the correlation between the cells in each row and column.

Embodiment 7 the method of embodiment 3, wherein the semantic information includes one or more of a name, a title, a label, and an annotation.

Embodiment 8 the method of embodiment 3, wherein the semantic information for each of the plurality of nodes is extracted or generated from text content of one or more cells corresponding to the node or defined by a user.

Embodiment 9 and the method according to embodiment 1, wherein establishing the dependency relationship between the plurality of nodes comprises:

for each node in the hierarchy, determining that the node is computed by another one or more nodes, for each of the one or more first cells corresponding to the node:

determining that the first cell is computed from a first set of second cells of the one or more second cells corresponding to the additional one or more nodes; and

the following dependencies are established: the first node corresponding to the first cell and all or part of ancestor nodes of the first node depend on the first group of second nodes corresponding to the first group of second cells and all or part of ancestor nodes of the first group of second nodes.

Embodiment 10, the method of embodiment 1, further comprising: for each node, the hierarchy type of the hierarchy in which the node is located, the child nodes of the node, and metadata of the node are stored.

Embodiment 11, the method of embodiment 10, further comprising: for each node, the node on which the node depends is stored.

Embodiment 12 the method of embodiment 3, wherein the metadata of each node further comprises one or more of the following additional information: a calculation formula for the node, a generation time of one or more cells corresponding to the node, a number of child nodes of the node, a numerical value in one or more cells corresponding to the node, a unit of the numerical value, a point in time or a time period corresponding to the numerical value, a format of one or more cells corresponding to the node, a location of one or more cells corresponding to the node in a spreadsheet, and a path from a root node in the hierarchy to the node, and the method further comprises:

receiving input to view additional information for a node in the visual representation; and

in response to receiving the input, additional information for the node is displayed.

Embodiment 13, the method of embodiment 1, further comprising:

receiving input to expand a third node in a visual representation of a hierarchy of the spreadsheet;

in response to determining that the hierarchy type of the hierarchy in which the third node is located is not a cell, updating the visual representation to display a descendant node of the third node, other nodes of the plurality of nodes that have a dependency relationship with the descendant node of the third node, and dependencies between the descendant node of the third node and the other nodes; and

in response to determining that the hierarchy type of the hierarchy in which the third node is located is a cell, updating the visual representation to display other nodes that have dependencies with the third node and dependencies between the third node and the other nodes.

Embodiment 14, the method of embodiment 13, wherein:

displaying other nodes of the plurality of nodes having a dependency relationship with the descendant node of the third node includes displaying some of all the nodes of the plurality of nodes having a dependency relationship with the descendant node of the third node, and

displaying the other nodes having a dependency relationship with the third node includes displaying a part of all the nodes having a dependency relationship with the third node.

Embodiment 15 the method of embodiment 13, wherein the descendant node of the third node comprises a first plurality of descendant nodes, and wherein displaying the descendant node of the third node comprises displaying the first plurality of descendant nodes in a manner such that the relative location of the first plurality of descendant nodes in the visual representation coincides with the relative location in the spreadsheet of one or more cells corresponding respectively to each of the descendant nodes in the first plurality of descendant nodes.

Embodiment 16 the method of embodiment 13, wherein updating the visual representation to display other nodes having a dependency relationship with a third node comprises: when the hierarchy type of the other node is a cell, the other node is displayed in a manner such that the relative position of the other node in the visual representation coincides with the relative position of the cells respectively corresponding to the other node in the spreadsheet.

Embodiment 17 the method of embodiment 1, wherein generating the visual representation of the hierarchy of the spreadsheet comprises displaying all nodes of the hierarchy at a level of the hierarchy that are of a page type and all the nodes of the hierarchy at a level of the hierarchy that are of a page type.

Embodiment 18, the method of embodiment 1, further comprising:

in response to receiving a request to visualize a node corresponding to a page in the spreadsheet, displaying the node, a first set of nodes that the node depends on corresponding to partial descendants of other pages in the spreadsheet, a second set of nodes that the node depends on corresponding to partial descendants of other pages in the spreadsheet, and dependencies between the node and the first and second sets of nodes.

Embodiment 19 the method of embodiment 18, further comprising:

hiding or collapsing partial nodes in the first set of nodes in response to determining that the number of first set of nodes on which the node depends corresponding to partial descendant nodes of other pages in the spreadsheet exceeds a first threshold; and

in response to determining that the number of the second set of nodes corresponding to partial descendant nodes of other pages in the spreadsheet that depend on the node exceeds a second threshold, hiding or collapsing the partial nodes in the second set of nodes.

Embodiment 20, the method of embodiment 1, further comprising:

in response to receiving a request for visualizing a first node and a second node respectively corresponding to a first page and a second page in the spreadsheet, displaying the first node, a third group of nodes which have dependency relationship with the first node and correspond to partial descendant nodes of other pages except the first page and the second page in the spreadsheet, and the dependency relationship between the first node, the third group of nodes which have dependency relationship with the first node and the first node, and displaying the second node, a fourth group of nodes which have dependency relationship with the second node and correspond to partial descendant nodes of other pages except the first page and the second page in the spreadsheet, and the dependency relationship between the second node and the fourth group of nodes and the second node.

Embodiment 21, the method according to embodiment 20, further comprising displaying a descendant node of the first node, a descendant node of the second node, and a dependency relationship between the descendant node of the first node and the descendant node of the second node.

Embodiment 22, the method of embodiment 1, further comprising:

in response to receiving a request to visualize a node corresponding to a row in the spreadsheet, displaying respective nodes corresponding to cells included in the row and a fifth set of nodes having a dependency relationship with each of the respective nodes corresponding to cells in other rows in the spreadsheet and dependencies between the fifth set of nodes and each of the respective nodes.

Embodiment 23 the method of embodiment 1, wherein generating the visual representation of the hierarchy of the spreadsheet comprises displaying the hierarchy of the spreadsheet as a hierarchical directory.

Embodiment 24 the method of embodiment 1, wherein generating a visual representation of a hierarchy of the spreadsheet comprises: nodes belonging to different pages in the spreadsheet are represented in the visual representation in different display formats, and different hierarchical types of nodes are represented in different node display formats.

Embodiment 25, the method of embodiment 1, further comprising:

in response to receiving input selecting a node in the visual representation, the selected node and nodes having a dependency relationship with the selected node are highlighted.

Embodiment 26 the method of embodiment 12, wherein displaying additional information for the node comprises displaying thumbnails of one or more cells corresponding to the node.

Embodiment 27 the method of embodiment 12, wherein displaying additional information for the node includes displaying links representing one or more other nodes affiliated or dependent with the node, and further comprising:

receiving an input to click on the link; and

updating or creating the visual representation to display the one or more other nodes.

Embodiment 28 the method of embodiment 13, further comprising:

receiving input to collapse a third node in the visual representation of the hierarchy of spreadsheets;

in response to determining that the hierarchy type of the hierarchy in which the third node is located is not a cell, updating the visual representation to hide the child node of the third node, a first group of other nodes of the plurality of nodes that have a dependency relationship with the child node of the third node, and the dependency relationship between the child node of the third node and the first group of other nodes, and to display a second group of other nodes that have a dependency relationship with the third node and the dependency relationship between the third node and the second group of other nodes; and

in response to determining that the hierarchy type of the hierarchy in which the third node is located is a cell, updating the visual representation to hide other nodes that have dependencies with the third node and dependencies between the third node and the other nodes.

Embodiment 29, the method of embodiment 1, further comprising:

receiving a modification to the visual representation; and

modifying the spreadsheet such that the modified spreadsheet corresponds to the modified visual representation.

Embodiment 30, an apparatus for visualizing a spreadsheet, comprising: means for performing the steps of the method of any one of embodiments 1-29.

Embodiment 31, an apparatus for visualizing a spreadsheet, 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 embodiments 1-29.

Embodiment 32, a non-transitory computer-readable storage medium storing instructions that, when executed by a processor, cause performance of the method of any of embodiments 1-29.

From the above embodiments, it is apparent to those skilled in the art that the present disclosure can be implemented by software and necessary hardware, or can be implemented by hardware, firmware, and the like. Based on this understanding, embodiments of the present disclosure may be implemented partially in software. The computer software may be stored in a computer readable storage medium, such as a floppy disk, hard disk, optical disk, or flash memory. The computer software includes a series of instructions that cause a computer (e.g., a personal computer, a service station, or a network terminal) to perform a method or a portion thereof according to various embodiments of the disclosure.

Having thus described the disclosure, it will be apparent that the same may be varied in many ways. Such variations are not to be regarded as a departure from the spirit and scope of the disclosure, and all such modifications as would be obvious to one skilled in the art are intended to be included within the scope of the following claims.

Claims (10)

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

obtaining a hierarchy of a spreadsheet, wherein the hierarchy comprises one or more levels having a plurality of nodes, wherein each level has at least one node and has a corresponding level type, each node corresponding to one or more cells in the spreadsheet, wherein the hierarchy further comprises membership between the plurality of nodes;

extracting metadata for each of the plurality of nodes;

establishing a dependency relationship between the plurality of nodes based at least on the calculated relationships between the plurality of nodes in the hierarchy; and

generating a visual representation of a hierarchy of the spreadsheet based on a hierarchical structure of the spreadsheet, the metadata, and dependencies between the plurality of nodes.

2. The method of claim 1, wherein establishing dependencies between the plurality of nodes is further based on membership between the plurality of nodes.

3. The method of claim 1, wherein the metadata for each node includes semantic information describing a specific meaning of the node, and when the node is displayed in a visual representation of a hierarchy of the spreadsheet, the semantic information is also displayed in the visual representation.

4. The method of claim 1, wherein the metadata for each node comprises an operator in a computational formula for the node.

5. The method of claim 1, wherein obtaining a hierarchical structure of a spreadsheet comprises one or more of:

identifying one or more of location, content, and format information of cells in the spreadsheet, and identifying a hierarchy of the spreadsheet based at least on the one or more of location, content, and format information of the cells;

a hierarchy specified by a user is received.

6. The method of claim 1, wherein a hierarchy type of the one or more hierarchies of the spreadsheet comprises one or more of: workbook, pages, sections, tables, blocks, rows, columns, sections, and cells, and wherein obtaining the hierarchy of the spreadsheet comprises one or more of:

detecting a title in each page of the workbook, determining a level of the title based on one or more of location, content, and format information of the title, and dividing each page into sections or tables of corresponding levels based on the level of the title;

determining a rank of the rows and columns based on one or more of position, content, and format information of the rows and columns of each table;

dividing the rows and columns into blocks based on one or more of a correlation between rows, a correlation between columns, a level of a row, a level of a column in each table; and

the cells are divided into bins based on the correlation between the cells in each row and column.

7. The method of claim 3, wherein the semantic information includes one or more of a name, a title, a label, and an annotation.

8. The method of claim 3, wherein the semantic information for each of the plurality of nodes is extracted or generated from textual content of one or more cells corresponding to the node or is defined by a user.

9. The method of claim 1, wherein establishing dependencies between the plurality of nodes comprises:

for each node in the hierarchy, determining that the node is computed by another one or more nodes, for each of the one or more first cells corresponding to the node:

determining that the first cell is computed from a first set of second cells of the one or more second cells corresponding to the additional one or more nodes; and

the following dependencies are established: the first node corresponding to the first cell and all or part of ancestor nodes of the first node depend on the first group of second nodes corresponding to the first group of second cells and all or part of ancestor nodes of the first group of second nodes.

10. The method of claim 1, further comprising: for each node, the hierarchy type of the hierarchy in which the node is located, the child nodes of the node, and metadata of the node are stored.

Images