Method, device and equipment for processing and exporting report data with large data volume and medium

Abstract

The invention relates to the technical field of big data processing, in particular to a method, a device, equipment and a medium for processing and exporting report data with a large data volume, and the method comprises the following steps: receiving a report exporting request initiated by a user, and analyzing the content of the request to obtain a report identifier, exporting parameters and a target format; loading report template information corresponding to the report identifier, and extracting a report style; if the report comprises a change area and the data volume of the change area exceeds a threshold value configured by a user, loading fixed area data at one time, and obtaining a change area data stream based on the export parameters; creating a worksheet, traversing a report template according to rows, sequentially processing fixed area data and variable area data streams, and rendering format attributes in real time; dynamically adjusting the coordinate range of the merged cells, and outputting the processed worksheet to a file; and the dynamic SQL query statement is assembled to obtain the required data, so that the data obtaining efficiency is improved, the high-efficiency processing of the large-data-volume report is realized, and the report exporting speed is effectively improved.

Description

Technical Field

[0001] This application relates to the technical field of big data processing, and specifically relates to a method, device, equipment and medium for processing and exporting big data volume report data. Background Art

[0002] In modern enterprise management and data analysis, reports, as an important data display and transmission tool, are widely used in various business fields. With the continuous growth of business data, the amount of data that needs to be processed in reports is also increasing day by day. Traditional report export methods usually adopt the method of loading all data at once and processing it. This method has obvious limitations when dealing with big data volume reports.

[0003] Specifically, when the report data volume is huge, loading all data at once will occupy a large amount of memory resources. At the same time, for reports containing variable areas (such as sales detail tables, inventory dynamic tables, etc.), the data volume often changes continuously with the progress of business activities, and traditional export methods are difficult to efficiently handle this dynamic change.

[0004] Based on this, when facing big data volume, traditional report export methods, on the one hand, have extremely high memory overhead. When the data volume exceeds the system memory capacity, memory warnings or even service outages often occur, seriously affecting the stability and normal operation of the system. On the other hand, the processing speed is slow. Loading and processing a large amount of data will consume a lot of time, resulting in low report export efficiency and unable to meet the user's demand for real-time data. Summary of the Invention

[0005] Aiming at the problems of large memory consumption, slow processing speed, and poor support for complex table formats in existing report export technologies when dealing with big data volume complex reports, the present invention provides a method, device, equipment and medium for processing and exporting big data volume report data.

[0006] In a first aspect, the technical solution of the present invention provides a method for processing and exporting big data volume report data, including the following steps: S1: Receive a report export request initiated by a user, and parse the request content to obtain a report identifier, export parameters, and a target format; S2: Load the report template information corresponding to the report identifier, and extract the report style, including cell information of the fixed area and variable area, as well as the mapping relationship between variable area data items and database fields; S3: Check whether the report contains a variable area. If it contains a variable area and the data volume of the variable area exceeds the threshold configured by the user, execute step S4; otherwise, execute step S5; S4: Perform streaming export. The specific steps include: S41: Load the fixed area data at one time, and obtain the variable area data stream based on the export parameters; S42: Create a worksheet, traverse the report template row by row, process the fixed area data and the variable area data stream in sequence, and render the format attributes in real time; S43: Dynamically adjust the coordinate range of the merged cells and output the processed worksheet to a file; S5: Perform a regular export: load all data at once and export to a file.

[0007] During the export process, the report is checked for inclusion of variable areas and the amount of data in those areas. For reports without variable areas, or where the amount of data in those areas does not exceed the user-configured parameter, the traditional method of reading all the data at once is used, which is simple and efficient. For reports with larger amounts of data in variable areas, streaming processing is used. This flexible selection of processing strategies based on data characteristics leverages the advantages of different processing methods and further optimizes report export performance.

[0008] As a further limitation of the technical solution of the present invention, step S2 specifically includes: S21: Reading report template information from a database or configuration file according to the report identifier, parsing row height, column width, and change area information, wherein the change area information includes the row number, row code, and number of changed rows of the changed row; S22: construct a two-dimensional table of cell information to store the cell value, font, background color, border style and alignment; S23: Record the starting and ending row and column coordinates of the merged cells; S24: parsing the attribute definition of the data item in the template, extracting the data item number, the associated database table name and field name, the data type and formatting rules; S25: Constructing a mapping relationship between data item numbers and database fields, that is, a mapping relationship between variable area data items and report templates, and generating a mapping table.

[0009] When loading report information to obtain the report style, detailed information such as row height, column width, variable area information, two-dimensional cell information, and merged cell information is extracted. When processing report data, whether in fixed or variable areas, this information can be used to accurately process cell data and render format attributes in real time, including font, background color, border style, etc. Merged cells can also be correctly handled, ensuring that complex report styles are accurately exported, greatly improving support for complex sample reports.

[0010] As a further limitation of the technical solution of the present invention, in step S21, the step of parsing row height, column width and variable area information includes: Load the row dictionary to get the height value of each row and the width value of each column; Traverse the row dictionary, extract the line numbers, line codes, and the number of changed lines where the changed lines are located, and construct the information of the changed area.

