An online spreadsheet processing method and device based on a CRDT algorithm

Abstract

The application discloses an online spreadsheet processing method and device based on a CRDT algorithm. A table semantic CRDT three-level data model is constructed, including a table layer, a structure layer and a cell layer; hierarchical incremental synchronization is executed based on the CRDT three-level data model, and the hierarchical incremental synchronization includes differential compression and local caching of operation instructions on the client side, and conflict merging and incremental broadcasting of the operation instructions on the server side; a dynamic formula dependency graph is constructed, incremental recalculation is performed on the affected formula cells, and formula calculation is executed in a mixed calculation manner of the client and the server; offline synchronization is executed based on the CRDT three-level data model, and the offline synchronization includes storing local operation instructions into a cache in an offline state, sequentially uploading local unsynchronized instructions and pulling remote instructions after network recovery, and data repair through hierarchical hash verification. The scheme of the application can solve the problem of low calculation efficiency caused by insufficient adaptability of general CRDT algorithm table semantics.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to an online spreadsheet processing method and apparatus based on the CRDT algorithm. Background Technology

[0002] With the deepening of enterprise digital transformation, online spreadsheets have become an important tool for collaborative work, data processing, and analysis. The widespread adoption of cloud computing and mobile internet has placed higher demands on online spreadsheets for high-concurrency, low-latency collaboration, efficient processing of massive amounts of data, adaptation to weak network environments, and ensuring data consistency across multiple devices. Related technologies have become the main research and development direction in the field of office digitalization.

[0003] Current online spreadsheets primarily use a client-side rendering architecture, employing a browser to fully load data and perform hybrid rendering. They rely on a third-party CRDT library for collaboration, mapping the entire spreadsheet to a single document structure and using version vectors to resolve conflicts.

[0004] However, existing solutions have shortcomings in adapting to high-performance collaborative requirements. The client-side rendering architecture has performance bottlenecks in full loading and synchronization. Loading large data tables takes a long time and consumes a lot of memory. Full synchronization leads to a significant increase in network latency when multiple people are concurrent. The general CRDT algorithm cannot adapt to the structured semantics of tables, which means that fine-grained modifications still require the synchronization of a large amount of metadata, resulting in low synchronization efficiency. Summary of the Invention

[0005] This invention provides an online spreadsheet processing method and apparatus based on the CRDT algorithm to solve the problem of low computational efficiency caused by insufficient semantic adaptability of the general CRDT algorithm.

[0006] According to one aspect of the present invention, an online spreadsheet processing method based on the CRDT algorithm is provided, comprising:

[0007] A three-level data model for table semantic CRDT is constructed. The three-level data model includes a table layer, a structure layer, and a cell layer. The table layer is used to manage table metadata and version vectors, the structure layer is used to manage the addition and deletion operations of rows and columns, and the cell layer is used to store multi-version data of cells.

[0008] Based on the CRDT three-level data model, hierarchical incremental synchronization is performed. The hierarchical incremental synchronization includes differential compression and local caching of operation instructions on the client side, and conflict merging and incremental broadcasting of the operation instructions on the server side.

[0009] A dynamic formula dependency graph is constructed, and the affected formula cells are incrementally recalculated based on the dynamic formula dependency graph. Formula calculation is then performed through a hybrid client-server calculation method.

[0010] Offline synchronization is performed based on the CRDT three-level data model. The offline synchronization includes storing local operation instructions in a cache while offline, uploading unsynchronized local instructions and retrieving remote instructions in sequence after the network is restored, and repairing data through hierarchical hash verification.

[0011] Optionally, the construction of the three-level CRDT data model for table semantics includes:

[0012] Initialize the table layer, assign a globally unique worksheet identifier to each worksheet, initialize the final write-win mapping structure to store worksheet metadata and version vectors, and define the metadata conflict resolution rule as comparing operation timestamps, retaining the operation result with the largest timestamp;

[0013] Construct a structural layer, creating two-dimensional sets of last written winning elements for rows and columns respectively. Assign a globally unique row identifier to each row and a globally unique column identifier to each column. Configure an existence flag attribute and a deletion flag attribute for each row or column node. The existence flag attribute indicates whether the row or column currently exists. The deletion flag attribute includes a boolean value indicating whether it has been deleted and the deletion time.

[0014] Configure the cell layer, generate cell identifiers based on row identifiers and column identifiers, and use an incrementing counter and modification vector to store cell data. Each version in the modification vector includes content, format, formula, modifier identifier and timestamp.

[0015] Optionally, the operation instruction is an atomic operation instruction, which includes an operation type, a target identifier, a version vector, and a timestamp base field, and extends the content, format, and formula fields for cell operations, and extends the insertion position index and deletion time fields for row or column operations.

[0016] Optionally, the hierarchical incremental synchronization based on the table semantic CRDT three-level data model includes:

[0017] On the client side, user operations are captured and atomic operation instructions are generated through an event delegation mechanism. The operation instructions are compressed to retain only the changed fields. The compressed instructions are pre-applied locally and stored in the local cache and marked as unsynchronized. When the network is interrupted, the system automatically enters offline mode and writes all operation instructions only to the local cache.

