Method, device and equipment for processing formula in spreadsheet and storage medium

Abstract

The application discloses a formula processing method and device in an electronic form, an equipment and a storage medium, relates to the computer application technical field, and establishes the mapping relationship table between the coordinate information of the cell where each formula in the electronic form document is located, the formula, and the coordinate information of the cell referenced by the formula. When a cell in the electronic form is modified, the coordinate information of the modified cell in the electronic form and the value of the modified cell are obtained, the target formula referencing the modified cell and the coordinate information of the target cell where the target formula is located are quickly found from the mapping relationship table according to the coordinate information of the modified cell in the electronic form, and then the target formula is analyzed, and the result value of the target formula is calculated. In this way, when a cell is modified each time, it is not necessary to poll and analyze all formulas, the formula processing efficiency is improved, the formula processing time is reduced, and the page operation fluency is improved.

Description

Technical Field

[0001] This application relates to the field of computer application technology, and in particular to a method, apparatus, device and storage medium for processing formulas in spreadsheets. Background Technology

[0002] Spreadsheets are being used more and more frequently in daily life and work. Users can create various simple or complex spreadsheet documents for information recording, statistical analysis, and other purposes. With the development of internet technology, in the office environment, the processing of spreadsheets is increasingly shifting from the traditional local processing method of local files and spreadsheet programs to online processing methods using remote services and browsers.

[0003] Spreadsheets offer a large number of formulas and functions to meet users' data calculation needs. Formulas are typically stored in a cell and reference the values ​​of one or more cells as variables. When a user modifies a cell in a spreadsheet document, the remote service needs to poll all formulas in the spreadsheet document, parse each formula, and then calculate the value of the formula that directly or indirectly references the modified cell.

[0004] When there are many formulas in the spreadsheet, the repeated polling and parsing calculations can cause the calculation of related formulas to take a long time each time a cell is modified, resulting in lag on the page. Summary of the Invention

[0005] This application provides a method, apparatus, device, and storage medium for processing formulas in spreadsheets, so as to improve formula processing efficiency, reduce formula processing time, and improve page operation smoothness.

[0006] Firstly, this application provides a method for processing formulas in a spreadsheet, including:

[0007] When a formula is entered in a spreadsheet, a mapping table is created between the coordinate information of the cell containing the formula, the formula itself, and the coordinate information of the cells referenced by the formula.

[0008] Get the coordinates of the modified cell in the spreadsheet, as well as the value of the modified cell;

[0009] Find the target formula that references the modified cell from the mapping table, and the coordinate information of the target cell where the target formula is located;

[0010] Calculate the result value of the target formula based on the modified cell value and the target formula, and update the value of the target cell to the result value.

[0011] Secondly, this application also provides a processing apparatus for formulas in a spreadsheet, comprising:

[0012] The mapping relationship table creation module is used to create a mapping relationship table between the coordinate information of the cell containing the formula, the formula, and the coordinate information of the cells referenced by the formula when a formula is entered in a spreadsheet.

[0013] The modified cell information acquisition module is used to acquire the coordinate information of the modified cell in the spreadsheet, as well as the value of the modified cell.

[0014] The target formula information lookup module is used to find the target formula that references the modified cell from the mapping relationship table, as well as the coordinate information of the target cell where the target formula is located;

[0015] The target cell update module is used to calculate the result value of the target formula based on the modified cell value and the target formula, and update the value of the target cell to the result value.

[0016] Thirdly, this application also provides an electronic device, including:

[0017] One or more processors;

[0018] Memory, used to store one or more programs;

[0019] When the one or more programs are executed by the one or more processors, the one or more processors implement the method for processing formulas in a spreadsheet as provided in the first aspect of this application.

[0020] Fourthly, this application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the method for processing formulas in a spreadsheet as described in the first aspect of this application.

[0021] Fifthly, this application also provides a computer program product, characterized in that it includes a computer program that, when executed by a processor, implements the processing method for formulas in a spreadsheet as provided in the first aspect of this application.

[0022] The method for processing formulas in spreadsheets provided in this application establishes a mapping table between the coordinates of the cell containing the formula, the formula itself, and the coordinates of the cells referenced by the formula. When a cell in the spreadsheet is modified, the coordinates of the modified cell and the value of the modified cell are obtained. Based on the coordinates of the modified cell, the target formula referencing the modified cell and the coordinates of the target cell containing the target formula can be quickly found from the mapping table of all formulas in the spreadsheet. Then, the target formula is parsed, the result value of the target formula is calculated based on the value of the modified cell, and the value of the target cell is updated to the result value. In this way, it is not necessary to poll and parse all formulas every time a cell is modified, which improves formula processing efficiency, reduces formula processing time, and improves page operation smoothness.

[0023] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of this application, nor is it intended to limit the scope of this application. Other features of this application will become readily apparent from the following description. Attached Figure Description

[0024] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0025] Figure 1 A flowchart illustrating a method for processing formulas in a spreadsheet, as provided in this application embodiment;

[0026] Figure 2 A flowchart illustrating another method for processing formulas in a spreadsheet, provided as an embodiment of this application;

[0027] Figure 3 A flowchart illustrating another method for processing formulas in a spreadsheet, provided as an embodiment of this application;

[0028] Figure 4 A flowchart illustrating another method for processing formulas in a spreadsheet, provided as an embodiment of this application;

[0029] Figure 5 A schematic diagram of the structure of a formula processing device in a spreadsheet provided in an embodiment of this application;

[0030] Figure 6 This is a schematic diagram of the structure of an electronic device provided as an embodiment of this application.

[0031] The accompanying drawings illustrate specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0032] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0033] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0034] Furthermore, it should be emphasized that the acquisition, storage, use, and processing of data in the technical solution of this application all comply with the relevant provisions of national laws and regulations.

[0035] Terminology Explanation:

