An Excel import and export component system based on modular design

Abstract

This application relates to the field of computer technology and discloses an Excel import / export component system based on modular design, including the following modules: a field mapping module, used to automatically perform field mapping and generate field mapping relationships based on the semantic similarity between fields in the source system and the target system; it also provides an Excel import / export method based on modular design, including the following steps: uploading a source data file and parsing the file content, identifying source fields and preparing for field mapping and data processing; automatically generating the mapping relationship between source fields and target fields through the field mapping module, and optimizing the mapping relationship with the support of the optimization module. This invention achieves highly efficient and automated data processing by dividing the entire data processing process into five independent modules: field mapping, data type conversion, data cleaning and anomaly detection, optimization, and user interface, avoiding the performance bottlenecks and lack of scalability issues of traditional systems when facing complex tasks.

Description

Technical Field

[0001] This invention relates to the field of computer technology, specifically to an Excel import / export component system based on modular design. Background Technology

[0002] Excel, as a widely used data management tool, is extensively applied in various fields such as enterprises, education, and scientific research, playing 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 needs, relying solely on Excel for data management has gradually revealed some problems, particularly in data import and export, inter-system data exchange, and batch data processing, where traditional Excel data import and export techniques have begun to prove inadequate.

[0003] Currently, importing and exporting Excel data usually relies on manual operation 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 from the source Excel file into the target system or export the data from the target system into Excel format through preset field mapping rules. The system usually performs basic formatting processing on the source data to ensure that it is compatible with the requirements of the target system. The data will undergo necessary type conversion and field mapping to complete the data exchange or saving.

[0004] However, existing Excel import and export technologies can meet basic data exchange needs to a certain extent. The import and export process relies on manual intervention, resulting in low processing efficiency and a high risk of errors. Especially when dealing with complex or large amounts of data, manual operation is not only tedious but also prone to errors. Existing Excel import and export tools usually lack flexibility and cannot cope with complex field mapping and data format conversion issues between different systems. Tasks such as data type conversion and date format conversion often require manual configuration, and the conversion results cannot guarantee consistency. Data cleaning and anomaly detection and handling are relatively weak, and there is a lack of intelligent abnormal data identification and repair mechanisms, making it difficult to effectively guarantee data quality during the data import and export process. Summary of the Invention

[0005] To address the shortcomings of existing technologies, this invention provides an Excel import / export component system based on modular design. This system solves the problem that existing Excel import / export technologies cannot flexibly adapt to the needs of different business scenarios, leading to errors during data conversion and integration, and reducing work efficiency.

[0006] To achieve the above objectives, the present invention provides the following technical solution: an Excel import / export component system based on modular design, comprising the following modules:

[0007] The field mapping module is used to automatically map fields and generate field mapping relationships based on the semantic similarity between fields in the source system and the target system.

[0008] The data type conversion module is responsible for extracting the data types of fields in the data source system and converting them into the types and formats required by the target system, based on the field mapping relationship.

[0009] The data cleaning and anomaly detection module is used to detect and repair missing values, duplicate records, and abnormal data in the data extracted from the source system in real time.

[0010] The optimization module is used to generate the optimal field mapping and data type conversion strategy under the premise of satisfying the constraints, through optimization algorithms and the principle of constraint minimization.

[0011] The user interface module is used to receive configuration data and set conversion rules from user input, and to perform data import and export operations according to the settings.

[0012] 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 formula for calculating the semantic similarity is:

[0013]

[0014] Among them, S ij Indicates the source field f i and target field t j The semantic similarity between them, cosine_similarity(f i ,t j ) represents the cosine similarity between the source and target field names, and max(cosine_similarity) is the maximum similarity value among all field pairs.

[0015] Preferably, the data type conversion module performs format conversion from source data to target data by selecting a data conversion function, wherein the data conversion function is:

[0016] T(f i ,type(f i ),type(t i ))=t i ;

[0017] Wherein, T(f i ,type(f i ),type(ti )) indicates that the source field f i The data type is from type(f) i Convert to target field t i type(t) i The conversion function of ).

[0018] 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 formula for detecting abnormal data is:

[0019] A(D)={d j ∣d j ∈D};

[0020] Where D represents the dataset to be detected, d j Let d represent the j-th data item in the dataset. j ∈D where d j A(D) represents the abnormal data in dataset D, where A(D) represents the dataset where anomalies were detected.

