An efficient formula coordination method and apparatus in a collaborative table

Abstract

This application discloses an efficient formula collaboration method and apparatus in collaborative tables, relating to the field of online collaborative editing technology. The method converts formulas in the collaborative table into structured formula information and determines the formula impact information. When performing structural change operations such as adding or deleting rows and columns, the affected formulas are located based on the formula impact information, and the formula references are automatically updated using an index adjustment algorithm without generating additional formula setting atomic operations, while caching relevant data. Simultaneously, formula reversibility is determined, irreversible formulas are selected, and their structured formula information is cached. When an undo operation is triggered, a reverse structural change operation is performed, and reversible formulas are automatically restored using the index adjustment algorithm. Only irreversible formulas are restored by generating and executing formula setting atomic operations, thus restoring the table data or deleting newly added rows and columns. This solves the problems of existing technologies, such as a surge in the number of atomic operations caused by structural changes, high network bandwidth consumption, high computational overhead of the collaborative engine, and significant processing latency.

Description

Technical Field

[0001] This application relates to the field of online collaborative editing technology, and in particular to an efficient formula collaboration method and apparatus in collaborative tables. Background Technology

[0002] With the rapid popularization of online collaborative work, collaborative spreadsheets have become a core tool for data editing, real-time multi-person collaboration, and business report processing. In collaborative spreadsheet systems, structural changes such as adding and deleting rows and columns occur frequently, and the numerous formula references in cells need to be updated synchronously as the row and column positions change.

[0003] Current collaborative spreadsheets typically employ a processing model that completely separates structural changes from formula adjustments. In this model, structural change operations only modify the table's row and column layout, without carrying any formula reference adjustment logic. After the structural change is complete, the system iterates through and parses the formulas of all affected cells, recalculates and generates the adjusted formula strings, and then generates a separate atomic operation for each changed cell's formula, sending these operations sequentially to the server. The server then forwards these operations to each client to complete state synchronization. During this process, the server is only responsible for forwarding and sequencing the atomic operations; it does not participate in formula calculation or adjustment.

[0004] The completely separate processing model described above works well when the number of formulas is small, but it has significant drawbacks in scenarios involving large-scale data and complex formula calculations. As the scale of table data increases, a single structural change often affects dozens or even thousands of formulas. In addition to the atomic operations for structural changes, the system generates a large number of corresponding atomic operations for formula settings, leading to an explosive growth in the number of atomic operations. A large number of atomic operations significantly increases network bandwidth consumption and substantially increases the computational overhead and processing latency of the collaboration engine in operation sorting, conflict detection, state merging, and data synchronization. This reduces the response speed in multi-user collaboration scenarios and affects system stability, scalability, and the overall user experience. Summary of the Invention

[0005] In this embodiment of the application, an efficient formula collaboration method in a collaboration table is provided, which solves the problems in the prior art such as a surge in the number of atomic operations caused by structural changes, large network bandwidth consumption, high computational overhead of the collaboration engine, and significant processing delay.

[0006] In a first aspect, embodiments of this application provide an efficient formula collaboration method in a collaborative table, including: step 101, converting the formulas in the collaborative table into structured formula information and determining formula impact information; wherein, the formula impact information is used to mark the formula adjustment area affected by the structural change operation;

[0007] Step 102: When the structure change operation is a row or column addition or deletion operation, the affected table range is determined based on the formula impact information. The index adjustment algorithm is used to judge and adjust the references of formulas within the affected table range, and the automatic update of formula references is completed without generating additional formula setting atomic operations. The new state of the formula is determined by the semantics of the structural change operation, and the execution parameters and formula reference adjustment data of this structural change operation are retained; If the structure change operation is a row or column deletion operation, the table data to be deleted is extracted from the original data and cached in the backup data; if the structure change operation is a row or column addition operation, the position, quantity, and filling data of the newly added rows and columns are recorded and cached in the backup data. Step 103: While performing the structural change operation, based on the structured formula information, the execution parameters of the structural change operation, and the formula reference adjustment data, perform a reversibility judgment on each adjusted formula, identify irreversible formulas that cannot be automatically recovered through reverse structural change operations, and cache their structured formula information in the backup data. Step 104: When the undo operation is triggered, generate and execute the reverse structure change operation corresponding to the structure change operation, and realize the automatic recovery of reversible formulas through the index adjustment algorithm; if the original structure change operation is a row and column deletion operation, extract and restore the deleted table data from the backup data; if the original structure change operation is a row and column addition operation, delete the rows and columns added in step 102 and the data filled in them; based on the structured formula information cached in the backup data, generate and execute formula setting atomic operations only for irreversible formulas to realize the recovery of irreversible formulas.

[0008] In one possible implementation, the structured formula information is IFormulaInfo, which includes a reference mapping table and an array of formula fragments; The reference mapping table stores reference information including start and end rows and columns, cross-table identifier, row and column span, and relative / absolute attributes; The formula fragment array records the text content of the formula and the reference key pointing to the reference mapping table in sequence.

