Rapid data exporting method and system and electronic equipment

Abstract

The invention discloses a quick data exporting method, which comprises the following steps of: configuring data reading state parameters which comprise a single data volume threshold value; performing data volume judgment on the to-be-read data to obtain a data volume judgment result; reading the to-be-read data according to the data volume judgment result, and writing the to-be-read data into an excel file; and completing the analysis of the excel file, and compressing the excel file to complete the export of a huge amount of data. According to the technical scheme, compared with the prior art, the stability of reading and exporting a huge amount of data can be improved, the memory pressure can be reduced, and memory overflow can be avoided. In addition, the invention also relates to a technical scheme of a data rapid export system and electronic equipment, which also have the above beneficial effects.

Description

Technical Field

[0001] The present application relates to the technical field of data storage and reading, and more specifically, to a method for rapid data export. In addition, the present application also relates to a rapid data export system and electronic equipment. Background Art

[0002] With the rapid development of the Internet era, offline businesses are gradually shifting to online application software system management. In online application software systems, the amount of data for the same business is increasing in magnitude, and export is a common function of the management system. When the amount of data is increasing in magnitude, there is an increasing demand for quickly satisfying customers' needs to export data for corresponding data. When the amount of data is particularly large, memory overflow and page freeze will occur.

[0003] In the prior art, developers need to manually write the code for data storage and reading each time they develop different software. There are still problems with the POI operation mode, such as excessive memory, empty loops, and memory overflow caused by integer division. Every time a field is modified or the style has a small change, and due to the uneven level of developers, the code quality is often difficult to control, which makes the subsequent maintenance of the project very troublesome.

[0004] Therefore, how to provide a method for quickly exporting data that can improve the stability of reading and exporting massive amounts of data, reduce memory pressure, and avoid memory overflow has become a technical problem that needs to be urgently solved by those skilled in the art. Summary of the invention

[0005] In order to solve the above technical problems, the present application provides a method for rapid data export, which can improve the stability of massive data reading and exporting, reduce memory pressure, and avoid memory overflow. In addition, the present application also relates to a system and electronic device for rapid data export, which also have the above beneficial effects.

[0006] The purpose of this technical solution is to cache data in a lightweight and batched manner, save the cached data to files to solve the problem of downloading large files, and control the parsing memory usage to the KB level through the template method mode, so that it only takes 20 to 30 seconds to read millions of data. From the development level, the repetitive logic processing process is universally developed to reduce repeated coding, allowing developers to focus on the logic of the business itself and improve development efficiency.

[0007] The technical solutions provided by this application are as follows:

[0008] The present application provides a technical solution for a method for rapid data export. The method comprises the following steps: configuring a read data state parameter, wherein the read data state parameter comprises: a single data volume threshold; performing a data volume judgment on the data to be read and obtaining a data volume judgment result; reading the data to be read according to the data volume judgment result and writing it into an Excel file; completing the Excel file parsing, performing Excel file compression, and completing the massive data export.

[0009] Furthermore, in a preferred embodiment of the present invention, the "judging the data volume of the data to be read and obtaining a data volume judgment result" specifically includes the following steps: numerically comparing the data volume of the data to be read with the single data volume threshold to obtain the data volume judgment result; the data volume judgment result includes a first result and a second result; the first result is that the data volume of the data to be read is less than or equal to the single data volume threshold; the second result is that the data volume of the data to be read is greater than the single data volume threshold.

[0010] Furthermore, in a preferred embodiment of the present invention, the step of “reading the data to be read and writing them into an Excel file according to the data amount judgment result” specifically includes the following steps: if the data amount judgment result is a first result, reading the data to be read and writing them into a single worksheet of an Excel file; if the data amount judgment result is a second result, reading the data to be read and writing them into multiple worksheets of an Excel file.

[0011] Furthermore, in a preferred embodiment of the present invention, the “reading the data to be read” specifically includes the following steps: reading the data to be read by distributed query; and temporarily storing the data to be read in a memory.