[0018] After receiving client operation instructions via a long connection on the server side, the version vector is verified. Operation instructions are collected in batches at preset time intervals. A globally ordered sequence is generated based on the timestamp and client identifier. Conflicts in the table layer, structure layer and cell layer are merged based on the CRDT three-level data model. The last synchronization point is maintained for each client, and incremental instructions after the last synchronization point are pushed to the corresponding client.

[0019] Optionally, the construction of a dynamic formula dependency graph, the incremental recalculation of affected formula cells based on the dynamic formula dependency graph, and the execution of formula calculation through a hybrid client-server calculation method include:

[0020] When a formula is written into a cell, the formula is parsed through an abstract syntax tree to extract a list of cell identifiers that it depends on, generate a formula fingerprint, and update the input dependency table and the output dependency table. The input dependency table records the mapping from the dependent cell to the set of formula cells that depend on it, and the output dependency table records the mapping from the formula cell to the set of cells it depends on.

[0021] When a formula is modified, old dependencies are removed from the input dependency table and output dependency table, and new dependencies are added;

[0022] When cell data changes, the input dependency table is queried to determine all affected formula cells, and the affected formula cells are recalculated.

[0023] Formulas are classified into lightweight formulas or complex formulas based on the number of operators and the preset function complexity. Lightweight formulas are calculated by the client and the calculation result is associated with the formula fingerprint and cached in local storage. Complex formulas are calculated by the client by requesting the server to calculate them, carrying the formula fingerprint and a snapshot of the dependent cell value. If the server does not find the formula fingerprint in the distributed cache or the dependent value changes, it calls the computing cluster to recalculate and return the result.

[0024] Optionally, the offline synchronization based on the table semantic CRDT three-level data model includes:

[0025] The network status is detected through a heartbeat detection mechanism. When the network is interrupted, it enters offline mode. In offline mode, user operation commands are pre-merged in the local CRDT engine, stored in the cache, and marked as unsynchronized.

[0026] After the network is restored, the client sends a synchronization request to the server. The synchronization request includes the worksheet identifier, the last synchronized version vector, and the number of unsynchronized instructions.

[0027] The client uploads the operation instructions marked as unsynchronized in ascending order of timestamp, so that the server can merge the conflict of the operation instructions based on the three-level data model and return the merge result. The server then obtains the remote instructions after the last synchronized version vector and applies them to the local three-level model in sequence.

[0028] After synchronization is complete, the client calculates the data hash values ​​of the table layer, structure layer and cell layer respectively and sends a verification request to the server. If the corresponding hash values ​​returned by the server are inconsistent, the comparison is performed layer by layer to locate the difference data, and the difference data returned by the server is received for targeted repair.

[0029] Optionally, the method further includes:

[0030] When offline, if a complex formula calculation is triggered, the cached result corresponding to the formula is retrieved from local storage, and the cached result is used as a temporary calculation result and marked as expired.

[0031] After the network is restored, the client sends a formula verification request to the server. The formula verification request includes the formula fingerprint of the formula and a snapshot of the value of the current dependent cell.

[0032] If the recalculation result returned by the server is inconsistent with the temporary calculation result, the local cell data and view are updated, and the formula cache and formula fingerprint of the formula in local storage are updated synchronously.

[0033] According to another aspect of the present invention, an online spreadsheet processing apparatus based on the CRDT algorithm is provided, comprising:

[0034] The three-level data model construction unit is used to construct a three-level data model of table semantic CRDT. The three-level data model includes a table layer, a structure layer and a cell layer. The table layer is used to manage table metadata and version vectors, the structure layer is used to manage the addition and deletion operations of rows and columns, and the cell layer is used to store multi-version data of cells.

[0035] The hierarchical incremental synchronization execution unit is used to perform hierarchical incremental synchronization based on the CRDT three-level data model. The hierarchical incremental synchronization includes differential compression and local caching of operation instructions on the client side, and conflict merging and incremental broadcasting of the operation instructions on the server side.

[0036] The formula calculation unit is used to construct a dynamic formula dependency graph, perform incremental recalculation of affected formula cells based on the dynamic formula dependency graph, and execute formula calculation through a hybrid client-server calculation method.

[0037] The offline synchronization unit is used to perform offline synchronization based on the CRDT three-level data model. The offline synchronization includes storing local operation instructions in a cache while offline, uploading unsynchronized local instructions in sequence and retrieving remote instructions after the network is restored, and repairing data through hierarchical hash verification.

[0038] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0039] At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the online spreadsheet processing method based on the CRDT algorithm according to any embodiment of the present invention.

[0040] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the online spreadsheet processing method based on the CRDT algorithm according to any embodiment of the present invention.

[0041] According to another aspect of the present invention, a computer program product is provided, the computer program product comprising a computer program that, when executed by a processor, implements the online spreadsheet processing method based on the CRDT algorithm described in any embodiment of the present invention.

