Method and system for dynamically generating and exporting excel files

Abstract

This invention provides a method and system for dynamically generating and exporting Excel files, comprising: Step 1: defining a data structure for the content of the Excel file to be exported; Step 2: querying the data to be written to the Excel file and writing the data; Step 3: setting the style attributes of the Excel file; Step 4: downloading the generated Excel file to the local machine. This invention improves the efficiency of report file generation and export without affecting the normal operation of the system; it has strong scalability, achieving dynamic generation of Excel files through dynamically maintaining the defined data structure.

Description

Technical Field

[0001] This invention relates to the field of Excel file processing technology, specifically to a method and system for dynamically generating and exporting Excel files. Background Technology

[0002] Currently, most Excel report generation and export methods use predefined Excel template files, mapping database queries to the results and writing them into these templates. This approach has significant drawbacks: If new export fields are needed, the Excel template file and even the underlying logic must be modified, requiring system redeployment. This method is highly intrusive and suffers from poor maintainability and scalability. Furthermore, generating identical Excel files requires database queries every time, severely impacting program efficiency.

[0003] Patent document CN112099801A (application number: CN202011207609.8) discloses an Excel parsing method and system based on metadata-driven processing, relating to the field of data processing technology. The method includes: generating a first underlying parsing language based on an acquired Excel template file; creating a visual operation interface based on the underlying parsing language, the operation interface being used to adjust the object configuration of cells; acquiring the adjusted cell configuration information from the operation interface and generating a second underlying parsing language; and exporting a newly configured Excel file based on the second underlying parsing language.

[0004] The technique used in this paper does not require defining any Excel template file. It dynamically generates Excel files by maintaining a data structure of the content of an Excel file and uses caching to improve the efficiency of Excel file generation.

[0005] To address the shortcomings of the prior art, the technical problem to be solved by the present invention is reflected in the following aspects:

[0006] 1) Implement dynamic generation of Excel files by maintaining a custom data structure;

[0007] 2) Cache the same data in the caching middleware Redis;

[0008] 3) Intercept the query SQL and update the cache with the latest data when data is updated or deleted. Summary of the Invention

[0009] In view of the deficiencies in the prior art, the purpose of this invention is to provide a method and system for dynamically generating and exporting Excel files.

[0010] The method for dynamically generating and exporting Excel files according to the present invention includes:

[0011] Step 1: Define the data structure for the content of the Excel file to be exported;

[0012] Step 2: Query the data that needs to be written to the Excel file and write the data;

[0013] Step 3: Set the style properties of the Excel file;

[0014] Step 4: Download the generated Excel file to your local computer.

[0015] Preferably, step 1 includes:

[0016] Step 1.1: Define an object whose member variables contain the header of the Excel file and the data to be filled. The data type of the header is a nested list, and the data type of the data to be filled is a nested hash table of lists.

[0017] Step 1.2: Set the way to get member variable values, set member variable values, and construct the data structure. The constructor is used to declare and initialize the member variables of the data structure, and the default value is set to empty;

[0018] Step 1.3: Initialize the header data by assigning the Excel header data to the data structure defined above.

[0019] Preferably, step 2 includes:

[0020] Step 2.1: When the system is running, it queries the database for the data to be written to Excel based on the query conditions, and caches the query results and query conditions in the non-relational database Redis. Each query first checks whether the same query conditions exist in Redis. If they exist, the cached query results are retrieved directly from Redis. Otherwise, the database query statement is executed. In subsequent queries with the same conditions, aspect-oriented programming is used to intercept operations that affect the query results. When the operation type is to update data, delete data, or insert data, the cached data is deleted and reassigned.

[0021] Step 2.2: Transform the data to be written into a data structure, and then assign values ​​to the defined data structure;

[0022] The data structure transformation process is as follows:

[0023] Step 2.2.1: Create a list to store the header data, and create a nested hash table to store the data to be written for each row;

[0024] Step 2.2.2: Traverse the data from the query results in Step 2.1 and store the data in the nested hash table data structure created in Step 2.2.1. Traverse the header data initialized in Step 1.3 and store it in the list created in Step 2.2.1.

[0025] Step 2.3: Data mapping process, which maps the data fields to be written to the table header data fields;

[0026] Data mapping processing includes the following steps:

[0027] Step 2.3.1: Traverse the data to be written, create a list to store the result after data structure transformation, and create a new linked list and hash table to implement LinkedHashMap for each data traversal to store each row of data to be written;

