Dynamic header rendering method and system based on EasyExcel

Abstract

The invention provides a dynamic header rendering method and system based on EasyExcel, and belongs to the technical field of data export and report generation. During export, an export request containing a service type and a request parameter is received, a callback address, a request mode and header field configuration are read from a database according to the service type, and an export report is generated; the field configuration at least comprises a field key, a field name, a data type and sorting information, and can comprise a header hierarchy and a style merging rule; the system constructs a header list required by EasyExcel, obtains service data according to callback configuration and converts the service data into Listt; mapt, Mapt; the method comprises the following steps of: (1) carrying out String, Objectgt Gt; and the form and the header field key are dynamically matched and written. An Excel file is generated in a paging writing mode so as to reduce memory occupation, and finally the file is stored in an object storage and returned to a downloading address. The method supports quick adjustment of the multi-task type header, reduces code modification and repeated model class maintenance, and improves the export stability of large data volume.

Description

A method and system for dynamic header rendering based on EasyExcel Technical Field

[0001] This invention belongs to the field of data export and report generation technology, and in particular relates to a dynamic header rendering method and system based on EasyExcel. Background Technology

[0002] In business systems, a common requirement is to export data from different business scenarios to Excel files. Existing implementation methods typically include: first, hardcoding the list of header fields in the code, directly defining the header structure and writing it; second, defining a model class containing header fields and using annotations to generate static headers; and third, manually configuring the write processor to achieve effects such as style settings and cell merging.

[0003] The above approach is prone to shortcomings in scenarios with multiple task types and frequent changes in multiple versions of fields: header fields often need to be fixed in the code in advance, making it difficult to adjust in real time according to business scenarios; changes to headers usually require code modification and redeployment, resulting in high maintenance costs; at the same time, binding model classes to headers will cause different export tasks to need to create multiple sets of redundant classes, increasing the coupling between data and headers, which is not conducive to building a unified export capability. Summary of the Invention

[0004] In view of this, the present invention aims to propose a dynamic header rendering method and system based on EasyExcel, so as to at least solve one of the problems in the background art.

[0005] To achieve the above objectives, the technical solution of the present invention is implemented as follows: A dynamic header rendering method based on EasyExcel, applied to a computer device, includes: receiving an export request, the export request at least including a business type identifier and data request parameters; based on the business type identifier, querying a business type table in a database to obtain a callback address and request method; querying a field parameter table in the database to obtain a header field configuration list associated with the business type identifier, the header field configuration list at least including a field key, field name, data type, sorting information, and hierarchical information for representing multi-level header hierarchy, and optionally including column width and merge unit. The system retrieves style parameters such as grid rules; constructs a multi-row header structure based on the sorting information and hierarchy information, converts it into a header list recognizable by EasyExcel, and configures a write processor based on the style parameters; retrieves business data by carrying the data request parameters according to the callback address and request method, and converts the business data into a list of key-value pairs with the field key as the key; calls EasyExcel's ExcelWriter to write the header list and the key-value pair list to generate an Excel file, wherein the writing uses pagination export to limit the number of rows written at one time; saves the Excel file to object storage and generates a download address to return.

[0006] Furthermore, the business type table includes at least a business type identifier, a callback address, and a request method field; the field parameter table includes at least a business type identifier, a field key, a field name, a data type, and a sorting field.

[0007] Furthermore, the hierarchy information includes at least one of the following: parent field identifier parentKey, hierarchy index levelIndex, and hierarchy path levelPath; when constructing a multi-row header structure, the header text corresponding to the same field is split into a multi-level header array according to the hierarchy information, and a List of the header list is generated. <List <string>>Structure.

[0008] Furthermore, the style parameters include at least column width, header font, alignment, bolding, background fill, and cell merging rules; the write processor includes a WriteHandler, which is used to apply the style parameters and perform cell merging during writing.

[0009] Furthermore, before constructing the header list, configuration validation is performed on the header field configuration list. The configuration validation includes at least: field key uniqueness validation, sorting information continuity or comparability validation, hierarchical information non-cyclic validation, and title text non-empty validation under the same level. When the validation fails, the export is terminated and an error message is returned.

[0010] Furthermore, obtaining business data includes: initiating an HTTP request based on the callback address and the request method, and carrying the data request parameters; executing a retry strategy when the request fails, the retry strategy including at least an upper limit on the number of retries and a retry interval; recording the reason for failure and returning the export failure result after the upper limit is reached.

[0011] Furthermore, converting the business data into a list of key-value pairs includes: using a List <Map<String,Object> The Map contains row data, where the key of the Map is the field key; and the Map's values ​​are converted or formatted according to the data types in the field parameter table, where the data types include at least numeric and string types.

