Excel report data and system data matching settlement processing method, system and equipment and storage medium

Abstract

The invention relates to the technical field of data processing, in particular to an Excel report data and system data matching settlement processing method, system and device and a storage medium. According to the method, a unified field format model is constructed by obtaining template configuration content including a field format rule, a header mapping rule and a matching field rule, and header identification, field mapping and format normalization processing are performed on an Excel report to obtain standardized report data. And generating a unique matching identifier based on the matching field rule, comparing the unique matching identifier with the system data one by one to obtain a matching result, and generating a standard identifier according to a standard condition. Further performing question marking on the unmatched or abnormal records to form question records, and finally generating a settlement processing report based on the matching result, the standard mark and the question records. According to the invention, accurate matching and abnormity pre-recognition of report data and system data are realized, and the accuracy, efficiency and traceability of settlement processing are improved.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method, system, device and storage medium for matching and settling Excel report data with system data. Background Technology

[0002] In related technologies, with the increasing digitalization of enterprise operations and the accelerated development of multi-channel business collaboration, various partners, suppliers, and business departments commonly submit transaction details, expense records, or settlement basis to enterprise systems in the form of Excel reports. Excel reports are widely adopted due to their flexibility and low barrier to entry; however, issues such as the lack of standardized formatting, inconsistent field naming, diverse date representations, and inconsistent anonymization rules prevent the system from directly parsing the report content in a structured manner. Manual interpretation, organization, and verification of the format are essential. When processing reconciliation and settlement, enterprises need to manually compare multiple fields of information in the Excel reports with the system data, confirming consistency item by item. This is not only time-consuming and labor-intensive, but also prone to misunderstandings, data entry errors, and omissions in verification as the data volume increases, leading to a significant decrease in overall accounting efficiency.

[0003] However, existing Excel report processing methods still rely heavily on manual operation or semi-automated scripts, which are particularly vulnerable when dealing with multi-source, heterogeneous reports. If the supplier adjusts the header, field order, or formatting, the script often fails to run, requiring the rules to be re-maintained or the data to be manually corrected. Furthermore, manual methods struggle to effectively handle complex scenarios such as inconsistent date formats, inconsistent field masking, and mixed text in cells, making it difficult to guarantee the accuracy of data cleaning and format conversion. More importantly, traditional processing models lack the ability to automatically identify abnormal data. Data with non-standard formats, failed matches, or suspected errors cannot be promptly flagged, increasing the workload of manual investigation and affecting the reliability and traceability of reconciliation results.

[0004] In summary, the technical problems existing in the relevant technologies need to be improved. Summary of the Invention

[0005] The main objective of this application is to propose a method, system, device, and storage medium for matching and settling Excel report data with system data, so as to achieve accurate matching of report data and system data and pre-identification of anomalies, thereby improving the accuracy, efficiency, and traceability of the settlement process.

[0006] To achieve the above objectives, one aspect of this application proposes a method for matching and settling Excel report data with system data, the method comprising: Obtain the template configuration content used for data parsing, which includes field format rules, table header mapping rules, and matching field rules; Based on the aforementioned field format rules, a unified field format model is constructed; The Excel report content to be processed is obtained, and header recognition and field mapping are performed based on the header mapping rules to form structured report data; Based on the unified field format model, the structured report data is subjected to field format conversion and content normalization processing to obtain standardized report data; Based on the matching field rules, a matching condition group for report data and system data is constructed, and a unique matching identifier is generated; Based on the unique matching identifier, the standardized report data is compared with the system data one by one to generate matching results; Based on preset compliance conditions, the matching results are judged to meet the standards, and a compliance identifier is generated; Based on the matching results, question marks are applied to unmatched records or records that trigger abnormal conditions according to the question marking mechanism to form question records; A settlement processing report is generated based on the matching results, compliance indicators, and question records.

[0007] In some embodiments, the field format rules include field type definition rules, field standardization rules, and desensitization format parsing rules; The field type definition rules are used to determine the type attribute of a field; The field standardization rules are used to perform format unification and value range normalization on field content according to field type; The de-identification format parsing rules are used to identify and parse the representation of de-identified fields in Excel reports.

[0008] In some embodiments, constructing a unified field format model based on the field format rules includes: Based on the field format rules, the type attributes, value range, date format, numeric format, and desensitization format of each field to be parsed are standardized and defined to obtain a set of field format benchmarks; Based on the differences in field naming, display format and desensitization methods among various types of Excel reports, synonym mapping parsing is performed on fields with the same business meaning to generate a field format mapping relationship table; Based on the aforementioned field format benchmark set and field format mapping table, abstract and unified modeling is performed on Excel report fields of different formats to form a unified field format model.

[0009] In some embodiments, the step of constructing a matching condition group for report data and system data based on the matching field rules and generating a unique matching identifier includes: Based on the matching field rules, key fields are extracted; the key fields include identifier fields, time fields, and business attribute fields. Based on the key fields, perform format unification, desensitization value restoration and content normalization on the corresponding fields in the report data and system data to obtain comparable field values; Field-level matching conditions are constructed based on the comparable field values, and matching condition groups are generated based on multiple matching conditions in a preset combination manner; Based on the matching condition group, perform connection, sorting and encryption encoding processes to generate a unique matching identifier corresponding to a single report data.

