Dynamic joint multi-table structure change method based on template driving

Abstract

The invention discloses a dynamic joint multi-table structure change method based on template driving, and belongs to the technical field of enterprise financial management and data processing. The method specifically comprises the following steps: S1, determining a linkage type: automatically performing pattern recognition to determine the linkage type according to a worksheet and a position operated by a user; s2, applying a corresponding linkage method: applying the corresponding linkage method according to the determined linkage type; and S3, generating insertion content: executing a template content generation method on the worksheet needing to be inserted, replacing placeholders according to context, and generating final cell content. According to the method, automation and intelligentization of multi-table structure change are achieved through a step-by-step method flow, and the method specifically comprises the steps that a basic linkage method is adopted, specifically, a multi-table linkage relation is described in a configuration mode, a reference row + relative offset calculation mode is adopted, the effect of automatically and synchronously inserting in multiple association tables when a user operates one table is achieved, and the user experience is improved. And the structure consistency of the multiple meters in relative positions is ensured.

Description

Technical Field

[0001] This invention relates to the fields of enterprise financial management and data processing technology, and in particular to a method for dynamic joint multi-table structure modification based on template-driven approach. Background Technology

[0002] In corporate financial budgeting scenarios, it is often necessary to handle a large number of interrelated budget tables. A typical corporate budgeting scenario might involve hundreds or even thousands of interconnected budget tables. These tables have complex data dependencies and formula references. When a user inserts a new row of data into one table (such as adding a new product line), the corresponding structure needs to be inserted simultaneously in the corresponding positions of multiple related tables, ensuring the correctness of formula references.

[0003] Existing technologies have the following main technical shortcomings when handling multi-table structure synchronization: Method 1: Manual maintenance Users need to manually insert rows one by one, which is labor-intensive and prone to omissions. It is impossible to guarantee structural consistency among multiple tables. Formula references are prone to errors and require manual checking and correction one by one. Method 2: Simple script automation Batch row insertion via script, but lacks a flexible template method. It cannot handle complex placeholder replacements (such as row numbers, worksheet references, etc.). It is difficult to manage worksheet groups split by dimension in a unified manner (for example, generating a separate budget table for each department, and needing to maintain these department tables uniformly in the configuration). The lack of hierarchical cascading methods makes it impossible to handle cascading updates between parent and child tables. The root cause of these technical deficiencies lies in the lack of a template-driven structural modification method designed for multi-table structure linkage scenarios that supports dynamic replacement of placeholders. Summary of the Invention

[0004] Purpose of the invention: The purpose of this invention is to provide a template-driven method for dynamically combining multiple table structures; it can solve the problems of large workload in manual maintenance, inability to guarantee structural consistency between multiple tables, easy errors in formula references requiring manual inspection and correction, lack of flexible template methods for simple script automation, inability to handle complex placeholder replacements, difficulty in uniformly managing worksheet groups split by dimension, lack of hierarchical linkage methods, and inability to handle cascading updates of parent and child tables.

[0005] Technical Solution: To solve the above-mentioned technical problems, according to one aspect of the present invention, more specifically, a method for dynamic joint multi-table structure modification based on template-driven approach, specifically including the following steps: S1. Determine the linkage type: Based on the worksheet and location of the user's operation, automatically perform pattern recognition to determine the linkage type; S2. Apply the corresponding linkage method: Based on the determined linkage type, apply the corresponding linkage method; S3. Generate Inserted Content: For the worksheet to be inserted, execute the template content generation method, replace placeholders according to the context, and generate the final cell content; S4. Update mapping relationship: If a one-to-many linkage is involved, execute the hierarchical linkage update method to maintain the consistency of the parent table mapping relationship; S5. Formula Calculation and Front-End Rendering: After receiving the formula content and formula reference update notification for the newly inserted row, the formula engine performs dependency analysis and calculation to generate the calculation result of the cell. After receiving the data update notification, the front-end table component re-renders the affected worksheet area and displays the newly inserted row and its calculation result to the user. S6. Operation Completion Feedback: Prompt the user to complete the operation, displaying how many rows were inserted in how many worksheets. The user can view the newly inserted content in each worksheet and verify the linkage effect and formula calculation results.

[0006] Furthermore, in step S1, the linkage types include: simple multi-table synchronization, one-to-many master-slave relationship, and work table families isolated by dimension.

[0007] Furthermore, in step S2, if it is a simple multi-table synchronization, the basic linkage method is executed; if it is a one-to-many master-slave relationship, the advanced linkage method is executed; if it is a worksheet family isolated by dimension, the dimension expansion linkage method is executed first, and then the basic linkage method or the advanced linkage method is called.

[0008] Furthermore, the basic linkage method is a "row-to-row" multi-table linkage mode, used to handle multi-table structure synchronization scenarios. When a user inserts a row in one worksheet, the same number of rows are automatically inserted in the corresponding positions in multiple related worksheets. Specifically, it includes the following steps: The first step is to configure multi-table cascading relationships: Pre-configure multi-table cascading relationships, including: cascading type identifier (type) and worklist diffs; each worksheet configuration includes the worksheet code (worksheetCode) and the baseline row index (baseLineRowIndex); the baseline row index indicates the reference row position for cascading insertion in that worksheet. The second step is to receive user insert operation requests: When a user clicks the insert row operation at a specified position in the source worksheet, enters the number of rows to be inserted, and confirms, the system receives the insert request, extracts the operation position and row number information, and automatically determines the corresponding linkage type and the content template to be used based on the linkage configuration pre-bound to the current worksheet. The third step is to find the linkage configuration and calculate the relative offset: Based on the linkage type, find the corresponding linkage relationship configuration in the configuration, determine the base row index of the source worksheet in the configuration, and calculate the offset of the user's operation position relative to the base row. The offset is the difference between the row number inserted by the user and the base row index of the source worksheet. This offset indicates how many rows after the base row the user inserted the data. This offset will be applied to all related tables. The fourth step is to generate a list of multi-table cascading operations: traverse all related worksheets in the cascading configuration and calculate the insertion position for each target worksheet; the method for calculating the insertion position is the sum of the base row index and the offset of the target worksheet; generate an operation list for the insertion operations of all target worksheets, with each operation including: worksheet name, insertion position, number of rows to be inserted, and corresponding content template; Step 5: Perform batch insertion operations: According to the generated operation list, insert rows synchronously at the calculation positions of all related tables, perform the insertion structure operation on each worksheet, notify the formula engine to update the formula references in the affected areas, where the row numbers are automatically adjusted, and then fill the template content into the newly inserted rows. After the execution is completed, all related tables remain synchronized in relative position, and the formula references are automatically updated to the correct row numbers.