[0009] In one possible implementation, the execution parameters of the structural change operation include at least the operation type, worksheet unique identifier, starting row and column index, and the number of rows and columns to be added / deleted. The formula reference adjustment data includes at least a list of affected formulas, the reference adjustment determination result, and the position and status information after the reference adjustment.

[0010] In one possible implementation, the step of judging and adjusting the references to formulas within the affected table range using an index adjustment algorithm includes: An index adjustment is required only if the reference type is a cell reference, the reference range is a single worksheet, the referenced worksheet is the same as the worksheet being operated on, the reference is valid, or it is not a whole row / column reference.

[0011] In one possible implementation, the index adjustment algorithm includes a single-point deletion adjustment function, a range deletion adjustment function, and a single-point insertion adjustment function; The single-point deletion adjustment function is used to offset and delete or mark invalid the index of a single cell reference based on the positional relationship between the single cell reference and the deletion range. The range deletion adjustment function is used to adjust the starting index and range size of the range reference or mark it as an invalid reference based on the positional relationship between the range reference and the range to be deleted. The single-point insertion adjustment function is used to offset the index of the cell reference based on the positional relationship between the cell reference and the insertion range.

[0012] In one possible implementation, the reversibility determination for each adjusted formula, identifying irreversible formulas that cannot be automatically recovered through reverse structural change operations, and caching their structured formula information in backup data includes: The reversibility determination is achieved by restoring the determination function through a formula that simulates a reverse insertion / deletion operation; The conditions under which the formula is automatically restored through the reverse structural change operation are: the formula is an empty formula or has no references, or all references to the formula meet the restoreability conditions. The recoverability condition is that the reference satisfies any of the following conditions: The reference is invalid. The referenced worksheet is inconsistent with the worksheet on which the structural change operation was performed; When deleting a row, the reference is set to the entire column; when deleting a column, the reference is set to the entire row. The scope of the structural change is entirely before / after the reference scope, or the scope of the structural change is entirely within the reference scope and does not touch the start or end point of the reference scope; If any reference in the formula does not meet the recoverability condition, the formula is determined to be an irreversible formula that cannot be automatically recovered through reverse structural change operations, and the structured formula information of the irreversible formula is cached in the backup data.

[0013] In one possible implementation, when an undo operation is triggered, the mapping rule between the reverse structural change operation and the structural change operation is as follows: The corresponding column is added after deleting a column; The deleted row corresponds to the added row; Adding a column corresponds to deleting a column; Adding a row corresponds to deleting a row; The worksheet identifier, row and column index, and number of rows and columns for the reverse structural change operation are the same as those for the structural change operation.

[0014] Secondly, embodiments of this application provide an efficient formula collaboration device in a collaborative table, comprising: a storage module, used to convert formulas in the collaborative table into structured formula information and determine formula impact information; wherein, the formula impact information is used to mark the formula adjustment area affected by the structure change operation; The update module is used to determine the affected table range based on formula impact information when the structure change operation is a row or column addition or deletion operation. It then uses an index adjustment algorithm to judge and adjust the references of formulas within the affected table range, and completes the automatic update of formula references without generating additional formula setting atomic operations. The new state of the formula is determined by the semantics of the structural change operation, and the execution parameters and formula reference adjustment data of this structural change operation are retained; If the structure change operation is a row or column deletion operation, the table data to be deleted is extracted from the original data and cached in the backup data; if the structure change operation is a row or column addition operation, the position, quantity, and filling data of the newly added rows and columns are recorded and cached in the backup data. The judgment module is used to perform a reversibility judgment on each adjusted formula based on the structured formula information, the execution parameters of the structured change operation and the formula reference adjustment data while performing the structured change operation. It identifies irreversible formulas that cannot be automatically recovered through reverse structured change operations and caches their structured formula information in the backup data. The recovery module is used to generate and execute the reverse structural change operation corresponding to the structural change operation when the undo operation is triggered. It realizes the automatic recovery of reversible formulas through the index adjustment algorithm. If the original structural change operation is a row and column deletion operation, it extracts and restores the deleted table data from the backup data. If the original structural change operation is a row and column addition operation, it deletes the newly added rows and columns in step 102 and the data filled in them. Based on the structured formula information cached in the backup data, it only generates and executes formula setting atomic operations for irreversible formulas to realize the recovery of irreversible formulas.

[0015] Thirdly, embodiments of this application provide an efficient formula collaboration server in a collaborative table, including a memory and a processor; the memory is used to store computer-executable instructions; the processor is used to execute the computer-executable instructions to implement the method described in the first aspect or any possible implementation of the first aspect.

[0016] Fourthly, embodiments of this application provide a computer-readable storage medium storing executable instructions, which, when executed by a computer, enable the method described in the first aspect or any possible implementation thereof.