[0010] In some embodiments, the step of comparing standardized report data with system data line by line based on the unique matching identifier to generate matching results includes: Based on the unique matching identifier, the target record corresponding to the unique matching identifier is located from the standardized report data and system data respectively, and a comparison data pair is constructed. The comparison range is parsed for the field set in the comparison data pair, and the corresponding comparison strategy is determined according to the field type; the comparison strategy includes numerical equivalence comparison, date standardization comparison and text attribute comparison. Based on the comparison strategy, field-level comparison is performed on the corresponding fields of the target record to generate a structured comparison result; Based on the structured comparison results, the comparison records are classified into successful matching records and abnormal matching records, and the matching results are summarized and generated.

[0011] In some embodiments, the step of judging the matching result based on preset compliance conditions and generating a compliance identifier includes: Based on the matching results, a set of target field values ​​is extracted; the set of target field values ​​includes numeric fields, date fields, and business attribute fields. According to the preset compliance conditions, condition comparison and rule calculation are performed on the target field value set to generate compliance judgment results; the compliance conditions include threshold judgment conditions, logical combination conditions and cross-field association conditions; Based on the compliance determination results, a compliance label is generated.

[0012] In some embodiments, the step of performing question marking on unmatched records or records that trigger abnormal conditions according to the question marking mechanism based on the matching result to form question records includes: Based on the matching results, identify unmatched records and target records that triggered abnormal conditions during the matching field comparison process; According to the question marking rules, anomaly type determination and marking are performed on the key fields of the target record to generate question marking results; the question marking rules include field missing rules, format anomaly rules, cross-field conflict rules, and value out-of-bounds rules. Based on the question marking results, a question record is generated.

[0013] To achieve the above objectives, another aspect of this application proposes an Excel report data and system data matching and settlement processing system, the system comprising: The template configuration module is used to obtain template configuration content for data parsing. The template configuration content includes field format rules, table header mapping rules, and matching field rules. The first construction module is used to construct a unified field format model according to the field format rules; The report parsing module is used to obtain the content of the Excel report to be processed, and perform header recognition and field mapping based on the header mapping rules to form structured report data; The field standardization module is used to perform field format conversion and content normalization processing on the structured report data according to the unified field format model to obtain standardized report data. The matching identifier generation module is used to construct a matching condition group between report data and system data based on the matching field rules, and generate a unique matching identifier. The data comparison module is used to compare standardized report data with system data one by one based on the unique matching identifier and generate matching results; The compliance judgment module is used to judge the compliance of the matching results based on preset compliance conditions and generate a compliance identifier; The question marking module is used to mark unmatched records or records that trigger abnormal conditions according to the question marking mechanism based on the matching results, thereby forming question records; The settlement report generation module is used to generate a settlement processing report based on the matching results, compliance indicators, and question records.

[0014] To achieve the above objectives, another aspect of this application provides an electronic device, which includes a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the above-described method.

[0015] To achieve the above objectives, another aspect of the embodiments of this application proposes a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described method.

[0016] To achieve the above objectives, another aspect of this application provides a computer program product, including a computer program that, when executed by a processor, implements the above-described method.

[0017] The embodiments of this application include at least the following beneficial effects: This application provides a method, system, device, and storage medium for matching and settling Excel report data with system data. This solution introduces template configuration content to achieve parameterized management of field format rules, header mapping rules, and matching field rules, enabling the report parsing process to flexibly adapt to multiple types of Excel report formats and significantly reducing manual configuration costs. By constructing a unified field format model and performing format conversion and content normalization processing on structured report data, the differences in field types, naming methods, and data specifications among reports from different sources are resolved, improving the standardization and consistency of the data processing process and providing a stable data foundation for subsequent automatic matching. Based on the matching field rules, matching condition groups are constructed, and unique matching identifiers are generated, allowing standardized report data to be accurately compared with system data line by line. This effectively avoids omissions, mismatches, and inefficiencies that occur in traditional manual verification, significantly improving matching accuracy and processing speed. By presetting compliance conditions and performing compliance judgments on the matching results, key settlement indicators in business scenarios can be automatically verified, achieving quantifiable scoring and risk identification of multi-dimensional matching status. Furthermore, this invention intelligently labels unmatched records and records that trigger abnormal conditions through an inquiry marking mechanism, automatically generating inquiry records. This allows for rapid identification of abnormal data, rule conflicts, or field deviations without requiring manual, line-by-line checks, improving overall verification efficiency. Finally, a settlement processing report is generated based on the matching results, compliance indicators, and inquiry records, achieving a closed-loop process from data parsing and matching verification to the presentation of business settlement results. It boasts advantages such as high automation, strong traceability, and timely anomaly handling, providing stable, accurate, and auditable technical support for enterprise settlement operations. Attached Figure Description

[0018] Figure 1 This is a flowchart illustrating a method for matching and settling Excel report data with system data, as provided in an embodiment of this application. Figure 2 yes Figure 1 The flowchart of step S2 in the text; Figure 3 This is a schematic diagram of a module of an Excel report data and system data matching and settlement processing system provided in an embodiment of this application. Detailed Implementation

[0019] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of this application and are not intended to limit it. In the following description, when referring to the accompanying drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with those of this application; they are merely examples of apparatuses and methods consistent with some aspects of the embodiments of this application as detailed in the appended claims.

[0020] It is understood that the terms “first,” “second,” etc., used in this application may be used herein to describe various concepts, but unless otherwise stated, these concepts are not limited by these terms. These terms are only used to distinguish one concept from another. For example, without departing from the scope of the embodiments of this application, first information may also be referred to as second information, and similarly, second information may also be referred to as first information. Depending on the context, the words “if,” “when,” or “in response to a determination” as used herein may be interpreted as “when…” or “when…” or “in response to a determination.”