[0009] Furthermore, the advanced linkage method is a "one-to-many" linkage mode. It maintains a mapping relationship table to record the correspondence between rows in the main table and rows in the detail table, enabling the insertion of main items to drive batch insertion of details. This is used to handle parent-child relationship scenarios between the main table and the detail table. When a user inserts a main item in the main table, multiple corresponding detail records are automatically inserted into the detail table. Specifically, this includes the following steps: The first step is to configure advanced linkage relationships and mapping data: This involves configuring advanced linkage relationships, including: sourceLength, target, parent, and data. sourceLength is the row span between adjacent main items in the main table, typically 1. target is the detail table configuration, including the worksheet code (worksheetCode) and the default number of detail rows (defaultSubItemCount). parent is the parent linkage type, optional, used for two-level linkage. data is a mapping data array; each record contains worksheetCode, offsetIndex (main item offset), startIndex (starting row of the detail block), and subItemCount (number of detail block rows). During initialization, based on the configuration and existing data structure, initial mapping records are generated for each worksheet, recording the position and number of detail blocks corresponding to each main item. The second step is to receive the main table insertion request: The user inserts a main item at the specified position rowIndex in the main table, enters the number of rows to be inserted and confirms. After receiving the request, the system extracts information including the operation position and the number of rows to be inserted, and automatically determines the corresponding advanced linkage type identifier type and the content template to be used based on the linkage configuration pre-bound to the current worksheet or the insertion action. The third step is to calculate the relative offset and find matching mapping records: Calculate the offset of the insertion position relative to the base row of the main table. Based on the offset, find all records in the mapping table whose main item offset is greater than or equal to the offset. These records correspond to the insertion position and its subsequent main items, and their detail block positions need to be adjusted. The fourth step is to calculate the insertion position in the detail table and generate the insertion operation: Based on the first matching record found, obtain its startIndex as the insertion position in the detail table. If no matching record is found, it means that the insertion should be done at the end. Calculate the end position of the detail table as the insertion position, insert the defaultSubItemCount row of detail at the calculated position in the detail table, and create a new mapping record for the newly inserted main item. Step 5, update the affected mapping records: Iterate through all mapping records. For records that satisfy offsetIndex ≥ rowIndexOffset (i.e., the primary item at or after the insertion position), update the fields as follows to maintain mapping data consistency: The new primary item offset is the sum of the original primary item offset and the number of inserted primary item rows; the new detail block starting row index is the original detail block starting index and the number of inserted detail rows; where the number of inserted detail rows is the product of the inserted primary item and the default number of detail rows for each primary item, or the product of the inserted primary item and the actual number of inserted detail rows. To simplify configuration, prefix wildcards for worksheet codes are supported. When the target worksheetCode ends with an underscore, it will automatically expand to all worksheets in the current workbook that begin with that prefix, and the insert operation will be performed in all the expanded worksheets.

[0010] Furthermore, the dimension expansion and linkage method is used to handle worksheet family scenarios generated by dimensions. When a user inserts a row in a worksheet of a certain dimension, the insertion only occurs in the related tables of the same dimension, without affecting worksheets of other dimensions. Specifically, it includes the following steps: The first step is to configure the worksheet dimension suffix naming rules: Define the worksheet code using the following suffix rule: {Basic Code}_{Dimension Type}_{Dimension Value}; where the basic code uses the format sheet_{Business Group}_{Sequence Number}, and the dimension type includes: dept (department), yr (year), and mo (month); automatic recognition and same-dimensional linkage are provided for the "department dimension dept"; yr (year) and mo (month) are used as part of the worksheet code for reference and configuration, but are not used as the basis for automatically appending suffixes to dimension linkage. The second step is to receive the insertion request from the worksheet with the dimension: The user inserts rows in the worksheet with the department suffix, enters the number of rows to insert and confirms. After receiving the request, the system extracts information including the worksheet code and the operation position, and automatically determines the linkage type identifier (type) based on the linkage configuration pre-bound to the current worksheet or the insertion action. The third step is to identify the dimension suffixes of the worksheet: Parse the worksheet code using regular expressions to identify the dimension suffixes. For each worksheet code, extract the dimension suffixes, which are used in subsequent steps to ensure that the interaction only occurs between worksheets of the same dimension.

[0011] Step 4: Apply dimension suffixes to the associated configuration: Locate the linked configuration, obtain the list of associated tables, and append the identified dimension suffixes to the worksheet code of each associated table in the configuration; Step 5: Perform same-dimensional linked insertion: Use the worksheet code after adding dimensions to perform the insertion operation according to the basic linked method or the advanced linked method.

[0012] Furthermore, the template content generation method uses a "fragment template" as a carrier. After inserting the structure, it replaces the placeholders in the template according to the insertion context, thereby generating new row content that can be directly written to the worksheet. This addresses the problem of "how to automatically generate cell content for new rows and how to automatically adapt formula row numbers / cross-sheet references after simultaneous insertion into multiple tables." Specifically, it includes the following steps: The first step is to pre-define fragment templates and bind them to insertable locations: In the workbook template, a fragment template is pre-defined for each type of insertable business row. The fragment template consists of several rows, and each row consists of several cells. Each cell includes at least: cell value v, style / category c, and extended attribute a. The "fragment code corresponding to the insertion action" is declared at the allowed insertion location and is used to locate the template when the user triggers the insertion action. The second step is to obtain the insertion context parameters: When the user triggers the insertion, for each target worksheet that needs to be linked, determine the following context parameters: the actual starting row number of the insertion, the dimension suffix of the current worksheet, the set of sub-tables / the list of expandable sub-tables of the current worksheet, and the letter or column coordinate of the column where the current cell is located; if the target worksheet is a one-to-many advanced linkage, the master item number / master table location value also needs to be determined; The third step is to perform cell-by-cell replacement according to the placeholder rules: traverse the content of each cell in the fragment template and replace the placeholders that appear, specifically including: row number placeholder: ${ROW} or ${ROW+n}, replaced with "the actual inserted row number starting from 1" and its offset; main item placeholder: ${BASE_ROW} or ${BASE_ROW+n}, replaced with "main item sequence number / main table location value" and its offset; dimension placeholder: ${DEPT}, replaced with the current worksheet dimension suffix; sub-table aggregation placeholder: ${SUB_SHEETS!A1}, replaced with a list of sub-table references that meet the conditions, connected by commas; column placeholder: ${COL} or ${COL+n}, replaced with the corresponding column letter; if the sub-table aggregation placeholder does not match any sub-table, and the cell content is in the form of "pure SUM aggregation and only contains this placeholder", then the cell content is set to empty to avoid generating meaningless aggregation formulas; Step 4: Generate the final fragment and write it to the newly inserted area: Copy and concatenate the fragment template after replacing the placeholders according to the number of rows to be inserted to form an "array of cell contents to be written". Then, in the newly inserted row area of ​​the target worksheet, batch write the values ​​and attributes of each cell; Step 5: Trigger formula dependency update and recalculation: After the structure insertion and cell writing are completed, the formula engine is triggered to perform dependency analysis and calculation on the affected area to ensure that cross-table references and row number references remain correct in the final result.