[0017] One or more technical solutions provided in this application embodiment have at least the following technical effects: This application embodiment provides an efficient formula collaboration method in a collaborative table. By converting the formulas in the collaborative table into structured formula information and determining the formula impact information, it can quickly locate the formula area affected by structural changes, ensure the traceability and easy adjustment of formula data, and improve the accuracy and efficiency of subsequent formula processing. When performing structural change operations of adding or deleting rows and columns, the affected table range is determined based on the formula impact information. The index adjustment algorithm judges and adjusts the references of formulas within the affected table range, completing the automatic update of formula references without generating additional formula setting atomic operations. The new state of the formula is determined by the semantics of the structural change operation. The formula reference adjustment is embedded in the structural change process, eliminating the need to generate formula setting atomic operations separately for each affected formula, thus solving the problem of the explosion of atomic operations from the root. At the same time, this statement synchronously realizes the caching and backup of row and column change data, retaining the original data for subsequent undo operations and ensuring data recoverability. By performing reversibility checks on each adjusted formula simultaneously with structural change operations, based on structured formula information, execution parameters of the structural change operation, and formula reference adjustment data, irreversible formulas that cannot be automatically recovered through reverse structural change operations are identified. Their structured formula information is then cached in backup data, pre-completing formula reversibility tiering and filtering. Only the core structured information of irreversible formulas is cached, avoiding full caching that consumes storage space while providing data support for accurate recovery in subsequent undo operations, balancing storage efficiency and recovery accuracy. When an undo operation is triggered, a reverse structural change operation corresponding to the structural change operation is generated and executed. An index adjustment algorithm enables automatic recovery of reversible formulas without additional intervention, simplifying the undo process and improving recovery speed. Simultaneously, based on the structured formula information cached in the backup data, atomic formula setting operations are generated and executed only for irreversible formulas, achieving recovery of irreversible formulas. Repair operations are performed only on a very small number of irreversible formulas, further reducing the number of atomic operations and lowering the load on the collaboration engine. Furthermore, this step simultaneously recovers deleted data and cleans up newly added rows and columns, ensuring complete restoration of table layout and data integrity. This application achieves integrated processing of structural changes and formula adjustments, reducing network bandwidth consumption and lowering computational latency in the collaboration engine. Even in large-scale, complex formula scenarios, it ensures smooth multi-user collaboration, improves system stability and scalability, and enables automatic formula updates and precise undo / restore, eliminating the need for manual formula correction by users and optimizing the overall collaborative user experience. It solves the problems in existing technologies where structural changes lead to a surge in the number of atomic operations, high network bandwidth consumption, high computational overhead in the collaboration engine, and significant processing latency. Attached Figure Description

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

[0019] Figure 1 A flowchart illustrating an efficient formula collaboration method in a collaborative table, as provided in this application embodiment; Figure 2 A schematic diagram of an efficient formula coordination device in a coordination table provided in this application embodiment; Figure 3 This is a schematic diagram of an efficient formula collaboration server in a collaborative table, provided as an embodiment of this application. Detailed Implementation

[0020] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the scope of protection of this application.

[0021] The following description of some technologies involved in the embodiments of this application is provided to aid understanding and should be considered merely exemplary. Therefore, those skilled in the art should recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope and spirit of this application. Similarly, for clarity and brevity, some descriptions of well-known functions and structures are omitted in the following description.

[0022] This application provides an efficient formula collaboration method in a collaborative table, such as... Figure 1 As shown, the method includes steps 101 to 104. Wherein, Figure 1 This is merely one execution order shown in the embodiments of this application and does not represent the only execution order of an efficient formula coordination method in a coordination table. Where the final result can be achieved, Figure 1 The steps shown can be performed in parallel or in reverse order.

[0023] Step 101: Convert the formulas in the collaborative table into structured formula information and determine the formula impact information; wherein, the formula impact information is used to mark the formula adjustment areas affected by the structural change operation.

[0024] Specifically, when performing step 101, this application can simultaneously store formula influence information and the original data of the collaborative table.

[0025] The structured formula information is IFormulaInfo, which includes a reference map and an array of formula fragments.

[0026] Specifically, structured formula information is the core storage structure of formulas in collaborative table snapshots. It adopts a separate storage design, which does not store formula strings, but only stores structured data that can be directly parsed and processed by the program.

[0027] The reference mapping table stores reference information including start and end rows and columns, table span identifiers, row and column spans, and relative / absolute attributes.

[0028] Specifically, the type of the referenced mapping table is Record.<string,IReference> (A structured data type in key-value pair format), using reference identifiers (such as "R0", "R1") as keys to store corresponding reference information (IReference). "R0" and "R1" are unique string identifiers assigned to each reference in a formula within the collaborative spreadsheet program. Reference information includes core fields such as start / end row / column, cross-sheet identifier (sheetId / endSheetId), row / column span, and relative / absolute attributes (identified by the CellReferenceFlags flag). It also includes auxiliary fields such as Reference Type and invalidRef flags to precisely describe the position, attributes, and status of each reference in the formula.

