Method, device and equipment for converting perspective data table and general data table

Abstract

The invention discloses a method, a device and equipment for converting a perspective data table and a general data table, and relates to the technical field of spreadsheets. The specific implementation scheme comprises the steps of receiving a first range; determining that the first range comprises the table cells of the general data table, and generating structured data according to the table cell data of the first range, the target header field and the target data area field; determining a rendering tree according to the structured data, a preset row title field, a preset column title field and a preset data area field; generating a data perspective table and a mapping relation according to the rendering tree; and / or determining that the first range comprises cells of the data perspective table, and generating a general data table according to the rendering tree corresponding to the data perspective table and the mapping relation. According to the method and the device, the calculation amount when the data perspective table is generated can be reduced, the efficiency is improved, the function of re-converting partial content in the generated data perspective table into a general data table is realized, and the user experience is improved.

Description

Technical Field

[0001] The present application relates to the technical field of electronic tables, and in particular to a method, device and equipment for converting a pivot table and a general data table. Background Art

[0002] A pivot table is a data summary and analysis tool that is used to extract specified information from a data source and display it in summary based on specific conditions.

[0003] At present, in various electronic spreadsheet applications, for the generation of pivot tables, row headers and column headers are generally generated according to user expectations first, and then for each cell in the data area, the general data table as the data source is traversed and queried according to the corresponding row headers and column headers, and the query results are returned to the corresponding cells. After traversing all value cells, invalid data is removed to obtain the pivot table.

[0004] However, in the prior art, as the number of data rows in the general data table serving as the data source increases, the number of queries to the data source will increase exponentially, and the amount of calculation required to generate a pivot table is huge and inefficient. At the same time, the prior art does not exist to reconvert part of the content in the existing pivot table into a general data table, resulting in a poor user experience. Summary of the invention

[0005] The embodiment of the present application provides a method, device and equipment for converting a pivot table to a general data table, thereby solving the problem that as the number of data rows in the general data table as the data source increases, the number of queries to the data source will increase exponentially, the amount of calculation required to generate a pivot table is huge, the efficiency is low, and there is no function to convert part of the content in the existing pivot table back to the general data table. The present application can reduce the amount of calculation required when generating a pivot table, improve efficiency, and realize the function of converting part of the content in the generated pivot table back to the general data table, thereby improving user experience.

[0006] In a first aspect, an embodiment of the present application provides a method for converting a pivot table and a general data table, including: Receive a first range; determine that the first range includes cells of a general data table, generate structured data based on the cell data of the first range, a target header field, and a target data area field, the structured data being used to indicate data of a target data area field corresponding to the target header field; determine a rendering tree based on the structured data and a preset row header field, a preset column header field, and a preset data area field, the rendering tree being used to indicate row header fields, column header fields, data area fields, and cell data corresponding to row header fields and column header fields of a pivot table; generate a pivot table and a mapping relationship based on the rendering tree, the mapping relationship being used to indicate positions of cells in the pivot table corresponding to nodes of the rendering tree; and / or, determine that the first range includes cells of the pivot table, generate a general data table based on the rendering tree corresponding to the pivot table, and the mapping relationship.

[0007] Furthermore, the method also includes: when the first range includes cells of multiple pivot tables, for the cells of the multiple pivot tables, based on the rendering trees respectively corresponding to the multiple pivot tables and the respectively corresponding mapping relationships, respectively generating general data tables corresponding to the multiple pivot tables.

[0008] Furthermore, the method also includes: in response to an operation of updating the data of the first cell, determining a target node according to the rendering tree and a mapping relationship; updating data corresponding to the target node in the rendering tree according to the updated data of the first cell; updating a pivot table according to the updated rendering tree; wherein the first cell is a cell in the pivot table, and the target node is a node that directly or indirectly references a node corresponding to the first cell.

[0009] Furthermore, the mapping relationship is also used to indicate the cell format corresponding to the node of the rendering tree, as well as the cell format of other cells in the pivot table. The method also includes: in response to an operation of setting the cell format for the first cell, updating the mapping relationship, and updating the pivot table according to the updated mapping relationship, the first cell being a cell in the pivot table.

[0010] Furthermore, after updating the pivot table according to the updated mapping relationship, the method also includes: determining the row header field, column header field and data area field corresponding to the first cell according to the rendering tree, the mapping relationship and the first cell; determining the fourth cell corresponding to the row header field, column header field and data area field corresponding to the first cell in the general data table corresponding to the pivot table according to the row header field, column header field and data area field corresponding to the first cell; and setting the cell format corresponding to the first cell for the fourth cell.

[0011] Furthermore, the method also includes: in response to an operation of editing data of a cell of a general data table in the first range, updating structured data; updating a rendering tree according to the updated structured data; and updating a pivot table according to the updated rendering tree.

[0012] Further, updating the pivot table according to the updated rendering tree includes: determining a fifth cell according to the rendering tree before the update and the rendering tree after the update, and a mapping relationship, the fifth cell being a cell in the pivot table whose data needs to be updated; and updating the data in the fifth cell.

[0013] In a second aspect, an embodiment of the present application provides a device for converting a pivot table and a general data table, including: a receiving module and a conversion module.

[0014] The receiving module is used to receive a first range.

