Artificial intelligence-based table data and text content bidirectional conversion method

Abstract

The application discloses a table data and text content bidirectional conversion method based on artificial intelligence, and particularly relates to the technical field of data processing; an Excel path, a text path and a primary key field configured by a user are received; a target Excel table is read and structured data is extracted; data is executed with format standardization, missing value completion and error correction; target text content is generated according to a user template; a natural language processing model is used to identify a target field, a target value and a target identification triple from the text; an Excel row is matched in combination with the primary key field, a target cell position is located and the target value is written, so that an updated Excel table is generated; the application realizes semantic bidirectional mapping between Excel and text, and improves the intelligentization and automation level of data processing.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to a method for bidirectional conversion between tabular data and text content based on artificial intelligence. Background Technology

[0002] With the rapid development of data-driven business processes in various enterprises and institutions, a large amount of structured data stored in Excel spreadsheets and unstructured text information in the form of logs, emails, and work orders are generated in daily work. Business scenarios often require efficient and accurate bidirectional conversion between the two, such as batch generating SQL scripts from customer configuration parameters in Excel spreadsheets, or automatically populating the original data table with the instruction information identified in customer feedback documents.

[0003] However, existing technologies mostly focus on one-way data processing: exporting data from Excel to scripts or reports, or manually extracting information from text to update tables. These solutions suffer from several key technical bottlenecks: Text backfilling is difficult: Key information in unstructured text (such as changed content, target fields, and primary keys) is difficult to parse, locate, and automatically write into the target Excel cells, requiring a lot of manual intervention; Poor data quality affects conversion accuracy: Original Excel data often has problems such as disordered formatting, missing values, and inconsistencies, which seriously affect the accuracy of batch conversion. The bidirectional transformation process lacks state tracking: existing tools lack the ability to synchronously use the context mapping (such as column → field relationship) generated by the forward transformation for reverse backfilling, resulting in inconsistencies or mismatches in the transformation; Furthermore, in scenarios such as microservice system log analysis and small business unit processing where "only 1 to 3 lines of data are processed each time," traditional tools have high initialization costs, slow response times, and unreasonable resource consumption, which seriously affects their usability.

[0004] Especially in log-based work order backfilling and agile operation and maintenance environments, accurately locating the target field to be changed from the text and synchronizing it to the corresponding position in the Excel spreadsheet has become one of the technical challenges in current data processing automation. Summary of the Invention

[0005] The purpose of this invention is to provide a method for bidirectional conversion between tabular data and text content based on artificial intelligence, so as to overcome the shortcomings of the prior art.

[0006] To achieve the above objectives, the present invention provides the following technical solution: a method for bidirectional conversion between tabular data and text content based on artificial intelligence, comprising: Receives the user-configured first path parameter, second path parameter, primary key field, and target column configuration; Read the target Excel spreadsheet according to the first path parameters, extract data from one or more specified target columns starting from the starting row, and organize the valid data in each row into a structured object according to the correspondence between field names and field values ​​to construct a structured raw data set D1; Perform data cleaning operations on the original structured data set D1 to obtain the cleaned data set D2; Receive and parse the template statement set M, identify and segment each template statement according to the preset placeholder format, and establish a template field index relationship table; based on the template field index relationship table, replace the corresponding placeholder in the template statement with the field value of each structured record in the data set D2 in the order of field index, generate the target text content T, and output it to the text file corresponding to the second path parameter; Read the text file corresponding to the second path parameter, perform standardized preprocessing on each sentence in the target text content T, and call the natural language processing model to identify the target field, target value and target identifier; perform entity alignment on the identification result, and according to the preset field mapping rules, and based on the semantic position distance or semantic connector, combine the entities in the same sentence into structured triples to construct a triple set E; For each record in the triple set E, the target identifier is extracted as the primary key matching value, and the corresponding data row is matched in the target Excel table according to the primary key field configured by the user; the field name information in the first row of the target Excel table is read, and a mapping relationship between the field name and the Excel column index is established; the target cell position is located according to the matched row number and the column index corresponding to the target field, and the target cell position is bound to the corresponding target value to build an update mapping relationship M'; Based on the updated mapping relationship M', the target values ​​in the triplet set E are filled into the corresponding target cells in the target Excel spreadsheet, generating the updated Excel spreadsheet file.

[0007] Preferably, the first path parameter is an Excel file path, the second path parameter is a text file path, and the primary key field is used to uniquely identify the data row to be matched in the Excel table.

