Data dynamic exporting method and system based on templated configuration

Abstract

The invention provides a data dynamic exporting method and system based on template configuration. The method comprises the steps that a data exporting request for exporting data in a data source in a report form is obtained; verifying the user permission data in the request to generate a verification result; when the verification result is that the verification is passed, generating a dynamic SQL statement according to template metadata corresponding to the template classification data; executing paging query according to the dynamic SQL statement, and performing data processing and streaming writing on data acquired from a data source to which data to be exported belongs in batches; and when the paging query is finished and data processing and streaming writing of the data obtained in the last batch are completed, generating a temporary file, and returning a downloading link to the client for triggering downloading of the data in the temporary file and generating a report corresponding to the data export request. According to the method and the system, the template adjustment efficiency is improved, the memory consumption is reduced, and the big data export success rate is greatly improved.

Description

Technical Field

[0001] This invention relates to the field of data visualization and report generation technology, and more specifically, to a method and system for dynamic data export based on templated configuration. Background Technology

[0002] In current enterprise data analysis, it is often necessary to convert structured data from databases of different types and sources into Excel reports that meet business needs. Existing technologies mostly use hard-coding to achieve specific format export, or use basic data export tools, such as the raw API call scheme of open source libraries like Apache POI, the fixed template export function of commercial BI tools, and the basic data export function of database management tools. However, all of these methods have certain shortcomings and cannot meet the needs of complex business scenarios. Specifically:

[0003] The technical implementation is complex, and format adjustments rely on hard-coding by developers. For example, the original API calls of open-source libraries like Apache POI provide basic APIs for manipulating Excel files. However, when using these original APIs to export Excel reports in a specific format, developers need to write formatting logic for each cell line by line in the code, such as font, color, border, column width, row height, and merging cells. Different business reports often have different formatting requirements, and each format adjustment requires modification of the hard-coded parts of the code. For example, if the original report title font was SimSun 12pt bold, and now it needs to be changed to Heiti 14pt bold, developers must find and modify the statement that sets the title font in the code, then recompile and deploy the code. This hard-coding approach tightly couples the code with the report format, increasing the workload and difficulty of development, and also reducing code maintainability, because the format adjustment code is scattered in various parts, making it difficult to manage and modify uniformly.

[0004] The lack of flexibility prevents them from dynamically adapting to changing business template needs. For example, the fixed template export function of commercial BI tools typically provides some preset templates for data export, but these templates are often fixed, and users can only make some simple parameter adjustments within a limited range, such as selecting which fields to display and setting some basic styles. For some complex and personalized business template needs, such as dynamically changing the report layout according to different business scenarios and adding custom logical calculations, the fixed template function of commercial BI tools cannot meet them and lacks sufficient flexibility. Another example is the basic data export function of database management tools, which mainly focuses on database management and basic data operations. Its basic data export function can usually only export data from the database in simple tabular form, such as CSV, TXT, etc., or export some common report formats with fixed templates. It cannot dynamically generate report templates with complex formats and business logic according to business needs; for example, it cannot automatically classify and summarize data according to different business rules and generate corresponding report layouts.

[0005] Insufficient system stability and a high risk of memory overflow when exporting large amounts of data. For example, the Apache POI raw API call scheme loads the entire file or part of the data into memory for processing Excel files. When exporting large reports, such as those containing millions of rows of data, memory usage increases rapidly as data is loaded. Since memory resources are limited, when memory usage exceeds the system's available memory, it leads to a memory overflow error, causing the application to crash and affecting system stability. Similarly, the basic data export function of database management tools typically reads the data into memory all at once for processing and format conversion before writing it to the export file. This method also faces the problem of memory overflow when handling large amounts of data. For instance, when exporting a large amount of data from a large database, the database management tool may not be able to effectively manage memory usage, leading to insufficient memory, causing the export process to fail or the system to crash.

[0006] Computational performance bottlenecks lead to inefficient multi-data source association processing. For example, the original Apache POI API primarily focuses on Excel file operations and lacks an efficient mechanism for multi-data source association. When data needs to be retrieved from multiple different data sources (such as different databases, file systems, etc.) and associated, developers need to write complex code to implement data reading, association, and integration. This code often lacks optimization, leading to decreased computational performance when processing large amounts of data. For instance, it may require multiple loops to traverse the data for association operations, increasing time complexity. Furthermore, while the fixed template export function of commercial BI tools supports multi-data source connections to some extent, its functionality may be limited by template design when handling multi-data source association. For example, the template may not be flexible enough to adapt to complex relationships between different data sources, resulting in extensive data transformation and matching operations during data association, reducing computational efficiency. Moreover, commercial BI tools may experience slow computation speeds and long response times when handling large volumes of multi-data source association due to insufficient architecture and performance optimization. Finally, the basic data export function of database management tools focuses on the management and operation of single databases and has weak capabilities for multi-data source association processing. When data needs to be retrieved from multiple databases or other data sources and exported as reports, database management tools typically require external scripts or tools to perform data association. This process is not only complex but also inefficient. For example, it may be necessary to first export data from different data sources to temporary files and then use scripts to merge and associate them, increasing intermediate steps and time costs in data processing. Summary of the Invention