[0021] As used in this application, the terms "at least one", "multiple", "each", "any", etc., "at least one" includes one, two or more, "multiple" includes two or more, "each" refers to each of the corresponding multiples, and "any" refers to any one of the multiples.

[0022] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein is for the purpose of describing embodiments of this application only and is not intended to limit this application.

[0023] This application provides a method, system, device, and storage medium for matching and settling Excel report data with system data. This solution introduces template configuration to achieve parameterized management of field format rules, header mapping rules, and matching field rules, enabling flexible adaptation of the report parsing process to various Excel report formats and significantly reducing manual configuration costs. By constructing a unified field format model and performing format conversion and content normalization on structured report data, it solves the problem of differences in field types, naming methods, and data specifications among reports from different sources, improving the standardization and consistency of the data processing process and providing a stable data foundation for subsequent automatic matching. Based on matching field rules, matching condition groups are constructed, and unique matching identifiers are generated, allowing standardized report data to be accurately compared with system data line by line. This effectively avoids omissions, mismatches, and inefficiencies that occur in traditional manual verification, significantly improving matching accuracy and processing speed. By presetting compliance conditions and performing compliance judgments on the matching results, key settlement indicators in business scenarios can be automatically verified, achieving quantifiable scoring and risk identification of multi-dimensional matching status. Furthermore, this invention intelligently labels unmatched records and records that trigger abnormal conditions through an inquiry marking mechanism, automatically generating inquiry records. This allows for rapid identification of abnormal data, rule conflicts, or field deviations without requiring manual, line-by-line checks, improving overall verification efficiency. Finally, a settlement processing report is generated based on the matching results, compliance indicators, and inquiry records, achieving a closed-loop process from data parsing and matching verification to the presentation of business settlement results. It boasts advantages such as high automation, strong traceability, and timely anomaly handling, providing stable, accurate, and auditable technical support for enterprise settlement operations.

[0024] This application provides a method for matching and settling Excel report data with system data, relating to the field of data processing technology. This method can be applied to a terminal, a server, or software running on either a terminal or a server. In some embodiments, the terminal can be a smartphone, tablet, laptop, desktop computer, smart speaker, smartwatch, or in-vehicle terminal, but is not limited thereto. The server can be configured as an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms. The server can also be a node server in a blockchain network. The software can be an application implementing the method for matching and settling Excel report data with system data, but is not limited to the above forms.

[0025] This application can be used in a wide variety of general-purpose or special-purpose computer system environments or configurations. Examples include: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics devices, network PCs, minicomputers, mainframe computers, and distributed computing environments including any of the above systems or devices. This application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc., that perform specific tasks or implement specific abstract data types. This application can also be practiced in distributed computing environments where tasks are performed by remote processing devices connected via a communication network. In distributed computing environments, program modules can reside in local and remote computer storage media, including storage devices.

[0026] It should be noted that in all specific embodiments of this application, when processing data related to user identity or characteristics, such as user information, user behavior data, user historical data, and user location information, user permission or consent is obtained first. Furthermore, the collection, use, and processing of this data comply with relevant laws, regulations, and standards. In addition, when embodiments of this application require access to sensitive personal information of users, separate permission or consent from the user is obtained through pop-ups or redirection to confirmation pages. Only after obtaining the user's separate permission or consent is the necessary user-related data required for the proper functioning of these embodiments acquired.

[0027] Figure 1 This is an optional flowchart of a method for matching and settling Excel report data with system data, provided in an embodiment of this application. Figure 1 The method may include, but is not limited to, steps S1 to S9: S1: Obtain the template configuration content used for data parsing, which includes field format rules, table header mapping rules, and matching field rules; In this embodiment, a corresponding data parsing template is first pre-created for each partner or report type in the template management interface of the unified processing platform. Business personnel then configure and save the field format rules, header mapping rules, and matching field rules sequentially through the visual template configuration page. The template configuration is not hardcoded once, but supports version management and multiple templates coexisting. Users can manage templates by dimensions such as "Supplier A Reconciliation Template" and "Supplier B Monthly Settlement Template" to achieve a "configure once, reuse multiple times" parsing mode.

[0028] The template configuration details are as follows: In the field formatting rules section, the platform provides a pre-built library of formatting options, such as: date categories (standard date, month and day only, year only, date mixed with text), amount categories (with thousands separator, with currency symbol, negative number brackets), text categories (preceding and following spaces, case-sensitive matching, special character removal), and anonymized field categories (first six characters visible, last four visible, first four visible, all asterisks, etc.). Business users only need to select the applicable rules through a "check dropdown list + parameter input" method, without writing any code or regular expressions. For complex scenarios, such as extracting dates from text like "produced on September 1, 2025", the platform also provides encapsulated rule options to specify the behavior of "extracting date pattern from text".

[0029] In the header mapping rules section, the platform allows maintaining a set of possible report header aliases for each "standard field". For example, the standard field "member_id" can be configured with multiple candidate headers such as "Member Number", "Member Code", "User ID", and "Account Number"; the field "bill_date" can be configured with "Accounting Period", "Reconciliation Date", and "Settlement Date". During configuration, business users can add, delete, or drag and drop to sort these aliases. The system then matches each alias item by item according to the template priority order when parsing the Excel headers to improve recognition accuracy. For some easily ambiguous headers (such as "Date"), the system can also use field type and sample data for auxiliary judgment to prevent mismapping.