[0029] The formula fragment array records the text content of the formula and the reference key pointing to the reference map in sequence.

[0030] Specifically, the formula fragment array (parts) is of type IFormulaPart[] (a one-dimensional array type). It records the components of the formula in the syntactic order of the formula. The array as a whole contains text content fragments and reference key fragments, and each array element is of type IFormulaPart, containing only one of either text content (text) or reference key (ref). The text content is used to record non-reference content such as function names, operators, and constants in the formula. The reference key is a unique identifier pointing to the reference mapping table. Through this key, the corresponding reference information in the reference mapping table can be associated, realizing the decoupled storage of formula text and reference data.

[0031] Specifically, the Formula Effect Information (IFormulaEffectInfo) is a structured tagging information used to pre-mark formula adjustment areas in collaborative tables that are affected by structural change operations (such as deleting columns or adding rows). It includes tagging fields at the workbook and sheet levels, and can accurately identify all areas containing formulas, such as cell formulas, conditional formatting rule formulas, data validation rule formulas, custom name formulas, and shape formulas. This provides a basis for quickly determining the affected table range when subsequent structural change operations are performed, avoiding indiscriminate traversal of all table content and improving formula adjustment efficiency.

[0032] Step 102: When the structural change operation is a row / column addition / deletion operation, the affected table range is determined based on the formula impact information. The index adjustment algorithm is used to judge and adjust the references of formulas within the affected table range, completing the automatic update of formula references without generating additional formula setting atomic operations. The new state of the formula is determined by the semantics of the structural change operation, and the execution parameters and formula reference adjustment data of this structural change operation are retained. If the structural change operation is a row / column deletion operation, the deleted table data is extracted from the original data and cached in the backup data. If the structural change operation is a row / column addition operation, the position, quantity, and filling data of the newly added rows and columns are recorded and cached in the backup data.

[0033] The execution parameters for structural change operations should include at least the operation type, worksheet unique identifier, starting row and column index, and the number of rows and columns to be added or deleted.

[0034] Specifically, the execution parameters of the structure change operation are the core basis for the server and client to execute the index adjustment algorithm and restore the structure change operation process. The operation types include four core types: deleteColumns, deleteRows, addColumns, and addRows.

[0035] The formula reference adjustment data should include at least a list of affected formulas, the result of the reference adjustment judgment, and the position and status information of the reference after adjustment.

[0036] Specifically, the list of affected formulas records the cell locations of all formulas affected by the structural change operation and their corresponding structured formula information. The reference adjustment judgment result records the determination of the necessity of adjustment for each reference in each formula (needs adjustment / does not need adjustment). The position and status information after reference adjustment records the row and column index, range size, or invalid reference flag (invalidRef) status of the reference after index adjustment.

[0037] The index adjustment algorithm is used to determine the references to formulas within a table range, including the following:

[0038] An index adjustment is required only if the reference type is a cell reference, the reference range is a single worksheet, the referenced worksheet is the same as the worksheet being operated on, the reference is valid, or it is not a whole row / column reference.

[0039] Specifically, the reference type is a cell reference, excluding structured references. The reference scope is a single worksheet, excluding range references across multiple worksheets. The referenced worksheet must be the same as the worksheet being manipulated, meaning the referenced worksheet must be the same as the worksheet where the structure change operation is performed, and the referenced sheetId must exactly match the sheetId of the structure change operation. The reference must be in a valid state, meaning the invalidRef flag is set to false, excluding invalid references marked with "#REF!". The reference must not be a whole row / column reference, meaning the CellReferenceFlags flags do not contain FullRow or FullColumn flags, excluding whole row / column references unaffected by row or column additions or deletions.

[0040] The index adjustment algorithm includes a single-point deletion adjustment function, a range deletion adjustment function, and a single-point insertion adjustment function. The single-point deletion adjustment function is used to offset and delete or mark invalid indices of a single cell reference based on the positional relationship between the single cell reference and the deletion range. The range deletion adjustment function is used to adjust the starting index and range size of a range reference or mark it as an invalid reference based on the positional relationship between the range reference and the deletion range. The single-point insertion adjustment function is used to offset and insert indices of a cell reference based on the positional relationship between the cell reference and the insertion range.

[0041] Specifically, the single-point delete adjustment function (_adjustIndexByDelete) is applicable to adjusting the index of a single cell reference. It is used to offset or invalidate the index of a single cell reference based on its position relative to the deletion range. If the referenced index is within the deletion range, the reference is marked as invalid (invalidRef=true). If the referenced index is after the deletion range, the referenced index is shifted forward by the corresponding deletion amount. If the referenced index is before the deletion range, the index remains unchanged.

[0042] For example, when the deleteColumns operation is executed to delete column B with col=1 (column index 1), the original column index of a single cell referencing C1 is 2. Because this index is after the deletion range, the column index is shifted forward by 1 after the function is adjusted, and updated to 1, so the corresponding referenced cell becomes B1.