[0007] To address the technical problems in existing technologies, such as the reliance on developers to hard-code format adjustments during data export, which complicates implementation, the inability to dynamically adapt to changing business template requirements leading to poor table export flexibility, the high risk of memory overflow during large-scale data export resulting in insufficient system stability, and the low efficiency of multi-data source association processing leading to computer performance bottlenecks, this invention provides a method and system for dynamic data export based on template configuration.

[0008] According to one aspect of the present invention, the present invention provides a method for dynamically exporting data based on templated configuration, comprising:

[0009] Obtain a data export request to export data from a data source in the form of a report. The data export request includes user permission data generated based on the user role that the client sends the data export request and template category data based on the data export template selected from a pre-built template category tree.

[0010] The user permission data is validated, and a validation result is generated, wherein the validation result includes validation passed and validation failed;

[0011] When the verification result is successful, based on the pre-established mapping relationship between template classification data and template metadata, a dynamic SQL statement is generated according to the template metadata corresponding to the template classification data. The dynamic SQL statement includes the type and source of the data source to which the data to be exported belongs, as well as pagination parameters generated according to the custom pagination value.

[0012] The pagination query is executed according to the dynamic SQL statement, and the data obtained in batches from the data source to which the data to be exported belongs is processed and streamed.

[0013] When the pagination query ends and the last batch of data has been processed and streamed, a temporary file is generated and a download link is returned to the client. The download link is used to trigger the download of the data in the temporary file and generate a report corresponding to the data export request.

[0014] According to another aspect of the present invention, the present invention provides a data dynamic export system based on templated configuration, the system comprising:

[0015] The system includes a client and a server. The client has a template selection UI installed, and the server includes a template management module, a query construction module, a data processing module, and an export engine module.

[0016] The template selection UI is used to generate a data export request to export data from the data source in the form of a report. The data export request includes user permission data generated based on the user role that the client sends the data export request and template category data based on the data export template selected from a pre-built template category tree.

[0017] The template management module is used to obtain the data export request, verify the user permission data, and generate a verification result, wherein the verification result includes verification passed and verification failed.

[0018] The query construction module is used to generate a dynamic SQL statement based on the template metadata corresponding to the template category data when the verification result is successful, based on the pre-established mapping relationship between template category data and template metadata. The dynamic SQL statement includes the type and source of the data source to which the data to be exported belongs, as well as pagination parameters generated based on the custom pagination value.

[0019] The data processing module is used to execute pagination queries according to the dynamic SQL statement, process and stream data obtained in batches from the data source to which the data to be exported belongs, and generate temporary files when the pagination query ends and the last batch of data obtained has completed data processing and streaming.

[0020] The export engine module is used to generate download links for temporary files and return the download links to the client. The download links are used to trigger the download of data in the temporary files and generate a report corresponding to the data export request.

[0021] According to another aspect of the present invention, a computer-readable storage medium is provided, the storage medium storing a computer program that, when executed by a processor, implements the methods described in any of the above aspects of the present invention.

[0022] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising: a processor; a memory for storing executable instructions of the processor; the processor being configured to read the executable instructions from the memory and execute the instructions to implement the method described in any of the preceding aspects of the present invention.

[0023] The present invention discloses a method and system for dynamic data export based on template configuration. The method includes: acquiring a data export request to export data from a data source in report form; verifying user permission data in the request and generating a verification result; when the verification result is successful, generating a dynamic SQL statement based on the template metadata corresponding to the template category data, based on a pre-established mapping relationship between template category data and template metadata; executing a paginated query based on the dynamic SQL statement, processing and streaming the data obtained in batches from the data source to which the data to be exported belongs; when the paginated query ends and the last batch of data has completed data processing and streaming, generating a temporary file, returning a download link to the client to trigger the download of data in the temporary file, and generating a report corresponding to the data export request. The method and system improve template adjustment efficiency by 80% by replacing hard-coded template definitions with visual configuration, and reduce memory consumption by 90% by replacing traditional full loading with memory-optimized paginated streaming export, thereby increasing the success rate of large data export to 99.9%. In addition to the above effects, the present invention can also realize version backtracking of data export templates and fine-grained permission control. Attached Figure Description

[0024] Exemplary embodiments of the present invention can be more fully understood by referring to the following figures:

[0025] Figure 1A flowchart of a method for dynamically exporting data based on templated configuration according to a preferred embodiment of the present invention;