[0021] Preferably, the optimization module uses an optimization algorithm to solve for the optimal field mapping relationship, and the optimization objective function is:

[0022]

[0023] Among them, E data,i E represents the data error of the i-th field. mapping,i E represents the mapping error of the i-th field. conversion,i This represents the transformation loss of the i-th field.

[0024] Preferably, the field mapping module includes:

[0025] The field semantic matching unit is used to calculate the semantic similarity between the source field and the target field using natural language processing technology, and automatically generate a preliminary field mapping relationship;

[0026] The similarity calculation unit is used to calculate the cosine similarity between each pair of source fields and target fields, and generate a candidate set of field mappings;

[0027] The mapping optimization unit optimizes the initial mapping relationship based on the optimal solution output by the optimization module, ensuring the best match between the source field and the target field and resolving possible mapping errors.

[0028] Preferably, the data type conversion module includes:

[0029] The data format recognition 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.

[0030] The type conversion function unit selects and applies appropriate data conversion functions according to the requirements of the target field to achieve data type conversion, ensuring that the source data can be adapted to the target data format;

[0031] The conversion verification unit is used to detect errors during the data conversion process, ensuring that the converted data is not lost or erroneous.

[0032] Preferably, the data cleaning and anomaly detection module includes:

[0033] An anomaly detection unit is used to detect missing values, duplicate records, and abnormal data in the data, and to determine the validity of the data according to preset rules.

[0034] The anomaly repair unit automatically repairs data problems according to preset rules and machine learning models after detecting abnormal data, including filling missing values, deleting duplicate records, and correcting outliers that do not conform to the rules.

[0035] The data verification unit is used to perform secondary verification on the repaired data to ensure the integrity and consistency of the repaired data.

[0036] Preferably, the optimization module includes:

[0037] The objective function generation unit is used to construct an optimization objective function based on field mapping error, data type conversion error, and data conversion loss factors. The objective function aims to minimize the error in the overall data import and export process.

[0038] The constraint setting unit is used to set constraints 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;

[0039] The solution unit is optimized by applying linear and nonlinear programming algorithms to find the optimal solution, ensuring that the error in field mapping and data transformation is minimized, and optimizing the efficiency of import and export while ensuring data consistency.

[0040] This invention also provides an Excel import / export method based on modular design, comprising the following steps:

[0041] Upload the source data file and parse its contents, identify the source fields, and prepare for field mapping and data processing.

[0042] The field mapping module automatically generates the mapping relationship between the source field and the target field, and optimizes the mapping relationship with the support of the optimization module.

[0043] The data type conversion module converts the data type of the source data fields into the data format required by the target system.

[0044] The data cleaning and anomaly detection module identifies and repairs missing values, duplicate records, and other abnormal data in the data to ensure data quality and consistency.

[0045] The optimization module generates optimal data mapping and transformation strategies to ensure efficiency and minimize errors during the data import and export process.

[0046] This invention provides an Excel import / export component system based on modular design. It offers the following advantages: 1. This invention employs an Excel import / export component system based on modular design. By dividing the entire data processing process into five independent modules—field mapping, data type conversion, data cleaning and anomaly detection, optimization, and user interface—it achieves highly efficient and automated data processing. Compared to existing complex systems that mix all functions together, this invention achieves greater flexibility and scalability through modular design, avoiding the performance bottlenecks and lack of scalability issues inherent in traditional systems when facing complex tasks.

[0047] 2. This invention uses an optimization module to automatically adjust and optimize field mapping strategies during data field mapping and data type conversion, thereby significantly reducing errors and improving data accuracy. Compared with existing manual or rule-driven mapping methods, the optimization algorithm of this invention can perform intelligent optimization based on the data itself, greatly improving the accuracy and efficiency of data import and export, and reducing manual intervention and errors.

[0048] 3. This invention automatically identifies and repairs missing values, duplicate records, and abnormal data in data through a rule engine and machine learning algorithms, which significantly improves data quality. Compared with traditional technologies that rely solely on simple rule verification and manual repair, this invention provides an intelligent and automated solution that can efficiently handle various anomalies in large-scale data and ensure data consistency and integrity.

[0049] 4. This invention simplifies the operation process, enabling users to easily configure import and export rules, view processing progress in real time, and generate data reports, thus improving the user experience. Compared with the cumbersome operation interface and complex configuration process in the prior art, this invention, through its simple and intuitive interface design, allows users to complete tasks quickly and accurately, whether setting rules or checking data status, thereby improving the system's usability and ease of operation. Attached Figure Description

