Excel import and export assembly system based on modular design

Abstract

The invention relates to the technical field of computers, and discloses an Excel import and export component system based on modular design, comprising the following modules: a field mapping module for automatically performing field mapping and generating a field mapping relationship according to semantic similarity between fields of a source system and a target system; the invention further provides an Excel import and export method based on modular design. The method comprises the following steps that a source data file is uploaded, file content is analyzed, a source field is recognized, and field mapping and data processing are prepared; a mapping relation between a source field and a target field is automatically generated through a field mapping module, and the mapping relation is optimized under the support of an optimization module. According to the method, the whole data processing process is divided into five independent modules including field mapping, data type conversion, data cleaning and anomaly detection, optimization and user interface, so that the efficient and automatic data processing effect is achieved, and the problems of performance bottleneck and non-extensibility when a traditional system faces complex tasks are solved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and particularly to an Excel import / export component system based on modular design. Background Art

[0002] Excel, as a widely used data management tool, is widely applied in various fields such as enterprises, education, and scientific research. It plays a crucial role especially in data storage, processing, and analysis. Due to its powerful data processing and analysis capabilities, Excel has become the preferred tool for many enterprises when recording business data and generating reports. However, with the continuous growth of data volume and business requirements, solely relying on Excel for data management gradually exposes some problems. Especially when it comes to data import / export, data exchange between systems, and batch data processing, the traditional Excel data import / export technology begins to seem inadequate.

[0003] Currently, the import / export of Excel data usually relies on manual operations or tools based on preset rules. Users need to manually select the source Excel file and upload it to the target system or tool. The system will import the data in the source Excel file into the target system through the preset field mapping rules, or export the data in the target system to the Excel format. The system usually performs basic formatting on the source data to ensure its adaptation to the requirements of the target system. The data will undergo necessary type conversion and field mapping to complete data exchange or storage.

[0004] However, the existing Excel import / export technology can meet the basic data exchange requirements to a certain extent. The import / export process relies on manual intervention, resulting in low processing efficiency and easy errors. Especially when faced with complex data or a large amount of data, manual operations are not only cumbersome but also prone to errors. The existing Excel import / export tools usually lack flexibility and cannot handle complex field mapping and data format conversion problems between different systems. Tasks such as data type conversion and date format conversion often require manual configuration, and the conversion effect cannot guarantee consistency. Data cleaning and anomaly detection processing are relatively weak, lacking an intelligent mechanism for identifying and repairing abnormal data, resulting in the difficulty of effectively guaranteeing the data quality during the data import / export process. Summary of the Invention

[0005] Aiming at the deficiencies of the existing technology, the present invention provides an Excel import / export component system based on modular design, which solves the problem that the existing Excel import / export technology cannot flexibly adapt to the requirements of different business scenarios, resulting in easy errors during data conversion and integration and reducing work efficiency.

[0006] To achieve the above objectives, the present invention is implemented through the following technical solutions: An Excel import / export component system based on modular design, including the following modules: A field mapping module, which is used to automatically perform field mapping and generate field mapping relationships according to the semantic similarity between the fields of the source system and the target system; A data type conversion module, which is responsible for extracting the field data types in the data source system and converting them into the types and formats required by the target system according to the field mapping relationship; A data cleaning and anomaly detection module, which is used to detect and repair data missing values, duplicate records, and anomaly data problems extracted from the source system in real time; An optimization module, which is used to generate optimal field mapping and data type conversion strategies by means of optimization algorithms and the principle of constrained minimization under the premise of meeting the constraint conditions; A user interface module, which is used to receive the configuration data input by the user and set conversion rules, and perform data import / export operations according to the settings.

[0007] Preferably, the field mapping module generates a preliminary field mapping relationship by calculating the semantic similarity between the source field and the target field. The calculation formula for the semantic similarity is: where S ij represents the semantic similarity between the source field f i and the target field t j , cosine_similarity(f i , t j ) is the cosine similarity of the source field and the target field names, and max(cosine_similarity) is the maximum similarity value of all field pairs.

[0008] Preferably, the data type conversion module realizes the format conversion from the source data to the target data by selecting a data conversion function. The data conversion function is: T(f i , type(f i ), type(t i )) = t i ; where T(f i , type(f i ), type(t i )) represents the conversion function that converts the data type of the source field f i from type(f i ) to the type type(t i ) of the target field t i .

[0009] Preferably, the data cleaning and anomaly detection module is used to detect and repair missing values, duplicate records and abnormal data in the data. The detection formula for the abnormal data is as follows: A(D) = {d j ∣d j ∈D}; where D represents the dataset to be detected, d j represents the j-th data in the dataset, and d j ∈D where d j is the abnormal data in the dataset D, and A(D) represents the dataset where anomalies are detected.

