Method and device for importing reports in batches, medium and product

Abstract

The invention discloses a method and device for importing reports in batches, a medium and a product, and relates to the field of data processing, and the method comprises the following steps: establishing an import result table and an import failure record table; establishing a database table corresponding to the report; preprocessing the original report template according to the database table to obtain a preprocessed report template; filling report data according to the preprocessed report template to obtain a report file; the business personnel packages the report file to generate a compressed file package, and uploads the compressed file package to a background system; the background system decompresses the compressed file package to obtain a report file, analyzes the report file and updates an import result table and an import failure record table; and querying the import result of each report and report information of import failure according to the import result table and the import failure record table. According to the method, the equipment, the medium and the product for importing the reports in batches, a scene of newly adding the reports can be flexibly coped with, shutdown release is not needed, and business personnel can conveniently carry out importing operation.

Description

Technical Field

[0001] The present application relates to the field of data processing, and in particular to a method, device, medium and product for batch importing reports. Background Art

[0002] In some systems (such as reporting systems), a large amount of Excel report data needs to be imported every month, and some new reports will be added from time to time. The usual implementation method is: develop a corresponding program for each report, parse the data and store it in the corresponding database table. Every time a new report is added, a corresponding database table needs to be created, and a new program needs to be developed to parse the report data and store it in this database table. The database table can be published online, but the parsing program needs to be shut down to publish the new program before it can take effect. For many systems, the shutdown publishing process is relatively strict (it is necessary to schedule a publishing plan and apply in advance, and it can only be shut down after approval at all levels). When a new report is added, it takes a long time to take effect, which is inefficient. If there is a scenario where new reports are added frequently, the contradiction will be more prominent. When importing, business personnel need to perform import operations on the interface of each report. When the number of reports to be imported is large, importing reports one by one will take a long time and be inconvenient to operate. Summary of the invention

[0003] The purpose of this application is to provide a method, device, medium and product for batch importing reports, which can flexibly cope with the scenario of adding new reports, do not need to stop the system for publishing, and facilitate business personnel to perform import operations.

[0004] To achieve the above objectives, this application provides the following solutions:

[0005] In a first aspect, the present application provides a method for batch importing reports, comprising:

[0006] Establish an import result table and an import failure record table; the import result table is used to record the import results of each report; the import failure record table is used to record the report information of failed import;

[0007] Create a database table corresponding to the report;

[0008] Preprocessing the original report template according to the database table to obtain a preprocessed report template;

[0009] Fill in report data according to the pre-processed report template to obtain a report file;

[0010] Business personnel package the report files, generate compressed file packages, and upload them to the backend system;

[0011] The backend system decompresses the compressed file package to obtain the report file, parses the report file, and updates the import result table and the import failure record table;

[0012] Query the import results of each report and the report information of failed imports based on the import result table and import failure record table.

[0013] Optionally, the fields of the import result table include: importer, import time, report name, number of successes and number of failures; the fields of the import failure record table include: importer, import time, report name, failure reason and failure data.

[0014] Optionally, create a database table corresponding to the report, including:

[0015] For each report, a corresponding database table is established in the database; the database table is used to store report data.

[0016] Optionally, the pre-processed report template includes N+3 rows, and the first 3 rows are locked and hidden; wherein N is the number of report header rows in the original report template;

[0017] The first row is the fixed value "database table", the name of the database table corresponding to the report, the fixed value "data start row", the data start row in the pre-processed report template, the fixed value "total number of data columns", the total number of data columns in the original report template, the fixed value "update field" and the update field expressed in time used by the database table;

[0018] The second line contains the data types of the database table fields corresponding to the fields in the original report template;

[0019] The third line contains the names of the database table fields corresponding to the fields in the original report template;

[0020] Lines 4 to N+3 are consistent with the report header in the original report template.

[0021] Optionally, fill in report data according to the pre-processed report template to obtain a report file, including:

[0022] According to the pre-processed report template, fill in the report data starting from row N+4 to obtain the report file.

[0023] Optionally, the business personnel package the report files, generate a compressed file package, and upload it to the backend system, including:

[0024] Business personnel package all report files within the set time period, generate a compressed file package, and upload it to the backend system through the set upload page.

[0025] Optionally, the background system decompresses the compressed file package to obtain a report file, parses the report file, and updates the import result table and the import failure record table, including:

[0026] The backend system decompresses the compressed file package to obtain the report file;

[0027] Parse the first three lines of the report file to read the database table name, database table field name and data type, update field, data start row and total number of data columns;

[0028] A preprocessing SQL statement string is concatenated according to the database table name and the database table field name, and a preprocessing object is determined according to the preprocessing SQL statement string;

[0029] Create a database table deletion statement based on the database table name, updated field, and the year and month of the compressed file package upload, and execute the database table deletion statement to clean up historical data;

[0030] According to the data start row, the total number of data columns and the update field, the report data in the report file is parsed line by line from the N+4th row to the maximum number of data rows, and the report data is inserted into the database table according to the pre-processing object and the data type of the database table field, and the import failure record table is updated; wherein, for any row of report data: when the update field is not equal to the upload year and month, the row of report data is not inserted into the database table, and the number of failures is increased by 1, and the reason for failure is that the update field is not equal to the specified value, and the failure data is the string after the row of report data is spliced; when the update field is equal to the upload year and month, the row of report data is inserted into the database table according to the pre-processing object and the data type of the database table field. If the insertion is successful, the number of successful entries is increased by 1, and if the insertion fails, the number of failures is increased by 1, and the reason for failure is the abnormal information captured by the background system, and the failure data is the string after the row of report data is spliced;

[0031] After the loop executes all data rows, the import result table is updated; the importer is the current operator, the import time is the current system timestamp, the report name is the name of the report file, and the number of successful and failed entries is accumulated by the background system.

[0032] In a second aspect, the present application provides a computer device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the method for batch importing reports.

[0033] In a third aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, which implements the method for batch importing reports when executed by a processor.

[0034] In a fourth aspect, the present application provides a computer program product, including a computer program, which implements the method for batch importing reports when executed by a processor.

[0035] According to the specific embodiments provided in this application, this application discloses the following technical effects:

[0036] The present application provides a method, device, medium and product for batch importing reports, including: establishing an import result table and an import failure record table; establishing a database table corresponding to the report; preprocessing the original report template according to the database table to obtain a preprocessed report template; filling in the report data according to the preprocessed report template to obtain a report file; the business personnel pack the report file, generate a compressed file package, and upload it to the background system; the background system decompresses the compressed file package to obtain the report file, parses the report file, updates the import result table and the import failure record table; and queries the import results of each report and the report information of the import failure according to the import result table and the import failure record table. Through the above method, when importing the report file, the compressed file package can be imported at one time, which reduces the operation workload of the business personnel compared to importing a single report file one by one, thereby improving the import efficiency. When there is a new report, the system administrator only needs to establish the corresponding database table and process the new template in the database to take effect without stopping the release. For some systems that cannot stop at any time to release new versions, it is more convenient and flexible. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the technical solutions in the embodiments of the present application or related technologies, the drawings required for use in the embodiments 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.

[0038] Figure 1 A flow chart of a method for batch importing reports provided for this application;

[0039] Figure 2 Sample diagram of departmental budget tables and report data pre-processed for this application;

[0040] Figure 3 Pre-processed commodity sales statistics and report data example diagrams provided for this application;

[0041] Figure 4 Schematic diagram of the file upload page provided for this application. DETAILED DESCRIPTION

[0042] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions 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 this field without creative work are within the scope of protection of this application.

[0043] In order to make the above-mentioned objects, features and advantages of the present application more obvious and easy to understand, the present application is further described in detail below in conjunction with the accompanying drawings and specific implementation methods.

[0044] In an exemplary embodiment, the present application provides a method for batch importing reports. In the embodiment of the present application, Figure 1 As shown, the method includes the following steps 1 to 7.

[0045] Step 1: Create an import result table and an import failure record table.

[0046] Import result table: used to record the import results of each report, such as how many records succeeded and how many records failed. This table includes the following fields: importer, import time, report name, number of successful records, and number of failed records.

[0047] Import failure record table: used to record the report information of failed import, that is, the detailed records of each report import failure. This table includes the following fields: importer, import time, report name, failure reason, and failure data. The maximum length of the failure data is 1000 characters.

[0048] Step 2: Create the database table corresponding to the report.

[0049] For each report, a corresponding database table is established in the database to store the report data.

[0050] Step 3: Preprocess the original report template according to the database table to obtain a preprocessed report template.