[0008] Preferably, the data cleaning operation includes format standardization, missing value completion, and error correction.

[0009] Preferably, based on the user-provided template statement set M, template replacement is performed on the cleaned data set D2, including: Receive and parse the template statement set M, identify and segment each template statement according to the preset placeholder format, and establish a template field index relationship table; Traverse the cleaned data set D2, and replace the corresponding placeholders in the template statement with the field values ​​in each structured record in the order of field index relationship to generate intermediate replacement results; Perform format validity checks and special character escaping on the intermediate replacement results; Each replaced text statement is appended to the target text file pointed to by the second path parameter, thus completing the construction and output of the target text content T.

[0010] Preferably, the text file corresponding to the second path parameter is read, and a natural language processing model is called to perform entity recognition on the text content T, including: Read the text file specified by the second path parameter line by line, extract each sentence of the target text content T, and convert it into a standard text input format that can be recognized by the natural language processing model; A semantic tag dictionary is constructed based on the named entity recognition model, and a pre-trained sequence labeling model is loaded to perform labeling processing on each text statement, identifying the corresponding target field, target value and target identifier; Perform entity alignment on the annotation results, and form structured triples by combining multiple groups of entities appearing in the same sentence according to the preset field mapping rules, and construct a triple set E.

[0011] Preferably, locating the target cell position in the target Excel spreadsheet and constructing an update mapping relationship M' includes: Extract the target identifier of each record in the triple set E, and match the cell contents of the corresponding column of the primary key field in the target Excel table row by row according to the primary key field configured by the user; When a match is successful, the matching row number and the field name of the target field in the corresponding triple are recorded to form a mapping pair between field name and Excel column number; Locate the target cell in an Excel spreadsheet that needs to be updated based on the matching row number and field column number; Establish a binding relationship between each target cell position and its corresponding target value, and construct an update mapping relationship M'.

[0012] Preferably, the location of the target cell to be updated in an Excel spreadsheet is determined by jointly locating the matching row number and field column number, including: After obtaining the row number where the primary key field is successfully matched, the column number of the target field in the target Excel table is determined based on the mapping relationship between the field name and the column index. The row number and column number are combined with coordinates to form a row and column coordinate pair that uniquely identifies the location of an Excel cell; Perform validity checks on row and column coordinates to determine whether they are within the valid data area of ​​the target Excel spreadsheet, excluding coordinates corresponding to header rows and blank columns; After the verification is passed, the row and column coordinate pairs are registered as updatable cell identifiers and used as the sole positioning basis for subsequent operations to write target values, thereby completing the determination of the target cell position.

[0013] Preferably, based on the updated mapping relationship M', filling the target values ​​in the triplet set E into the corresponding target field positions in the target Excel table includes: Iterate through each key-value pair in the updated mapping relationship M′ and obtain the corresponding row and column coordinate pairs and the target value; Use the spreadsheet read / write component to locate the target cell in the target Excel spreadsheet by row and column coordinates; Write the target value to the located cell and perform character encoding conversion and data type adaptation on the written content; After all target values ​​have been written, a save operation is performed, writing the modified table data into an updated Excel spreadsheet file. The original path can be overwritten or saved as an output mode, depending on the user configuration options.

[0014] The technical effects and advantages provided by the present invention in the above technical solution are as follows: 1. The AI-based bidirectional conversion method for tabular data and text content provided in this invention achieves accurate mapping and reverse filling of unstructured text semantics to structured Excel cells by constructing structured triples and combining natural language processing models and field mapping logic. This method not only solves the technical bottleneck of traditional batch processing tools not being able to support reverse text writing into tables, but also significantly improves data positioning accuracy and batch processing efficiency by updating the mapping relationship M′ and cell coordinate positioning mechanism, avoiding common problems such as manual comparison and data misfilling.

[0015] 2. This invention possesses advantages such as bidirectional semantic driving, automatic field matching, dynamic data type adaptation, and visual configuration operations, demonstrating higher intelligence and flexibility when handling structured and unstructured data interactions. Especially in scenarios such as batch work order backfilling, log data correction, and automatic customer information updates, this invention can significantly reduce manual intervention costs, improve data quality consistency and system automation levels, and has good engineering promotion value. Attached Figure Description

[0016] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this invention. For those skilled in the art, other drawings can be obtained based on these drawings.