[0050] Figure 1 This is a system architecture diagram of the present invention;

[0051] Figure 2 This is a schematic diagram of the field mapping module of the present invention;

[0052] Figure 3 This is a schematic diagram of the data type conversion module of the present invention;

[0053] Figure 4 This is a schematic diagram of the data cleaning and anomaly detection module of the present invention;

[0054] Figure 5 This is a schematic diagram of the optimized module of the present invention;

[0055] Figure 6 This is a flowchart of the method steps of the present invention. Detailed Implementation

[0056] The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0057] Please see the appendix Figure 1 - Appendix Figure 5 This invention provides an Excel import / export component system based on modular design. Through a modular architecture and intelligent data processing flow, 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, it enables efficient data import and export between different data sources and target systems, thereby improving the accuracy and efficiency of data processing.

[0058] For the field mapping module, in this embodiment, the field mapping work is automatically completed based on the field names and semantic similarity between the source data and the target data system. By calculating the semantic similarity between the source field and the target field, and generating an accurate field mapping relationship based on the calculation result, the seamless import and export of data can be achieved.

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

[0060] Generally, the names of source and target fields may differ, and in some cases, the field names may even be 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 the field "purchase_date". By calculating the semantic similarity between the source and target fields, the field mapping module can automatically determine that the two fields are the same and establish a corresponding relationship.

[0061] Alternatively, the field mapping module uses a cosine similarity algorithm to calculate the semantic similarity between the source and target fields, as shown in the following formula:

[0062]

[0063] Among them, S ij Indicates the source field f i and target field t j The semantic similarity between them, cosine_similarity(f i ,t j ) represents the cosine similarity between the source and target field names, and max(cosine_similarity) is the maximum similarity value among all field pairs. By calculating semantic similarity, the system can determine whether two fields belong to the same category and map them together.

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

[0065] In one possible implementation, the field mapping module and the optimization module work together. The optimization module further optimizes the field mapping relationship, ensuring that the matching relationship between the source field and the target field is as accurate as possible. Specifically, after the field mapping module generates the initial 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 the field mapping.

[0066] In some embodiments, the optimization module solves for the optimal field mapping relationship by applying optimization algorithms, such as linear programming or nonlinear programming. By iteratively adjusting the mapping relationship between the source and target fields, the optimization module can minimize errors in the field mapping and make the data import and export process more accurate and efficient.

[0067] In this embodiment of the invention, the field mapping module can also be combined with other modules for more complex data processing. For example, when the semantic similarity of the source fields is low, the field mapping module will cooperate 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 automatically adjust them through optimization algorithms to ensure that manual intervention is reduced in the complex field mapping process.

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

[0069] In this embodiment, the data type conversion module ensures 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 format and data type, the data type conversion module undertakes the formatting and type conversion tasks during data transmission to ensure data consistency and integrity.

[0070] The role 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 transfer from the source system to the target system. For example, the date field in the source system may use the "MM / DD / YYYY" format, while the target system may require the "YYYY-MM-DD" format. The data type conversion module needs to automatically detect and perform the format conversion to ensure that the data can be correctly stored in the target system.

[0071] Generally, the data type conversion module receives the field mapping relationships provided by the field mapping module and performs 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, or other type, and select the corresponding conversion method according to the requirements of the target system.

[0072] Alternatively, the data type conversion module uses conversion functions to perform format conversions on fields. The form of these conversion functions can dynamically change according to the data requirements of the target system. For example, for a date field, the system might convert the "MM / DD / YYYY" format to "YYYY-MM-DD" format, or adjust the precision or unit of the field according to the requirements of the target system. A specific conversion function can be expressed as:

[0073] T(f i ,type(f i ),type(t i))=t i ;

[0074] Wherein, T(f i ,type(f i ),type(t i )) indicates that the source field f i The data type is from type(f) i Convert to target field t i type(t) i The conversion function of ).

[0075] In practice, 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 fields, if the field in the source system has high precision and the target system requires lower precision, the system will automatically adjust the precision. In addition, fields with different formats such as date format, currency symbol, and timestamp will also be adjusted through the corresponding conversion function to ensure that they meet the requirements of the target system.

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

[0077] Specifically, the workflow of the data type conversion module is as follows:

[0078] 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, it can determine the field type by checking whether the string conforms to the date format or by parsing the precision of the numeric field.

[0079] After identifying the data type, the data type conversion module selects the most appropriate conversion function to perform the conversion operation based on the requirements of the target system. If the target system requires a field to be of date type, the date conversion function is used for conversion. If the target system requires a currency field to be converted from USD to RMB, the system will perform currency unit conversion.

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

[0081] 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, such as by setting an error threshold to detect whether the accuracy of the converted numeric field meets the requirements, or by checking whether the length of the string field conforms to the standard of the target system.

[0082] The data type conversion module and the field mapping module have a close working relationship. 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.

[0083] 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 based on the mapping relationship between the source field and the target field, as well as the data type conversion requirements, thereby achieving the best conversion effect and minimizing data errors.

[0084] Throughout the entire data import and export process, the data type conversion module, in conjunction with the field mapping module, optimization module, and data cleaning and anomaly detection module, ensures that data is accurately converted in terms of format and type, reducing human intervention and improving the efficiency and accuracy of data import and export.

[0085] In this embodiment, the data cleaning and anomaly detection module detects and repairs issues such as missing values, duplicate records, and abnormal data in the imported data. Through this module, the system can effectively ensure the accuracy and consistency of the data during the data import and export process.

[0086] The data cleaning and anomaly detection module primarily undertakes two core tasks: first, detecting anomalies in the data, and second, repairing problems within the data. Anomalies typically refer to missing values, duplicate records, incorrect formatting, and data exceeding preset ranges. The goal of this module is to automatically identify and correct these anomalies, ensuring that subsequent data processing is not affected.

[0087] Generally, the data cleaning and anomaly detection module first identifies anomalous data in the dataset. For example, the module may identify missing values ​​(such as null values) according to preset rules, and then use imputation strategies (such as filling with the average or default value) to repair these missing values. For duplicate data, the module will identify and remove duplicate records. For data with abnormal format (such as field values ​​that are out of range), the system will automatically mark and correct them.

[0088] In some embodiments, the system detects outliers in the data using the following formula:

[0089] A(D)={d j ∣d j ∈D};

[0090] Where D represents the dataset to be detected, d j Let d represent the j-th data item in the dataset. j ∈D where d j A(D) represents the abnormal data in dataset D, where A(D) represents the dataset where anomalies were detected, and A(D) is all the abnormal data filtered out by the anomaly detection method.

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

[0092] Generally, the data cleaning module analyzes the data in real time during the data import process, identifying anomalies through a series of preset rules or machine learning algorithms. For example, when the system detects that a field has an empty value, it will automatically fill in the missing value; when the value of a 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 completes data cleaning and repair through the following steps: The module uses a rule engine and preset standards to analyze each record in the dataset. The rules can include "non-empty check" rules, "duplicate data check" rules, "value range check" rules, etc. For example, if a field has an empty value, the module will execute a filling strategy to fill the field with the default value or infer a suitable value from other related fields. For numeric data, if the value is found to exceed the preset range, the system will mark or correct it.

[0093] As an alternative, the data cleaning and anomaly detection module uses machine learning algorithms for anomaly detection. By training the model, the system can automatically learn normal and abnormal patterns in the data. For example, the system can identify the occurrence of certain anomalies through patterns in historical data, and then mark and repair the abnormal data. For complex data patterns, the machine learning model can continuously improve, enhancing the accuracy and efficiency of data cleaning and anomaly detection. Specifically, the data cleaning and anomaly detection module performs data processing through the following units:

[0094] Anomaly Detection Unit: This unit analyzes data based on rule engines or machine learning models and detects missing values, duplicate records, and abnormal data. Anomaly detection is not limited to simple value checks, but can also perform in-depth analysis of data distribution.

[0095] Anomaly Repair Unit: After detecting abnormal data, the system will repair the abnormal data through preset rules or machine learning models. For missing values, the system can choose to fill in the default value or fill in by inference. For duplicate data, the system will perform deduplication based on the unique identifier of the field. For outliers that do not conform to the rules, the system will correct them based on similarity calculation and data from other fields.

[0096] Data verification unit: After data repair, the system will perform secondary verification on the repaired data to ensure the integrity and consistency of the repaired data. By re-analyzing the repaired data, the system can discover and correct any possible omissions, thereby ensuring the accuracy of the data.

