CN-122021583-A - Dynamic joint multi-table structure changing method based on template driving

CN122021583ACN 122021583 ACN122021583 ACN 122021583ACN-122021583-A

Abstract

The invention discloses a dynamic joint multi-table structure changing method based on template driving, and belongs to the technical field of enterprise financial management and data processing. The method comprises the following steps of S1, determining linkage types, S2, applying corresponding linkage methods, S3, generating insertion contents, namely, executing a template content generation method on a work sheet to be inserted, and generating final cell contents according to context replacement placeholders. The method comprises the steps of describing the multi-table linkage relation in a collocation mode, adopting a calculation mode of reference lines and relative offset, realizing the effect that a user automatically inserts synchronously in a plurality of associated tables when one table is operated, and ensuring the structural consistency of the multi-table in the relative position.

Inventors

Fu Chaohang
DENG JIE
XIA XU
GONG MIN
Zan Yulu

Assignees

上海阿欧一数字科技有限公司

Dates

Publication Date: 20260512
Application Date: 20260203

Claims (9)

1. The method for dynamically combining the multi-table structure change based on the template driving is characterized by comprising the following steps of: s1, determining a linkage type, namely automatically performing mode identification to determine the linkage type according to a worksheet and a position operated by a user; S2, applying a corresponding linkage method according to the determined linkage type; s3, generating inserted content, namely executing a template content generation method for a worksheet to be inserted, and generating final cell content according to the context replacement placeholders; S4, updating the mapping relation, namely if one-to-many linkage is involved, executing a hierarchical linkage updating method and maintaining consistency of the mapping relation of the father table; S5, formula calculation and front-end rendering, wherein the formula engine receives the formula content and formula quotation update notification of the newly inserted row, performs dependency analysis and calculation to generate a calculation result of the cell, and re-renders the affected worksheet region after the front-end table component receives the data update notification to display the newly inserted row and the calculation result thereof to a user; S6, operation completion feedback, namely prompting operation completion information to a user, displaying how many rows are inserted in how many worksheets, and checking newly inserted contents in each worksheet by the user to verify linkage effect and formula calculation result.
2. The method for dynamically associating multiple table structure changes based on template driving according to claim 1, wherein in said step S1, the linkage type comprises simple multiple table synchronization, one-to-many master-slave relationship, and worksheet family separated by dimension.
3. The method for changing the dynamic joint multi-table structure based on the template driving of claim 2, wherein in the step S2, a basic linkage method is executed if the simple multi-table synchronization is performed, an advanced linkage method is executed if the simple multi-table synchronization is performed in a one-to-many master-slave relationship, and a dimension unfolding linkage method is executed first if the working table group is isolated according to dimensions, and then the basic linkage method or the advanced linkage method is called.
4. The method for dynamically associating multiple table structure changes based on template driving according to claim 3, wherein said basic linkage method is a "row-to-row" multiple table linkage method for processing multiple table structure synchronization scenes, and when a user inserts a row in a worksheet, the same number of rows is automatically inserted in corresponding positions of multiple associated worksheets, comprising the steps of: The method comprises the steps of firstly, configuring a multi-table linkage relation, wherein the configuration content comprises linkage type identification type and a work list diffs, wherein each work table configuration comprises a work table code worksheetCode and a reference row index baseLineRowIndex; Step two, receiving a user insertion operation request, namely clicking an insertion row operation at a designated position of a source worksheet by a user, inputting the row number to be inserted, confirming, extracting operation position and row number information after receiving the insertion request, and automatically determining a corresponding linkage type and a content template to be used according to the pre-bound linkage configuration of the current worksheet; Searching linkage configuration and calculating relative offset, namely searching corresponding linkage relation configuration from the configuration according to linkage type, determining a reference line index of a source worksheet in the configuration, and calculating offset of a user operation position relative to the reference line, wherein the offset is the difference between a line number inserted by a user and the reference line index of the source worksheet, the offset represents the position of the user after the reference line, and the offset is applied to all associated tables; Step four, generating a multi-table linkage operation list, namely traversing all the associated worksheets in the linkage configuration, and calculating an insertion position for each target worksheet; generating an operation list by inserting all target worksheets, wherein each operation comprises a worksheet name, an insertion position, an insertion line number and a corresponding content template; And fifthly, performing inserting operations in batches, namely synchronously inserting rows at the calculation positions of all the associated tables according to the generated operation list, performing inserting structure operations on each worksheet, informing a formula engine to update formula references of affected areas, wherein row numbers are automatically adjusted, then filling template contents into newly inserted rows, and after the execution is completed, keeping the synchronization of all the associated tables at the relative positions, wherein the formula references are automatically updated to correct row numbers.
5. The method for changing the dynamic joint multi-table structure based on the template driving of claim 3, wherein the high-level linkage method is a 'line-to-line' linkage mode, and the main item insertion driving list batch insertion is realized by maintaining the corresponding relation between the main table row and the list table row in the mapping relation table, so as to process the father-son relation scene of the main table and the list table, and when a user inserts a main item in the main table, the list records corresponding to the lines are automatically inserted in the list table, and the method specifically comprises the following steps: The method comprises the steps of configuring a high-level linkage relation and mapping data, wherein the configuration content comprises sourceLength, target, parent, data, sourceLength is a row span between adjacent main items in a main table, the value of the row span is usually 1, a target is a list configuration comprising a work table code worksheetCode and a default list number defaultSubItemCount, parent is a parent linkage type and is optional and used for two-layer linkage, data is a mapping data array, each record comprises worksheetCode, offsetIndex main item offset, startIndex list block starting row and subItemCount list block line number, and in an initialization stage, an initial mapping record is generated for each work table according to the configuration and an existing data structure and the list block position and line number corresponding to each main item are recorded; Step two, receiving a main table insertion request, namely inserting a main item into a formulated position rowIndex of the main table by a user, inputting the number of insertion lines, confirming, extracting information comprising the operation position and the number of insertion lines after receiving the request, and automatically determining a corresponding high-level linkage type identifier type and a content template to be used according to linkage configuration which is bound in advance by the current worksheet or the insertion action; Calculating the offset of the insertion position relative to the main table reference line, and searching records with the offset greater than or equal to the offset of all main items in the mapping relation table according to the offset, wherein the records correspond to the insertion position and the main items following the insertion position, and the detail block position of the records needs to be adjusted; Step four, calculating the insertion position of the detail table and generating an insertion operation, namely acquiring a startIndex of the first matching record as the insertion position of the detail table according to the searched first matching record, if no matching record is found and indicated to be inserted at the last time, calculating the tail position of the detail table as the insertion position, inserting defaultSubItemCount rows of detail at the calculation position of the detail table, and creating a new mapping record for a main item which is not newly inserted; Step five, updating the affected mapping records, namely traversing all mapping records, and updating fields for records meeting offsetIndex more than or equal to rowIndexOffset, namely inserting positions and main items later, in a manner that the new main item offset is the sum of the original main item offset and the number of inserted main item lines, wherein the new detail block initial line index is the original detail block initial index and the number of inserted detail lines, and the inserted detail lines are the product of the inserted main item and each main item default detail line or the product of the inserted main item and the actually inserted detail line; To simplify configuration, a prefix wild card of worksheet code is supported, automatically expanded into all worksheets beginning with the prefix in current workbook when target worksheetCode ends with an underline, and an insert operation is performed in all worksheets after expansion.
6. The method for dynamically associating multiple table structure changes based on template driving according to claim 1, wherein said dimension expansion linkage method is used for processing a worksheet family scene generated according to dimensions, when a user inserts in a worksheet of a certain dimension, the worksheet is only inserted in linkage in an associated table of the same dimension, and other dimensions are not affected, and the method specifically comprises the following steps: The method comprises the steps of configuring a worksheet dimension suffix naming rule, namely defining worksheet codes by adopting suffix name rules, wherein the worksheet codes are { basic codes } _ { dimension types } _ { dimension values } {, the basic codes are in the form of sheet_ { business groups } _ { serial numbers }, the dimension types comprise dept departments, yr years and mo month; Step two, receiving an insertion request of a worksheet with dimension, namely, a user performs motor insertion row operation in the worksheet with department suffix, inputs the insertion row number and confirms, extracts information comprising worksheet codes and operation positions after receiving the request, and automatically determines a linkage type identification type according to linkage configuration pre-bound with the current worksheet or insertion action; and thirdly, identifying the dimension suffix of the worksheet, namely analyzing the worksheet code through the regular expression, identifying the dimension suffix, and extracting the dimension suffix for the worksheet code, wherein the dimension suffix is used for the subsequent step, so that linkage between worksheets with the same dimension is ensured.
7. Searching linkage configuration, obtaining a list of association tables, and adding the identified dimension suffix to each association table worksheet code in the configuration; And fifthly, executing the same-dimension linkage insertion, namely executing the insertion operation according to a basic linkage method or a high-level linkage method by using the worksheet codes with the added dimension.
8. The method for dynamically associating multi-table structure change based on template driving of claim 1, wherein the template content generating method uses "fragment template fragment" as a carrier, and after the structure is inserted, the placeholder in the template is replaced according to the insertion context, thereby generating new line content capable of being directly written into the worksheet, and the method is used for solving the problems of how to automatically generate the cell content of the newly added line and how to automatically adapt formula line number/cross-table reference after multi-table synchronous insertion, and specifically comprises the following steps: Presetting a fragment template and binding the fragment template to an insertable position, wherein in a workbook template, the fragment template is predefined for each type of insertable service line, the fragment template is composed of a plurality of lines, each line is composed of a plurality of cells, each cell at least comprises a cell value v, a style/category c and an expansion attribute a, and a fragment template identifier fragmentCode corresponding to an insertion action is declared at a position allowing insertion and is used for triggering an inserted positioning template at a user; The second step, obtaining the inserted context parameters, namely, after the user triggers the insertion, determining the context parameters of the initial line number, the dimension suffix of the current worksheet, the sub-table set/expandable sub-table list of the current worksheet and the column letter or column coordinate of the current unit for each target worksheet needing linkage; Step three, performing cell-by-cell replacement according to a placeholder rule, namely traversing each cell content in a segment template, replacing the placeholders appearing in the segment template, wherein the cell-by-cell replacement specifically comprises a ROW number placeholder $ { ROW } or $ { row+n }, replacing the ROW number placeholder with an actual inserted ROW number from 1 and offset thereof, a main item placeholder $ { base_row } or $ { base_row+n }, replacing the main item serial number/main table location value and offset thereof, a dimension placeholder $ { DEPT }, replacing the dimension placeholder with a current working table dimension suffix, a SUB-table aggregation placeholder $ { sub_ SHEETS }, replacing the column reference list meeting the conditions with comma connection, and replacing the column placeholder $ { COL } or $ { col+n }, replacing the column letter with a corresponding column letter, wherein if the aggregation placeholder is not matched to any, and the cell content belongs to a SUB-table aggregation formula which is "pure M aggregation and only contains the placeholder". The cell content is not applicable to be generated in a form of the aggregation formula; Generating a final segment and writing a new insertion area, namely copying and splicing segment templates after completing placeholder replacement according to the number of insertion lines to form a 'cell content array to be written', and then writing the values and the attributes of all cells in batches in the new insertion line area of the target worksheet; And fifthly, triggering formula dependency updating and recalculation, namely triggering a formula engine to perform dependency analysis and calculation on an affected area after structure insertion and cell writing are completed, so that cross-table references and line number references are kept correct on a final result.
9. The method for dynamically associating multiple table structure changes based on template driving according to claim 1, wherein said hierarchical linkage updating method is used for processing a parent-child table relation scene, and automatically updating mapping data maintained by a parent table when a child table is inserted or deleted, and specifically comprises the following steps: Firstly, configuring a parent-child table relationship, namely declaring a parent field in a high-level dynamic configuration, establishing the parent-child table relationship, and setting a linkage type of a parent attribute pointing to a parent table in the configuration of a child table to indicate that mapping data maintained by the parent table need to be updated when the child table is subjected to structure change; Secondly, the sub-table triggering structure is changed, namely a user inserts or deletes a plurality of rows changeRowCount at the appointed position changeRowIndex of the sub-table, the insertion is positive, the deletion is negative, and after receiving the change request, the change position and the number of rows are extracted; step three, traversing the mapping record of the father table and judging the change position, namely searching the parent configuration of the son table, positioning to the father table, acquiring all mapping records maintained by the father table, and judging each mapping record by calculating the end boundary endIndex of the detail block and judging the relation between the change position and the detail block; when the relation between the change position and the detail block is judged, if the change position index changeRowIndex is larger than or equal to the end boundary endIndex, the change is performed below the detail block, the mapping is not affected, the skip is not updated, if the change position index changeRowIndex is in the detail block range, namely, the startIndex is smaller than or equal to changeRowIndex < endIndex, the change is performed inside the detail block, the detail block line number is updated according to the rule that the detail block line number is the sum of the detail block line number and the change line number, if the change position index changeRowIndex < the detail block start line index startIndex is performed above the detail block, the change is performed according to the rule translation index that the detail block start line index is the sum of the detail block start line index and the change line number, and if the main item offset is maintained, the main item offset is the sum of the main item offset and the change line number; Fourthly, storing updated mapping data, namely storing the updated mapping record into the mapping data of the father table, wherein the updating of the mapping data ensures that the father table can accurately know which rows of the sub-table the detail block corresponding to each main item is currently in, and provides correct range information for subsequent formula quotation and data query; fifthly, storing the updated mapping relation table, namely storing the updated mapping record into the mapping relation table of the father table; The mapping data maintained by the parent table is used not only for automatic updating when the structure is changed, but also for formula referencing scenes.

Description

Dynamic joint multi-table structure changing method based on template driving Technical Field The invention relates to the technical field of enterprise financial management and data processing, in particular to a method for changing a dynamic joint multi-table structure based on template driving. Background In an enterprise financial budgeting scenario, a large number of interrelated budgeting tables typically need to be processed. Taking a typical enterprise budgeting scenario as an example, it may contain hundreds of thousands of interrelated budgeting tables. Complex data dependencies and formula references exist between these tables. When a user inserts a new data line (such as a new product line) in a certain table, the structure is required to be synchronously inserted in the corresponding positions of a plurality of related tables, and the correctness of formula reference is ensured. The prior art mainly has the following technical defects when processing multi-table structure synchronization: mode one, manual maintenance mode The user needs to manually insert lines table by table, the workload is large and the lines are easy to miss Cannot guarantee structural consistency among multiple tables Formula quotation is easy to make mistakes, and manual checking and correction are needed one by one Mode two simple script Automation Lines are inserted in batches through scripts, but flexible template methods are lacking Cannot handle complex placeholder substitutions (e.g., line numbers, worksheet references, etc.) It is difficult to uniformly manage a set of dimension-split worksheets (e.g., generating separate budget tables for each department, requiring uniform maintenance of the department tables in the configuration) Lacking a hierarchical linkage method, the cascade update of the parent-child can not be processed The root cause of the technical defects is the lack of a template-driven structure changing method which is designed for multi-table structure linkage scene and supports dynamic replacement of placeholders Disclosure of Invention The invention aims to provide a method for changing a dynamic joint multi-table structure based on template driving, which can solve the problems that the manual maintenance workload is large, the structural consistency among multiple tables cannot be ensured, the formula referencing is easy to make mistakes, manual checking and correction are needed one by one, a simple script automation lacks a flexible template method, complex placeholder replacement cannot be processed, a work table group split according to dimensions is difficult to uniformly manage, a hierarchical linkage method is lacking, and cascade update of a father and son table cannot be processed. According to one aspect of the invention, the method for changing the dynamic joint multi-table structure based on template driving specifically comprises the following steps: s1, determining a linkage type, namely automatically performing mode identification to determine the linkage type according to a worksheet and a position operated by a user; S2, applying a corresponding linkage method according to the determined linkage type; s3, generating inserted content, namely executing a template content generation method for a worksheet to be inserted, and generating final cell content according to the context replacement placeholders; S4, updating the mapping relation, namely if one-to-many linkage is involved, executing a hierarchical linkage updating method and maintaining consistency of the mapping relation of the father table; S5, formula calculation and front-end rendering, wherein the formula engine receives the formula content and formula quotation update notification of the newly inserted row, performs dependency analysis and calculation to generate a calculation result of the cell, and re-renders the affected worksheet region after the front-end table component receives the data update notification to display the newly inserted row and the calculation result thereof to a user; S6, operation completion feedback, namely prompting operation completion information to a user, displaying how many rows are inserted in how many worksheets, and checking newly inserted contents in each worksheet by the user to verify linkage effect and formula calculation result. Further, in the step S1, the linkage type comprises simple multi-table synchronization, one-to-many master-slave relation and worksheet families isolated according to dimensions. Further, in the step S2, if the simple multi-table synchronization is performed, a basic linkage method is performed, if the simple multi-table synchronization is performed in a one-to-many master-slave relationship, an advanced linkage method is performed, if the simple multi-table synchronization is performed in a working table group isolated by dimensions, a dimension expansion linkage method is performed first, and then the basic linkage method or the advanced linkage metho