[0028] Step 2.3.2: Store the traversed data objects in a LinkedHashMap. The mapping rule is: the key is the header title of each column, and the value is the data object. Add the LinkedHashMap to the list created in 2.3.1.

[0029] Step 2.3.3: The traversal ends, and the list is returned;

[0030] Step 2.4: Use Easy Excel to generate an Excel file in memory via a file stream, and write the list data returned in Step 2.3.3 into the Excel file.

[0031] Preferably, step 3 includes:

[0032] Step 3.1: Set the worksheet name in the Excel file;

[0033] Step 3.2: Set the cell font size, font type, and bold text in the Excel file;

[0034] Step 3.3: Set the Excel file cell width to auto-adjust.

[0035] Preferably, step 4 includes:

[0036] Step 4.1: Define the path to save the downloaded Excel file;

[0037] Step 4.2: Write the generated Excel file to the local file path, and then close the file stream.

[0038] The system for dynamically generating and exporting Excel files according to the present invention includes:

[0039] Module M1: Defines the data structure for the content of the Excel file to be exported;

[0040] Module M2: Queries the data to be written to the Excel file and writes the data;

[0041] Module M3: Sets the style properties of Excel files;

[0042] Module M4: Downloads the generated Excel file to the local machine.

[0043] Preferably, the module M1 includes:

[0044] Module M1.1: Defines an object whose member variables contain the header of the Excel file and the data to be filled. The data type of the header is a nested list, and the data type of the data to be filled is a nested hash table of lists.

[0045] Module M1.2: Sets the way to get member variable values, set member variable values, and construct the data structure. The constructor is used to declare and initialize the member variables of the data structure, and the default value is set to empty.

[0046] Module M1.3: Initializes header data by assigning the Excel header data to the predefined data structure.

[0047] Preferably, the module M2 includes:

[0048] Module M2.1: During system runtime, the system queries the database for the data to be written to Excel based on the query conditions, and caches the query results and query conditions in the non-relational database Redis. Each query first checks whether the same query conditions exist in Redis. If they exist, the cached query results are retrieved directly from Redis. Otherwise, the database query statement is executed. In subsequent queries with the same conditions, aspect-oriented programming is used to intercept operations that affect the query results. When the operation type is updating data, deleting data, or inserting data, the cached data is deleted and reassigned.

[0049] Module M2.2: Performs data structure transformation on the data to be written, and then assigns values ​​to the defined data structure;

[0050] The data structure transformation process is as follows:

[0051] Module M2.2.1: Creates a list to store header data and a nested hash table to store the data to be written for each row;

[0052] Module M2.2.2: Iterates through the data from the query results of module M2.1 and stores the data in the list-nested hash table data structure created by module M2.2.1. It also iterates through the header data initialized in module M1.3 and stores it in the list created by module M2.2.1.

[0053] Module M2.3: Data mapping processing, which maps the data fields to be written to the table header data fields;

[0054] Data mapping processing includes the following modules:

[0055] Module M2.3.1: Iterates through the data to be written, creates a list to store the results after data structure transformation, and creates a new linked list and hash table to implement LinkedHashMap for each data iteration to store each row of data to be written;

[0056] Module M2.3.2: Stores the traversed data objects into a LinkedHashMap, with the mapping rule being: the key is the header title of each column, and the value is the data object. The LinkedHashMap is then added to the list created in 2.3.1.

[0057] Module M2.3.3: After traversal, return the list;

[0058] Module M2.4: Uses Easy Excel to generate an Excel file in memory via a file stream, and writes the list data returned by module M2.3.3 into the Excel file.

[0059] Preferably, the module M3 includes:

[0060] Module M3.1: Sets the worksheet name in an Excel file;

[0061] Module M3.2: Sets the cell font size, font type, and bold mark in an Excel file;

[0062] Module M3.3: Set Excel file cell width to auto-adjust.

[0063] Preferably, the module M4 includes:

[0064] Module M4.1: Defines the path for downloading and saving Excel files;

[0065] Module M4.2: Writes the generated Excel file to a local file path and then closes the file stream.

[0066] Compared with the prior art, the present invention has the following beneficial effects:

[0067] This invention improves the efficiency of report file generation and export without affecting the normal operation of the system; it is highly scalable and achieves dynamic generation of Excel files by dynamically maintaining the defined data structure. Attached Figure Description