[0012] Furthermore, in a preferred embodiment of the present invention, the “writing into an Excel file” specifically includes the following steps: establishing the Excel file in a memory, the Excel file including a plurality of worksheets; and writing the data to be read into the worksheets of the Excel file.

[0013] Furthermore, in a preferred embodiment of the present invention, the method of "reading the data to be read and writing it into multiple worksheets of an Excel file" includes the following steps: setting a single-page data volume threshold for writing into the worksheet; writing the data to be read into the worksheet according to the single-page data volume threshold; and continuing to write into the next worksheet after writing into the current worksheet is completed until all the data to be read are written into.

[0014] Furthermore, in a preferred embodiment of the present invention, the "reading the data to be read and writing it into multiple worksheets of the Excel file" also includes the following steps: if an exception occurs, performing asynchronous rollback of the shards.

[0015] Furthermore, in a preferred embodiment of the present invention, the data reading status parameter also includes: the export format and export method of the data to be read; the export format includes XLS, XLSX, and CSV.

[0016] In addition, the present application also provides a technical solution for a data rapid export system. The system executes the above-mentioned data rapid export method; the system includes: a first configuration module, the first configuration module is used to obtain the instruction configuration reading data state parameter; a first judgment module, the first judgment module is used to judge the data volume of the data to be read and obtain the data volume judgment result; a first reading module, the first reading module is used to read the data to be read according to the data volume judgment result, and write it into an Excel file; a first storage module, the first storage module is used to complete the Excel file parsing, perform Excel file compression, and complete the export of huge amounts of data.

[0017] In addition, the present application also provides a technical solution for an electronic device, including: a computer program, the computer program is used to execute the above-mentioned data rapid export method; a memory, the memory is used to store the computer program; and a processor, the processor is used to execute the computer program.

[0018] Compared with the prior art, the present invention provides a method for rapid data export. When reading and exporting the data to be read, the data state parameters are first configured, specifically, the single data volume threshold is configured; then the data volume of the data to be read is judged to obtain the data volume judgment result, and the reading and exporting of the data to be read is controlled according to the data volume judgment result. Specifically, after reading the data to be read, it is written into an Excel file. Since the Excel file is not stored in the memory area, it will not occupy the memory space; after completing the data reading, the Excel file is parsed, and the Excel file is compressed before the data export is completed. Since the reading and export is performed according to the data volume judgment result, and the Excel file is not stored in the memory, such as the Excel file is stored in the hard disk, memory pressure is avoided. The technical solution provided by the present application can improve the stability of reading and exporting massive data, reduce memory pressure, and avoid the occurrence of memory overflow.

[0019] Compared with the prior art, the technical solution of the present invention has the following beneficial effects: first, logical functions can be reused, which reduces code duplication and improves development efficiency; second, the compilation and deployment process and time consumption after code adjustment are shorter, resulting in a shortened development to online cycle; third, it can quickly respond to customers' adjustment needs for different data magnitudes; fourth, the introduction cost is low and the learning threshold is low; fifth, the operation is simple and configuration items can be flexibly selected; sixth, the operation mode of POI is reduced to cause excessive memory usage; seventh, the risk of data defects and memory overflow when there are empty loops and integer divisions is reduced. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0021] Figure 1 A flowchart of the main steps of the method for quickly exporting data provided by an embodiment of the present invention;

[0022] Figure 2 A processing flow chart of a method for quickly exporting data provided by an embodiment of the present invention;

[0023] Figure 3 A flowchart of the reservation optimization process provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0024] In order to enable those skilled in the art to better understand the technical solutions in the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without creative work are within the scope of protection of this application.

[0025] It should be noted that when an element is referred to as being "fixed on" or "set on" another element, it can be directly on the other element or indirectly set on the other element; when an element is referred to as being "connected to" another element, it can be directly connected to the other element or indirectly connected to the other element.