[0015] A conversion module is used to determine that the first range includes cells of a general data table, generate structured data based on the cell data of the first range, a target header field and a target data area field, the structured data being used to indicate the data of the target data area field corresponding to the target header field; determine a rendering tree based on the structured data and a preset row header field, a preset column header field and a preset data area field, the rendering tree being used to indicate the row header field, the column header field and the data area field of a pivot table, and the cell data corresponding to the row header field and the column header field; generate a pivot table and a mapping relationship based on the rendering tree, the mapping relationship being used to indicate the position of the node of the rendering tree corresponding to the cell in the pivot table; and / or determine that the first range includes cells of a pivot table, generate a general data table based on the rendering tree corresponding to the pivot table and the mapping relationship.

[0016] In a third aspect, an embodiment of the present application provides a device, comprising: a processor; a memory for storing processor executable instructions; and a method for implementing the first aspect or any possible implementation of the first aspect when the processor executes the executable instructions.

[0017] In a fourth aspect, an embodiment of the present application provides a non-volatile computer-readable storage medium, which includes a device for storing a computer program or instruction. When the computer program or instruction is executed, the method of the first aspect or any possible implementation method of the first aspect is implemented.

[0018] One or more technical solutions provided in the embodiments of the present application have at least the following technical effects or advantages: The embodiment of the present application determines that the first range includes cells of a general data table, generates structured data according to the cell data of the first range, the target header field and the target data area field, determines a rendering tree according to the structured data, generates a pivot table according to the rendering tree, and the mapping relationship between the pivot table and the rendering tree, which can reduce the amount of calculation when generating a pivot table and improve efficiency. By determining that the first range includes cells of a pivot table, generating a general data table according to the rendering tree corresponding to the pivot table, and the mapping relationship between the pivot table and the rendering tree, it is possible to realize the function of reconverting part of the content of the generated pivot table into a general data table, thereby improving the user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments of the present application or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0020] Figure 1 A flowchart of a method for converting a pivot table to a general data table provided in an embodiment of the present application; Figure 2 A schematic diagram of the structure of a rendering tree provided in an embodiment of the present application; Figure 3 This is a schematic diagram of a general data table; Figure 4 for Figure 3 Schematic diagram of the pivot table corresponding to the general data table; Figure 5 for Figure 3 A schematic diagram of structured data corresponding to a general data table; Figure 6 This is a diagram of a pivot table before forced redrawing; Figure 7 This is a diagram of a pivot table after forced redrawing; Figure 8 A schematic diagram of the composition of a device for converting a pivot table and a general data table provided in an embodiment of the present application. DETAILED DESCRIPTION

[0021] The technical solutions in the embodiments of the present application will be described clearly and completely below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0022] The following describes some of the techniques involved in the embodiments of the present application to facilitate understanding, and they should be considered as merely exemplary. Therefore, it should be appreciated by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present application. Similarly, for the sake of clarity and conciseness, some descriptions of well-known functions and structures are omitted in the following description.

[0023] A PivotTable is a data summary and analysis tool that is used to extract specified information from a data source and display it in summary based on specific conditions.

[0024] At present, in various electronic spreadsheet applications, for the generation of pivot tables, row headers and column headers are generally generated according to user expectations first, and then for each cell in the data area, the data source is traversed and queried according to the corresponding row header and column header, and the query results are returned to the corresponding cells. After traversing all value cells, invalid data is removed to obtain the pivot table.

[0025] However, in the prior art, as the number of data rows in a general data table serving as a data source increases, the number of queries to the data source will increase exponentially, and the amount of calculation required to generate a pivot table is huge and inefficient. At the same time, the prior art does not have a function for reconverting part of the content in an existing pivot table into a general data table, resulting in a poor user experience.

[0026] Against this background technology, the present disclosure provides a method for converting a pivot table to a general data table, which can reduce the amount of calculation when generating a pivot table and improve efficiency, as well as realize the function of reconverting part of the content of the generated pivot table into a general data table, thereby improving the user experience.

[0027] The execution subject of the method for converting a pivot table to a general data table provided in the embodiment of the present disclosure may be a computer or a server, or may also be other electronic devices with data processing capabilities; or, the execution subject of the method may also be a processor (e.g., a central processing unit (CPU)) in the above electronic device; or, the execution subject of the method may also be an application (application, APP) installed in the above electronic device that can implement the function of the method; or, the execution subject of the method may be a functional module or unit in the above electronic device that has the function of the method, etc. The execution subject of the method is not limited here.

[0028] The following is an exemplary description of the method for converting the pivot table and a general data table with reference to the accompanying drawings.

[0029] Figure 1 The flowchart of the method for converting a pivot table to a general data table provided in the embodiment of the present application is shown in FIG. Figure 1 This is only an execution order shown in the embodiment of the present application, and does not represent the only execution order of the conversion method between the pivot table and the general data table. If the final result can be achieved, Figure 1 The steps shown can be performed in parallel or in reverse. Figure 1 As shown, the method may include S101 to S105.

[0030] S101. Receive a first range.

[0031] Exemplarily, the first range may be a cell area selected by a user. For example, a user may select a desired cell area in a user interface provided by a spreadsheet application through an input device (such as a mouse), and the execution subject receives the cell area selected by the user as the first range. There is no restriction on the size and shape of the first range.

