Search

CN-121580997-B - Excel data emergency reporting and processing method based on dynamic mapping

CN121580997BCN 121580997 BCN121580997 BCN 121580997BCN-121580997-B

Abstract

The invention relates to the technical field of data processing, in particular to an Excel data emergency reporting and processing method based on dynamic mapping, which comprises the steps of receiving an emergency reporting instruction, confirming an emergency processing system and an Excel file to be processed based on the emergency reporting instruction, carrying out structural analysis on the Excel file to be processed to obtain a header field set and a data area set, constructing a field mapping rule base by using a dynamic mapping unit and the header field set, generating a dynamic filling template set based on the field mapping rule base and the data area set, checking a backfill data set by using the field mapping rule base to obtain a check result set, carrying out data aggregation by using a data summarizing unit based on the check result set and the backfill data set to obtain a summarized data table, and sending the summarized data table to an initiating end of the emergency reporting instruction to realize emergency reporting and processing of Excel data. The invention can improve the accuracy and efficiency of the emergency report and processing of the Excel data.

Inventors

  • WANG LIFENG
  • WANG DONGYAN
  • HAN QILONG

Assignees

  • 河北华烨冀科信息技术有限责任公司

Dates

Publication Date
20260508
Application Date
20260126

Claims (9)

  1. 1. The Excel data emergency reporting and processing method based on dynamic mapping is characterized by comprising the following steps of: receiving an emergency report instruction, and confirming an emergency processing system and an Excel file to be processed based on the emergency report instruction, wherein the emergency processing system comprises a dynamic mapping unit, a task distribution unit and a data summarizing unit, and the Excel file to be processed comprises a plurality of worksheets; Carrying out structural analysis on the Excel file to be processed to obtain a header field set and a data area set, wherein the header field set comprises a plurality of header fields, the data area set comprises a plurality of data areas, and the header fields are in one-to-one correspondence with the data areas; Constructing a field mapping rule base by using a dynamic mapping unit and a header field set, and generating a dynamic filling template set based on the field mapping rule base and a data area set, wherein the dynamic filling template set comprises a plurality of dynamic filling templates; Acquiring organization level data and a filling terminal, generating a distribution task set by using a task distribution unit, the organization level data and a dynamic filling template set, and sending the distribution task set to the filling terminal; The method comprises the steps of acquiring a backfill data set from a filling terminal in real time, checking the backfill data set by using a field mapping rule base to obtain a check result set, carrying out data aggregation by using a data aggregation unit based on the check result set and the backfill data set to obtain an aggregated data table, and sending the aggregated data table to an initiating terminal of an emergency report instruction to realize emergency report and processing of Excel data; Based on the verification result set and the backfill data set, the data aggregation is performed by using a data aggregation unit to obtain an aggregated data table, and the method comprises the following steps: Screening out an effective backfill data set from the backfill data set by using a verification result set, wherein the effective backfill data set comprises a plurality of effective backfill data, each effective backfill data comprises a filling source identifier and a filling time stamp, and the data verification results corresponding to the effective backfill data are all passed; And executing the following operations on each effective backfill data in the effective backfill data set: extracting a filling source identifier from the effective backfill data, confirming source hierarchy nodes in a hierarchy node tree by using the filling source identifier, and acquiring node hierarchy and lower node quantity of the source hierarchy nodes to obtain source hierarchy and source lower node quantity; Associating effective backfill data, source hierarchy and source subordinate node quantity to obtain hierarchy backfill nodes, and summarizing the hierarchy backfill nodes to obtain a hierarchy backfill node set; Ordering the hierarchical backfill nodes in the hierarchical backfill node set according to the sequence from low to high of the source hierarchy to obtain a hierarchical backfill sequence; Calculating an aggregate weight by using a pre-constructed aggregate weight calculation formula, wherein the aggregate weight calculation formula is as follows: ; Wherein, the Representation level backfill node set The aggregation weights corresponding to the nodes are backfilled by the individual levels, 、 Respectively represents a preset first weight coefficient and a preset second weight coefficient, and + =1, Representing the largest level in the hierarchical node tree, 、 Respectively represent the backfill node set of the hierarchy First, second The source hierarchy corresponding to the node is backfilled by each hierarchy, Representing hierarchical backfill node commonality The nodes are backfilled in the individual levels, 、 Respectively represent the first First, second The number of source lower nodes corresponding to the backfill nodes of each hierarchy; summarizing the aggregation weights to obtain a plurality of aggregation weights; and carrying out step-by-step data aggregation on the hierarchical backfill sequence by utilizing a plurality of aggregation weights to obtain a summary data table.
  2. 2. The method for emergency reporting and processing of Excel data based on dynamic mapping according to claim 1, wherein the performing structural analysis on the Excel file to be processed to obtain a header field set and a data area set includes: Each worksheet in the Excel file to be processed performs the following operations: Acquiring a cell matrix of a worksheet, and identifying a non-empty cell set in the cell matrix, wherein the non-empty cell set comprises a plurality of non-empty cells; identifying the position coordinates of each non-empty cell in the non-empty cell set to obtain a plurality of position coordinates, wherein the non-empty cells are in one-to-one correspondence with the position coordinates; Carrying out connected domain analysis on the plurality of position coordinates to obtain one or more connected regions; The following is performed for each of the one or more connected regions: confirming a first row cell sequence based on the communication area, wherein the first row cell sequence comprises a plurality of first row cells, and executing the following operation on each first row cell in the first row cell sequence: acquiring cell content and cell format characteristics of a first row of cells, and judging whether the cell content accords with a preset field naming rule or not to obtain a naming rule judging result, wherein the naming rule judging result accords with or does not accord with the preset field naming rule; Calculating the header confidence coefficient based on the cell format characteristics and a preset characteristic score mapping rule base, comparing the header confidence coefficient with a preset confidence coefficient threshold value, and if the naming rule judging result is in accordance with the header confidence coefficient threshold value and the header confidence coefficient is greater than or equal to the confidence coefficient threshold value, confirming the cell content as a header field, and summarizing the header field to obtain a header field set; Removing the first row of cell sequences from the communication area to obtain a data area; and summarizing the data areas to obtain a data area set.
  3. 3. The method for emergency reporting and processing of Excel data based on dynamic mapping according to claim 2, wherein the constructing a field mapping rule base by using the dynamic mapping unit and the header field set comprises: the method comprises the steps of obtaining a standard field library, wherein the standard field library comprises a plurality of standard field nodes, and the standard field nodes comprise standard field names, field data types and field constraint conditions; Scanning a cell formula in a data area set, identifying a formula function set, and dynamically generating a check rule node based on the formula function set, wherein the check rule node comprises a regular check rule and a data dictionary check rule; the following is performed for each header field in the header field set: Calculating the semantic similarity of the header field and the standard field name corresponding to each standard field node in the standard field library by using a pre-constructed semantic similarity model to obtain a similarity set, and extracting the maximum similarity from the similarity set to obtain the target similarity; Comparing the target similarity with a preset similarity threshold, and if the target similarity is greater than or equal to the similarity threshold, associating the header field, the check rule node and the standard field node corresponding to the target similarity to obtain a mapping rule node; Otherwise, generating a custom field node based on the header field, and associating the header field, the check rule node and the custom field node to obtain a mapping rule node; And summarizing the mapping rule nodes to obtain a field mapping rule base.
  4. 4. The method for emergency reporting and processing of Excel data based on dynamic mapping according to claim 3, wherein the generating a dynamic reporting template set based on the field mapping rule base and the data region set comprises: Sequentially extracting data areas from the data area set, and executing the following operations on the extracted data areas: Extracting a data line set in the data area, and executing the following operations on each data line in the data line set: performing type inference on the cell data in the data row by using mapping rule nodes in the field mapping rule library to obtain an inferred type set, and counting the number of each inferred type in the inferred type set to obtain a type number set; summarizing the type quantity sets to obtain region type quantity sets, and carrying out cluster analysis on the region type quantity sets to obtain one or more cluster type groups; Identifying the cluster type group with the largest quantity in one or more cluster type groups to obtain a target type group, and generating a dynamic filling template based on the target type group and a field mapping rule base, wherein the dynamic filling template comprises a filling range identifier; and summarizing the dynamic filling templates to obtain a dynamic filling template set.
  5. 5. The method for emergency reporting and processing of Excel data based on dynamic mapping according to claim 4, wherein the generating a distribution task set by using a task distribution unit, organizing hierarchical data and a dynamic reporting template set comprises: analyzing and organizing the hierarchical data to obtain a hierarchical node tree, wherein the hierarchical node tree comprises a plurality of hierarchical nodes, and the hierarchical nodes comprise node identifications, node levels and upper node identifications; and executing the following operations on each dynamic filling template in the dynamic filling template set: Extracting a filling range identifier from the dynamic filling template, and carrying out matching search in the hierarchical node tree by using the filling range identifier to obtain a target hierarchical node set; The following operations are performed for each target hierarchy node in the set of target hierarchy nodes: associating the dynamic filling template with the target level node to obtain a task distributing node, wherein the task distributing node comprises task deadline and reminding frequency; and summarizing the distribution task nodes to obtain a distribution task set.
  6. 6. The method for emergency reporting and processing of Excel data based on dynamic mapping according to claim 5, wherein the step of verifying the backfill data set by using the field mapping rule base to obtain a verification result set comprises the steps of: the following operations are performed on each backfill data in the backfill data set: extracting a backfill field value set from the backfill data, and executing the following operation on each backfill field value in the backfill field value set: Searching a corresponding mapping rule node in a field mapping rule base by using a header field corresponding to the backfill field value, and performing type verification on the backfill field value based on the field data type in the mapping rule node to obtain a type verification result, wherein the type verification result is pass or fail; Performing constraint verification on the backfill field value based on field constraint conditions in the mapping rule nodes to obtain constraint verification results, wherein the constraint verification results are pass or fail; dynamically checking the backfill field value based on a check rule node in the mapping rule nodes to obtain a dynamic check result, wherein the dynamic check result is passing or failing; Associating a type verification result, a constraint verification result and a dynamic verification result to obtain a field verification result; summarizing the field verification results to obtain data verification results, and summarizing the data verification results to obtain a verification result set.
  7. 7. The dynamic mapping-based Excel data emergency reporting and processing method as claimed in claim 6, wherein the step-by-step data aggregation is performed on the hierarchical backfill sequence by using a plurality of aggregation weights to obtain a summary data table, and the method comprises the following steps: identifying hierarchical backfill nodes of the same source hierarchy in the hierarchical backfill sequence to obtain a peer backfill node group set, and executing the following operations on each peer backfill node group in the peer backfill node group set: confirming a numerical field value set in the peer backfill node group, and carrying out abnormal value detection on the numerical field value set to obtain an abnormal value identification set; Removing abnormal values in the numerical field value set by using the abnormal value identification set to obtain an effective value set, and calculating the average value of the effective values in the effective value set to obtain the same-level average value; calculating standard deviation of effective values in the effective value set to obtain the same-level standard deviation, and constructing a confidence interval based on the same-level mean value and the same-level standard deviation; Correcting the numerical value type field value in the same-level backfill node group by using the confidence interval to obtain a corrected backfill node group; And summarizing the correction backfill node group to obtain a correction backfill sequence, and generating a summarized data table based on the correction backfill sequence and a plurality of aggregation weights.
  8. 8. The method for emergency reporting and processing of Excel data based on dynamic mapping according to claim 7, wherein after generating the summary data table based on the correction backfill sequence and the plurality of aggregation weights, further comprises: Acquiring task progress monitoring data based on a distribution task set and a backfill data set, wherein the task progress monitoring data comprises the filled quantity, the unfilled quantity and the filling completion rate; Updating task progress monitoring data in real time by using a preset monitoring time interval to obtain a progress monitoring time sequence, and carrying out trend analysis on the progress monitoring time sequence by using a pre-constructed filling risk prediction model to obtain a filling trend prediction value and a filling risk grade; Comparing the predicted value of the filling trend with a preset early warning threshold, and if the predicted value of the filling trend is smaller than the early warning threshold, dynamically adjusting the task deadline or the reminding frequency of a corresponding distribution task node in a distribution task set based on the filling risk level, generating a prompting reminding message based on the distribution task set, and sending the prompting reminding message to an unfilled filling terminal; Generating a data tracing record based on the filling source identifier and the filling time stamp in the effective backfill data, and associating the data tracing record to a summary data table; and generating a statistical analysis report by using the summary data table and the hierarchical node tree, and sending the statistical analysis report to an initiating end of the emergency report instruction.
  9. 9. An Excel data emergency reporting and processing system based on dynamic mapping is characterized in that the system comprises: the structure analysis module is used for receiving an emergency report instruction, and confirming an emergency processing system and an Excel file to be processed based on the emergency report instruction, wherein the emergency processing system comprises a dynamic mapping unit, a task distribution unit and a data summarizing unit, and the Excel file to be processed comprises a plurality of worksheets; Carrying out structural analysis on the Excel file to be processed to obtain a header field set and a data area set, wherein the header field set comprises a plurality of header fields, the data area set comprises a plurality of data areas, and the header fields are in one-to-one correspondence with the data areas; The mapping rule construction module is used for constructing a field mapping rule base by utilizing the dynamic mapping unit and the header field set, and generating a dynamic filling template set based on the field mapping rule base and the data area set, wherein the dynamic filling template set comprises a plurality of dynamic filling templates; the data backfilling module is used for acquiring the organization level data and the filling terminal, generating a distribution task set by utilizing the task distribution unit, the organization level data and the dynamic filling template set, and sending the distribution task set to the filling terminal; The data verification module is used for acquiring a backfill data set from the filling terminal in real time, verifying the backfill data set by using the field mapping rule base to obtain a verification result set, carrying out data aggregation by using the data summarization unit based on the verification result set and the backfill data set to obtain a summarized data table, and sending the summarized data table to an initiating terminal of an emergency report instruction to realize emergency report and processing of Excel data; Based on the verification result set and the backfill data set, the data aggregation is performed by using a data aggregation unit to obtain an aggregated data table, and the method comprises the following steps: Screening out an effective backfill data set from the backfill data set by using a verification result set, wherein the effective backfill data set comprises a plurality of effective backfill data, each effective backfill data comprises a filling source identifier and a filling time stamp, and the data verification results corresponding to the effective backfill data are all passed; And executing the following operations on each effective backfill data in the effective backfill data set: extracting a filling source identifier from the effective backfill data, confirming source hierarchy nodes in a hierarchy node tree by using the filling source identifier, and acquiring node hierarchy and lower node quantity of the source hierarchy nodes to obtain source hierarchy and source lower node quantity; Associating effective backfill data, source hierarchy and source subordinate node quantity to obtain hierarchy backfill nodes, and summarizing the hierarchy backfill nodes to obtain a hierarchy backfill node set; Ordering the hierarchical backfill nodes in the hierarchical backfill node set according to the sequence from low to high of the source hierarchy to obtain a hierarchical backfill sequence; Calculating an aggregate weight by using a pre-constructed aggregate weight calculation formula, wherein the aggregate weight calculation formula is as follows: ; Wherein, the Representation level backfill node set The aggregation weights corresponding to the nodes are backfilled by the individual levels, 、 Respectively represents a preset first weight coefficient and a preset second weight coefficient, and + =1, Representing the largest level in the hierarchical node tree, 、 Respectively represent the backfill node set of the hierarchy First, second The source hierarchy corresponding to the node is backfilled by each hierarchy, Representing hierarchical backfill node commonality The nodes are backfilled in the individual levels, 、 Respectively represent the first First, second The number of source lower nodes corresponding to the backfill nodes of each hierarchy; summarizing the aggregation weights to obtain a plurality of aggregation weights; and carrying out step-by-step data aggregation on the hierarchical backfill sequence by utilizing a plurality of aggregation weights to obtain a summary data table.