[0017] Figure 1 This is a flowchart of the method of the present invention. Detailed Implementation

[0018] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of 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, 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.

[0019] For examples, please refer to Figure 1 As shown in this embodiment, the method for bidirectional conversion between tabular data and text content based on artificial intelligence includes: In this invention, receiving the user-configured first path parameter, second path parameter, and primary key field can be accomplished through collaboration between the front-end interactive interface and the back-end parameter receiving module. The implementation process is as follows: After users access the bidirectional conversion function module through the system's front-end interface, they first need to complete the basic configuration of the path and primary key fields: The first path parameter input box is used to receive the Excel file path specified by the user. Users can directly enter the local path or select a specific Excel file or a folder path containing multiple Excel files through the file selector component. After receiving the path, the system backend performs a validity check, including but not limited to: whether the path exists, whether it contains an Excel file with a valid format (supports .xlsx and .xls formats), and whether it has read permissions.

[0020] The second path parameter input box is used to specify the output path of the target text file. In "Excel → Text" mode, it indicates the save address of the generated text content; in "Text → Excel" mode, it indicates the path of the text input file to be parsed. The configuration is also completed through the path selector or by manual input, and the system performs format and permission verification on its validity.

[0021] Primary Key Field Input Box: This box receives the user-specified primary key field name. This field should be a data column in the target Excel spreadsheet that uniquely identifies each record, such as "Username," "Mobile Number," or "Employee ID." The system uses this field as a key index for subsequent reverse fill operations. If the specified primary key field exists in multiple Excel files, the system will automatically execute a file-by-file search strategy during matching to ensure uniqueness.

[0022] In this invention, after the user completes the configuration of the path parameters and the primary key field, the system performs a batch read operation on multiple Excel files in the target Excel file or folder according to the received first path parameters, and constructs a structured raw data set D1.

[0023] After the user completes the configuration of path parameters, primary key fields, and target columns, the system reads one or more Excel files from the target Excel file or folder based on the first path parameter. Starting from the user-specified starting row, it extracts data from one or more specified target columns. It reads the contents of corresponding cells row by row according to the column index, and performs null value and format preprocessing on the read results; rows with empty or missing primary key fields are skipped. For each row of valid data, it organizes it into a structured object according to the correspondence between field names and field values, and integrates them to form a structured raw data set D1, which serves as the input basis for subsequent data cleaning, template replacement, and reverse backfilling. The specific steps are as follows: The backend system uses Excel parsing components such as Apache POI or EasyExcel to read all Excel files in the specified directory one by one, according to the first path parameter. For each Excel file, the following sub-steps are executed: Worksheet selection: If the user specifies the name or index of the worksheet to be processed during the parameter configuration stage, the system will prioritize extracting the corresponding worksheet according to the setting; if not specified, the system will select the first worksheet for processing by default.

[0024] Start row validation and skip: Users can configure the starting row for data reading (e.g., row 2, to skip the header). The system automatically skips the content before the starting row and extracts valid data only from the specified row.

[0025] The user has configured the target column number (e.g., columns 2, 4, and 6) in the system interface. Based on this column configuration, the system reads the data from the corresponding column in the worksheet row by row. The extraction methods include: Precisely locate the corresponding cell in each row using column index; Perform preprocessing on the data in each cell, including removing blanks and formatting, such as removing spaces and standardizing date formats. If the primary key field of a certain row is empty or missing, the system automatically skips this row to avoid unidentifiable items in the structured result.

[0026] Each piece of valid data read in each row is organized into a structured object and stored in the form of key-value pairs, for example: {"primary key":"User A","Field 1":"Parameter value 1","Field 2":"Parameter value 2","Field 3":"Parameter value 3"}; all row data are integrated into a structured raw data set D1.

[0027] Through this step, unstructured Excel spreadsheet data is effectively standardized into a structured object set, which provides high-quality input for the downstream data cleaning and text generation module, and ensures data consistency and processing accuracy of the entire conversion process.

[0028] Perform data cleaning operations on the structured raw data set D1 to obtain a cleaned data set D2.