[0011] As a further limitation of the technical solution of the present invention, in step S41, the steps of obtaining the data stream of the changed area include: S411: Assemble a dynamic SQL query statement according to the mapping relationship between the data items in the changed area and the report template; S412: Query and obtain the data stream of the changed area through the SQL query statement with the export parameters as conditions.

[0012] As a further limitation of the technical solution of the present invention, step S411 specifically includes: S411a: Determine the target table and fields to be queried according to the table name and field name in the mapping relationship; S411b: Convert the export parameters in the user request into SQL conditional statements; S411c: Generate SQL that supports streaming reading.

[0013] As a further limitation of the technical solution of the present invention, in S42, processing the fixed area data includes: S421a: Locate the writing position according to the template line number, traverse the cells and fill in the processed data item values; S421b: Apply the font, border, and background color styles of the cells in real time.

[0014] As a further limitation of the technical solution of the present invention, in S42, the steps of processing the data stream of the changed area include: S422a: Group the data items by line number, read a single record from the data stream, and match the field name with the data item number according to the mapping table; S422b: Fill the data into the corresponding cells of the worksheet according to the row and column positions in the report template; S422c: Perform format conversion on non-empty data items according to the format rules defined by the data items, and pre-read the next data; S422d: Dynamically adjust the starting row number of the merged cells according to the number of changed lines that have been written.

[0015] When processing the data stream of the changed area, grouping and aggregating the data items by line number, performing format conversion and style application when filling the data, recording the writing information for dynamic adjustment of the merged cells, and pre-reading the next data when processing the current line, this series of operations optimize the data processing logic, improve the accuracy and efficiency of data processing, and ensure the integrity of the report data and the standardization of the format.

[0016] As a further limitation of the technical solution of the present invention, in S43, the steps of dynamically adjusting the coordinate range of the merged cells include: S431: Traverse the merged cell information, clone the original coordinate range, and if the starting row is within the changed area, update the row number of the starting row to: New row number = original row number + number of changed rows written; S432: Write the adjusted merged range to the worksheet.

[0017] In the technical solution of this application, when outputting the processed worksheet to a file, it is output to the specified document storage location according to different export methods, and the user is notified to download during asynchronous export, which provides convenience for the user. At the same time, since problems such as memory warnings, service outages, and report format errors are solved, users can quickly and accurately obtain the required reports, improving the satisfaction and experience of users using the report export function.

[0018] In a second aspect, the technical solution of the present invention also provides a device for processing and exporting large amounts of report data, including a request processing module, a report processing module, an export method determination module, a streaming export module, and a conventional export module; The request processing module is used to receive a report export request initiated by the user, and parse the request content to obtain a report identifier, export parameters, and a target format; The report processing module is used to load the report template information corresponding to the report identifier, and extract the report style, including cell information in the fixed area and the changed area, and the mapping relationship between the data items in the changed area and the database fields; The export method determination module is used to check whether the report contains a changed area. If it contains a changed area and the data volume in the changed area exceeds the threshold configured by the user, the streaming export module is triggered; otherwise, the conventional export module is triggered; The streaming export module is specifically used to load the fixed area data at one time, and obtain the changed area data stream based on the export parameters; create a worksheet, traverse the report template row by row, process the fixed area data and the changed area data stream in sequence, and render the format attributes in real time; dynamically adjust the coordinate range of the merged cells, and output the processed worksheet to a file; The conventional export module is used to load all the data at one time and export it to a file.

[0019] In a third aspect, the technical solution of the present invention also provides an electronic device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; the memory stores computer program instructions executable by the at least one processor, and the computer program instructions are executed by the at least one processor so that the at least one processor can execute the method for processing and exporting large amounts of report data as described in the first aspect.

[0020] In a fourth aspect, the technical solution of the present invention further provides a non-transitory computer-readable storage medium storing computer instructions that cause the computer to execute the method for processing and exporting large amounts of report data as described in the first aspect.

[0021] As can be seen from the above technical solutions, the present application has the following advantages: By querying and loading the fixed-area data at one time and simultaneously obtaining the data stream of the variable-area data, the present application avoids the problem of excessive memory pressure caused by loading a large amount of data at one time. When obtaining the variable-area data, data items are filtered and a dynamic SQL query statement is assembled, which can accurately obtain the required data, improve the data acquisition efficiency, and thus achieve the efficient processing of large amounts of report data, effectively improving the speed of report export. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] To more clearly illustrate the technical solutions of the present application, the drawings required for description will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments of the present application. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0023] Figure 1 It is a schematic flowchart of the method provided by the embodiment of the present invention.

[0024] Figure 2 It is a data export flowchart in the embodiment of the present invention.