[0036] EXCEL: In this article, EXCEL refers to the document corresponding to an xlsx (or xls) file, and not specifically to Microsoft software.

[0037] SHEET_ID: An Excel spreadsheet contains multiple sheets, each with a unique identifier called SHEET_ID. The SHEET_ID is generated when the sheet is created and will not change thereafter.

[0038] ROW_INDEX: The row coordinate of the cell, the position of the cell in the vertical direction. The row coordinate of the first cell is 0, the row coordinate of the second cell is 1, and so on.

[0039] COLUMN_INDEX: The column coordinate of the cell, the horizontal position of the cell. The column coordinate of the first cell is 0, the column coordinate of the second cell is 1, and so on.

[0040] Key-value pairs are data where the key is unique and cannot be repeated, and the value can be data from various data structures. In applications, data can be retrieved from the value based on the key.

[0041] In existing technology, when a user modifies a cell in a spreadsheet document, the remote service needs to poll all formulas in the spreadsheet document each time a modification is made. It parses each formula, determines if it references the currently modified cell, and if so, calculates the formula and updates the value of the cell containing that formula. Then, it checks if the cell containing this formula is referenced by other formulas; if so, it parses those other formulas, calculates them, and updates the values ​​of the cells containing those formulas. When there are many formulas in the spreadsheet, this multiple polling, parsing, and calculation process leads to lengthy calculation times for each cell modification, causing page lag.

[0042] To address the aforementioned issues, this application proposes the following technical concept: When formulas are entered in a spreadsheet, a mapping table is created between the coordinates of the cell containing the formula, the formula itself, and the coordinates of the cells referenced by the formula. When a cell is modified, the formula referencing the modified cell can be quickly located based on the coordinates of the modified cell, the result of the formula can be calculated, and the value of the cell containing the formula can be updated. This eliminates the need to poll and parse all formulas, improving formula processing efficiency, reducing formula processing time, and enhancing the smoothness of page operation.

[0043] The technical solution of this application and how the technical solution of this application solves the above-mentioned technical problems are described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of this application will now be described with reference to the accompanying drawings.

[0044] Figure 1 A flowchart illustrating a method for processing formulas in a spreadsheet, as provided in this application embodiment, is shown below. Figure 1 As shown, this embodiment is applicable to processing formulas in spreadsheets during online editing. This method can be executed by the formula processing device provided in this embodiment, which can be implemented in software and / or hardware, and is typically configured in a server, such as... Figure 1 As shown, processing formulas in a spreadsheet can include the following steps:

[0045] S110. When formulas are entered in a spreadsheet, create a mapping table between the coordinates of the cell containing the formula, the formula itself, and the coordinates of the cells referenced by the formula.

[0046] In this embodiment, the formula is typically written into a cell, and it defines referenced cells, using the data in those cells for calculation. The result is then displayed in the cell containing the formula. For example, the formula might be written to the last cell of a column or row, referencing all cells preceding it in that column or row to sum the data in all cells before it.

[0047] When a formula is entered in a spreadsheet, the server records the coordinates of the cell containing the formula, the formula itself, and the coordinates of the cells referenced by the formula. It then establishes a mapping table between each pair of these coordinates. In this way, the server records all formulas in the spreadsheet document, the coordinates of the cells containing the formulas, and the coordinates of the cells referenced by the formulas, and creates a mapping table for each formula. Therefore, the server can quickly find the corresponding formula and the coordinates of the cell containing the formula based on the coordinates of the cell referenced by the formula.

[0048] In the embodiments of this application, the specific form of the formula is not limited; it can be a user-defined formula or a preset formula provided by the spreadsheet. The embodiments of this application do not impose any limitations on this. In the embodiments of this application, the formula can reference cells in the current SHEET (i.e., the spreadsheet containing the formula) or reference cells in other SHEETs across spreadsheets. The embodiments of this application do not impose any limitations on this.

[0049] In this embodiment, for formulas that only reference the current SHEET, the cell coordinate information may include the cell's row coordinates (ROW_INDEX) and column coordinates (COLUMN_INDEX) in the spreadsheet. For example, the coordinate information of the cell in the first row and second column is represented as ["0, 1"]. For formulas that reference across spreadsheets, the cell coordinate information includes the unique identifier of the spreadsheet containing the cell (SHEET_ID), the cell's row coordinates (ROW_INDEX) and column coordinates (COLUMN_INDEX) in that spreadsheet. For example, the coordinate information of the cell in the first row and second column of the first SHEET is represented as {"1": ["0, 1"]}.

[0050] S120. Obtain the coordinates of the modified cell in the spreadsheet, as well as the value of the modified cell.

[0051] In this embodiment, when a user edits a spreadsheet, the server obtains the coordinates of the modified cell within the spreadsheet, as well as the value of the modified cell. For example, when editing a spreadsheet, a user may modify one cell at a time or multiple cells at a time; this embodiment does not limit the scope of the modification.

[0052] S130. Find the target formula that references the modified cell from the mapping table, as well as the coordinate information of the target cell where the target formula is located.

[0053] In this embodiment, after obtaining the coordinate information of the modified cell in the spreadsheet and the value of the modified cell, the coordinate information of the referenced cell that is the same as the coordinate information of the modified cell is found in the mapping table of all formulas in the spreadsheet document, that is, it is determined that the modified cell is referenced by a formula. Then, the target formula that references the modified cell and the coordinate information of the target cell where the target formula is located are found in the mapping table.

[0054] For example, in some embodiments of this application, S130, finding the target formula referencing the modified cell from the mapping table, and the coordinate information of the target cell where the target formula is located, includes sub-steps S131-S132:

[0055] S131. Using the coordinates of the cell to be modified as an index, search the mapping table to see if there is a referenced cell with the same coordinates as the cell to be modified.

