Complex data dynamic export method based on templated configuration, computer program product and system

Abstract

The invention relates to the technical field of data visualization and report generation, and provides a complex data dynamic export method based on template configuration, a computer program product and a system.The method comprises the steps that report layout and database information are recognized by extracting metadata of historical Excel files, and an Excel template file and a corresponding JSON configuration file are generated according to the report layout and the database information; then JSON configuration is converted into a corresponding dynamic SQL statement according to selection operation of a user on the Excel template file so as to execute query, configuration only needs to be updated instead of recoding and publishing, and the development efficiency and the instant configuration capability are improved; in the data exporting process, the output sequence is determined by analyzing formula elements, and the final report data is output in batches by adopting a streaming data pipeline technology, so that the big data exporting success rate is improved. According to the complex data dynamic exporting method, the capability of flexibly and stably dynamically exporting the complex data into the Excel report can be provided for an enterprise.

Description

Technical Field

[0001] This invention relates to the field of data visualization and report generation technology, and in particular to a method, computer program product and data dynamic export system for complex data based on template-based configuration. Background Technology

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

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

[0004] Business BI tools offer powerful data analysis and visualization capabilities through their fixed template export function. However, the function of exporting analysis results to Excel reports is usually based on fixed templates. Users can only make limited adjustments within the preset template framework, which is difficult to meet the personalized needs of specific business scenarios.

[0005] Database management tools typically offer basic data export functionality, allowing query results to be exported in CSV or Excel format. However, they lack support for advanced Excel features such as charts, formulas, and conditional formatting, limiting their application in generating professional-grade reports.

[0006] In summary, faced with increasing business complexity and data volume, existing data export methods exhibit varying degrees of limitations in terms of flexibility, stability, and performance. Therefore, proposing a new solution to overcome these issues is particularly important. Summary of the Invention

[0007] The purpose of this invention is to provide a method for dynamically exporting complex data based on templated configuration, and a computer program product or system that implements the method when executed. Using this method, complex data can be dynamically exported into Excel reports in a flexible and stable manner.

[0008] This invention provides a method for dynamically exporting complex data based on templated configuration, comprising 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. 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. 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.

[0009] Furthermore, 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.

[0010] Furthermore, the column information includes quantity, position index, and title text; the row information includes start and end positions.

[0011] Furthermore, in the template definition step, generating an Excel template file based on the report layout refers to: an empty table file with styles generated based on the layout description using the ASPOSE API.

[0012] Furthermore, in the template definition step, generating the JSON configuration file of 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 the following information: field mapping, style rules, formula definitions, and data source configuration.

[0013] Furthermore, the pagination step performed in the multi-source data processing step includes: recording the current page index pageIndex and the total number of pages totalPages, and incrementing the current page index pageIndex after each query until the number of returned results is less than the preset pagination threshold pageSize.

[0014] Furthermore, the data sorting step specifically involves parsing the formula into an abstract syntax tree, identifying operator nodes and the dependencies between nodes, 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 the aggregate formula.

[0015] Furthermore, the operator node includes addition, subtraction, multiplication, division, or the SUM function.

[0016] The present invention also provides a computer program product, which stores a computer program that, when executed by a processor, can implement the steps of the above-described method for dynamically exporting complex data based on templated configuration.

[0017] The present invention also provides a data dynamic export system, including a processor and a terminal and a server respectively connected to the processor, and also includes the aforementioned computer program product, wherein the computer program on the computer program product can be executed by the processor.

[0018] This invention presents a method for dynamically exporting complex data based on templated configuration. It extracts metadata from historical Excel files to identify report layout and database information, generating Excel template files and corresponding JSON configuration files accordingly. Then, based on the user's selection of the Excel template file, the JSON configuration is converted into corresponding dynamic SQL statements for query execution. This requires only configuration updates rather than recoding and redeploying, improving development efficiency and real-time configuration capabilities. During the data export process, the output order is determined by parsing formula elements, and streaming data pipeline technology is used to output the final report data in batches, improving the success rate of exporting large amounts of data. This invention's method for dynamically exporting complex data provides enterprises with the flexible and stable ability to dynamically export complex data into Excel reports. Attached Figure Description

[0019] Figure 1 This is a flowchart illustrating the method for dynamically exporting complex data based on templated configuration, as presented in this invention. Detailed Implementation

[0020] The present invention will be further described in detail below with reference to specific embodiments.