[0025] Figure 3 It is a block diagram of the device provided by the embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0026] The sample form of the report product consists of a fixed area and a variable area. The fixed area and the variable area are divided into format information and data items. Among them, the data item is the part for filling in data, and the item information is the description of the data. The format of the fixed area is fixed, and data can only be filled in the specified data items. The variable area consists of one or more rows to form a base group. The format of each base group is fixed, and a certain number of base groups can be inserted arbitrarily. The multi-level header report is composed of multiple rows of fixed styles to form a variable base group. In the scenario of exporting a large amount of data, generally the data volume of this type of variable area is large. When the user selects a report and clicks to export, first, the user request is parsed, and the exported report is checked to check whether it contains a variable area and the data volume of the variable area. If the data volume of the variable area exceeds the parameter value configured by the user, then streaming export is adopted; otherwise, the traditional export method is adopted. To make the application purpose, features, and advantages of the present application more obvious and understandable, the following will use specific embodiments and accompanying drawings to clearly and completely describe the technical solutions protected by the present application. Obviously, the embodiments described below are only a part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in this patent, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the scope of protection of this patent.

[0027] As Figure 1 shown, an embodiment of the present invention provides a method for processing and exporting report data with a large amount of data, including the following steps: S1: Receive the report export request initiated by the user, and parse the request content to obtain the report identifier, export parameters, and target format; Parse out information such as the report, unit, year, period, task, extended dimension, data version, etc. exported by the user. Parse the export method selected by the user, whether it is asynchronous export, and the document storage location.

[0028] S2: Load the report template information corresponding to the report identifier, and extract the report style, including the cell information of the fixed area and the variable area, and the mapping relationship between the data items in the variable area and the database fields; S3: Check whether the report contains a variable area. If it contains a variable area and the data volume of the variable area exceeds the threshold configured by the user, then execute step S4; otherwise, execute step S5; S4: Perform streaming export, and the specific steps include: S41: Load the fixed area data at one time, and obtain the variable area data stream based on the export parameters; S42: Create a worksheet, traverse the report template row by row, and process the fixed area data and the variable area data stream in sequence, and render the format attributes in real time; S43: Dynamically adjust the coordinate range of the merged cells, and output the processed worksheet to a file; S5: Perform regular export: Load all data at once and export it to a file.

[0029] In some embodiments, the report style includes row height, column width, a two-dimensional table storing cell information, and merged cell information. The cell information includes the value of the cell (format information or data item number), font, background color, border style, and alignment. Specifically, step S2 includes: S21: Read the report template information from the database or configuration file according to the report identifier, and parse the row height, column width, and variable area information. The variable area information includes the row number, row internal code, and number of variable rows of the variable row. This template information is usually stored in XML or JSON format and contains the overall structure and style definition of the report. Specifically, load the row dictionary to obtain the height value of each row (in pixels or points). Load the column dictionary to obtain the width value of each column (in pixels or number of characters). Traverse the row dictionary to extract the row position (row number, row internal code) and number of variable rows where the variable row is located, and construct the variable area information. Create a two-dimensional array or similar data structure to store the detailed information of each cell.

[0030] S22: Construct a two-dimensional table of cell information to store the value, font, background color, border style, and alignment of the cell; create a list or array to store the ranges of all cells that need to be merged.

[0031] For the two-dimensional table of cell information, traverse each cell in the template and extract and store the following attributes: Value of the cell: It may be static text (format information) or a reference to dynamic data (data item number).

[0032] Font information: Includes font name, size, whether it is bold, whether it is italic, etc.

[0033] Background color: Represented by RGB values or predefined color names.

[0034] Border style: Includes the styles (such as solid line, dotted line, double line, etc.) and colors of the top, bottom, left, and right borders.

[0035] Alignment: Includes horizontal alignment (left alignment, center alignment, right alignment) and vertical alignment (top alignment, center alignment, bottom alignment).

[0036] Construct the report data item information, and create a list to store the following attributes of the data item: Value: The value stored in the background data table.

[0037] Database information: The background table and field where the current data item is located.

[0038] Data type: Amount, numeric value, character, long text, date, etc.

[0039] Format information: whether to display thousand separators, percent signs, currency units, scientific notation, zero values, etc.

[0040] Default value: the default value of the current data item when no data is filled in.

[0041] Internal code of the row and column where it is located: determine in which change area the current data item is.

[0042] S23: Record the starting and ending row and column coordinates of the merged cells; In the embodiments of the present invention, record the row and column indexes of the starting cell and the ending cell of each merged area.

[0043] S24: Analyze the attribute definitions of the data items in the template, and extract the data item numbers, associated database table names and field names, data types, and formatting rules; S25: Construct the mapping relationship between the data item numbers and the database fields, that is, the mapping relationship between the change area data items and the report template, and generate a mapping table.

[0044] Organize all the extracted information into a unified data structure or object for quick access and use in subsequent processing steps. Verify the extracted information to ensure that all necessary style information has been correctly loaded. If there are any omissions or errors, record a log and use the default value instead. Use a caching mechanism to cache the common report template information in memory or Redis to reduce the overhead of repeated loading and parsing.

[0045] In some embodiments, in step S41, the steps of obtaining the change area data stream include: S411: Assemble a dynamic SQL query statement according to the mapping relationship between the change area data items and the report template; S412: Query and obtain the change area data stream through the SQL query statement with the export parameters as conditions.