Description

Excel data emergency reporting and processing method based on dynamic mapping Technical Field The invention relates to the technical field of data processing, in particular to an Excel data emergency reporting and processing method based on dynamic mapping. Background Under the technical service contract framework, technical engineers assume the key role of rapidly linking customer business needs with technical systems. When dealing with bursty data reporting and analysis requirements, technical engineers often need to manually process Excel tables from parties to assist in lead decisions. An intelligent emergency data processing product is constructed, so that a technical engineer can be liberated from tedious and repeated data collection and arrangement work, and the technical engineer can focus on key tasks such as anomaly analysis and the like. Through a standardized and automatic process, the accuracy and consistency of the data in the links of extraction, conversion and summarization are ensured, and the emergency response speed and the service quality are improved. At present, technical engineers execute Excel emergency tasks mainly by manual operation, and in the face of multi-source and heterogeneous Excel files, personnel need to manually open, copy, screen and paste, errors are prone to occur, and particularly when the data size is large and the time is urgent, accurate results are difficult to guarantee to be delivered on time. Although the method can realize the emergency report and processing of the Excel data, the source is difficult to trace when errors occur in the data processing process in the traditional mode, and similar but slightly different demands are met each time, a reusable standardized flow is needed from the beginning, response lag is caused, the efficiency and the specialty of emergency service are restricted, and therefore, how to improve the accuracy and the efficiency of the emergency report and processing of the Excel data becomes a problem to be solved urgently. Disclosure of Invention The invention provides an Excel data emergency reporting and processing method based on dynamic mapping and a computer readable storage medium, and mainly aims to improve accuracy and efficiency of the Excel data emergency reporting and processing. In order to achieve the above object, the method for reporting and processing Excel data based on dynamic mapping provided by the present invention comprises: receiving an emergency report instruction, and confirming an emergency processing system and an Excel file to be processed based on the emergency report instruction, wherein the emergency processing system comprises a dynamic mapping unit, a task distribution unit and a data summarizing unit, and the Excel file to be processed comprises a plurality of worksheets; Carrying out structural analysis on the Excel file to be processed to obtain a header field set and a data area set, wherein the header field set comprises a plurality of header fields, the data area set comprises a plurality of data areas, and the header fields are in one-to-one correspondence with the data areas; Constructing a field mapping rule base by using a dynamic mapping unit and a header field set, and generating a dynamic filling template set based on the field mapping rule base and a data area set, wherein the dynamic filling template set comprises a plurality of dynamic filling templates; Acquiring organization level data and a filling terminal, generating a distribution task set by using a task distribution unit, the organization level data and a dynamic filling template set, and sending the distribution task set to the filling terminal; and acquiring a backfill data set from the backfill terminal in real time, checking the backfill data set by using a field mapping rule base to obtain a check result set, and based on the check result set and the backfill data set, performing data aggregation by using a data summarizing unit to obtain a summarized data table, and sending the summarized data table to an initiating end of an emergency report instruction to realize emergency report and processing of Excel data. Optionally, the performing structural analysis on the Excel file to be processed to obtain a header field set and a data area set includes: Each worksheet in the Excel file to be processed performs the following operations: Acquiring a cell matrix of a worksheet, and identifying a non-empty cell set in the cell matrix, wherein the non-empty cell set comprises a plurality of non-empty cells; identifying the position coordinates of each non-empty cell in the non-empty cell set to obtain a plurality of position coordinates, wherein the non-empty cells are in one-to-one correspondence with the position coordinates; Carrying out connected domain analysis on the plurality of position coordinates to obtain one or more connected regions; The following is performed for each of the one or more connected regions: confirming