[0056] For example, using the coordinates of the cell to be modified as an index, the system searches the coordinates of all formula-referenced cells in the spreadsheet document's mapping table for a matching coordinate information. If a matching coordinate information exists, it indicates that the modified cell is referenced by a formula, and step S132 is executed. If no matching coordinate information exists, it means the modified cell is not referenced by any formula, and no further processing is required.

[0057] S132. Based on the target coordinate information, find the target formula that references the modified cell from the mapping table, as well as the coordinate information of the target cell where the target formula is located.

[0058] If the mapping table contains the target coordinates of a referenced cell that match the coordinates of the modified cell, then the target formula referencing the modified cell, along with the coordinates of the target cell containing the target formula, is retrieved from the mapping table based on these target coordinates. For example, the mapping table can be used to find the target formula referencing the modified cell, and the coordinates of the target cell containing the target formula, based on the coordinates of the modified cell (i.e., the target coordinates of the referenced cell).

[0059] S140. Calculate the result value of the target formula based on the modified cell value and the target formula, and update the value of the target cell to the result value.

[0060] In this embodiment, after determining the target formula referencing the modified cell and the coordinate information of the target cell containing the target formula, the result value of the target formula is calculated based on the modified cell value and the target formula, and the value of the target cell is updated to the result value. For example, the value of the cell referenced by the target formula is updated to the modified cell value, and the target formula is parsed and its result value is calculated. The target cell is determined based on the coordinate information of the target cell containing the target formula, and the value of the target cell is updated to the result value. For example, the coordinate information of the target cell containing the target formula is {"1":["5,1"]}, and the target formula is {"f"::"1":["3,1"]+"1":["2,1"]"}, meaning the target formula references cells ["3,1"] and ["2,1"] in SHEET_1. When the value of the cell with coordinate information {"1":["3,1"]} is modified, the target formula is parsed, the value of "1":["3,1"] is updated, the new result value is calculated, and the result value is updated in the target cell.

[0061] The method for processing formulas in a spreadsheet provided in this application establishes a mapping table between the coordinates of the cell containing the formula, the formula itself, and the coordinates of the cells referenced by the formula. When a cell in the spreadsheet is modified, the coordinates of the modified cell and the value of the modified cell are obtained. Based on the coordinates of the modified cell, the target formula referencing the modified cell and the coordinates of the target cell containing the target formula can be quickly found from the mapping table of all formulas in the spreadsheet. Then, the target formula is parsed, the result value of the target formula is calculated based on the value of the modified cell, and the value of the target cell is updated to the result value. In this way, it is not necessary to poll and parse all formulas every time a cell is modified, which improves the efficiency of formula processing, reduces the formula processing time, and improves the smoothness of page operation.

[0062] To enable those skilled in the art to more clearly understand the technical solution of this application, the steps in the above embodiments will be described in detail below.

[0063] Figure 2 A flowchart illustrating another method for processing formulas in a spreadsheet, as provided in this application embodiment, is shown below. Figure 2 As shown, the methods for processing formulas in a spreadsheet include:

[0064] S201. Use the coordinates of the cell containing the formula as the key, and the coordinates of the formula and the cells referenced by the formula as the value to construct the first key-value pair.

[0065] In this embodiment, when a formula is entered in the spreadsheet, the server records the coordinates of the cell containing the formula, the formula itself, and the coordinates of the cells referenced by the formula. This records the coordinates of each formula in the spreadsheet document, the coordinates of the cell containing the formula, and the coordinates of the cells referenced by the formula.

[0066] For each formula, the coordinates of the cell containing the formula are used as the key, and the coordinates of the formula and the cells referenced by the formula are used as the value to construct the first key-value pair.

[0067] For example, the key in the first key-value pair is formatted as: "ROW_INDEX, COLUMN_INDEX", where ROW_INDEX and COLUMN_INDEX represent the row and column coordinates of the cell containing the formula.

[0068] The format of the value in the first key-value pair is: {"f": "formula", "relevant": {"SHEET_ID": ["ROW_INDEX, COLUMN_INDEX", "ROW_INDEX, COLUMN_INDEX"]}, where "formula" after "f" is the formula itself, "relevant" contains the coordinates of the cell referenced by the formula, SHEET_ID is the offset identifier of the spreadsheet containing the cell referenced by the formula, and ROW_INDEX and COLUMN_INDEX are the row and column coordinates of the cell referenced by the formula in the spreadsheet. For example, in this embodiment, the formula references two cells, and the coordinates of the two cells are separated by a separator.

[0069] For example, in some embodiments of this application, the value in the first key-value pair is stored in JSON format. A JSON file is a file with the .JSON extension; JSON is short for JavaScript Object Notation. JSON is a lightweight data-interchange format based on a subset of ECMAScript (the JavaScript specification defined by the W3C), using a text format completely independent of programming languages ​​to store and represent data. Its concise and clear hierarchical structure makes JSON an ideal data-interchange language. It is easy for humans to read and write, and also easy for machines to parse and generate, effectively improving network transmission efficiency.

[0070] S202. Use the coordinates of the cell referenced by the formula as the key and the coordinates of the cell containing the formula as the value to construct a second key-value pair.

[0071] For each formula, the coordinates of the cell referenced by the formula are used as the key, and the coordinates of the cell containing the formula are used as the value, to construct a second key-value pair.

[0072] For example, the key in the second key-value pair is formatted as: "ROW_INDEX, COLUMN_INDEX", where ROW_INDEX and COLUMN_INDEX represent the row and column coordinates of the cell referenced by the formula.

[0073] The format of the value in the second key-value pair is: {"SHEET_ID": ["ROW_INDEX, COLUMN_INDEX"]}, where SHEET_ID is the offset identifier of the spreadsheet in which the cell containing the formula is located, and ROW_INDEX and COLUMN_INDEX are the row and column coordinates of the cell in which the formula is located.

[0074] For example, in an embodiment of this application, the value in the second key-value pair is stored in JSON format.

[0075] S203. Store the first key-value pair and the second key-value pair to obtain a mapping table between the coordinate information of the cell containing the formula, the formula, and the coordinate information of the cells referenced by the formula.

[0076] Store the first and second key-value pairs corresponding to each formula, save them to the server, and obtain a mapping table between the coordinate information of the cell where the formula is located, the formula, and the coordinate information of the cell referenced by the formula.

[0077] S204. Obtain the coordinates of the modified cell in the spreadsheet, as well as the value of the modified cell.

[0078] In this embodiment, when a user edits a spreadsheet, the server obtains the coordinates of the modified cell within the spreadsheet, as well as the value of the modified cell. For example, when editing a spreadsheet, a user may modify one cell at a time or multiple cells at a time; this embodiment does not limit the scope of the modification.

[0079] S205. Search the key of the second key-value pair for a first target key that is the same as the coordinate information of the modified cell.

[0080] After obtaining the coordinates of the modified cell in the spreadsheet and the value of the modified cell, the coordinates of the modified cell are combined into a key, which is used as an index to search for the key of the second key-value pair of all formulas, and to find whether there is a first target key that is the same as the coordinates of the modified cell.

[0081] If the second key-value pair contains a first target key whose coordinates match those of the modified cell, it means the modified cell is referenced by the formula corresponding to the first target key, and step S206 is executed. If the second key-value pair does not contain a key whose coordinates match those of the modified cell, it means the modified cell is not referenced by any formula, and the processing flow ends.

[0082] S206. Based on the first target key, read the first target value in the second key-value pair of the target to which the first target key belongs, and obtain the coordinate information of the cell where the target formula is located.

[0083] If the key of the second key-value pair contains the same first target key as the coordinate information of the modified cell, it means that the modified cell is referenced by the formula corresponding to the first target key. Based on the first target key, the JSON file in the target second key-value pair to which the first target key belongs is read, and the first target value, that is, the coordinate information of the cell where the target formula is located, is read from it.

[0084] S207. Using the coordinates of the cell containing the target formula as an index, find the second target key in the key of the first key-value pair that has the same coordinates as the cell containing the target formula.

[0085] After reading the coordinates of the cell containing the target formula, the coordinates of the cell containing the target formula are combined into a key, which is used as an index to search for the key of the first key-value pair of all formulas, and then to find the second target key that is the same as the coordinates of the cell containing the target formula.

[0086] S208. Based on the second target key, read the second target value in the first key-value pair of the target to which the second target key belongs, and obtain the target formula that references the modified cell.

[0087] Using the second target key, read the JSON file in the first key-value pair of the target to which the second target key belongs, read the second target value from it, and obtain the target formula that references the modified cell.

[0088] S209. Calculate the result value of the target formula based on the modified cell value and the target formula, and update the value of the target cell to the result value.

[0089] After obtaining the target formula referencing the modified cell and the coordinates of the target cell containing the target formula through the aforementioned steps, the result value of the target formula is calculated based on the modified cell value and the target formula, and the value of the target cell is updated to the result value. For example, the value of the cell referenced by the target formula is updated to the modified cell value, the target formula is parsed, and the result value of the target formula is calculated. The target cell is determined based on the coordinates of the target cell containing the target formula, and the value of the target cell is updated to the result value.

[0090] In some embodiments of this application, editing a spreadsheet also involves performing row and column operations on the spreadsheet. For example, row and column operations include inserting rows, inserting columns, deleting rows, and deleting columns. When performing row and column operations on a spreadsheet, the coordinates of the cell containing the formula or the coordinates of the cell referenced by the formula may be affected; therefore, the formulas need to be processed. Figure 3A flowchart illustrating another method for processing formulas in a spreadsheet, as provided in this application embodiment, is shown below. Figure 3 As shown, the methods for processing formulas in this spreadsheet include:

[0091] S310. When formulas are entered in a spreadsheet, create a mapping table between the coordinates of the cell containing the formula, the formula itself, and the coordinates of the cells referenced by the formula.

[0092] For example, in this embodiment of the application, when a formula is entered in the spreadsheet, the server records the coordinate information of the cell containing the formula, the formula itself, and the coordinate information of the cell referenced by the formula. In this way, the coordinate information of each formula in the spreadsheet document, the coordinate information of the cell containing the formula, and the coordinate information of the cell referenced by the formula are recorded.

[0093] For each formula, construct a first key-value pair using the coordinates of the cell containing the formula as the key and the coordinates of the formula and the cells referenced by the formula as the value. Construct a second key-value pair using the coordinates of the cells referenced by the formula as the key and the coordinates of the cell containing the formula as the value. Store the first and second key-value pairs to obtain a mapping table between the coordinates of the cell containing the formula, the formula, and the coordinates of the cells referenced by the formula.

[0094] S320. Determine if a spreadsheet contains an object formula that stores the coordinates of cells affected by row and column operations.

[0095] In this embodiment, all formulas in the spreadsheet are polled to determine whether there is an object formula in the spreadsheet whose cell coordinate information is affected by row and column operations.

[0096] For example, in a specific embodiment of this application, any formula in the spreadsheet is taken as the object formula. It is determined whether the row coordinate of the target row for the row-column operation is less than the row coordinate of the cell containing the object formula, or whether the column coordinate of the target column for the row-column operation is less than the column coordinate of the cell containing the object formula. If the row coordinate of the target row for the row-column operation is less than the row coordinate of the cell containing the object formula, or the column coordinate of the target column is less than the column coordinate of the cell containing the object formula, it indicates that the coordinate information of the cell containing the object formula is affected by the row-column operation, and step S330 is executed. If the row coordinate of the target row for the row-column operation is greater than the row coordinate of the cell containing the object formula, or the column coordinate of the target column is greater than the column coordinate of the cell containing the object formula, it indicates that the coordinate information of the cell containing the object formula is not affected by the row-column operation, and step S340 is executed.

[0097] S330. Update the coordinates of the cell containing the object formula.

[0098] If the row coordinate of the target row is less than the row coordinate of the cell containing the object formula, or the column coordinate of the target column is less than the column coordinate of the cell containing the object formula, it indicates that the coordinate information of the cell containing the object formula is affected by the row and column operations. Therefore, the coordinate information of the cell containing the object formula is updated. For example, in this embodiment, the coordinate information of the cell containing the object formula in the key of the first key-value pair is updated, and the coordinate information of the cell containing the object formula in the value of the second key-value pair is also updated. After updating the coordinate information of the cell containing the object formula, step S340 is executed.

[0099] S340. Determine whether the cell referenced by the formula of the object is affected by row and column operations.

[0100] If the row coordinate of the target row is greater than the row coordinate of the cell containing the object formula, or the column coordinate of the target column is greater than the column coordinate of the cell containing the object formula, it means that the coordinate information of the cell containing the object formula is not affected by the row and column operation. In this case, it is determined whether the cell referenced by the object formula is affected by the row and column operation, or after updating the coordinate information of the cell containing the object formula, it is determined whether the cell referenced by the object formula is affected by the row and column operation.

[0101] For example, in a specific embodiment of this application, it is determined whether the row coordinate of the target row of the row-column operation is less than the row coordinate of the cell referenced by the object formula, or whether the column coordinate of the target column of the row-column operation is less than the column coordinate of the cell referenced by the object formula. If the row coordinate of the target row of the row-column operation is less than the row coordinate of the cell referenced by the object formula, or the column coordinate of the target column of the row-column operation is less than the column coordinate of the cell referenced by the object formula, it indicates that the coordinate information of the cell referenced by the object formula is affected by the row-column operation, and step S350 is executed. If the row coordinate of the target row of the row-column operation is greater than the row coordinate of the cell referenced by the object formula, or the column coordinate of the target column of the row-column operation is greater than the column coordinate of the cell referenced by the object formula, it indicates that the coordinate information of the cell referenced by the object formula is not affected by the row-column operation, and step S320 is returned to be executed.

[0102] S350, Update the coordinate information of the cell referenced by the object formula.

[0103] If the row coordinate of the target row for the row-column operation is less than the row coordinate of the cell referenced by the object formula, or the column coordinate of the target column for the row-column operation is less than the column coordinate of the cell referenced by the object formula, it indicates that the coordinate information of the cell referenced by the object formula is affected by the row-column operation, and the coordinate information of the cell referenced by the object formula is updated. For example, in a specific embodiment of this application, the affected cells in the first key-value pair (value) corresponding to the object formula and the affected cells in the second key-value pair (key) corresponding to the object formula are updated.

[0104] If the row coordinate of the target row of the row-column operation is greater than the row coordinate of the cell referenced by the object formula, or the column coordinate of the target column of the row-column operation is greater than the column coordinate of the cell referenced by the object formula, it means that the coordinate information of the cell referenced by the object formula is not affected by the row-column operation. Then, return to step S320, determine the new formula from the spreadsheet document as the object formula, and repeat the above steps S320-S350 until all affected formulas are updated.

[0105] In some embodiments of this application, editing a spreadsheet also involves dragging cells. If the dragged cell contains a formula, dragging will affect the coordinate information of the cell containing the formula. Therefore, the formula needs to be processed. Figure 4 A flowchart illustrating another method for processing formulas in a spreadsheet, as provided in this application embodiment, is shown below. Figure 4 As shown, the methods for processing formulas in this spreadsheet include:

[0106] S410. When formulas are entered in a spreadsheet, create a mapping table between the coordinates of the cell containing the formula, the formula itself, and the coordinates of the cells referenced by the formula.

[0107] For example, in this embodiment of the application, when a formula is entered in the spreadsheet, the server records the coordinate information of the cell containing the formula, the formula itself, and the coordinate information of the cell referenced by the formula. In this way, the coordinate information of each formula in the spreadsheet document, the coordinate information of the cell containing the formula, and the coordinate information of the cell referenced by the formula are recorded.

[0108] For each formula, construct a first key-value pair using the coordinates of the cell containing the formula as the key and the coordinates of the formula and the cells referenced by the formula as the value. Construct a second key-value pair using the coordinates of the cells referenced by the formula as the key and the coordinates of the cell containing the formula as the value. Store the first and second key-value pairs to obtain a mapping table between the coordinates of the cell containing the formula, the formula, and the coordinates of the cells referenced by the formula.

[0109] S420. Determine if the dragged cell contains a formula.

[0110] In this embodiment, the coordinate information of the dragged cell is determined, and using the coordinate information of the dragged cell as an index, the coordinate information of the cell containing the same formula as the dragged cell is searched in the above mapping table. For example, the coordinate information of the dragged cell is combined into a key, and the key of the first key-value pair of all formulas is searched for a key that is the same as the key formed by combining the coordinate information of the dragged cell. If it exists, it means that the dragged cell contains a formula, and step 430 is executed. If not, it means that the dragged cell does not contain a formula, but the operation of dragging a cell is equivalent to modifying both the dragged cell and the target cell after dragging. Therefore, it is necessary to determine whether the dragged cell and the target cell after dragging are referenced by a formula, i.e., to execute steps S120-S140 in the aforementioned embodiments of this application, or steps S204-S209 in the aforementioned embodiments of this application. This part of the solution has been described in detail in the aforementioned embodiments, and will not be repeated here.

[0111] S430. Update the coordinates of the cell containing the formula in the dragged cell.

[0112] If the dragged cell does not contain a formula, then the coordinate information of the cell containing the formula in the dragged cell is updated. For example, in one specific embodiment of this application, the coordinate information of the cell containing the formula in the first key-value pair (key) and the coordinate information of the cell containing the formula in the second key-value pair (value) corresponding to the formula in the dragged cell are updated.

[0113] This application also provides a device for processing formulas in spreadsheets. Figure 5 This application provides a schematic diagram of the structure of a formula processing device in a spreadsheet, as shown in the embodiments of the present application. Figure 5 As shown, the formula processing device in a spreadsheet includes:

[0114] The mapping relationship table creation module 501 is used to create a mapping relationship table between the coordinate information of the cell where the formula is located, the formula, and the coordinate information of the cell referenced by the formula when a formula is entered in the spreadsheet.

[0115] The modified cell information acquisition module 502 is used to acquire the coordinate information of the modified cell in the spreadsheet, as well as the value of the modified cell.

[0116] The target formula information lookup module 503 is used to look up the target formula that references the modified cell from the mapping relationship table, as well as the coordinate information of the target cell where the target formula is located;

[0117] The target cell update module 504 is used to calculate the result value of the target formula based on the modified cell value and the target formula, and update the value of the target cell to the result value.

[0118] In some embodiments of this application, the coordinate information of the cell referenced by the formula includes the identifier of the spreadsheet in which the cell referenced by the formula is located, and the coordinates of the cell referenced by the formula within the spreadsheet.

[0119] In some embodiments of this application, the mapping table establishment module 501 includes:

[0120] The first key-value pair construction submodule is used to construct the first key-value pair by using the coordinate information of the cell where the formula is located as the key and the coordinate information of the formula and the cell referenced by the formula as the value.

[0121] The second key-value pair construction submodule is used to construct a second key-value pair by using the coordinate information of the cell referenced by the formula as the key and the coordinate information of the cell where the formula is located as the value.

[0122] The storage submodule is used to store the first key-value pair and the second key-value pair, and to obtain a mapping table between the coordinate information of the cell where the formula is located, the formula, and the coordinate information of the cell referenced by the formula.

[0123] In some embodiments of this application, the values ​​in the first key-value pair and the second key-value pair are stored in JSON format.

[0124] In some embodiments of this application, the target formula information lookup module 503 includes:

[0125] The first search submodule is used to search the mapping table for the coordinate information of a referenced cell that is the same as the coordinate information of the modified cell, using the coordinate information of the modified cell as an index.

[0126] The second search submodule is used to search for the target formula referencing the modified cell and the coordinate information of the target cell in the mapping table if the target coordinate information of the referenced cell is the same as that of the modified cell.

[0127] In some embodiments of this application, the first lookup submodule includes:

[0128] The first target key lookup unit is used to use the coordinate information of the cell to be modified as an index to search for whether there is a first target key in the key of the second key-value pair that is the same as the coordinate information of the cell to be modified.

[0129] In some embodiments of this application, the second search submodule includes:

[0130] The first reading unit is configured to, if the key of the second key-value pair contains a first target key that is identical to the coordinate information of the modified cell, then based on the first target key, read the first target value in the target second key-value pair to which the first target key belongs, and obtain the coordinate information of the cell where the target formula is located.

[0131] The second target key lookup unit is used to use the coordinate information of the cell where the target formula is located as an index to look up a second target key from the key of the first key-value pair that is the same as the coordinate information of the cell where the target formula is located.

[0132] The second reading unit is used to read the second target value in the target first key-value pair to which the second target key belongs, based on the second target key, to obtain the target formula that references the modified cell.

[0133] In some embodiments of this application, the apparatus for processing formulas in a spreadsheet further includes:

[0134] The first judgment module is used to determine whether there is an object formula in the spreadsheet containing the coordinate information of the cell affected by the row and column operation when performing row and column operations on the spreadsheet.

[0135] The first update module is used to update the coordinate information of the cell containing the object formula if there is an object formula in the spreadsheet whose cell coordinate information is affected by the row and column operation.

[0136] The second judgment module is used to determine whether the cell referenced by the object formula is affected by the row and column operation if there is no object formula in the spreadsheet that contains the coordinate information of the cell affected by the row and column operation, or after updating the coordinate information of the cell where the object formula is located.

[0137] The second update module is used to update the coordinate information of the cell referenced by the object formula if the cell is affected by the row and column operations.

[0138] The first return execution module is used to return to the step of determining whether there is an object formula in the spreadsheet that is affected by the row and column operation if the cell referenced by the object formula is not affected by the row and column operation, until all affected formulas are updated.

[0139] In some embodiments of this application, the first determination module includes:

[0140] The first judgment submodule is used to take any formula in the spreadsheet as the object formula and determine whether the row coordinate of the target row of the row and column operation is less than the row coordinate of the cell where the object formula is located, or whether the column coordinate of the target column of the row and column operation is less than the column coordinate of the cell where the object formula is located.

[0141] The first determining submodule is used to determine that the coordinate information of the cell where the object formula is located is affected by the row and column operations if the row coordinate of the target row is less than the row coordinate of the cell where the object formula is located, or the column coordinate of the target column is less than the column coordinate of the cell where the object formula is located.

[0142] In some embodiments of this application, the first update module includes:

[0143] The first update submodule is used for the key in the first key-value pair corresponding to the object formula and the value in the second key-value pair corresponding to the object formula.

[0144] In some embodiments of this application, the second determination module includes:

[0145] The second judgment submodule is used to determine whether the row coordinate of the target row of the row and column operation is less than the row coordinate of the cell referenced by the object formula, or whether the column coordinate of the target column of the row and column operation is less than the column coordinate of the cell referenced by the object formula.

[0146] The second determining submodule is used to determine that the coordinate information of the cell referenced by the object formula is affected by the row and column operation if the row coordinate of the target row of the row and column operation is less than the row coordinate of the cell referenced by the object formula, or the column coordinate of the target column of the row and column operation is less than the column coordinate of the cell referenced by the object formula.

[0147] In some embodiments of this application, the second update module includes:

[0148] The second update submodule is used to update the value in the first key-value pair corresponding to the object formula and the key in the second key-value pair corresponding to the object formula.

[0149] In some embodiments of this application, the apparatus for processing formulas in a spreadsheet further includes:

[0150] The third judgment module is used to determine whether the dragged cell contains a formula when performing a cell drag operation on a spreadsheet.

[0151] The third update module is used to update the coordinate information of the cell containing the formula in the dragged cell if the dragged cell contains a formula.

[0152] The second return execution module is used to return to the execution steps of obtaining the coordinate information of the modified cell in the spreadsheet and the value of the modified cell if the dragged cell does not contain a formula.

[0153] In some embodiments of this application, the third update module includes:

[0154] The third update submodule is used to update the key in the first key-value pair corresponding to the formula in the dragged cell and the value in the second key-value pair corresponding to the formula in the dragged cell.

[0155] The above-mentioned spreadsheet formula processing device can execute the spreadsheet formula processing method provided in any embodiment of this application, and has the corresponding functional modules and beneficial effects of executing the spreadsheet formula processing method.

[0156] This application provides an electronic device, which may be the server described in the foregoing embodiments of this application. Figure 6 This is a schematic diagram of an electronic device provided for embodiments of this application. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the present application described and / or claimed herein.

[0157] like Figure 6As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0158] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0159] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as the processing methods for formulas in a spreadsheet.