[0026] It should be understood that the terms "length", "width", "up", "down", "front", "back", "first", "second", "vertical", "horizontal", "top", "bottom", "inside", "outside", etc., indicating the orientation or position relationship, are based on the orientation or position relationship shown in the drawings, and are only for the convenience of describing the present application and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore should not be understood as a limitation on the present application.

[0027] In addition, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, a feature defined as "first" or "second" may explicitly or implicitly include one or more of the features. In the description of this application, "multiple" and "several" mean two or more, unless otherwise clearly and specifically defined.

[0028] It should be noted that the structures, proportions, sizes, etc. illustrated in the drawings of this specification are only used to match the contents disclosed in the specification for people familiar with this technology to understand and read, and are not used to limit the conditions under which this application can be implemented. Therefore, they have no substantive technical significance. Any structural modification, change in proportional relationship or adjustment of size should still fall within the scope of the technical content disclosed in this application without affecting the effects and purposes that can be achieved by this application.

[0029] like Figures 1 to 3 As shown, the technical solution of the data rapid export method provided by the embodiment of the present application. The method comprises the following steps: configuring the read data state parameters, the read data state parameters comprising: a single data volume threshold; performing a data volume judgment on the data to be read, and obtaining a data volume judgment result; reading the data to be read according to the data volume judgment result, and writing it into an Excel file; completing the Excel file parsing, performing Excel file compression, and completing the massive data export.

[0030] The present invention provides a technical solution for a method for rapid data export. When reading and exporting the data to be read, the data state parameters are first configured, specifically, the single data volume threshold is configured; then the data volume is judged for the data to be read to obtain the data volume judgment result, and the reading and exporting of the data to be read is controlled according to the data volume judgment result. Specifically, after reading the data to be read, the data is written into an Excel file. Since the Excel file is not stored in the memory area, it does not occupy the memory space; after completing the data reading, the Excel file is parsed, and the Excel file is compressed before the data export is completed. Since the reading and export is performed according to the data volume judgment result, and the Excel file is not stored in the memory, such as the Excel file is stored in the hard disk; memory pressure is avoided. The technical solution provided by the present application can improve the stability of reading and exporting massive data, reduce memory pressure, and avoid the occurrence of memory overflow.

[0031] Specifically, in an embodiment of the present invention, the "judgment of the data volume of the data to be read and obtaining a data volume judgment result" specifically includes the following steps: numerically comparing the data volume of the data to be read with the single data volume threshold to obtain the data volume judgment result; the data volume judgment result includes a first result and a second result; the first result is that the data volume of the data to be read is less than or equal to the single data volume threshold; the second result is that the data volume of the data to be read is greater than the single data volume threshold.

[0032] It should be noted that by comparing the amount of data to be read with the single data amount threshold to obtain the data amount judgment result, the data to be read can be objectively and accurately controlled to prevent memory overflow due to excessive data loading into the memory.

[0033] Specifically, in an embodiment of the present invention, the "reading the data to be read according to the data amount judgment result, and writing it into an Excel file" specifically includes the following steps: if the data amount judgment result is a first result, reading the data to be read and writing it into a single worksheet of an Excel file; if the data amount judgment result is a second result, reading the data to be read and writing it into multiple worksheets of an Excel file.

[0034] It should be noted that different excel file storage modes are used according to the amount of data to be read; if the amount of data to be read is less than the single data amount threshold, the data can be stored in only one worksheet of the excel file; if the amount of data to be read is greater than the single data amount threshold, a single worksheet cannot fully store the data to be read, and multiple worksheets are used to store the data, thereby improving the utilization rate of the storage space of a single excel file.

[0035] Specifically, in an embodiment of the present invention, the “reading the data to be read” specifically includes the following steps: reading the data to be read by distributed query; and temporarily storing the data to be read in a memory.

[0036] It should be noted that due to the use of distributed query and reading, the amount of data temporarily stored in the memory is low, which reduces memory pressure and improves system stability.

[0037] Specifically, in an embodiment of the present invention, the "writing into an Excel file" specifically includes the following steps: establishing the Excel file in a memory, the Excel file including a plurality of worksheets; writing the data to be read into the worksheets of the Excel file.