[0046] Further, step S411 specifically includes: S411a: Determine the target table and fields to be queried according to the table name and field name in the mapping relationship; S411b: Convert the export parameters in the user request into SQL conditional statements; S411c: Generate SQL that supports streaming reading.

[0047] Because the fixed area has limited data, all fixed areas of a report only have one record in the database, so they are loaded all at once. The variable area has a larger data volume, so only the data stream is obtained first. All data items in the current variable area are filtered, and SQL is organized based on the background tables and data items. The unit, year, period, task, extended dimension, and data version parsed in S1 are used as conditions to obtain the query data stream.

[0048] In some embodiments, in S42, processing fixed area data includes: S421a locates the write position according to the template row number, traverses the cells and fills the processed data item value; S421b: Apply the font, border and background color styles of the cell in real time.

[0049] The steps for processing the data stream in the changing area include: S422a: Group the data items by row number, read a single record from the data stream, and match the field name and data item number according to the mapping table; S422b: Fill the data into the corresponding cells of the worksheet according to the row and column positions in the report template; S422c: Perform format conversion on the non-empty data item according to the format rule defined for the data item, and pre-read the next piece of data; S422d: Dynamically adjust the starting row number of the merged cell according to the number of changed rows written.

[0050] It should be noted that the data flow of the report change area is processed and then filled into the worksheet. The specific steps include: according to the row number grouping rule of the change area, the data items are aggregated into a complete change area by row number; Read a data record from the data stream and execute the following loop: fill the data items of the current record into the corresponding cells of the change area according to the mapping relationship; convert the format of non-empty data items and apply cell styles; record the starting row number and total number of rows written to the current change area; Dynamically adjust merged cells and write the adjusted merged cells to the worksheet; and pre-read the next data when processing the current row.

[0051] In some embodiments, in S43, the step of dynamically adjusting the coordinate range of the merged cells includes: S431: Traverse the merged cell information, clone the original coordinate range, and if its starting row is within the change area, update the row number of the starting row to: New line number = original line number + number of changed lines written; S432: Write the adjusted consolidation range into the worksheet.

[0052] In the embodiments of the present invention, a worksheet is created using SXSSFWorkbook of POI, and data is exported row by row. Traverse the two-dimensional table of cell information row by row: (1) If the current row is a fixed area, create a row, process the values of the data items in this row and fill them in this row in sequence, and set the cell font, border, and background color. Processing the values of data items includes: assigning a default value to a null value, then formatting the data according to the data item format information, and converting the data item format information into an Excel custom format and setting it to the corresponding cell in sequence.

[0053] (2) If the current row is a variable row, obtain all the data items in the current variable area. The multi-level header report consists of multiple rows of data that form a variable area, so the data items are grouped by the row number where they are located, and the grouped data is a variable area. As Figure 2 shown, read a piece of data in the data stream and execute the following process until all data is read: (a) Assign the read data to the data items in the variable area according to the mapping relationship.

[0054] (b) Traverse the grouped data, that is, traverse each row in the variable area. Process the data items in the current row and fill them in sequence, complete the filling of the current variable area, and record the number of rows inserted into Excel. Similar to the fixed area, it is necessary to format the values of the data items and set the Excel custom format.

[0055] (c) Traverse the merged cell information. The merged cells within the range of variable rows need to be cloned, and the starting row number is added with the number of variable rows. The merged cells below the variable rows need to be added with the number of variable rows recorded in the previous step. While reading a piece of data into memory and processing the above process, pre-read the next piece of data to further improve the export efficiency.

[0056] (3) Set the row height and column width, and add merged cells.

[0057] Output the processed SXSSFWorkbook worksheet to a file. Determine whether to output it to the response file stream or the specified server directory according to the output method selected by the user. If the user selects asynchronous output, the user will be notified by in-site message or SMS after the data export is completed, and the user can log in to the system to download within a certain period of time. It is also possible to upload the exported file to the specified server according to the user configuration.

[0058] Suppose a large manufacturing enterprise uses the method described in the embodiments of the present invention for equipment management data statistics. The financial staff of this enterprise wants to export data reports such as the usage duration and repair times of equipment in each workshop in the previous quarter for cost accounting analysis.

[0059] On the reporting system interface, select the appropriate report type, enter export parameters, such as the time range (last quarter), workshop range (all workshops), and select Excel as the target format. Upon receiving the export request, the reporting system's request processing module parses and obtains the report identifier, export parameters, and target format.

[0060] Based on the report identifier, the corresponding report template information is retrieved from the database. For example, a report template may contain a fixed area for displaying information such as the report title and header description, while a variable area presents detailed data for each workshop device. The report style is also extracted, including cell information for the fixed and variable areas, as well as the mapping between data items in the variable area and database fields.

[0061] After checking the report, we found that it contained a variable area. We also found out through database query that the amount of data in the variable area was large, exceeding the user-configured threshold (for example, 500 data records). Therefore, we decided to perform streaming export.

[0062] Streaming export operation: Execute step S41: load fixed area data at one time, such as the report title "Last quarter's workshop equipment data report". At the same time, obtain the variable area data stream based on the export parameters, and read the relevant data of each workshop equipment line by line from the database in a certain order.