[0030] The matching field rules section defines the key field combinations used to align subsequent report data with internal platform records. For example, you can configure "Member ID + Billing Date + Product Code" as a mandatory matching field set, or specify an "optional participation" flag for certain fields to handle situations where some reports lack certain fields. Business users specify the source field (i.e., the corresponding standard field name), whether it's mandatory, and the method of generating the unique identifier for each matching field in the template. After configuration, the template is serialized into XML or a similar structure and stored in the template engine for subsequent steps, truly realizing a template-driven data parsing model.

[0031] S2: Construct a unified field format model based on field format rules; Among them, field format rules include field type definition rules, field standardization rules, and desensitization format parsing rules; Field type definition rules are used to determine the type attribute of a field; Field standardization rules are used to perform format standardization and value range normalization on field content based on field type; The masking format parsing rules are used to identify and parse the masked field representation in Excel reports.

[0032] refer to Figure 2 As shown, a unified field format model is constructed based on field format rules, including: S21: Based on the field format rules, the type attributes, value range, date format, numeric format and desensitization format of each field to be parsed are standardized and defined to obtain the field format benchmark set; S22: Based on the differences in field naming, display format and desensitization method of various types of Excel reports, perform synonym mapping parsing on fields with the same business meaning and generate a field format mapping relationship table; S23: Based on the field format baseline set and the field format mapping relationship table, perform abstract and unified modeling on the fields of Excel reports with different formats to form a unified field format model.

[0033] In this embodiment, after loading the template configuration, a unified field format model is first constructed based on the field format rules. This embodiment clearly classifies the type attributes of each field to be parsed according to the "Field Type Definition Rules," categorizing fields into types such as date, amount, integer, string, and anonymized data. Corresponding parsing requirements are established for each type of field, including acceptable input formats, typical examples, abnormal input identification methods, and fault-tolerant logic. Through the systematic definition of field types, the model can accurately identify diverse input formats from different Excel reports, thereby ensuring the consistency of field meaning.

[0034] Subsequently, this embodiment defines the format and value range of each field according to the "Field Standardization Rules," forming a set of field format benchmarks. This set specifies a unified output format and normalization strategy for each field. For example, date fields must be uniformly output as "YYYY-MM-DD"; amount fields must have thousands separators removed, decimal precision standardized, and negative signs standardized; string fields must undergo space cleaning, case neutralization, and special character removal. Simultaneously, this embodiment configures permissible business value ranges and outlier judgment methods for fields, ensuring that fields from different suppliers and templates, even with significant differences in input format, can still be transformed into standardized field values ​​suitable for subsequent matching through standardization.

[0035] When processing masked fields, this embodiment further identifies and parses the masked expression methods in Excel reports based on the "mask-masking format parsing rules." Since suppliers may use various formats such as "six characters visible before and four after," "four characters visible before and three after," or "mask-masked in the middle with an asterisk," this embodiment extracts the reserved character segment, the masked segment position, and relevant matching information from the field value, restoring it to a structured fragment that can participate in the generation of a unique matching identifier. By uniformly modeling multiple masking methods, the matching difficulties caused by different masking formats can be effectively solved, improving the success rate of masked fields participating in system comparisons.

[0036] Finally, this embodiment constructs a field format mapping table based on the field format baseline set and the mapping relationship between synonymous fields, and performs abstract unified modeling on all Excel report fields accordingly. This embodiment will uniformly merge synonymous fields such as "Member Number / User ID / Member Code" into the same business field and bind them with corresponding format rules, type requirements, and desensitization parsing methods. Through this abstract modeling method, a unified field format model covering all field types and adapting to different vendor report formats is ultimately formed, enabling subsequent field conversion, matching condition construction, and unique identifier generation to be based on consistent field semantics and format specifications.

[0037] S3: Obtain the content of the Excel report to be processed, and perform header recognition and field mapping based on the header mapping rules to form structured report data; In this embodiment, after receiving the original Excel report file provided by the supplier through the front-end upload interface, the unified processing platform first calls the underlying report parsing engine to extract the structured content of the file. The parsing engine is implemented based on the Apache POI technology stack and is compatible with formats such as .xls and .xlsx. It then sequentially performs preprocessing operations such as workbook scanning, valid data area identification, merging and expanding cells, and formula cell value retrieval. For some supplier reports that have "hidden columns," "multiple header rows," or "fixed descriptive headers," this embodiment will perform header row search and noise row filtering according to the preset header positioning rules in the template to ensure that the finally identified header rows have consistent field meaning and structural stability, thereby ensuring the accuracy of subsequent field mapping.

[0038] Once the header row is successfully located, this embodiment reads the header text of each column contained in that row and identifies and matches it column by column according to the header mapping rules in the template configuration. The mapping rules typically include a set of standard fields and a list of possible synonymous column names, such as "member_id" corresponding to "member number / member code / member ID / user account," etc. During the matching process, three strategies are used simultaneously: "exact matching + fuzzy matching + semantic judgment." Fuzzy matching automatically handles differences in capitalization, spaces, and punctuation, while semantic judgment is used to identify header expressions like "account" and "user number" that are semantically related but not identical. Furthermore, for some fields prone to misjudgment, such as "date," "time," and "payment period," this embodiment also performs secondary verification based on the field type and example data values ​​to ensure the stability and uniqueness of the field mapping results.