[0043] The range deletion adjustment function (_adjustRangeByDelete) is used to adjust the index of range references. It adjusts the starting index and size of the range reference or marks it as an invalid reference based on the positional relationship between the range reference and the range to be deleted. If the intersection of the deleted range and the referenced range covers the entire referenced range, the reference is marked as invalid. If there is partial intersection, the starting index of the reference is adjusted and the corresponding range size is reduced. If the deleted range precedes the referenced range, only the starting index of the reference is shifted forward, and the range size remains unchanged.

[0044] For example, when the deleteColumns operation is executed to delete column B with col=1, the original column index of the range reference A1:C1 is 0, the end column index is 2, and the range size is 3 columns. After the function is adjusted, the starting column index remains unchanged at 0, the end column index is updated to 1, the range size is reduced to 2 columns, and the corresponding reference range becomes A1:B1.

[0045] The single-point insertion adjustment function (_adjustIndexByInsert) is used to adjust the index of a cell reference. It offsets the cell reference index based on the positional relationship between the cell reference and the insertion range. If the insertion position is before or at the same position as the reference index, the reference index is shifted forward by the corresponding insertion amount. If the insertion position is after the reference index, the reference index remains unchanged.

[0046] For example, when the addColumns operation is executed to insert a column at position col=1, the original column index of a single cell referencing C1 is 2. Because the insertion position is before the reference index, the column index is shifted 1 to the right after the function adjustment, and updated to 3. The corresponding referenced cell becomes D1.

[0047] Step 103: While performing the structural change operation, based on the structured formula information, the execution parameters of the structural change operation, and the formula reference adjustment data, perform a reversibility judgment on each adjusted formula, identify irreversible formulas that cannot be automatically recovered through the reverse structural change operation, and cache their structured formula information in the backup data.

[0048] For each adjusted formula, a reversibility check is performed to identify irreversible formulas that cannot be automatically recovered through reverse structural change operations. The structured formula information is then cached in backup data. This includes: the reversibility check is implemented using a formula recovery check function that simulates reverse insertion / deletion operations; the conditions for automatic recovery of a formula through reverse structural change operations are: the formula is an empty formula or has no references, or all references to the formula meet the recoverability conditions; the recoverability conditions are that the reference meets any of the following conditions: the reference is invalid; the worksheet to which the reference belongs is inconsistent with the worksheet to which the structural change operation is performed; the reference is a whole column reference during row deletion, or a whole row reference during column deletion; the structural change range is completely before / after the reference range, or the structural change range is completely within the reference range and does not touch the start or end point of the reference range.

[0049] Specifically, the formula recovery judgment function is `canFormulaInfoBeAdjustedBackByInsert`. The reference is invalid, meaning the `invalidRef` flag is set to `true`. The worksheet to which the reference belongs is inconsistent with the worksheet where the structure change operation was performed; that is, the referenced `sheetId` (worksheet ID) does not match the `sheetId` of the structure change operation. During row deletion, the reference is a whole column reference, or during column deletion, the reference is a whole row reference; that is, during row deletion, the `CellReferenceFlags` flag contains `FullColumn` (whole column reference identifier), and during column deletion, the flag contains `FullRow` (whole row reference identifier).

[0050] If any reference in the formula does not meet the recoverability condition, the formula is determined to be an irreversible formula that cannot be automatically recovered through reverse structural change operations, and the structured formula information of the irreversible formula is cached in the backup data. Let's take deleting column B (col=1) using the `deleteColumn` operation as an example (structural change range: column index 1). When the original formula is "=A1+C1", the reference range is column index 2, and the structural change range is completely before the reference range, the formula is reversible. When the original formula is "=SUM(A1:E1)", the reference range is column indices 0-4, and the structural change range is completely within the reference range and does not touch the start or end of the reference range, the formula is reversible. When the original formula is "=B1",... 2”, the reference range is: column index 1, there is no recoverable condition that is satisfied, the formula is irreversible.

[0051] Step 104: When the undo operation is triggered, generate and execute the reverse structure change operation corresponding to the structure change operation, and realize the automatic recovery of reversible formulas through the index adjustment algorithm; if the original structure change operation is a row and column deletion operation, extract and restore the deleted table data from the backup data; if the original structure change operation is a row and column addition operation, delete the rows and columns added in step 102 and the data filled in them; based on the structured formula information cached in the backup data, generate and execute formula setting atomic operations only for irreversible formulas to realize the recovery of irreversible formulas.

[0052] When an undo operation is triggered, the mapping rule between the reverse structural change operation and the structural change operation is as follows: Deleting a column corresponds to adding a column; deleting a row corresponds to adding a row; adding a column corresponds to deleting a column; adding a row corresponds to deleting a row. The worksheet identifier, row and column indices, and number of rows and columns for the reverse structural change operation are consistent with those for the structural change operation.