[0042] The technical solution of this invention improves the performance, reliability, and user experience of online spreadsheets in multi-user collaborative scenarios by constructing a three-level data model of table semantic CRDT, performing hierarchical incremental synchronization, constructing a dynamic formula dependency graph to achieve hybrid computation, and performing offline synchronization and breakpoint resume. The three-level CRDT model reduces the structural conflict rate of concurrent row and column operations; the hierarchical synchronization mechanism reduces the amount of data transmitted per operation; the dynamic formula dependency graph enables incremental recalculation, and combined with a client / server hybrid computation strategy, shortens the single calculation time of complex functions; and the local caching and breakpoint resume mechanism ensures stable offline operation retention and reconnection synchronization time, thereby improving the performance, reliability, and user experience of online spreadsheets in multi-user collaborative scenarios.

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

[0044] To more clearly illustrate the technical solutions in the embodiments of the present invention, 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 the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0045] Figure 1 This is a flowchart of an online spreadsheet processing method based on the CRDT algorithm provided in an embodiment of the present invention;

[0046] Figure 2 This is a schematic diagram of a CRDT three-level data model applicable to an embodiment of the present invention;

[0047] Figure 3 This is a schematic diagram of the structure of an online spreadsheet processing device based on the CRDT algorithm provided in an embodiment of the present invention;

[0048] Figure 4 This is a schematic diagram of the structure of an electronic device that implements the online spreadsheet processing method based on the CRDT algorithm according to an embodiment of the present invention. Detailed Implementation

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

[0050] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention 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 the invention 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 a 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.

[0051] Figure 1This is a flowchart of an online spreadsheet processing method based on the CRDT algorithm provided by an embodiment of the present invention. This embodiment is applicable to solving performance and reliability issues of online spreadsheets in multi-user collaborative scenarios. The method can be executed by an online spreadsheet processing device based on the CRDT algorithm, which can be implemented in hardware and / or software and can be configured in an electronic device. Figure 1 As shown, the method includes:

[0052] S110. Construct a three-level data model for table semantic CRDT. The three-level data model includes a table layer, a structure layer, and a cell layer. The table layer is used to manage table metadata and version vectors, the structure layer is used to manage the addition and deletion of rows and columns, and the cell layer is used to store multi-version data of cells.

[0053] Figure 2 This is a schematic diagram of a three-level CRDT data model applicable to embodiments of the present invention. Through layered design, it adapts to the natural semantics of worksheets, rows, columns, and cells, resolving the structural operation conflicts inherent in traditional CRDT in table scenarios. The table layer, as the top-level container of the three-level data model, assigns a globally unique identifier to each worksheet and initializes a Last-Write-Wins Map (LWW-Map) structure to store metadata and version vectors recording the modification counts of each client. When multiple clients concurrently modify metadata such as worksheet names, the LWW-Map automatically compares operation timestamps and retains the operation with the largest timestamp, ensuring metadata consistency in concurrent scenarios. The structure layer, based on the namespace of the table layer, uses a two-dimensional Last-Write-Wins Element Set (2D-LWW-Element-Set) to manage row and column addition and deletion operations, resolving the ghost row / column structural conflict problem that occurs in traditional CRDT in table scenarios. The cell layer, as the smallest unit of data storage, generates cell identifiers by combining corresponding row and column identifiers, and uses a composite structure of an incrementing counter (G-Counter) and a modification vector (M-Vector) to store multi-version data of cells.

[0054] S120. Based on the CRDT three-level data model, hierarchical incremental synchronization is performed. Hierarchical incremental synchronization includes differential compression and local caching of operation instructions on the client side, and conflict merging and incremental broadcasting of operation instructions on the server side.

[0055] Specifically, the client, as the initiator of the operation, generates operation instructions after capturing the user's cell editing, row and column addition and deletion operations. The operation instructions are compressed and pre-applied on the local data model to ensure that the user interface responds instantly. The operation instructions are also stored in the browser's IndexedDB local cache and marked as unsynchronized.

[0056] The server acts as the central coordination hub. After receiving compression commands from each client, it uses a three-level CRDT data model to perform layered merging of concurrent conflicts. At the cell level, it determines the valid operation result by comparing the number of modifications with user weight rules. Afterward, the server maintains a last synchronization point for each client and pushes incremental commands after that point to the corresponding client, thus avoiding duplicate transmissions.

[0057] S130. Construct a dynamic formula dependency graph, perform incremental recalculation of affected formula cells based on the dynamic formula dependency graph, and execute formula calculation through a hybrid client-server calculation method.

[0058] Specifically, the dependency graph is the core of incremental calculation and needs to be updated in real time as formulas are modified to locate related cells. When a user enters a formula in a cell, the formula expression is parsed to extract which other cells the formula references, thus establishing a dependency relationship between the referenced cells and the cells containing the formula. As the user continuously edits formulas or modifies the table structure, these dependencies are dynamically added, deleted, and updated, forming a dependency graph in memory that reflects all formula reference relationships in the current table in real time. When a user modifies the original data value of a cell, the dependency graph is queried to locate all formula cells that directly or indirectly depend on that cell, and the affected formula cells are re-evaluated, thus narrowing the calculation scope from the entire table to related cells and avoiding waste of computing resources.

