An Excel report synchronization method, device and equipment and a storage medium

Abstract

The application discloses a kind of Excel report synchronization method, device, equipment and storage medium, comprising: obtaining business table, and constructs Excel template, to add the mapping relationship between Excel template and business table, and determine data synchronization direction;If data is synchronized from Excel template to business table, replace the element code in Excel template with the second business data to be synchronized, and the table field of second business data and business table is assembled to obtain first insert statement, so that the first business data is synchronized into business table;If data is synchronized from business table to Excel template, encode the first business data to be synchronized according to Excel template, and generate the node tree of corresponding business table, so that the first business data is converted into cell data, and then the converted cell data is assembled into second insert statement, to synchronize the second business data into the Excel template.

Description

Technical Field

[0001] This invention relates to the field of information data processing technology, and in particular to a method, apparatus, device, and storage medium for synchronizing Excel reports. Background Technology

[0002] Currently, business data often comes from different systems and platforms. Integrating this data into Excel may lead to problems such as inconsistent data formats, data duplication, and data redundancy.

[0003] While business data can currently be synchronized to Excel reports using format conversion software, Excel has limitations in handling large volumes of data. When the volume of business data is large, Excel may become slow to respond or even unable to process it. Furthermore, business data is dynamically changing, while Excel spreadsheets usually require manual updates, which can lead to data inconsistencies and timeouts. Additionally, it is impossible to synchronize data from Excel spreadsheets to business data, resulting in significant errors, low accuracy, and low reliability in data conversion and synchronization. This makes it difficult to dynamically merge business data and Excel reports, and makes managing and maintaining data permissions in Excel relatively complex, hindering the implementation of sophisticated access control and data security policies. Summary of the Invention

[0004] This invention provides a method, apparatus, device, and storage medium for synchronizing Excel reports, in order to solve the technical problems of large errors, low accuracy, and low reliability in the mutual conversion between business data and Excel reports in the prior art.

[0005] To address the aforementioned technical problems, embodiments of the present invention provide a method for synchronizing Excel reports, comprising:

[0006] Obtain the business table and construct an Excel template, thereby adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction; wherein, the business table records first business data, the Excel template stores instance data, and the data synchronization direction includes data synchronization from the Excel template to the business table and data synchronization from the business table to the Excel template;

[0007] If data is synchronized from an Excel template to a business table, the element code corresponding to the instance data in the Excel template is replaced with the second business data to be synchronized. According to the mapping relationship, the second business data is assembled into a first insert statement, and the first insert statement is executed to synchronize the second business data to the business table.

[0008] If data is synchronized from the business table to the Excel template, the first business data to be synchronized is cell-coded according to the Excel template, and a node tree corresponding to the business table is generated. Then, according to the node tree, the cell-coded first business data is converted into cell data, and the cell data is assembled into a second insert statement so that the second insert statement is executed to synchronize the first business data to the Excel template.

[0009] As a preferred embodiment, the step of obtaining the business table and constructing an Excel template, thereby adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction, specifically includes:

[0010] Obtain the business table containing the first business data, construct a main template to represent the business table, and construct a sub-template to associate the relationship between the business table and the element code. Import several instance data to construct an Excel template.

[0011] Based on the sub-templates in the Excel template and their association with the business table, a mapping relationship between the Excel template and the business table is obtained.

[0012] In response to user-updated data, determine whether the updated data is in an Excel template or a business table;

[0013] If the updated data is not in an Excel template, the data synchronization direction is from the Excel template to the business table;

[0014] If the updated data is not in the business table, the data synchronization direction is from the business table to the Excel template.

[0015] As a preferred embodiment, the step of replacing the element codes corresponding to the instance data in the Excel template with the second business data to be synchronized, and assembling the second business data into a first insert statement according to the mapping relationship, thereby executing the first insert statement to synchronize the second business data to the business table, specifically includes:

[0016] The element code of the cell corresponding to each instance data in the Excel template is determined, and each element code is replaced with the second business data to be synchronized; wherein, each cell corresponding to the instance data in the Excel template has a corresponding original element code;

[0017] Based on the mapping relationship and the Excel template, obtain the element definition information of the main template, the element definition information of the sub-template, the table name of the business table, and the table fields of the business table;

[0018] Iterate through all the replaced first business data to be synchronized, and assemble the first insert statement according to the element definition information of the main template, the element definition information of the sub-template, and the table name and table fields of the business table;

[0019] Execute the first insert statement to synchronize the second business data to the business table.

[0020] As a preferred embodiment, the step of encoding the cells of the first business data to be synchronized according to the Excel template and generating a node tree corresponding to the business table specifically includes:

[0021] Based on the Excel template, cell encoding is performed on first business data corresponding to fixed values ​​in the Excel template, and encoding is also performed on first business data corresponding to dynamic values ​​in the Excel template; wherein, the fixed values ​​include dictionary values, field values, and cell values ​​containing hyperlinks;

[0022] Based on the business table, the relationships between the first business data in the business table are obtained, thereby generating child nodes and sibling nodes between the first business data in the business table, and constructing a node tree of the business data in the business table based on the child nodes and sibling nodes.