[0097] In one possible implementation, the data cleaning and anomaly detection module can also be customized according to the user's specific needs. For example, the user can define which fields need to be "null value checked" and which fields need to be "format validated". In this way, the user can flexibly adjust the data cleaning strategy according to different data scenarios to meet specific data cleaning needs.

[0098] For abnormal data repair strategies, the system supports multiple options. For example, when encountering missing values, the system can fill them in in the following two ways: Use default values: This method is suitable for some non-core fields, filling them with a fixed default value, such as "unknown" or "0".

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

[0100] In the data cleaning process, the anomaly detection section often relies on mathematical formulas to measure the degree of data anomaly. In some embodiments, the system can introduce formulas to calculate the "degree of anomaly" of the data and determine whether repair is needed. For example, regarding the numerical range issue of a certain field, the system can use a formula to determine whether the value exceeds a predetermined range: E range,i =|x i -μ i |;

[0101] Among them, E range,i x represents the range error of the i-th field. i This represents the value of the field, μ.i This indicates the expected average value of the field. If E range,i If the value exceeds a preset threshold, it indicates that the data is an outlier, and the system will repair it according to the strategy. In addition, for duplicate record detection, the system can calculate the similarity between records using the following formula:

[0102]

[0103] Among them, S ij f represents the similarity between the i-th record and the j-th record. ik and f jk These represent the values ​​of the k-th field in the i-th and j-th records, respectively, where N is the total number of fields. If the similarity between two records exceeds a preset threshold, the system will treat them as duplicate records and perform deduplication.

[0104] The data cleaning and anomaly detection module works closely with the field mapping module, data type conversion module, and optimization module. The field mapping module first maps the source data to the target system fields, the data type conversion module then performs format conversion on the fields, and the data cleaning and anomaly detection module performs real-time data anomaly detection and repair throughout the process. In this way, the data cleaning and anomaly detection module ensures data quality and ensures that the data remains consistent and accurate after field mapping and type conversion.

[0105] In this embodiment, the optimization module uses an optimization algorithm to minimize field mapping errors and data conversion errors based on the system's constraints, thereby improving the accuracy and efficiency of data import and export. By optimizing multiple variables, the optimization module automatically selects the best field mapping relationship and data conversion strategy, ensuring that the system can achieve the most efficient data processing effect while meeting the conditions.

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

[0107] Specifically, the core function of the optimization module is to adjust and optimize the field mapping and data type conversion scheme based on the existing field mapping relationship using optimization algorithms. In this process, the optimization module minimizes the mapping and conversion error through an objective function, thereby ensuring that data loss and format mismatch are minimized during the system import and export process.

[0108] Generally, the optimization module receives data mapping relationships and data conversion requirements from the field mapping module and the data type conversion module. Based on this input information, the optimization module performs calculations through algorithms and outputs the optimal field mapping and conversion strategies. For example, when there are semantic differences between the source field and the target field, the optimization module reduces the error in field matching by adjusting the field mapping strategy.

[0109] As an option, the optimization module uses linear programming or nonlinear programming algorithms to find the optimal solution. In these implementations, the optimization module optimizes the mapping relationship of each field through iterative calculation until the solution with the minimum error is reached. The optimization process is automatic. 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 and export tasks.

[0110] Specifically, the optimization module uses the following objective function to calculate the optimal solution during field mapping and data type conversion:

[0111]

[0112] Among them, E data,i E represents the data error in the i-th field, such as the error caused by data type conversion. mapping,i E represents the mapping error of the i-th field, used to measure the semantic difference in field names. conversion,i This represents the transformation loss of the i-th field, used to indicate information that may be lost during data format conversion. Through optimization algorithms, the optimal field mapping and data transformation strategy are found between the source and target data to minimize errors, thereby ensuring the efficiency and accuracy of data import and export.

[0113] In some embodiments, the optimization module solves the problem by incorporating constraints, which may include a minimum similarity for matching field names, a requirement for consistent data types, or a tolerance range for conversion precision. The system adjusts the solution process of the objective function based on these constraints to ensure that the optimization result meets the actual requirements. For example, the optimization module may set the following constraint: the similarity between the source field and the target field must exceed a certain set threshold in order to establish a mapping relationship. This constraint ensures the semantic consistency of the data fields and avoids mismatches.

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

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

[0116] In this embodiment, the optimization module works as follows: The optimization module first receives input data from the field mapping module and the data type conversion module, including field mapping relationships and conversion rules.