[0010] Preferably, the optimization module solves for the optimal field mapping relationship through an optimization algorithm. The optimization objective function is as follows: where E data,i represents the data error of the i-th field, E mapping,i represents the mapping error of the i-th field, and E conversion,i represents the conversion loss of the i-th field.

[0011] Preferably, the field mapping module includes: A field semantic matching unit, which is used to calculate the semantic similarity between the source field and the target field through natural language processing technology and automatically generate a preliminary field mapping relationship; A similarity calculation unit, which is used to calculate the cosine similarity between each pair of source fields and target fields and generate a field mapping candidate set; A mapping optimization unit, which optimizes the preliminary mapping relationship based on the optimal solution output by the optimization module to ensure the best match between the source field and the target field and solve possible mapping errors.

[0012] Preferably, the data type conversion module includes: A data format recognition unit, which is used to recognize the data type of the source field and determine the data type of the target field according to the requirements of the target system; A type conversion function unit, which selects and applies an appropriate data conversion function according to the requirements of the target field to implement the data type conversion and ensure that the source data can adapt to the target data format; A conversion verification unit, which is used to detect errors in the data conversion process to ensure that the data after the data type conversion will not be lost or incorrect.

[0013] Preferably, the data cleaning and anomaly detection module includes: Anomaly detection unit, which is used to detect missing values, duplicate records and abnormal data in the data, and judge the validity of the data according to preset rules; Anomaly repair unit, after detecting abnormal data, automatically repairs data problems according to preset rules and machine learning models, including filling missing values, deleting duplicate records, and correcting abnormal values that do not conform to the rules; Data verification unit, which is used to perform secondary verification on the repaired data to ensure the integrity and consistency of the data after repair.

[0014] Preferably, the optimization module includes: Objective function generation unit, which is used to construct an optimization objective function according to field mapping error, data type conversion error and data conversion loss factors. The objective of this objective function is to minimize the error in the overall data import and export process; Constraint condition setting unit, which is used to set constraint conditions in the optimization process according to the rules set by the system, including data type consistency, data integrity, field mapping relationship, data range limit, format requirements and performance constraints; Optimization solution unit, which solves the optimal solution by applying linear programming and non-linear programming algorithms, ensures the minimum error in the field mapping and data conversion process, and optimizes the import and export efficiency on the premise of ensuring data consistency.

[0015] The present invention also provides an Excel import and export method based on modular design, including the following steps: Upload the source data file and parse the file content, identify the source fields and prepare for field mapping and data processing; Automatically generate the mapping relationship between the source fields and the target fields through the field mapping module, and optimize this mapping relationship with the support of the optimization module; Convert the data type of the source data field into the data format required by the target system through the data type conversion module; The data cleaning and anomaly detection module identifies and repairs missing values, duplicate records and other abnormal data in the data to ensure the quality and consistency of the data; Generate the optimal data mapping and conversion strategy through the optimization module to ensure the high efficiency and minimum error in the data import and export process.

[0016] The present invention provides an Excel import and export component system based on modular design, which has the following beneficial effects: 1. The Excel import and export component system based on modular design is adopted in the present invention. By dividing the entire data processing process into five independent modules, namely field mapping, data type conversion, data cleaning and anomaly detection, optimization, and user interface, an efficient and automated data processing effect is achieved. Compared with the complex systems in the prior art that mix all functions together, the present invention realizes higher flexibility and scalability through modular design, avoiding the performance bottleneck and non-scalability problems of traditional systems when facing complex tasks.

[0017] 2. In the present invention, during the process of data field mapping and data type conversion by the optimization module, the field mapping strategy is automatically adjusted and optimized, thus significantly reducing errors and improving data accuracy. Compared with the manual or rule-driven mapping methods in the prior art, the optimization algorithm of the present invention can perform intelligent optimization based on the data itself, greatly improving the accuracy and efficiency of data import and export, and reducing the occurrence of manual intervention and errors.

[0018] 3. The present invention automatically identifies and repairs missing values, duplicate records, and abnormal data in the data through a rule engine and machine learning algorithms, significantly improving the quality of the data. Compared with the traditional technology that only performs simple rule verification and manual repair, the present invention provides an intelligent and automated solution, which can efficiently handle various abnormal situations in large-scale data and ensure data consistency and integrity.

[0019] 4. Through a simplified operation process, the present invention enables users to easily configure import and export rules, view the processing progress in real time, and generate data reports, enhancing the user experience. Compared with the cumbersome operation interface and complex configuration process in the prior art, the present invention, through a simple and intuitive interface design, allows users to quickly and accurately complete tasks whether setting rules or checking the data status, improving the usability and operation convenience of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] Figure 1 is the system architecture diagram of the present invention; Figure 2 is the schematic diagram of the field mapping module of the present invention; Figure 3 is the schematic diagram of the data type conversion module of the present invention; Figure 4 is the schematic diagram of the data cleaning and anomaly detection module of the present invention; Figure 5 is the schematic diagram of the optimization module of the present invention; Figure 6 is the flowchart of the method steps of the present invention. Detailed implementation manners