[0012] Furthermore, paginated export includes: dividing business data into multiple batches of data blocks by page, writing each batch to ExcelWriter, and releasing the memory reference of the previous batch of data blocks after writing, so as to reduce the peak memory usage during the export process.

[0013] Furthermore, a header template cache is maintained for each business type identifier. The header template cache includes at least a header list and write processor configuration results. When a change is detected in the record corresponding to the field parameter table, the cache is updated and the header template is regenerated.

[0014] Furthermore, the cell merging rules include header merging rules and data area merging rules; wherein the header merging rules are used to merge adjacent cells horizontally when the header text is the same and they are at the same level, and the data area merging rules are used to merge adjacent rows of cells vertically according to the merging conditions specified by the field key.

[0015] Furthermore, before or after receiving the export request, the method includes: calling the task creation interface to generate a parameter template or parameter validation rule corresponding to the business type identifier, and returning the parameter template or parameter validation rule to the caller to constrain the legality of the data request parameters.

[0016] Furthermore, this solution discloses a dynamic header rendering system based on EasyExcel, comprising: a database module for storing business type tables and field parameter tables; a configuration management module for configuring and maintaining header field configuration lists and style parameters; a dynamic rendering engine for querying the header field configuration list based on the business type identifier and constructing the header list, calling EasyExcel's ExcelWriter to write the header list and business data into an Excel file, and exporting it using pagination; and a storage and distribution module for saving the Excel file to object storage and generating a download address for return.

[0017] Furthermore, the dynamic rendering engine includes a template caching unit and a configuration verification unit; the configuration verification unit is used to perform field key uniqueness, hierarchical acyclicity, and sorting validity verification; the template caching unit is used to identify the cache header list and write the processor configuration result according to the business type, and refresh the cache when the configuration changes.

[0018] Furthermore, this solution discloses a computer-readable storage medium having a computer program stored thereon, the computer program implementing the method when executed by a processor.

[0019] Compared with the prior art, the dynamic header rendering method and system based on EasyExcel described in this invention has the following advantages: (1) This invention stores the mapping relationship between business types and field configurations in the database, and loads the header fields and hierarchical relationships in real time according to the business type during export, so that the same set of export capabilities can adapt to different task types. The addition, deletion or adjustment of header columns or order can be completed through configuration, reducing the number of code modifications and deployments caused by header changes; (2) The business data of this invention is in the form of List <Map<String,Object> >Formal transmission, Map key corresponds to field key, dynamic rendering engine matches and writes according to field key, avoids creating independent model class for each export task, and reduces the complexity of data structure maintenance; (3) The field configuration of the present invention can maintain hierarchical relationship information, thereby dynamically constructing multi-row header structure; and can maintain style parameters such as column width and merging rules in the configuration management module, so that different export tasks can maintain the same header display effect under the unified platform, reducing the workload of manually adjusting the header style repeatedly on the code side; (4) The present invention adopts the paginated export method to limit the number of rows written at one time, writes and releases intermediate data in batches, which can reduce the peak memory occupation during the export process, reduce the risk of memory overflow, and improve the export stability; (5) The business type table of the present invention maintains the callback address and request method. The export service initiates a request to obtain data according to the configuration, generates Excel and saves it to object storage and returns the download address. The caller does not need to care about the file generation details, which makes it easy to provide the export capability as a general platform capability to multiple business systems. Attached Figure Description

[0020] The accompanying drawings, which form part of this invention, are used to provide a further understanding of the invention. The illustrative embodiments of the invention and their descriptions are used to explain the invention and do not constitute an undue limitation of the invention. In the drawings: Figure 1 is a schematic flowchart of the method described in an embodiment of the invention; Figure 2 is a timing diagram of the method described in an embodiment of the invention. Detailed Implementation

[0021] It should be noted that, unless otherwise specified, the embodiments and features described in the present invention can be combined with each other.

[0022] In the description of this invention, it should be understood that the terms "center," "longitudinal," "lateral," "upper," "lower," "front," "rear," "left," "right," "vertical," "horizontal," "top," "bottom," "inner," and "outer," etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. These terms are used only for the convenience of describing the invention and for simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limitations on the invention. Furthermore, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first," "second," etc., may explicitly or implicitly include one or more of that feature. In the description of this invention, unless otherwise stated, "a plurality of" means two or more.

[0023] In the description of this invention, it should be noted that, unless otherwise explicitly specified and limited, the terms "installation," "connection," and "linking" should be interpreted broadly. For example, they can refer to a fixed connection, a detachable connection, or an integral connection; they can refer to a mechanical connection or an electrical connection; they can refer to a direct connection or an indirect connection through an intermediate medium; and they can refer to the internal communication between two components. Those skilled in the art will understand the specific meaning of the above terms in this invention based on the specific circumstances.