[0068] Other features, objects, and advantages of the present invention will become more apparent from the following detailed description of non-limiting embodiments with reference to the accompanying drawings:

[0069] Figure 1 This is a flowchart of the present invention. Detailed Implementation

[0070] The present invention will now be described in detail with reference to specific embodiments. These embodiments will help those skilled in the art to further understand the present invention, but do not limit the invention in any way. It should be noted that those skilled in the art can make several changes and improvements without departing from the concept of the present invention. These all fall within the protection scope of the present invention.

[0071] Example:

[0072] like Figure 1 This invention provides a method for dynamically generating and exporting Excel files, comprising the following steps:

[0073] Step 1: Define the data structure for the content of the Excel file to be exported;

[0074] Step 2: Query the data that needs to be written to the Excel file and write the data;

[0075] Step 3: Set the style properties of the Excel file;

[0076] Step 4: Download the generated Excel file to your local computer.

[0077] Step 1 includes the following steps:

[0078] Step 1.1: Define an object whose member variables contain the header of the Excel file and the data to be populated. The header data type is a nested list, and the data to be populated is a nested hash table of lists.

[0079] Step 1.2: Set the methods for getting member variable values, setting member variable values, and the constructor for this data structure; the constructor is used to declare and initialize the member variables of the data structure, setting the default value to empty;

[0080] Step 1.3: Initialize the header data by assigning the Excel header data to the data structure defined above.

[0081] Step 2 includes the following steps:

[0082] Step 2.1: During system runtime, the system retrieves the data to be written to Excel from the database based on the query conditions, including store name, date, and region name. The query results and conditions are then cached in a Redis NoSQL database. For each query, the system first checks if the same query conditions exist in Redis. If they do, the cached results are retrieved directly from Redis; otherwise, the database query is executed. In subsequent queries with the same conditions, aspect-oriented programming (AOP) is used to intercept operations that affect the query results. When the operation type is updating, deleting, or inserting data, the cached data is deleted and reassigned to ensure the accuracy of the query results.

[0083] Step 2.2: Transform the data to be written into a data structure, and then assign values ​​to the data structure defined in Step 1;

[0084] The data structure transformation process is as follows:

[0085] Step 2.2.1: Create a list to store the header data, and create a nested hash table to store the data to be written for each row;

[0086] Step 2.2.2: Traverse the data from the query results in Step 2.1 and store the data in the nested hash table data structure created in Step 2.2.1. Traverse the header data initialized in Step 1.3 and store it in the list created in Step 2.2.1.

[0087] Step 2.3: Data mapping process, which maps the data fields to be written to the table header data fields;

[0088] Data mapping processing includes the following steps:

[0089] Step 2.3.1: Iterate through the data to be written, create a list to store the result after the data structure transformation, and create a new LinkedHashMap (a data structure that can store key-value types) for each data iteration to store the data to be written in each row;

[0090] Step 2.3.2: Store the traversed data objects in a LinkedHashMap. The mapping rule is: the key is the header title of each column, and the value is the data object. Add the LinkedHashMap to the list created in 2.3.1.

[0091] Step 2.3.3: The traversal ends, and the list is returned;

[0092] Step 2.4: Write the mapped data to be written and generate an Excel file.

[0093] Step 2.4 includes the following steps:

[0094] Step 2.4.1: Use Easy Excel (a toolkit that supports manipulating Excel files) to generate an Excel file in memory using file streams, and write the list data returned in Step 2.3.3 into the Excel file;

[0095] Step 3 includes the following steps:

[0096] Step 3.1: Set the worksheet name in the Excel file;

[0097] Step 3.2: Set the cell font size, font type, and bold text in the Excel file;

[0098] Step 3.3: Set the Excel file cell width to auto-adjust.

[0099] Step 4 includes the following steps:

[0100] Step 4.1: Define the path to save the downloaded Excel file;

[0101] Step 4.2: Write the generated Excel file to the local file path, and then close the file stream.

[0102] The system for dynamically generating and exporting Excel files according to the present invention includes: module M1: defining a data structure for the content of an Excel file to be exported; module M2: querying the data to be written into the Excel file and writing the data; module M3: setting the style attributes of the Excel file; and module M4: downloading the generated Excel file to the local machine.

[0103] Module M1 includes: Module M1.1: Defines an object whose member variables include the header of the Excel file and the data to be filled. The data type of the header is a nested list, and the data type of the data to be filled is a nested hash table; Module M1.2: Sets the methods for obtaining and setting member variable values ​​and the construction method of the data structure. The construction method is used to declare and initialize the member variables of the data structure, and the default value is set to empty; Module M1.3: Initializes the header data by assigning the Excel header data to the data structure defined above.