[0021] Next, in combination with the accompanying drawings of the present invention specification, the technical solutions in the embodiments of the present invention will be clearly and completely described. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0022] Please refer to the attached Figure 1 - attached Figure 5 , the embodiment of the present invention provides an Excel import / export component system based on modular design. Through a modular architecture and an intelligent data processing process, including a field mapping module, a data type conversion module, a data cleaning and anomaly detection module, an optimization module, and a user interface module, efficient data import / export between different data sources and target systems is realized, thereby improving the accuracy and efficiency of data processing.

[0023] For the field mapping module, in this embodiment, according to the field names and semantic similarities between the source data and the target data system, the field mapping work is automatically completed. By calculating the semantic similarity between the source field and the target field, and generating an accurate field mapping relationship according to the calculation result, seamless import and export of data are realized.

[0024] In this embodiment, the main task of the field mapping module is to solve the differences in field names and semantic inconsistencies between the source system and the target system; for this purpose, the field mapping module first analyzes the source field and the target field, and uses natural language processing technology and the cosine similarity algorithm to calculate the semantic similarity of the field names. In this way, it can be ensured that even if the names of the source field and the target field are different, but semantically the same or similar, the system can still correctly map them together.

[0025] Generally, there may be differences in the names of the source field and the target field. Even in some cases, the names of the fields are completely different, but their actual meanings are the same; for example, in a sales system, the source field may be named "order_date", while the target system may name this field "purchase_date". By calculating the semantic similarity between the source field and the target field, the field mapping module can automatically determine that these two fields are the same and establish a corresponding relationship.

[0026] As an option, the field mapping module uses the cosine similarity algorithm to calculate the semantic similarity between the source field and the target field, and the formula is as follows: Among them, S ijRepresents the source field f i and the target field t j The semantic similarity between them, cosine_similarity(f i , t j ) is the cosine similarity of the source field and target field names, and max(cosine_similarity) is the maximum similarity value for all field pairs. By calculating the semantic similarity, the system can determine whether two fields belong to the same category and map them together.

[0027] Specifically, when processing data, the field mapping module first calculates the similarity for each pair of source and target fields. After the calculation is completed, the module generates a candidate set of field mappings based on the similarity values. During this process, if the similarity value of a certain field exceeds the set threshold, the system will consider it as a field that can be mapped and generate a preliminary mapping relationship.

[0028] In a possible implementation, the field mapping module works in cooperation with the optimization module. The role of the optimization module is to further optimize the field mapping relationship to ensure that the matching relationship between the source field and the target field is as accurate as possible. Specifically, after the field mapping module generates a preliminary mapping relationship, the optimization module adjusts the mapping relationship according to the optimization algorithm to minimize the mapping error of each field. The optimization process ensures the accuracy and efficiency of field mapping.

[0029] In some embodiments, the optimization module applies optimization algorithms such as linear programming or nonlinear programming to solve the optimal field mapping relationship. By iteratively adjusting the mapping relationship between the source field and the target field, the optimization module can minimize the error in field mapping and make the data import / export process more accurate and efficient.

[0030] In the embodiments of the present invention, the field mapping module can also cooperate with other modules for more complex data processing. For example, when the semantic similarity of the source field is low, the field mapping module cooperates with the data cleaning and anomaly detection module to identify and repair anomalies in the data, thereby improving the accuracy of the mapping relationship. The field mapping module can automatically detect field differences between different systems and perform automatic adjustment through optimization algorithms to ensure less manual intervention in the complex field mapping process.

[0031] Through these technical means, the field mapping module of the present invention can flexibly adapt to the requirements of various data sources and target systems, automatically complete field mapping, and greatly improve the efficiency and accuracy of data import / export.

[0032] For the data type conversion module, in this embodiment, it is ensured that the data in the source system can be accurately converted into the format and data type required by the target system. Since different systems may have differences in data formats and data types, the data type conversion module undertakes the tasks of formatting and type conversion during the data transmission process to ensure data consistency and integrity.

[0033] The function of the data type conversion module is to ensure that the data format and type of each field are correctly converted during the process of data from the source system to the target system. For example, the date field in the source system may adopt the format of "MM / DD / YYYY", while the target system may require the format of "YYYY-MM-DD". The data type conversion module needs to automatically detect and perform formatting conversion to ensure that the data can be correctly stored in the target system.

[0034] Generally, the data type conversion module will receive the field mapping relationships provided by the field mapping module and perform subsequent data conversion work based on these mapping relationships. This module can automatically identify the data type of the source field and select the appropriate conversion function according to the requirements of the target system. Specifically, the data format recognition unit will analyze the data type of the source field, identify whether it is a date, number, string, etc., and select the corresponding conversion method according to the needs of the target system.