[0029] In the present invention, after the construction of the structured raw data set D1 is completed, the data cleaning module is automatically triggered to perform a series of predefined cleaning operations on the data in D1, including format standardization, missing value completion, error correction, etc., so as to generate the cleaned data set D2 for subsequent data conversion and text generation operations. The specific implementation process is as follows: In the front-end parameter configuration interface, the user can enable this step by checking the "Enable AI Data Cleaning and Completion" option; if not checked, the cleaning process is skipped, and the system directly uses D1 as the subsequent input by default. The cleaning operation is executed by the back-end AI data cleaning service, which is implemented based on a combination of a predefined rule engine and a lightweight machine learning model, and has the following functions: Unify the format of each field in the structured data set D1, including but not limited to: date format conversion: convert non-standard date formats such as "20xx / 12 / 01", "December 1st", "20xx.12.1" uniformly into "20xx-12-01"; phone number cleaning: remove invalid characters such as spaces, hyphens, parentheses, for example, "138-xxxx-1111" → "138xxxx1111"; email standardization: convert all to lowercase, remove spaces, and fix common spelling errors.

[0030] For fields with null values or "N / A" in the structured data, the system attempts to complete them in the following ways: filling based on the statistical law of the same column: for example, most of the "Gender" column is "Male", and the missing items are filled with "Male" by default; associated filling based on related fields: for example, the "Position" field is missing, but the "Department" is "Finance Department", then it is automatically filled with "Accountant" according to the common positions in the Finance Department; model prediction completion: introduce a lightweight classification model to predict the most probable value of the missing field according to the sample features.

[0031] Automatically detect and repair potential errors in structured data, including but not limited to: spelling correction: detect common spelling errors, such as “Devlopment”→“Development”; illegal character cleanup: remove HTML tags, special symbols or garbled characters from data fields; value range validation: if the “age” field contains unreasonable values ​​such as “320” or “-5”, replace them with the system-defined default value or indicate an error.

[0032] After data cleaning is complete, a summary of the cleaning process is displayed in the status bar of the front-end interface, such as: Data cleaning complete: 3,000 records were processed, 172 format errors were corrected, 58 missing values ​​were filled, and 27 spelling errors were corrected. Simultaneously, the cleaned structured data is named D2 as a set and cached in memory or a temporary file system for direct input in subsequent template replacement (Excel → Text) or entity comparison (Text → Excel).

[0033] Based on the template statement set M provided by the user, the cleaned data set D2 is template replaced to generate the target text content T, which is then output to the text file corresponding to the second path parameter.

[0034] In this invention, after the user inputs the template statement set M, the system first parses each template statement as an independent template, identifies the preset placeholders within it, and establishes a template field index relationship table to determine the replacement order between each placeholder in the template and the field values ​​in the structured record. When the number of placeholders is inconsistent with the number of fields in the corresponding structured record, the corresponding template is marked as invalid. Subsequently, the system traverses the cleaned data set D2, and for each structured record, extracts the corresponding field values ​​sequentially according to the order defined in the template field index relationship table and replaces the corresponding placeholders in the template to generate the text statement corresponding to the structured record. After the replacement is completed, the text statement undergoes format validity verification and special character processing, and then the processed text statement is output to the text file corresponding to the second path parameter to form the target text content T. This process specifically includes the following steps: First, each template in the user-input template statement set M is processed as an independent statement. Each template statement should contain several predefined placeholders to identify the field values ​​to be filled later. Placeholders are defined using curly braces and ordinal numbers, such as {0}, {1}, {2}, etc., indicating the field filling order.

[0035] This step will perform the following operations: split the template set M by semicolon delimiter to obtain multiple template statements; traverse and scan each template statement to identify all placeholders; construct a template field index relationship table by associating the position of each placeholder with its index number, in the format: template number → {placeholder sequence number → field sequence number}; if the number of placeholders in the template statement is inconsistent with the number of fields in the cleaned data set D2, the template will be marked as invalid and the user will be prompted to correct it on the front end.

[0036] This process ensures that the mapping rules between template statements and field values ​​are clear and the replacement order is fixed, laying a structural foundation for the subsequent automatic replacement process.

[0037] After the template index relationship is constructed, the system iterates through the cleaned dataset D2 record by record. For each structured record in D2, the corresponding field values ​​are extracted according to the placeholder order defined in the index relationship table, and a one-to-one replacement operation is performed. Extract the set of field values ​​from the current structured record and construct a value list according to the field order; Replace the placeholder {0} in the template statement with the first element in the field value list, {1} with the second element, and so on, until all placeholders have been replaced. The replaced string constitutes the intermediate replacement result corresponding to this data, that is, a complete text statement, but it has not yet been written to the target file.