[0026] Figure 2 This is a schematic diagram of the structure of a data dynamic export system based on templated configuration according to a preferred embodiment of the present invention;

[0027] Figure 3 This is a schematic diagram of the structure of an electronic device according to a preferred embodiment of the present invention. Detailed Implementation

[0028] Exemplary embodiments of the invention will now be described with reference to the accompanying drawings. However, the invention may be embodied in many different forms and is not limited to the embodiments described herein. These embodiments are provided to fully and completely disclose the invention and to fully convey its scope to those skilled in the art. The terminology used in the exemplary embodiments illustrated in the drawings is not intended to limit the invention. In the drawings, the same units / elements are referred to by the same reference numerals.

[0029] Unless otherwise stated, the terms used herein (including technical terms) have their common meaning as understood by one of ordinary skill in the art. Furthermore, it is understood that terms defined in commonly used dictionaries should be understood to have a meaning consistent with the context of their relevant field, and not to be interpreted as having an idealized or overly formal meaning.

[0030] Exemplary methods

[0031] Figure 1 This is a flowchart of a method for dynamically exporting data based on templated configuration according to a preferred embodiment of the present invention. Figure 1 As shown, the data dynamic export method based on templated configuration described in this preferred embodiment starts from step 101.

[0032] In step 101, a data export request is obtained to export data from the data source in the form of a report. The data export request includes user permission data generated based on the user role that the client sends the data export request and template category data based on the data export template selected from a pre-built template category tree.

[0033] Preferably, the process includes, before obtaining the data export request to export data from the data source in report form:

[0034] A template classification tree is built on the client side, and templates are exported for each type of data in the template classification tree to generate corresponding template classification data.

[0035] Define user categories and establish the correspondence between user categories and user permissions;

[0036] Construct template metadata, wherein the template metadata includes data related to field mapping, style rules, formula definition, data filtering conditions and data sorting rules. Field mapping is used to define the correspondence between fields in the report and fields in the data source. Style rules are used to define style information. Formula definition is used to define the formulas used for calculation in the report. Data filtering conditions are used to specify the conditions for obtaining data from the data source. Sorting rules are used to define the sorting method of the data to be exported.

[0037] Establish a mapping relationship between template category data and template metadata to define the template metadata corresponding to each template type.

[0038] In this preferred embodiment, client-side template classification can be based on business domain, report type, etc. For example, it can be divided into financial reports (such as profit and loss statements and balance sheets), sales reports (such as sales order reports and customer sales analysis reports), and human resources reports (such as employee attendance reports and payroll reports). After template classification is completed, a category label is assigned to each type of template as template classification data. Then, a category field is added to the template metadata to correspond to the template classification data, thus establishing a mapping relationship between template classification data and template metadata, thereby limiting the template metadata corresponding to each template type.

[0039] The template metadata constructed in this preferred embodiment mainly includes the following parts:

[0040] Field mapping: Defines the correspondence between fields in the report and fields in the data source. For example, the "Customer Name" field in the report corresponds to the customer_name field in the customers table in the database.

[0041] Style rules: These include style information such as font, color, border, alignment, column width, and row height. For example, the header row uses a bold, 14-point font with a light blue background; the data rows use a 10-point font with a thin solid border.

[0042] Formula definition: Formulas used to perform calculations in reports. For example, SUM(column_name) to calculate the sum of a column, or IF(condition,value_if_true,value_if_false) to perform calculations based on conditions.

[0043] Data filtering criteria: Specify the criteria for retrieving data from the data source. For example, retrieve only order data with the status "completed" (status='completed').

[0044] Sorting rules: Define how the data is sorted, such as sorting by a specific field in ascending or descending order. For example, sort by order date in descending order: ORDER BY order_date DESC.

[0045] Preferably, the definition of user roles and the establishment of the correspondence between user roles and user permissions are implemented using a role-based access control (RBAC) model, including:

[0046] Define roles based on the client's business requirements;

[0047] Assign appropriate permissions to each role;

[0048] Assign users of the client to the corresponding roles and establish a mapping between user roles and user permissions.

[0049] This preferred embodiment implements fine-grained access control for client users through the RBAC model, and its specific implementation process is as follows:

[0050] Define roles: Define different roles based on business needs, such as administrator, template creator, template viewer, etc.

[0051] Assign permissions: Assign corresponding permissions to each role. For example, administrators can create, modify, delete, and view all templates; template creators can create and modify the templates they create; template viewers can only view the template's metadata and preview its appearance.

[0052] User role assignment: Assign users to appropriate roles. For example, assign system administrators to the administrator role and regular business users to the template viewer role.

[0053] In step 102, the user permission data is verified to generate a verification result, wherein the verification result includes verification passed and verification failed.