[0035] As an option, the data type conversion module uses a conversion function to perform format conversion on the field. The form of the conversion function can vary dynamically according to the data requirements of the target system. For example, for a date field, the system may convert the format of "MM / DD / YYYY" to "YYYY-MM-DD", or adjust the precision or unit of the field according to the requirements of the target system. The specific conversion function can be expressed as: T(f i ,type(f i ),type(t i ))=t i ; where, T(f i ,type(f i ),type(t i )) represents the conversion function that converts the data type of the source field f i from type(f i ) to the type type(t i ) of the target field t i .

[0036] In actual implementation, when the data type of the source field is inconsistent with that of the target field, the data type conversion module performs type conversion by selecting an appropriate conversion function. For example, for numeric type fields, if the field in the source system has a higher precision and the target system requires a lower precision, the system will automatically adjust the precision. In addition, fields with different formats such as date formats, currency symbols, and timestamps will also be adjusted through corresponding conversion functions to ensure compliance with the requirements of the target system.

[0037] In a possible implementation, the data type conversion module not only performs single data conversion, but also supports multi-level formatting and conversion. For example, the conversion of a date field may involve adjusting the date format and also requires converting the time zone, or converting a string type date field into an actual date object. During this process, the data type conversion module will ensure that no information is lost during the format conversion.

[0038] Specifically, the working process of the data type conversion module is as follows: This module first identifies the data type of the source field, which can be determined by methods such as regular expressions and data analysis. For example, by checking whether a string conforms to a date format, or by parsing the precision of a numeric field to determine the type of the field.

[0039] After identifying the data type, the data type conversion module selects the most appropriate conversion function according to the requirements of the target system to perform the conversion operation. If the target system requires a certain field to be of date type, a date conversion function is used for conversion. If the target system requires converting a certain currency field from US dollars to RMB, the system will perform the conversion of currency units.

[0040] After the conversion operation is completed, the system will perform verification to ensure that the converted data meets the data type requirements of the target system. If there are errors or data loss during the conversion process, the system will automatically issue a warning to remind the user to conduct manual review.

[0041] In some embodiments, the data type conversion module further includes a conversion verification unit for verifying whether the converted data conforms to the expected format. This unit can verify the data in various ways. For example, by setting an error threshold to detect whether the precision of the numeric field after conversion meets the requirements, or by checking whether the length of the string field conforms to the standards of the target system.

[0042] There is a close cooperation relationship between the data type conversion module and the field mapping module. The field mapping module first completes the matching of field names and passes the mapping relationship to the data type conversion module. The data type conversion module performs the corresponding format conversion work according to these mapping relationships.

[0043] Meanwhile, the data type conversion module also collaborates with the optimization module to ensure that no additional errors are introduced during the conversion process. The optimization module can automatically adjust the conversion strategy according to the mapping relationship between the source field and the target field, as well as the conversion requirements of the data type, so as to achieve the optimal conversion effect and minimize data errors to the greatest extent.

[0044] During the entire process of data import and export, the data type conversion module, in cooperation with the field mapping module, the optimization module, and the data cleaning and anomaly detection module, ensures that the data can be accurately completed during the format and type conversion process, reduces human intervention, and improves the efficiency and accuracy of data import and export.

[0045] For the data cleaning and anomaly detection module, in this embodiment, it detects and repairs problems such as missing values, duplicate records, and abnormal data in the imported data. Through this module, the system can ensure that the accuracy and consistency of the data are effectively guaranteed during the data import and export process.

[0046] The data cleaning and anomaly detection module mainly undertakes two core tasks: one is to detect anomalies in the data, and the other is to repair problems in the data. Abnormal data usually refers to problems such as missing values, duplicate records, format errors, and data beyond the preset range. The goal of this module is to automatically identify these anomalies and repair them to ensure that subsequent data processing is not affected.

[0047] Generally, the data cleaning and anomaly detection module will first identify abnormal data in the dataset. For example, the module may identify missing values (such as empty values, null values) according to preset rules, and then use filling strategies (such as filling the average value or default value) to repair these missing values. For duplicate data, the module will identify and remove duplicate records. For data with abnormal formats (such as field values exceeding the range), the system will automatically mark and correct them.

[0048] In some embodiments, the system uses the following formula to detect outliers in the data: A(D) = {d j ∣d j ∈ D}; where D represents the dataset to be detected, d j represents the j-th data in the dataset, d j ∈ D where d j is the abnormal data in the dataset D, and A(D) represents the dataset where anomalies are detected. A(D) is all the abnormal data screened out by the anomaly detection method.

[0049] Through this formula, the system can identify and extract the abnormal parts in the dataset and mark or process them separately. The criteria for judging abnormal data are usually determined by preset rules. For example, for numerical fields, the system may check whether the data is within a reasonable range; for date fields, the system will check whether it conforms to the predetermined date format; for string fields, the system will determine whether the characters conform to the expected format or length.