[0059] To improve computational efficiency and user experience, this implementation adopts a hybrid client-server computing strategy. For routine calculations with simple logic and high real-time requirements, the computation is completed locally in the user's browser (client), ensuring immediate feedback and a smooth user experience. For complex, time-consuming formulas, the client delegates the computation requests to a more powerful server cluster. After completing the computation, the server only returns the result to the client for rendering, thereby reducing the client's workload while leveraging server computing power to accelerate the overall computation process in complex scenarios.

[0060] S140. Perform offline synchronization based on the CRDT three-level data model. Offline synchronization includes storing local operation instructions in a cache while offline, uploading unsynchronized local instructions in sequence and retrieving remote instructions after the network is restored, and repairing data through hierarchical hash verification.

[0061] Specifically, in offline mode, the system automatically switches to offline mode when the network connection is interrupted. During this time, all user editing operations are not hindered by the network outage; standardized operation commands continue to be generated locally. These commands are stored in the browser's local IndexedDB cache in timestamp order and marked as unsynchronized. When the network is restored, the client uploads all unsynchronized operation commands from the local cache to the server in ascending order of timestamp. The server merges conflicts based on the three-level model and returns the results. The client then retrieves all remote commands generated by other collaborators after its last synchronization point from the server and applies them sequentially to the local three-level model.

[0062] After synchronization is complete, the client will calculate the hash values ​​of the table layer metadata, all row and column identifiers in the structure layer, and the content and format of the cell layer, and send a comparison and verification to the server. If the hash values ​​of a certain layer are inconsistent, it means that there is discrepancy data at that layer. The client will locate the discrepancy rows, columns, or cells, retrieve these discrepancies, and perform targeted repairs to avoid data retransmission.

[0063] In this embodiment of the invention, a three-level data model for tabular semantic CRDT is constructed, including:

[0064] Initialize the table layer, assign a globally unique worksheet identifier to each worksheet, initialize the final write-win mapping structure to store worksheet metadata and version vectors, and define the metadata conflict resolution rule as comparing operation timestamps, retaining the operation result with the largest timestamp;

[0065] Construct a structure layer, creating two-dimensional sets of last written winning elements for rows and columns respectively. Assign a globally unique row identifier to each row and a globally unique column identifier to each column. Configure an existence flag attribute and a deletion flag attribute for each row or column node. The existence flag attribute indicates whether the row or column currently exists, and the deletion flag attribute includes a boolean value indicating whether it has been deleted and the deletion time.

[0066] Configure the cell layer, generate cell identifiers based on row identifiers and column identifiers, and use an increment-only counter and modification vector to store cell data. Each version in the modification vector includes content, format, formula, modifier identifier and timestamp.

[0067] Specifically, the role of the table layer is to establish a unique identity and management rules for each worksheet. By assigning a globally unique identifier (UUID) to each worksheet, it ensures that table identities are not duplicated in distributed collaboration. A Last-Write-Wins Map (LWW-Map) structure is used to store metadata such as name and time, and a version vector is embedded to track the modification progress of each client. When multiple people concurrently modify metadata, consensus is automatically reached based on the principle that the one with the largest timestamp wins.

[0068] The structural layer resolves table layout conflicts by establishing a two-dimensional set of last-written winning elements (2D-LWW-Element-Set) for both rows and columns, and assigning a globally unique identifier to each row and column. To address the ghost row / column problem caused by multiple users simultaneously adding or deleting rows / columns in the same location, an existence flag indicates the current display state, and a deletion flag containing the deletion time and a boolean value tracks the deletion history, thus accurately determining concurrent intent. Furthermore, when concurrent insertions occur in the same position, the display order is determined by hashing and sorting the row and column identifiers.

[0069] A cell is the smallest and most atomic unit of data storage. Each cell is assigned a unique identifier by combining its row and column global uniqueness. Cells use a structure combining an incrementing counter (G-Counter) and a modification vector (M-Vector) to store data. The counter records the number of modifications to quickly determine version age, while the modification vector retains historical data records from multiple versions. Each version details its content, format, formulas, modifier, and timestamp. This information is used to provide a basis for user weighting decisions when concurrent write conflicts occur.

[0070] In this embodiment of the invention, the operation instruction is an atomic operation instruction. The atomic operation instruction includes an operation type, a target identifier, a version vector, and a timestamp base field. For cell operations, it extends the content, format, and formula fields. For row or column operations, it extends the insertion position index and deletion time fields.

[0071] Specifically, atomic operation instructions represent the smallest indivisible logical unit, ensuring that in distributed collaboration, an operation is either fully executed or not executed at all, avoiding data inconsistency. Atomic operation instructions contain four general fields: operation type, indicating the action category such as cell update, row insertion, or column deletion; target identifier, used to identify the object being operated on, such as the specific cell ID, row ID, or column ID; version vector, recording the modification progress as known to the client during the operation, used for conflict detection; and timestamp, marking the time the operation occurred. Building upon this, fields are expanded for operations on objects at different levels. For cell operation instructions, three additional fields—content, format, and formula—are added. When a user modifies a cell, only the changed data needs to be transmitted; for example, if only the format is modified, the instruction does not need to carry the complete content and formula information. For row or column operation instructions, insertion position index and deletion time fields are added. The position index indicates the specific location within the table structure for sorting during concurrent insertions; the deletion time field corresponds to the deletion flag attribute in the structure layer, used to record the time of deletion when resolving ghost row / column conflicts.

