EXCEL export method and system for portal resource application file downloading

Abstract

The invention relates to the technical field of software application, and particularly provides an EXCEL export method and system for portal resource application file downloading, and the method comprises the following steps: S1, a user clicks a downloading file generation button on a portal application resource file downloading page; s2, background asynchronous processing is carried out, data are written into Excel in a paging mode through multiple threads, and the task state is updated to be executed; s3, the Excel file is stored in a server, and the task state is updated to be completed; and S4, finally, notifying a portal to apply for a resource file downloading page through WebSocket to automatically refresh the generated downloadable file list, and clicking a downloading button to directly download the generated EXCEL file from the server. Compared with the prior art, high-performance export can be realized, the waiting time of the user is shortened, and the satisfaction degree of the user is improved.

Description

TECHNICAL FIELD

[0001] The application relates to the technical field of software application, and particularly provides an EXCEL export method and system for portal resource application file downloading. BACKGROUND

[0002] At present, the demand for Excel data export often occurs in work, but performance and memory problems may be encountered.

[0003] When developing a data export function, the HSSF / XSSF component is habitually used. When the data amount exceeds 50,000 rows, the performance of the scheme obviously drops. The root cause lies in the design defect of the memory object model: each Cell object occupies about 1 KB of memory, and the million-level data directly leads to the explosion of the JVM heap memory.

[0004] The following writing method is currently commonly used:

[0005] Workbook workbook = new XSSFWorkbook();

[0006] Sheet sheet = workbook.createSheet();

[0007] for (int i = 0; i <1000000>; i++) {

[0008] Row row = sheet.createRow(i); / / a Row object is generated for each row

[0009] row.createCell (0).setCellValue ("data" + i); / / each Cell is independently stored

[0010] The writing method generates about 1 million Row objects and 10 million Cell objects (assuming 10 columns per row), which directly leads to the memory occupation of more than 1 GB. More fatally, frequent full GC can cause system lag and even OOM crash. SUMMARY

[0011] The application provides an EXCEL export method for portal resource application file downloading, which has high practicability and solves the problems of the prior art.

[0012] The application further provides an EXCEL export system for portal resource application file downloading, which is reasonable in design and safe and suitable for use.

[0013] The application adopts the technical scheme that:

[0014] An EXCEL export method for downloading portal resource application files, comprising the following steps:

[0015] S1, a user clicks a download file generation button on a portal resource application file download page;

[0016] S2, asynchronous processing in the background, using multi-threading to implement paging to write data into Excel, and updating the task status to be in execution;

[0017] S3, storing the Excel file on the server and updating the task status to be completed;

[0018] S4, finally, notifying the portal resource application file download page to automatically refresh the generated downloadable file list through WebSocket, and clicking the download button to directly download the generated EXCEL file from the server.

[0019] Further, in step S1, a backend interface is called, a download file generation task record is created, a unique task ID is generated, and the task status is pending execution.

[0020] Further, in step S2, it includes:

[0021] S2-1, using a cursor to query all data to be exported, and only querying the required fields;

[0022] S2-2, determining the export scheme according to the data volume;

[0023] S2-3, calculating the number of shards according to the data volume of each subtask configured;

[0024] S2-4, processing shards in parallel;

[0025] S2-5, merging files.

[0026] Further, in step S2-2, it includes:

[0027] (1) less than 50,000, directly using the traditional POI scheme HSSF / XSSF component implementation;

[0028] (2) 50,000 to 500,000, using the SXSSFWorkbook class implementation;

[0029] (3) more than 500,000, using EasyExcel or FastExcel implementation, and must be processed by sharding.

[0030] An Excel export system for downloading portal resource application files: First, the user clicks the "Generate Download File" button on the portal resource application file download page. Then, the background processes the data asynchronously, using multi-threading to implement pagination and write the data into Excel, and updates the task status to "Executing".

[0031] Then, the Excel file is stored on the server, and the task status is updated to "completed".

[0032] Finally, the WebSocket notification portal automatically refreshes the list of downloadable files, allowing users to click the download button to download the generated Excel file directly from the server.

[0033] Furthermore, when users click the "Generate Download File" button on the resource file download application page of the user portal, the backend interface is called to create a new download file generation task record, generating a unique task ID, and the task status is set to "Pending Execution".

[0034] Furthermore, the update task status being "in execution" includes:

[0035] (1) Use a cursor to query all the data to be exported, and only query the fields that are needed;

[0036] (2) Determine the export method to use based on the amount of data;

[0037] (3) Calculate the number of shards based on the data volume of each subtask configured;

[0038] (4) Parallel processing of fragments;

[0039] (5) Merge files.

[0040] Furthermore, step (2) includes:

[0041] (2-1) If the number is less than 50,000, the traditional POI solution's HSSF / XSSF components can be used directly.

[0042] (2-2) From 50,000 to 500,000, the SXSSFWorkbook class can be used to achieve this.

[0043] (2-3) For numbers exceeding 500,000, use EasyExcel or FastExcel and perform split processing.

[0044] Compared with existing technologies, the Excel export method and system for downloading portal resource application documents of the present invention have the following outstanding advantages:

[0045] This invention optimizes data query and export tools, reduces time complexity, avoids system lag and OOM crashes, uses different Excel export strategies based on different data volumes, automatically loads data in batches, eliminates duplicate object creation through reflection caching, and avoids memory fragmentation through built-in style optimization strategies, achieving high-performance export, reducing user waiting time, and improving user satisfaction. Attached Figure Description

[0046] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0047] Figure 1 This is a flowchart illustrating an Excel export method for downloading portal resource application files. Detailed Implementation