[0050] Generally, the data cleaning module will analyze the data in real time during the data import process, and identify the abnormalities in the data through a series of preset rules or machine learning algorithms. For example, when the system detects that the value of a certain field is empty, the system will automatically fill in the missing value; when the value of a certain field exceeds the set range, the system will mark it and prompt the user for manual review or automatic correction. Specifically, the data cleaning and anomaly detection module will complete the data cleaning and repair through the following steps: The module will use a rule engine and preset criteria to analyze each record in the dataset. The rules can include "non-empty check" rules, "duplicate data check" rules, "numerical range check" rules, etc. For example, if a certain field value is empty, the module will execute a filling strategy to fill the field with a default value or infer a suitable value from other related fields. For numerical data, if it is found that the value exceeds the preset range, the system will mark or correct it.

[0051] As an option, the data cleaning and anomaly detection module uses machine learning algorithms for anomaly detection. By training the model, the system can automatically learn the normal and abnormal patterns in the data. For example, the system can identify the occurrence of certain anomalies based on the patterns of historical data and mark and repair the abnormal data. For complex data patterns, the machine learning model can continuously improve to enhance the accuracy and efficiency of data cleaning and anomaly detection. Specifically, the data cleaning and anomaly detection module realizes data processing through the following units: Anomaly detection unit: This unit analyzes the data based on a rule engine or a machine learning model and detects missing values, duplicate records, and abnormal data. Anomaly detection is not limited to simple value checks but can also conduct in-depth analysis of the data distribution.

[0052] Anomaly repair unit: After detecting abnormal data, the system will repair the abnormal data through preset rules or a machine learning model. For missing values, the system can choose to fill in the default value or fill it in by inference. For duplicate data, the system will perform deduplication based on the uniqueness identifier of the field. For abnormal values that do not conform to the rules, the system will correct them based on similarity calculation and the data of other fields.

[0053] Data Verification Unit: After data repair, the system will conduct secondary verification on the repaired data to ensure the integrity and consistency of the repaired data. By analyzing the repaired data again, the system can discover and correct possible omitted anomalies, thereby ensuring data accuracy.

[0054] In a possible implementation, the data cleaning and anomaly detection module can also be customized according to the specific needs of users. For example, users can define which fields need to be "null value checked" and which fields need to be "format verified", etc. In this way, users can flexibly adjust the data cleaning strategy according to different data scenarios to meet specific data cleaning requirements.

[0055] For the repair strategy of abnormal data, the system supports multiple options. For example, when encountering missing values, the system can fill them in the following two ways: Using default values: This method is applicable to some non-core fields and fills in a fixed default value, such as "unknown" or "0".

[0056] Using inferred values: For certain business scenarios, the system can infer a suitable filling value by analyzing historical data or other relevant fields. For example, if a field for the delivery address in an order table is empty, the system can infer the value of this field based on the user's historical orders.

[0057] During the data cleaning process, the work of the abnormal data detection part often needs to rely on mathematical formulas to measure the degree of data abnormality. In some embodiments, the system can introduce formulas to calculate the "degree of abnormality" of the data and determine whether repair is needed. For example, for the numerical range problem of a certain field, the system can use a formula to determine whether the value exceeds the predetermined range: E range,i =|x i -μ i |; where, E range,i represents the range error of the i-th field, x i represents the value of this field, μ i represents the expected average value of this field. If E range,i is greater than the preset threshold, it indicates that this data is an outlier, and the system will repair it according to the strategy. In addition, for duplicate record detection, the system can use the following formula to calculate the similarity between records: where, S ij represents the similarity between the i-th record and the j-th record, f ik and f jkThey respectively represent the values of the k-th field in the i-th and j-th records. N is the total number of fields. If the similarity between two records exceeds a preset threshold, the system will regard them as duplicate records and perform deduplication.

[0058] There is a close cooperation relationship between the data cleaning and anomaly detection module and the field mapping module, the data type conversion module, and the optimization module. The field mapping module first completes the mapping of the source data and the target system fields. Then, the data type conversion module performs format conversion on the fields. And the data cleaning and anomaly detection module conducts real-time data anomaly detection and repair throughout the process. In this way, the data cleaning and anomaly detection module ensures the data quality, guaranteeing that the data remains consistent and accurate after field mapping and type conversion.

[0059] For the optimization module, in this embodiment, through the optimization algorithm, according to the constraint conditions set by the system, it minimizes the field mapping error and data conversion error, thereby improving the accuracy and efficiency of data import and export. The optimization module automatically selects the best field mapping relationship and data conversion strategy by optimizing multiple variables, ensuring that the system can achieve the most efficient data processing effect while meeting the conditions.