[0039] After mapping the headers, the data in each column of the original Excel file is reconstructed into structured field objects with "standard field names" as keys. Then, each row of data is iterated through, and all column values ​​are written to the corresponding fields according to the standard field names, generating "one structured report record". If some columns cannot match the standard fields in the template, they are marked as "unrecognized fields" or "non-critical fields" and ignored, based on the template instructions. Finally, the structured report data is output, providing a deterministic input source for the next step of field format conversion and content normalization.

[0040] S4: Perform field format conversion and content normalization on the structured report data according to the unified field format model to obtain standardized report data; In this embodiment, structured report data is input line by line and field by field into a unified field format model for processing to ensure that the format and type of each field meet the template standard. During the field conversion process, the model performs four levels of processing on the values ​​according to the field type: "text normalization → format recognition → type conversion → exception handling". For example, when the field type is "date", the system automatically recognizes the pattern of the input value, including pure numbers, short dates (such as "9.1"), Chinese formats (such as "2025-09-01"), and descriptive text (such as "produced on 2025-09-01"). Then, it extracts the valid date components according to the format rules; if the year is missing, it automatically infers the year based on the payment period or business rules; finally, the output is a standard date string in the format "YYYY-MM-DD".

[0041] For character fields, such as member numbers, card numbers, and product codes, this embodiment executes unified cleaning rules based on the field format model, including removing spaces, removing invisible characters, unifying capitalization, and removing special symbols. If some fields have masking patterns (e.g., six characters before and four after, or masking in the middle), the unified field format model automatically identifies the masking location and extracts the retained segments for subsequent field matching calculations. If the same field may appear in multiple masking styles in different reports, the unified field format model will try multiple parsing strategies sequentially to ensure that the maximum range of fields can be successfully identified and restored to a matching format. For monetary fields, thousands separator elimination, negative number sign standardization, and decimal alignment are performed to completely eliminate differences in monetary values ​​across different supplier reports, ensuring that the field can be compared precisely at the numerical level.

[0042] Finally, this step converts all structured fields into standardized report data that is "normalized, matchable, and verifiable," ensuring high consistency and reliability for subsequent matching and comparison.

[0043] S5: Construct a matching condition group between report data and system data based on matching field rules, and generate a unique matching identifier; This involves constructing a set of matching conditions between report data and system data based on matching field rules, generating a unique matching identifier, including: S51: Extract key fields based on matching field rules; key fields include identifier fields, time fields, and business attribute fields. S52: Based on the key fields, perform format unification, desensitization value restoration and content normalization on the corresponding fields in the report data and system data to obtain comparable field values; S53: Construct field-level matching conditions based on comparable field values, and generate a matching condition group based on multiple matching conditions in a preset combination manner; S54: Perform connection, sorting and encryption encoding processing based on the matching condition group to generate a unique matching identifier corresponding to a single report data.

[0044] In this embodiment, key fields for establishing association with system data are first extracted from the standardized report data according to matching field rules. Key fields typically include identification fields (such as member number, order number), time fields (such as billing period, business date), and business attribute fields (such as product code, business type). The template pre-determines the necessity and priority of these fields, so the required fields are automatically filtered during this step, and error handling is performed in case of missing fields or inconsistent field sources to ensure the completeness of field extraction.

[0045] Subsequently, the key fields corresponding to the report data and system data underwent format unification, anonymization parsing, and content normalization. For fields involving anonymization, such as "abcdh****wsn" in the report, recognizable character fragments were extracted according to the anonymization format parsing rules and structurally matched with the complete field in the system to generate field values ​​that could be used for comparison. For date fields, dates in different formats were uniformly converted to the standard date format; for text or numeric fields, methods such as case unification, whitespace removal, and decimal place normalization were used to ensure that both fields were compared under the same format system.

[0046] After obtaining consistent field values, field-level matching conditions are constructed based on each field, such as character consistency conditions, date consistency conditions, or business attribute consistency conditions. Then, according to the combination strategy defined in the template, multiple field conditions are combined in priority order to generate a matching condition group. To ensure the stability of the matching condition group in subsequent comparisons, this embodiment performs concatenation and sorting processing on the combined conditions, making its structure reproducible and independent of the field source order.

[0047] Finally, the matching condition group is encoded to generate a unique matching identifier. This identifier is based on the normalized content of multiple key fields and constructed into a unique string according to fixed rules. It is used to accurately locate the system data corresponding to the report record during the matching phase, achieving high-accuracy association under multi-field joint matching.

[0048] S6: Based on a unique matching identifier, the standardized report data is compared with the system data one by one to generate matching results; Specifically, based on a unique matching identifier, standardized report data is compared with system data line by line to generate matching results, including: S61: Based on the unique matching identifier, locate the target record corresponding to the unique matching identifier from the standardized report data and system data respectively, and construct a comparison data pair; S62: Perform comparison range parsing on the field set in the comparison data pair, and determine the corresponding comparison strategy according to the field type; among which, the comparison strategy includes numerical equivalence comparison, date standardization comparison and text attribute comparison; S63: Based on the comparison strategy, perform field-level comparison on the corresponding fields of the target record to generate a structured comparison result; S64: Based on the structured comparison results, classify the execution results of the comparison records to form successfully matched records and abnormally matched records, and summarize and generate matching results.

[0049] In this embodiment, upon receiving a unique matching identifier, the system locates the corresponding target record in both standardized report data and system data, and constructs a "comparison data pair" for comparison. This unique matching identifier already contains field combination information, allowing the system to quickly screen candidate records and ensure consistency of the comparison objects. If multiple candidate records exist on the system side, the system further narrows down the scope based on field priority and business constraint rules, ultimately forming a pair of target records that can be compared item by item.