[0160] In some embodiments, the method for processing formulas in a spreadsheet may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the method for processing formulas in a spreadsheet described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to perform the method for processing formulas in a spreadsheet by any other suitable means (e.g., by means of firmware).

[0161] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0162] Computer programs used to implement the methods of this application may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0163] In the context of this application, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium can be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0164] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0165] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0166] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0167] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the method for processing formulas in a spreadsheet as provided in any embodiment of this application.

[0168] In the implementation of the computer program product, computer program code for performing the operations of this application can be written in one or more programming languages ​​or a combination thereof. Programming languages ​​include object-oriented programming languages ​​such as Java, Smalltalk, and C++, as well as conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0169] It should be understood that the various forms of processes shown above can be used to rearrange, add, or delete steps. For example, the steps described in this application can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this application can be achieved, and this is not limited herein.

[0170] The specific embodiments described above do not constitute a limitation on the scope of protection of this application. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this application should be included within the scope of protection of this application.

Claims

1. A method for processing formulas in a spreadsheet, characterized in that, include: When a formula is entered in a spreadsheet, the coordinates of the cell containing the formula are used as the key, and the coordinates of the formula and the cells referenced by the formula are used as the value to construct the first key-value pair. The coordinates of the cell referenced by the formula are used as the key, and the coordinates of the cell containing the formula are used as the value to construct a second key-value pair; Store the first key-value pair and the second key-value pair to obtain a mapping table between the coordinate information of the cell where the formula is located, the formula, and the coordinate information of the cell referenced by the formula; Get the coordinates of the modified cell in the spreadsheet, as well as the value of the modified cell; Using the coordinates of the cell to be modified as an index, search the mapping table to see if there is a referenced cell with the same coordinates as the modified cell. If the mapping table contains the target coordinate information of a referenced cell that is identical to the coordinate information of the modified cell, then the target formula referencing the modified cell and the coordinate information of the target cell containing the target formula are retrieved from the mapping table based on the target coordinate information. The target formula is written to the last cell of a column or row and references all cells in that column or row that precede that cell; Calculate the result value of the target formula based on the modified cell value and the target formula, and update the value of the target cell to the result value; The method further includes: When dragging cells in a spreadsheet, check if the dragged cell contains a formula. If the dragged cell contains a formula, update the key in the first key-value pair corresponding to the formula in the dragged cell and the value in the second key-value pair corresponding to the formula in the dragged cell. If the dragged cell does not contain a formula, return to the step of retrieving the coordinates of the modified cell in the spreadsheet and the value of the modified cell.