[0060] The optimization module ensures the consistency and accuracy of data during import and export by calculating and adjusting the mapping relationship between the source field and the target field. Working in coordination with other modules enables the optimization module to automatically identify and correct errors in field mapping, ensuring the integrity and correctness of the final data.

[0061] Specifically, the core function of the optimization module is to adjust and optimize the field mapping and data type conversion solutions based on the existing field mapping relationship using the optimization algorithm. In this process, the optimization module minimizes the mapping and conversion errors through an objective function, thereby ensuring the minimization of data loss and format mismatch during the system import and export process.

[0062] Generally, the optimization module receives the data mapping relationship and data conversion requirements provided by the field mapping module and the data type conversion module. Based on this input information, the optimization module calculates through the algorithm and outputs the optimal field mapping and conversion strategy. For example, when there is a semantic difference between the source field and the target field, the optimization module adjusts the field mapping strategy to reduce the error during field matching.

[0063] As an option, the optimization module uses linear programming or nonlinear programming algorithms to solve for the optimal solution. In these implementations, the optimization module optimizes each field mapping relationship through iterative calculations until a solution with the minimum error is reached. The optimization process is automatic, and the system will automatically adjust the mapping relationship of each field according to the actual data and set rules to ensure the accuracy and efficiency of the import / export task.

[0064] Specifically, during the field mapping and data type conversion processes, the optimization module uses the following objective function to calculate the optimal solution: where E data,i represents the data error of the i-th field, such as the error generated during data type conversion. E mapping,i represents the mapping error of the i-th field, which is used to measure the semantic difference in field names. E conversion,i represents the conversion loss of the i-th field, which is used to represent the information that may be lost during data format conversion. Through the optimization algorithm, the optimal field mapping and data conversion strategy are found between the source data and the target data to minimize the error, thereby ensuring the efficiency and accuracy of data import / export.

[0065] In some embodiments, the optimization module solves the problem in combination with constraint conditions. The constraint conditions may include the minimum similarity of field name matching, the requirement that data types must be consistent, or the tolerance range of conversion accuracy. The system will adjust the solution process of the objective function according to these constraint conditions to ensure that the optimization result meets the actual requirements. For example, the optimization module may set the following constraint conditions: The similarity between the source field and the target field must exceed a certain set threshold to establish a mapping relationship. This constraint ensures the semantic consistency of data fields and avoids incorrect matching.

[0066] The source field and the target field must have the same data type. For example, if the source field is of date type, the target field must also be of date type. If the source field is numeric, the target field must also be numeric.

[0067] The error generated during the conversion process must be less than a certain preset threshold to ensure the accuracy and consistency of the data.

[0068] In this embodiment, the workflow of the optimization module is as follows: The optimization module first receives the input data from the field mapping module and the data type conversion module, including the field mapping relationship and the conversion rules.

[0069] The optimization module constructs an objective function based on the input data and uses a linear programming or nonlinear programming algorithm to solve for the optimal solution.

[0070] The system outputs the optimal field mapping and data conversion scheme according to the solution result to ensure that the error is minimized during the import and export process of data.

[0071] The optimization module closely collaborates with the field mapping module, the data type conversion module, and the data cleaning and anomaly detection module. The mapping relationship provided by the field mapping module provides the initial field matching data for the optimization module, while the data type conversion module provides the requirements for data format conversion for the optimization module. The data cleaning and anomaly detection module ensures the quality of the input data, and the optimization module performs optimization calculations based on these input data to ensure the consistency and accuracy of the data during the import and export process.

[0072] For the user interface module, in this embodiment, it is responsible for providing an intuitive and efficient operation interface for the user. Through this interface, the user can conveniently upload Excel files, set field mapping rules, select data format and type conversion options, and perform import and export operations. The design of the user interface module aims to simplify complex data processing tasks, enabling the user to easily operate and quickly complete relevant import and export tasks.

[0073] The user interface module is the interaction bridge between the system and the user. Its main goal is to provide a simple and easy-to-use interface, enabling the user to complete data import and export operations with the fewest steps. Generally, the functions provided by the user interface module include file upload, rule configuration, status viewing, and report generation, etc. All these functions are presented to the user through a unified interface. The interface design is simple and intuitive, avoiding complex settings and steps for the user during the operation process.

[0074] As an option, the user interface module adopts Web front-end technologies such as Vue.js or React.js, combined with the API services provided by the back-end, and interacts with other modules of the system through HTTP requests. Through this front-end and back-end separation architecture design, the user can directly access and operate the system through a browser without installing additional software, improving the convenience of use.

[0075] Specifically, the working process of the user interface module is as follows: The user selects and uploads the Excel file to be imported through the interface, and the system automatically parses the content of the uploaded file and identifies the fields therein.

[0076] After the file is uploaded, the user can set import and export rules, such as specifying the target system fields, setting field mapping relationships, selecting data type conversion options, etc. The system will provide intelligent prompts to help the user select the correct mapping rules and data conversion options.