[0063] Execute step S42: Create an Excel worksheet and traverse the report template row by row. First, process the fixed area data, filling the fixed area content into the corresponding position of the worksheet according to the template style. Next, process the variable area data stream and fill the data into the variable area of the worksheet in sequence. During the filling process, format attributes such as font, color, and alignment are rendered in real time according to the style information in the template.

[0064] Execute step S43: After the data is filled, dynamically adjust the coordinate range of the merged cell to ensure that the merged cell can still be displayed correctly when the amount of data in the variable area changes. Finally, output the processed worksheet as an Excel file and save it to a specified local folder.

[0065] Conventional export operation (hypothetical scenario): If the report does not contain a variable area, or the amount of data in the variable area is small and does not exceed the user-configured threshold, the export method judgment module will trigger the conventional export process to load all data at once, including data in the fixed area and the variable area, and then directly export the data to a file and save it to the specified location.

[0066] When loading the report template information, execute S21: Read the report template from the database according to the report identifier, and parse the row height of the report (such as the title row height is 20 points, and the body row height is 14 points), column width (such as the equipment name column width is 20 characters, and the usage duration column width is 10 characters), and variable area information. The variable area information includes the row number of the variable row (such as starting from the 5th row), row internal code (used to uniquely identify each row of variable data), and the number of variable rows (it is expected that there are 100 rows of variable data).

[0067] Execute S22: Construct a two-dimensional table of cell information to store the detailed information of each cell. For example, the value of the fixed area title cell is "Report on Equipment Data of Each Workshop in the Previous Quarter", the font is Song typeface, bold 16-point size, the background color is light blue, the border style is thin solid line, and the alignment method is centered; the value of the variable area equipment name cell corresponds to the equipment name field in the database, the font is Song typeface, 12-point size, there is no special setting for the background color, the border style is default, and the alignment method is left-aligned.

[0068] Execute S23: Record the start and end row and column coordinates of the merged cells. For example, for the merged cells in the "Workshop Summary" part of the report, the start row number is the 3rd row, the end row number is the 3rd row, the start column number is the 2nd column, and the end column number is the 5th column.

[0069] Execute S24: Parse the attribute definitions of the data items in the template, and extract the data item number (such as the data item number of the equipment usage duration is 001), the associated database table name (such as the "equipment_usage" table), field name (such as the "usage_time" field), data type (numeric type), and formatting rule (keep two decimal places).

[0070] Execute S25: Construct the mapping relationship between the data item number and the database field, and generate a mapping table. For example, the data item number 001 corresponds to the "usage_time" field in the "equipment_usage" table, and 002 corresponds to the "repair_times" field, etc., which is convenient for subsequent data query and filling.

[0071] During the above enterprise report export process, when executing step S41 to obtain the variable area data stream: Execute S411: Assemble a dynamic SQL query statement according to the generated mapping relationship between the variable area data items and the report template.

[0072] Execute S411a: According to the table name "equipment_usage" and field names "usage_time", "repair_times", etc. in the mapping relationship, determine that the target table for the query is the "equipment_usage" table, and the query fields are related fields such as "usage_time", "repair_times", etc.

[0073] Execute S411b: Convert the export parameters in the user request (time range: last quarter, workshop range: all workshops) into an SQL conditional statement, such as "WHERE usage_date BETWEEN '2024-01-01' AND '2024-03-31' AND workshop IN (SELECT workshop_name FROM workshops)".

[0074] Execute S411c: Generate an SQL that supports streaming reading, such as "SELECT usage_time, repair_times FROM equipment_usage WHERE usage_date BETWEEN '2024-01-01' AND '2024-03-31' AND workshop IN (SELECT workshop_name FROM workshops) ORDER BY equipment_id", so as to read data row by row.

[0075] Execute S412: Using the export parameters as conditions, query and obtain the data stream in the changed area through the generated SQL query statement above, and read the usage duration, repair times, etc. of the equipment in each workshop row by row from the database to prepare for subsequent filling into the changed area of the report.

[0076] When assembling the dynamic SQL query statement (step S411): Execute S411a: Taking the data item of equipment usage duration as an example, according to the mapping table, determine that its corresponding database table name is "equipment_usage" and the field name is "usage_time". When multiple data items need to be queried, add the corresponding field names to the query field list, such as "usage_time, repair_times, equipment_status".

[0077] Execute S411b: Assume that in addition to the time range and workshop range, the export parameters in the user request also include an equipment type filtering condition (such as only exporting production equipment). When converting these parameters into an SQL conditional statement, further improve the WHERE clause, such as "WHERE usage_date BETWEEN '2024-01-01' AND '2024-03-31' AND workshop IN (SELECT workshop_name FROM workshops) AND equipment_type = 'production equipment'".

[0078] Execute S411c: Considering the large amount of data, to improve the reading efficiency, add an ORDER BY clause when generating SQL, sort by equipment ID, and generate a SQL that supports streaming reading, such as "SELECT usage_time, repair_times, equipment_status FROM equipment_usage WHERE usage_date BETWEEN '2024-01-01' AND '2024-03-31' AND workshop IN (SELECT workshop_name FROM workshops) AND equipment_type='production equipment' ORDER BY equipment_id". In this way, when obtaining the data stream in the changed area, data can be efficiently read row by row in order.