[0038] The replacement process is based on fixed-position mapping and does not involve fuzzy matching or semantic analysis, ensuring stable statement structure and clear execution logic.

[0039] To ensure that the generated intermediate replacement results can be correctly parsed and executed by subsequent systems or tools, this step performs format validity checks and special character escaping on the replacement results. The specific operations are as follows: Validation includes: determining whether the statement begins with a valid keyword, such as "INSERT", "UPDATE", or "DELETE"; checking for placeholders that have not been completely replaced (such as those still containing "{" or "}" characters); and verifying the presence of consecutive separators, missing values, or unclosed parentheses in the syntax structure. Special character handling includes: escaping single quotes ' in field values ​​to two consecutive single quotes '' to prevent string truncation; replacing newline and tab characters with space characters; removing illegal characters in field values ​​that may cause syntax errors, such as semicolons;, backslashes \, double quotes ", etc., or replacing them with Unicode encoding.

[0040] Finally, the valid text statements generated through the above processing are used as the final conversion results and appended sequentially to the target text file specified by the second path parameter configured by the user. The writing process adopts a line-by-line appending method, writing one line of text content after each data processing is completed. The operation flow is as follows: Open the target text file using UTF-8 encoding; append each intermediate replacement result as a newline to the end of the file; immediately flush the file buffer after writing to ensure successful writing; after all structured records have been processed, close the file write stream, and the target text content T is complete. If the output file path configured by the user already exists, the system can choose to clear the original content before writing or append it after the existing content. The writing mode can be preset in the front-end interface.

[0041] Read the text file corresponding to the second path parameter, call the natural language processing model to perform entity recognition on the text content T, and extract the set of triples E including the target field, target value, and target identifier.

[0042] In this invention, the system reads the text file corresponding to the second path parameter line by line, performs standardized preprocessing on each text sentence to convert it into a standard input format recognizable by the natural language processing model; subsequently, it uses a named entity recognition model to identify the target field, target value, and target identifier in the text. After named entity recognition is completed, the system does not stop at recognizing a single entity, but further performs entity alignment operations on multiple entities output by the model. According to preset field mapping rules and based on semantic position distance or semantic connectors, related entities in the same sentence are combined to form structured "target field-target value-target identifier" triples, which are then stored in the triple set E. The specific processing steps are as follows: This step first opens the target text file according to the text file path specified in the second path parameter, and extracts the text content by reading it line by line. To ensure that the subsequent processing model can recognize the text structure, each text sentence read must undergo standardized preprocessing operations, including: Remove leading spaces, newlines, tabs, and extra punctuation. Add uniform separators (such as ":" or "=") between keywords in the text and data fields; For statements with non-natural language structures (such as SQL fragments and command instructions), perform word segmentation and conversion processing to break down each group of keywords and parameters into independent semantic units.

[0043] The standardized text statements are converted into fixed-length string arrays or word sequences, which serve as input data for subsequent named entity recognition models.

[0044] To identify and classify entities in text sentences, this implementation uses a sequence labeling algorithm based on a bidirectional long short-term memory neural network and a conditional random field combination model (BiLSTM-CRF). The model training process includes the following steps: Corpus preparation: Select real text samples from multiple business scenarios and label them with three types of tags: "target field", "target value" and "target identifier". Tag system construction: Define a set of BIO (Begin-Inside-Outside) tagging rules for each semantic category, such as "B-FIELD" representing the starting word of the field name and "I-VALUE" representing the words inside the field value; Model training: The BiLSTM-CRF model is trained based on labeled corpora. The objective function is to maximize the probability of labeled sequences. The training convergence condition is that the loss function decreases by less than 0.001 on the validation set. Model deployment: Deploy the trained model as a locally callable service, which takes a standardized text sequence as input and outputs a label sequence.

[0045] In practical use, the model receives a standardized single text statement and outputs the corresponding label sequence. The label position is the interval where the corresponding entity is located. Based on the label combination relationship, the system can extract several named entities, for example: target field: "phone number" target value: "138xxxx1111"; target identifier: "Zhang San".