[0013] Furthermore, the hierarchical linkage update method is used to handle parent-child table relationships. When a row is inserted or deleted in the child table, the mapping data maintained by the parent table is automatically updated. Specifically, it includes the following steps: The first step is to configure the parent-child table relationship: In the advanced linkage configuration, declare the parent field to establish the parent-child table relationship. In the configuration of the child table, set the parent attribute to point to the linkage type of the parent table, indicating that when the child table undergoes structural changes, the mapping data maintained by the parent table needs to be updated. The second step is to trigger a structure change in the sub-table: the user inserts or deletes a number of rows at the specified position changeRowIndex in the sub-table, with changeRowCount being a positive number for insertion and a negative number for deletion. After receiving the change request, the change position and row number are retrieved. Step 3: Traverse the parent table mapping records and determine the change position: Search for the parent configuration of the child table, locate the parent table, obtain all the mapping records maintained by the parent table, and perform the following judgments for each mapping record: Calculate the end boundary endIndex of the detail block and determine the relationship between the change position and the detail block; among them, the end boundary of the detail block is the sum of the starting row index of the detail block and the number of rows in the detail block, and the range of the detail block is [startIndex, endIndex); when determining the relationship between the change position and the detail block, if the change position index changeRowIndex ≥ the end boundary endIndex, the change occurs below this detail block and does not affect this mapping, so skip it without updating; if the change position index changeRowIndex falls within the range of the detail block, that is, satisfies startIndex ≤ changeRowIndex < endIndex, the change occurs inside this detail block, and update the number of rows in the detail block according to the following rules: the number of rows in the detail block is the sum of the number of rows in the detail block and the number of changed rows; if the change position index changeRowIndex < the starting row index startIndex of the detail block, the change occurs above this detail block, and translate the index according to the following rules: the starting row index of the detail block is the sum of the starting row index of the detail block and the number of changed rows; if the main item offset is maintained, the main item offset is the sum of the main item offset and the number of changed rows; Step 4: Save the updated mapping data: Save the updated mapping records to the mapping data of the parent table. The update of the mapping data ensures that the parent table can accurately know which rows in the child table the detail block corresponding to each main item is currently in, providing correct range information for subsequent formula references and data queries; Step 5: Save the updated mapping relationship table: Save the updated mapping records to the mapping relationship table of the parent table; The mapping data maintained by the parent table is not only used for automatic updates during structural changes but also for formula reference scenarios.

[0014] Beneficial effects: Through a step-by-step method process, the automation and intelligence of multi-table structure changes are achieved, specifically including: Basic linkage method: Describe the multi-table linkage relationship through a configuration method, adopt the calculation method of the reference row + relative offset, and achieve the effect of automatically inserting synchronously in multiple associated tables when the user operates on one table, ensuring the structural consistency of multiple tables in relative positions, reducing the workload of manual table-by-table operations, and avoiding omissions and errors.

[0015] Advanced linkage method: By maintaining the correspondence between the main table row and the detail table row in the mapping data record, achieve a one-to-many linkage where 1 row in the main table automatically drives the insertion of multiple rows in the detail table, and automatically update the mapping data to maintain the consistency of the parent-child relationship. The user only needs to operate on the main table to automatically complete the batch insertion and mapping maintenance of the detail table, greatly reducing manual operations.

[0016] Dimension expansion and linkage method: By recognizing dimension suffixes and automatically appending steps, it realizes linkage insertion isolated by dimension (such as department), supports independent management and linkage of multi-department, multi-period and other dimensional worksheet families, avoids cross-dimensional misoperation, and improves the flexibility and security of data management.

[0017] Template content generation method: By using a predefined template containing placeholders, the placeholders (ROW, BASE_ROW, DEPT, SUB_SHEETS, COL, etc.) are automatically replaced according to the context when inserting, and cell content containing correct row number references, cross-sheet references, and aggregation formulas is dynamically generated, which greatly reduces the workload of manually configuring formulas and significantly reduces the cost of manually maintaining the consistency between formulas and content.

[0018] Hierarchical linkage update method: By judging the relationship between the changed position and the mapping record, the mapping data maintained by the parent table is automatically updated. It supports recursive upward updates of multi-level parent-child table relationships, ensuring that the mapping data is always consistent with the actual structure and providing correct range information for formula references.

[0019] Through configurable, template-based, and dimensional design principles, it provides more efficient and user-friendly solutions for vertical fields such as financial budgeting. In typical multi-table budgeting scenarios, it can significantly reduce the workload of maintaining each table and lower the cost of formula reference maintenance, and can support the configuration and coordinated execution of complex budgeting systems with large-scale multiple tables (such as hundreds or thousands of tables). Attached Figure Description

[0020] Figure 1 This is a schematic diagram of the overall process. Figure 2 This is a schematic diagram of the basic linkage method; Figure 3 This refers to the template content generation method and placeholder replacement process. Figure 4 This is a diagram illustrating advanced linkage mapping relationships; Figure 5 This is a schematic diagram of the dimensional expansion and linkage method; Figure 6 This is a schematic diagram of the hierarchical linkage update method. Detailed Implementation

[0021] To make the technical solution of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings and specific embodiments.

[0022] 1. Determine the linkage type: Based on the worksheet and location of the user's operation, the linkage type is automatically determined by pattern recognition. The linkage types include: simple multi-table synchronization, one-to-many master-slave relationship, and worksheet families isolated by dimension.

[0023] 2. Apply the corresponding linkage method: Based on the determined linkage type, apply the corresponding linkage method. If it is a simple multi-table synchronization, execute the basic linkage method; if it is a one-to-many master-slave relationship, execute the advanced linkage method; if it is a worksheet family isolated by dimension, first execute the dimension expansion linkage method, and then call the basic linkage method or the advanced linkage method.