[0021] The data dynamic export system of this embodiment includes a processor and a terminal and a server respectively connected to the processor. This embodiment uses computer code to program a computer program product, which, when executed by the processor of the data dynamic export system, implements the following... Figure 1 This illustrates a method for dynamically exporting complex data based on templated configuration. The following section details the execution process of this method using the export of financial data as an example.

[0022] The template definition process involves: retrieving historical Excel files; using a JSON Schema tool to extract template metadata from each Excel file; identifying the report layout and database information of the extracted template metadata; generating an Excel template file based on the report layout (i.e., a styled empty table file generated using the ASPOSE API based on the layout description); and generating a JSON configuration file for the Excel template file based on the database information (i.e., using an XML parsing tool to read the file content and extract various information, including field mappings, style rules, formula definitions, and data source configurations). Specifically, extracting template metadata from each Excel file includes the following steps: obtaining an XML file with report structure and data source definitions; using an XML parsing tool to read the file content; locating the `structure` tag in the XML file content to extract column information (such as quantity, position index, and title text) and row information (such as start and end positions) to generate structured report layout description data; and locating the `data_source` tag in the XML to identify the data source type, extract parameter information, and generate database information description data for the data source configuration.

[0023] Specifically, the server in this embodiment includes a template management module, which initiates the entire process by receiving an XML file containing the report structure and data source definition. It uses an XML parsing tool such as DOM4J to read the file content and decomposes the XML structure into layout definition and data source definition. It then locates the relevant elements within the XML. <structure>The tag extracts column information, including the number of columns, the position index of each column, and the title text. Simultaneously, it extracts row information, i.e., the start and end positions of data rows, thereby generating a structured layout description of the data. For the data source portion, it is necessary to identify the XML...<data_source> The tags determine whether the data source is a stored procedure or an inline query, and further extract parameter information, including parameter names, data types, and default values, to generate data source configuration description data. Based on the extracted information, the ASPOSE API is used to generate a styled empty table file as an Excel template file according to the layout description, while field mappings, style rules, formula definitions, and data source configurations are integrated into a JSON configuration file.

[0024] The template definition step in this embodiment also includes a version update step, employing a Git-like incremental storage mechanism to ensure template version management. When the first version is created, its complete JSON configuration file is saved. Once the user modifies the template, the system compares the old and new JSON configuration files to identify the changes and only records these changes to form lightweight patch files. Each patch file is stored according to the naming convention of [old version number]-[new version number].patch, and includes metadata such as patch size, author, and modification time. By sequentially applying all patch files starting from the base version, it is easy to roll back to any historical version.

[0025] Furthermore, this embodiment constructs a permission management system based on a Role-Based Access Control (RBAC) model, defining basic permission codes such as TEMPLATE_EDIT for editing templates, TEMPLATE_EXPORT for exporting reports, and VERSION_VIEW for viewing historical versions. Corresponding permission sets are assigned through predefined roles such as financial analyst or administrator. When a user requests to perform a specific operation, the server verifies whether the user's role possesses the necessary permissions to perform the operation, thus achieving secure control over data export.

[0026] The dynamic SQL generation process 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.

[0027] The server in this embodiment includes a query construction module that converts template metadata into dynamic SQL to ensure flexible response to changes in business requirements. Specifically, it reads the `fields` array from the JSON configuration file and extracts all `db_source` fields, such as `orders.order_id`; it parses the `filter_conditions` node to obtain business filtering conditions, such as `status='completed'`. The fields declared in the configuration file are configured into the SQL statement, and user input values ​​are converted into pre-compiled parameters to prevent SQL injection attacks.

[0028] The multi-source data processing steps involve: identifying the primary data source of the Excel template file, executing its independent query using dynamic SQL statements, and using the query results as column data; fully loading auxiliary table data (excluding the primary data source) from the Excel template file, and initiating parallel queries on the auxiliary table data; obtaining the association keys between the auxiliary table data and the primary data source using a HashMap key matching algorithm, appending them as association fields to the column data, and generating a merged Excel file as the currently selected Excel report format. Specifically, the primary data source (e.g., the orders table) is identified, its independent query is executed, and parallel queries are initiated on other data sources (e.g., the customers table); the auxiliary table data is fully loaded into memory, and a HashMap is constructed for fast indexing of association keys, such as the mapping from customer_id to customer_name; the main query result set is traversed, and related data is obtained through HashMap key matching and appended to the result rows. These processes improve query efficiency when processing large amounts of data.