[0046] After named entity recognition is completed, this step performs an alignment operation on the multiple entities output by the model to construct a set E of semantically complete triples. The specific steps are as follows: Extract all entities belonging to the categories "target field", "target value", and "target identifier" in the order they appear in the text statement; By referencing the preset field mapping rules, the "field name → data column" is logically bound to ensure that the field names are consistent with the column names in the original Excel data; Within the same sentence, multiple entities are combined based on semantic positional distance or explicit semantic conjunctions (such as "will", "change to", "belong to"). For statements with multiple fields or multiple values, the sliding window algorithm is used to sequentially combine them into multiple non-overlapping triplet records; Each complete combination of "target field-target value-target identifier" is constructed into a structured triple object and stored in set E. For example, after recognizing and combining the text statement "Change Zhang San's mobile phone number to 138xxxx1111", the triple is represented as: {"target field":"mobile phone number","target value":"138xxxx1111","target identifier":"Zhang San"}; each record in triple set E serves as the input basis for subsequent Excel positioning and backfilling operations.

[0047] Through the above steps, the present invention can automatically extract entity content with data operability from natural language expressions with high accuracy, and maintain the mapping relationship with the original structured data, providing semantic support for bidirectional conversion, significantly reducing the cost of manual intervention, and improving the automation level of text understanding and backfilling.

[0048] Based on the primary key field and the target identifier in the triple set E, locate the target cell position in the target Excel table and construct an update mapping relationship M'.

[0049] In this invention, for each record in the triple set E, the system first extracts the target identifier as the primary key matching value, and matches the cell content of the column containing the primary key field in the target Excel spreadsheet row by row according to the user-configured primary key field to determine the row number of the corresponding data row. After the row number is successfully located, the system reads the field name information in the first row of the target Excel spreadsheet, establishes a mapping relationship between the logical field name and the physical column index, and determines the corresponding column index according to the target field in the current triple. Subsequently, the matching row number and the column index corresponding to the target field are combined to form the row and column coordinates of the target cell. After passing the validity check, the target cell position is bound to the corresponding target value, and an update mapping relationship M' is constructed for subsequent write operations to call.

[0050] For each record in the triple set E, the system first extracts the "target identifier" item as the primary key matching value. Then, the system scans the column in the target Excel spreadsheet that the user has configured as the "primary key field" in a row-by-row manner, reading the value of the cell in that column row by row.

[0051] When the value of the primary key field in a row is exactly equal to the target identifier text in the triple (case-sensitive, ignoring leading and trailing spaces), the row number of that row is recorded as the matching result. This row number will be used as the row dimension coordinate for locating the target cell corresponding to the current triple.

[0052] If no matching primary key field value is found, the triplet record is marked as "not located" and will not participate in subsequent cell updates.

[0053] After successfully locating the primary key field row number, the system reads the field name information from the first row of the Excel spreadsheet to establish a mapping table between logical field names and physical column indexes. For example, if the first row contains "Name", "Department", and "Mobile Number", the mapping relationship would be: "Name" → Column 1; "Department" → Column 2; "Mobile Number" → Column 3; The system then extracts the "target field" content from the current triplet record and searches for its corresponding column number in the aforementioned mapping table as the target field column number. If the target field name does not match in the table header, the triplet record is marked as "field not recognized".

[0054] Once the matching row number and the corresponding column number of the target field are obtained, the system combines them into a row-column coordinate pair. For example, the 5th row and the 3rd column are combined to form the coordinates (5, 3), which are used to uniquely identify a cell in the target Excel spreadsheet.

[0055] To ensure the accuracy and security of the update operation, the system needs to perform the following validity checks on each coordinate pair: Check if the row number is greater than the first row number (usually the first row) to avoid overwriting the table header; Check if the column number is a valid index (greater than 0 and less than or equal to the maximum number of columns); Verify that the current cell position exists within the data range to prevent out-of-bounds writing or writing to a blank table.

[0056] If the above three conditions are met, the coordinate pair is considered valid and proceeds to the next step; if any condition is not met, the system marks the triplet as "location failed".

[0057] For triplet records that have successfully completed row and column coordinate positioning and passed verification, the system uses the current coordinate pair as the key and the "target value" in the triplet as the value to construct a key-value pair update mapping relationship. For example: {"(5,3)":"138xxxx1111","(6,2)":"Finance Department"}; the above key-value pair is the update mapping relationship M′, which is used in the subsequent Excel cell value writing steps. Each item in M′ clearly identifies the cell position to be written and its new value to be updated, ensuring the accuracy and automation of the update process.

[0058] The process of constructing the update mapping relationship M′ does not rely on a full table scan, but is constructed only based on the matching results of the primary key field values. Therefore, it is efficient and has good scalability, and is suitable for batch reverse update scenarios involving multiple files and multiple worksheets.