[0038] Specifically, in an embodiment of the present invention, the "reading the data to be read and writing it into multiple worksheets of an Excel file" includes the following steps: setting a single-page data volume threshold for writing into the worksheet; writing the data to be read into the worksheet according to the single-page data volume threshold; after the current worksheet is written, continuing to write into the next worksheet until all the data to be read are written.

[0039] It should be noted that, by setting and judging the single-page data volume threshold, data errors can be avoided when the data to be read can be output from the worksheet.

[0040] Specifically, in an embodiment of the present invention, the "reading the data to be read and writing it into multiple worksheets of the excel file" also includes the following steps: if an exception occurs, performing asynchronous rollback of the shards.

[0041] It should be noted that due to the use of distributed query storage, when an exception occurs in the data read later, it will not affect the data previously stored in the Excel file, thereby improving the stability of data storage.

[0042] Specifically, in an embodiment of the present invention, the data reading status parameter also includes: the export format and export method of the data to be read; the export format includes XLS, XLSX, and CSV.

[0043] In addition, the present application also provides a technical solution for a data rapid export system. The system executes the above-mentioned data rapid export method; the system includes: a first configuration module, the first configuration module is used to obtain the instruction configuration reading data state parameters; a first judgment module, the first judgment module is used to judge the data volume of the data to be read, and obtain the data volume judgment result; a first reading module, the first reading module is used to read the data to be read according to the data volume judgment result, and write it into an Excel file; a first storage module, the first storage module is used to complete the Excel file parsing, perform Excel file compression, and complete the export of massive data. The technical solution of a data rapid export system provided by the present application also has the beneficial effects of the above-mentioned technical solution.

[0044] It should be noted that the above data quick export system is configured as a module. You only need to put it into the project and add indexes to achieve quick configuration of this function.

[0045] In addition, the present application also provides a technical solution for an electronic device, including: a computer program, the computer program is used to execute the above-mentioned data rapid export method; a memory, the memory is used to store the computer program; and a processor, the processor is used to execute the computer program. The technical solution for an electronic device provided by the present application also has the beneficial effects of the above-mentioned technical solution.

[0046] It should be noted that the existing technology based on pure manual coding has the following technical problems: first, logical functions cannot be reused, codes are repeatedly written, and development efficiency is low; second, the compilation and deployment process after code adjustment is long and time-consuming, resulting in a long development to online cycle; third, it is unable to quickly respond to customer permissions and data volume adjustment requirements; fourth, the introduction cost is high and the learning threshold is high; fifth, the operation is complicated, and some configurations are not applicable to small and medium-sized projects; sixth, the encapsulation is not friendly and inconvenient to use; seventh, these operating methods using POI still have excessive memory usage; eighth, there are data defects and memory overflow risks during empty loops and integer division.

[0047] In order to solve the above problems, this technology will cache data to files in lightweight batches, which has solved the problem of Excel freezing the page when downloading large files. The components are configured in the project, and memory overflow is avoided by querying data in batches and caching data to files using SXSSFWorkBook. When the function is released, you only need to submit the corresponding configuration to the production environment to take effect in real time, without the need for code compilation and deployment process, saving costs and improving efficiency.

[0048] It should be noted that the design principle of this technical solution is: when requesting business data, configure the single data limit on the basis of normal access, and selectively configure load balancing; write data in batches to Excel; define multiple sheets, and write sheets (worksheets) step by step according to needs to achieve the purpose of reducing memory consumption. At the same time, the server optimizes SQL through magnitude and offset, and adds indexes to the data table design to improve the efficiency of SQL queries.

[0049] It is necessary to add that, if Figure 2 As shown, the processing flow of the technical solution is as follows: the first step is to configure the threshold, export method, and export format; the second step is to determine whether the threshold is exceeded; if the threshold is not exceeded, a single sheet page is queried and written step by step; if the threshold is exceeded, the upper limit is lowered, the query is performed step by step, a single sheet is written, and a pre-abnormal rollback is performed; finally, the required data is written and a file stream is generated.