2. The method for processing formulas in a spreadsheet according to claim 1, characterized in that, The coordinate information of the cell referenced by the formula includes the identifier of the spreadsheet in which the cell referenced by the formula is located, and the coordinates of the cell referenced by the formula within the spreadsheet.

3. The method for processing formulas in a spreadsheet according to claim 1, characterized in that, The values ​​in the first key-value pair and the second key-value pair are stored in JSON format.

4. The method for processing formulas in a spreadsheet according to claim 1, characterized in that, Using the coordinates of the cell to be modified as an index, the mapping table is searched to determine if there exists a referenced cell with the same coordinates as the modified cell, including: Using the coordinates of the cell to be modified as an index, search the key of the second key-value pair to see if there is a first target key that is the same as the coordinates of the cell to be modified.

5. The method for processing formulas in a spreadsheet according to claim 4, characterized in that, If the mapping table contains target coordinate information for a referenced cell that is identical to the coordinate information of the modified cell, then based on the target coordinate information, the mapping table is used to find the target formula referencing the modified cell, and the coordinate information of the target cell containing the target formula, including: If the key of the second key-value pair contains a first target key that is the same as the coordinate information of the cell being modified, then based on the first target key, the first target value in the target second key-value pair to which the first target key belongs is read to obtain the coordinate information of the cell where the target formula is located; Using the coordinates of the cell containing the target formula as an index, find the second target key that is the same as the coordinates of the cell containing the target formula from the key of the first key-value pair; Based on the second target key, read the second target value in the first key-value pair to which the second target key belongs, and obtain the target formula that references the modified cell.