[0079] When processing the fixed area data (the part of processing the fixed area data in step S42): Execute S421a: Locate the writing position according to the template row number. For example, the fixed area title row in the report template is on the 1st row. Traverse the cells in this row, such as the title cell, and fill the processed data item value (i.e., the report title "Equipment Data Report for Each Workshop in the Last Quarter") into the cell at the 1st row and 1st column of the worksheet. For the header cells, such as "Equipment Name", "Usage Duration", "Repair Times", etc., fill them into the cells at the 2nd row of the corresponding columns according to the template information.

[0080] Execute S421b: Apply the font, border, and background color styles of the cells in real time. Set the font of the title cell to Song typeface, bold, 16-point size, and apply this font style to the worksheet title cell through the style setting interface of the report system; set the background color to light blue, and also set the background color property through the interface; set the border style to thin solid line and apply it to the border of the title cell according to the template requirements. The header cells are also applied in a similar way, and the font (Song typeface, 12-point size), border (default border style), and background color (no special setting) styles defined in the template are applied in real time to ensure that the format of the fixed area data meets the requirements of the report template.

[0081] When processing the data stream in the changed area (the part of processing the data stream in the changed area in step S42): Execute S422a: Group the data items by row number and read a single record from the data stream. For example, the first record contains data such as the usage duration and repair times of equipment A. According to the generated mapping table, match the field name with the data item number, and determine that the "usage_time" field corresponds to the data item number 001 for the equipment usage duration data item, and the "repair_times" field corresponds to the data item number 002.

[0082] Execute S422b: Fill the data into the corresponding cells of the worksheet according to the row and column positions in the report template. Assume that the data of device A in the variable area of the report starts from row 5. The "usage_time" data is filled into the cell at row 5, column 3, and the "repair_times" data is filled into the cell at row 5, column 4.

[0083] Execute S422c: Perform format conversion on non-empty data items according to the format rules defined for the data items. For example, if the usage duration data of device A is 123.456, it is converted to 123.46 by retaining two decimal places according to the formatting rules and then filled into the cell. When processing the data of the current row, pre-read the next record, such as the data of device B, to improve the data processing efficiency.

[0084] Execute S422d: Assume that 10 rows of variable data have been written. When processing the 11th row of variable data, dynamically adjust the starting row number of the merged cells according to the number of written variable rows (10 rows). If the original starting row number of a merged cell is row 5, during the writing process of the variable area data, its new row number is updated to 5 + 10 = 15 rows, ensuring that the range of the merged cells is correctly adjusted as the variable area data is written.

[0085] When dynamically adjusting the coordinate range of the merged cells (step S43): Execute S431: Traverse the merged cell information, such as the merged cells in the "Workshop Summary" part of the report. Clone the original coordinate range. Assume that the original starting row number is row 3, the starting column number is column 2, the ending row number is row 3, and the ending column number is column 5. If its starting row is within the variable area (assuming the variable area of this report starts from row 5) and 20 rows of variable data have been written, then update the row number of the starting row to: new row number = 3 + 20 = 23 rows, the ending row number remains unchanged (still row 3 because this merged cell only spans one row), and the starting column number and ending column number also remain unchanged.

[0086] Execute S432: Write the adjusted merged range (starting row number 23 rows, starting column number 2 columns, ending row number 3 rows, ending column number 5 columns) into the worksheet to ensure that the display range of the merged cells in the worksheet is correct when the amount of variable area data changes.

[0087] As Figure 3 shown, an embodiment of the present invention further provides a device for processing and exporting large amounts of report data, including a request processing module, a report processing module, an export method determination module, a streaming export module, and a conventional export module; The request processing module is used to receive a report export request initiated by a user, and parse the request content to obtain a report identifier, export parameters, and a target format; A report processing module, which is used to load report template information corresponding to the report identifier, extract report styles, including cell information in the fixed area and variable area, and the mapping relationship between data items in the variable area and database fields; An export method determination module, which is used to check whether the report contains a variable area. If it contains a variable area and the data volume in the variable area exceeds the threshold configured by the user, it triggers the streaming export module; otherwise, it triggers the regular export module; A streaming export module, specifically used to load fixed area data at one time, and obtain a data stream for the variable area based on export parameters; create a worksheet, traverse the report template row by row, process the fixed area data and the data stream for the variable area in sequence, and render format attributes in real time; dynamically adjust the coordinate range of merged cells, and output the processed worksheet to a file; A regular export module, which is used to load all data at one time and export it to a file.

[0088] In some embodiments, the report processing module includes a template parsing unit, a two-dimensional table construction unit, a recording unit, and a mapping relationship construction unit; The template parsing unit is used to read report template information from a database or configuration file according to the report identifier, parse the row height, column width, and variable area information. The variable area information includes the row number, row internal code, and number of variable rows of the variable row; parse the attribute definitions of data items in the template, and extract the data item number, associated database table name and field name, data type, and formatting rules; The two-dimensional table construction unit is used to construct a two-dimensional table of cell information, and store the value, font, background color, border style, and alignment method of the cell; The recording unit is used to record the start and end row and column coordinates of merged cells; The mapping relationship construction unit is used to construct the mapping relationship between the data item number and the database field, that is, the mapping relationship between the data item in the variable area and the report template, and generate a mapping table.