[0051] All original report templates are preprocessed in combination with the corresponding database table structure. The preprocessed report template includes N+3 rows, and the first 3 rows are locked and hidden; where N is the number of report header rows in the original report template. The first row is the fixed value "database table", the name of the database table corresponding to the report, the fixed value "data start row", the data start row in the preprocessed report template, the fixed value "total number of data columns", the total number of data columns in the original report template, the fixed value "update field" and the update field expressed in time used by the database table; the second row is the data type of the database table field corresponding to each field in the original report template; the third row is the name of the database table field corresponding to each field in the original report template; the fourth to N+3 rows are consistent with the report header in the original report template.

[0052] Take the department budget table (original report template) shown in Table 1 as an example, in which the report header is fixed content and is located in the first row. Business personnel regularly (for example, monthly) add the budget expenses, actual expenses, remaining budget and budget utilization rate data of each department for a specified month to this report (Excel file) (see rows 2 and 3, called business data, there will be multiple rows of business data, only 2 rows of samples are listed here). It should be noted that the various reports provided in this application are for illustrative purposes only, and the data does not represent the actual business meaning and rationality.

[0053] Table 1 Department budget table

[0054]

[0055] In order to record the report data, the administrator needs to create a corresponding table in the background database. For example, the table name is ODS.T_DEPT_BUDGET. The table needs to contain all the columns in the original report and add Chinese comments to each column. The pseudo code (only for the convenience of explaining the structure and data type of the data table, not the actual executable SQL statement for creating the table) is as follows:

[0056] create table ODS.T_DEPT_BUDGET (

[0058] DEPT_NAME varchar(100) COMMENT Department,

[0059] YEAR_MONTH char(6)COMMENT Year and month format YYYYMM,

[0060] BUDGET_AMT float COMMENT budget cost (yuan),

[0061] ACT_AMT float COMMENT actual cost (yuan),

[0062] REMAINING_BUDGET float COMMENT remaining budget (yuan),

[0063] BUDGET_RATE float COMMENT Budget utilization rate (%) )

[0065] According to the original report template and the structure of the database table, the pre-processed report template is shown in Table 2 (the pre-processed report template does not contain the business data in the original report):

[0066] Table 2 Preprocessed department budget table

[0067]

[0068] The specific process of preprocessing the original report template according to the database table is as follows:

[0069] 3.1: In the first row, fill in the database table, update field, data start row and total number of data columns corresponding to the report.

[0070] 3.1.1: The first cell is a fixed value "database table".

[0071] 3.1.2: The second cell is the name of the background database table corresponding to this report, in this case ODS.T_DEP T_BUDGET.

[0072] 3.1.3: The third cell is the fixed value "Data start row".

[0073] 3.1.4: The 4th cell is the number of rows where the business data of this template starts. The parser will start from this row and insert the read data into the corresponding database table. The first 3 rows in the pre-processed report template are fixed auxiliary description items. The number of report header rows in the original report template may be 1 or more (this number of rows is marked as N). The number of rows where the business data starts is obtained according to the formula N+3+1. In this example, the business report header has only 1 row (N=1), so the number of rows where the data starts is 5.

[0074] 3.1.5: The fifth cell is the fixed value "Total number of data columns".

[0075] 3.1.6: The 6th cell is the maximum number of columns for this template data. Cell data exceeding this value will be ignored. Fill in the number of fields in the actual business report. In this example, the number of fields in the original report template is 6.

[0076] 3.1.7: The 7th cell is the fixed value "Update Field".

[0077] 3.1.8: The 8th cell is the field used to update this database table (it must be a field like year or year-month. If the database table does not have a field like year or year-month, leave it blank). In this example, it is YEAR_MONTH. Each time you import data, you will first clean up the historical data according to this field, and then write the Excel data. In actual scenarios, you will import multiple times, so each time you import, you need to clear the historical data in the database table first, and then write the data in the Excel file to the database table. If this cell is empty, all the data in the data table will be cleared during the import, and then the data in the Excel file will be written.

[0078] 3.2: In the second line, fill in the data types of the corresponding database table fields in the order of the report fields in the original report template, such as char(n), varchar(n), DATE, INT, etc. In this example, they are char(6), varchar(100), float, float, float, float, float.