[0054] In this preferred embodiment, user permission verification refers to verifying the user's role and assigned permissions extracted from the user permission data when the user needs to download a report from the server, ensuring that the user can only perform operations within their authorized scope.

[0055] In step 103, when the verification result is successful, a dynamic SQL statement is generated based on the pre-established mapping relationship between template classification data and template metadata, according to the template metadata corresponding to the template classification data. The dynamic SQL statement includes the type and source of the data source to which the data to be exported belongs, as well as pagination parameters generated based on the custom pagination value.

[0056] Preferably, generating dynamic SQL statements based on the template metadata corresponding to the template classification data includes:

[0057] Parse the template metadata corresponding to the template category data, extract field mapping information, and map the report fields to the data source fields;

[0058] Based on the type of data source, construct a basic query statement framework. For example, if the filter condition is status='completed', then add WHERE status='completed' to the query statement.

[0059] Add the data filtering conditions from the template metadata corresponding to the template category data to the query statement. For example, if the sorting rule is to sort by order date in descending order, then add ORDER BY order_date DESC to the query statement.

[0060] Add the sorting rules from the template metadata corresponding to the template category data to the query statement. For example, if the sorting rule is to sort by order date in descending order, then add ORDER BY order_date DESC to the query statement.

[0061] When a formula definition exists in the template metadata corresponding to the template classification data, determine the function to perform the corresponding calculation or processing in SQL. For example, for a summation formula, aggregate functions can be used in SQL for calculation.

[0062] Based on the custom pagination value and the number of data to be exported, add a pagination parameter to the query statement. For example, if the custom pagination value is pageSize = 5000 and the number of data to be exported is 15000, then the pagination parameter 15000 / 5000 = 3 needs to be generated. The database returns the data in 3 batches according to the dynamic SQL statement with this pagination parameter. For example, the first query returns the first 5000 data, and the pagination parameter is decremented by 1. After processing and storing the first batch of data to be exported, the pagination parameter is checked to see if it is 0 to determine if all the data to be exported has been obtained. If there is still data that has not been exported, the second query is executed, and the next 5000 data are returned according to the offset. This process continues until all the data to be exported is obtained.

[0063] Preferably, after generating a dynamic SQL statement based on the template metadata corresponding to the template classification data according to a pre-established mapping relationship between template classification data and template metadata, the method further includes pre-associating the data source according to the data source type and source in the dynamic SQL statement and following custom association rules, wherein the association rules include:

[0064] When the data source is a relational database, pre-associations can be performed using foreign keys or JOIN operations. For example, when retrieving data from the order table and the customer table, a JOIN statement can be used to join the two tables; or

[0065] When the database originates from different systems or databases, pre-association is performed during data extraction, transformation, and loading using data middleware, such as Apache Kafka or Apache NiFi.

[0066] In this preferred embodiment, after pre-associating the data source, the pre-associated data can be further cleaned, transformed, and calculated before executing the dynamic SQL statement. For example, the data can be formatted, calculated according to business rules, and data that does not meet the requirements can be filtered out, thereby ensuring the validity of the data to be exported.

[0067] In step 104, a pagination query is executed according to the dynamic SQL statement, and the data obtained in batches from the data source to which the data to be exported belongs is processed and streamed.

[0068] Preferably, the pagination query is executed according to the dynamic SQL statement, and the data obtained in batches from the data source to which the data to be exported belongs is processed and streamed, including:

[0069] When performing a paginated query, the data to be exported is retrieved row by row from the database based on the cursor returned by the database, processed, and then stored in the memory cache;

[0070] The data to be exported is read line by line from the memory cache and then streamed into the SXSSFWorkbook object using the POI API.

[0071] In this preferred embodiment, when performing a paginated query, the database returns a cursor, through which the data to be exported can be retrieved row by row. For example, in MySQL, a cursor can be created using the CURSOR statement, and then the data to be exported can be retrieved row by row using the FETCH statement. The data retrieved from the database cursor is first stored in a memory cache. The memory cache can be implemented using some caching framework (such as Redis, Memcached, etc.) or simple in-memory data structures (such as lists, dictionaries, etc.). For example, a Python list can be used to temporarily store the retrieved data rows. When the data to be exported is read row by row from the memory cache, and then the data is streamed into an SXSSFWorkbook object using the POI API, in Java, SXSSFRow and SXSSFCell can be used to create rows and cells, and the data can be filled into the cells.

[0072] When the template metadata corresponding to the data export template contains formula definitions, the data to be exported needs to be processed according to the formula definitions to perform formula calculations on the corresponding data to meet business requirements. For example, this could involve calculating the sum or average of a column, or filtering and calculating data based on conditions. Specifically, the formulas in the template metadata are parsed into an Abstract Syntax Tree (AST). The AST is traversed, and the formula values ​​are calculated step-by-step according to the tree's hierarchical structure and operational rules. For example, for the formula SUM(A1:A10)+B1, the value of SUM(A1:A10) is calculated first, then the value of B1 is calculated, the two results are added together, and finally, the calculated result is filled into the corresponding cells of the report.