[0024] The basic linkage method is a "row-to-row" multi-table linkage approach, used to handle scenarios involving the synchronization of multiple table structures. When a user inserts a row in one worksheet, the same number of rows are automatically inserted in the corresponding positions across multiple related worksheets. Specifically, this includes the following steps: Step 1, Configure Multi-Table Linkage: Pre-configure multi-table linkage relationships, including: linkage type identifier (type) and worklist diffs; each worksheet configuration includes a worksheet code (worksheetCode) and a baseline row index (baseLineRowIndex); the baseline row index indicates the reference row position for the linkage insertion in this worksheet. Configure a linkage type named "Product Linkage" containing 3 related tables: Budget sheet: worksheetCode="budget_sheet", base row index = 3 (corresponding to the 4th row of the interface) Cost sheet: worksheetCode="cost_sheet", baseline row index = 2 (corresponding to the 3rd row of the interface) Profit and loss statement: worksheetCode="profit_sheet", base row index = 9 (corresponding to row 10 on the interface); The second step is to receive user insert operation requests: The user clicks the "Insert Row" operation at a specified position in the source worksheet, enters the number of rows to be inserted, and confirms. After receiving the insert request, the operation position and row number information are extracted, and the corresponding linkage type and the content template to be used are automatically determined according to the pre-bound linkage configuration of the current worksheet. For example, the user clicks "Insert Row" in row 6 of the budget sheet, enters the number of rows to be inserted = 1, and confirms. The third step is to find the linkage configuration and calculate the relative offset: Based on the linkage type, find the corresponding linkage relationship configuration in the configuration, determine the base row index of the source worksheet in the configuration, and calculate the offset of the user's operation position relative to the base row. The offset is calculated as: Offset = Row number inserted by the user - Base row index of the source worksheet. This offset indicates how many rows after the base row the user inserts. This offset will be applied to all related tables. If the user inserts in the 6th row of the budget table, and the base row index of the budget table is 3, then the offset is 6 - 3 = 3, meaning the user inserts 3 rows after the base row. Step 4: Generate a list of multi-table cascading operations: Iterate through all related worksheets in the cascading configuration and calculate the insertion position for each target worksheet. The insertion position is calculated as the sum of the base row index and the offset of the target worksheet. Generate an operation list for all insertion operations of the target worksheets, with each operation including: worksheet name, insertion position, number of rows to insert, and corresponding content template. Budget Sheet: Insertion Position = 6 (3 + 3, corresponding to row 6 on the interface) Cost table: Insert position = 5 (2 + 3, corresponding to row 5 on the interface) Profit and loss statement: Insertion position = 12 (9 + 3, corresponding to row 12 on the interface).

[0025] Step 5: Batch Execution of Insertion Operations: Following the generated operation list, rows are synchronously inserted at the calculation positions in all related tables. The insertion structure operation is performed on each worksheet, instructing the formula engine to update formula references in the affected areas. Row numbers are automatically adjusted. Template content is then filled into the newly inserted rows. After execution, all related tables remain synchronized in relative positions, and formula references are automatically updated to the correct row numbers. By configuring the description of linkage relationships and using a base row + relative offset calculation method, the system automatically inserts the same number of rows synchronously in multiple related tables when the user operates on one table. This ensures structural consistency across multiple tables in relative positions, reduces the workload of manual table-by-table operations, and avoids omissions and errors.

[0026] The advanced linkage method is a "one-to-many" linkage mode. It maintains a mapping relationship table to record the correspondence between the main table rows and the detail table rows, so as to realize the main item insertion driving the batch insertion of details. It is used to handle the parent-child relationship scenario between the main table and the detail table. When a user inserts a main item in the main table, multiple corresponding detail records are automatically inserted in the detail table.

[0027] The so-called "mapping relationship table" refers to a data table that records which rows each main item occupies in the detail table when each row in the main table (called a "main item") corresponds to multiple rows in the detail table (called a "detail block"). For example, the first project in the project table corresponds to the task details in rows 5-9 of the task table, and the second project corresponds to the task details in rows 10-14 of the task table. The specific steps include: The first step is to configure advanced linkage relationships and mapping data: This configuration includes `sourceLength`, `target`, `parent`, and `data`. `sourceLength` is the row span between adjacent main items in the main table, typically 1. `target` configures the detail table, including the worksheet code `worksheetCode` and the default number of detail rows `defaultSubItemCount`. `parent` is the parent linkage type, optional, used for two-level linkage. `data` is a mapping data array; each record contains `worksheetCode`, `offsetIndex` (main item offset), `startIndex` (starting row of the detail block), and `subItemCount` (number of detail block rows). During initialization, based on the configuration and existing data structure, initial mapping records are generated for each worksheet, recording the position and number of detail blocks for each main item. Example configuration: The configuration name is "Project Task Linkage," the main table is the project table (each project occupies 1 row), and the detail table is the task table (each project has 5 task detail rows by default). The initial mapping data includes: the mapping record for the first project points to the 5th row of the task table (5 rows in total); the mapping record for the second project points to the 10th row of the task table (5 rows in total). Each mapping record contains fields such as worksheet code, primary item offset, starting row index of detail block, and number of detail block rows.

[0028] The second step is to receive the main table insertion request: The user inserts a main item at the specified position `rowIndex` in the main table, enters the number of rows to insert, and confirms. After receiving this request, the system extracts information including the operation position and the number of rows to insert, and automatically determines the corresponding advanced linkage type identifier `type` and the content template to be used based on the current worksheet or the linkage configuration pre-bound to the insertion action. For example: The user inserts a new item in row 5 of the project table and confirms.

[0029] The third step is to calculate the relative offset and find matching mapping records: Calculate the offset of the insertion position relative to the base row of the main table. Based on the offset, search the mapping table for all records whose main item offset is greater than or equal to that offset. These records correspond to the insertion position and its subsequent main items, and their detail block positions need to be adjusted. For example, if the offset = 2, then search for all mapping records whose main item offset is greater than or equal to 2.

[0030] Step 4: Calculate the insertion position in the detail table and generate the insertion operation: Based on the first matching record found, obtain its startIndex as the insertion position in the detail table. If no matching record is found, it means the insertion is at the end. Calculate the end position of the detail table as the insertion position, insert the defaultSubItemCount row at the calculated position in the detail table, and create a new mapping record for the newly inserted main item. Example: Insert 5 rows of task details (the default number of task rows for a new project) at the 10th row position of the task table, create a new mapping record, and record the worksheet code as "task_sheet", main item offset = 2, detail block start row index = 10, and detail block row number = 5.

[0031] Step 5: Update the affected mapping records: Iterate through all mapping records. For records where offsetIndex ≥ rowIndexOffset (i.e., the main item at or after the insertion position), update the fields as follows to maintain mapping data consistency: The new main item offset is the sum of the original main item offset and the number of inserted main item rows; the new detail block starting row index is the original detail block starting index and the number of inserted detail rows; where the number of inserted detail rows is the product of the inserted main item and the default number of detail rows per main item, or the product of the inserted main item and the actual number of inserted detail rows. Example: For an original record, its main item offset = 2 (offsetIndex = 2) and detail block starting row index = 10 (startIndex = 10). When one main item is inserted at this position along with 5 detail rows, the record is updated to offsetIndex = 3 and startIndex = 15.