[0079] 3.3: In the third line, fill in the names of the corresponding database table fields in the order of the report fields in the original report template. In this example, they are DEPT_NAME, YEAR_MONTH, BUDGET_AMT, ACT_AMT, REMAINING_BUDGET, and BUDGET_RATE.

[0080] 3.4: The 4th to N+3rd rows (N is the number of rows in the report header in the original report template) are consistent with the report header in the original report template. You can use the copy and paste method to paste the report header in the original report template. In this example, the report header in the original report template has only one row, so only the 4th row is occupied in the pre-processed report template. Similarly, if the report header in the original report template has 3 rows, then the 4th to 6th rows will be occupied in the pre-processed report template.

[0081] 3.5: After the settings are completed, the first three rows of the pre-processed report template are locked and hidden, which is transparent to the business operators and appears to be consistent with the original report template. When new reports need to be imported later, the system administrator only needs to follow steps 2 and 3 to create the corresponding database table and report template, and the business personnel can follow the subsequent steps to import Excel data without shutting down the system for release.

[0082] Step 4: Fill in the report data according to the pre-processed report template to obtain the report file.

[0083] According to the pre-processed report template, fill in the report data starting from row N+4 to obtain the report file.

[0084] In this embodiment, in daily work, business personnel use pre-processed report templates to fill in various report data. Since the pre-processed report template looks the same as the original report template, it will not affect the daily work of business personnel to fill in reports or the impact is very small. Figure 2 The following is a department budget table for August 2024 filled in by business personnel (Note: the first three rows are locked and hidden in actual use, and the 5th to 9th rows are business data filled in by business personnel). Figure 3 Shown is the commodity sales statistics table for May 2024 filled in by business personnel (Note: the first 3 rows are locked and hidden in actual use, and the 6th to 9th rows are business data filled in by business personnel).

[0085] Step 5: The business personnel packages the report files, generates a compressed file package, and uploads it to the backend system.

[0086] Business personnel package all report files within the set time period, generate a compressed file package, and upload it to the backend system through the set upload page.

[0087] In this embodiment, when business personnel import data every month, they package all report Excel files that need to be imported, generate a zip or rar compressed file package, and upload this compressed file package by specifying the upload page, selecting the year and month conditions (in this example, 202408). Figure 4 As shown, because the upload is monthly, the upload page will include year and month conditions to distinguish which year and month the uploaded Excel data belongs to.

[0088] Step 6: The backend system decompresses the compressed file package to obtain the report file, parses the report file, and updates the import result table and the import failure record table.

[0089] The backend system decompresses the compressed file package to obtain the report file (i.e., Excel data file); parses the first three lines of the report file to read the database table name, the name and data type of the database table field, the update field, the data start row, and the total number of data columns; splices the preprocessing SQL statement string according to the database table name and the name of the database table field, and determines the preprocessing object according to the preprocessing SQL statement string; splices the database table deletion statement according to the database table name, update field, and the year and month of the compressed file package upload, and executes the database table deletion statement to clean up historical data; parses the report data in the report file line by line from the N+4th row to the maximum number of data rows according to the data start row, the total number of data columns, and the update field, and inserts the report data into the database table according to the preprocessing object and the data type of the database table field, and the update import fails Record table; wherein, for any row of report data: when the update field is not equal to the upload year and month, the row of report data is not inserted into the database table, and the number of failures is set to +1, the reason for failure is that the update field is not equal to the specified value, and the failure data is the string after the row of report data is spliced; when the update field is equal to the upload year and month, the row of report data is inserted into the database table according to the data type of the preprocessing object and the database table field. If the insertion is successful, the number of successful entries is set to +1, if the insertion fails, the number of failed entries is set to +1, the reason for failure is the abnormal information captured by the background system, and the failure data is the string after the row of report data is spliced; after the loop executes all data rows, the import result table is updated; wherein, the importer is the current operator, the import time is the current system timestamp, the report name is the name of the report file, and the number of successful entries and the number of failed entries are accumulated by the background system.

[0090] by Figure 2 Taking the department budget table shown in the figure as an example, the detailed description is as follows:

[0091] 6.1: Through the universal method of 3rd party tools, the value of any cell in the Excel file can be read. For example, the general process of parsing using POI's jar package is as follows: first, get the file input stream (marked as fin) through new FileInputStream (Excel file path), then get the workspace of the Excel file (the collection of all sheet pages of the Excel file, marked as workbook) through new XSSFWorkbook (fin), and then get the first sheet page of the Excel file (marked as sheet, note: the sheet page sequence starts from 0, this method only parses the first sheet page of the Excel file) through workbook.getSheetAt(0), then get the specified row (marked as row) through sheet.getRow (row number), get the specified cell of the row (marked as cell) through row.getCe ll (column number), get the data type of this cell (such as string type, numeric type, etc.) through cell.getCellType(), and finally get the cell value by judging the data type and taking the corresponding method (for example, use cell.getString CellValue() for string type). Since these methods are standard methods of the POI tool, they are not described in detail.

[0092] 6.2: Parse the first three lines of the Excel file to read the database table name, database table fields and data types, update fields, data start row, total number of data columns, and other information. Combined with this example, the description is as follows:

[0093] 6.2.1: Read the second cell in the first row to get the database table name ODS.T_DEPT_BUDGET, and the sixth cell to get the total number of data columns as 6. Then read the first 6 cells in the third row according to the total number of data columns 6, and get the field list of the database table as (DEPT_NAME, YEAR_MONTH, BUDGET_AM T, ACT_AMT, REMAINING_BUDGET, BUDGET_RATE). Then, according to the JDBC standard syntax, assemble the preprocessed SQL statement string (marked as preSql): insert intoODS.T_DEP T_BUDGET(DEPT_NAME, YEAR_MONTH, BUDGET_AMT, ACT_AMT, REMAI NING_BUDGET, BUDGET_RATE) values(?,?,?,?,?,?,?), where the number of ? is consistent with the total number of columns. Then, a preprocessing object PreparedStatement (marked as ps) is obtained through the Connection..PreparedStatement(preSql) method in the JDBC package, which is used when the Excel data is written to the database later.

[0094] 6.2.2: Clean up historical data: Before writing to the Excel data file, you need to clean up the historical data in the database table to support multiple repeated import operations. According to the database table name ODS.T_DEPT_BUDGET, the update field YEAR_MONTH, and the year and month value 202408 selected on the upload page, you can splice the database table deletion statement according to the standard SQL syntax, delete from ODS.T_DEPT_BUDGET where YEAR_MONTH='202408'; execute this statement to delete the data of 202408 in the ODS.T_DEPT_BUDGET table, that is, the historical data. If the update field is empty, the spliced ​​deletion statement will be delete from ODS.T_DEPT_BUDGET, which will delete all the data in the table after execution.

[0095] 6.2.3: Read the 4th cell in the 1st row and get the starting row number 5; read the first 6 cells (the maximum number of cells obtained previously) in the 2nd row and get the field data type list (varchar(100), char(6), float, float, float, float). This data will be used in the subsequent steps.

[0096] 6.3: Parse the Excel file data, starting with row 5, total number of data columns 6, and updating the field YEAR_MONTH according to the data obtained in the previous step. By comparing the database fields in row 3, the cell number corresponding to YEAR_MONTH in row 3 is 2. The maximum number of data rows in the Excel file MAX (9 in this case) is obtained through the sheet.getLastRow() method (sheet obtained in step 6.1). Therefore, from row 5 to row 9, parse the cell data from column 1 to column 6 line by line, as follows:

[0097] 6.3.1: Check the update field YEAR_MONTH (if the update field is empty, no check is required). If the value of the second column of the row is not equal to the upload year and month 202408 (for example, the second column of the 9th row in this example is 202407), then the data in this row will not be written to the database table ODS.T_DEPT_BUDGET. Add 1 to the number of failures and add a record to the import failure information table, where the failure reason is "the update field is not equal to the specified value", the data record is the string of the values ​​of each cell concatenated (in this example, XXX Business Department, 202407, 270000, 258500, 11500, 95.74. If the total length after concatenation exceeds 1000 digits, the first 1000 digits are truncated), the importer is the current operator (for example, Zhang San), the import time is the current system timestamp (for example, 20240831142521), and the report name is the name of the current Excel file (in this example, the department budget table).

