Method and system for quickly generating large-data-volume EXCEL report

Abstract

The invention discloses a method and a system for quickly generating a large-data-volume EXCEL report. The EXCEL report of dynamic data columns and rows can be generated. An EXCEL report with more than 10,000 rows of data volume can be generated at one time, and the time for generating an EXCEL document can be compressed. According to the method, great convenience is provided for the function of generating a large amount of EXCEL documents, a user does not need to prepare an EXCEL template in advance, the working efficiency is improved, and a large amount of time is saved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and particularly relates to a method and system for quickly generating an EXCEL report with a large amount of data. Background Art

[0002] In the user personalization program of the existing SAP-ERP system in current petrochemical enterprises, the technology commonly used for the function of generating EXCEL reports is SAP-OLE. This technology is based on the ERP calling the office software, and writes data into each cell in the EXCEL report.

[0003] For reports with a large amount of data (such as more than 5000 rows of data), the efficiency of generating EXCEL documents is very low and unstable. When the data volume exceeds 30 columns and 7000 rows, the time required to generate an EXCEL document is close to 1 hour. If the data limit is exceeded, there is a high probability that the program system will directly crash.

[0004] The SAP-DOI technology can save the complete data as a cache in its entirety and directly write it into the EXCEL document. However, the underlying mechanism of this technology can only achieve a write limit of "999 rows and 9999 columns" or "9999 rows and 999 columns" in actual tests. And this limit cannot be directly modified. Summary of the Invention

[0005] In view of this, one of the objectives of the present invention is to provide a method for quickly generating an EXCEL report with a large amount of data, which can break through the limit of EXCEL reports with more than 10,000 rows of data, and at the same time compress the time required to generate EXCEL documents. Another objective of the present invention is to provide a system for quickly generating an EXCEL report with a large amount of data.

[0006] One of the objectives of the present invention is achieved through the following technical solutions:

[0007] The method for quickly generating an EXCEL report with a large amount of data includes:

[0008] Run the ERP system and generate an ERP data report using the ERP system;

[0009] Use the built-in code tool in ABAP language to identify and parse the ERP data report to be output, strip out the column headers of the current ERP data report, and use them as a separate set of ERP report data to generate a first temporary ERP system table, that is, the header table;

[0010] Re-insert the only set of data in the header table into the original ERP data report to be output and place it in the first row, thereby generating a second temporary ERP system table, that is, the complete table;

[0011] Perform an overall data scan on the complete table to obtain the actual number of rows ROW and columns COL of the complete table;

[0012] Perform data structure parsing on the complete table to generate a third temporary ERP system table with the framework of "(which) row; (which) column; data", that is, the parsing table;

[0013] Characterize and limit the number of rows ROW and columns COL of the parsing table, and call the DOI tool to perform segmented caching on all the data in the parsing table.

[0014] When all the data in the parsing table is read and cached by the DOI tool, an EXCEL document is automatically generated and output.

[0015] The reason for being able to achieve time compression is that: the DOI tool itself has the effect of directly using cached data and exporting EXCEL documents at high speed.

[0016] Furthermore, the segmented data caching includes:

[0017] Use the DOI tool to read the third temporary ERP system table - parsing table. When the column value COL exceeds 99, the extra number of columns will be written into the SHEET tab of the next EXCEL document; when the row value ROW exceeds 9000, the DOI tool caches the first 9000 pieces of data, and then continues to read the third temporary ERP system table - parsing table. After reading the next 9000 rows, data caching is performed again; if there are less than 9000 pieces, they are directly cached. This design enables the ERP system to export more than ten thousand pieces of data at one time. The reason is that: the DOI tool can store data in the form of caching. The setting of the DOI tool is that it can cache less than 10,000 pieces of data in a single execution, but there is no limit on the number of times of caching execution. In other words: according to the data volume of the ERP report to be exported actually, in one execution of the overall program logic, the DOI tool can be used multiple times in a loop, so as to achieve the effect of performing multiple caches and "limiting the data cached each time to less than 10,000 pieces". Furthermore, when executing the overall logic program once, more than 10,000 pieces of data can be exported at one time.

[0018] The second object of the present invention is achieved by the following technical solutions:

[0019] This kind of large - data - volume EXCEL report fast - generation system is characterized in that: the system includes:

[0020] An ERP system module, used to generate an ERP data report from the data in the ERP system;

[0021] The first logical processing module uses the built-in code tools in ABAP language to identify and parse the ERP data report to be output, strip out the column headers of the current ERP data report, and use them as a separate set of ERP report data to generate the first temporary ERP system table, i.e., the title table.

[0022] The second logical processing module reinserts the only set of data in the title table into the original ERP data report to be output and places it in the first row, thereby generating the second temporary ERP system table, i.e., the complete table;