[0050] After constructing the data pairs, this embodiment parses the comparison field set and automatically selects the corresponding comparison strategy based on the field type. For numeric fields such as amount and quantity, it performs numerical equality comparison, unifying decimal places and signs before determining if the values ​​are consistent. For time-based fields such as payment period and business date, it performs date standardization comparison, unifying the date formats of both parties before alignment. For text-based fields such as product code, name, and channel identifier, it uses text attribute comparison, determining consistency after character cleaning, whitespace removal, and case unification. This field type-driven comparison strategy can adapt to differences in various report formats.

[0051] After selecting the field strategy, the system generates field-level comparison results one by one, and records the comparison status of each field in a structured manner, such as "matched," "inconsistent," "missing but tolerable," and "abnormal format." Finally, the entire record is categorized based on the comparison results of all fields: if all key fields are consistent, the record is marked as "match successful"; if there are inconsistent or abnormal fields, the record is marked as "match abnormal." The system summarizes the categorization results of all records to form a matching result set for subsequent compliance judgment.

[0052] S7: Based on the preset compliance conditions, judge the matching results to meet the standards and generate compliance indicators; Among them, based on preset compliance conditions, the matching results are judged to meet the standards, and compliance indicators are generated, including: S71: Based on the matching results, extract the target field value set; whereby the target field value set includes numeric fields, date fields, and business attribute fields; S72: According to the preset compliance conditions, perform condition comparison and rule calculation on the target field value set to generate compliance judgment results; among which, compliance conditions include threshold judgment conditions, logical combination conditions and cross-field association conditions; S73: Generate a compliance mark based on the compliance determination results.

[0053] In this embodiment, after obtaining the matching results, the target field value set for compliance judgment is first extracted based on the compliance conditions configured in the template. This set typically consists of three types of fields: numeric fields (such as amount, quantity, and expense items), date fields (such as payment period date and transaction date), and business attribute fields (such as product type, transaction channel, and business status). These fields are then formatted and semantically restored to ensure that all fields involved in compliance judgment are standardized values, thereby avoiding judgment biases caused by differences in report formats.

[0054] After extracting the target field value set, the set is compared and calculated item by item according to preset compliance conditions. Compliance conditions typically include three types of logic: first, threshold judgment conditions, such as "amount must be greater than 0" or "cost difference must not exceed a set threshold," used to verify whether a field meets business boundary requirements; second, logical combination conditions, such as "amount consistent AND product consistent" or "date within the payment period OR specific business status met," achieving complex judgment logic through multiple condition combinations; and third, cross-field association conditions, such as "amount must be consistent with quantity and unit price calculation results" or "payment period must be associated with creation date," used to ensure logical consistency between multiple fields. Judgments are executed sequentially according to the conditions configured in the template, and independent calculation results are recorded for each condition for subsequent analysis and traceability.

[0055] Finally, by combining the calculation results of all conditions, a final compliance determination result is generated, and a corresponding compliance indicator is output. If all key conditions are met, the record is marked as "compliant"; if any core condition is not met, it is marked as "non-compliant"; for cases where some conditions cannot be determined or require manual confirmation, indicators such as "pending review" or "insufficient conditions" are generated for use in subsequent review processes. This compliance indicator ultimately serves as a key judgment basis in the settlement process, establishing a clear and traceable business judgment link for the overall automated verification process.

[0056] S8: Based on the matching results, perform question marking on records that do not match or trigger abnormal conditions according to the question marking mechanism to form question records; Based on the matching results, question marks are applied to unmatched records or records that trigger abnormal conditions according to the question marking mechanism, forming question records, including: S81: Based on the matching results, identify unmatched records and target records that trigger abnormal conditions during the matching field comparison process; S82: Based on the question marking rules, perform anomaly type determination and marking on the key fields of the target record to generate question marking results; among which, the question marking rules include field missing rules, format anomaly rules, cross-field conflict rules, and value out of bounds rules; S83: Generate question records based on the question marking results.

[0057] In this embodiment, after the matching comparison is completed, the target record that needs to enter the question handling process is automatically identified based on the matching results. First, the matching results are scanned to filter two types of records: one type is unmatched records that failed to match the corresponding system data during the comparison stage; the other type is records that, although candidate records were found, triggered abnormal conditions during the field comparison process, such as inconsistent fields, dates outside the legal range, or amount differences exceeding a set threshold. These two types of records are merged to form a "question candidate set," providing input for subsequent question marking.

[0058] Subsequently, the above target records are individually assessed for anomalies according to the question marking rules. The question marking rules consist of four categories: field missing rules, used to identify cases where required fields in report records are missing or empty; format anomaly rules, used to detect discrepancies between field content and standardized formats, such as date parsing failures or illegal characters in amounts; cross-field conflict rules, used to identify logical inconsistencies between multiple fields, such as discrepancies between payment period and creation date, or discrepancies between the product of amount and quantity; and value out-of-bounds rules, used to determine whether field values ​​exceed the business's allowed range, such as negative amounts or zero quantities that are not permitted by the business type. The above rule chain is executed for each target field, and a corresponding anomaly label is generated based on the triggered anomaly conditions.

