Method, computer program product and system for dynamic derivation of complex data based on templated configuration

Abstract

The application relates to the technical field of data visualization and report generation, and provides a complex data dynamic export method based on templated configuration, a computer program product and a system.The method is as follows: metadata of a historical Excel file is extracted to identify report layout and database information, and an Excel template file and a corresponding JSON configuration file are generated according to the metadata; then, the JSON configuration is converted into corresponding dynamic SQL statements to execute query according to a selection operation of a user on the Excel template file, so that the development efficiency and instant configuration capability are improved by only updating the configuration instead of re-coding and publishing; in the data export process, an output sequence is determined by parsing formula elements, and a flow data pipeline technology is adopted to output final report data in batches, so that the big data export success rate is improved.The complex data dynamic export method can provide the capability of flexibly and stably exporting complex data into an Excel report for an enterprise.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data visualization and report generation, and particularly relates to a complex data dynamic export method based on templated configuration, a computer program product and a data dynamic export system. BACKGROUND

[0002] In current enterprise data analysis, it is often necessary to convert structured data in a database into an Excel report that meets business requirements. Existing technologies mostly use hard coding to achieve specific format export, or use basic data export tools that cannot meet the needs of complex business scenarios. Current report generation technologies mainly include using open source libraries such as Apache POI to directly call APIs, fixed template export functions provided by commercial BI tools, and basic data export functions of database management tools. However, these methods have deficiencies in terms of implementation complexity, flexibility, system stability, and computing performance.

[0003] Apache POI is a powerful Java library for manipulating Microsoft Office documents, including Excel. It allows developers to create, modify, and read Excel files programmatically. However, it requires developers to write a large amount of code to define cell styles, format data, and handle formulas, which increases the development workload and technical difficulty.

[0004] The fixed template export function of commercial BI tools provides powerful data analysis and visualization capabilities, but its function of exporting analysis results to Excel reports is usually a fixed template, and users can only make limited adjustments within the preset template framework, making it difficult to meet individual needs in specific business scenarios.

[0005] The basic data export function of database management tools can export query results in CSV or Excel format. However, it does not support advanced Excel features such as charts, formulas, conditional formatting, etc., limiting its application in generating professional-level reports.

[0006] In summary, in the face of increasing business complexity and data volume, existing data export methods in the prior art have limitations in flexibility, stability, and performance to varying degrees. Therefore, it is particularly important to propose a new solution to overcome these problems. SUMMARY

[0007] The present application aims to provide a complex data dynamic export method based on templated configuration and a computer program product and system that implement the method when executed, which can flexibly and stably export complex data to Excel reports.

[0008] The application provides a complex data dynamic derivation method based on templated configuration, comprising the following steps:

[0009] A template definition step, obtaining historical Excel files, calling a JSON Schema tool to extract template metadata of each Excel file, identifying report layout and database information of the extracted template metadata, generating an Excel template file according to the report layout, and generating a JSON configuration file of the Excel template file according to the database information;

[0010] A dynamic SQL generation step, obtaining a JSON configuration file associated with the selected operation of the Excel template file according to the user, and converting the JSON configuration file into an executable dynamic SQL statement;

[0011] A multi-source data processing step, identifying a main data source of the Excel template file, calling the dynamic SQL statement to execute an independent query, and taking the query result as column data; loading auxiliary table data other than the main data source in the Excel template file in full, initiating a query on the auxiliary table data in parallel; obtaining an association key of the auxiliary table data and the main data source through a HashMap key matching algorithm, and appending the association key as an association field to the column data to generate a fused Excel file as the currently selected Excel report format;

[0012] A data sorting step, parsing formula elements of Excel report data, identifying formula-related cells and their topological sorting, and confirming the output order of the formula-related cells according to the topological sorting;

[0013] A derivation step, constructing a streaming data pipeline, and outputting the current Excel report data to be derived in batches to the currently selected Excel report format file.

[0014] Further, in the template definition step, the template metadata of each Excel file comprises the following steps: obtaining an XML file with report structure and data source definition, reading the file content using an XML parsing tool; positioning the structure tag in the XML file content to extract column information and row information, and generating structured report layout description data; positioning the data_source tag in the XML, identifying the data source type, extracting the parameter information, and generating database information description data of the data source configuration.

[0015] Further, the column information comprises quantity, position index and title text; and the row information comprises start and end positions.

[0016] Further, in the template defining step, generating the Excel template file according to the report layout means generating a style table file based on the layout description according to the ASPOSE API.

[0017] Further, in the template defining step, generating the JSON configuration file of the Excel template file according to the database information means reading the file content using an XML parsing tool and extracting multiple information from the following information: field mapping, style rule, formula definition and data source configuration.