[0029] In this embodiment, the pagination step performed in the multi-source data processing steps ensures the stability of large-volume data queries by automatically adding pagination parameters. The pagination step records 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 a preset pagination threshold (pageSize). Specifically, pageSize is set to 5000 as the default value; when the number of returned results is less than 5000, it indicates that the query is nearing completion. If an abnormal interruption occurs, the system persists the current pageIndex so that the query can continue from the breakpoint when execution resumes, thereby avoiding the resource waste caused by rerunning the entire query.

[0030] The data sorting process 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 formula is parsed into an abstract syntax tree, from which operator nodes (addition, subtraction, multiplication, division, or SUM function) and the dependencies between each node are identified. The execution order of the formula is determined by the topological sorting of the dependencies, prioritizing the calculation of referenced cells, then calculating the cells that depend on them, and finally calculating the aggregate formula.

[0031] The streaming pipeline uses database cursors to fetch data in batches of 5000 rows, consistent with query page sizes, ensuring data consistency and processing efficiency. The cursor maintains the database connection and fetches the next batch of data as needed, reducing unnecessary resource consumption. Next, a row data buffer is established, and data cleaning operations are performed, including null value replacement and data type conversion, and lightweight metadata such as row numbers and column index markers are added. Then, the sliding window mechanism of SXSSFWorkbook is used to initialize the window size, with a default setting of 100 rows. During the write process, 5000 rows of data are written to the memory window in batches. When the window is full, the earliest rows are automatically flushed to a temporary file on disk, and the memory space of the already written rows is released, managing memory in a first-in, first-out (FIFO) manner. The temporary files on disk are organized using the naming convention ` / tmp / {task_id}_{page_index}.bin`, and a corresponding file cleanup strategy is implemented based on the success or failure of the task.

[0032] Specifically, the formula parsing process identifies operator nodes and operand nodes (constants or cell references). For example, the formula =SUM(B2:B100)*0.9 is parsed into an Abstract Syntax Tree (AST), which contains the specific operational relationships. The dependency analysis phase marks direct dependencies and detects circular dependencies, constructing a dependency graph. Based on this graph, the execution order of formulas is determined through topological sorting: referenced cells are calculated first, then cells that depend on them, and finally aggregate formulas. To further improve efficiency, formulas of the same type are calculated together. For example, when calculating tiered commissions in a sales report, the base values ​​in all columns B are first calculated in batches, then the base commissions in column C are calculated in parallel based on these results, then the incentive bonuses in column D are calculated based on the results in column C, and finally the total commissions in column E are summarized.

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

[0034] This invention presents a method for dynamically exporting complex data based on templated configuration. It extracts metadata from historical Excel files to identify report layout and database information, generating Excel template files and corresponding JSON configuration files accordingly. Then, based on the user's selection of the Excel template file, the JSON configuration is converted into corresponding dynamic SQL statements for query execution. This requires only configuration updates rather than recoding and redeploying, improving development efficiency and real-time configuration capabilities. During the data export process, the output order is determined by parsing formula elements, and streaming data pipeline technology is used to output the final report data in batches, improving the success rate of exporting large amounts of data. This invention's method for dynamically exporting complex data provides enterprises with the flexible and stable ability to dynamically export complex data into Excel reports.

[0035] Finally, it should be noted that the method for dynamically exporting complex data based on templated configuration disclosed in the embodiments of the present invention is only a preferred embodiment of the present invention and is only used to illustrate the technical solutions of the present invention, not to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.< / 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. 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. 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, 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.

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

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 an Excel template file based on the report layout refers to generating a styled empty table file based on the layout description using the ASPOSE API.

5. 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 the following information: field mapping, style rules, formula definitions, and data source configuration.

6. 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).

7. The method for dynamically exporting complex data based on templated configuration as described in claim 1, characterized in that, Specifically, the data sorting step involves parsing the formula into an abstract syntax tree, from which operator nodes and the dependencies between nodes are identified; The execution order of formulas is determined by topological sorting based on dependencies: the referenced cells are calculated first, then the cells that depend on them, and finally the aggregate formulas are calculated.

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

9. 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 8.

10. 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 a computer program product as described in claim 9, wherein the computer program on the computer program product can be executed by the processor.

Images