[0053] Specifically, when the undo operation is triggered, a reverse structural change operation corresponding to the structural change operation is generated and executed. During the execution of the reverse structural change operation, the index adjustment algorithm in step 102 is reused to perform an index offset calculation opposite to the original change direction on the reference of the reversible formula, so that the reference index of the reversible formula is automatically restored to the original position before the structural change. Thus, the automatic restoration of the reversible formula can be completed without generating any formula setting atomic operations.

[0054] Specifically, the reverse structure change operation only modifies the type (operation type) to the corresponding reverse operation. The sheetId (worksheet ID), starting row and column index, and number of rows and columns are not modified at all. It completely inherits the original structure change operation parameters, ensuring that the execution position of the reverse operation is consistent with that of the original structure change operation.

[0055] To delete column B with col=1 using the deleteColumns operation, the formulas "=A1+C1" (which is reversible) and "=SUM(A1:B1)" (which is irreversible) are affected. Taking 2” as an example, the execution steps and results of the undo operation are shown.

[0056] Specifically, the formula "=SUM(A1:B1)" has column indices from 0 (start) to 1 (end). After deleting column B, the column indexes remain from 0 (start) to 0 (end). The deletion of column B (col=1) changes the end point of the reference range. This situation does not meet the recoverable condition of "not touching the start and end points of the reference range," therefore the reference is not recoverable, and the formula as a whole is irreversible.

[0057] 1. Generate and execute the reverse structural change operation corresponding to the structural change operation.

[0058] The addColumns operation inserts a column (column B) at position col=1. The formula is automatically adjusted based on the index adjustment algorithm. The reversible formula "=A1+C1" is automatically restored from the deleted "=A1+B1" to the original formula "=A1+C1" without any additional operation.

[0059] 2. Restore the deleted original table data.

[0060] Extract all cell data (including cell values, styles, original formula information, etc.) from the original column B from the backup data, and fill the newly inserted column B with the data using setRange OP (batch data setting atomic operation), thus restoring the deleted data to its original position.

[0061] 3. Delete newly added rows and columns (executed when adding rows and columns is undone).

[0062] If the original structure change operation was to insert column B at position col=1, the deleteColumns operation will be executed when the operation is undone to delete the newly added column B, clear all the fill data in the column, and restore the original row and column layout of the table.

[0063] 4. Generate and execute setFormula OP (formula setting atomic operation).

[0064] Based on the cached original structured formula information (IFormulaInfo) in the backup data, only the irreversible formulas "=SUM(A1:B1)" and "=B1" are processed. 2” Generate and execute setFormula OP to achieve explicit restoration. The formula “=SUM(A1:B1)” is restored from the deleted “=SUM(A1:A1)” to the original range reference formula “=SUM(A1:B1)”. The formula “=B1” 2” is from the deleted “=#REF!” 2” Restore the original cell reference formula “=B1” 2". "#REF!" is an invalid reference marker.

[0065] Ultimately, the table's row and column layout was restored to its state before column B was deleted, all formulas were accurately restored to their original state, and setFormula OP was generated only for the two irreversible formulas, without any redundant operations.

[0066] This application also provides an efficient formula coordination device 200 in a coordination table, such as... Figure 2 As shown, the device includes: a storage module 201, an update module 202, a judgment module 203, and a recovery module 204.

[0067] The storage module 201 is used to convert the formulas in the collaborative table into structured formula information and determine the formula impact information; wherein, the formula impact information is used to mark the formula adjustment area affected by the structural change operation.

[0068] The update module 202 is used to determine the affected table range based on formula impact information when the structure change operation is a row / column addition / deletion operation. It then uses an index adjustment algorithm to judge and adjust the references of formulas within the affected table range, completing the automatic update of formula references without generating additional formula setting atomic operations. The new state of the formula is determined by the semantics of the structure change operation, and the execution parameters and formula reference adjustment data of this structure change operation are retained. If the structure change operation is a row / column deletion operation, the deleted table data is extracted from the original data and cached in the backup data. If the structure change operation is a row / column addition operation, the position, quantity, and fill data of the newly added rows and columns are recorded and cached in the backup data.

[0069] The judgment module 203 is used to perform a reversibility judgment on each adjusted formula based on the structured formula information, the execution parameters of the structured change operation and the formula reference adjustment data while performing the structured change operation. It identifies irreversible formulas that cannot be automatically recovered through reverse structured change operations and caches their structured formula information in the backup data.

[0070] When an undo operation is triggered, the recovery module 204 generates and executes the reverse structural change operation corresponding to the structural change operation, and automatically restores the reversible formula through the index adjustment algorithm. If the original structural change operation is a row and column deletion operation, the deleted table data is extracted and restored from the backup data. If the original structural change operation is a row and column addition operation, the newly added rows and columns in step 102 and the data filled in them are deleted. Based on the structured formula information cached in the backup data, only irreversible formulas are generated and formula setting atomic operations are executed to restore the irreversible formula.