[0032] S102: Determine that the first range includes cells of a general data table, and generate structured data according to the cell data of the first range, a target table header field, and a target data area field.

[0033] The structured data is used to indicate the data of the target data area field corresponding to the target header field.

[0034] Exemplarily, the target header field refers to the field corresponding to the header (including row headers and column headers) of the pivot table to be generated, and the target data area field refers to the field corresponding to the data area of ​​the pivot table to be generated.

[0035] Exemplarily, the target table header field and the target data area field may be pre-set by the user according to the expected title field and data area field of the pivot table.

[0036] For example, taking the cells in the first range and their contents such as rows 2 to 5 in Table 1 as an example (it should be noted that the headers are added for ease of understanding), when the user expects to obtain a pivot table that can reflect the sales of each product for each person in each month, the target header fields can be set to include person, month, and product, and the target data area fields can be set to include sales.

[0037] Table 1

[0038] Exemplarily, the structural form of the structured data may be pre-set, and there is no restriction on the structural form. Continuing with the above example, the generated structured data may include: Person & A-&-Product & a-&-Month & January: 10; Person & A-&-Product & b-&-Month & February: 20; Person & C-&-Product & a-&-Month & January: 10; Person & B-&-Product & a-&-Month & February: 10. This structured data may indicate persons, months, products, and sales, and the relationships between them.

[0039] Specifically, when generating structured data, it is necessary to traverse the data source (ie, the data of the cells in the first range) and query the records according to the target header field and the target data area field.

[0040] For example, continuing with the above example, when querying each row of data, it is necessary to query the target header field and the target data area field, a total of 4 information, namely, person, month, product and sales; the data source has a total of 4 rows, and the number of query records required to obtain structured data is a total of 4×4=16 times.

[0041] It is understandable that the amount of structured data cannot exceed the product of the number of types of all header fields corresponding to the target header field in the first range. Taking the above example, the amount of structured data will not exceed (number of people × number of months × number of products) = 3 × 2 × 2 = 12.

[0042] S103: Determine a rendering tree according to the structured data and the preset row title field, the preset column title field, and the preset data area field.

[0043] The rendering tree is used to indicate the row header field, column header field, data area field of the pivot table, and the cell data corresponding to the row header field and column header field.

[0044] Exemplarily, the preset row title field, the preset column title field, and the preset data area field are respectively the row title field, the expected column title field, and the expected data area field expected by the user.

[0045] For example, the structured data can be traversed to query records, and a rendering tree similar to a tree diagram can be generated according to the preset row title field, the preset column title field, and the preset data area field. Continuing with the above example, the preset row title field includes person and product, the preset column title field includes month, and the preset data area field includes sales. The generated rendering tree can be as follows: Figure 2 shown. Figure 2 A schematic diagram of the structure of a rendering tree provided by an embodiment of the present application, wherein the left part is a rendering tree obtained according to a row header field, and the right part is a rendering tree obtained according to a column header field.

[0046] It should be noted that part of the content of the rendering tree obtained according to the row header field and the rendering tree obtained according to the column header field are actually the same.

[0047] It should be noted that the data area field indicated by the rendering tree may be indicated in the attribute information of the rendering tree.

[0048] S104: Generate a pivot table and a mapping relationship according to the rendering tree.

[0049] The mapping relationship is used to indicate the position of the cell in the pivot table corresponding to the node of the rendering tree.

[0050] For example, the rendering tree can be expanded two-dimensionally by the renderer and filled into a worksheet of a spreadsheet application to obtain a pivot table. Continuing with the above example, the generated pivot table is shown in Table 2.

[0051] It should be noted that the data in the last row and last column cannot be directly reflected in the rendering tree. It is necessary to perform a calculation during the rendering process to obtain the total value of January and February.

[0052] Table 2

[0053] In this way, you can complete the conversion of general data table data into PivotTable.

[0054] For example, continuing with the above example, the pivot table shown in Table 2 is Figure 2 The mapping relationship of the rendering tree shown may include the fourth node in the rendering tree obtained according to the row header field (ie Figure 2 ), and the seventh node in the rendering tree obtained according to the column header field (i.e. Figure 2 The 19th row in the right part of the data), the corresponding cell is the cell in the fourth row and fifth column in the PivotTable.

[0055] It should be noted that after obtaining the pivot table, if the user changes the row title field, column title field, or data area title field of the pivot table, it is necessary to redefine the rendering tree and obtain the pivot table again based on the rendering tree.

[0056] And / or, S105, determining that the first range includes cells of the pivot table, and generating a general data table according to the rendering tree corresponding to the pivot table and the mapping relationship.

[0057] For example, taking the first range including the cells in the fourth row and third column to the sixth row and fifth column in Table 2 in the above example as an example, according to the corresponding table 2, Figure 2 The rendering tree shown in Table 2 and the pivot table shown in Table 2 Figure 2 The mapping relationship of the rendering tree shown generates a row of data for each cell of the pivot table in the first range, and after traversing all the cells of the pivot table in the first range, a general data table as shown in Table 3 is obtained.

[0058] Table 3

[0059] In this way, you can convert the existing PivotTable data into a general data table.

[0060] It can be understood that when the first range includes both cells of a general data table and cells of a pivot table, the cells of the general data table can be converted into corresponding pivot tables, and the cells of the pivot table can be converted into corresponding general data tables, which will not be elaborated herein.