6. The method for processing formulas in a spreadsheet according to claim 1, characterized in that, Also includes: When performing row and column operations on a spreadsheet, determine whether there is an object formula in the spreadsheet that contains the coordinate information of the cell affected by the row and column operations; If the spreadsheet contains an object formula whose cell coordinates are affected by the row and column operations, then update the coordinates of the cell containing the object formula. If there is no object formula in the spreadsheet that contains the coordinate information of the cell affected by the row and column operation, or after updating the coordinate information of the cell containing the object formula, determine whether the cell referenced by the object formula is affected by the row and column operation; If the cell referenced by the object formula is affected by the row and column operations, then update the coordinate information of the cell referenced by the object formula; If the cell referenced by the object formula is not affected by the row and column operation, then return to the step of determining whether there is an object formula in the spreadsheet that contains the coordinate information of the cell affected by the row and column operation, until all affected formulas are updated.

7. The method for processing formulas in a spreadsheet according to claim 6, characterized in that, An object formula for determining whether the spreadsheet contains coordinate information of the cells affected by the row and column operations includes: Take any formula in the spreadsheet as the object formula, and determine whether the row coordinate of the target row of the row and column operation is less than the row coordinate of the cell where the object formula is located, or whether the column coordinate of the target column of the row and column operation is less than the column coordinate of the cell where the object formula is located. If the row coordinate of the target row is less than the row coordinate of the cell where the object formula is located, or the column coordinate of the target column is less than the column coordinate of the cell where the object formula is located, then it is determined that the coordinate information of the cell where the object formula is located is affected by the row and column operations.