[0023] As a preferred embodiment, after generating the node tree corresponding to the business table, the method further includes:

[0024] Based on the main template in the Excel template, obtain cell information, metadata mapping table data, cell value configuration table data, and instance association mapping table data;

[0025] Based on the instance association mapping table data, a table relationship object is obtained, and then the first business data after cell encoding is assembled into a table business data object through the table relationship object;

[0026] Assemble the in-memory data of the Excel template based on cell information, metadata mapping table data, cell value configuration table data, instance association mapping table data, and table business data objects.

[0027] As a preferred embodiment, the step of converting the first business data after cell encoding into cell data according to the node tree, and then assembling the cell data into a second insert statement, so that executing the second insert statement synchronizes the first business data to the Excel template, specifically includes:

[0028] The configuration of the node tree is traversed so that when processing each node, a single node is obtained and the cell code of the root node corresponding to that node is extracted. The table code is obtained from the metadata table mapping object according to the corresponding cell code. Then, according to the table code, the first business data corresponding to the table business data object of each node is extracted until the first business data of all nodes is obtained.

[0029] Assemble the first business data of all nodes into a second insert statement, and execute the second insert statement to synchronize the first business data to the Excel template.

[0030] As a preferred embodiment, assembling the first business data of all nodes into a second insert statement specifically includes:

[0031] Define a temporary variable for merged rows, obtain a fixed value from the fixed-value cell object, obtain the field containing the metadata from the metadata table mapped cell object, filter out the business data object based on the table relationship object, count the number of merged rows in the current cell, and thus construct the cell object of the current node;

[0032] If the first business data of the corresponding cell object is empty, recursively call the process for the child node;

[0033] If the first business data of the corresponding cell object is not empty, iterate through the first business data, convert it into cell data, and process the child nodes;

[0034] Assemble cell objects and set the assembled cell objects to the global cell data collection. Then, configure the current cell object and sibling cell objects according to the number of nodes, thereby assembling instance association mapping table data and setting it to the global instance mapping object collection.

[0035] Based on the global cell data set and the global instance mapping object set, generate the second insert statement corresponding to the first business data of all nodes.

[0036] As a preferred option, it also includes:

[0037] Based on the global cell data set and the global instance mapping object set, determine whether there are any cell merges;

[0038] If it exists, the table configuration object is updated, then the merged rows are updated, and finally the cell data table and the data instance mapping table are updated; wherein, the cell data table includes cell information, and the data instance mapping table includes instance association mapping table data.

[0039] Accordingly, the present invention also provides an Excel report synchronization device, comprising: a synchronization determination module, a first execution module, and a second execution module;

[0040] The synchronization determination module is used to obtain the business table and construct an Excel template, thereby adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction; wherein, the business table includes business data, and the data synchronization direction includes data synchronization from the Excel template to the business table and data synchronization from the business table to the Excel template;

[0041] The first execution module is configured to, if data is synchronized from an Excel template to a business table, replace the element codes in the Excel template with the first business data to be synchronized, and according to the mapping relationship, assemble the replaced first business data to be synchronized and the table fields of the business table to obtain a first insert statement, thereby executing the first insert statement to synchronize the second business data to the business table.

[0042] The second execution module is used to encode the second business data to be synchronized according to the Excel template if the data is synchronized from the business table to the Excel template, and generate a node tree corresponding to the business table. Then, according to the node tree, the encoded second business data is converted into cell data, and the converted cell data is assembled into a second insert statement so that the second insert statement is executed to synchronize the second business data to the Excel template.

[0043] Accordingly, the present invention also provides a terminal device, including a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor, wherein the processor executes the computer program to implement the Excel report synchronization method as described in any of the above.

[0044] Accordingly, the present invention also provides a computer-readable storage medium comprising a stored computer program, wherein, when the computer program is executed, it controls the device where the computer-readable storage medium is located to perform the Excel report synchronization method as described in any of the above.

[0045] Compared with the prior art, the embodiments of the present invention have the following beneficial effects:

[0046] The technical solution of this invention obtains a business table and constructs an Excel template, and adds a mapping relationship between the Excel template and the business table to determine the data synchronization direction. Based on different data synchronization directions, different corresponding insert statements are generated and executed to synchronize business data to the business table or the Excel template. This achieves mutual conversion between Excel and business data. Furthermore, by constructing a mapping relationship between the Excel template and the business table, data synchronization can be achieved quickly, whether from Excel to the business system or vice versa. This significantly improves the efficiency of data updates and migrations, ensures data consistency between the Excel template and the business table, and avoids information discrepancies that occur when data is transferred between different systems.

[0047] Furthermore, the first business data to be synchronized is encoded using an Excel template, and a node tree of the corresponding business table is generated. The encoded first business data is then converted into cell data. By assembling the cell data, the business data is synchronized to the Excel template for dynamic merging or splitting of cells. This improves the efficiency and accuracy of business data processing while reducing the complexity and error rate of operations. It is particularly beneficial for business scenarios that require frequent data synchronization and updates. Attached Figure Description