[0032] By maintaining the correspondence between rows in the master table and rows in the detail table through mapping data, the insertion of one row in the master table automatically drives the insertion of multiple rows in the detail table, and automatically updates the mapping data to maintain the consistency of the parent-child relationship. Users only need to operate on the master table to automatically complete the batch insertion and mapping maintenance of the detail table, greatly reducing manual operations.

[0033] To simplify configuration, prefix wildcards for worksheet codes are supported. When the target worksheetCode ends with an underscore, it will automatically expand to all worksheets in the current workbook that begin with that prefix, and the insert operation will be performed in all the expanded worksheets.

[0034] The dimension-based expansion and linkage method is used to handle scenarios where worksheet families are generated by dimensions. When a user inserts a row into a worksheet of a certain dimension, the row is only inserted in the related tables of the same dimension, without affecting worksheets of other dimensions. Specifically, it includes the following steps: The first step is to configure the worksheet dimension suffix naming rules: define the worksheet code using the suffix rule: {basic code}_{dimension type}_{dimension value}; where the basic code takes the form of sheet_{business group}_{serial number}, and the dimension type includes: dept (department), yr (year), mo (month); Example: sheet_1_1_dept_123: A sub-table of departments within the base table sheet_1_1, with department ID = 123. sheet_31_dept_456: A sub-table of departments within the base table sheet_31, with department ID = 456. sheet_1_1_yr_2024: A yearly sub-table of the base table sheet_1_1, where year = 2024. Automatic recognition and same-dimensional linkage are provided for the "department dimension (dept)"; the annual (yr) and monthly (mo) data are used as part of the worksheet code for reference and configuration, but are not used as the basis for automatically appending suffixes to dimension linkage. The second step is to receive the insertion request from the worksheet with the dimension: The user inserts rows in the worksheet with the department suffix, enters the number of rows to insert and confirms. After receiving the request, the system extracts information including the worksheet code and the operation position, and automatically determines the linkage type identifier (type) based on the linkage configuration pre-bound to the current worksheet or the insertion action. Example: The user inserts a row in row 5 of worksheet sheet_1_1_dept_123 (Sheet 1.1 of Department A) and confirms.

[0035] The third step is to identify the dimension suffixes in the worksheet: Parse the worksheet code using regular expressions to identify the dimension suffixes. For the worksheet code sheet_1_1_dept_123, extract the dimension suffix "dept_123".

[0036] This dimension suffix will be used in subsequent steps to ensure that the interaction only occurs between worksheets of the same dimension (same department).

[0037] The fourth step is to apply the dimension suffix to the associated configuration: find the linked configuration, obtain the list of associated tables, and append the identified dimension suffix to the worksheet code of each associated table in the configuration.

[0038] For example: Related tables in the configuration: sheet_1_1, sheet_31, sheet_51 Apply dimension suffixes: sheet_1_1_dept_123, sheet_31_dept_123, sheet_51_dept_123 This ensures that the interaction only occurs between worksheets 1, 2, and 3, and will not affect worksheets 4, 5, 6, 7, 8, or 9.

[0039] Step 5, perform same-dimensional linked insertion: Using the worksheet code with the appended dimension suffix, perform the insertion operation according to the basic linked method or the advanced linked method. Rows will be inserted in the corresponding positions of all related tables for Department 123; worksheets for Department 456 and Department 789 will not be affected.

[0040] After execution, all related tables of department 123 remain synchronized, while tables of other departments remain independent.

[0041] By recognizing dimension suffixes and automatically appending steps, it enables linked insertion isolated by dimension, supporting independent management and linkage of worksheet families with multiple departments and periods. When a user operates on a table in a specific department, it automatically only affects other related tables in that department, avoiding cross-departmental errors and improving the flexibility and security of data management.

[0042] III. Generating Inserted Content: For the worksheet to be inserted, execute the template content generation method. Replace placeholders based on the context to generate the final cell content. The template content generation method uses a "fragment template" as a carrier. After inserting the structure, it replaces the placeholders in the template according to the insertion context, thereby generating new row content that can be directly written to the worksheet. This solves the problem of "how to automatically generate cell content for new rows after simultaneous insertion into multiple sheets, and how to automatically adapt formula row numbers / cross-sheet references." Specifically, it includes the following steps: The first step is to pre-define fragment templates and bind them to insertable locations: In the workbook template, a fragment template is pre-defined for each type of insertable business row. The fragment template consists of several rows, and each row consists of several cells. Each cell includes at least: cell value v, style / category c, extended attribute a, and declares "fragment code corresponding to the insertion action" at the allowed insertion location, which is used to locate the template when the user triggers the insertion action.

[0043] Example: Define a 4-line fragment template for "Add Project": the first line is the project name line, the second and third lines are the details input lines, and the fourth line is the subtotal summary line.

[0044] The second step is to obtain the insertion context parameters: the actual starting row number for insertion (used as a row number placeholder). If it belongs to a one-to-many advanced linkage, the main item sequence number / the main table location value (used as a placeholder for the main item). The dimension suffix of the current worksheet (e.g., dept_123). The collection of sub-tables in the current worksheet / an expandable list of sub-tables (for aggregation across sub-tables). The letter or column coordinate of the current cell (used as a column placeholder).

[0045] The third step is to perform cell-by-cell replacement according to the placeholder rules: traverse the content of each cell in the fragment template and replace the placeholders that appear. Typical placeholders include: Line number placeholder: ${ROW} or ${ROW+n}, replace with "actual inserted line number (starting from 1)" and its offset. The primary item placeholder, ${BASE_ROW} or ${BASE_ROW+n}, is replaced with "primary item number / primary table location value" and its offset, used for detail tables to reference the primary table. Dimension placeholder: ${DEPT}, replace with the dimension suffix of the current worksheet, used for cross-sheet references within the same dimension. Sub-table aggregation placeholder: ${SUB_SHEETS!A1}, replace with a list of sub-table references that meet the criteria (connected by commas), used to generate the aggregation formula. Column placeholders: ${COL} or ${COL+n}, replaced with the corresponding column letter, used to concatenate cell coordinates. If the sub-table aggregation placeholder does not match any sub-table, and the cell content is in the form of "pure SUM aggregation containing only the placeholder" (e.g., =SUM(${SUB_SHEETS!B5})), the cell content is set to empty to avoid generating meaningless aggregation formulas; Step 4: Generate the final fragment and write it to the newly inserted area: Copy and concatenate the fragment template after replacing the placeholders according to the number of rows to be inserted to form an "array of cell contents to be written". Then, in the newly inserted row area of ​​the target worksheet, batch write the values ​​and attributes of each cell; Step 5: Trigger formula dependency update and recalculation: After the structure insertion and cell writing are completed, the formula engine is triggered to perform dependency analysis and calculation on the affected area to ensure that cross-table references and row number references remain correct in the final result.