[0024] The present invention will now be described in detail with reference to the accompanying drawings and embodiments.

[0025] This embodiment uses a unified export platform providing Excel export services for multiple business types as a scenario, employing a database configuration-driven approach to achieve dynamic header rendering and export file generation. The system includes at least a database module, a configuration management module, a dynamic rendering engine, and a storage and distribution module. The database module stores at least a business type table and a field parameter table. The business type table includes at least a business type identifier (bussinessType), a callback address (callBackAddress), and a request method (requestMethod, GET or POST) to determine the data source and access method for a specific business type. The field parameter table includes at least a businessType, a field key (columnKey), a field name (columnName), a data type (dataType), and a sorting order to determine the header field set, column order, and write mapping relationship. The dataType includes at least numbers and strings. The configuration management module provides a visual configuration entry point for maintaining business type and field configurations. It can also be extended to maintain column widths, merging rules, and header hierarchy information, allowing for changes in field additions, deletions, renamings, or reorderings to take effect simply by modifying the configuration and writing it to the database, thereby reducing code modification and repeated deployment costs.

[0026] During export execution, the caller can first call the task creation interface to generate a task identifier (taskId) and persist the businessType and callback request parameters. Optionally, to avoid duplicate exports, a task fingerprint can be calculated based on the businessType and request parameters. If the same fingerprint exists within the validity period and a result has already been generated, the existing download address or taskId can be reused directly to achieve idempotency control. Subsequently, the export service queries the business type table based on businessType to obtain callBackAddress and requestMethod, then queries the field parameter table to obtain the {columnKey, columnName, dataType, order} configuration list, and constructs a header list that EasyExcel can recognize by sorting by order. The headerList is a List. <List <string>The format is as follows: When no hierarchical relationship is configured, each column generates only a single-level header text, columnName. When hierarchical relationship information is configured, each column header generates a "header path array" (e.g., [first-level header, second-level header, column header]), thus forming a multi-row, multi-level header and allowing horizontal merging of the same parent header. Optionally, configuration consistency checks are performed before or during header construction, including at least columnKey uniqueness checks, order conflict checks, hierarchical relationship acyclicity checks, and columnName non-empty checks. If the checks fail, the export is terminated and a clear error reason is returned. Optionally, headerList is cached by businessType and the configuration version number or update time is recorded. When a field configuration change is detected, the cache is automatically invalidated and the header is rebuilt to reduce the overhead of repeated construction in high-frequency export scenarios and ensure that configuration changes take effect in a timely manner.

[0027] After the header template is determined, the export service initiates an HTTP request based on the callBackAddress and requestMethod to retrieve business data, and then assembles the business data into a List. <Map<String,Object> The format is as follows: the Map key is columnKey, and the value is the field data content. Dynamic matching and writing of the table header and data are achieved through columnKey, thus avoiding redundancy and strong coupling caused by creating independent model classes for each export task. Optionally, when a callback request fails, it is retried according to a preset retry strategy. The retry strategy includes at least a maximum number of retries and a retry interval. If it still fails after exceeding the upper limit, the reason for the failure is recorded in the task record and the failure result is returned. Optionally, the callback interface supports pagination parameters (page number or cursor, number of rows per page). The export service requests and writes pages in a loop. When the task is interrupted, it can resume running based on the cursor position of the last successful write, thereby reducing the failure rate and repetitive workload when exporting large amounts of data. Optionally, during the data assembly stage, when a row of data is missing a columnKey, it is filled with an empty value or a default value according to rules, and the missing field statistics are recorded to avoid column misalignment. The dataType is used to perform type conversion or formatting of the value, such as numerical conversion or unifying decimal places for numeric types, and blanking for string types. Date and time formatting can also be extended to improve the consistency and usability of data types within Excel.

[0028] During the file generation phase, the dynamic rendering engine, based on EasyExcel's ExcelWriter, separates the header and business data during writing. A paginated export mechanism is preferred to limit the number of rows written per page: only the current batch of data blocks is loaded or retained and written at a time. After writing, the memory reference to that batch of data blocks is released before processing the next batch, thus reducing peak memory usage and minimizing the risk of memory overflow. Optionally, when the cumulative number of written rows exceeds the single-sheet limit or a preset threshold, a new sheet is automatically created to continue writing. After each page is written, the task progress (number of rows written, current page number, current sheet index) is updated and logged or written to the task table to enhance observability and troubleshooting capabilities. After exporting, the Excel file is saved to object storage, and a download address is generated and returned to the caller. Simultaneously, the download address is written back to the task record, and the task status is set to success. Optionally, the download address is a temporary access credential with an expiration date to reduce the risk of long-term exposure of exported files and improve distribution security and controllability.