[0098] 6.3.2: Insert data. If the value of the second column of the row is equal to the uploaded year and month value 202408, for example, rows 5 to 8 in this example (if the update field is empty, no check is required), according to the data type corresponding to each cell (varchar(100), char(6), float, float, float, float in this example), select the appropriate ps (obtained in step 6.2.1) to set parameters according to the standard usage in the JDBC package (the number of parameters required is the same as the total number of columns, which is 6 in this example). If column 1 is For varchar(100), use ps.setString(1, the cell value in the first column); for char(6), use ps.setString(2, the cell value in the second column); for float, use ps.setFloat(3, the cell value in the third column), and so on. Set 6 parameters (use the parameter setting method according to the specific data type. This is the common usage of JDBC. For details, please refer to the JDBC document and will not be listed in detail here), then execute the ps.execute() method to insert data. If the insertion is successful, the number of successful records +1; if the insertion fails, the number of failed records +1, and a record is added to the import failure information table. The failure reason is the abnormal information captured by the system (for example, the length of the department field exceeds the maximum length of 100 specified by the database, resulting in failure to write to the database; or the value of the budget expense field is 1a00, the database requires a numeric value, but it actually contains characters, resulting in failure to write to the database, etc.), the data record is a string of the concatenated values ​​of each cell (if the total length after concatenation exceeds 1000 digits, the first 1000 digits are truncated), the importer is the current operator (for example, Zhang San), the import time is the current system timestamp (for example, 20240831142521), and the report name is the name of the current Excel file (in this case, the department budget table).

[0099] 6.3.3: After the loop executes all rows (rows 5 to 9 in this example), insert a piece of information into the import result table. The importer is the current operator (for example, Zhang San), the import time is the current system timestamp (for example, 20240831142521), the report name is the name of the current Excel file (department budget table in this example), and the number of successful and failed records is the accumulated number in the above steps.

[0100] In addition, for Figure 3For the commodity sales statistics table shown, assuming that May 2024 is selected in the upload interface, the preprocessing SQL statement string is preSQL = insert into ODS.T_SALES_STAT(sale_date,product_name,dept_name,unit_price,sales_quantity,sales_amount,sales_region)values(?,?,?,?,?,?,?,?), the preprocessing object is ps = Connection..PreparedStatement(preSql), and the database table deletion statement is delete from ODS.T_SALES_STAT where sale_date = '202405'. When checking and updating fields, parse from rows 6 to 9 one by one. Taking row 7 as an example, set the parameters of ps according to the field data type as ps.setString(1,"202405"), ps.setString(2,"Air Conditioning"), ps.setString(3,"E-commerce Department 2"), ps.setBigDecimal(4,8000), ps.setInt(5,200), ps.setBigDecimal(6,160.00), ps.setString(7,"South China"). The remaining operations are basically the same as those for the department budget table, which will not be described in detail here.

[0101] Step 7: Query the import results of each report and the report information of failed imports based on the import result table and import failure record table.

[0102] After parsing all files, you can query the results of this import through the specified page, including the number of successful and failed imports, the importer, and the import time (data is obtained from the import result table). Click the number of failed imports data to pop up a new page to display the detailed information of the number of failed imports for this report, including the failure reason, data record, importer, import time, etc. (data is obtained from the import failure record table).

[0103] In summary, the present application provides a method for batch importing reports. This method can be used to import Excel data files, and compressed file packages can be imported at one time. Compared with importing individual files one by one, the operating workload of business personnel is reduced, thereby improving the import efficiency. The report template processed by the system administrator is not much different from the template usually used by business personnel. It only adds 3 lines of configuration information, and is locked and hidden. It is transparent to business personnel and will not affect the business personnel's usual filling of reports. When there is a new report, the system administrator only needs to create a corresponding database table in the database and process the new template to take effect. There is no need to shut down for release. For some systems that cannot be shut down at any time to release new versions, it is more convenient and flexible.

[0104] In an exemplary embodiment, the present application further provides a computer device, including a memory and a processor, wherein a computer program is stored in the memory, and the processor implements the steps in the above-mentioned method embodiments when executing the computer program.

[0105] In an exemplary embodiment, the present application further provides a computer-readable storage medium storing a computer program, which implements the steps in the above-mentioned method embodiments when executed by a processor.

[0106] In an exemplary embodiment, the present application further provides a computer program product, including a computer program, which implements the steps in the above-mentioned method embodiments when executed by a processor.

[0107] In this application, all actions to obtain signals, information or data are carried out in compliance with the relevant data protection laws and policies of the country where the application is located, and with the authorization of the corresponding device owner. It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant legal provisions.

[0108] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM may be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM).