[0048] To enable those skilled in the art to better understand the present invention, the present invention will be further described in detail below with reference to specific embodiments. Obviously, the described embodiments are merely some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0049] The following is a preferred embodiment:

[0050] like Figure 1 As shown, this embodiment describes an EXCEL export method for downloading portal resource application documents, which includes the following steps:

[0051] S1. On the user portal's resource file download page, click the "Generate Download File" button;

[0052] Call the backend interface to create a new download file and generate a task record, generating a unique task ID, and setting the task status to pending execution.

[0053] S2. Background asynchronous processing: Use multi-threading to implement pagination, write data to Excel, and update the task status to "in execution".

[0054] S2-1. Use a cursor to query all the data to be exported, and only query the fields that are needed;

[0055] S2-2. Determine the export method to use based on the amount of data;

[0056] include:

[0057] (1) For numbers less than 50,000, the traditional POI solution's HSSF / XSSF components can be used directly.

[0058] (2) For amounts ranging from 50,000 to 500,000, the SXSSFWorkbook class can be used.

[0059] (3) For more than 500,000, use EasyExcel or FastExcel and it must be split into chunks.

[0060] S2-3. Calculate the number of shards based on the data volume of each subtask as configured.

[0061] S2-4, Parallel processing of fragments;

[0062] S2-5, Merge files.

[0063] S3. Store the Excel file on the server and update the task status to complete.

[0064] S4. Finally, notify the portal via WebSocket that the resource file download page will automatically refresh the list of downloadable files. Click the download button to download the generated Excel file directly from the server.

[0065] By optimizing data query and export tools and maintaining a balance between memory and disk, high-performance export is achieved, reducing the waiting time for users after clicking the "Generate Download File" button on the portal's resource file application page, and improving user satisfaction.

[0066] To solve the deep pagination problem, a cursor approach is used to leverage the ordered nature of the index, reducing the time complexity from O(N^2) to O(N^2). 2 Reduce the time complexity to O(N) to avoid the performance avalanche caused by the LIMIT clause in traditional SELECT queries, which slows down progressively. Reduce the number of fields queried, only querying the fields that need to be exported, thus reducing query time and network traffic. Use dedicated connection pools for export operations whenever possible to avoid impacting other functions; the maximum number of threads should be equal to twice the number of CPU cores plus the number of disks.

[0067] The SXSSFWorkbook class can be used to keep only 1000 rows of data in memory. A sliding window mechanism can then be used to write processed data to a temporary disk file, keeping only the current batch in memory. Alternatively, EasyExcel or FastExcel, using an event-driven model and object reuse pooling, can keep memory usage below 50MB when exporting millions of data points.

[0068] By using multi-threaded asynchronous data processing, the task is broken down into multiple subtasks and executed in parallel through a divide-and-conquer strategy, and the use of threads ensures resource control.