[0072] In this embodiment of the invention, hierarchical incremental synchronization is performed based on the three-level data model of table semantic CRDT, including:

[0073] On the client side, user operations are captured and atomic operation instructions are generated through an event delegation mechanism. The operation instructions are compressed to retain only the changed fields. The compressed instructions are pre-applied locally and stored in the local cache and marked as unsynchronized. When the network is interrupted, the system automatically enters offline mode and writes all operation instructions to the local cache only.

[0074] After receiving client operation instructions via a long connection on the server side, the version vector is verified. Operation instructions are collected in batches at preset time intervals. A globally ordered sequence is generated based on the timestamp and client identifier. Conflicts in the table layer, structure layer and cell layer are merged based on the CRDT three-level data model. The last synchronization point is maintained for each client, and incremental instructions after the last synchronization point are pushed to the corresponding client.

[0075] Specifically, the client uses an event delegation mechanism to uniformly listen for and capture various user operations on the table, such as cell editing, row and column insertion or deletion, and converts them into atomic operation commands. After generating the commands, the client first compresses them, comparing them with historical versions using a differential algorithm, retaining only the fields that have actually changed. The compressed commands are pre-applied to the local CRDT three-level data model to ensure that the user interface receives immediate visual feedback. Simultaneously, the commands are stored in a local cache and marked as unsynchronized for later synchronization or offline recovery. When the network is interrupted, the system automatically switches to offline mode. In this mode, newly generated operation commands skip the network transmission step and are only written to the local cache, ensuring the integrity of offline operations.

[0076] The server receives compression commands uploaded by each client in real time via a WebSocket long-lived connection. First, the server verifies the version vector carried in each command to determine if the client's data version is within an acceptable deviation range; if so, it proceeds to the next step. The server collects arriving commands in batches at preset time intervals (e.g., 10 milliseconds) and generates a unique and ordered global sequence based on the timestamp and client identifier of each command, effectively solving the clock skew and operation sorting problems in a distributed environment. Based on a three-level CRDT data model, the server performs targeted merging of concurrency conflicts at different levels: at the table level, it follows a timestamp priority rule; at the structure level, it uses a double-label and hash sorting rule; and at the cell level, it uses a rule based on the number of modifications and user weight, ensuring global state consistency. After processing, the server uses its independently maintained last synchronization point for each client to push incremental commands generated by other collaborators after that synchronization point, thereby minimizing network load.

[0077] In this embodiment of the invention, a dynamic formula dependency graph is constructed, and the affected formula cells are incrementally recalculated based on the dynamic formula dependency graph. Formula calculation is then performed using a hybrid client-server calculation method, including:

[0078] When a formula is written into a cell, the formula is parsed through an abstract syntax tree to extract the list of cell identifiers it depends on, generate a formula fingerprint, and update the input dependency table and the output dependency table. The input dependency table records the mapping from the dependent cell to the set of formula cells that depend on it, and the output dependency table records the mapping from the formula cell to the set of cells it depends on.

[0079] When a formula is modified, old dependencies are removed from the input dependency table and output dependency table, and new dependencies are added;

[0080] When cell data changes, the input dependency table is queried to determine all affected formula cells, and the affected formula cells are recalculated.

[0081] Formulas are classified as lightweight or complex based on the number of operators and the preset function complexity. Lightweight formulas are calculated by the client and the calculation result is associated with the formula fingerprint and cached in local storage. Complex formulas are calculated by the client by requesting the server with the formula fingerprint and a snapshot of the dependent cell values. If the server does not find the formula fingerprint in the distributed cache or the dependent values ​​change, it calls the computing cluster to recalculate and return the result.

[0082] Specifically, when a user enters a formula in a cell for the first time or modifies an existing formula, the formula string is deeply parsed using an abstract syntax tree to extract a list of all cell identifiers referenced by the formula. For example, =SUM(A1:B3) is transformed into a specific list containing the IDs of multiple cells such as A1, A2, and A3. A unique formula fingerprint is generated for cache verification, and the mapping table is updated synchronously. The input dependency table records the mapping from the dependent cells to the set of formula cells that depend on them, while the output dependency table records the reverse mapping from the formula cells to the set of cells they depend on. When a formula is modified, the old dependencies of the formula are removed from both mapping tables, and the new dependencies are added to ensure that the dependency graph always reflects the latest table reference state.

[0083] When a user modifies the value of a regular data cell, the query input dependency table locates the set of all formula cells that directly or indirectly depend on that data cell. The calculation engine recalculates the formula cells in this set, reducing the calculation scope from the entire table to the related links, thereby completing the data update with minimal computational overhead.