8. The method for processing formulas in a spreadsheet according to claim 6, characterized in that, Update the coordinates of the cell containing the object formula, including: Update the key in the first key-value pair corresponding to the object formula and the value in the second key-value pair corresponding to the object formula.

9. The method for processing formulas in a spreadsheet according to claim 6, characterized in that, Determining whether the cell referenced by the object formula is affected by the row and column operations includes: Determine whether the row coordinate of the target row for the row and column operation is less than the row coordinate of the cell referenced by the object formula, or whether the column coordinate of the target column for the row and column operation is less than the column coordinate of the cell referenced by the object formula. If the row coordinate of the target row of the row-column operation is less than the row coordinate of the cell referenced by the object formula, or the column coordinate of the target column of the row-column operation is less than the column coordinate of the cell referenced by the object formula, then it is determined that the coordinate information of the cell referenced by the object formula is affected by the row-column operation.

10. The method for processing formulas in a spreadsheet according to claim 6, characterized in that, Updating the coordinate information of the cell referenced by the object formula includes: Update the value in the first key-value pair corresponding to the object formula and the key in the second key-value pair corresponding to the object formula.

11. A formula processing device for spreadsheets, characterized in that, include: The mapping relationship table creation module is used to create a mapping relationship table between the coordinate information of the cell containing the formula, the formula, and the coordinate information of the cells referenced by the formula when a formula is entered in a spreadsheet. The modified cell information acquisition module is used to acquire the coordinate information of the modified cell in the spreadsheet, as well as the value of the modified cell. The target formula information lookup module is used to find the target formula that references the modified cell from the mapping relationship table, as well as the coordinate information of the target cell where the target formula is located; The target formula is written to the last cell of a column or row and references all cells in that column or row that precede that cell; The target cell update module is used to calculate the result value of the target formula based on the modified cell value and the target formula, and update the value of the target cell to the result value; The mapping relationship table establishment module includes: The first key-value pair construction submodule is used to construct the first key-value pair by using the coordinate information of the cell where the formula is located as the key and the coordinate information of the formula and the cell referenced by the formula as the value. The second key-value pair construction submodule is used to construct a second key-value pair by using the coordinate information of the cell referenced by the formula as the key and the coordinate information of the cell where the formula is located as the value. The storage submodule is used to store the first key-value pair and the second key-value pair, and to obtain a mapping table between the coordinate information of the cell where the formula is located, the formula, and the coordinate information of the cell referenced by the formula. The target formula information lookup module includes: The first search submodule is used to search the mapping table for the coordinate information of a referenced cell that is the same as the coordinate information of the modified cell, using the coordinate information of the modified cell as an index. The second search submodule is used to search for the target formula referencing the modified cell and the coordinate information of the target cell in the mapping table if there is a target coordinate information of a referenced cell that is the same as the coordinate information of the modified cell in the mapping table. The device further includes: The third judgment module is used to determine whether the dragged cell contains a formula when performing a cell drag operation on a spreadsheet. The third update module is used to update the coordinate information of the cell containing the formula in the dragged cell if the dragged cell contains a formula. The second return execution module is used to return to the execution step of obtaining the coordinate information of the modified cell in the spreadsheet and the value of the modified cell if the dragged cell does not contain a formula. The third update module includes: The third update submodule is used to update the key in the first key-value pair corresponding to the formula in the dragged cell and the value in the second key-value pair corresponding to the formula in the dragged cell.