[0029] In one example, the business type is ecsList: The business type table records businessType=ecsList, callBackAddress=http: / / xxx / instanceList, requestMethod=POST; the field parameter table records columnKey=instance, columnName=instanceID, dataType=String, order=0 and columnKey=instanceName, columnName=instanceName, dataType=String, order=1. After the caller creates a task and initiates the export, the export service reads the above configuration to construct a headerList, retrieves data through the callback interface, and assembles it into a List. <Map<String,Object> The ExcelWriter then writes the data to an Excel file by page and saves it to the object storage, returning the download address. If you want the two columns to be grouped under the same parent title "Instance Information", you can simply add hierarchical relationship information by configuring the header paths to [Instance Information, Instance ID] and [Instance Information, Instance Name], which will automatically generate multi-level headers and complete the merging without modifying the export engine code.

[0030] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.< / string> < / string>

Claims

1. A dynamic header rendering method based on EasyExcel, applied to computer equipment, characterized in that, include: Receive an export request, which includes at least a business type identifier and data request parameters; Based on the business type identifier, the callback address and request method are retrieved from the business type table in the database; the table header field configuration list associated with the business type identifier is retrieved from the field parameter table in the database. This table header field configuration list includes at least a field key, field name, data type, sorting information, and hierarchical information representing a multi-level table header hierarchy, and optionally includes style parameters such as column width and cell merging rules; a multi-row table header structure is constructed based on the sorting information and hierarchical information and converted into a table header list recognizable by EasyExcel, and a write processor is configured based on the style parameters; business data is obtained by carrying the data request parameters according to the callback address and request method, and the business data is converted into a key-value pair list with the field key as the key; EasyExcel's ExcelWriter is called to write the table header list and the key-value pair list to generate an Excel file, wherein pagination is used during writing to limit the number of rows written at a time; the Excel file is saved to object storage and a download address is generated and returned.

2. The method according to claim 1, characterized in that, Before constructing the header list, configuration validation is performed on the header field configuration list. The configuration validation includes at least: field key uniqueness validation, sorting information continuity or comparability validation, hierarchical information non-cyclic validation, and title text non-empty validation under the same level. If the validation fails, the export is terminated and an error message is returned.

3. The method according to claim 1, characterized in that, Obtaining business data includes: initiating an HTTP request based on the callback address and the request method, and carrying the data request parameters; executing a retry strategy when the request fails, the retry strategy including at least an upper limit on the number of retries and a retry interval; recording the reason for failure and returning the export failure result after the upper limit is reached.

4. The method according to claim 1, characterized in that, Converting the business data into a list of key-value pairs includes: using a List <Map<String,Object> The Map contains row data, where the key of the Map is the field key; and the Map's values ​​are converted or formatted according to the data types in the field parameter table, where the data types include at least numeric and string types.

5. The method according to claim 1, characterized in that, Paginated export involves dividing business data into multiple batches of data blocks by page, writing each batch to ExcelWriter, and releasing memory references to the previous batch of data blocks after writing, in order to reduce peak memory usage during the export process.

6. The method according to claim 1, characterized in that, Maintain a header template cache for each business type identifier. The header template cache includes at least a header list and write processor configuration results. When a change is detected in the record corresponding to the field parameter table, update the cache and regenerate the header template.

7. The method according to claim 1, characterized in that, The cell merging rules include header merging rules and data area merging rules; the header merging rules are used to merge adjacent cells horizontally when the header text is the same and they are at the same level, and the data area merging rules are used to merge adjacent rows of cells vertically according to the merging conditions specified by the field key.

8. The method according to claim 1, characterized in that, Before or after receiving the export request, the process also includes: calling the task creation interface to generate a parameter template or parameter validation rule corresponding to the business type identifier, and returning the parameter template or parameter validation rule to the caller to constrain the legality of the data request parameters.

9. A dynamic header rendering system based on EasyExcel, characterized in that, include: The database module is used to store business type tables and field parameter tables; The configuration management module is used to configure and maintain the header field configuration list and style parameters; the dynamic rendering engine is used to query the header field configuration list based on the business type identifier and construct the header list, call EasyExcel's ExcelWriter to write the header list and business data into an Excel file, and export it with pagination; the storage and distribution module is used to save the Excel file to object storage and generate a download address to return.

10. The system according to claim 9, characterized in that, The dynamic rendering engine includes a template caching unit and a configuration verification unit; the configuration verification unit is used to perform field key uniqueness, hierarchical acyclicity, and sorting validity verification; the template caching unit is used to identify the cache header list and write the processor configuration results according to the business type, and refresh the cache when the configuration changes.

Images