[0046] The template content generation method can automatically generate the content of the new row after multi-table linkage insertion, and automatically adapt the row number, cross-table reference, same-dimensional reference and sub-table aggregate reference in the formula to the actual insertion position, which significantly reduces the cost of manually maintaining the consistency of formulas and content.

[0047] IV. Updating Mapping Relationships: If a one-to-many linkage is involved, a hierarchical linkage update method is executed to maintain the consistency of the parent table's mapping relationship. The hierarchical linkage update method is used to handle parent-child table relationships. When a row is inserted or deleted in the child table, the mapping data maintained by the parent table is automatically updated. Specifically, it includes the following steps: The first step is to configure the parent-child table relationship: declare the parent field in the advanced linkage configuration to establish the parent-child table relationship. In the configuration of the child table, set the parent attribute to point to the linkage type of the parent table, which means that when the child table undergoes structural changes, the mapping data maintained by the parent table needs to be updated.

[0048] Configuration Example: The sub-table B (materials detail table) is configured with `parent="Product Material Linkage"`, meaning that changes to sub-table B will notify the parent table A (product table) to update the mapping. Parent table A maintains the mapping data; each record contains `worksheetCode` (sub-table code), `offsetIndex` (main item offset), `startIndex` (detail block start row), and `subItemCount` (detail block row number).

[0049] The second step involves triggering a structure change in the sub-table: the user inserts or deletes a certain number of rows (changeRowCount, positive for insertion, negative for deletion) at a specified position (changeRowIndex) in sub-table B. Upon receiving this change request, the changed position and row count are retrieved.

[0050] For example: If a user inserts 2 rows in the 12th row of sub-table B, then the change position index = 12 (changeRowIndex = 12), and the number of rows changed = 2 (changeRowCount = 2).

[0051] The third step is to traverse the parent table mapping records and determine the change location: Locate the parent configuration of child table B, locate the parent table A, and retrieve all mapping records maintained by parent table A. Perform the following checks on each mapping record: Calculate the end index of the detail block: The end boundary of the detail block = the starting row index of the detail block + the number of rows in the detail block (the range of the detail block is [startIndex, endIndex)).

[0052] Determine the relationship between the changed location and the detail block: Case 1: If the change location index (changeRowIndex) is greater than or equal to the end boundary (endIndex), the change occurs below the detail block and does not affect the mapping; therefore, it is skipped and not updated.

[0053] Case 2: If the change location index (changeRowIndex) falls within the detail block (i.e., startIndex ≤ changeRowIndex < endIndex), then the change occurred within that detail block, and the detail block row number is updated according to the following rules: Detail block line count = Detail block line count + Change line count Case 3: If the change position index (changeRowIndex) is less than the start index (startIndex) of the detail block, the change occurs above that detail block, and the index is shifted according to the following rules: Detail block starting row index = Detail block starting row index + Number of changed rows If maintaining the primary item offset, then the primary item offset = primary item offset + number of changed rows. Example: Parent table A has two mappings: Product 1 (startIndex = 10, subItemCount = 4) and Product 2 (startIndex = 14, subItemCount = 4). Insert two rows into the 12th row of child table B: Product 1 mapping: Line 12 falls within the scope of the Product 1 detail block (lines 10 to 14, excluding line 14), which belongs to case 2. The update is to have 4 + 2 = 6 detail block lines.

[0054] Product 2 mapping: Line 12 is before line 14 of the starting line of the Product 2 detail block, which belongs to case 3. Update it to the starting line index of the detail block = 14 + 2 = 16.

[0055] The fourth step is to save the updated mapping data: save the updated mapping records to the mapping data in parent table A. Updating the mapping data ensures that parent table A can accurately know which rows in child table B each major item's corresponding detail block currently exists in, providing correct range information for subsequent formula references and data queries.

[0056] After execution, the mapped data of parent table A will be consistent with the actual structure of child table B.

[0057] Step 5: Save the updated mapping table: Save the updated mapping records to the mapping table of the parent table. Updating the mapping data ensures that the parent table can accurately know which rows in the child table the corresponding detail block for each main item is currently in, providing correct range information for subsequent formula references and data queries.

[0058] After execution, the mapping relationship table of the parent table remains consistent with the actual structure of the child table.

[0059] By employing a hierarchical linked update method, the parent table's mapping data is automatically maintained when the child table structure changes, eliminating the need for manual updates to the mapping records. Through precise determination of three positional relationships, it automatically decides which fields of which mapping records to update, maintaining consistency between the parent and child tables.

[0060] Mapped data application instructions: The mapping data maintained by the parent table is used not only for automatic updates during structural changes but also for formula reference scenarios. For example, row 3 (Product 1) of parent table A can use the formula =SUM(Sub-table B!C{startIndex}:C{startIndex+subItemCount-1}) to summarize all material costs for that product, automatically generating the correct cell range reference based on the mapping data. When rows are inserted or deleted in the sub-table, the mapping data is automatically updated, and the formula reference range is automatically adjusted accordingly, without requiring manual modification of the formula.

[0061] V. Formula Calculation and Front-End Rendering: After receiving the formula content and formula reference update notification for the newly inserted row, the formula engine performs dependency analysis and calculation to generate the calculation results of the cells. After receiving the data update notification, the front-end table component re-renders the affected worksheet area and displays the newly inserted row and its calculation results to the user.

[0062] VI. Operation Completion Feedback: The system displays operation completion information to the user, showing how many rows were inserted across how many worksheets. Users can view the newly inserted content in each worksheet to verify the linkage effect and formula calculation results.

[0063] The embodiments described above are merely examples of several implementations of the present invention, and while the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the present invention. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of the present invention, and these all fall within the scope of protection of the present invention. Therefore, the scope of protection of this patent should be determined by the appended claims.

Claims