[0104] Module M2 includes: Module M2.1: During system runtime, it queries the database for data to be written to Excel based on the query conditions, and caches the query results and query conditions in a non-relational database Redis. For each query, it first checks whether the same query conditions exist in Redis. If they exist, it directly retrieves the cached query results from Redis; otherwise, it executes the database query statement. In subsequent queries with the same conditions, it uses aspect-oriented programming to intercept operations that affect the query results. When the operation type is updating data, deleting data, or inserting data, it deletes the cached data and reassigns the values. Module M2.2: It performs data structure transformation on the data to be written and then assigns values ​​to the defined data structure.

[0105] The data structure transformation process is as follows: Module M2.2.1: Creates a list to store the header data and a nested hash table to store the data to be written for each row; Module M2.2.2: Iterates through the data query results of Module M2.1 and stores the data in the nested hash table data structure created by Module M2.2.1, and iterates through the header data initialized in Module M1.3 and stores it in the list created by Module M2.2.1; Module M2.3: Data mapping processing, mapping the data fields to be written to the header data fields;

[0106] The data mapping process includes the following modules: Module M2.3.1: Iterates through the data to be written, creates a list to store the results after data structure transformation, and creates a new linked list and hash table to implement LinkedHashMap for each data iteration to store the data to be written; Module M2.3.2: Stores the iterated data objects into the LinkedHashMap, with the mapping rule as follows: the key is the header of each column, and the value is the data object. The LinkedHashMap is added to the list created in 2.3.1; Module M2.3.3: The iteration ends and the list is returned; Module M2.4: Uses Easy Excel to generate an Excel file in memory through a file stream, and writes the list data returned by module M2.3.3 into the Excel file.

[0107] Module M3 includes: Module M3.1: Setting the worksheet name of the Excel file; Module M3.2: Setting the cell font size, font type, and bold mark of the Excel file; Module M3.3: Setting the Excel file cell width to auto-adjust. Module M4 includes: Module M4.1: Defining the path for downloading and saving the Excel file; Module M4.2: Writing the generated Excel file to the local file path and then closing the file stream.

[0108] Those skilled in the art will understand that, in addition to implementing the system, apparatus, and their modules provided by this invention in purely computer-readable program code, the same program can be implemented in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers by logically programming the method steps. Therefore, the system, apparatus, and their modules provided by this invention can be considered a hardware component, and the modules included therein for implementing various programs can also be considered structures within the hardware component; alternatively, modules for implementing various functions can be considered both software programs implementing the method and structures within the hardware component.

[0109] Specific embodiments of the present invention have been described above. It should be understood that the present invention is not limited to the specific embodiments described above, and those skilled in the art can make various changes or modifications within the scope of the claims, which do not affect the essence of the present invention. Unless otherwise specified, the embodiments and features described in this application can be arbitrarily combined with each other.

Claims

1. A method for dynamically generating and exporting Excel files, characterized in that, The method comprises the following steps: Step 1: defining a data structure of the content of an Excel file to be exported; Step 2: querying data to be written into the Excel file and performing data writing; Step 3: setting the style attribute of the Excel file; Step 4: downloading the generated Excel file to the local; The step 1 comprises: Step 1.1: defining an object, and the member variables include the table header of the Excel file and the data to be filled, wherein the data type of the table header is a nested list, and the data type of the data to be filled is a list-nested hash table; Step 1.2: setting the ways of obtaining and setting the values of the member variables of the data structure and the construction mode, and the construction mode is used to declare and initialize the member variables of the data structure, and the default value is set to be empty; The step 2 comprises: Step 2.1: during system running, querying the data to be written into the Excel file from the database according to a query condition, and caching the query result data and the query condition to a non-relational database Redis, and each time the query is performed, it is judged whether the same query condition exists in the Redis, if the same query condition exists, the cached query result is directly taken out from the Redis, otherwise, a database query statement is executed, and in the subsequent same query condition, an aspect-oriented programming technology is intercepted to affect the operation of the query result, and when the operation type is updating data, deleting data or inserting data, the cached data is deleted and re-assigned; Step 2.2: converting the data to be written into a data structure, and then assigning values to the defined data structure; The data structure conversion process is as follows: Step 2.2.1: creating a list for storing the table header data, and creating a list-nested hash table for storing the data to be written in each row; Step 2.2.2: traversing the data of the query result in step 2.1, and storing the data into the data structure of the list-nested hash table created in step 2.2.1, and traversing the table header data initialized in step 1.3 and storing the data into the list created in step 2.2.1; Step 2.3: data mapping processing is performed on the data field to be written and the table header data field; Step 2.4: an Excel file is generated in the memory by using Easy Excel through a file stream. The data mapping processing comprises the following steps:

2. The method for dynamically generating Excel files and exports of claim 1, wherein, Step 2.3.1: traversing the data to be written, creating a list to store the converted result, and creating a new linked hash map for storing the data to be written in each row during each traversal; Step 2.3.2: storing the traversed data object into the linked hash map, and the mapping rule is that the key is the table header title of each column, and the value is the data object, and the linked hash map is added to the list created in step 2.3.1; Step 2.3.3: returning the list after the traversal is completed; The list data returned in step 2.3.3 is written into the Excel file. The step 3 comprises:

3. The method for dynamically generating Excel files and exports of claim 1, wherein, ​ Step 3.1: Set the worksheet name of the Excel file; Step 3.2: Set the cell font size, font type, and bold flag of the Excel file; Step 3.3: Set the cell width of the Excel file to adapt.

4. The method for dynamically generating Excel files and exports of claim 1, wherein, The step 4 includes: Step 4.1: Define the path for downloading and saving the Excel file; Step 4.2: Write the generated Excel file to the local file path, and then close the file stream.

5. A system for dynamically generating and exporting Excel files, the system comprising: It includes: Module M1: define a data structure of the content of the Excel file to be exported; Module M2: query the data to be written into the Excel file and perform data writing; Module M3: set the style properties of the Excel file; Module M4: download the generated Excel file to the local; The module M1 includes: Module M1.1: define an object, and the member variables include the table header of the Excel file and the data to be filled, the data type of the table header is a nested list, and the data type of the data to be filled is a list nested hash table; Module M1.2: set the way to get and set the value of the member variable of the data structure and the construction method, and the construction method is used to declare and initialize the member variable of the data structure with default value set to empty; Module M1.3: initialize the table header data, and assign the Excel table header data to the above defined data structure; The module M2 includes: Module M2.1: when the system runs, query the data to be written into the Excel according to the query condition, and cache the query result data and the query condition to the non-relational database Redis, and each time query whether the same query condition exists in the Redis, if exists, directly take out the cached query result from the Redis, otherwise execute the database query statement, and in the subsequent same query condition, intercept the operation affecting the query result through aspect-oriented programming technology, and when the operation type is updating data, deleting data, and inserting data, delete and reassign the cached data; Module M2.2: convert the data to be written into a data structure, and then assign the defined data structure; The data structure conversion process is as follows: Module M2.2.1: create a list to store the table header data, and create a list nested hash table to store the data to be written in each row; Module M2.2.2: traverse the data of the query result of module M2.1, and store the data in the data structure of the list nested hash table created by module M2.2.1, and traverse the table header data initialized in module M1.3 and store them in the list created by module M2.2.1; Module M2.3: data mapping processing, mapping the data fields to be written with the table header data fields; Module M2.4: generate an Excel file in memory using Easy Excel through file stream.

6. The system for dynamically generating and exporting an Excel file according to claim 5, wherein The data mapping processing includes the following modules: Module M2.3.1: Traverse the data to be written, create a list to store the converted results of the data structure, and create a new linked list and hash table implementation LinkedHashMap to store the data of each row written each time the data is traversed; Module M2.3.2: Store the traversed data object in LinkedHashMap, the mapping rule is: the key is the table header title of each column, and the value is the data object. Add LinkedHashMap to the list created in module 2.3.1; Module M2.3.3: Traverse ends, return the list; Write the list data returned by module M2.3.3 into the Excel file.

7. The system for dynamically generating Excel files and exports of claim 5, wherein, The module M3 includes: Module M3.1: Set the worksheet name of the Excel file; Module M3.2: Set the font size, font type, and bold flag of the Excel file cell; Module M3.3: Set the Excel file cell width to adapt.

8. The system for dynamically generating Excel files and exports of claim 5, wherein, The module M4 includes: Module M4.1: Define the path for downloading and saving the Excel file; Module M4.2: Write the generated Excel file to the local file path, and then close the file stream.

Images