[0059] Based on the updated mapping relationship M', the target values ​​in the triple set E are filled into the corresponding target field positions in the target Excel spreadsheet, generating the updated Excel spreadsheet file.

[0060] In this invention, the pre-constructed update mapping relationship M′ is used to guide the precise writing of the target value extracted from natural language into a specified cell position in the target Excel spreadsheet. This process ensures data type matching, consistent formatting, and that the written file can be correctly opened and used. Specifically, it includes the following four steps: The updated mapping relationship M′ is a set of key-value pairs consisting of cell positions and target values. The system processes each record in M′ using an ordered traversal: The key is a pair of row and column coordinates, in the format (row number, column number), which represents the position of the cell in the target Excel spreadsheet that needs to be updated; The value is the "target value" identified in the triple set E, which is the actual content to be written.

[0061] The system reads these key-value pairs one by one, parses out the specific row number, column number and target value, and provides input for subsequent cell positioning and data writing.

[0062] This embodiment uses the Apache POI component as the Excel file manipulation tool. During each iteration, the system calls the table read / write interface to perform the following location steps: Use the getSheetAt() method of the table object to retrieve the worksheet specified by the user; The target row object is obtained using the getRow(row number) method; Perform the getCell(column number) operation on the row object to obtain the target cell object; If the cell object does not exist (i.e., an empty cell), the createCell(column number) method is used to create the cell.

[0063] The above positioning operation precisely points to the target cell in physical coordinates, ensuring that the write operation does not deviate from the field.

[0064] Perform necessary pre-writing processing on the extracted target values ​​to ensure that their content is consistent with the original field types and formats of the Excel file. This includes the following operations: Character encoding conversion: Convert the target value to UTF-8 encoding to avoid garbled characters after writing Chinese characters or special characters; Data type adaptation: If the target field's data type in the original table is "pure number," the system will attempt to parse the target value into a floating-point number before writing it. If the original field type is "date format," the target value must conform to the "year-month-day" format and be converted into a date object by the date parser before writing. If the field is a boolean type or an enumeration value (such as "yes / no"), the target value will be converted into an internal enumeration key value through a mapping table before writing. The write operation is completed by calling the cell object's setCellValue() method, with the target value processed as described above as the parameter.

[0065] Once all target values ​​in the mapping relationships have been written, the system performs a file save operation, writing the table object in memory as an updated Excel spreadsheet file. The save strategy has two modes, which are selected by the user during the front-end configuration phase: Original path overwrite mode: The system directly writes the modified results to the original Excel file path, calling the file stream to overwrite, which is suitable for scenarios that directly update the original file; Save As mode: The system creates a new filename with the "_updated" suffix in the original path and writes the updated content to the new file, keeping the original file unchanged. This is suitable for audit and archiving scenarios.

[0066] The save operation is completed using the FileOutputStream interface provided by Apache POI, ensuring that the file can be opened correctly by regular office software after writing, and that the written content retains its format, encoding, and style.

[0067] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any changes or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application.

Claims

1. A method for bidirectional conversion between tabular data and text content based on artificial intelligence, characterized by: include: Receives the user-configured first path parameter, second path parameter, primary key field, and target column configuration; Read the target Excel spreadsheet according to the first path parameters, extract data from one or more specified target columns starting from the starting row, and organize the valid data in each row into a structured object according to the correspondence between field names and field values ​​to construct a structured raw data set D1; Perform data cleaning operations on the original structured data set D1 to obtain the cleaned data set D2; Receive and parse the template statement set M, identify and segment each template statement according to the preset placeholder format, and establish a template field index relationship table; Based on the template field index relationship table, the field values ​​of each structured record in the data set D2 are replaced with the corresponding placeholders in the template statement in the order of field index to generate the target text content T, and output to the text file corresponding to the second path parameter; Read the text file corresponding to the second path parameter, perform standardized preprocessing on each sentence in the target text content T, and call the natural language processing model to identify the target field, target value and target identifier; perform entity alignment on the identification result, and according to the preset field mapping rules, and based on the semantic position distance or semantic connector, combine the entities in the same sentence into structured triples to construct the triple set E; For each record in the triple set E, the target identifier is extracted as the primary key matching value, and the corresponding data row is matched in the target Excel table according to the primary key field configured by the user. Read the field name information in the first row of the target Excel spreadsheet and establish a mapping relationship between the field name and the Excel column index; locate the target cell position based on the matched row number and the column index corresponding to the target field, and establish a binding relationship between the target cell position and the corresponding target value to construct an update mapping relationship M'; Based on the updated mapping relationship M', the target values ​​in the triplet set E are filled into the corresponding target cells in the target Excel spreadsheet, generating the updated Excel spreadsheet file.