[0048] Figure 1 : A flowchart illustrating the steps of an Excel report synchronization method provided in an embodiment of the present invention;

[0049] Figure 2 : This is a schematic diagram illustrating the data synchronization between the Excel template and the business table provided in this embodiment of the invention;

[0050] Figure 3 This is a flowchart illustrating the main process of data synchronization between the Excel template and the business table provided in this embodiment of the invention.

[0051] Figure 4 : A structural diagram of an Excel report synchronization device provided in an embodiment of the present invention. Detailed Implementation

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

[0053] Example 1

[0054] Please refer to Figure 1 The present invention provides a method for synchronizing Excel reports, comprising steps S101-S103:

[0055] Step S101: Obtain the business table and construct an Excel template to add a mapping relationship between the Excel template and the business table, and determine the data synchronization direction; wherein, the business table records the first business data, the Excel template stores instance data, and the data synchronization direction includes data synchronization from the Excel template to the business table and data synchronization from the business table to the Excel template.

[0056] As a preferred embodiment, the step of obtaining the business table, constructing an Excel template, adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction specifically includes:

[0057] The system retrieves a business table containing first business data, constructs a main template to represent the business table, and constructs sub-templates to associate the relationship between the business table and element codes. It imports several instance data sets to construct an Excel template. Based on the sub-templates in the Excel template and their association with the business table, it adds a mapping relationship between the Excel template and the business table. In response to user-updated data, it determines whether the updated data is in the Excel template or the business table. If the updated data is not in the Excel template, the data synchronization direction is from the Excel template to the business table; otherwise, the data synchronization direction is from the business table to the Excel template.

[0058] In this embodiment, please refer to Figure 2 and 3 The primary function of an Excel template is to link an Excel spreadsheet with a system business table, specifying which field in the business table corresponds to which cell in the Excel spreadsheet. The Excel template includes a main template and sub-templates. The main template primarily displays the data from the business table. Element coding uses a standardized method defined by the system; preferably, C represents the column number and R represents the row number. After defining the element coding, the system matches it with the element coding in the sub-templates to obtain the mapping relationship of the business table. For example, C2R3 corresponds to the field in the second column and third row of the sub-template.

[0059] In this embodiment, the main function of the sub-template is to associate the relationship between business tables and element codes. When adding associated business tables, one business table corresponds to one sheet. The system will use the name of the business table as the sheet name of the template and the fields of the business table as the header of the sub-template. Preferably, the user can customize the main template element code corresponding to the field.

[0060] Step S102: If data is synchronized from the Excel template to the business table, the element code corresponding to the instance data in the Excel template is replaced with the second business data to be synchronized, and the second business data is assembled into a first insert statement according to the mapping relationship, thereby executing the first insert statement to synchronize the second business data to the business table.

[0061] As a preferred embodiment, the step of replacing the element codes corresponding to the instance data in the Excel template with the second business data to be synchronized, and assembling the second business data into a first insert statement according to the mapping relationship, thereby executing the first insert statement to synchronize the second business data to the business table, specifically includes:

[0062] The element code of the cell corresponding to each instance data in the Excel template is determined, and each element code is replaced with the second business data to be synchronized. Each cell corresponding to the instance data in the Excel template has a corresponding original element code. Based on the mapping relationship and the Excel template, the element definition information of the main template, the element definition information of the sub-template, the table name of the business table, and the table fields of the business table are obtained. All the replaced second business data to be synchronized are traversed, and a first insert statement is assembled based on the element definition information of the main template, the element definition information of the sub-template, and the table name and table fields of the business table. The first insert statement is executed to synchronize the second business data to the business table.

[0063] In this embodiment, please refer to Figure 3 The rows and columns of the Excel template remain basically unchanged. After designing the fixed template, replace the element codes in the template (standardized code definitions: C corresponds to column number, R corresponds to row number) with the required business data, and the business data can be synchronized to the business table according to the mapping relationship between Excel and the business table.

[0064] In this embodiment, importing data from an Excel template into a business table first requires obtaining basic information. This involves obtaining the main template element definition information (by connecting a general worksheet within the template element table) using the template number, obtaining the sub-template element definition information by concatenating the template number with a _tab string, and then querying the sub-template metadata mapping table using the template number concatenated with a _tab string to obtain the table name and fields of the business table. Furthermore, since the business table may have a parent-child table relationship, the mapping relationship data between the parent and child tables is obtained by concatenating the template number with a _tab string and using the condition that the sub-table field equals fid.

[0065] Further, the `insert_sql` statement is assembled. It iterates through the business data used as input parameters to obtain the data's value, row number, and column number. Then, it iterates through the main template element definition information, matching the input parameter's row and column. If a match is found, it iterates through the sub-template element definition information, matching the sub-template's element code with the main template's element code to obtain the row and column number of the cell data within the sub-template. Based on this column number and the sub-template's sheet name (the business table's name), the corresponding business table name and field can be obtained. Finally, the first insert statement (`insert`) is assembled based on the business data value, business table name, and business table field names. Finally, the assembled first insert statement is executed to synchronize the data to the business table.