[0077] During the data import and export process, users can view the real-time processing progress and status. The user interface module will display the status of the current processing task, allowing users to keep track of the import and export progress at any time.

[0078] When the data import and export task is completed, the system will generate a detailed report. Users can view this report to confirm the correctness and consistency of the data. The report will list all field mapping relationships, data conversion results, and the repair status of any abnormal data.

[0079] In a possible implementation, the user interface module supports storing the rules and parameters set by users through configuration files or databases. When users set specific import and export rules, these rules will be saved and reused in subsequent operations. This design not only reduces the user's operation steps but also improves the efficiency and accuracy of the system.

[0080] For example, in some embodiments, users can select different data format conversion rules through a drop-down menu, such as date format, currency format, etc. In addition, the system will automatically recommend the most suitable field mapping rules based on the content of the Excel file uploaded by the user to improve the user's operation efficiency.

[0081] In some embodiments, the user interface module is not just an operation interface. It also helps users customize certain conversion rules by entering formulas. For example, when configuring data format conversion, users can manually enter the formula for date format conversion: T date (d) = format(d, "YYYY-MM-DD"); where T date (d) represents the date format conversion function, d is the source date field, and format(d, "YYYY-MM-DD") represents converting the source date format to the date format required by the target system. This formula-based setting enables users to flexibly define data processing rules without involving complex programming, further improving the operability and flexibility of the system.

[0082] A method for Excel import and export based on modular design described below can be correspondingly referred to the modular design-based Excel import and export component system described above.

[0083] Please refer to the append Figure 6 , the present invention also provides a method for Excel import and export based on modular design, including the following steps: S1. Upload the source data file and parse the file content, identify the source fields, and prepare for field mapping and data processing; S2. Automatically generate the mapping relationship between the source fields and the target fields through the field mapping module, and optimize this mapping relationship with the support of the optimization module; S3. Convert the data types of the source data fields into the data formats required by the target system through the data type conversion module; S4. The data cleaning and anomaly detection module identifies and repairs missing values, duplicate records, and other abnormal data in the data; S5. Generate the optimal data mapping and conversion strategy through the optimization module.

[0084] For step S1, in this embodiment, the user uploads an Excel file through the user interface module, the system automatically parses the file content, identifies and extracts the source fields. Through the field mapping module, the system will automatically identify the field structure of the source data and prepare for field mapping and data processing. At the same time, the system will analyze the types of data fields and prepare for data format conversion.

[0085] For step S2, in this embodiment, according to the semantic similarity between the source fields and the target fields, a preliminary field mapping relationship is automatically generated. The system uses natural language processing and cosine similarity algorithms to calculate the similarity of field names to ensure the accurate matching of the source fields and the target fields. Through the optimization module, the preliminary mapping relationship will be further optimized to minimize the mapping error and ensure that the field mapping meets the requirements of the target system.

[0086] For step S3, in this embodiment, according to the requirements of the target system, the system converts the field types of the source data into the data formats required by the target fields. For example, the date format may need to be converted from "MM / DD / YYYY" to "YYYY-MM-DD", and the precision of numeric fields will be adjusted according to the requirements of the target system. During this process, the system will select appropriate conversion functions to complete the data type conversion according to the preset rules or the rules configured by the user.

[0087] For step S4, in this embodiment, the missing values, duplicate records, and abnormal data in the data will be scanned and repaired. The system identifies the inconsistencies in the data and repairs them according to the set rules and machine learning models. For example, for missing field values, the system can choose to fill in default values or inferred values. Duplicate records will be detected and deleted by comparing the field contents to ensure the uniqueness of each record. The repair of abnormal data is processed with the support of rules and models to ensure that the data meets the format and accuracy requirements of the target system.

[0088] For step S5, in this embodiment, the optimal field mapping relationship and data conversion strategy are solved through an optimization algorithm. Based on a preset objective function, the system minimizes the field mapping error, data conversion error, and conversion loss to ensure the optimal performance of the import / export process. By applying a linear programming or non-linear programming algorithm, the system outputs the optimal field mapping and data conversion solutions. Finally, after data verification and report generation, the data will be accurately imported into the target system, or the required Excel file will be generated according to the export task.

[0089] The method of this embodiment can be used to implement the above system embodiment, and its principle and technical effect are similar, so they will not be elaborated here.

[0090] Although the embodiments of the present invention have been shown and described, for those of ordinary skill in the art, it can be understood that various changes, modifications, substitutions, and variations can be made to these embodiments without departing from the principle and spirit of the present invention. The scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. An Excel import and export component system based on modular design, characterized in that: Includes the following modules: The field mapping module is used to automatically map fields and generate field mapping relationships based on the semantic similarity between the fields in the source system and the target system; The data type conversion module is responsible for extracting the field data type in the data source system and converting it into the type and format required by the target system according to the field mapping relationship; Data cleaning and anomaly detection module, which is used to detect and repair missing values, duplicate records and abnormal data problems extracted from the source system in real time; The optimization module is used to generate the optimal field mapping and data type conversion strategy under the premise of satisfying the constraints through the optimization algorithm and constraint minimization principle; The user interface module is used to receive configuration data and set conversion rules input by the user, and perform data import and export operations according to the settings.