[0117] The optimization module constructs an objective function based on the input data and uses linear programming or nonlinear programming algorithms to find the optimal solution.

[0118] The system outputs the optimal field mapping and data transformation scheme based on the solution results, ensuring that errors are minimized during the data import and export process.

[0119] The optimization module works closely with the field mapping module, data type conversion module, and data cleaning and anomaly detection module. The mapping relationship provided by the field mapping module provides the optimization module with the initial field matching data, while the data type conversion module provides the optimization module with the data format conversion requirements. The data cleaning and anomaly detection module ensures the quality of the input data, and the optimization module performs optimization calculations based on this input data, thereby ensuring the consistency and accuracy of the data during the import and export process.

[0120] In this embodiment, the user interface module provides users with an intuitive and efficient user interface. Through this interface, users can easily upload Excel files, set field mapping rules, select data format and type conversion options, and perform import and export operations. The user interface module is designed to simplify complex data processing tasks, enabling users to easily operate and quickly complete import and export tasks.

[0121] The user interface module serves as an interaction bridge between the system and the user. Its main goal is to provide a simple and easy-to-use interface that allows users to complete data import and export operations with minimal steps. Generally, the user interface module provides functions such as file upload, rule configuration, status viewing, and report generation. All these functions are presented to the user through a unified interface with a simple and intuitive design, avoiding complex settings and steps for users during operation.

[0122] Alternatively, the user interface module employs web front-end technologies such as Vue.js or React.js, combined with API services provided by the backend, to interact with other modules of the system via HTTP requests. This front-end / back-end separation architecture allows users to directly access and operate the system through a browser without installing additional software, thus improving ease of use.

[0123] Specifically, the workflow 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 uploaded file content and identifies the fields within it.

[0124] After uploading a file, users can set import / export rules, such as specifying target system fields, setting field mapping relationships, and selecting data type conversion options. The system will provide intelligent prompts to help users select the correct mapping rules and data conversion options.

[0125] During the data import / export process, users can view the real-time processing progress and status. The user interface module displays the current task status, allowing users to understand the import / export progress at any time.

[0126] Once the data import / export task is complete, 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 transformation results, and the repair status of any abnormal data.

[0127] In one possible implementation, the user interface module supports storing user-set rules and parameters through configuration files or a database. When a user sets specific import / export rules, these rules are saved and reused in subsequent operations. This design not only reduces the number of steps for users but also improves the efficiency and accuracy of the system.

[0128] For example, in some embodiments, users can select different data format conversion rules through drop-down menus, 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, so as to improve the user's operating efficiency.

[0129] In some embodiments, the user interface module is not just an operation interface; it also helps users customize certain conversion rules by inputting formulas. For example, when configuring data format conversion, users can manually input formulas for date format conversion.

[0130] T date (d)=format(d,"YYYY-MM-DD");

[0131] Among them, T date (d) represents the date format conversion function, where d is the source date field. format(d,"YYYY-MM-DD") means converting the source date format to the date format required by the target system. This formulaic setting allows users to flexibly define data processing rules without involving complex programming, further improving the operability and flexibility of the system.

[0132] The Excel import / export method based on modular design described below can be referred to as a modular design Excel import / export component system described above.

[0133] Please see the appendix Figure 6 The present invention also provides an Excel import and export method based on modular design, comprising the following steps:

[0134] S1. Upload the source data file and parse its contents, identify the source fields, and prepare for field mapping and data processing;

[0135] S2. The mapping relationship between the source field and the target field is automatically generated through the field mapping module, and the mapping relationship is optimized with the support of the optimization module;

[0136] S3. Convert the data type of the source data field to the data format required by the target system using the data type conversion module;

[0137] S4. The data cleaning and anomaly detection module identifies and repairs missing values, duplicate records, and other abnormal data in the data.

[0138] S5. Generate the optimal data mapping and transformation strategy through the optimization module.

[0139] 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 automatically identifies the field structure of the source data and prepares to perform field mapping and data processing. At the same time, the system analyzes the type of data fields and prepares for data format conversion.

[0140] For step S2, in this embodiment, a preliminary field mapping relationship is automatically generated based on the semantic similarity between the source field and the target field. The system uses natural language processing and cosine similarity algorithm to calculate the similarity of field names to ensure accurate matching between the source field and the target field. 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.