[0073] When processing the data to be exported, a style cache pool based on cell coordinates is used to establish a relationship for the style rules in the template metadata corresponding to the data export template. The style cache pool is a data structure used to store cell style information. It uses cell coordinates (such as row and column numbers) as keys and style objects as values. For example, a dictionary can be used, with keys (row, col) and values ​​containing style information such as font, color, and border. Specifically, according to the style rules in the template metadata, a corresponding style is determined for each cell. Then, the cell coordinates and the corresponding style information are stored in the style cache pool. When a style needs to be applied, the style information is retrieved from the cache pool based on the cell coordinates and applied to the cell. For example, in Java, a HashMap can be used to implement the style cache pool. When processing each cell, a corresponding style object is created according to the style rules in the template metadata and placed in the HashMap along with the cell coordinates. When writing to an Excel file, the style object is retrieved from the HashMap based on the cell coordinates and applied to the cell.

[0074] In step 105, when the pagination query ends and the last batch of data obtained has completed data processing and streaming writing, a temporary file is generated and a download link is returned to the client. The download link is used to trigger the download of the data in the temporary file and generate a report corresponding to the data export request.

[0075] Preferably, the method further includes version control of the data export template using a Git-like incremental storage mechanism, wherein the version control includes:

[0076] When a user modifies the data export module, the change information corresponding to the modification is recorded, and an incremental file is generated. The incremental file only contains the difference between the current modification and the previous data export template version.

[0077] Store the incremental file and associate the incremental file with the version information of the data export template;

[0078] When the data export template is to be rolled back to a historical version, the data export template corresponding to the historical version is gradually restored according to the stored incremental files.

[0079] In this preferred embodiment, each modification to the data export template (such as field mapping adjustments, style rule changes, formula definition modifications, etc.) generates a new template version. When a user modifies the template, the client records the specific details of the modification, such as which field mappings, style rules, or formula definitions were changed. Then, based on the recorded change information, an incremental file is generated. This file only contains the differences between the current modification and the previous version. The generated incremental file is then stored in a specific location on the server and associated with the template's version information. Finally, when a rollback to a certain version is needed, the template for that version is gradually restored based on the stored incremental files. For example, starting from the current version, reverse incremental files are applied sequentially until the target version is reached.

[0080] The preferred embodiment of the data dynamic export method based on template configuration allows the client user to select a data export template, the server to verify user permissions and load the template metadata corresponding to the data export template, construct dynamic SQL statements to execute paginated queries, obtain the data to be exported in batches and stream it into an object, store it in memory, and generate a download link to notify the client to download the table. This realizes a visual configuration to replace hard-coded template definition and memory-optimized paginated streaming export, thereby greatly improving the template adjustment efficiency and the success rate of big data export, and reducing memory consumption.

[0081] Exemplary System

[0082] Figure 2 This is a schematic diagram of the structure of a data dynamic export system based on templated configuration according to a preferred embodiment of the present invention. Figure 2 As shown, the data dynamic export system 200 based on templated configuration described in this preferred embodiment includes a client 201 and a server 202. The client is equipped with a template selection UI 211, and the server 202 includes a template management module 221, a query construction module 222, a data processing module 223, and an export engine module 224.

[0083] The template UI211 is selected to generate a data export request that exports data from the data source in the form of a report. The data export request includes user permission data generated based on the user role that the client sends the data export request and template category data based on the data export template selected from a pre-built template category tree.

[0084] The template management module 221 is used to obtain the data export request, verify the user permission data, and generate a verification result, wherein the verification result includes verification passed and verification failed.

[0085] The query construction module 222 is used to generate a dynamic SQL statement based on the template metadata corresponding to the template category data when the verification result is a successful verification, based on the pre-established mapping relationship between template category data and template metadata. The dynamic SQL statement includes the type and source of the data source to which the data to be exported belongs, as well as pagination parameters generated based on the custom pagination value.

[0086] The data processing module 223 is used to execute pagination queries according to the dynamic SQL statement, process and stream data obtained in batches from the data source to which the data to be exported belongs, and generate temporary files when the pagination query ends and the last batch of data obtained has completed data processing and streaming.

[0087] The export engine module 224 is used to generate a download link for a temporary file and return the download link to the client. The download link is used to trigger the download of data in the temporary file and generate a report corresponding to the data export request.

[0088] Preferably, the client template selection UI211 is also used to build a template classification tree, and to export templates for each type of data in the template classification tree to generate corresponding template classification data;