[0023] The third logical processing module is used to scan the second temporary ERP system table to obtain the number of rows and columns of the data to be exported;

[0024] The fourth logical processing module is used to parse the data structure of the second temporary ERP system table to generate the third temporary ERP system table with the framework of "row; column; data", i.e., the parsing table;

[0025] The fifth logical processing module is used to depict and limit the number of rows ROW and columns COL of the third temporary ERP system table, and call the DOI tool for segmented data caching;

[0026] The sixth logical processing module automatically generates and outputs an EXCEL document after all the data in the parsing table has been read and cached by the DOI tool.

[0027] Furthermore, the segmented data caching of the fifth logical processing module reads the third temporary ERP system table through the DOI tool. When the column value COL exceeds 99, the extra number of columns will be written into the SHEET tab of the next EXCEL document; when the row value ROW exceeds 9000, the DOI tool caches the first 9000 pieces of data, and then continues to read the remaining data in the third temporary ERP system table until the next 9000 rows are read and data caching is performed again; if there are less than 9000 pieces, it will be directly cached.

[0028] The beneficial effects of the present invention are:

[0029] It can generate EXCEL reports with dynamic data columns and rows; it can generate EXCEL reports with a data volume of more than 10,000 rows at one time; and it can compress the time for generating EXCEL documents; the present invention provides great convenience for the function of generating EXCEL documents, and users do not need to separately upload the edited EXCEL template with column headers, saving a lot of time.

[0030] Other advantages, objects, and features of the present invention will be set forth in part in the description which follows, and in part will be obvious to those having ordinary skill in the art upon examination of the following, or may be learned from practice of the invention. The objects and other advantages of the invention may be realized and attained by means of the instrumentalities and combinations particularly pointed out in the appended claims and specification. Brief Description of the Drawings

[0031] To make the objectives, technical solutions, and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings, where:

[0032] Figure 1 is a schematic diagram of the method flow of the present invention;

[0033] Figure 2 is a schematic diagram of the data representation of the embodiment;

[0034] Figure 3 is the first temporary ERP system table (title table) of the embodiment;

[0035] Figure 4 is the second temporary ERP system table (complete table) of the embodiment;

[0036] Figure 5 is the third temporary ERP system table (analysis table) of the embodiment.

[0037] Figure 6 is a schematic diagram of the EXCEL document of the complete output of the embodiment. Detailed Description of the Preferred Embodiments

[0038] The preferred embodiments of the present invention will be described in detail below with reference to the accompanying drawings. It should be understood that the preferred embodiments are only for explaining the present invention and not for limiting the protection scope of the present invention.

[0039] The descriptions of the relevant names involved in the present invention are as follows:

[0040] Temporary system table: Transformed from the ERP data report, it is a data table that only exists in the ERP system and is used for data processing and conversion in program logic operations;

[0041] ROW: The number of rows of the ERP data report;

[0042] COL: The number of columns of the ERP data report;

[0043] DOI: Abbreviation of (Desktop Office Integration); It is a tool for the technical solution provided by SAP to solve the integration with OFFICE;

[0044] EXCEL Report: The EXCEL document actually generated on the computer desktop, which can be actually copied, pasted and sent as a file;

[0045] The First Temporary ERP System Table (Title Table): The title table belongs to the temporary system table and stores the ERP data table of the column titles in the report;

[0046] The Second Temporary ERP System Table (Complete Table): Belongs to the temporary system table and stores the ERP table of all data including column titles in the report;

[0047] The Third Temporary ERP System Table (Analysis Table): Belongs to the temporary system table and is an ERP data table generated after disassembling all the data in the complete table in the way of "which row; which column; what data".

[0048] It should be noted that any process or method description shown in the flowchart or described in other ways here can be understood as representing a module, segment or part of code including one or more executable instructions for implementing a specific logical function or process, and the scope of the preferred embodiment of the present invention includes additional implementations, where the functions can be executed in a substantially simultaneous manner or in the reverse order according to the functions involved, rather than in the order shown or discussed, which should be understood by those skilled in the technical field to which the embodiments of the present invention belong.

[0049] As Figure 1 shown, the method for quickly generating a large amount of data EXCEL report of the present invention includes:

[0050] Run the ERP system and generate an ERP data report using the ERP system; Call the associated program component integrated with SAP and office as needed;