[0069] Based on the above method, in this embodiment, an EXCEL export system for downloading portal resource application files first allows users to click the "Generate Download File" button on the portal resource application file download page. The background processes the data asynchronously, using multi-threading to implement pagination and write the data into Excel, and then updates the task status to "Executing".

[0070] Then, the Excel file is stored on the server, and the task status is updated to "completed".

[0071] Finally, the WebSocket notification portal automatically refreshes the list of downloadable files, allowing users to click the download button to download the generated Excel file directly from the server.

[0072] In this process, when users click the "Generate Download File" button on the resource file download application page of the user portal, the backend interface is called to create a new download file generation task record, generate a unique task ID, and the task status is "Pending Execution".

[0073] Update the task status to running, including:

[0074] (1) Use a cursor to query all the data to be exported, and only query the fields that are needed;

[0075] (2) Determine the export method to use based on the amount of data;

[0076] (3) Calculate the number of shards based on the data volume of each subtask configured;

[0077] (4) Parallel processing of fragments;

[0078] (5) Merge files.

[0079] Step (2) includes:

[0080] (2-1) If the number is less than 50,000, the traditional POI solution's HSSF / XSSF components can be used directly.

[0081] (2-2) From 50,000 to 500,000, the SXSSFWorkbook class can be used to achieve this.

[0082] (2-3) For numbers exceeding 500,000, use EasyExcel or FastExcel and perform split processing.

[0083] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for exporting Excel files from a portal resource application document download, characterized in that, It has the following steps: S1. On the user portal's resource file download page, click the "Generate Download File" button; S2. Background asynchronous processing: Use multi-threading to implement pagination, write data to Excel, and update the task status to "in execution". S3. Store the Excel file on the server and update the task status to complete. S4. Finally, notify the portal via WebSocket that the resource file download page will automatically refresh the list of downloadable files. Click the download button to download the generated Excel file directly from the server.

2. The method for exporting an Excel file from a portal resource application file according to claim 1, characterized in that, In step S1, the backend interface is called to create a new download file generation task record, generate a unique task ID, and set the task status to pending execution.

3. The method for exporting an Excel file from a portal resource application file according to claim 2, characterized in that, Step S2 includes: S2-1. Use a cursor to query all the data to be exported, and only query the fields that are needed; S2-2. Determine the export method to use based on the amount of data; S2-3. Calculate the number of shards based on the data volume of each subtask as configured. S2-4, Parallel processing of fragments; S2-5, Merge files.

4. The method for exporting an Excel file from a portal resource application file according to claim 3, characterized in that, Step S2-2 includes: (1) For numbers less than 50,000, the traditional POI solution's HSSF / XSSF components can be used directly. (2) For amounts ranging from 50,000 to 500,000, the SXSSFWorkbook class can be used. (3) For more than 500,000, use EasyExcel or FastExcel and it must be split into chunks.

5. An Excel export system for downloading portal resource application documents, characterized in that, First, when users click the "Generate Download File" button on the resource file download page of the user portal, the background processes the data asynchronously, using multi-threading to implement pagination and write the data to Excel, and then updates the task status to "Executing". Then, the Excel file is stored on the server, and the task status is updated to "completed". Finally, the WebSocket notification portal automatically refreshes the list of downloadable files, allowing users to click the download button to download the generated Excel file directly from the server.

6. The EXCEL export system for downloading portal resource application documents according to claim 5, characterized in that, Clicking the "Generate Download File" button on the user portal's resource file download page will call the backend interface to create a new download file generation task record, generating a unique task ID, and setting the task status to "Pending Execution".

7. An EXCEL export system for downloading portal resource application documents according to claim 6, characterized in that, The update task is in the process of being executed, including: (1) Use a cursor to query all the data to be exported, and only query the fields that are needed; (2) Determine the export method to use based on the amount of data; (3) Calculate the number of shards based on the data volume of each subtask configured; (4) Parallel processing of fragments; (5) Merge files.

8. An EXCEL export system for downloading portal resource application documents according to claim 7, characterized in that, Step (2) includes: (2-1) If the number is less than 50,000, the traditional POI solution's HSSF / XSSF components can be used directly. (2-2) From 50,000 to 500,000, the SXSSFWorkbook class can be used to achieve this. (2-3) For numbers exceeding 500,000, use EasyExcel or FastExcel and perform split processing.

Images