[0089] The template management module is also used to define user categories and establish a correspondence between user categories and user permissions; construct template metadata, wherein the template metadata includes data related to field mapping, style rules, formula definitions, data filtering conditions, and data sorting rules. Field mapping is used to define the correspondence between fields in the report and fields in the data source. Style rules are used to define style information. Formula definitions are used to define formulas used for calculations in the report. Data filtering conditions are used to specify the conditions for obtaining data from the data source. Sorting rules are used to define the sorting method for the data to be exported; establish a mapping relationship between template category data and template metadata to limit the template metadata corresponding to each template type.

[0090] In this preferred embodiment, the server-side template management module receives data export requests via a RESTful API.

[0091] Preferably, the template management module 221 defines user roles and establishes the correspondence between user roles and user permissions using a role-based access control (RBAC) model, including:

[0092] Define roles based on the client's business requirements;

[0093] Assign appropriate permissions to each role;

[0094] Assign users of the client to the corresponding roles and establish a mapping between user roles and user permissions.

[0095] Preferably, the query construction module 222 generates dynamic SQL statements based on the template metadata corresponding to the template classification data, including:

[0096] Parse the template metadata corresponding to the template category data, extract field mapping information, and map the report fields to the data source fields;

[0097] Based on the type of data source, construct a basic query statement framework;

[0098] Add the data filtering conditions from the template metadata corresponding to the template classification data to the query statement;

[0099] Add the sorting rules from the template metadata corresponding to the template classification data to the query statement;

[0100] When a formula definition exists in the template metadata corresponding to the template classification data, determine the function to perform the corresponding calculation or processing in SQL.

[0101] Add pagination parameters to the query statement based on the custom pagination value and the amount of data to be exported.

[0102] Preferably, the query construction module 222 is further configured to pre-associate the data source according to the data source type and source in the dynamic SQL statement, based on a custom association rule, wherein the association rule includes:

[0103] When the data source is a relational database, pre-association is performed using foreign keys or JOIN operations; or

[0104] When the database originates from different systems or databases, pre-association is performed during data extraction, transformation, and loading using data middleware.

[0105] Preferably, the data processing module 223 executes a pagination query based on the dynamic SQL statement, and processes and streams the data obtained in batches from the data source to which the data to be exported belongs, including:

[0106] When performing a paginated query, the data to be exported is retrieved row by row from the database based on the cursor returned by the database, processed, and then stored in the memory cache;

[0107] The data to be exported is read line by line from the memory cache and then streamed into the SXSSFWorkbook object using the POI API.

[0108] Preferably, the template management module 221 is further configured to perform version control on the data export template using a Git-like incremental storage mechanism, wherein the version control includes:

[0109] When a user modifies the data export module, the change information corresponding to the modification is recorded, and an incremental file is generated. The incremental file only contains the difference between the current modification and the previous data export template version.

[0110] Store the incremental file and associate the incremental file with the version information of the data export template;

[0111] When the data export template is to be rolled back to a historical version, the data export template corresponding to the historical version is gradually restored according to the stored incremental files.

[0112] The data dynamic export system based on template configuration described in this preferred embodiment and the data dynamic export method based on template configuration have the same steps: the client user selects a data export template, the server verifies the user's permissions and loads the template metadata corresponding to the data export template, constructs dynamic SQL statements to execute paginated queries, obtains the data to be exported in batches and streams it into an object, stores it in memory, and generates a download link to notify the client to download. The technical effects achieved are also the same, and will not be described in detail here.

[0113] Exemplary electronic devices

[0114] Figure 3 This is a schematic diagram of the structure of an electronic device according to a preferred embodiment of the present invention. Figure 3 As shown, the electronic device includes one or more processors 301 and memory 302.

[0115] The processor 301 may be a central processing unit (CPU) or other form of processing unit with data processing capabilities and / or instruction execution capabilities, and may control other components in the electronic device to perform desired functions.

[0116] The memory 302 may include one or more computer program products, which may include various forms of computer-readable storage media, such as volatile memory and / or non-volatile memory. The volatile memory may include, for example, random access memory (RAM) and / or cache memory. The non-volatile memory may include, for example, read-only memory (ROM), hard disk, flash memory, etc. One or more computer program instructions may be stored on the computer-readable storage medium, and the processor 301 may execute the program instructions to implement the template-based configuration-based dynamic data export method and / or other desired functions described in the various embodiments disclosed above. In one example, the electronic device may also include an input device 303 and an output device 304, which are interconnected via a bus system and / or other forms of connection mechanisms (not shown).

[0117] In addition, the input device 303 may also include, for example, a keyboard, a mouse, etc.

[0118] The output device 304 can output various information to the outside. The output device 304 may include, for example, a display, a speaker, a printer, and a communication network and its connected remote output devices, etc.