[0061] The embodiment of the present application determines that the first range includes cells of a general data table, generates structured data according to the cell data of the first range, the target header field and the target data area field, determines a rendering tree according to the structured data, generates a pivot table according to the rendering tree, and the mapping relationship between the pivot table and the rendering tree, which can reduce the amount of calculation when generating a pivot table and improve efficiency. By determining that the first range includes cells of a pivot table, generating a general data table according to the rendering tree corresponding to the pivot table, and the mapping relationship between the pivot table and the rendering tree, it is possible to realize the function of reconverting part of the content of the generated pivot table into a general data table, thereby improving the user experience.

[0062] It should be noted that the PivotTable includes general total cells and secondary total cells. General cells are cells corresponding to the lowest-level row fields and the lowest-level column fields (such as the cells in the third row and third column, the cells in the third row and fourth column, the cells in the fourth row and third column, the cells in the fourth row and fourth column, etc. in Table 2). Secondary total cells are other cells in the data area of ​​the PivotTable (such as the cells in the last row of the total row, the cells in the last column of the total column in Table 2, and the total cells for the sales of person A in January in the second row and third column in Table 2, etc.).

[0063] In the prior art, the value of any cell in the general total cell and the secondary total cell needs to be traversed through the data source to query the summary records. As the number of data source rows in a general data table increases, the number of queries on the data source will increase exponentially, and the amount of calculation is huge. In addition, for invalid cells, additional removal processing is required after obtaining the pivot table, which increases the amount of calculation.

[0064] The amount of calculation of the method of this embodiment is only related to the number of data source rows when generating structured data. In subsequent processing, the amount of calculation is only related to the number of row header fields, column header fields, and data area fields, and there is no step of removing invalid cells, which greatly reduces the amount of calculation.

[0065] The effect of this application is described in detail below using a specific example.

[0066] Figure 3 This is a schematic diagram of a general data table. Figure 4 for Figure 3 Schematic diagram of the PivotTable corresponding to the general data table. The user expects to know how much each salesperson sold each product each month. The target header fields include the salesperson's name, product, and date, and the target data area fields include the total sales.

[0067] The existing technology needs to be Figure 3 After performing (6×5+6+1)×(4+1)×17=3145 queries and summaries on the second to eighteenth rows (first range) of the general data table shown, and then removing invalid cells, we get the following: Figure 4 . Among them, 6×5+6+1 represents the total number of rows in the PivotTable without removing invalid cells, 4+1 represents the total number of columns in the PivotTable without removing invalid cells, and 17 is the number of data rows in the first range.

[0068] Even if the data of the secondary total cell is obtained by summing up the data of the general total cell, it is necessary to perform 6×5×4×17=2040 queries and summaries on the first range just to obtain the general total cell.

[0069] This embodiment requires Figure 3 The second to eighteenth rows (first range) of the general data table shown in the figure are queried and summarized 17×4=68 times, and the following is obtained: Figure 5 The structured data shown, Figure 5 for Figure 3 A schematic diagram of the structured data corresponding to the general data table of FIG. 55 is then performed on the structured data to generate a rendering tree including 55 nodes (excluding duplicate nodes), and then the rendering tree is expanded and filled into the cell to obtain the following Figure 4 As shown in the pivot table, since the rendering tree has 55 nodes, the two-dimensional expansion of the rendering tree through the renderer needs to be executed 55 times, and the query summary times are 68+55+55=178 times in total, and the amount of calculation is much less than the existing technology.

[0070] It can be seen from this example that the embodiments of the present application can reduce the amount of calculation required to convert a general data table into a pivot table.

[0071] In some possible implementations, when the first range includes cells of multiple pivot tables, general data tables corresponding to the multiple pivot tables are generated for the cells of the multiple pivot tables based on the rendering trees corresponding to the multiple pivot tables and the corresponding mapping relationships.

[0072] Exemplarily, taking the first range including some cells of pivot table pivottableA and some cells of pivot table pivottableB as an example, for some cells of pivot table pivottableA, a general data table is generated according to the rendering tree tree1 of pivot table pivottableA and the mapping relationship between pivot table pivottableA and rendering tree tree1; for some cells of pivot table pivottableB, another general data table is generated according to the rendering tree tree2 of pivot table pivottableB and the mapping relationship between pivot table pivottableB and rendering tree tree2.

[0073] For example, the method of generating the general data table can refer to the specific implementation of the aforementioned S105, which will not be described in detail here.

[0074] In this embodiment, when the first range includes cells of multiple pivot tables, general data tables corresponding to the multiple pivot tables are generated for the cells of the multiple pivot tables based on the rendering trees respectively corresponding to the multiple pivot tables and the respectively corresponding mapping relationships, so that corresponding general data tables can be generated for different pivot tables in the first range.

[0075] In some possible implementations, the method may further include: In response to the operation of updating the data of the first cell, a target node is determined according to the rendering tree and the mapping relationship; data corresponding to the target node in the rendering tree is updated according to the updated data of the first cell; and the pivot table is updated according to the updated rendering tree.

[0076] The first cell is a cell in the pivot table, and the target node is a node that directly or indirectly references a node corresponding to the first cell.

[0077] Exemplarily, the operation of updating the data of the first cell may be an operation of editing the data of the first cell.