[0051] Use the built-in code tool in ABAP language to identify and parse the ERP data report to be output, strip out the column headers of the current ERP data report, and use them as a separate set of ERP report data to generate the first temporary ERP system table, that is, the header table; this avoids the execution step of "uploading the EXCEL template separately first and then generating the EXCEL report"; it should be noted that the reason for stripping and the reason for generating the EXCEL report of dynamic data columns and rows are as follows: if you want to use the DOI tool, you must specify the blank EXCEL template provided by the ERP system or specify a personalized EXCEL template with different header columns uploaded and edited by yourself. Executing the current step of generating the header table is to directly use the blank EXCEL template provided by the ERP system. First, dynamically identify which header columns exist in the ERP report data to be exported currently, and associate the current header columns with the ERP report data to be exported subsequently in the form of ERP data, and import them into the DOI tool in the form of a cache, and directly use the blank EXCEL template provided by the ERP system to export data and generate an EXCEL document. In this way, the user can avoid the step of separately editing and uploading an EXCEL document template with different header rows for different ERP report data.

[0052] Re-insert the only set of data in the header table into the original ERP data report to be output and place it in the first row position, thus generating the second temporary ERP system table, that is, the complete table;

[0053] Perform an overall data scan on the complete table to obtain the actual number of rows ROW and the number of columns COL of the complete table;

[0054] Perform data structure parsing on the complete table to generate the third temporary ERP system table with the framework of "(which) row; (which) column; data", that is, the parsing table;

[0055] Characterize and limit the number of rows ROW and the number of columns COL of the parsing table, call the DOI tool, and perform segmented caching on all the data in the parsing table; specifically, use the DOI tool to read the third temporary ERP system table - the parsing table. When the column value COL exceeds 99, the extra number of columns will be written into the SHEET tab of the next EXCEL document; when the row value ROW exceeds 9000, the DOI tool caches the first 9000 pieces of data, and then continues to read the third temporary ERP system table - the parsing table until the next 9000 rows are read and data caching is performed again; if it is less than 9000 pieces, it will be directly cached. This step breaks through the original limit of "999 rows and 9999 columns" based on the underlying mechanism of SAP-DOI technology, realizes the segmented characterization of the "row" quantity, and makes the "column" quantity infinite.

[0056] After all the data in the parsing table is read and cached by the DOI tool, an EXCEL document is automatically generated and output, such as output to the computer desktop.

[0057] The above method steps are further described below through a specific embodiment.

[0058] As Figure 2 shown, currently it is necessary to export the data table "Roster" in the ERP system from the system and generate an EXCEL document report. The specific steps are as follows: Figure 2 in the

[0059] (1) Scan and parse the header / title structure of the current data table to generate a title table as Figure 3 shown;

[0060] (2) Insert the data of the temporarily generated title table into the actually required "Roster" to generate a complete table as Figure 4 shown:

[0061] (3) Scan the main body data of the complete table and draw the conclusion: the number of data rows to be exported is 13001; the number of data columns to be exported is 13001.

[0062] (4) Parse the main body data of the complete table to generate a parsing table as Figure 5 shown;

[0063] (5) Characterize the data volume, call the DOI tool, and perform segmented data caching. This tool reads the parsing table, with 9000 rows as the boundary. When ROW = 9001, the DOI tool caches the first 9000 data rows, and then continues to read the parsing table until the next 9000 rows are read, and then performs data caching again. If it is less than 9000 rows, it is directly cached.

[0064] (6) After completely putting the parsing table into the DOI tool in a cached form, output an EXCEL document. The content of the document is as Figure 6 shown.

[0065] This functional system can be directly grafted into any program under the ABAP language. In theory, after simple secondary packaging, it can be called by the code programs of other machine languages.

[0066] After testing, the comparison data of the EXCEL reports generated by the original OLE technology and the current functional system of this invention for different data volumes are as follows:

[0067]

[0068] Based on the design concept of the aforementioned method for quickly generating large-volume EXCEL reports, the present invention also provides a system for quickly generating large-volume EXCEL reports, which includes:

[0069] This system for quickly generating large-volume EXCEL reports is characterized in that it includes:

[0070] Run the ERP system. After generating the ERP data report in the ERP system, call the relevant program components integrated with SAP and office;

[0071] The first logic processing module uses the built-in code tool in ABAP language to identify and parse the ERP data report to be output, strip out the column headers of the current ERP data report, and use them as a separate set of ERP report data to generate the first temporary ERP system table, i.e., the title table.

[0072] The second logic processing module reinserts the only set of data in the title table into the original ERP data report to be output and places it in the first row, thereby generating the second temporary ERP system table, i.e., the complete table;

[0073] The third logic processing module is used to scan the second temporary ERP system table to obtain the number of rows and columns of the data to be exported;

[0074] The fourth logic processing module is used to parse the data structure of the second temporary ERP system table to generate a third temporary ERP system table with a framework of "row; column; data", i.e., the parsing table;