[0066] Step S103: If data is synchronized from the business table to the Excel template, the first business data to be synchronized is cell-coded according to the Excel template, and a node tree corresponding to the business table is generated. Then, according to the node tree, the first business data after cell coding is converted into cell data, and the cell data is assembled into a second insert statement so that the second insert statement is executed to synchronize the first business data to the Excel template.

[0067] As a preferred embodiment, the step of encoding the cells of the first business data to be synchronized according to the Excel template and generating a node tree corresponding to the business table specifically includes:

[0068] Based on the Excel template, cell encoding is performed on the first business data corresponding to fixed values ​​in the Excel template, and encoding is also performed on the first business data corresponding to dynamic values ​​in the Excel template; wherein, the fixed values ​​include dictionary values, field values, and cell values ​​containing hyperlinks; based on the business table, the relationships between the first business data in the business table are obtained, thereby generating child nodes and sibling nodes between the first business data in the business table, and based on the child nodes and sibling nodes, a node tree of the business data in the business table is constructed.

[0069] In this embodiment, the business data in the business table is synchronized to the Excel template using a hybrid table template. A hybrid table template is a template where the rows and columns of the main template are variable and irregular. By using a hybrid table template, the situation of incompatibility in format can be avoided when the business data in the business table is synchronized to the Excel template.

[0070] In this embodiment, the business data in the business table has child nodes and sibling nodes. Child nodes are one-to-one or one-to-many descriptions between the field columns of the control; sibling nodes are nodes that change according to the main node, and have the same row height. For example, regarding the business data of the "Enterprise New Business Chain List", its first node is the title of the "Enterprise New Business Chain Column". Its child nodes are "Demand Side", "Supply Side", and "Capability Side". The three nodes "Demand Side", "Supply Side", and "Capability Side" are all child nodes of the "Enterprise New Business Chain Column", and "Demand Side", "Supply Side", and "Capability Side" are their respective child nodes. Each "Demand Side", "Supply Side", and "Capability Side" has its corresponding child nodes.

[0071] In this embodiment, fixed values ​​are static values ​​that cannot be modified in the Excel control. Preferably, they can be divided into three categories: values ​​that exist in Excel and are dictionary values ​​in the business table; values ​​that exist in Excel but do not exist in the business table; header values ​​used to describe field values ​​in the business table; and cell values ​​containing hyperlinks. Furthermore, by pre-defining fixed values ​​in the cell encoding configuration table, the encoding value corresponding to the first business data of the fixed values ​​in the Excel template, as well as the encoding value corresponding to the first business data of the dynamic values ​​in the Excel template, can be determined during the business data synchronization process. It is understood that dynamic values ​​can be descriptive variable values ​​corresponding to the fixed values.

[0072] As a preferred embodiment, after generating the node tree corresponding to the business table, the method further includes:

[0073] Based on the main template in the Excel template, obtain cell information, metadata mapping table data, cell value configuration table data, and instance association mapping table data; based on the instance association mapping table data, obtain a table relationship object, and then assemble the first business data after cell encoding into a table business data object through the table relationship object; based on the cell information, metadata mapping table data, cell value configuration table data, instance association mapping table data, and table business data object, assemble the memory data of the Excel template.

[0074] As a preferred embodiment, the step of converting the first business data after cell encoding into cell data according to the node tree, and then assembling the cell data into a second insert statement, so that executing the second insert statement synchronizes the first business data to the Excel template, specifically includes:

[0075] The configuration of the node tree is traversed so that when processing each node, a single node is obtained and the cell code of the root node corresponding to that node is extracted. The table code is obtained from the metadata table mapping object according to the corresponding cell code. Then, according to the table code, the first business data corresponding to the table business data object of each node is extracted until the first business data of all nodes is obtained. The first business data of all nodes is assembled into a second insert statement and executed to synchronize the first business data to the Excel template.

[0076] In this embodiment, before converting business table data into cell data, a new workbook needs to be copied. Specifically, a new sheet is copied based on the Excel template gridkey and sheet. Then, a new data entry is added to the worksheet (general worksheet), and the corresponding business data synchronized from the business table is populated into this new sheet. Alternatively, a new celldata set can be copied based on the template gridkey and sheet, and then cell data can be added in batches to the workcelldata table.

[0077] In this embodiment, it is necessary to assemble in-memory data in the Excel template to be synchronized, including: main template cell objects, metadata table mapping objects, fixed-value cell objects, table relationship cell objects, and table business data objects. For the main template cell objects, the cell information of the main template can be queried based on the main template and the main template sheet name, and assembled into a Map<cell code, main template cell data collection>. For the metadata table mapping objects, the metadata mapping table data can be queried based on the unique key of the main template concatenated with _tab, and assembled into a Map<cell code, metadata table mapping relationship collection>. For the fixed-value cell objects, the cell value configuration table data can be queried based on the unique key of the main template concatenated with _tab, and assembled into a Map<cell code, fixed-value collection>. For the table relationship cell objects, the instance association mapping table data can be queried based on the unique key of the main template concatenated with _tab, and assembled into a Map<cell code, table relationship collection>. For the table business data objects, the business data can be obtained based on the table relationship objects and assembled into a Map<table code, business data collection>.