[0018] Further, the paging step included in the multi-source data processing step records the current page index pageIndex and the total number of pages totalPages, and increments the current page index pageIndex after each query until the number of returned results is less than the preset paging threshold pageSize.

[0019] Further, the data sorting step specifically parses the formula into an abstract syntax tree, identifies the operator nodes and the dependency relationship of each node from the abstract syntax tree, determines the execution order of the formula through topological sorting of the dependency relationship, preferentially calculates the cells referenced, then calculates the cells dependent on them, and finally calculates the aggregation formula.

[0020] Further, the operator nodes include addition, subtraction, multiplication, division or SUM function.

[0021] The application further provides a computer program product storing a computer program, which can realize the steps of the complex data dynamic export method based on templated configuration when executed by a processor.

[0022] The application further provides a data dynamic export system including a processor and a terminal and a server connected with the processor, and further including the computer program product, and the computer program on the computer program product can be executed by the processor.

[0023] The complex data dynamic export method based on templated configuration provided by the application can recognize the report layout and the database information by extracting the metadata of the historical Excel file, and generate the Excel template file and the corresponding JSON configuration file accordingly, then convert the JSON configuration into the corresponding dynamic SQL statement to perform the query according to the selection operation of the user on the Excel template file, so that the development efficiency and the instant configuration capability are improved by only updating the configuration instead of re-coding and publishing; the output order is determined by parsing the formula element during the data export process, and the flow data pipeline technology is adopted to output the final report data in batches, so that the success rate of the big data export is improved. The complex data dynamic export method can provide the enterprise with the capability of flexibly and stably exporting the complex data into the Excel report. BRIEF DESCRIPTION OF DRAWINGS

[0024] Figure 1 is a flowchart of the complex data dynamic derivation method based on templated configuration given by the present application. DETAILED DESCRIPTION

[0025] The present application will be further described in detail below in conjunction with the specific embodiments.

[0026] The data dynamic derivation system of the present embodiment comprises a processor and a terminal and a server connected with the processor respectively. The present embodiment adopts computer code for programming to obtain a computer program product, which, when executed by the processor of the data dynamic derivation system, implements the complex data dynamic derivation method based on templated configuration as shown in Figure 1 The execution process of the method will be described in detail below with the derivation of financial data as an example.

[0027] The template definition step acquires historical Excel files, calls the JSON Schema tool to extract the template metadata of each Excel file, identifies the report layout and database information of the extracted template metadata, generates an Excel template file according to the report layout, i.e., generates a styled empty table file based on the layout description according to the ASPOSE API; generates a JSON configuration file of the Excel template file according to the database information, i.e., uses the XML parsing tool to read the file content and extracts multiple information from the following information: field mapping, style rule, formula definition and data source configuration. The extraction of the template metadata of each Excel file comprises the following steps: acquiring an XML file with report structure and data source definition, using the XML parsing tool to read the file content; positioning the structure tag in the XML file content to extract column information (such as number, position index, title text) and row information (such as start and end position), generating structured report layout description data; positioning the data_source tag in the XML, identifying the data source type, extracting parameter information, and generating database information description data of the data source configuration.

[0028] Specifically, the server of the present embodiment comprises a template management module, which starts the whole process by receiving an XML file containing report structure and data source definition. The file content is read by using an XML parsing tool such as DOM4J, and the XML structure is decomposed into layout definition and data source definition. The data_source tag in the XML is positioned to identify the data source type, extract parameter information, and generate database information description data of the data source configuration. <structure>The label extracts column information, including the number of columns, the position index of each column, and the title text, and synchronously extracts row information, i.e., the start and end positions of the data row, to generate structured layout description data. For the data source part, the <data_source> tag in XML needs to be identified, the data source type needs to be determined to be a stored procedure or an inline query, and further parameter information, including the parameter name, data type, and default value, needs to be extracted, so as to generate data source configuration description data. Based on the above extracted information, an ASPOSE API is used to generate a styled empty table file as an Excel template file according to the layout description, and field mapping, style rules, formula definition, and data source configuration are integrated into a JSON configuration file.

[0029] The template definition step of the embodiment further includes a version update step, which uses a Git-like incremental storage mechanism to ensure version management of the template. When the first version is created, the JSON configuration file thereof is completely saved. Once the user modifies the template, the system compares the new and old JSON configuration files to identify the changed content, and only records the changes to form a lightweight patch file. Each patch file is stored according to the naming specification of [old version number]-[new version number].patch, and is attached with meta information such as patch size, author, modification time, etc. By sequentially applying all patch files from the base version, it is easy to roll back to any historical version.