[0075] The fifth logic processing module is used to depict and limit the number of rows ROW and columns COL of the third temporary ERP system table, and call the DOI tool for segmented data caching; in specific use, the segmented data caching of the fifth logic processing module reads the third temporary ERP system table through the DOI tool. When the column value COL exceeds 99, the extra columns will be written into the SHEET tab of the next EXCEL document; when the row value ROW exceeds 9000, the DOI tool caches the first 9000 pieces of data, and then continues to read the remaining data in the third temporary ERP system table until it reads the next 9000 rows and caches the data again; if there are less than 9000 pieces, it will be directly cached.

[0076] The sixth logic processing module automatically generates an EXCEL document and outputs it after all the data in the parsing table has been read and cached by the DOI tool.

[0077] For the user personalization program in the SAP-ERP system, the invention provides great convenience for the function of generating EXCEL documents. Users do not need to prepare EXCEL templates in advance and do not need to wait for a long time. For the user group that often needs to use EXCEL data reports at work, after the relevant developers graft the function system into the program, users can directly use it according to their original office habits without any additional operations.

[0078] It should be noted that the technology for exporting EXCEL documents is the current common OLE technology rather than the DOI technology because the OLE technology is simpler and more friendly for program developers to develop. The DOI technology is relatively more complex in the development process. For ERP reports with less data volume, the effect of using the OLE technology to export EXCEL documents can be acceptable. However, for the situation where a large amount of ERP data needs to be exported, the OLE technology is not applicable at this time and a more excellent technology needs to be adopted for replacement. Through in-depth research on this situation and comparing various tools, the invention selects the DOI technology tool for further development, thus realizing the rapid generation of EXCEL reports in the case of a large amount of data.

[0079] 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 preferred embodiments, those of ordinary skill in the art should understand that the technical solutions of the present invention can be modified or equivalently replaced without departing from the purpose and scope of the present technical solution, and they should all be covered within the scope of the claims of the present invention.

Claims

1. A method for quickly generating large amounts of EXCEL reports, characterized in that: The method comprises: Run the ERP system and generate ERP data reports using data in the ERP system; Use the code tool provided in the ABAP language to identify and parse the ERP data report to be output, and strip out the column headers of the current ERP data report, and use them as a separate set of ERP report data to generate the first temporary ERP system table, namely the header table; Re-insert the unique set of data in the title table into the original ERP data report to be output, and place it in the first row, thereby generating a second temporary ERP system table, i.e., a complete table; Scan the entire table as a whole to obtain the actual number of rows and columns in the table. Parse the data structure of the complete table to generate a third temporary ERP system table with the framework of "(number) row; (number) column; data", i.e., the parsing table; Characterize and limit the number of rows and columns in the parsing table, call the DOI tool, and cache all the data in the parsing table in segments; When all the data in the parsing table are read and cached by the DOI tool, an EXCEL document is automatically generated and output.

2. The method for quickly generating a large amount of EXCEL report according to claim 1, characterized in that: The segment data cache comprises: Use the DOI tool to read the third temporary ERP system table - parsing table. When the column value COL exceeds 99, the excess columns will be written to the SHEET tab of the next EXCEL document; when the row value ROW exceeds 9000, the DOI tool caches the first 9000 data, and then continues to read the third temporary ERP system table - parsing table until the next 9000 rows are read, and then caches the data again; if there are less than 9000 records, cache them directly.

3. A system for quickly generating large amounts of data in Excel, characterized by: The system comprises: ERP system module, used to generate ERP data reports from data in the ERP system; The first logic processing module uses the built-in code tool in the ABAP language to identify and parse the ERP data report, strip off the column headers of the current ERP data report, and use it as a separate set of ERP report data to generate the first temporary ERP system table, namely the header table. The second logic processing module re-inserts the unique set of data in the title table into the original ERP data report to be output, and places it in the first row, thereby generating a second temporary ERP system table, i.e., a complete table; The third logic processing module is used to scan the second temporary ERP system table to obtain the number of rows and columns of data to be exported; A fourth logic processing module is used to parse the data structure of the second temporary ERP system table to generate a third temporary ERP system table with a frame of "row; column; data", namely, a parsing table; The fifth logic processing module is used to characterize and limit the number of rows ROW and the number of columns COL of the third temporary ERP system table, and call the DOI tool to cache segmented data; The sixth logic processing module automatically generates and outputs an EXCEL document after all the data in the parsing table are read and cached by the DOI tool.

4. The system for quickly generating large amounts of data in Excel as claimed in claim 3, characterized in that: The segmented data cache of the fifth logic processing module reads the third temporary ERP system table through the DOI tool. When the column value COL exceeds 99, the excess columns will be written into the SHEET tab of the next EXCEL document; when the row value ROW exceeds 9000, the DOI tool caches the first 9000 data, and then continues to read the remaining data of the third temporary ERP system table until the next 9000 rows are read, and then caches the data again; if there are less than 9000 data, cache them directly.

Images