[0078] As a preferred embodiment, the step of assembling the first business data of all nodes into a second insert statement specifically includes:

[0079] Define a temporary variable for merged rows and obtain a fixed value from the fixed-value cell object. Obtain the field containing the metadata from the metadata table mapping cell object. Filter out the business data object based on the table relationship object. Count the number of merged rows in the current cell to construct the cell object for the current node. If the first business data corresponding to this cell object is empty, recursively call the processing of child nodes. If the first business data corresponding to this cell object is not empty, iterate through the first business data, convert it into cell data, and process the child nodes. Assemble the cell objects and set the assembled cell objects to the global cell data collection. Then, configure the current cell object and sibling cell objects according to the number of nodes to assemble the instance association mapping table data and set it to the global instance mapping object collection. Based on the global cell data collection and the global instance mapping object collection, generate the second insert statement corresponding to the first business data of all nodes.

[0080] As a preferred embodiment, it also includes:

[0081] Based on the global cell data set and the global instance mapping object set, determine whether there is a cell merging situation; if so, update the table configuration object, then update the merged rows, and finally update the cell data table and the data instance mapping table; wherein, the cell data table includes cell information, and the data instance mapping table includes instance association mapping table data.

[0082] In this embodiment, the encoded first business data is converted into cell data that can be assembled. This can be done by first traversing the node tree configuration to obtain a single tree node, extracting the cell code (cell_code) of the root node, and then obtaining the table code from the metadata table mapping object based on the cell_code. Based on the table code, all business data can be obtained from the table business data object.

[0083] Furthermore, for other nodes, a recursive method needs to be called to process each tree node. By defining currentNodeMergeRow as a temporary variable for merging rows, a fixed value is obtained from the fixed-value cell object, the field containing the metadata is obtained from the metadata table mapping cell object, the business data objects that meet the conditions are filtered out according to the table relationship, and the number of merged rows in the current cell is counted, and then the CellData cell object of the current node is constructed. If the business data is empty, the child nodes are processed recursively; if the business data is not empty, the business data is traversed and the child nodes are processed.

[0084] Then, the celldata object is assembled and set into the global cell data collection celldataList. Based on the number of nodes, the current cell data object and its sibling cell objects are configured, thereby assembling the instance mapping object information and setting it into the global instance mapping object collection instanceList.

[0085] In this embodiment, the converted cell data is finally assembled into a second insert statement, and the assembled second insert statement is executed to synchronize the data to the business table.

[0086] Furthermore, updating data in the cell data table (workcelldata) and the data instance mapping table (tb_cellinstance_mapping) can be achieved by combining celldataList and instanceList. If merged cells exist, sheetconfig needs to be updated to update the merged rows, ultimately updating workcelldata and tb_cellinstance_mapping. This allows for dynamic merging and splitting of cells when synchronizing business data to Excel, improving the accuracy and adaptability of business data synchronization.

[0087] In this embodiment, the system can also dynamically convert data between the Excel template and the business table in real time by responding to changes in the Excel template made by the user. This allows for dynamic merging and splitting of cells when business data is synchronized to Excel. Please refer to [link to relevant documentation]. Figure 2 and 3 By editing the data in the Excel template or loading the data in the business table, you can update the data in the business table while editing the data in the Excel template.

[0088] Implementing the above embodiments has the following effects:

[0089] The technical solution of this invention obtains a business table and constructs an Excel template, and adds a mapping relationship between the Excel template and the business table to determine the data synchronization direction. Based on different data synchronization directions, different corresponding insert statements are generated and executed to synchronize business data to the business table or the Excel template. This achieves mutual conversion between Excel and business data. Furthermore, by constructing a mapping relationship between the Excel template and the business table, data synchronization can be achieved quickly, whether from Excel to the business system or vice versa. This significantly improves the efficiency of data updates and migrations, ensures data consistency between the Excel template and the business table, and avoids information discrepancies that occur when data is transferred between different systems.

[0090] Furthermore, the first business data to be synchronized is encoded using an Excel template, and a node tree of the corresponding business table is generated. The encoded first business data is then converted into cell data. By assembling the cell data, the business data is synchronized to the Excel template for dynamic merging or splitting of cells. This improves the efficiency and accuracy of business data processing while reducing the complexity and error rate of operations. It is particularly beneficial for business scenarios that require frequent data synchronization and updates.

[0091] Example 2

[0092] Please refer to the figure, which shows the synchronization device for Excel reports provided by the present invention, including: a synchronization determination module 201, a first execution module 202, and a second execution module 203;

[0093] The synchronization determination module 201 is used to obtain a business table and construct an Excel template, thereby adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction; wherein, the business table includes business data, and the data synchronization direction includes data synchronization from the Excel template to the business table and data synchronization from the business table to the Excel template;