[0071] Some modules in the apparatus described in this application can be described in the general context of computer-executable instructions that are executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, classes, etc., that perform a specific task or implement a specific abstract data type. This application can also be practiced in distributed computing environments where tasks are performed by remote processing devices connected via a communication network. In distributed computing environments, program modules can reside in local and remote computer storage media, including storage devices.

[0072] The apparatus or module described in the above embodiments can be implemented by a computer chip or physical entity, or by a product with a certain function. For ease of description, the above apparatus is described by dividing it into various modules according to their functions. When implementing the embodiments of this application, the functions of each module can be implemented in one or more software and / or hardware. Of course, a module that implements a certain function can also be implemented by combining multiple sub-modules or sub-units.

[0073] The methods, apparatus, or modules described in this application can be implemented in a computer-readable program code manner. The controller can be implemented in any suitable manner, for example, as a microprocessor or processor and a computer-readable medium storing computer-readable program code (e.g., software or firmware) executable by the (micro)processor, logic gates, switches, application-specific integrated circuits (ASICs), 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 Silicon Labs C8051F320. A memory controller can also be implemented as part of the control logic of a memory. Those skilled in the art will also recognize that, in addition to implementing the controller in purely computer-readable program code manner, the same functionality can be achieved by logically programming the method steps to make the controller take the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers. Therefore, such a controller can be considered a hardware component, and the means included within it for implementing various functions can also be considered as structures within the hardware component. Alternatively, the device used to implement various functions can be viewed as either a software module that implements the method or a structure within a hardware component.

[0074] like Figure 3 As shown in the figure, this application embodiment also provides an efficient formula collaboration server in a collaborative table, including a memory 301 and a processor 302; the memory 301 is used to store computer-executable instructions; the processor 302 is used to execute computer-executable instructions to implement the efficient formula collaboration method in a collaborative table described above in this application embodiment.

[0075] This application also provides a computer-readable storage medium storing executable instructions, which, when executed by a computer, can implement the efficient formula collaboration method in a collaborative table described above in this application embodiment.

[0076] As can be seen from the above description of the embodiments, those skilled in the art can clearly understand that this application can be implemented by means of software plus necessary hardware. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product, or it can be embodied in the process of data migration. The computer software product can be stored in a storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, mobile terminal, server, or network device, etc.) to execute the methods described in the embodiments of this application.

[0077] The various embodiments described in this specification are presented in a progressive manner. Similar or identical parts between embodiments can be referred to interchangeably. Each embodiment focuses on its differences from other embodiments. All or part of this application can be used in numerous general-purpose or special-purpose computer system environments or configurations.

[0078] The above embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit this application. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of this application.

Claims

1. An efficient formula collaboration method in a collaborative table, characterized in that, include: Step 101: Convert the formulas in the collaborative table into structured formula information and determine the formula impact information; wherein, the formula impact information is used to mark the formula adjustment area affected by the structural change operation; the structured formula information is IFormulaInfo, which includes a reference mapping table and an array of formula fragments; Step 102: When the structure change operation is a row or column addition or deletion operation, the affected table range is determined based on the formula impact information. The index adjustment algorithm is used to judge and adjust the references of formulas within the affected table range, and the automatic update of formula references is completed without generating additional formula setting atomic operations. The new state of the formula is determined by the semantics of the structural change operation, and the execution parameters and formula reference adjustment data of this structural change operation are retained; If the structure change operation is a row or column deletion operation, the table data to be deleted is extracted from the original data and cached in the backup data; if the structure change operation is a row or column addition operation, the position, quantity, and filling data of the newly added rows and columns are recorded and cached in the backup data. Step 103: While performing the structural change operation, based on the structured formula information, the execution parameters of the structural change operation, and the formula reference adjustment data, perform a reversibility judgment on each adjusted formula, identify irreversible formulas that cannot be automatically recovered through reverse structural change operations, and cache their structured formula information in the backup data. Step 104: When the undo operation is triggered, generate and execute the reverse structure change operation corresponding to the structure change operation, and realize the automatic recovery of reversible formulas through the index adjustment algorithm; if the original structure change operation is a row and column deletion operation, extract and restore the deleted table data from the backup data; if the original structure change operation is a row and column addition operation, delete the rows and columns added in step 102 and the data filled in them; based on the structured formula information cached in the backup data, generate and execute formula setting atomic operations only for irreversible formulas to realize the recovery of irreversible formulas.

2. The efficient formula collaboration method in the collaborative table according to claim 1, characterized in that, The reference mapping table stores reference information including start and end rows and columns, cross-table identifier, row and column span, and relative / absolute attributes; The formula fragment array records the text content of the formula and the reference key pointing to the reference mapping table in sequence.