[0050] like Figure 3 As shown, the reserved optimization processing idea and process of this technical solution are: first parse and compress the Excel file; then perform the reading step in the memory, then perform the model conversion, and finally return the call.

[0051] In addition, the logic of this technical solution is as follows: first, set a fixed maximum configurable order of magnitude for the configuration item, and reserve a configurable upper limit threshold; second, configure the data export format and export method (front-end export or back-end export). Currently, XLS, XLSX, and CSV are supported, and other formats will be expanded later; third, write a single sheet page according to the set threshold, followed by the upper limit of a single sheet page in the corresponding format table, and paging; fourth, this function reserves the expansion of the optimization method for quickly querying tens of millions of orders of magnitude. It only needs to be configured according to the specific table name field of the project to be used normally, and it also supports export file compression; fifth, exception handling is independent asynchronous rollback of shards, which does not affect the written data.

[0052] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for rapid data export, characterized in that: The following steps are involved: Configure the read data status parameters; The data reading state parameters include: a single data volume threshold; Performing data volume judgment on the data to be read and obtaining a data volume judgment result; According to the data volume judgment result, read the data to be read and write it into an Excel file; Complete the excel file parsing; Compress excel files and export huge amounts of data.

2. The method for rapid data export according to claim 1, characterized in that: The "judging the amount of the data to be read and obtaining the result of the data amount judgment" comprises the following steps: Compare the amount of the data to be read with the single data amount threshold to obtain a data amount determination result; The data volume determination result includes a first result and a second result; The first result is that the amount of the data to be read is less than or equal to the single data amount threshold; The second result is that the amount of the data to be read is greater than the single data amount threshold.

3. The method for rapid data export according to claim 2, characterized in that: The "reading the data to be read according to the data amount judgment result and writing it into an Excel file" includes the following steps: If the data volume determination result is the first result, Read the data to be read and write it into a single worksheet of the Excel file; If the data volume determination result is the second result, The data to be read is read and written into multiple worksheets of the Excel file.

4. The method for rapid data export according to claim 3, characterized in that: The “reading the data to be read” comprises the following steps: Distributed query reads the data to be read; The data to be read is temporarily stored in the memory.

5. The method for rapid data export according to claim 3, characterized in that: The "writing into an excel file" comprises the following steps: Creating the Excel file in a memory, wherein the Excel file includes a plurality of worksheets; The data to be read is written into the worksheet of the Excel file.

6. The method for rapid data export according to claim 3, characterized in that: The "reading the data to be read and writing it into multiple worksheets of the excel file" comprises the following steps: Setting a single-page data volume threshold for writing to the worksheet; Writing the to-be-read data into the work table according to the single-page data amount threshold; After the writing of the current work table is completed, the writing continues to the next work table until all the data to be read are written.

7. The method for rapid data export according to claim 6, characterized in that: The "reading the data to be read and writing it into multiple worksheets of the excel file" also includes the following steps: If an exception occurs, the shard will be asynchronously rolled back.

8. The method for rapid data export according to any one of claims 1 to 7, characterized in that: The data reading state parameter also includes: The export format and export method of the data to be read; The export formats include XLS, XLSX, CSV.

9. Massive data rapid export system, characterized by: The system executes the data rapid export method according to any one of claims 1 to 8; the system comprises: A first configuration module, the first configuration module is used to obtain instruction configuration reading data state parameters; A first judgment module, the first judgment module is used to judge the data amount of the data to be read and obtain a data amount judgment result; A first reading module, the first reading module is used to read the to-be-read data according to the data amount judgment result, and write it into an Excel file; The first storage module is used to complete the Excel file parsing, perform Excel file compression, and complete the export of massive data.

10. An electronic device, characterized in that include: A computer program, the computer program being used to execute the method for rapid data export according to any one of claims 1 to 8; A memory, the memory being used to store the computer program; A processor is used to execute the computer program.

Images