Excel method and system for exporting large-scale data
Through asynchronous processing of message queues, paging query and data compression technology, combined with EasyExcel framework and WebSocket notification, the problems of memory overflow and performance bottlenecks in traditional data export methods are solved, and efficient and flexible large-scale data export and user experience improvement are achieved.
Patent Information
- Application Number
- CN202411118488.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-15
- Publication Date
- 2024-11-29
AI Technical Summary
Traditional data export methods tend to lead to memory overflow and extended system response time when processing large-scale data, and existing improved solutions such as item-by-item reading and paging processing have performance and flexibility limitations.
Message queue (MQ) is used for asynchronous processing, combined with paging query technology and data compression, and line-by-line data processing is used using EasyExcel framework, and real-time notification and file management are implemented through WebSocket, supporting a variety of compression algorithms and permission management.
It significantly improves the speed and user experience of data export, avoids memory overflow and system crashes, and realizes flexible data export operations and efficient file management.
Smart Images

Figure CN119046355A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data analysis and export processing, and in particular to a large-scale data export Excel method and system. Background Art
[0002] With the rapid development of information technology, data processing and analysis capabilities play an increasingly important role in all walks of life. In many application scenarios, users need to export large amounts of data into Excel files for subsequent data analysis, report making, or data sharing. However, when the amount of data reaches millions or even larger, traditional data export methods often face performance bottlenecks and memory limitations.
[0003] Traditional data export methods usually load all data into memory at once and then write it to an Excel file. This method works well when processing small-scale data, but when the amount of data increases, it consumes a lot of memory resources and even causes memory overflow, making it impossible to complete the export task. In addition, due to processing a large amount of data at one time, the system response time will also increase significantly, and the user experience will decrease.
[0004] To solve these problems, researchers and developers have proposed some improvement solutions, such as using database cursors to read data one by one, paging data before exporting, etc. These solutions have improved the efficiency and stability of export to a certain extent, but there are still some limitations. For example, although the method of reading data one by one reduces memory usage, it increases the number of interactions with the database and reduces overall performance; the paging method requires users to manually set the paging size, which is cumbersome and inflexible. Summary of the invention
[0005] The purpose of the present invention is to provide a method and system for exporting large-scale data to Excel to solve the problems raised in the above background technology.
[0006] To achieve the above object, the present invention provides the following technical solution: a method for exporting large-scale data to Excel, the method comprising the following steps:
[0007] The user triggers a data export request on the front-end interface;
[0008] The system responds to the request, generates a message containing the export request information, and sends the message to the message queue MQ server;
[0009] A dedicated MQ consumer listens to and consumes messages in MQ, and loads the data to be exported from the database in batches according to the request information;
[0010] Use paging query technology to dynamically calculate the starting position of each page of data and perform paging query operations from the database to reduce memory usage and improve query efficiency;
[0011] Use the EasyExcel framework to convert the queried data into Excel file format, and use data compression technology to reduce file size and improve transmission and storage efficiency in the process;
[0012] Upload the generated Excel file to the object storage service OOS and obtain the access path of the file;
[0013] The file access path is saved in the database so that users can remotely access and download files through a browser.
[0014] Preferably, the method further comprises the following steps:
[0015] MQ consumers have a failure retry mechanism when processing export tasks. When the export task fails due to network problems or database failures, the MQ server automatically triggers the retry logic to ensure that the task is eventually completed.
[0016] Preferably, the implementation of the data compression technology includes:
[0017] Before writing the data into the Excel file, the data is compressed according to the compression algorithm and parameters set by the user;
[0018] Supports multiple compression algorithms, including but not limited to lossless compression technologies such as Huffman coding and LZW coding, to meet different data characteristics and export requirements.
[0019] Preferably, it also includes a real-time push notification function, specifically:
[0020] Use WebSocket technology to establish a real-time connection between the client and the server;
[0021] When the data export task is completed, the server sends a notification message containing the export results and the file download link to the client via WebSocket;
[0022] After receiving the message, the client displays the notification content on the user interface and allows the user to directly click the link to download the file.
[0023] Preferably, the following file management and security measures are also included:
[0024] The system supports file version control to ensure that the historical versions of files can be traced;
[0025] Manage access rights to files to ensure that only authorized users can access and download files;
[0026] Associate the file access path with the user identity information to perform identity authentication and permission verification when accessing the file.
[0027] A large-scale data export Excel system includes a Web service client, an MQ message queue and a server. The system receives all export requests from users through the Web service client, generates an MQ message containing export request information and sends it to the MQ server; the MQ server stores and distributes the message to the MQ consumer, and the MQ consumer asynchronously executes the data export task, thereby significantly improving the system response speed and ensuring that the user experience is not affected.
[0028] Preferably, when executing data export tasks, MQ consumers use paging query technology to load the data to be exported from the database in batches to avoid memory overflow problems, and use a failure retry mechanism to ensure that the export task can be automatically retried when encountering network problems or database failures until the task is completed.
[0029] Preferably, during the data export process, the system uses the EasyExcel framework to parse and generate data, and adopts the method of reading data line by line and writing it into Excel files one by one, which effectively reduces memory usage and supports the output of multiple Excel versions and formats to meet the different needs of users.
[0030] Preferably, the system introduces data compression technology before exporting data to Excel files, compresses the data according to the compression algorithm and parameters set by the user, and compresses the Excel file as a whole before uploading the file to the OOS object storage service to reduce the file size and improve transmission efficiency and storage efficiency. At the same time, different compression algorithms and parameters are provided to meet diverse export requirements.
[0031] Preferably, it also includes a WebSocket push notification function. When the data export task is completed, a notification message containing the export results and the file download link is sent to the client with which the connection has been established via WebSocket, so as to realize the function of informing the user of the data export results in real time and providing the download link, thereby improving the user experience and reducing the frequency and cost of the user's query on the export results.
[0032] Compared with the prior art, the present invention has the following beneficial effects:
[0033] The large-scale data export Excel method and system proposed by the present invention optimize the data processing flow, reduce unnecessary memory usage and the number of database interactions, thereby significantly improving the speed of data export; dynamically adjust the allocation and release of memory resources by introducing an intelligent memory management mechanism, effectively preventing problems such as memory overflow and system crash; adopt a data block export strategy, allow users to set the size and number of data blocks according to actual needs, and realize flexible data export operations; and improve user satisfaction and convenience when using the present invention to export data by optimizing system response time and the format of the export file. BRIEF DESCRIPTION OF THE DRAWINGS
[0034] Figure 1 It is a flow chart of the principle of data export of the present invention;
[0035] Figure 2 This is a system architecture diagram of the present invention;
[0036] Figure 3 The figure is a flow chart of the method of the present invention. DETAILED DESCRIPTION
[0037] In order to make the purpose and technical solution of the present invention clearly and completely described, and the advantages more clearly understood, the embodiments of the present invention are further described in detail with reference to the accompanying drawings. It should be understood that the specific embodiments described herein are part of the embodiments of the present invention, rather than all of the embodiments, and are only used to explain the embodiments of the present invention, and are not used to limit the embodiments of the present invention. All other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0038] For example, see Figure 3 The present invention provides a technical solution: a method for exporting large-scale data to Excel, the method comprising the following steps:
[0039] The user triggers a data export request on the front-end interface;
[0040] The system responds to the request, generates a message containing the export request information, and sends the message to the message queue MQ server;
[0041] A dedicated MQ consumer listens to and consumes messages in MQ, and loads the data to be exported from the database in batches according to the request information;
[0042] Use paging query technology to dynamically calculate the starting position of each page of data and perform paging query operations from the database to reduce memory usage and improve query efficiency;
[0043] Use the EasyExcel framework to convert the queried data into Excel file format, and use data compression technology to reduce file size and improve transmission and storage efficiency in the process; before writing data into the Excel file, compress the data according to the compression algorithm and parameters set by the user;
[0044] Supports multiple compression algorithms, including but not limited to lossless compression technologies such as Huffman coding and LZW coding, to meet different data characteristics and export requirements;
[0045] Upload the generated Excel file to the object storage service OOS and obtain the access path of the file;
[0046] Save the file access path to the database so that users can remotely access and download files through a browser;
[0047] MQ consumers have a failure retry mechanism when processing export tasks. When an export task fails due to network problems or database failures, the MQ server automatically triggers the retry logic to ensure that the task is eventually completed.
[0048] It also includes real-time push notification capabilities, specifically:
[0049] Use WebSocket technology to establish a real-time connection between the client and the server;
[0050] When the data export task is completed, the server sends a notification message containing the export results and the file download link to the client via WebSocket;
[0051] After receiving the message, the client displays the notification content on the user interface and allows the user to directly click the link to download the file;
[0052] The following file management and security measures are also included:
[0053] The system supports file version control to ensure that the historical versions of files can be traced;
[0054] Manage access rights to files to ensure that only authorized users can access and download files;
[0055] Associate the file access path with the user identity information to perform identity authentication and permission verification when accessing the file.
[0056] Embodiment 2, based on embodiment 1, refers to the attached Figure 1 to Figure 2 , proposed a large-scale data export Excel system, including three modules: Web service client, MQ message queue, and server. The specific solution details are as follows:
[0057] In the scenario of large-scale data export, directly processing user requests synchronously will cause the system response time to be prolonged, affecting the user experience. To this end, the present invention adopts MQ (message queue) to implement an asynchronous processing mechanism. When the user clicks the "Export All" button, the system generates an MQ message containing the export request information and sends it to the MQ server. Subsequently, a special MQ consumer is responsible for listening to and consuming the message and executing the data export task. This asynchronous processing method not only improves the system response speed, but also ensures that the export task is stably executed in the back end without being affected by the front-end user operation.
[0058] When processing an export task, the MQ consumer will first obtain the data to be exported from the database according to the request information. In order to avoid memory overflow caused by excessive data volume, the data is loaded into the memory in batches for processing. At the same time, the MQ consumer also has a failure retry mechanism. When the export task fails due to network problems, database failures, etc., the MQ server will automatically trigger the retry logic to ensure that the task is finally completed.
[0059] In order to reduce the impact of a large amount of data loaded at one time on system performance, the present invention adopts paging query technology. In specific implementation, the system will calculate the total number of pages to be queried according to the export parameters set by the user (such as the number of records per page, export range, etc.), and perform paging query operations in the database through ORM frameworks such as MyBatis. During the query process, the system will dynamically calculate the starting position of each page of data (i.e., the offset parameter in the limit statement) to ensure that the data within the specified range can be accurately obtained. The application of paging query technology not only improves system performance, but also reduces memory usage, ensuring the stability and reliability of data export.
[0060] In the process of data export, it is necessary to convert the data in the database into Excel file format. Traditional frameworks such as ApachePOI have the problem of high memory usage when processing large-scale data. For this reason, the present invention adopts the EasyExcel framework for data parsing and generation. EasyExcel greatly reduces memory usage by reading data line by line and writing Excel files one by one. At the same time, it also supports multiple Excel versions and formats, which is convenient for users to choose according to their needs.
[0061] In the process of data export, in order to further reduce the file size, improve transmission efficiency and reduce storage costs, the present invention introduces data compression technology. According to the characteristics of the data and the export requirements, different compression algorithms can be selected for compression. For scenarios requiring data integrity and no distortion, lossless compression technology such as Huffman coding, LZW coding, etc. can be used. These technologies achieve compression by reducing the redundancy and repetition in the data, while ensuring that the decompressed data is completely consistent with the original data.
[0062] In the present invention, data compression technology is performed before the data is exported to the Excel file. First, the system compresses the data according to the compression algorithm and parameters set by the user. Then, the compressed data is written into the Excel file. Before the file is uploaded to OOS, the system also compresses the Excel file as a whole to further reduce the file size. By introducing data compression technology, not only can the size of the exported file be reduced, the transmission efficiency and storage efficiency be improved, but also the processing capacity and response speed of the system can be improved to a certain extent. At the same time, users can also select different compression algorithms and parameters as needed to meet different export requirements.
[0063] After the data export is completed, the system needs to save the generated Excel file to the file server for users to download. In order to save the disk resources of the application server and improve the efficiency of file access, the present invention adopts OOS (object storage service) for file management. The system will upload the generated Excel file to OOS and obtain the access path of the file (such as URL address). Subsequently, the system will save the path to the database so that users can access and download it remotely through the browser. In addition, the system also supports functions such as version control and access permission management of files, ensuring the security and traceability of files.
[0064] In order to inform users of data export results in real time and provide download links, the present invention uses WebSocket technology to implement push notification function. When the data export task is completed, the system will send a notification message containing export results and file download links to the client that has established a WebSocket connection. After receiving the message, the client will display the notification content in real time on the user interface and allow the user to directly click on the link to download the file. This real-time push notification method not only improves the user experience, but also reduces the frequency and cost of users querying export results.
[0065] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.
Claims
1. A method for exporting large-scale data to Excel. Features: The method comprises the following steps: The user triggers a data export request on the front-end interface; The system responds to the request, generates a message containing the export request information, and sends the message to the message queue MQ server; A dedicated MQ consumer listens to and consumes messages in MQ, and loads the data to be exported from the database in batches according to the request information; Use paging query technology to dynamically calculate the starting position of each page of data and perform paging query operations from the database to reduce memory usage and improve query efficiency; Use the EasyExcel framework to convert the queried data into Excel file format, and use data compression technology to reduce file size and improve transmission and storage efficiency in the process; Upload the generated Excel file to the object storage service OOS and obtain the access path of the file; The file access path is saved in the database so that users can remotely access and download files through a browser.
2. A method for exporting large-scale data to Excel according to claim 1, Features: The following steps are also included: MQ consumers have a failure retry mechanism when processing export tasks. When the export task fails due to network problems or database failures, the MQ server automatically triggers the retry logic to ensure that the task is eventually completed.
3. A method for exporting large-scale data to Excel according to claim 1, Features: The implementation of data compression technology includes: Before writing the data into the Excel file, the data is compressed according to the compression algorithm and parameters set by the user; Supports multiple compression algorithms, including but not limited to lossless compression technologies such as Huffman coding and LZW coding, to meet different data characteristics and export requirements.
4. A method for exporting large-scale data to Excel according to claim 1, Features: It also includes real-time push notification capabilities, specifically: Use WebSocket technology to establish a real-time connection between the client and the server; When the data export task is completed, the server sends a notification message containing the export results and the file download link to the client via WebSocket; After receiving the message, the client displays the notification content on the user interface and allows the user to directly click the link to download the file.
5. The method for exporting large-scale data to Excel according to claim 1, Features: The following file management and security measures are also included: The system supports file version control to ensure that the historical versions of files can be traced; Manage access rights to files to ensure that only authorized users can access and download files; Associate the file access path with the user identity information to perform identity authentication and permission verification when accessing the file.
6. A large-scale data export Excel system according to any one of claims 1 to 5, comprising a Web service client, an MQ message queue and a server, Features: The system receives all export requests from users through the Web service client, generates an MQ message containing the export request information and sends it to the MQ server; the MQ server stores and distributes the message to the MQ consumer, and the MQ consumer executes the data export task asynchronously, thereby significantly improving the system response speed and ensuring that the user experience is not affected.
7. A large-scale data export Excel system according to claim 6, Features: When executing data export tasks, MQ consumers use paging query technology to load the data to be exported from the database in batches to avoid memory overflow problems, and use a failure retry mechanism to ensure that the export task can be automatically retried when encountering network problems or database failures until the task is completed.
8. A large-scale data export Excel system according to claim 6, Features: During the data export process, the system uses the EasyExcel framework to parse and generate data, and adopts the method of reading data line by line and writing them into Excel files one by one, which effectively reduces memory usage and supports the output of multiple Excel versions and formats to meet the different needs of users.
9. A large-scale data export Excel system according to claim 6, Features: Before exporting data to Excel files, the system introduces data compression technology to compress data according to the compression algorithm and parameters set by the user, and compresses the Excel file as a whole before uploading it to the OOS object storage service to reduce file size and improve transmission and storage efficiency. At the same time, different compression algorithms and parameters are provided to meet diverse export requirements.
10. A large-scale data export Excel system according to claim 6, Features: It also includes a WebSocket push notification function. When the data export task is completed, a notification message containing the export results and file download link is sent to the connected client via WebSocket, so that users can be informed of the data export results in real time and a download link is provided, thereby improving the user experience and reducing the frequency and cost of users querying the export results.
Citation Information
Patent Citations
Large-data-volume Excel file exporting method and device, computer equipment and storage medium
CN111104448A
Asynchronous file exporting method and system based on message queue and fast DFS micro-service architecture
CN111405020A
Data exporting method and device, computer equipment and storage medium
CN114398441A
Data exporting method, data importing method and data exporting and importing system
CN116795913A
Excel exporting method for large data volume and paging query and cloud storage method
CN117573748A