[0094] The first execution module 202 is used to replace the element codes in the Excel template with the first business data to be synchronized if data is synchronized from the Excel template to the business table, and to assemble the replaced first business data to be synchronized and the table fields of the business table according to the mapping relationship to obtain a first insert statement, thereby executing the first insert statement to synchronize the second business data to the business table.

[0095] The second execution module 203 is used to encode the second business data to be synchronized according to the Excel template if the data is synchronized from the business table to the Excel template, and generate a node tree corresponding to the business table. Then, according to the node tree, the encoded second business data is converted into cell data, and the converted cell data is assembled into a second insert statement so that the second insert statement is executed to synchronize the second business data to the Excel template.

[0096] As a preferred embodiment, the step of obtaining the business table and constructing an Excel template, thereby adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction, specifically includes:

[0097] Obtain the business table; wherein the business table contains business data;

[0098] A main template for displaying the business table is constructed, and a sub-template for associating the relationship between the business table and the element code is constructed, thereby constructing an Excel template;

[0099] Based on the sub-templates in the Excel template and their association with the business table, a mapping relationship between the Excel template and the business table is obtained.

[0100] In response to user-updated data, determine whether the data is in an Excel template or a business table, and thus determine the direction in which the updated data should be synchronized.

[0101] As a preferred embodiment, the step of replacing the element codes in the Excel template with the first business data to be synchronized, and assembling the replaced first business data to be synchronized and the table fields of the business table according to the mapping relationship to obtain a first insert statement, thereby executing the first insert statement to synchronize the second business data to the business table, specifically includes:

[0102] Replace the element codes in the Excel template with the first business data to be synchronized; wherein, the first business data in each cell of the Excel template has a corresponding original element code;

[0103] Based on the mapping relationship and the Excel template, obtain the element definition information of the main template, the element definition information of the sub-template, and the table name and table fields of the business table;

[0104] Iterate through all the replaced first business data to be synchronized, and assemble the first insert statement according to the element definition information of the main template, the element definition information of the sub-template, and the table name and table fields of the business table;

[0105] Execute the first insert statement to synchronize the second business data to the business table.

[0106] As a preferred embodiment, the step of encoding the second business data to be synchronized according to the Excel template and generating a node tree corresponding to the business table specifically includes:

[0107] Based on the Excel template, second business data corresponding to fixed values ​​in the Excel template and second business data corresponding to dynamic values ​​in the Excel template are encoded; wherein, the fixed values ​​include dictionary values, field values ​​and cell values ​​containing hyperlinks;

[0108] Based on the business table, child nodes and sibling nodes between business data in the business table are generated, thereby constructing a node tree of business data in the business table based on the child nodes and sibling nodes.

[0109] As a preferred embodiment, the step of converting the encoded second business data into cell data according to the node tree, and then assembling the converted cell data into a second insert statement, so that executing the second insert statement synchronizes the second business data to the Excel template, specifically includes:

[0110] Based on the main template in the Excel template, obtain cell information, metadata mapping table data, cell value configuration table data, and instance association mapping table data, and combine them with the table relationship object to assemble the memory data of the Excel template;

[0111] The configuration of the node tree is traversed so that when processing each tree node, a single tree node is obtained, the cell code of the root node is extracted, and the table code is obtained from the metadata table mapping object according to the cell code. Then, according to the table code, the encoded second business data is converted into cell data until each tree node is recursively processed, thereby assembling the converted cell data into the second insert statement.

[0112] Execute the second insert statement to synchronize the second business data to the Excel template.

[0113] As a preferred embodiment, the step of converting the encoded second business data into cell data specifically includes:

[0114] Define a temporary variable for merged rows, obtain a fixed value from the fixed-value cell object, obtain the field containing the metadata from the metadata table mapped cell object, filter out the business data object based on the table relationship object, count the number of merged rows in the current cell, and thus construct the cell object of the current node;

[0115] If the second business data of the corresponding cell object is empty, recursively call the process for the child node;

[0116] If the second business data corresponding to the cell object is not empty, iterate through the first business data, convert it into cell data, and process the child nodes.

[0117] As a preferred option, it also includes:

[0118] In response to user modifications to data in an Excel template, the corresponding business data in the business table containing the modified data is updated synchronously.

[0119] Those skilled in the art will understand that, for the sake of convenience and brevity, the specific working process of the device described above can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0120] Implementing the above embodiments has the following effects:

[0121] The technical solution of this invention obtains a business table and constructs an Excel template, and adds a mapping relationship between the Excel template and the business table to determine the data synchronization direction. Based on different data synchronization directions, different corresponding insert statements are generated and executed to synchronize business data to the business table or the Excel template. This achieves mutual conversion between Excel and business data. Furthermore, by constructing a mapping relationship between the Excel template and the business table, data synchronization can be achieved quickly, whether from Excel to the business system or vice versa. This significantly improves the efficiency of data updates and migrations, ensures data consistency between the Excel template and the business table, and avoids information discrepancies that occur when data is transferred between different systems.