[0109] The database involved in each embodiment provided in this application is a relational database. The processor involved in each embodiment provided in this application can be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic unit, a data processing logic unit based on quantum computing, etc., but is not limited thereto.

[0110] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0111] This article uses specific examples to illustrate the principles and implementation methods of this application. The description of the above embodiments is only used to help understand the method and core ideas of this application. At the same time, for those skilled in the art, according to the ideas of this application, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as limiting this application.

Claims

1. A method for batch importing reports, characterized in that: include: Create an import result table and an import failure record table; The import result table is used to record the import results of each report; The import failure record table is used to record report information of import failure; Create a database table corresponding to the report; Preprocessing the original report template according to the database table to obtain a preprocessed report template; Fill in report data according to the pre-processed report template to obtain a report file; Business personnel package the report files, generate compressed file packages, and upload them to the backend system; The backend system decompresses the compressed file package to obtain the report file, parses the report file, and updates the import result table and the import failure record table; Query the import results of each report and the report information of failed imports based on the import result table and import failure record table.

2. The method for batch importing reports according to claim 1, characterized in that: The fields of the import result table include: importer, import time, report name, number of successes and number of failures; the fields of the import failure record table include: importer, import time, report name, failure reason and failure data.

3. The method for batch importing reports according to claim 1, characterized in that: Create the database table corresponding to the report, including: For each report, a corresponding database table is established in the database; the database table is used to store report data.

4. The method for batch importing reports according to claim 1, characterized in that: The pre-processed report template includes N+3 rows, and the first 3 rows are locked and hidden; wherein N is the number of report header rows in the original report template; The first row is the fixed value "database table", the name of the database table corresponding to the report, the fixed value "data start row", the data start row in the pre-processed report template, the fixed value "total number of data columns", the total number of data columns in the original report template, the fixed value "update field" and the update field expressed in time used by the database table; The second line contains the data types of the database table fields corresponding to the fields in the original report template; The third line contains the names of the database table fields corresponding to the fields in the original report template; Lines 4 to N+3 are consistent with the report header in the original report template.

5. The method for batch importing reports according to claim 4, characterized in that: Fill in the report data according to the pre-processed report template to obtain the report file, including: According to the pre-processed report template, fill in the report data starting from row N+4 to obtain the report file.

6. The method for batch importing reports according to claim 5, characterized in that: Business personnel package the report files, generate compressed file packages, and upload them to the backend system, including: Business personnel package all report files within the set time period, generate a compressed file package, and upload it to the backend system through the set upload page.

7. The method for batch importing reports according to claim 6, characterized in that: The backend system decompresses the compressed file package to obtain the report file, parses the report file, and updates the import result table and import failure record table, including: The backend system decompresses the compressed file package to obtain the report file; Parse the first three lines of the report file to read the database table name, database table field name and data type, update field, data start row and total number of data columns; A preprocessing SQL statement string is concatenated according to the database table name and the database table field name, and a preprocessing object is determined according to the preprocessing SQL statement string; Create a database table deletion statement based on the database table name, updated field, and the year and month of the compressed file package upload, and execute the database table deletion statement to clean up historical data; According to the data start row, the total number of data columns and the update field, the report data in the report file is parsed line by line from the N+4th row to the maximum number of data rows, and the report data is inserted into the database table according to the pre-processing object and the data type of the database table field, and the import failure record table is updated; wherein, for any row of report data: when the update field is not equal to the upload year and month, the row of report data is not inserted into the database table, and the number of failures is increased by 1, and the reason for failure is that the update field is not equal to the specified value, and the failure data is the string after the row of report data is spliced; when the update field is equal to the upload year and month, the row of report data is inserted into the database table according to the pre-processing object and the data type of the database table field. If the insertion is successful, the number of successful entries is increased by 1, and if the insertion fails, the number of failures is increased by 1, and the reason for failure is the abnormal information captured by the background system, and the failure data is the string after the row of report data is spliced; After the loop executes all data rows, the import result table is updated; the importer is the current operator, the import time is the current system timestamp, the report name is the name of the report file, and the number of successful and failed entries is accumulated by the background system.

8. A computer device comprising: A memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the method for batch importing reports according to any one of claims 1 to 7.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method for batch importing reports described in any one of claims 1 to 7 is implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the method for batch importing reports described in any one of claims 1 to 7 is implemented.

Images