[0030] Further, the embodiment constructs a permission management system based on a role-based access control (RBAC) model, and defines basic permission codes, such as TEMPLATE_EDIT for editing a template, TEMPLATE_EXPORT for exporting a report, and VERSION_VIEW for viewing a historical version. By predefining roles such as a financial analyst or an administrator, corresponding permission sets are assigned. When a user requests to perform a specific operation, the server verifies whether the role to which the user belongs has the permission required to perform the operation to achieve security control of data export.

[0031] The dynamic SQL generation step obtains the JSON configuration file associated with the selected Excel template file according to the user's selected operation on the Excel template file, and converts the JSON configuration file into an executable dynamic SQL statement.

[0032] The server of the embodiment includes a query construction module which converts template metadata into dynamic SQL to ensure flexible response to changes in business requirements. Specifically, the fields array in the JSON configuration file is read and all db_source fields such as orders.order_id are extracted therefrom; the filter_conditions node is parsed to obtain business filtering conditions such as status='completed'. Among them, the fields declared in the configuration file are configured to the SQL statement, and the user input value is converted into a precompiled parameter to prevent SQL injection attacks.

[0033] The multi-source data processing step identifies the main data source of the Excel template file, calls a dynamic SQL statement to execute an independent query thereof, and takes the query result as column data; the auxiliary table data other than the main data source in the Excel template file is loaded in full, and parallel queries are initiated for the auxiliary table data; the association key of the auxiliary table data and the main data source is obtained through a HashMap key matching algorithm, and is appended to the column data as an association field to generate a fused Excel file as the currently selected Excel report format. Specifically, the main data source (such as the order table orders) is identified, an independent query thereof is executed, and parallel queries are initiated for other data sources (such as the customer table customers); the data of the auxiliary table is loaded in full into the memory, a HashMap is constructed to quickly index the association key, for example, the mapping of customer_id to customer_name; the main query result set is traversed, the associated data is obtained through the HashMap key matching, and is appended to the result row. Through the above processing, the query efficiency when processing a large amount of data is improved.

[0034] The paging step performed in the multi-source data processing step of the embodiment guarantees the stability of large data queries by automatically adding a paging parameter. The paging step: records the current page index pageIndex and the total number of pages totalPages, and increments the current page index pageIndex after each query until the number of returned results is less than the preset paging threshold pageSize. Specifically, pageSize is set to 5000 as the default value, and when the number of returned results is less than 5000, it indicates that the query is approaching the end. If an abnormal interruption occurs, the system will persist the current pageIndex so that it can continue querying from the breakpoint when resuming execution, thereby avoiding resource waste caused by full re-running.

[0035] Data ordering step, parsing the formula elements of the Excel report data, identifying the formula related cells and their topological ordering, and confirming the output order of the formula related cells according to the topological ordering; specifically, the formula is parsed into an abstract syntax tree, from which the operator nodes (addition, subtraction, multiplication, division or SUM function) and the dependency relationship of each node are identified; the execution order of the formula is determined by the topological sorting of the dependency relationship, the cells referenced are calculated first, then the cells dependent on them are calculated, and finally the aggregate formula is calculated.

[0036] Wherein, the streaming processing pipeline uses a database cursor to obtain data in batches, and each batch is fixed at 5000 rows, which is consistent with the query page size, ensuring data consistency and processing efficiency. The cursor maintains the database connection state and obtains the next batch of data on demand, reducing unnecessary resource occupation. Next, a row data buffer is established to perform data cleaning operations, including null value replacement and data type conversion, and add lightweight metadata such as row number and column index markers. Subsequently, the sliding window mechanism of SXSSFWorkbook is used to initialize the window size, with a default setting of 100 rows. During the writing process, 5000 rows of data are written into the memory window in batches, and when the window is full, the earliest row is automatically flushed to the disk temporary file, and the memory space of the written row is released. Through this first-in-first-out (FIFO) way of managing memory. For temporary files on disk, a naming rule of / tmp / {task_id}_{page_index}.bin is used for organization, and corresponding file cleaning strategies are formulated according to the success or failure of the task.

[0037] Wherein, parsing the formula element specifically identifies the operator node and the operand node (constant or cell reference). Taking the formula =SUM(B2:B100)*0.9 as an example, it will be parsed into an abstract syntax tree (AST), which contains specific operation relationships. The dependency relationship analysis stage will mark the direct dependencies and detect circular dependencies, and build a dependency graph. Based on this graph, the execution order of the formula is determined by topological sorting, which calculates the referenced cells first, then the cells dependent on them, and finally the aggregate formula. In order to further improve efficiency, formulas of the same type will be calculated in batches. For example, when calculating the hierarchical commission in the sales report, first calculate the base value of all B columns in batches, then calculate the base commission of column C based on these results in parallel, then calculate the incentive addition of column D based on the results of column C, and finally summarize the total commission of column E.