[0059] After completing the field-level anomaly detection, the anomaly tags are merged with the original record information to generate a structured question tag result. Each question record not only includes the triggered anomaly type but also the field name, original field value, standardized field value, and anomaly detection criteria, facilitating quick identification of the problem source by subsequent manual reviewers. Finally, all records with question tags are aggregated into a question record set, serving as an important basis for subsequent review, supplier communication, or template rule adjustments, achieving automatic identification and end-to-end traceable management of anomaly data.

[0060] S9: Generate a settlement processing report based on the matching results, compliance indicators, and question records.

[0061] Please see Figure 3 This application also provides an Excel report data and system data matching and settlement processing system, the system including: The template configuration module is used to obtain template configuration content for data parsing. The template configuration content includes field format rules, table header mapping rules, and matching field rules. The first building module is used to construct a unified field format model based on field format rules; The report parsing module is used to obtain the content of the Excel report to be processed, and perform header recognition and field mapping based on the header mapping rules to form structured report data; The field standardization module is used to perform field format conversion and content normalization on structured report data according to a unified field format model to obtain standardized report data. The matching identifier generation module is used to construct a matching condition group between report data and system data based on matching field rules, and generate a unique matching identifier. The data comparison module is used to compare standardized report data with system data one by one based on a unique matching identifier, and generate matching results. The compliance judgment module is used to judge the compliance of the matching results based on preset compliance conditions and generate compliance labels; The question marking module is used to mark unmatched records or records that trigger abnormal conditions based on the matching results and according to the question marking mechanism, thus forming question records; The settlement report generation module is used to generate a settlement processing report based on the matching results, compliance indicators, and question records.

[0062] It is understood that the content of the above method embodiments is applicable to this system embodiment. The specific functions implemented in this system embodiment are the same as those in the above method embodiments, and the beneficial effects achieved are also the same as those achieved in the above method embodiments.

[0063] This application also provides an electronic device, which includes a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the above-described method. This electronic device can be any smart terminal, including tablet computers, in-vehicle computers, etc.

[0064] It is understood that the content of the above method embodiments is applicable to this device embodiment. The specific functions implemented by this device embodiment are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0065] This application also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described method.

[0066] It is understood that the content of the above method embodiments is applicable to this storage medium embodiment. The specific functions implemented in this storage medium embodiment are the same as those in the above method embodiments, and the beneficial effects achieved are also the same as those achieved in the above method embodiments.

[0067] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the above-described method.

[0068] It is understood that the content of the above method embodiments is applicable to the embodiments of this program product. The specific functions implemented by the embodiments of this program product are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0069] Memory, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs and non-transitory computer-executable programs. Furthermore, memory may include high-speed random access memory, and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid-state storage device. In some embodiments, memory may optionally include memory remotely located relative to the processor, and these remote memories can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0070] This application provides a method, system, device, and storage medium for matching and settling Excel report data with system data. This solution introduces template configuration to achieve parameterized management of field format rules, header mapping rules, and matching field rules, enabling flexible adaptation of the report parsing process to various Excel report formats and significantly reducing manual configuration costs. By constructing a unified field format model and performing format conversion and content normalization on structured report data, it solves the problem of differences in field types, naming methods, and data specifications among reports from different sources, improving the standardization and consistency of the data processing process and providing a stable data foundation for subsequent automatic matching. Based on matching field rules, matching condition groups are constructed, and unique matching identifiers are generated, allowing standardized report data to be accurately compared with system data line by line. This effectively avoids omissions, mismatches, and inefficiencies that occur in traditional manual verification, significantly improving matching accuracy and processing speed. By presetting compliance conditions and performing compliance judgments on the matching results, key settlement indicators in business scenarios can be automatically verified, achieving quantifiable scoring and risk identification of multi-dimensional matching status. Furthermore, this invention intelligently labels unmatched records and records that trigger abnormal conditions through an inquiry marking mechanism, automatically generating inquiry records. This allows for rapid identification of abnormal data, rule conflicts, or field deviations without requiring manual, line-by-line checks, improving overall verification efficiency. Finally, a settlement processing report is generated based on the matching results, compliance indicators, and inquiry records, achieving a closed-loop process from data parsing and matching verification to the presentation of business settlement results. It boasts advantages such as high automation, strong traceability, and timely anomaly handling, providing stable, accurate, and auditable technical support for enterprise settlement operations.

[0071] The embodiments described in this application are for the purpose of more clearly illustrating the technical solutions of the embodiments of this application, and do not constitute a limitation on the technical solutions provided by the embodiments of this application. As those skilled in the art will know, with the evolution of technology and the emergence of new application scenarios, the technical solutions provided by the embodiments of this application are also applicable to similar technical problems.

[0072] Those skilled in the art will understand that the technical solutions shown in the figures do not constitute a limitation on the embodiments of this application, and may include more or fewer steps than shown, or combine certain steps, or different steps.

[0073] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0074] Those skilled in the art will understand that all or some of the steps in the methods disclosed above, as well as the functional modules / units in the systems and devices, can be implemented as software, firmware, hardware, or suitable combinations thereof.

[0075] The terms “first,” “second,” “third,” “fourth,” etc. (if present) in the specification and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms “comprising” and “having,” and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0076] The preferred embodiments of the present application have been described above with reference to the accompanying drawings, but this does not limit the scope of the claims of the present application. Any modifications, equivalent substitutions, and improvements made by those skilled in the art without departing from the scope and substance of the embodiments of the present application shall be within the scope of the claims of the present application.

Claims