3. The efficient formula collaboration method in the collaborative table according to claim 1, characterized in that, The execution parameters for the structural change operation include at least the operation type, worksheet unique identifier, starting row and column index, and the number of rows and columns to be added / deleted. The formula reference adjustment data includes at least a list of affected formulas, the reference adjustment determination result, and the position and status information after the reference adjustment.

4. The efficient formula collaboration method in the collaborative table according to claim 1, characterized in that, The process of judging and adjusting the references to formulas within the affected table range using an index adjustment algorithm includes: An index adjustment is required only if the reference type is a cell reference, the reference range is a single worksheet, the referenced worksheet is the same as the worksheet being operated on, the reference is valid, or it is not a whole row / column reference.

5. The efficient formula collaboration method in the collaborative table according to claim 4, characterized in that, The index adjustment algorithm includes single-point deletion adjustment functions, range deletion adjustment functions, and single-point insertion adjustment functions; The single-point deletion adjustment function is used to offset and delete or mark invalid the index of a single cell reference based on the positional relationship between the single cell reference and the deletion range. The range deletion adjustment function is used to adjust the starting index and range size of the range reference or mark it as an invalid reference based on the positional relationship between the range reference and the range to be deleted. The single-point insertion adjustment function is used to offset the index of the cell reference based on the positional relationship between the cell reference and the insertion range.

6. The efficient formula collaboration method in the collaborative table according to claim 1, characterized in that, The process of performing a reversibility check on each adjusted formula, identifying irreversible formulas that cannot be automatically recovered through reverse structural change operations, and caching their structured formula information in backup data includes: The reversibility determination is achieved by restoring the determination function through a formula that simulates a reverse insertion / deletion operation; The conditions under which the formula is automatically restored through the reverse structural change operation are: the formula is an empty formula or has no references, or all references to the formula meet the restoreability conditions. The recoverability condition is that the reference satisfies any of the following conditions: The reference is invalid. The referenced worksheet is inconsistent with the worksheet on which the structural change operation was performed; When deleting a row, the reference is set to the entire column; when deleting a column, the reference is set to the entire row. The scope of the structural change is entirely before / after the reference scope, or the scope of the structural change is entirely within the reference scope and does not touch the start or end point of the reference scope; If any reference in the formula does not meet the recoverability condition, the formula is determined to be an irreversible formula that cannot be automatically recovered through reverse structural change operations, and the structured formula information of the irreversible formula is cached in the backup data.

7. The efficient formula collaboration method in the collaborative table according to claim 1, characterized in that, When a cancellation operation is triggered, the mapping rule between the reverse structural change operation and the structural change operation is as follows: The corresponding column is added after deleting a column; The deleted row corresponds to the added row; Adding a column corresponds to deleting a column; Adding a row corresponds to deleting a row; The worksheet identifier, row and column index, and number of rows and columns for the reverse structural change operation are the same as those for the structural change operation.

8. A highly efficient formula coordination device in a coordination table, characterized in that, The device performs the method as described in any one of claims 1 to 7, including: The storage module is used to convert formulas in the collaborative table into structured formula information and determine formula impact information; wherein, the formula impact information is used to mark the formula adjustment area affected by the structural change operation; the structured formula information is IFormulaInfo, which includes a reference mapping table and an array of formula fragments; The update module is used to determine the affected table range based on formula impact information when the structure change operation is a row or column addition or deletion operation. It then uses an index adjustment algorithm to judge and adjust the references of formulas within the affected table range, and completes the automatic update of formula references without generating additional formula setting atomic operations. The new state of the formula is determined by the semantics of the structural change operation, and the execution parameters and formula reference adjustment data of this structural change operation are retained; If the structure change operation is a row or column deletion operation, the table data to be deleted is extracted from the original data and cached in the backup data; if the structure change operation is a row or column addition operation, the position, quantity, and filling data of the newly added rows and columns are recorded and cached in the backup data. The judgment module is used to perform a reversibility judgment on each adjusted formula based on the structured formula information, the execution parameters of the structured change operation and the formula reference adjustment data while performing the structured change operation. It identifies irreversible formulas that cannot be automatically recovered through reverse structured change operations and caches their structured formula information in the backup data. The recovery module is used to generate and execute the reverse structural change operation corresponding to the structural change operation when the undo operation is triggered. It realizes the automatic recovery of reversible formulas through the index adjustment algorithm. If the original structural change operation is a row and column deletion operation, it extracts and restores the deleted table data from the backup data. If the original structural change operation is a row and column addition operation, it deletes the newly added rows and columns in step 102 and the data filled in them. Based on the structured formula information cached in the backup data, it only generates and executes formula setting atomic operations for irreversible formulas to realize the recovery of irreversible formulas.

9. A high-efficiency formula collaboration server in a collaborative table, characterized in that, Including memory and processor; The memory is used to store computer-executable instructions; The processor is configured to execute the computer-executable instructions to implement the method according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores executable instructions, which, when executed by a computer, enable the implementation of the method as described in any one of claims 1-7.

Images