[0038] Export step, building a streaming data pipeline, outputting the current Excel report data to be exported to the currently selected Excel report format file in batches.

[0039] The complex data dynamic derivation method based on templated configuration provided by the present application identifies the report layout and database information by extracting the metadata of the historical Excel file, and generates the Excel template file and the corresponding JSON configuration file accordingly, then converts the JSON configuration into the corresponding dynamic SQL statement to execute the query according to the selection operation of the user on the Excel template file, only needs to update the configuration instead of re-coding and publishing, improves the development efficiency and the instant configuration capability; in the data derivation process, the output sequence is determined by parsing the formula element, and the flow data pipeline technology is used to output the final report data in batches, which improves the success rate of large data derivation. The complex data dynamic derivation method can provide the enterprise with the ability to flexibly and stably derive the complex data into the Excel report.

[0040] Finally, it should be noted that: the complex data dynamic derivation method based on templated configuration disclosed by the embodiments of the present application is only the preferred embodiment of the present application, and is used to illustrate the technical solutions of the present application, but not to limit it; although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that; it can still modify the technical solutions recorded in the foregoing embodiments, or make equivalent replacement for part of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present application.< / structure>

Claims

1. A method for dynamically exporting complex data based on templated configuration, characterized by: Includes the following steps: The template definition steps are as follows: obtain historical Excel files, call the JSON Schema tool to extract template metadata of each Excel file, identify the report layout and database information of the extracted template metadata, generate an Excel template file according to the report layout, and generate a JSON configuration file for the Excel template file according to the database information. In the template definition step, extracting template metadata from each Excel file includes the following steps: obtaining an XML file with report structure and data source definition, and reading the file content using an XML parsing tool; locating the structure tag in the XML file content to extract column and row information, and generating structured report layout description data; locating the data_source tag in the XML, identifying the data source type, extracting parameter information, and generating database information description data for the data source configuration. The dynamic SQL generation step involves obtaining the associated JSON configuration file based on the user's selection of the Excel template file, and then converting the JSON configuration file into executable dynamic SQL statements. The multi-source data processing steps include: identifying the main data source of the Excel template file; calling the dynamic SQL statement to execute its independent query and using the query result as column data; fully loading the auxiliary table data other than the main data source in the Excel template file and initiating parallel queries on the auxiliary table data; obtaining the association key between the auxiliary table data and the main data source through the HashMap key matching algorithm, appending it as the association field to the column data, and generating a merged Excel file as the currently selected Excel report format. The data sorting step involves parsing the formula elements of the Excel report data, identifying the formula-related cells and their topological sorting, and determining the output order of the formula-related cells based on the topological sorting. Specifically, the data sorting step involves parsing the formula into an abstract syntax tree, identifying operator nodes and their dependencies, and determining the execution order of the formula through topological sorting of dependencies, prioritizing the calculation of referenced cells, then calculating cells that depend on them, and finally calculating aggregate formulas. The export step involves building a streaming data pipeline to output the data of the Excel report to be exported in batches to the currently selected Excel report format file.

2. The method for dynamically exporting complex data based on templated configuration as described in claim 1, characterized in that, The column information includes quantity, position index, and title text; the row information includes start and end positions.

3. The method for dynamically exporting complex data based on templated configuration as described in claim 1, characterized in that, In the template definition step, generating an Excel template file based on the report layout means generating a styled empty table file based on the layout description using the ASPOSE API.

4. The method for dynamically exporting complex data based on templated configuration as described in claim 1, characterized in that, In the template definition step, generating the JSON configuration file for the Excel template file based on the database information means using an XML parsing tool to read the file content and extract multiple pieces of information from the following: field mapping, style rules, formula definitions, and data source configuration.

5. The method for dynamically exporting complex data based on templated configuration as described in claim 1, characterized in that, The pagination step, which is performed in the multi-source data processing steps, involves recording the current page index (pageIndex) and the total number of pages (totalPages). After each query, the current page index (pageIndex) is incremented until the number of returned results is less than the preset pagination threshold (pageSize).

6. The method for dynamically exporting complex data based on templated configuration as described in claim 1, characterized in that, The operator nodes include addition, subtraction, multiplication, division, or the SUM function.

7. A computer program product storing a computer program, characterized in that, When the computer program is executed by the processor, it can implement the steps of the method for dynamically exporting complex data based on templated configuration as described in any one of claims 1 to 6.

8. A dynamic data export system, characterized in that, It includes a processor and a terminal and a server respectively connected to the processor, and also includes the computer program product as described in claim 7, wherein the computer program on the computer program product can be executed by the processor.

Images