2. The method for bidirectional conversion of tabular data and text content based on artificial intelligence according to claim 1, characterized in that: The first path parameter is the path to the Excel file, the second path parameter is the path to the text file, and the primary key field is used to uniquely identify the data row to be matched in the Excel table.

3. The method for bidirectional conversion of tabular data and text content based on artificial intelligence according to claim 1, characterized in that: The data cleaning operations include format standardization, missing value completion, and error correction.

4. The method for bidirectional conversion of tabular data and text content based on artificial intelligence according to claim 1, characterized in that: Based on the user-provided template statement set M, perform template replacement on the cleaned data set D2, including: Receive and parse the template statement set M, identify and segment each template statement according to the preset placeholder format, and establish a template field index relationship table; Traverse the cleaned data set D2, and replace the corresponding placeholders in the template statement with the field values ​​in each structured record in the order of field index relationship to generate intermediate replacement results; Perform format validity checks and special character escaping on the intermediate replacement results; Each replaced text statement is appended to the target text file pointed to by the second path parameter, thus completing the construction and output of the target text content T.

5. The method for bidirectional conversion of tabular data and text content based on artificial intelligence according to claim 1, characterized in that: Read the text file corresponding to the second path parameter, and call the natural language processing model to perform entity recognition on the text content T, including: Read the text file specified by the second path parameter line by line, extract each sentence of the target text content T, and convert it into a standard text input format that can be recognized by the natural language processing model; A semantic tag dictionary is constructed based on the named entity recognition model, and a pre-trained sequence labeling model is loaded to perform labeling processing on each text statement, identifying the corresponding target field, target value and target identifier; Perform entity alignment on the annotation results, and form structured triples by combining multiple groups of entities appearing in the same sentence according to the preset field mapping rules, and construct a triple set E.

6. The method for bidirectional conversion of tabular data and text content based on artificial intelligence according to claim 1, characterized in that: Locate the target cell position in the target Excel spreadsheet and construct an update mapping relationship M', including: Extract the target identifier of each record in the triple set E, and match the cell contents of the corresponding column of the primary key field in the target Excel table row by row according to the primary key field configured by the user; When a match is successful, the matching row number and the field name of the target field in the corresponding triple are recorded to form a mapping pair between field name and Excel column number; Locate the target cell in an Excel spreadsheet that needs to be updated based on the matching row number and field column number; Establish a binding relationship between each target cell position and its corresponding target value, and construct an update mapping relationship M'.

7. The method for bidirectional conversion of tabular data and text content based on artificial intelligence according to claim 6, characterized in that: Locating the target cell position in an Excel spreadsheet that needs to be updated based on the matched row number and field column number, including: After obtaining the row number where the primary key field is successfully matched, the column number of the target field in the target Excel table is determined based on the mapping relationship between the field name and the column index. The row number and column number are combined with coordinates to form a row and column coordinate pair that uniquely identifies the location of an Excel cell; Perform validity checks on row and column coordinates to determine whether they are within the valid data area of ​​the target Excel spreadsheet, excluding coordinates corresponding to header rows and blank columns; After the verification is passed, the row and column coordinate pairs are registered as updatable cell identifiers and used as the sole positioning basis for subsequent operations to write target values, thereby completing the determination of the target cell position.

8. The method for bidirectional conversion of tabular data and text content based on artificial intelligence according to claim 1, characterized in that: Based on the updated mapping relationship M', the target values ​​in the triplet set E are filled into the corresponding target field positions in the target Excel table, including: Iterate through each key-value pair in the updated mapping relationship M′ and obtain the corresponding row and column coordinate pairs and the target value; Use the spreadsheet read / write component to locate the target cell in the target Excel spreadsheet by row and column coordinates; Write the target value to the located cell and perform character encoding conversion and data type adaptation on the written content; After all target values ​​have been written, a save operation is performed, writing the modified table data into an updated Excel spreadsheet file. The original path can be overwritten or saved as an output mode, depending on the user configuration options.

Images