[0119] Of course, for the sake of simplicity, Figure 3 Only some of the components of the electronic device relevant to this disclosure are shown, omitting components such as buses, input / output interfaces, etc. In addition, the electronic device may include any other suitable components depending on the specific application.

[0120] Exemplary computer program products and computer-readable storage media

[0121] In addition to the methods and apparatus described above, embodiments of this disclosure may also be computer program products comprising computer program instructions that, when executed by a processor, cause the processor to perform the steps in the data dynamic export method based on templated configuration according to various embodiments of this disclosure as described in the "Exemplary Methods" section above.

[0122] The computer program product can be written in any combination of one or more programming languages ​​to perform the operations of the embodiments of this disclosure. The programming languages ​​include object-oriented programming languages ​​such as Java and C++, as well as conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on a user's computing device, partially on a user's computing device, as a standalone software package, partially on a user's computing device and partially on a remote computing device, or entirely on a remote computing device or server.

[0123] Furthermore, embodiments of this disclosure may also be computer-readable storage media storing computer program instructions that, when executed by a processor, cause the processor to perform the steps in the data dynamic export method based on templated configuration according to various embodiments of this disclosure as described in the "Exemplary Methods" section above.

[0124] The computer-readable storage medium may be any combination of one or more readable media. A readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may, for example, include, but is not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatuses, or devices, or any combination thereof. More specific examples of readable storage media (a non-exhaustive list) include: electrical connections having one or more wires, portable disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0125] The basic principles of this disclosure have been described above with reference to specific embodiments. However, it should be noted that the advantages, benefits, and effects mentioned in this disclosure are merely examples and not limitations, and should not be considered as essential features of each embodiment of this disclosure. Furthermore, the specific details disclosed above are for illustrative and facilitative purposes only, and are not limitations. These details do not limit the scope of this disclosure to the necessity of employing the aforementioned specific details for implementation.

[0126] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For system embodiments, since they largely correspond to method embodiments, the description is relatively simple; relevant parts can be referred to the descriptions in the method embodiments.

[0127] The block diagrams of devices, apparatuses, devices, and systems disclosed herein are merely illustrative examples and are not intended to require or imply that they must be connected, arranged, or configured in the manner shown in the block diagrams. As those skilled in the art will recognize, these devices, apparatuses, devices, and systems can be connected, arranged, and configured in any manner. Words such as “comprising,” “including,” “having,” etc., are open-ended terms meaning “including but not limited to,” and are used interchangeably with them. The terms “or” and “and” as used herein refer to the terms “and / or,” and are used interchangeably with them unless the context clearly indicates otherwise. The term “such as” as used herein refers to the phrase “such as but not limited to,” and is used interchangeably with it.

[0128] The apparatus and methods of this disclosure may be implemented in many ways. For example, they may be implemented by software, hardware, firmware, or any combination of software, hardware, and firmware. The above-described order of steps for the methods is for illustrative purposes only, and the steps of the methods of this disclosure are not limited to the order specifically described above unless otherwise specifically stated. Furthermore, in some embodiments, this disclosure may also be implemented as a program recorded on a recording medium, the program including machine-readable instructions for implementing the methods according to this disclosure. Thus, this disclosure also covers recording media storing programs for performing the methods according to this disclosure.

[0129] It should also be noted that in the apparatus, devices, and methods of this disclosure, the components or steps are decomposable and / or recombinable. Such decomposition and / or recombination should be considered equivalent to the present disclosure. The above description of the disclosed aspects is provided to enable any person skilled in the art to make or use this disclosure. Various modifications to these aspects will be readily apparent to those skilled in the art, and the general principles defined herein can be applied to other aspects without departing from the scope of this disclosure. Therefore, this disclosure is not intended to be limited to the aspects shown herein, but rather to be carried out within the widest scope consistent with the principles and novel features disclosed herein.

[0130] The above description has been given for purposes of illustration and description. Furthermore, this description is not intended to limit the embodiments of this disclosure to the forms disclosed herein. Although numerous exemplary aspects and embodiments have been discussed above, those skilled in the art will recognize certain variations, modifications, alterations, additions, and sub-combinations therein.

Claims

1. A method for dynamically exporting data based on templated configuration, characterized in that, The method includes: Obtain a data export request to export data from a data source in the form of a report. The data export request includes user permission data generated based on the user role that the client sends the data export request and template category data based on the data export template selected from a pre-built template category tree. The user permission data is validated, and a validation result is generated, wherein the validation result includes validation passed and validation failed; When the verification result is successful, based on the pre-established mapping relationship between template classification data and template metadata, a dynamic SQL statement is generated according to the template metadata corresponding to the template classification data. The dynamic SQL statement includes the type and source of the data source to which the data to be exported belongs, as well as pagination parameters generated according to the custom pagination value. The pagination query is executed according to the dynamic SQL statement, and the data obtained in batches from the data source to which the data to be exported belongs is processed and streamed. When the pagination query ends and the last batch of data has been processed and streamed, a temporary file is generated and a download link is returned to the client. The download link is used to trigger the download of the data in the temporary file and generate a report corresponding to the data export request.