1. A method for matching and settling Excel report data with system data, characterized in that, The method includes: Obtain the template configuration content used for data parsing, which includes field format rules, table header mapping rules, and matching field rules; Based on the aforementioned field format rules, a unified field format model is constructed; The Excel report content to be processed is obtained, and header recognition and field mapping are performed based on the header mapping rules to form structured report data; Based on the unified field format model, the structured report data is subjected to field format conversion and content normalization processing to obtain standardized report data; Based on the matching field rules, a matching condition group for report data and system data is constructed, and a unique matching identifier is generated; Based on the unique matching identifier, the standardized report data is compared with the system data one by one to generate matching results; Based on preset compliance conditions, the matching results are judged to meet the standards, and a compliance identifier is generated; Based on the matching results, question marks are applied to unmatched records or records that trigger abnormal conditions according to the question marking mechanism to form question records; A settlement processing report is generated based on the matching results, compliance indicators, and question records.

2. The method according to claim 1, characterized in that, The field format rules include field type definition rules, field standardization rules, and desensitization format parsing rules; The field type definition rules are used to determine the type attribute of a field; The field standardization rules are used to perform format unification and value range normalization on field content according to field type; The de-identification format parsing rules are used to identify and parse the de-identified field representation in Excel reports.

3. The method according to claim 1, characterized in that, The step of constructing a unified field format model based on the field format rules includes: Based on the field format rules, the type attributes, value range, date format, numeric format, and desensitization format of each field to be parsed are standardized and defined to obtain a set of field format benchmarks; Based on the differences in field naming, display format and desensitization methods among various types of Excel reports, synonym mapping parsing is performed on fields with the same business meaning to generate a field format mapping relationship table; Based on the aforementioned field format benchmark set and field format mapping table, abstract and unified modeling is performed on Excel report fields of different formats to form a unified field format model.

4. The method according to claim 1, characterized in that, The step of constructing a matching condition group for report data and system data based on the matching field rules, and generating a unique matching identifier, includes: Based on the matching field rules, key fields are extracted; the key fields include identifier fields, time fields, and business attribute fields. Based on the key fields, perform format unification, desensitization value restoration and content normalization on the corresponding fields in the report data and system data to obtain comparable field values; Field-level matching conditions are constructed based on the comparable field values, and matching condition groups are generated based on multiple matching conditions in a preset combination manner; Based on the matching condition group, perform connection, sorting and encryption encoding processes to generate a unique matching identifier corresponding to a single report data.

5. The method according to claim 1, characterized in that, The process of comparing standardized report data with system data line by line based on the unique matching identifier to generate matching results includes: Based on the unique matching identifier, the target record corresponding to the unique matching identifier is located from the standardized report data and system data respectively, and a comparison data pair is constructed. The comparison range is parsed for the field set in the comparison data pair, and the corresponding comparison strategy is determined according to the field type; the comparison strategy includes numerical equivalence comparison, date standardization comparison and text attribute comparison. Based on the comparison strategy, field-level comparison is performed on the corresponding fields of the target record to generate a structured comparison result; Based on the structured comparison results, the comparison records are classified into successful matching records and abnormal matching records, and the matching results are summarized and generated.

6. The method according to claim 1, characterized in that, The step of judging the matching results based on preset compliance conditions and generating a compliance identifier includes: Based on the matching results, a set of target field values ​​is extracted; the set of target field values ​​includes numeric fields, date fields, and business attribute fields. According to the preset compliance conditions, condition comparison and rule calculation are performed on the target field value set to generate compliance judgment results; the compliance conditions include threshold judgment conditions, logical combination conditions and cross-field association conditions; Based on the compliance determination results, a compliance label is generated.

7. The method according to claim 1, characterized in that, Based on the matching results, the process of marking unmatched records or records that trigger abnormal conditions according to the question marking mechanism to form question records includes: Based on the matching results, identify unmatched records and target records that triggered abnormal conditions during the matching field comparison process; According to the question marking rules, anomaly type determination and marking are performed on the key fields of the target record to generate question marking results; the question marking rules include field missing rules, format anomaly rules, cross-field conflict rules, and value out-of-bounds rules. Based on the question marking results, a question record is generated.

8. A system for matching and settling Excel report data with system data, characterized in that, The system is used to perform the method as described in any one of claims 1-7, the system comprising: The template configuration module is used to obtain template configuration content for data parsing. The template configuration content includes field format rules, table header mapping rules, and matching field rules. The first construction module is used to construct a unified field format model according to the field format rules; The report parsing module is used to obtain the content of the Excel report to be processed, and perform header recognition and field mapping based on the header mapping rules to form structured report data; The field standardization module is used to perform field format conversion and content normalization processing on the structured report data according to the unified field format model to obtain standardized report data. The matching identifier generation module is used to construct a matching condition group between report data and system data based on the matching field rules, and generate a unique matching identifier. The data comparison module is used to compare standardized report data with system data one by one based on the unique matching identifier and generate matching results; The compliance judgment module is used to judge the compliance of the matching results based on preset compliance conditions and generate a compliance identifier; The question marking module is used to mark unmatched records or records that trigger abnormal conditions according to the question marking mechanism based on the matching results, thereby forming question records; The settlement report generation module is used to generate a settlement processing report based on the matching results, compliance indicators, and question records.

9. An electronic device, characterized in that, include: At least one processor; At least one memory for storing at least one program; When the at least one program is executed by the at least one processor, the at least one processor implements the method as described in any one of claims 1-7.

10. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the method of any one of claims 1 to 7.

Images