[0084] To balance real-time computation with processing power, formulas are categorized into two types based on the number of operators and function complexity. For lightweight formulas involving simple operations or basic functions, calculations are performed locally on the client side, and the results are bound to a formula fingerprint and cached locally to ensure immediate feedback. For complex formulas with multiple nesting layers and containing complex functions, the client sends a calculation request to the server, carrying the formula fingerprint and snapshots of dependent cell values. Upon receiving the request, the server first queries the distributed cache. If the formula fingerprint matches and the dependent values ​​remain unchanged, the cached result is returned directly. Only when the fingerprint does not match or the dependent values ​​change is the WebAssembly-accelerated computing cluster invoked for recalculation and the result returned. These technical features achieve precise control over the computational scope through a dependency graph, rational allocation of computing resources through hybrid computing, and reduced redundant calculation overhead through multi-level caching, thus achieving significant performance improvements in scenarios involving tables with numerous complex formulas.

[0085] In this embodiment of the invention, offline synchronization is performed based on the three-level data model of table semantic CRDT, including:

[0086] The network status is detected through a heartbeat detection mechanism. When the network is interrupted, it enters offline mode. In offline mode, user operation commands are pre-merged in the local CRDT engine, stored in the cache, and marked as unsynchronized.

[0087] After the network is restored, the client sends a synchronization request to the server. The synchronization request includes the worksheet identifier, the last synchronized version vector, and the number of unsynchronized instructions.

[0088] The client uploads operation instructions marked as out of sync in ascending order of timestamp, so that the server can merge the conflicting operation instructions based on the three-level data model and return the merged result. The remote instructions after the last synchronized version vector are obtained and applied to the local three-level model in sequence.

[0089] After synchronization is complete, the client calculates the data hash values ​​of the table layer, structure layer and cell layer respectively and sends a verification request to the server. If the corresponding hash values ​​returned by the server are inconsistent, the comparison is performed layer by layer to locate the difference data, and the difference data returned by the server is received for targeted repair.

[0090] Specifically, a heartbeat detection mechanism continuously sends probe requests to the server. When there is no response after multiple consecutive heartbeats, a network interruption is determined, and the system automatically switches to offline mode. In offline mode, all user editing operations remain unaffected, and user actions continue to be captured and atomic operation instructions are generated. Atomic operation instructions are first pre-merged in the local CRDT engine, that is, potential concurrent modifications are pre-reconciled according to predetermined conflict resolution rules. Then, the merged instructions are stored sequentially in the local IndexedDB cache and marked as unsynchronized. Even if the browser restarts or the network is disconnected for a long time, the user's operation records are still completely preserved, ensuring a high operation retention rate.

[0091] Once the heartbeat detection resumes, the client sends a synchronization request to the server. This request includes the globally unique identifier of the current worksheet, the last synchronized version vector of the local record, and the number of backlogged unsynchronized instructions. The client uploads all cached unsynchronized operation instructions to the server in ascending order of timestamp. Upon receiving the offline instructions, the server uses the CRDT three-level data model to resolve conflicts between these instructions and operations generated by other clients during the same period, and returns the merged result to the client. After confirming that the local instructions have been globally accepted, the client retrieves all remote instructions generated by other collaborators after the last synchronized version vector from the server and applies them to the local three-level model one by one in global order. This ensures that offline operations are prioritized for integration into the global state while also ensuring that no collaborative modifications by other users are overlooked.

[0092] After synchronization, the client performs hash calculations on the table layer metadata, all row and column identifier sequences in the structure layer, and the content and format of the cell layer, respectively, and sends the hash values ​​of the three levels to the server for comparison. If the hash values ​​of a certain level are inconsistent, the client compares each item at that level to locate the specific discrepancy in the row, column, or cell. The client requests the server to return these discrepancies and performs targeted repair and updates on the local model, avoiding the retransmission of all data. This refines the granularity of verification and repair from the entire table to the individual element level, significantly shortening data alignment time and enabling efficient breakpoint resumption.

[0093] In this embodiment of the invention, the method may further include the following steps:

[0094] When offline, if a complex formula calculation is triggered, the cached result corresponding to the formula is retrieved from local storage, the cached result is used as a temporary calculation result and marked as expired.

[0095] After the network is restored, the client sends a formula verification request to the server. The formula verification request includes the formula fingerprint of the formula and a snapshot of the value of the current dependent cell.

[0096] Receive the recalculation result returned by the server. If the recalculation result is inconsistent with the temporary calculation result, update the local cell data and view, and synchronously update the formula cache and formula fingerprint of the formula in local storage.

[0097] Specifically, in offline mode, if a user's operation triggers the recalculation of a complex formula, the calculation request cannot be sent to the server due to network interruption. The client retrieves the previously cached calculation result of the formula from local storage, returns the historical cached result as the current temporary calculation result to the user, and marks the result as expired on the interface to prompt the user that the data may not be calculated based on the latest dependency values. This ensures that even in a network-disconnected environment, the user can still see a valuable calculation result, rather than a blank or error message.