[0122] Furthermore, the first business data to be synchronized is encoded using an Excel template, and a node tree of the corresponding business table is generated. The encoded first business data is then converted into cell data. By assembling the cell data, the business data is synchronized to the Excel template for dynamic merging or splitting of cells. This improves the efficiency and accuracy of business data processing while reducing the complexity and error rate of operations. It is particularly beneficial for business scenarios that require frequent data synchronization and updates.

[0123] Example 3

[0124] Accordingly, the present invention also provides a terminal device, comprising: a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor, wherein the processor executes the computer program to implement the Excel report synchronization method as described in any of the above embodiments.

[0125] The terminal device in this embodiment includes a processor, a memory, and a computer program and computer instructions stored in the memory and executable on the processor. When the processor executes the computer program, it implements the various steps described in Embodiment 1 above, for example... Figure 1 The steps S101 to S103 are shown. Alternatively, when the processor executes the computer program, it implements the functions of each module / unit in the above-described device embodiment, such as determining the synchronization module 201.

[0126] For example, the computer program can be divided into one or more modules / units, which are stored in the memory and executed by the processor to complete the present invention. The one or more modules / units can be a series of computer program instruction segments capable of performing specific functions, which describe the execution process of the computer program in the terminal device. For example, the synchronization determination module 201 is used to obtain a business table, construct an Excel template, thereby adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction.

[0127] The terminal device may be a desktop computer, laptop, handheld computer, or cloud server, etc. The terminal device may include, but is not limited to, a processor and memory. Those skilled in the art will understand that the schematic diagram is merely an example of a terminal device and does not constitute a limitation on the terminal device. It may include more or fewer components than illustrated, or combine certain components, or different components. For example, the terminal device may also include input / output devices, network access devices, buses, etc.

[0128] The processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor can be a microprocessor or any conventional processor. The processor is the control center of the terminal device, connecting all parts of the terminal device via various interfaces and lines.

[0129] The memory can be used to store the computer programs and / or modules. The processor implements various functions of the terminal device by running or executing the computer programs and / or modules stored in the memory and by calling data stored in the memory. The memory may mainly include a program storage area and a data storage area. The program storage area may store the operating system, at least one application program required for a function, etc.; the data storage area may store data created based on the use of the mobile terminal, etc. In addition, the memory may include high-speed random access memory, and may also include non-volatile memory, such as hard disk, RAM, plug-in hard disk, smart media card (SMC), secure digital card (SD), flash card, at least one disk storage device, flash memory device, or other volatile solid-state storage device.

[0130] Wherein, if the modules / units integrated in the terminal device are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments of the present invention can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when the computer program is executed by a processor, it can implement the steps of the various method embodiments described above. Wherein, the computer program includes computer program code, which can be in the form of source code, object code, executable file, or some intermediate form, etc. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording medium, USB flash drive, portable hard drive, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content contained in the computer-readable medium can be appropriately added or removed according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, the computer-readable medium does not include electrical carrier signals and telecommunication signals.

[0131] Example 4

[0132] Accordingly, the present invention also provides a computer-readable storage medium comprising a stored computer program, wherein, when the computer program is executed, it controls the device where the computer-readable storage medium is located to perform the Excel report synchronization method as described in any of the above embodiments.

[0133] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above descriptions are merely specific embodiments of the present invention and are not intended to limit the scope of protection of the present invention. In particular, it should be noted that any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention for those skilled in the art.

Claims

1. A method for synchronizing Excel reports, characterized in that, include: Obtain the business table and construct an Excel template, thereby adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction; wherein, the business table records first business data, the Excel template stores instance data, and the data synchronization direction includes data synchronization from the Excel template to the business table and data synchronization from the business table to the Excel template; If data is synchronized from an Excel template to a business table, the element code corresponding to the instance data in the Excel template is replaced with the second business data, and the second business data is assembled into a first insert statement according to the mapping relationship, thereby executing the first insert statement to synchronize the second business data to the business table; If data is synchronized from the business table to the Excel template, the first business data to be synchronized is cell-coded according to the Excel template, and a node tree corresponding to the business table is generated. Then, according to the node tree, the cell-coded first business data is converted into cell data, and the cell data is assembled into a second insert statement so that the second insert statement is executed to synchronize the first business data to the Excel template.

2. The method for synchronizing Excel reports of claim 1, wherein, The process of obtaining the business table, constructing an Excel template, adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction specifically includes: Obtain the business table containing the first business data, construct a main template to represent the business table, and construct a sub-template to associate the relationship between the business table and the element code. Import several instance data to construct an Excel template. Based on the sub-templates in the Excel template and their association with the business table, the mapping relationship between the Excel template and the business table is obtained; In response to user-updated data, determine whether the updated data is in an Excel template or a business table; If the updated data is in an Excel template, the data synchronization direction is from the Excel template to the business table; If the updated data is in a business table, the data synchronization direction is from the business table to the Excel template.