2. According to the Excel import and export component system based on modular design according to claim 1, it is characterized in that: The field mapping module generates a preliminary field mapping relationship by calculating the semantic similarity between the source field and the target field. The calculation formula of the semantic similarity is: Among them, S ij Indicates the source field f i and the target field t j The semantic similarity between i ,t j ) is the cosine similarity between the source field and the target field name, and max(cosine_similarity) is the maximum similarity value of all field pairs.

3. The Excel import and export component system based on modular design according to claim 1, characterized in that: The data type conversion module realizes the format conversion from source data to target data by selecting a data conversion function, and the data conversion function is: T(f i ,type(f i ),type(t i ))=t i ; Among them, T(f i ,type(f i ),type(t i )) means to convert the source field f i The data type is changed from type(f i ) is converted to the target field t i The type type(t i ) conversion function.

4. The Excel import and export component system based on modular design according to claim 1, characterized in that: The data cleaning and anomaly detection module is used to detect and repair missing values, duplicate records and abnormal data in the data. The detection formula for the abnormal data is: A(D)={d j ∣d j ∈D}; Among them, D represents the data set to be tested, d j represents the jth data in the data set, d j ∈D where d j is the abnormal data in the data set D, and A(D) represents the data set where the anomaly is detected.

5. The Excel import and export component system based on modular design according to claim 1, characterized in that: The optimization module solves the optimal field mapping relationship through an optimization algorithm, and the optimization objective function is: Among them, E data,i represents the data error of the ith field, E mapping,i represents the mapping error of the ith field, E conversion,i represents the conversion loss of the i-th field.

6. The Excel import and export component system based on modular design according to claim 2 is characterized in that: The field mapping module includes: The field semantic matching unit is used to calculate the semantic similarity between the source field and the target field through natural language processing technology, and automatically generate a preliminary field mapping relationship; A similarity calculation unit, used to calculate the cosine similarity between each pair of source fields and target fields, and generate a field mapping candidate set; The mapping optimization unit optimizes the preliminary mapping relationship based on the optimal solution output by the optimization module to ensure the best match between the source field and the target field and resolve possible mapping errors.

7. The Excel import and export component system based on modular design according to claim 3 is characterized in that: The data type conversion module comprises: A data format identification unit is used to identify the data type of the source field and determine the data type of the target field according to the requirements of the target system; The type conversion function unit selects and applies appropriate data conversion functions to achieve data type conversion according to the target field requirements, ensuring that the source data can adapt to the target data format; The conversion check unit is used to detect errors in the data conversion process to ensure that there is no loss or error in the data after the data type conversion.

8. The Excel import and export component system based on modular design according to claim 4 is characterized in that: The data cleaning and anomaly detection module includes: Anomaly detection unit, used to detect missing values, duplicate records and abnormal data in the data, and determine the validity of the data according to preset rules; The anomaly repair unit automatically repairs data problems based on preset rules and machine learning models after detecting abnormal data, including filling missing values, deleting duplicate records, and correcting abnormal values ​​that do not meet the rules; The data verification unit is used to perform secondary verification on the repaired data to ensure the integrity and consistency of the repaired data.

9. The Excel import and export component system based on modular design according to claim 5, characterized in that: The optimization module includes: An objective function generating unit, used to construct an optimization objective function according to field mapping errors, data type conversion errors and data conversion loss factors, the goal of which is to minimize the error in the overall data import and export process; The constraint setting unit is used to set the constraints in the optimization process according to the rules set by the system, including data type consistency, data integrity, field mapping relationship, data range restrictions, format requirements and performance constraints; The optimization solution unit solves the optimal solution by applying linear programming and nonlinear programming algorithms to ensure the minimum error in the field mapping and data conversion process, and optimizes the efficiency of import and export while ensuring data consistency.

10. The Excel import and export method based on modular design is applied to the Excel import and export component system based on modular design as claimed in any one of claims 1 to 9, characterized in that: The following steps are involved: Upload source data files and parse the file contents, identify source fields and prepare for field mapping and data processing; Automatically generate the mapping relationship between source fields and target fields through the field mapping module, and optimize the mapping relationship with the support of the optimization module; The data type conversion module converts the data type of the source data field into the data format required by the target system; The data cleaning and anomaly detection module identifies and repairs missing values, duplicate records, and other abnormal data in the data; Generate optimal data mapping and conversion strategies through the optimization module.

Images