[0078] Exemplarily, the target node may be determined according to the hierarchical relationship and mapping relationship between nodes in the rendering tree.

[0079] For example, in the previous example Figure 2Taking the rendering tree shown in FIG. 1 and the pivot table shown in Table 2 as examples, when the first cell is a cell in the fourth row and fifth column, according to the mapping relationship, it can be obtained that the first cell corresponds to the fourth node in the rendering tree obtained according to the row header field (i.e. Figure 2 ), and the seventh node in the rendering tree obtained according to the column header field (i.e. Figure 2 According to the hierarchical relationship between nodes in the rendering tree, we can get the target nodes including Figure 2 The second node in the rendering tree obtained according to the row header field (i.e. Figure 2 ), the sixth node in the rendering tree obtained according to the column header field (i.e. Figure 2 center right part, line 16).

[0080] Exemplarily, the data of the node corresponding to the first cell may be updated to the updated data of the first cell, and then the data of the target node that references the data of the node corresponding to the first cell may be updated.

[0081] For example, continuing with the above example, when the user updates the data of the first cell from 20 to 30, the first cell can be mapped to the fourth node in the rendering tree obtained according to the row title field (i.e. Figure 2 ), and the seventh node in the rendering tree obtained according to the column header field (i.e. Figure 2 The data of the 19th line in the right part of the middle is updated to 30, and the fourth node in the rendering tree (i.e. Figure 2 The data in row 3 of the table is updated to 40. Figure 2 The second node in the rendered tree obtained based on the row header field (i.e. Figure 2 ), the sixth node in the rendering tree obtained according to the column header field (i.e. Figure 2 The data in the right part (row 16) is updated to 60.

[0082] Exemplarily, after the rendering tree is updated, the pivot table can be regenerated according to the updated rendering tree to update the pivot table. The specific implementation of generating the pivot table according to the rendering tree will not be described in detail.

[0083] In this embodiment, in response to an operation of updating the data of a first cell in a pivot table, according to a rendering tree and a mapping relationship, a target node that directly or indirectly references a node corresponding to the first cell is determined; according to the updated data of the first cell, data corresponding to the target node in the rendering tree is updated; according to the updated rendering tree, the pivot table is updated, so that editing and data updating of the pivot table can be achieved.

[0084] In some possible implementations, the mapping relationship is also used to indicate the cell format corresponding to the node of the rendering tree and the cell format of other cells in the pivot table. The method may further include: In response to an operation of setting a cell format for the first cell, the mapping relationship is updated, and the pivot table is updated according to the updated mapping relationship.

[0085] The first cell is a cell in the pivot table.

[0086] Exemplarily, other cells may be cells without numerical data. For example, taking the pivot table shown in Table 2 as an example, other cells may include all cells in the first column and the second column, cells in the fourth row, the fifth row, and the sixth row in the third column, cells in the third row, the seventh row, and the eighth row in the fourth column, and cells in the first row of the fifth column.

[0087] For example, the mapping relationship may indicate the cell format corresponding to each rendering tree node and the cell format of each other cell. It can be understood that the mapping relationship may indicate the format of each cell in the pivot table.

[0088] For example, taking the pivot table shown in Table 2 as an example, when the user sets the cell format of the cells in the fourth row and third column, the fourth row and fourth column, the fifth row and third column, and the fifth row and fourth column to "cell background color is green", the cell format corresponding to the rendering tree node corresponding to the cells in the fourth row and third column, the fourth row and fourth column, the fifth row and third column, and the fifth row and fourth column in the mapping relationship is updated to "cell background color is green", and the cell formats in the pivot table are updated according to the updated mapping relationship.

[0089] This embodiment allows the mapping relationship to also indicate the cell format corresponding to the node of the rendering tree and the cell format of other cells in the pivot table. In response to the operation of setting the cell format for the first cell, the mapping relationship is updated, and the pivot table is updated according to the updated mapping relationship. This ensures that each cell in the pivot table, after the cell format is set, will not lose the set format or the cell format will change due to forced redrawing of the pivot table as in the prior art. The cell format can be accurately and durably maintained.

[0090] The effect of this application is described in detail below using a specific example.

[0091] Figure 6 This is a diagram of a pivot table before forced redrawing. Figure 7 This is a diagram of a pivot table after forced redrawing.

[0092] For example, in the prior art (such as Microsoft Office's electronic spreadsheet application Excel), after obtaining a pivot table, the user formats the pivot table and sets the border format of some cells in the pivot table, and obtains the following: Figure 6 After refreshing the pivot table data, i.e. forcing a redraw, you can get the following: Figure 7 The PivotTable shown.

[0093] contrast Figure 6 and Figure 7 , it can be seen that the cell format of the pivot table has changed dramatically. If the user sets the cell border format of the pivot table to highlight the content in the thick border for easy viewing by the user, the forced redraw will not meet the user's expectations, and the user experience is poor. However, the method of this embodiment will not cause the problem of cell format changes.

[0094] In some possible implementations, the method may further include: In response to the operation of changing the row header field and the column header field of the pivot table, the rendering tree is updated; it is determined that the row header field and the column header field of the pivot table after the change contain the row header field and the column header field of the pivot table before the change; according to the updated rendering tree, the cell format corresponding to the node of the rendering tree indicated in the mapping relationship corresponding to the rendering tree before the update, and the cell format of other cells in the pivot table, the pivot table is updated.

[0095] Exemplarily, when a user changes the hierarchical relationship between row header fields, or changes the hierarchical relationship between column header fields, or swaps row header fields with column header fields, or adds row header fields and column header fields, the row header fields and column header fields of the changed PivotTable will still contain the row header fields and column header fields of the PivotTable before the change.

[0096] For example, continuing with the PivotTable shown in Table 2, when the user adds a "Year" field to the column header field (such as including 2023 and 2024), the row header field and column header field of the changed PivotTable will still include the row header field and column header field of the PivotTable before the change.

[0097] It can be understood that when the user changes the hierarchical relationship between row header fields, or changes the hierarchical relationship between column header fields, or swaps row header fields with column header fields, the updated rendering tree is changed, but compared with the rendering tree before the update, only the hierarchical order in the nodes of the rendering tree is changed.

[0098] For example, continuing with the pivot table shown in Table 2, after changing the row title field to include personnel (including personnel A, personnel B, personnel C), months (including month January, month February), and the column title field to products (including product a, product b), there will be a node in the updated rendering tree such as Person A-Month January-Product a corresponding to sales of 10, which is essentially the same as the node in the rendering tree before the update that corresponds to Person A-Product a-Month January with sales of 10.

[0099] For another example, continuing with the pivot table shown in Table 2, when the "year" field (including 2023 and 2024) is added to the column header field, the updated rendering tree will still have a node with a sales volume of 10 corresponding to Person A-Product a-Month 1.

[0100] In this way, according to the updated rendering tree, the cell formats corresponding to the rendering tree nodes indicated in the mapping relationship corresponding to the rendering tree before the update, and the cell formats of other cells in the pivot table, the cell formats corresponding to the rendering tree nodes indicated in the mapping relationship corresponding to the rendering tree before the update can be set for the pivot table cells corresponding to the nodes that have not substantially changed. In this way, when the row title field and column title field of the pivot table after the change contain the row title field and column title field of the pivot table before the change, the original cell formats are not eliminated, thereby improving the user experience.

[0101] In some possible implementations, after updating the pivot table according to the updated mapping relationship, the method may further include S201 to S203.

[0102] S201. Determine a row header field, a column header field, and a data area field corresponding to the first cell according to a rendering tree, a mapping relationship, and a first cell.

[0103] Exemplarily, for the first cell with numerical data, its corresponding row header field, column header field and data area field may be determined; for the first cell without numerical data, subsequent processing may not be performed.

[0104] For example, the pivot table shown in Table 2 and Figure 2 Taking the rendering tree shown as an example, when the first cell is the cell in the fourth row and third column, according to the mapping relationship, it can be known that the cell in the fourth row and third column has no corresponding node in the rendering tree, that is, the cell in the fourth row and third column has no numerical data, and there is no need to determine the row header field, column header field, and data area field corresponding to the cell in the fourth row and third column.

[0105] When the first cell is the cell in the fourth row and fourth column, according to the mapping relationship, it can be known that the cell in the fourth row and fourth column corresponds to the fifth node in the rendering tree obtained according to the row title field (i.e. Figure 2 ), and the eighth node in the rendering tree obtained from the column header field (i.e. Figure 2 In the 22nd row on the right side of the graph, according to the rendering tree, we can see that the row title fields corresponding to the first cell include Person A and Product B, the column title fields include Month February, and the data area field is Sales.

[0106] When the first cell is the cell in the second row and fifth column, according to the mapping relationship, it can be known that the cell in the fourth row and fourth column corresponds to the first node in the rendering tree obtained according to the row title field (i.e. Figure 2 In the third row on the left side of the image, according to the rendering tree, we can see that the row title field corresponding to the first cell includes Person A, and the data area field is Sales.

[0107] S202. According to the row header field, column header field, and data area field corresponding to the first cell, in the general data table corresponding to the pivot table, determine the fourth cell corresponding to the row header field, column header field, and data area field corresponding to the first cell.

[0108] Exemplarily, a cell in a general data table that satisfies the row title field, column title field, and data area field corresponding to the first cell may be determined as the fourth cell.

[0109] For example, continuing with the above example, when the first cell is a cell in the fourth row and third column, the fourth cell does not exist.

[0110] When the first cell is the cell in the fourth row and fourth column, the fourth cell includes the cells in the first column, the second column, the third column and the sixth column in the third row of the general data table shown in Table 1 corresponding to the pivot table of Table 2.

[0111] When the first cell is the cell in the second row and fifth column, the fourth cell includes the cells in the first row and first column, the first row and sixth column, the second row and first column, and the second row and sixth column in the general data table shown in Table 1 corresponding to the pivot table of Table 2.

[0112] S203. Set a cell format corresponding to the first cell for the fourth cell.

[0113] Exemplarily, the cell format corresponding to the first cell may be pre-set, and there is no limitation on this. For example, when the first cell format is a cell background color of red, the cell format corresponding to the first cell may be a cell background color of red, a cell background color of green, a cell border of thick red line, etc.

[0114] This embodiment determines the row header field, column header field and data area field corresponding to the first cell according to the rendering tree, the mapping relationship and the first cell; determines the fourth cell corresponding to the row header field, column header field and data area field corresponding to the first cell in the general data table corresponding to the pivot table according to the row header field, column header field and data area field corresponding to the first cell; sets the cell format corresponding to the first cell for the fourth cell, and the cell format set by the user on the pivot table can be fed back to the corresponding general data table.

[0115] In some possible implementations, the method may further include: In response to an operation of editing data of a cell of a general data table in a first range, the structured data is updated; the rendering tree is updated according to the updated structured data; and the pivot table is updated according to the updated rendering tree.

[0116] Exemplarily, the implementation of this embodiment may refer to the specific implementations of S102 to S104, which will not be described in detail here.

[0117] This embodiment updates the structured data in response to the operation of editing the data of the cells of the general data table in the first range; updates the rendering tree according to the updated structured data; and updates the pivot table according to the updated rendering tree, so as to achieve real-time update of the pivot table.

[0118] In some possible implementations, updating the pivot table according to the updated rendering tree may include: According to the rendering tree before and after the update, and the mapping relationship, a fifth cell is determined, where the fifth cell is a cell in the pivot table whose data needs to be updated; and the data in the fifth cell is updated.

[0119] For example, the rendering tree before the update can be compared with the rendering tree after the update to obtain the node where the data has changed, and the fifth cell corresponding to the node where the data has changed can be obtained according to the mapping relationship, and the data of the fifth cell can be updated. In this way, only the cells in the pivot table where the data needs to be updated can be updated, without the need to generate the pivot table again according to the rendering tree.

[0120] This embodiment determines the fifth cell according to the rendering tree before and after the update, and the mapping relationship. The fifth cell is the cell whose data in the pivot table needs to be updated. When the data in the fifth cell is updated, there is no need to regenerate the pivot table according to the updated rendering tree, thereby improving the updating efficiency of the pivot table.

[0121] Although the present application provides method operation steps such as embodiments or flow charts, more or fewer operation steps may be included based on conventional or non-creative labor. The order of steps listed in this embodiment is only one way of executing the order of many steps and does not represent the only execution order. When the actual device or client product is executed, it can be executed in the order of the method shown in this embodiment or the accompanying drawings or in parallel (for example, in a parallel processor or multi-threaded processing environment).

[0122] like Figure 8 As shown, the embodiment of the present application also provides a device for converting a pivot table and a general data table. The device includes: a receiving module 801 and a conversion module 802.

[0123] The receiving module 801 is configured to receive a first range.

[0124] The conversion module 802 is used to determine that the first range includes cells of a general data table, and generate structured data according to the cell data of the first range, the target header field and the target data area field, wherein the structured data is used to indicate the data of the target data area field corresponding to the target header field; determine a rendering tree according to the structured data and the preset row header field, the preset column header field and the preset data area field, wherein the rendering tree is used to indicate the row header field, the column header field and the data area field of the pivot table, and the cell data corresponding to the row header field and the column header field; generate a pivot table and a mapping relationship according to the rendering tree, wherein the mapping relationship is used to indicate the position of the node of the rendering tree corresponding to the cell in the pivot table; and / or determine that the first range includes cells of the pivot table, and generate a general data table according to the rendering tree corresponding to the pivot table and the mapping relationship.

[0125] The beneficial effects and specific implementation methods of the present device embodiment can be referred to the aforementioned method embodiment, and will not be described in detail here.

[0126] Some modules in the apparatus described in the present application can be described in the general context of computer executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, classes, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0127] The devices or modules described in the above application embodiments can be implemented by computer chips or entities, or by products with certain functions. For the convenience of description, the above devices are described in various modules according to their functions. When implementing the embodiments of the present application, the functions of each module can be implemented in the same or multiple software and / or hardware. Of course, the module that implements a certain function can also be implemented by combining multiple sub-modules or sub-units.

[0128] The methods, devices or modules described in this application can be implemented in the form of computer-readable program codes. The controller can be implemented in any appropriate manner. For example, the controller can take the form of a microprocessor or processor and a computer-readable medium storing computer-readable program codes (such as software or firmware) that can be executed by the (micro)processor, logic gates, switches, application-specific integrated circuits (English: Application Specific Integrated Circuit; Abbreviation: ASIC), programmable logic controllers and embedded microcontrollers. Examples of controllers include but are not limited to the following microcontrollers: ARC 625D, Atmel AT91SAM, Microchip PIC18F26K20 and Silicone Labs C8051F320. The memory controller can also be implemented as part of the control logic of the memory. Those skilled in the art also know that in addition to implementing the controller in the form of pure computer-readable program codes, the controller can be implemented in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers and embedded microcontrollers by logically programming the method steps. Therefore, this controller can be considered as a hardware component, and the devices included in it for implementing various functions can also be regarded as structures within the hardware component. Or even, the means for realizing various functions may be regarded as both a software module for realizing the method and a structure within a hardware component.

[0129] An embodiment of the present application further provides a device, comprising: a processor; a memory for storing processor executable instructions; when the processor executes the executable instructions, the method described in the embodiment of the present application is implemented.

[0130] The embodiments of the present application also provide a non-volatile computer-readable storage medium on which a computer program or instruction is stored. When the computer program or instruction is executed, the method described in the embodiments of the present application is implemented.

[0131] In addition, each functional module in each embodiment of the present invention may be integrated into one processing module, or each module may exist independently, or two or more modules may be integrated into one module.

[0132] The above storage media include but are not limited to random access memory (RAM), read-only memory (ROM), cache, hard disk drive (HDD) or memory card. The memory can be used to store computer program instructions.

[0133] It can be seen from the description of the above implementation methods that those skilled in the art can clearly understand that the present application can be implemented by means of software plus necessary hardware. Based on this understanding, the technical solution of the present application can be essentially or partly reflected in the prior art in the form of a software product, or it can be reflected in the implementation process of data migration. The computer software product can be stored in a storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a mobile terminal, a server, or a network device, etc.) to execute the methods described in the various embodiments of the present application or certain parts of the embodiments.

[0134] The various embodiments in this specification are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. All or part of this application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld devices or portable devices, tablet devices, mobile communication terminals, multi-processor systems, microprocessor-based systems, programmable electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc.

[0135] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit the present application. Although the present application has been described in detail with reference to the aforementioned embodiments, a person of ordinary skill in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some or all of the technical features thereof may be replaced by equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the present application.

Claims

1. A method for converting a pivot table to a general data table, characterized in that: include: receiving a first range; Determine that the first range includes cells of a general data table, and generate structured data according to the cell data of the first range, a target table header field, and a target data area field, wherein the structured data is used to indicate data of a target data area field corresponding to the target table header field; Determine a rendering tree according to the structured data and the preset row header field, the preset column header field, and the preset data area field, wherein the rendering tree is used to indicate the row header field, the column header field, the data area field, and the cell data corresponding to the row header field and the column header field of the pivot table; Generate a pivot table and a mapping relationship according to the rendering tree, where the mapping relationship is used to indicate the position of the cell in the pivot table corresponding to the node of the rendering tree; And / or, determining that the first range includes cells of a pivot table, and generating a general data table according to a rendering tree corresponding to the pivot table and a mapping relationship.

2. The method according to claim 1, characterized in that The method further includes: when the first range includes cells of multiple pivot tables, for the cells of the multiple pivot tables, based on the rendering trees respectively corresponding to the multiple pivot tables and the respectively corresponding mapping relationships, respectively generating general data tables corresponding to the multiple pivot tables.

3. The method according to claim 1, characterized in that The method further comprises: In response to the operation of updating the data of the first cell, determining a target node according to the rendering tree and the mapping relationship; Update the data corresponding to the target node in the rendering tree according to the updated data of the first cell; Update the pivot table based on the updated rendering tree; The first cell is a cell in a pivot table, and the target node is a node that directly or indirectly references a node corresponding to the first cell.

4. The method according to claim 1, characterized in that: The mapping relationship is also used to indicate the cell format corresponding to the node of the rendering tree, and the cell format of other cells in the pivot table. The method further includes: In response to an operation of setting a cell format for a first cell, a mapping relationship is updated, and a pivot table is updated according to the updated mapping relationship, wherein the first cell is a cell in the pivot table.

5. The method according to claim 4, characterized in that After updating the pivot table according to the updated mapping relationship, the method further includes: Determine, according to the rendering tree, the mapping relationship, and the first cell, a row header field, a column header field, and a data area field corresponding to the first cell; According to the row title field, column title field and data area field corresponding to the first cell, in the general data table corresponding to the pivot table, determine a fourth cell corresponding to the row title field, column title field and data area field corresponding to the first cell; A cell format corresponding to the first cell is set for the fourth cell.

6. The method according to claim 1, characterized in that The method further comprises: In response to an operation of editing data of a cell of the general data table in the first range, the structured data is updated; the rendering tree is updated according to the updated structured data; and the pivot table is updated according to the updated rendering tree.

7. The method according to claim 6, characterized in that The updating of the pivot table according to the updated rendering tree includes: Determine a fifth cell according to the rendering tree before and after the update, and the mapping relationship, where the fifth cell is a cell in the pivot table whose data needs to be updated; The data of the fifth cell is updated.

8. A device for converting a pivot table to a general data table, characterized in that: include: A receiving module, configured to receive a first range; a conversion module, configured to determine that the first range includes cells of a general data table, and generate structured data according to the cell data of the first range, a target table header field, and a target data area field, wherein the structured data is used to indicate data of a target data area field corresponding to the target table header field; Determine a rendering tree according to the structured data and the preset row header field, the preset column header field, and the preset data area field, wherein the rendering tree is used to indicate the row header field, the column header field, the data area field, and the cell data corresponding to the row header field and the column header field of the pivot table; generate a pivot table and a mapping relationship according to the rendering tree, wherein the mapping relationship is used to indicate the position of the cell in the pivot table corresponding to the node of the rendering tree; And / or, used to determine that the first range includes cells of a pivot table, and generate a general data table according to a rendering tree corresponding to the pivot table and a mapping relationship.

9. A device for executing a method for converting a pivot table to a general data table, characterized in that: include: processor; a memory for storing processor-executable instructions; When the processor executes the executable instructions, the method according to any one of claims 1 to 7 is implemented.

10. A non-volatile computer-readable storage medium, characterized in that: The device comprises a computer program or an instruction for storing the computer program or the instruction, which, when executed, enables the method according to any one of claims 1 to 7 to be implemented.

Images