3. The method of claim 2, wherein the Excel report is synchronized by: The step of replacing the element codes corresponding to the instance data in the Excel template with the second business data to be synchronized, and assembling the second business data into a first insert statement according to the mapping relationship, thereby executing the first insert statement to synchronize the second business data to the business table, specifically includes: The element code of the cell corresponding to each instance data in the Excel template is determined, and each element code is replaced with the second business data to be synchronized; wherein, each cell corresponding to the instance data in the Excel template has a corresponding original element code; Based on the mapping relationship and the Excel template, obtain the element definition information of the main template, the element definition information of the sub-template, the table name of the business table, and the table fields of the business table; Iterate through all the replaced second business data to be synchronized, and assemble the first insert statement according to the element definition information of the main template, the element definition information of the sub-template, and the table name and table fields of the business table; Execute the first insert statement to synchronize the second business data to the business table.

4. The method of claim 3, wherein the Excel report is synchronized by: The step of encoding the cells of the first business data to be synchronized according to the Excel template and generating the node tree corresponding to the business table specifically includes: Based on the Excel template, cell encoding is performed on the first business data corresponding to fixed values ​​in the Excel template, and cell encoding is also performed on the first business data corresponding to dynamic values ​​in the Excel template; wherein, the fixed values ​​include dictionary values, field values, and cell values ​​containing hyperlinks; Based on the business table, the relationships between the first business data in the business table are obtained, thereby generating child nodes and sibling nodes between the first business data in the business table, and constructing a node tree of the business data in the business table based on the child nodes and sibling nodes.

5. The method for synchronizing Excel reports of claim 4, wherein, After generating the node tree corresponding to the business table, the method further includes: Based on the main template in the Excel template, obtain cell information, metadata mapping table data, cell value configuration table data, and instance association mapping table data; Based on the instance association mapping table data, a table relationship object is obtained, and then the first business data after cell encoding is assembled into a table business data object through the table relationship object; Assemble the in-memory data of the Excel template based on cell information, metadata mapping table data, cell value configuration table data, instance association mapping table data, and table business data objects.

6. The method for synchronizing Excel reports of claim 5, wherein, The step of converting the first business data after cell encoding into cell data according to the node tree, and then assembling the cell data into a second insert statement, so that executing the second insert statement synchronizes the first business data to the Excel template, specifically includes: The configuration of the node tree is traversed so that when processing each node, a single node is obtained and the cell code of the root node corresponding to that node is extracted. The table code is obtained from the metadata table mapping object according to the corresponding cell code. Then, according to the table code, the first business data corresponding to the table business data object of each node is extracted until the first business data of all nodes is obtained. Assemble the first business data of all nodes into a second insert statement, and execute the second insert statement to synchronize the first business data to the Excel template.

7. The method for synchronizing Excel reports of claim 6, wherein, The process of assembling the first business data of all nodes into a second insert statement specifically includes: Define a temporary variable for merged rows, obtain a fixed value from the fixed-value cell object, obtain the field containing the metadata from the metadata table mapped cell object, filter out the business data object based on the table relationship object, count the number of merged rows in the current cell, and thus construct the cell object of the current node; If the first business data of the corresponding cell object is empty, recursively call the process for the child node; If the first business data of the corresponding cell object is not empty, iterate through the first business data, convert it into cell data, and process the child nodes; Assemble cell objects and set the assembled cell objects to the global cell data collection. Then, configure the current cell object and sibling cell objects according to the number of nodes, thereby assembling instance association mapping table data and setting it to the global instance mapping object collection. Based on the global cell data set and the global instance mapping object set, generate the second insert statement corresponding to the first business data of all nodes.

8. The method of claim 7, wherein the Excel report is synchronized by: Also includes: Based on the global cell data set and the global instance mapping object set, determine whether there are any cell merges; If it exists, the table configuration object is updated, then the merged rows are updated, and finally the cell data table and the data instance mapping table are updated; wherein, the cell data table includes cell information, and the data instance mapping table includes instance association mapping table data.

9. An apparatus for synchronizing Excel reports, the apparatus comprising: include: Identify the synchronization module, the first execution module, and the second execution module; The synchronization determination module is used to obtain the business table and construct an Excel template, thereby adding a mapping relationship between the Excel template and the business table, and determining the data synchronization direction; wherein, the business table records first business data, the Excel template stores instance data, and the data synchronization direction includes data synchronization from the Excel template to the business table and data synchronization from the business table to the Excel template; The first execution module is configured to, if data is synchronized from an Excel template to a business table, replace the element code corresponding to the instance data in the Excel template with the second business data, and assemble the second business data into a first insert statement according to the mapping relationship, thereby executing the first insert statement to synchronize the second business data to the business table; The second execution module is used to encode the first business data to be synchronized according to the Excel template if the data is synchronized from the business table to the Excel template, and generate a node tree corresponding to the business table. Then, according to the node tree, the first business data after cell encoding is converted into cell data, and the cell data is assembled into a second insert statement so that the second insert statement is executed to synchronize the first business data to the Excel template.

10. A terminal device, comprising: The system includes a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor, wherein the processor, when executing the computer program, implements the method for synchronizing Excel reports as described in any one of claims 1 to 8.

Images