12. The apparatus for processing formulas in a spreadsheet according to claim 11, characterized in that, Also includes: The first judgment module is used to determine whether there is an object formula in the spreadsheet containing the coordinate information of the cell affected by the row and column operation when performing row and column operations on the spreadsheet. The first update module is used to update the coordinate information of the cell containing the object formula if there is an object formula in the spreadsheet whose cell coordinate information is affected by the row and column operation. The second judgment module is used to determine whether the cell referenced by the object formula is affected by the row and column operation if there is no object formula in the spreadsheet that contains the coordinate information of the cell affected by the row and column operation, or after updating the coordinate information of the cell where the object formula is located. The second update module is used to update the coordinate information of the cell referenced by the object formula if the cell is affected by the row and column operations. The first return execution module is used to return to the step of determining whether there is an object formula in the spreadsheet that is affected by the row and column operation if the cell referenced by the object formula is not affected by the row and column operation, until all affected formulas are updated.

13. An electronic device, characterized in that, include: One or more processors; Memory, used to store one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the method for processing formulas in a spreadsheet as described in any one of claims 1-10.

14. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the method for processing formulas in a spreadsheet as described in any one of claims 1-10.

15. A computer program product, characterized in that, It includes a computer program that, when executed by a processor, implements the method for processing formulas in a spreadsheet according to any one of claims 1-10.

Images