2. The method according to claim 1, characterized in that, Before obtaining a data export request that exports data from a data source in report form, the following steps are included: A template classification tree is built on the client side, and templates are exported for each type of data in the template classification tree to generate corresponding template classification data. Define user categories and establish the correspondence between user categories and user permissions; Construct template metadata, wherein the template metadata includes data related to field mapping, style rules, formula definition, data filtering conditions and data sorting rules. Field mapping is used to define the correspondence between fields in the report and fields in the data source. Style rules are used to define style information. Formula definition is used to define the formulas used for calculation in the report. Data filtering conditions are used to specify the conditions for obtaining data from the data source. Sorting rules are used to define the sorting method of the data to be exported. Establish a mapping relationship between template category data and template metadata to define the template metadata corresponding to each template type.

3. The method according to claim 2, characterized in that, The definition of user roles and the establishment of the correspondence between user roles and user permissions are implemented using a role-based access control (RBAC) model, including: Define roles based on the client's business requirements; Assign appropriate permissions to each role; Assign users of the client to the corresponding roles and establish a mapping between user roles and user permissions.

4. The method according to claim 2, characterized in that, Generate dynamic SQL statements based on the template metadata corresponding to the template classification data, including: Parse the template metadata corresponding to the template category data, extract field mapping information, and map the report fields to the data source fields; Based on the type of data source, construct a basic query statement framework; Add the data filtering conditions from the template metadata corresponding to the template classification data to the query statement; Add the sorting rules from the template metadata corresponding to the template classification data to the query statement; When a formula definition exists in the template metadata corresponding to the template classification data, determine the function to perform the corresponding calculation or processing in SQL. Add pagination parameters to the query statement based on the custom pagination value and the amount of data to be exported.

5. The method according to claim 1, characterized in that, After generating a dynamic SQL statement based on the template metadata corresponding to the template classification data, the process further includes pre-associating the data source according to the data source type and source in the dynamic SQL statement and following custom association rules. The association rules include: When the data source is a relational database, pre-association is performed using foreign keys or JOIN operations; or When the database originates from different systems or databases, pre-association is performed during data extraction, transformation, and loading using data middleware.

6. The method according to claim 1, characterized in that, Execute pagination queries based on the dynamic SQL statements, and process and stream the data obtained in batches from the data source to which the data to be exported belongs, including: When performing a paginated query, the data to be exported is retrieved row by row from the database based on the cursor returned by the database, processed, and then stored in the memory cache; The data to be exported is read line by line from the memory cache and then streamed into the SXSSFWorkbook object using the POI API.

7. The method according to claim 1, characterized in that, The method further includes version control of the data export template using a Git-like incremental storage mechanism, wherein the version control includes: When a user modifies the data export module, the change information corresponding to the modification is recorded, and an incremental file is generated. The incremental file only contains the difference between the current modification and the previous data export template version. Store the incremental file and associate the incremental file with the version information of the data export template; When the data export template is to be rolled back to a historical version, the data export template corresponding to the historical version is gradually restored according to the stored incremental files.

8. A data dynamic export system based on templated configuration, characterized in that, The system includes a client and a server. The client has a template selection UI installed, and the server includes a template management module, a query construction module, a data processing module, and an export engine module. The template selection UI is used to generate a data export request to export data from the data source in the form of a report. The data export request includes user permission data generated based on the user role that the client sends the data export request and template category data based on the data export template selected from a pre-built template category tree. The template management module is used to obtain the data export request, verify the user permission data, and generate a verification result, wherein the verification result includes verification passed and verification failed. The query construction module is used to generate a dynamic SQL statement based on the template metadata corresponding to the template category data when the verification result is successful, based on the pre-established mapping relationship between template category data and template metadata. The dynamic SQL statement includes the type and source of the data source to which the data to be exported belongs, as well as pagination parameters generated based on the custom pagination value. The data processing module is used to execute pagination queries according to the dynamic SQL statement, process and stream data obtained in batches from the data source to which the data to be exported belongs, and generate temporary files when the pagination query ends and the last batch of data obtained has completed data processing and streaming. The export engine module is used to generate download links for temporary files and return the download links to the client. The download links are used to trigger the download of data in the temporary files and generate a report corresponding to the data export request.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the steps of the method as described in any one of claims 1-7.

10. An electronic device, characterized in that, include: processor; Memory used to store the processor's executable instructions; The processor is configured to read the executable instructions from the memory and execute the instructions to implement the method described in any one of claims 1-7.

Images