[0089] In some embodiments, the template parsing unit is specifically used to load a row dictionary to obtain the height value of each row and the width value of each column; traverse the row dictionary, and extract the row number, row internal code, and number of variable rows where the variable row is located to construct variable area information.

[0090] In some embodiments, the streaming export module includes a data acquisition unit, which is used to load fixed area data at one time, and obtain a data stream for the variable area based on export parameters; The data acquisition unit includes a query statement assembly sub-module and a data stream acquisition sub-module; The query statement assembly sub-module is used to assemble a dynamic SQL query statement according to the mapping relationship between the data item in the variable area and the report template; A data stream acquisition sub-module, configured to query and acquire the data stream of the changed area by means of an SQL query statement with the export parameters as conditions.

[0091] In some embodiments, the query statement assembly sub-module is specifically configured to determine the target table and fields to be queried according to the table names and field names in the mapping relationship; convert the export parameters in the user request into an SQL conditional statement; and generate an SQL that supports streaming reading.

[0092] In some embodiments, the streaming export module includes a data processing unit, configured to create a worksheet, traverse the report template row by row, process the fixed area data and the data stream of the changed area in sequence, and render the format attributes in real time; The data processing unit includes a fixed area processing sub-module, configured to locate the writing position according to the template row number, traverse the cells and fill in the processed data item values; and apply the font, border and background color styles of the cells in real time.

[0093] The data processing unit includes a changed area processing sub-module, which groups the data items by row number, reads a single record from the data stream, matches the field name with the data item number according to the mapping table; fills the data into the corresponding cells of the worksheet according to the row and column positions in the report template; performs format conversion on the non-empty data items according to the format rules defined by the data items, and pre-reads the next data; and dynamically adjusts the starting row number of the merged cells according to the number of changed rows that have been written.

[0094] In some embodiments, the streaming export module further includes a dynamic adjustment unit, configured to traverse the merged cell information, clone the original coordinate range, and if its starting row is located in the changed area, update the starting row number to: new row number = original row number + number of changed rows that have been written; and write the adjusted merged range into the worksheet.

[0095] An embodiment of the present invention further provides an electronic device, which includes: a processor, a communication interface, a memory, and a communication bus. Among them, the processor, the communication interface, and the memory complete mutual communication through the communication bus. The communication bus can be used for information transmission between the electronic device and the sensor. The processor can call the logical instructions in the memory to execute the following method: S1: Receive a report export request initiated by the user, and parse the request content to obtain a report identifier, export parameters, and a target format; S2: Load the report template information corresponding to the report identifier, and extract the report style, including cell information in the fixed area and the variable area, as well as the mapping relationship between the data items in the variable area and the database fields; S3: Check whether the report contains a variable area. If it contains a variable area and the data volume in the variable area exceeds the threshold configured by the user, then execute step S4; otherwise, execute step S5; S4: Perform streaming export. The specific steps include: S41: Load the fixed area data at one time, and obtain the variable area data stream based on the export parameters; S42: Create a worksheet, traverse the report template row by row, process the fixed area data and the variable area data stream in sequence, and render the format attributes in real time; S43: Dynamically adjust the coordinate range of the merged cells, and output the processed worksheet to a file; S5: Perform regular export: Load all the data at one time and export it to a file.

[0096] In addition, when the logical instructions in the above-mentioned memory can be implemented in the form of software functional units and sold or used as an independent product, they can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or a part of this technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in various embodiments of the present invention. And the aforementioned storage medium includes: USB flash drives, mobile hard disks, read-only memories (ROM, Read-Only Memory), random access memories (RAM, Random Access Memory), magnetic disks, or optical discs, etc., which can store program codes.

[0097] An embodiment of the present invention provides a non-transitory computer-readable storage medium. The non-transitory computer-readable storage medium stores computer instructions, and the computer instructions cause the computer to execute the method provided by the above method embodiment, for example, including: S1: Receive a report export request initiated by a user, and parse the request content to obtain a report identifier, export parameters, and a target format; S2: Load the report template information corresponding to the report identifier, and extract the report style, including cell information in the fixed area and the variable area, and the mapping relationship between the data items in the variable area and the database fields; S3: Check whether the report contains a variable area. If it contains a variable area and the data volume in the variable area exceeds the threshold configured by the user, then execute step S4; otherwise, execute step S5; S4: Perform streaming export, and the specific steps include: S41: Load the fixed area data at one time, and obtain the variable area data stream based on the export parameters; S42: Create a worksheet, traverse the report template row by row, process the fixed area data and the variable area data stream in sequence, and render the format attributes in real time; S43: Dynamically adjust the coordinate range of the merged cells, and output the processed worksheet to a file; S5: Perform conventional export: Load all the data at one time and export it to a file.