[0098] Once the network is restored, the client automatically iterates through all formula cells marked as expired, initiating a formula verification request for each one. Each verification request includes a unique formula fingerprint for the formula, used by the server to quickly identify the formula's calculation logic; and a snapshot of the values ​​of the currently dependent cells, i.e., the current actual data values ​​of all cells referenced by the formula. Upon receiving the request, the server recalculates based on the latest dependent values ​​and returns the result. After receiving the recalculated result from the server, the client compares it with the expired result in its local temporary cache. If they match, it means that the dependent data was not collaboratively modified by other users during the offline period, and the local temporary result is the accurate value; only the expired mark needs to be removed. If they do not match, it means that the dependent data was modified by other users during the offline period. The client immediately updates the data content and view display of the local cells, and simultaneously updates the correct result returned by the server to the locally stored formula cache and formula fingerprint record, ensuring that the latest valid cache can be used in subsequent offline scenarios.

[0099] Figure 3 This is a schematic diagram of the structure of an online spreadsheet processing device based on the CRDT algorithm provided in an embodiment of the present invention. Figure 3 As shown, the device includes:

[0100] The three-level data model building unit 310 is used to build a three-level data model of table semantic CRDT. The three-level data model includes a table layer, a structure layer and a cell layer. The table layer is used to manage table metadata and version vectors, the structure layer is used to manage the addition and deletion operations of rows and columns, and the cell layer is used to store multi-version data of cells.

[0101] The hierarchical incremental synchronization execution unit 320 is used to perform hierarchical incremental synchronization based on the CRDT three-level data model. The hierarchical incremental synchronization includes differential compression and local caching of operation instructions on the client side, and conflict merging and incremental broadcasting of operation instructions on the server side.

[0102] Formula calculation unit 330 is used to construct a dynamic formula dependency graph, perform incremental recalculation of affected formula cells based on the dynamic formula dependency graph, and execute formula calculation through a hybrid client-server calculation method.

[0103] The offline synchronization unit 340 is used to perform offline synchronization based on the CRDT three-level data model. Offline synchronization includes storing local operation instructions in a cache when offline, uploading unsynchronized local instructions in sequence after the network is restored, retrieving remote instructions, and repairing data through hierarchical hash verification.

[0104] The online spreadsheet processing device based on the CRDT algorithm provided in this embodiment of the invention can execute the online spreadsheet processing method based on the CRDT algorithm provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the method execution.

[0105] Figure 4 A schematic diagram of an electronic device 10, which can be used to implement embodiments of the present invention, is shown. 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 (e.g., 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 invention described and / or claimed herein.

[0106] like Figure 4 As 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.

[0107] 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.

[0108] 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 online spreadsheet processing methods based on the CRDT algorithm.

[0109] In some embodiments, the CRDT-based online spreadsheet processing method can 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 can 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 CRDT-based online spreadsheet processing method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the CRDT-based online spreadsheet processing method by any other suitable means (e.g., by means of firmware).

[0110] 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.

[0111] Computer programs used to implement the methods of the present invention 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.

[0112] In the context of this invention, 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 may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may 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 fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0113] 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).

[0114] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or 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.

[0115] 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.

[0116] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0117] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. 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 invention should be included within the scope of protection of this invention.

Claims

1. An online spreadsheet processing method based on the CRDT algorithm, characterized in that, include: A three-level data model for table semantic CRDT is constructed. The three-level data model includes a table layer, a structure layer, and a cell layer. The table layer is used to manage table metadata and version vectors, the structure layer is used to manage the addition and deletion operations of rows and columns, and the cell layer is used to store multi-version data of cells. Based on the CRDT three-level data model, hierarchical incremental synchronization is performed. The hierarchical incremental synchronization includes differential compression and local caching of operation instructions on the client side, and conflict merging and incremental broadcasting of the operation instructions on the server side. A dynamic formula dependency graph is constructed, and the affected formula cells are incrementally recalculated based on the dynamic formula dependency graph. Formula calculation is then performed through a hybrid client-server calculation method. Offline synchronization is performed based on the CRDT three-level data model. The offline synchronization includes storing local operation instructions in a cache while offline, uploading unsynchronized local instructions and retrieving remote instructions in sequence after the network is restored, and repairing data through hierarchical hash verification.

2. The method according to claim 1, characterized in that, The construction of the three-level CRDT data model for table semantics includes: Initialize the table layer, assign a globally unique worksheet identifier to each worksheet, initialize the final write-win mapping structure to store worksheet metadata and version vectors, and define the metadata conflict resolution rule as comparing operation timestamps, retaining the operation result with the largest timestamp; Construct a structural layer, creating two-dimensional sets of last written winning elements for rows and columns respectively. Assign a globally unique row identifier to each row and a globally unique column identifier to each column. Configure an existence flag attribute and a deletion flag attribute for each row or column node. The existence flag attribute indicates whether the row or column currently exists. The deletion flag attribute includes a boolean value indicating whether it has been deleted and the deletion time. Configure the cell layer, generate cell identifiers based on row identifiers and column identifiers, and use an incrementing counter and modification vector to store cell data. Each version in the modification vector includes content, format, formula, modifier identifier and timestamp.

3. The method according to claim 1, characterized in that, The operation instructions are atomic operation instructions, which include operation type, target identifier, version vector and timestamp basic fields, and for cell operations, they extend to content, format and formula fields, and for row or column operations, they extend to insertion position index and deletion time fields.