[0141] 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 format required by the target field. 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 an appropriate conversion function to complete the data type conversion according to preset rules or user-configured rules.

[0142] For step S4, in this embodiment, missing values, duplicate records, and abnormal data in the data will be scanned and repaired. The system identifies 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 the default value or inferred value. Duplicate records will be detected and deleted by comparing field content 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.

[0143] For step S5, in this embodiment, the optimal field mapping relationship and data conversion strategy are solved by optimization algorithm. Based on the preset objective function, the system minimizes the field mapping error, data conversion error and conversion loss to ensure the optimal performance of the import and export process. By applying linear programming or nonlinear programming algorithm, the system outputs the optimal field mapping and data conversion scheme. 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.

[0144] The method in this embodiment can be used to execute the above system embodiment, and its principle and technical effect are similar, so it will not be described again here.

[0145] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. An Excel import / 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 fields in the source system and the target system. It includes: a field semantic matching unit, which calculates the semantic similarity between the source field and the target field using natural language processing technology and automatically generates preliminary field mapping relationships; a similarity calculation unit, which calculates the cosine similarity between each pair of source fields and target fields and generates a candidate set of field mappings; and a mapping optimization unit, which optimizes the preliminary mapping relationships based on the optimal solution output by the optimization module. The data type conversion module is responsible for extracting the data types of fields in the data source system and converting them to the types and formats required by the target system based on the field mapping relationship. This includes: a data format identification unit, which identifies the data type of the source field and determines the data type of the target field according to the requirements of the target system; a type conversion function unit, which selects and applies data conversion functions to achieve data type conversion according to the requirements of the target field; and a conversion verification unit, which detects errors in the data conversion process. The data cleaning and anomaly detection module is used to detect and repair missing values, duplicate records, and abnormal data extracted from the source system in real time. The optimization module is used to generate optimal field mapping and data type conversion strategies under the premise of satisfying constraints, through optimization algorithms and the principle of constraint minimization. Specifically, the optimization module uses optimization algorithms to solve for the optimal field mapping relationship, and the optimization objective function is: ； in, This represents the data error in the i-th field. This represents the mapping error of the i-th field. This represents the transformation loss of the i-th field; The user interface module is used to receive configuration data and set conversion rules from user input, and to perform data import and export operations according to the settings.

2. The Excel import / export component system based on modular design according to claim 1, 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 formula for calculating the semantic similarity is as follows: ； in, Indicates source field and target field Semantic similarity between them Cosine similarity between the source and target field names. This represents the maximum similarity value among all field pairs.

3. The Excel import / export component system based on modular design according to claim 1, characterized in that, The data type conversion module converts the source data to the target data format by selecting a data conversion function. The data conversion function is: ； in, Indicates the source field Data types from Convert to target field Type The conversion function.

4. The Excel import / 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 formula for detecting abnormal data is as follows: ； Where D represents the dataset to be detected. This represents the j-th data item in the dataset. in These are outlier data in dataset D. This indicates a dataset where an anomaly was detected.

5. The Excel import / export component system based on modular design according to claim 4, characterized in that, The data cleaning and anomaly detection module includes: An anomaly detection unit is used to detect missing values, duplicate records, and abnormal data in the data, and to determine the validity of the data according to preset rules. The anomaly repair unit automatically repairs data problems according to preset rules and machine learning models after detecting abnormal data, including filling missing values, deleting duplicate records, and correcting outliers that do not conform to 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.

6. The Excel import / export component system based on modular design according to claim 1, characterized in that, The optimization module includes: The objective function generation unit is used to construct an optimization objective function based on field mapping error, data type conversion error, and data conversion loss factors. The objective function aims to minimize the error in the overall data import and export process. The constraint setting unit is used to set constraints 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; The solution unit is optimized by applying linear and nonlinear programming algorithms to find the optimal solution, ensuring that the error in the field mapping and data transformation process is minimized.

7. An Excel import / export method based on modular design, applied to an Excel import / export component system based on modular design as described in any one of claims 1-6, characterized in that, Includes the following steps: Upload the source data file and parse its contents, identify the source fields, and prepare for field mapping and data processing. The field mapping module automatically generates the mapping relationship between the source field and the target field, and optimizes the mapping relationship with the support of the optimization module. The data type conversion module converts the data type of the source data fields 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. The optimal data mapping and transformation strategy is generated through the optimization module.

Images