[0098] The foregoing 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 readily 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. Thus, the present invention is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for processing and exporting large amounts of report data, characterized in that, It includes the following steps: S1: Receive the report export request initiated by the user, parse the request content to obtain the report identifier, export parameters, and target format; S2: Load the report template information corresponding to the report identifier, and extract the report style, including the cell information of the fixed area and the variable area, and the mapping relationship between the data items in the variable area and the database fields; S3: Check whether the report contains a variable area. If it contains a variable area and the data volume in the variable area exceeds the threshold configured by the user, then execute step S4; Otherwise, execute step S5; S4: Perform streaming export. The specific steps include: S41: Load the fixed area data at one time, and obtain the variable area data stream based on the export parameters; S42: Create a worksheet, traverse the report template row by row, process the fixed area data and the variable area data stream in sequence, and render the format attributes in real time; S43: Dynamically adjust the coordinate range of the merged cells, and output the processed worksheet to a file; S5: Perform conventional export: Load all the data at one time and export it to a file.

2. The method for processing and exporting large-volume report data according to claim 1, wherein Specifically included in step S2 are: S21: Read the report template information from the database or configuration file according to the report identifier, and parse the row height, column width, and variable area information. The variable area information includes the row number, row internal code, and number of variable rows of the variable row; S22: Construct a two-dimensional table of cell information, and store the value, font, background color, border style, and alignment method of the cell; S23: Record the start and end row and column coordinates of the merged cells; S24: Parse the attribute definition of the data items in the template, and extract the data item number, associated database table name and field name, data type, and formatting rule; S25: Construct the mapping relationship between the data item number and the database field, that is, the mapping relationship between the data items in the variable area and the report template, and generate a mapping table.

3. The method for processing and exporting large-volume report data according to claim 2, characterized in that, In step S41, the steps to obtain the variable area data stream include: S411: Assemble a dynamic SQL query statement according to the mapping relationship between the data items in the variable area and the report template; S412: Use the export parameters as conditions, and query and obtain the variable area data stream through the SQL query statement.

4. The method for processing and exporting large amounts of report data according to claim 3, wherein Specifically included in step S411 are: S411a: Determine the target table and fields to be queried according to the table name and field name in the mapping relationship; S411b: Convert the export parameters in the user request into SQL conditional statements; S411c: Generate SQL that supports streaming reading.

5. The method for processing and exporting large-volume report data according to claim 4, characterized in that, In S42, processing the fixed area data includes: S421a: Locate the writing position according to the template row number, traverse the cells and fill in the processed data item values; S421b: Apply the font, border, and background color styles of the cells in real time.

6. The method for processing and exporting big data volume report data according to claim 5, characterized in that, In S42, the steps to process the variable area data stream include: S422a: Group the data items by row number, read a single record from the data stream, and match the field name and data item number according to the mapping table; S422b: Fill the data into the corresponding cells of the worksheet according to the row and column positions in the report template; S422c: Perform format conversion on the non-empty data items according to the format rules defined by the data items, and pre-read the next data; S422d: Dynamically adjust the start row number of the merged cells according to the number of variable rows that have been written.

7. The method for processing and exporting big data volume report data according to claim 6, wherein In S43, the steps of dynamically adjusting the coordinate range of merged cells include: S431: Traverse the merged cell information, clone the original coordinate range. If the starting row is within the changed area, update the row number of the starting row to: new row number = original row number + the number of changed rows that have been written; S432: Write the adjusted merged range to the worksheet.

8. An apparatus for processing and exporting large amounts of report data, characterized in that, It includes a request processing module, a report processing module, an export method determination module, a streaming export module, and a conventional export module; The request processing module is used to receive the report export request initiated by the user, and parse the request content to obtain the report identifier, export parameters, and target format; The report processing module is used to load the report template information corresponding to the report identifier, and extract the report style, including the cell information of the fixed area and the changed area, and the mapping relationship between the data items in the changed area and the database fields; The export method determination module is used to check whether the report contains a changed area. If it contains a changed area and the data volume in the changed area exceeds the threshold configured by the user, trigger the streaming export module; Otherwise, trigger the conventional export module; The streaming export module is specifically used to load the fixed area data at one time, and obtain the data stream of the changed area based on the export parameters; Create a worksheet, traverse the report template row by row, process the fixed area data and the data stream of the changed area in sequence, and render the format attributes in real time; dynamically adjust the coordinate range of the merged cells, and output the processed worksheet to a file; The conventional export module is used to load all the data at one time and export it to a file.

9. An electronic device, characterized in that, The electronic device includes: at least one processor; and a memory communicatively connected to the at least one processor; the memory stores computer program instructions executable by the at least one processor, and the computer program instructions are executed by the at least one processor so that the at least one processor can execute the method for processing and exporting large-volume report data as described in any one of claims 1 to 7.

10. A non-transitory computer-readable storage medium, characterized in that, The non-transitory computer-readable storage medium stores computer instructions, and the computer instructions cause the computer to execute the method for processing and exporting large-volume report data as described in any one of claims 1 to 7.

Images