1. A method for dynamically joining multiple table structures based on template-driven design, characterized in that... Specifically, it includes the following steps: S1. Determine the linkage type: Based on the worksheet and location of the user's operation, automatically perform pattern recognition to determine the linkage type; S2. Apply the corresponding linkage method: Based on the determined linkage type, apply the corresponding linkage method; S3. Generate Inserted Content: For the worksheet to be inserted, execute the template content generation method, replace placeholders according to the context, and generate the final cell content; S4. Update mapping relationship: If a one-to-many linkage is involved, execute the hierarchical linkage update method to maintain the consistency of the parent table mapping relationship; S5. Formula Calculation and Front-End Rendering: After receiving the formula content and formula reference update notification for the newly inserted row, the formula engine performs dependency analysis and calculation to generate the calculation result of the cell. After receiving the data update notification, the front-end table component re-renders the affected worksheet area and displays the newly inserted row and its calculation result to the user. S6. Operation Completion Feedback: Prompt the user to complete the operation, displaying how many rows were inserted in how many worksheets. The user can view the newly inserted content in each worksheet and verify the linkage effect and formula calculation results.

2. The method for dynamic joint multi-table structure modification based on template-driven approach according to claim 1, characterized in that... In step S1, the linkage types include: simple multi-table synchronization, one-to-many master-slave relationship, and work table families isolated by dimension.

3. The method for dynamic joint multi-table structure modification based on template-driven approach according to claim 2, characterized in that... In step S2, if it is a simple multi-table synchronization, the basic linkage method is executed; if it is a one-to-many master-slave relationship, the advanced linkage method is executed; if it is a worksheet family isolated by dimension, the dimension expansion linkage method is executed first, and then the basic linkage method or the advanced linkage method is called.

4. The method for dynamic joint multi-table structure modification based on template-driven approach according to claim 3, characterized in that... The basic linkage method is a "row-to-row" multi-table linkage approach, used to handle multi-table structure synchronization scenarios. When a user inserts a row in one worksheet, the same number of rows are automatically inserted in the corresponding positions in multiple related worksheets. Specifically, it includes the following steps: The first step is to configure multi-table cascading relationships: Pre-configure multi-table cascading relationships, including: cascading type identifier (type) and worklist diffs; each worksheet configuration includes the worksheet code (worksheetCode) and the baseline row index (baseLineRowIndex); the baseline row index indicates the reference row position for cascading insertion in that worksheet. The second step is to receive user insert operation requests: When a user clicks the insert row operation at a specified position in the source worksheet, enters the number of rows to be inserted, and confirms, the system receives the insert request, extracts the operation position and row number information, and automatically determines the corresponding linkage type and the content template to be used based on the linkage configuration pre-bound to the current worksheet. The third step is to find the linkage configuration and calculate the relative offset: Based on the linkage type, find the corresponding linkage relationship configuration in the configuration, determine the base row index of the source worksheet in the configuration, and calculate the offset of the user's operation position relative to the base row. The offset is the difference between the row number inserted by the user and the base row index of the source worksheet. This offset indicates how many rows after the base row the user inserted the data. This offset will be applied to all related tables. The fourth step is to generate a list of multi-table cascading operations: traverse all related worksheets in the cascading configuration and calculate the insertion position for each target worksheet; the method for calculating the insertion position is the sum of the base row index and the offset of the target worksheet; generate an operation list for the insertion operations of all target worksheets, with each operation including: worksheet name, insertion position, number of rows to be inserted, and corresponding content template; Step 5: Perform batch insertion operations: According to the generated operation list, insert rows synchronously at the calculation positions of all related tables, perform the insertion structure operation on each worksheet, notify the formula engine to update the formula references in the affected areas, where the row numbers are automatically adjusted, and then fill the template content into the newly inserted rows. After the execution is completed, all related tables remain synchronized in relative position, and the formula references are automatically updated to the correct row numbers.

5. The method for dynamic joint multi-table structure modification based on template-driven approach according to claim 3, characterized in that... The advanced linkage method is a "one-to-many" linkage approach. It maintains a mapping table to record the correspondence between rows in the main table and rows in the detail table, enabling main item insertion to drive batch detail insertion. This method is used to handle parent-child relationships between the main table and the detail table. When a user inserts a main item into the main table, multiple corresponding detail records are automatically inserted into the detail table. Specifically, it includes the following steps: The first step is to configure advanced linkage relationships and mapping data: This involves configuring advanced linkage relationships, including: sourceLength, target, parent, and data. sourceLength is the row span between adjacent main items in the main table, typically 1. target is the detail table configuration, including the worksheet code (worksheetCode) and the default number of detail rows (defaultSubItemCount). parent is the parent linkage type, optional, used for two-level linkage. data is a mapping data array; each record contains worksheetCode, offsetIndex (main item offset), startIndex (starting row of the detail block), and subItemCount (number of detail block rows). During initialization, based on the configuration and existing data structure, initial mapping records are generated for each worksheet, recording the position and number of detail blocks corresponding to each main item. The second step is to receive the main table insertion request: The user inserts a main item at the specified position rowIndex in the main table, enters the number of rows to be inserted and confirms. After receiving the request, the system extracts information including the operation position and the number of rows to be inserted, and automatically determines the corresponding advanced linkage type identifier type and the content template to be used based on the linkage configuration pre-bound to the current worksheet or the insertion action. The third step is to calculate the relative offset and find matching mapping records: Calculate the offset of the insertion position relative to the base row of the main table. Based on the offset, find all records in the mapping table whose main item offset is greater than or equal to the offset. These records correspond to the insertion position and its subsequent main items, and their detail block positions need to be adjusted. The fourth step is to calculate the insertion position in the detail table and generate the insertion operation: Based on the first matching record found, obtain its startIndex as the insertion position in the detail table. If no matching record is found, it means that the insertion should be done at the end. Calculate the end position of the detail table as the insertion position, insert the defaultSubItemCount row of detail at the calculated position in the detail table, and create a new mapping record for the newly inserted main item. Step 5, update the affected mapping records: Iterate through all mapping records. For records that satisfy offsetIndex ≥ rowIndexOffset (i.e., the primary item at or after the insertion position), update the fields as follows to maintain mapping data consistency: The new primary item offset is the sum of the original primary item offset and the number of inserted primary item rows; the new detail block starting row index is the original detail block starting index and the number of inserted detail rows; where the number of inserted detail rows is the product of the inserted primary item and the default number of detail rows for each primary item, or the product of the inserted primary item and the actual number of inserted detail rows. To simplify configuration, prefix wildcards for worksheet codes are supported. When the target worksheetCode ends with an underscore, it will automatically expand to all worksheets in the current workbook that begin with that prefix, and the insert operation will be performed in all the expanded worksheets.