4. The method according to claim 3, characterized in that, The hierarchical incremental synchronization based on the table semantic CRDT three-level data model includes: On the client side, user operations are captured and atomic operation instructions are generated through an event delegation mechanism. The operation instructions are compressed to retain only the changed fields. The compressed instructions are pre-applied locally and stored in the local cache and marked as unsynchronized. When the network is interrupted, the system automatically enters offline mode and writes all operation instructions only to the local cache. After receiving client operation instructions via a long connection on the server side, the version vector is verified. Operation instructions are collected in batches at preset time intervals. A globally ordered sequence is generated based on the timestamp and client identifier. Conflicts in the table layer, structure layer and cell layer are merged based on the CRDT three-level data model. The last synchronization point is maintained for each client, and incremental instructions after the last synchronization point are pushed to the corresponding client.

5. The method according to claim 1, characterized in that, The construction of a dynamic formula dependency graph, the incremental recalculation of affected formula cells based on the dynamic formula dependency graph, and the execution of formula calculation through a hybrid client-server calculation method include: When a formula is written into a cell, the formula is parsed through an abstract syntax tree to extract a list of cell identifiers that it depends on, generate a formula fingerprint, and update the input dependency table and the output dependency table. The input dependency table records the mapping from the dependent cell to the set of formula cells that depend on it, and the output dependency table records the mapping from the formula cell to the set of cells it depends on. When a formula is modified, old dependencies are removed from the input dependency table and output dependency table, and new dependencies are added; When cell data changes, the input dependency table is queried to determine all affected formula cells, and the affected formula cells are recalculated. Formulas are classified into lightweight formulas or complex formulas based on the number of operators and the preset function complexity. Lightweight formulas are calculated by the client and the calculation result is associated with the formula fingerprint and cached in local storage. Complex formulas are calculated by the client by requesting the server to calculate them, carrying the formula fingerprint and a snapshot of the dependent cell value. If the server does not find the formula fingerprint in the distributed cache or the dependent value changes, it calls the computing cluster to recalculate and return the result.

6. The method according to claim 1, characterized in that, The offline synchronization based on the table semantic CRDT three-level data model includes: The network status is detected through a heartbeat detection mechanism. When the network is interrupted, it enters offline mode. In offline mode, user operation commands are pre-merged in the local CRDT engine, stored in the cache, and marked as unsynchronized. After the network is restored, the client sends a synchronization request to the server. The synchronization request includes the worksheet identifier, the last synchronized version vector, and the number of unsynchronized instructions. The client uploads the operation instructions marked as unsynchronized in ascending order of timestamp, so that the server can merge the conflict of the operation instructions based on the three-level data model and return the merge result. The server then obtains the remote instructions after the last synchronized version vector and applies them to the local three-level model in sequence. After synchronization is complete, the client calculates the data hash values ​​of the table layer, structure layer and cell layer respectively and sends a verification request to the server. If the corresponding hash values ​​returned by the server are inconsistent, the comparison is performed layer by layer to locate the difference data, and the difference data returned by the server is received for targeted repair.

7. The method according to claim 1, characterized in that, The method further includes: When offline, if a complex formula calculation is triggered, the cached result corresponding to the formula is retrieved from local storage, and the cached result is used as a temporary calculation result and marked as expired. After the network is restored, the client sends a formula verification request to the server. The formula verification request includes the formula fingerprint of the formula and a snapshot of the value of the current dependent cell. If the recalculation result returned by the server is inconsistent with the temporary calculation result, the local cell data and view are updated, and the formula cache and formula fingerprint of the formula in local storage are updated synchronously.

8. An online spreadsheet processing device based on the CRDT algorithm, characterized in that, include: The three-level data model construction unit is used to construct a three-level data model of table semantic CRDT. The three-level data model includes a table layer, a structure layer and a cell layer. The table layer is used to manage table metadata and version vectors, the structure layer is used to manage the addition and deletion operations of rows and columns, and the cell layer is used to store multi-version data of cells. The hierarchical incremental synchronization execution unit is used to perform hierarchical incremental synchronization based on the CRDT three-level data model. The hierarchical incremental synchronization includes differential compression and local caching of operation instructions on the client side, and conflict merging and incremental broadcasting of the operation instructions on the server side. The formula calculation unit is used to construct a dynamic formula dependency graph, perform incremental recalculation of affected formula cells based on the dynamic formula dependency graph, and execute formula calculation through a hybrid client-server calculation method. The offline synchronization unit is used to perform offline synchronization based on the CRDT three-level data model. The offline synchronization includes storing local operation instructions in a cache when offline, uploading unsynchronized local instructions in sequence and retrieving remote instructions after the network is restored, and repairing data through hierarchical hash verification.

9. An electronic device, characterized in that, The electronic device includes: At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the online spreadsheet processing method based on the CRDT algorithm according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the online spreadsheet processing method based on the CRDT algorithm as described in any one of claims 1-7.

11. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the online spreadsheet processing method based on the CRDT algorithm according to any one of claims 1-7.

Images