6. The method for dynamic joint multi-table structure modification based on template-driven approach according to claim 1, characterized in that... The dimension expansion and linkage method described above is used to handle worksheet families generated by dimensions. When a user inserts a row in a worksheet of a certain dimension, the row is only inserted in the related tables of the same dimension, without affecting worksheets of other dimensions. Specifically, it includes the following steps: The first step is to configure the worksheet dimension suffix naming rules: Define the worksheet code using the following suffix rule: {Basic Code}_{Dimension Type}_{Dimension Value}; where the basic code uses the format sheet_{Business Group}_{Sequence Number}, and the dimension type includes: dept (department), yr (year), and mo (month); automatic recognition and same-dimensional linkage are provided for the "department dimension dept"; yr (year) and mo (month) are used as part of the worksheet code for reference and configuration, but are not used as the basis for automatically appending suffixes to dimension linkage. The second step is to receive the insertion request from the worksheet with the dimension: The user inserts rows in the worksheet with the department suffix, enters the number of rows to insert and confirms. After receiving the request, the system extracts information including the worksheet code and the operation position, and automatically determines the linkage type identifier (type) based on the linkage configuration pre-bound to the current worksheet or the insertion action. The third step is to identify the dimension suffixes of the worksheet: Parse the worksheet code using regular expressions to identify the dimension suffixes. For each worksheet code, extract the dimension suffixes, which are used in subsequent steps to ensure that the interaction only occurs between worksheets of the same dimension.

7. Fourth step, apply dimension suffixes to the associated configuration: find the linked configuration, obtain the list of associated tables, and append the identified dimension suffixes to the worksheet code of each associated table in the configuration; Step 5: Perform same-dimensional linked insertion: Use the worksheet code after adding dimensions to perform the insertion operation according to the basic linked method or the advanced linked method.

8. The method for dynamic joint multi-table structure modification based on template-driven approach according to claim 1, characterized in that... The template content generation method uses a "fragment template" as a carrier. After inserting the structure, it replaces the placeholders in the template according to the insertion context, thereby generating new row content that can be directly written to the worksheet. This method solves the problem of "how to automatically generate cell content for new rows and how to automatically adapt formula row numbers / cross-sheet references after simultaneous insertion into multiple tables". Specifically, it includes the following steps: The first step is to pre-define fragment templates and bind them to insertable locations: In the workbook template, a fragment template is pre-defined for each type of insertable business row. The fragment template consists of several rows, and each row consists of several cells. Each cell includes at least: cell value v, style / category c, and extended attribute a. The "fragment code corresponding to the insertion action" is declared at the allowed insertion location and is used to locate the template when the user triggers the insertion action. The second step is to obtain the insertion context parameters: When the user triggers the insertion, for each target worksheet that needs to be linked, determine the following context parameters: the actual starting row number of the insertion, the dimension suffix of the current worksheet, the set of sub-tables / the list of expandable sub-tables of the current worksheet, and the letter or column coordinate of the column where the current cell is located; if the target worksheet is a one-to-many advanced linkage, the master item number / master table location value also needs to be determined; The third step is to perform cell-by-cell replacement according to the placeholder rules: traverse the content of each cell in the fragment template and replace the placeholders that appear, specifically including: row number placeholder: ${ROW} or ${ROW+n}, replaced with "the actual inserted row number starting from 1" and its offset; main item placeholder: ${BASE_ROW} or ${BASE_ROW+n}, replaced with "main item sequence number / main table location value" and its offset; dimension placeholder: ${DEPT}, replaced with the current worksheet dimension suffix; sub-table aggregation placeholder: ${SUB_SHEETS!A1}, replaced with a list of sub-table references that meet the conditions, connected by commas; column placeholder: ${COL} or ${COL+n}, replaced with the corresponding column letter; if the sub-table aggregation placeholder does not match any sub-table, and the cell content is in the form of "pure SUM aggregation and only contains this placeholder", then the cell content is set to empty to avoid generating meaningless aggregation formulas; Step 4: Generate the final fragment and write it to the newly inserted area: Copy and concatenate the fragment template after replacing the placeholders according to the number of rows to be inserted to form an "array of cell contents to be written". Then, in the newly inserted row area of ​​the target worksheet, batch write the values ​​and attributes of each cell; Step 5: Trigger formula dependency update and recalculation: After the structure insertion and cell writing are completed, the formula engine is triggered to perform dependency analysis and calculation on the affected area to ensure that cross-table references and row number references remain correct in the final result.

9. A method for dynamic joint multi-table structure modification based on template-driven approach according to claim 1, characterized in that... The hierarchical linkage update method is used to handle parent-child table relationships. When a row is inserted or deleted in the child table, the mapping data maintained by the parent table is automatically updated. Specifically, it includes the following steps: Step 1: Configure the parent-child table relationship: Declare the "parent" field in the advanced linkage configuration to establish the parent-child table relationship. In the configuration of the child table, set the "parent" attribute to point to the linkage type of the parent table, indicating that when the structure of the child table changes, the mapping data maintained by the parent table needs to be updated. Step 2: The child table triggers a structure change: The user inserts or deletes a certain number of rows (changeRowCount) at the specified position (changeRowIndex) in the child table. Insertion is represented by a positive number, and deletion is represented by a negative number. After receiving the change request, extract the change position and the number of rows. Step 3: Traverse the mapping records of the parent table and judge the change position: Search for the "parent" configuration of the child table to locate the parent table and obtain all the mapping records maintained by the parent table. For each mapping record, perform the following judgment: Calculate the end boundary (endIndex) of the detail block and judge the relationship between the change position and the detail block. The end boundary of the detail block is the sum of the start row index of the detail block and the number of rows in the detail block, and the range of the detail block is [startIndex, endIndex). When judging the relationship between the change position and the detail block, if the change position index (changeRowIndex) ≥ the end boundary (endIndex), the change occurs below this detail block and does not affect this mapping, so skip it without updating. If the change position index (changeRowIndex) falls within the range of the detail block, that is, startIndex ≤ changeRowIndex < endIndex, the change occurs within this detail block, and update the number of rows in the detail block according to the following rule: The number of rows in the detail block is the sum of the number of rows in the detail block and the number of changed rows. If the change position index (changeRowIndex) < the start row index (startIndex) of the detail block, the change occurs above this detail block, and translate the index according to the following rule: The start row index of the detail block is the sum of the start row index of the detail block and the number of changed rows. If the main item offset is maintained, the main item offset is the sum of the main item offset and the number of changed rows. Step 4: Save the updated mapping data: Save the updated mapping records to the mapping data of the parent table. The update of the mapping data ensures that the parent table can accurately know which rows in the child table the detail block corresponding to each main item is currently in, providing correct range information for subsequent formula references and data queries. Step 5: Save the updated mapping relationship table: Save the updated mapping records to the mapping relationship table of the parent table. The mapping data maintained by the parent table is not only used for automatic updates during structure changes but also for formula reference scenarios.

Images