An AI-driven-based excel file intelligent analysis method and system

Abstract

The application relates to an AI driving-based Excel file intelligent analysis method and system, which comprises the following steps: loading an Excel file and obtaining a worksheet, and constructing a structured prompt word; inputting table data and the prompt word into a large language model for semantic analysis, obtaining table header information, and establishing a field and column index mapping relationship; extracting data based on the mapping relationship; integrating data and outputting a structured result. The application realizes intelligent semantic analysis and field mapping of the table header by combining the large language model with the structured prompt word, can flexibly adapt to various table header formats, significantly reduces the dependence on manual programming of analysis scripts, improves the analysis efficiency of large files through parallel processing of multiple worksheets, and enhances the accuracy and automation degree of complex table analysis by using the man-machine interaction correction and the knowledge base adaptive learning mechanism, and has the advantages of high intelligence, high processing efficiency and strong adaptability.

Description

Technical Field

[0001] This invention relates to the fields of data processing and artificial intelligence technology, and in particular to an AI-driven intelligent parsing method and system for Excel files. Background Technology

[0002] With the rapid development of information technology and the deepening of digital applications in various industries, massive amounts of business data are centrally stored in Excel spreadsheets. Especially in core data processing scenarios such as enterprise management, financial analysis, and data statistics, the data volume and structural complexity of Excel files continue to rise. Efficiently parsing such files has become an important prerequisite for data flow and business applications.

[0003] Most current mainstream traditional Excel file parsing methods rely on pre-defined parsing rules to perform data processing, or require technicians to manually write custom parsing scripts for different file formats to read, identify, and extract table data row by row and column by column according to preset fixed logic, without intelligent adaptation and dynamic optimization capabilities.

[0004] Traditional Excel file parsing methods typically rely on fixed rules or manually written scripts, which have the following problems: 1. Inconsistent header information: Different Excel files may have significantly different header formats and naming rules, making it difficult for traditional methods to adapt flexibly. 2. Low parsing efficiency: For large Excel files containing multiple sheets, parsing row by row and column by column is time-consuming, impacting data processing efficiency. 3. Insufficient automation: When extracting specific fields or reorganizing data, manual intervention is often required, making it difficult to meet the need for rapid response. 4. Lack of dynamic adjustment capabilities: When field data is missing or header information is mismatched during parsing, traditional methods are difficult to adjust and optimize quickly. Summary of the Invention

[0005] This invention provides an AI-driven intelligent parsing method and system for Excel files, which solves the technical problems in the prior art such as difficulty in parsing inconsistent Excel header formats, low efficiency in processing multiple sheets, and insufficient accuracy and adaptability in data extraction.

[0006] In a first aspect of the present invention, an AI-driven intelligent parsing method for Excel files is provided, comprising: Load the target Excel file and extract its worksheets, then construct structured prompts; The table data of the worksheet and the structured prompt words are submitted to the large language model for semantic parsing to obtain the parsing results containing the table header information, and a mapping relationship between field names and column indexes is established based on the parsing results; Based on the mapping relationship, extract the corresponding field data from the data area of ​​the worksheet; Integrate the extracted data and output structured data results.

[0007] In a second aspect of the present invention, an AI-driven intelligent Excel file parsing system is provided, comprising: The file loading module is used to load the target Excel file and obtain the worksheets within it, and to construct structured prompt words; The header parsing module is used to submit the table data of the worksheet and the structured prompt words to the large language model for semantic parsing, obtain the parsing result containing the header information, and establish the mapping relationship between field names and column indexes based on the parsing result; The data extraction and processing module is used to extract corresponding field data from the data area of ​​the worksheet based on the mapping relationship; The data integration and output module is used to integrate the extracted data and output structured data results.

[0008] Preferably, the system further includes: The multi-worksheet parallel processing module is used to perform parallel processing of the entire parsing task of each worksheet through a dynamic thread pool when the target Excel file contains multiple worksheets. The human-computer interaction correction module is used to receive mapping correction information input by the user and re-execute the data extraction task when the table header parsing results show that field data has not been extracted or the table header information is incorrectly identified. The knowledge base update module stores the user-corrected header mapping relationship and prioritizes the use of the stored historical mapping relationship for adaptive parsing in subsequent parsing tasks.

[0009] In a third aspect of the present invention, an electronic device is provided, including a memory, a processor, and a computer program stored in the memory and executable by the processor, wherein the processor executes the computer program to implement the steps of the method as described in the first aspect of the present invention.

[0010] The AI-driven intelligent parsing method and system for Excel files provided by this invention have the following advantages compared with existing technologies: 1) High level of intelligence: It adopts a large language model combined with structured prompt words for semantic parsing, which can intelligently identify various format table headers and establish field mapping, significantly improving the level of parsing intelligence. It can adapt to complex and ever-changing table structures and reduce the dependence on manual rules and scripts.

[0011] 2) High processing efficiency: By using a dynamic thread pool to perform parallel parsing of multiple worksheets, the processing speed of large Excel files is greatly improved, the data processing time is effectively shortened, the overall parsing efficiency is improved, and the business needs of rapid processing of large-scale data are met.

[0012] 3) Strong dynamic adjustment capability: It has a parsing result verification and human-computer interaction correction mechanism, which can quickly locate and correct anomalies in header recognition and field extraction, ensuring accurate and reliable data extraction and improving the stability and usability of parsing results.

[0013] 4) Strong adaptability: By storing the corrected table header mapping relationship in the knowledge base, historical records are reused first in subsequent parsing to achieve adaptive parsing, continuously reduce manual intervention, and improve the system's automation and self-adaptability.

[0014] 5) Strong compatibility: Supports merging cells, data cleaning, structure reconstruction and traceability information addition. It is compatible with various complex table structures and outputs standardized data. The system has stronger applicability and scalability, and is more user-friendly and convenient to use. Attached Figure Description

[0015] Figure 1 A flowchart of the AI-driven intelligent parsing method for Excel files provided by this invention; Figure 2 The overall flowchart of the AI-driven intelligent parsing method for Excel files provided by this invention; Figure 3 The structural block diagram of the AI-driven intelligent Excel file parsing system provided by this invention; Figure 4 This is a structural block diagram of the electronic device provided by the present invention. Detailed Implementation

[0016] 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.

[0017] The naming or numbering of steps in the embodiments of the present invention does not mean that the steps in the method flow must be executed in the time / logical order indicated by the naming or numbering. The execution order of the named or numbered process steps can be changed according to the technical purpose to be achieved, as long as the same or similar technical effect can be achieved.

[0018] Figure 1 This is a flowchart illustrating the AI-driven intelligent parsing method for Excel files provided by the present invention. (Refer to...) Figure 1 The method includes the following steps: S1, Load the target Excel file and obtain the worksheets within it, then construct structured prompts; The target Excel file refers to the spreadsheet file storing business data to be parsed. It is compatible with mainstream Excel file formats such as .xls and .xlsx, and may contain one or more independent worksheets. A worksheet is an independent tabular data storage unit within an Excel file, composed of a matrix of cells consisting of rows and columns. Each worksheet can be divided into a header area for identifying field meanings and a data area for storing detailed content. Structured prompts are text instructions written according to a standardized logical framework, used to explicitly guide the large language model to execute the specified table parsing task, providing clear task direction and output requirements.

[0019] This step completes the loading and parsing of the Excel file and the construction of parsing instructions, laying the foundation for subsequent intelligent parsing. Specifically, it first uses an Excel parser to open and read the target Excel file, performs file format validity checks, obtains the collection of all worksheet objects contained in the Excel workbook, parses the organizational relationships of each worksheet, and completes the basic content reading and initialization of all worksheets, preparing for subsequent table-by-table parsing processing.

[0020] After acquiring the worksheet, structured prompts are constructed based on the specific business scenario corresponding to the target Excel file, adapting to the input requirements of the large language model. These structured prompts, as the core input commands of the large language model, are crucial for achieving intelligent table header parsing, allowing the general natural language understanding capabilities of the large language model to focus on table structure recognition tasks within a specific domain.

[0021] S2, Submit the table data of the worksheet and the structured prompt words to the large language model for semantic parsing, obtain the parsing result containing the table header information, and establish the mapping relationship between field names and column indexes based on the parsing result.

[0022] This step leverages the semantic analysis capabilities of a large language model to achieve intelligent recognition of table header information and establish data location relationships.

[0023] Specifically, firstly, table sample data covering at least the header area is extracted from the initialized worksheet to ensure that the large language model can obtain sufficient contextual information to accurately determine the header position; then, the extracted table sample data is combined with the structured prompt words constructed in step S1 to form complete model input content, which is then submitted to the large language model (LLM).

[0024] After receiving input, the large language model performs deep semantic analysis on the tabular data based on structured prompts. By distinguishing the textual features of the rows and columns in the table, it identifies descriptive field names (i.e., table headers) and specific data instance values, achieving accurate identification of header rows or columns. After completing the semantic analysis, the large language model generates a parsing result containing table header information according to the output format specified by the structured prompts. This result at least specifies the direction of the table header (horizontal or vertical) and the corresponding row / column index. If there are multiple rows / columns of headers, all header positions will be recorded in a list format. The table header information consists of field names used to define the business meaning of data columns. These names can be distributed across specified rows or columns of the table and are the core identifiers defining the business attributes of the data.

[0025] After obtaining the parsing results returned by the large language model, the column positions of each field name in the table are determined based on the header direction and row / column index information in the parsing results. The field name text in the header area is read, and a one-to-one mapping relationship is established between each field name and the corresponding column index in the data area, clarifying the storage location of each business field data. For cases with multiple header rows, the header information is standardized to generate a unified and standardized header structure, ensuring that subsequent data extraction can accurately correspond to the correct column positions.

[0026] S3. Based on the mapping relationship, extract the corresponding field data from the data area of ​​the worksheet.

[0027] In this embodiment, based on the mapping relationship between field names and column indices established in the preceding steps, the corresponding field data is extracted from the data area of ​​the worksheet. Specifically, based on the established mapping relationship, the data area in the worksheet that stores business detail data is located. Using the column indices corresponding to each field in the mapping relationship as a basis, the cell contents of the corresponding columns are read row by row from the data area to complete the extraction operation of the original data corresponding to each business field. Here, the data area refers to the cell range outside the header area of ​​the worksheet used to store business detail data, and it is the core target area for field data extraction.

[0028] S4 integrates the extracted data and outputs structured data results.

[0029] Specifically, the data extracted from each field of the worksheet is integrated according to the relationship between field names and corresponding values. When the Excel file contains multiple worksheets, the data extracted from each worksheet is merged and uniformly assembled. During the integration process, the data is standardized and formatted, invalid and redundant content is removed, and a standardized dataset conforming to business specifications is formed, ultimately resulting in structured data output. The output format can be JSON (JavaScript Object Notation), CSV (Comma-Separated Values), or directly stored in a database to meet the data delivery format requirements of different business scenarios. The output structured data results maintain a clear hierarchical relationship and standardized field structure, ensuring data consistency and usability, and can be directly adapted to subsequent business analysis, data flow, and other application scenarios.

[0030] This invention achieves intelligent parsing of Excel spreadsheets by combining a large language model with structured prompts. It can flexibly adapt to different header structures, solving the problems of traditional methods that rely on fixed rules and have poor adaptability. By establishing a mapping relationship between fields and column indexes, it achieves accurate data extraction, significantly reducing manual intervention and improving parsing efficiency and automation. This method has good compatibility and scalability, can adapt to complex Excel files, and outputs standardized structured data to meet the business data processing needs of various scenarios.

[0031] Based on the above embodiments, as a preferred implementation, step S1, which involves loading the target Excel file and obtaining its worksheets, and constructing structured prompts, includes: First, use an Excel parser to open and read the target Excel file, obtain all worksheets in the workbook, and complete the basic content reading and initialization of each worksheet.

[0032] After obtaining the worksheet, structured prompts are constructed as the core input instructions for the Large Language Model (LLM). In this embodiment, the structured prompt text includes four key components: role definition, task definition, example guidance, and output specification, as detailed below: Role Definition: This defines the specialized role of the large language model in the target business domain. In this implementation, the large language model is explicitly designated as an "AUTOSAR (Automotive Open System Architecture) expert," enabling it to understand relevant business terminology within the AUTOSAR domain. It is designed to accurately identify the semantics of core business terms such as ECU (Electronic Control Unit), Msg (message), and Signal, focusing the large language model's general text understanding capabilities on the specific technical field of AUTOSAR. This avoids misinterpreting specialized field names as ordinary data values ​​due to a lack of domain knowledge.

[0033] Task Definition: This is the core task used to instruct the large language model to perform the identification of the row and column positions of the table header information. In this implementation, the core task of the large language model is clearly stated as "identifying the row and column positions of the header information in a given table," while specifying that the input data is the original table row data separated by commas. This enables the large language model to accurately understand the data organization and parsing boundaries when it receives the actual table data.

[0034] Example Guidance: This provides a table example containing header rows and data rows to assist the large language model in understanding the specific execution criteria of the task. In this implementation, a complete AUTOSAR signal mapping example is provided, consisting of header rows and data rows. The header rows visually demonstrate the typical format characteristics of field name rows in the input data, while the data rows display the specific instance numerical styles. Through this example, the large language model understands the specific execution method and judgment criteria of the task using a few-shot learning approach; that is, the first row containing descriptive semantic attributes is the header row, and subsequent rows containing specific instance numerical values ​​are the data rows.

[0035] Output Specification: This specification defines the output of the large language model as a structured data format containing header direction and corresponding row / column index information. In this implementation, the output of the large language model is strictly defined to be in pure JSON format and must contain two key fields: the first field is "direction", which indicates the header direction and is limited to "horizontal" or "vertical". "horizontal" represents a horizontal header, i.e., the header is located in the top row of the table, and "vertical" represents a vertical header, i.e., the header is located in the left column of the table; the second field is "index", which indicates the row index or column index list occupied by the header. For example, [1] indicates that the header is only located in the first row (or the first column), and [1, 2] indicates that the header spans the first and second rows (or the first and second columns). This design can support accurate description of complex merged headers that span multiple rows or columns, ensuring that subsequent mapping relationships and field data extraction can accurately correspond to the correct positions.

[0036] The four key components mentioned above together constitute the structured prompt text, which serves as the core input instruction for the large language model. This guides the large language model to accurately and efficiently perform the task of recognizing table header information in subsequent steps, based on the professional perspective of AUTOSAR experts and in accordance with clear task objectives and standardized output formats.

[0037] This embodiment constructs structured prompts comprising four key components: role definition, task definition, example guidance, and output specifications. This allows the general semantic understanding capabilities of a large language model to be precisely focused on header recognition tasks within a specific business domain. Role definitions enable the model to understand domain terminology; task definitions clarify the recognition target; example guidance intuitively demonstrates the execution standards through small-sample learning; and output specifications ensure the programmable processing of the parsing results with a unified structure. Through the synergistic effect of these components, accurate and automatic recognition of complex header information is achieved. This effectively overcomes the shortcomings of traditional fixed-rule methods that struggle to adapt to diverse header formats, significantly reduces reliance on manually written parsing scripts, and improves the intelligence and automation level of the parsing process.

[0038] Based on the above embodiments, as a preferred implementation, in step S2, the table data of the worksheet and the structured prompt words are submitted to a large language model for semantic parsing to obtain the parsing result containing the table header information, and a mapping relationship between field names and column indexes is established based on the parsing result, specifically including the following steps S21~S23: S21, extract table sample data from the worksheet that includes at least a header area, combine the table sample data with the structured prompt words, and submit them to the large language model.

[0039] First, extract sample table data from the worksheet, ensuring it contains at least a header area. This sample data is extracted from the worksheet to be parsed and includes at least a header area. Typically, this sample data is taken from the first few rows or all of the worksheet to ensure the large language model has sufficient contextual information to accurately determine the header position. The extracted sample table data is then combined with structured prompts to form the complete model input, which is submitted to the large language model.

[0040] Upon receiving input, the large language model, based on its pre-training knowledge of the world and guided by AUTOSAR domain expert roles, specific tasks, and examples defined in the structured prompts, performs deep semantic analysis on the input tabular text. During semantic analysis, the model distinguishes header rows from data rows by comparing the textual features of each row: typically, the first row contains descriptive field names with abstract definitions, such as "ECU(Tx)" (Electronic Control Unit Transmit) or "Signal Name," while subsequent rows contain specific data instance values, such as "FLXXX" or "FLXXX_1_CRC." By recognizing these differences in semantic attributes, the model determines which rows or columns possess the abstract descriptive semantics of "field names" and which belong to specific data values, thus achieving accurate identification of header rows or columns with field name semantic attributes. In this process, the model relies on a deep understanding of textual semantics, rather than simple string matching or rule-based judgments based on fixed positions.

[0041] S22, the large language model performs semantic analysis on the input tabular data based on the role definition, task definition, example guidance and output specifications contained in the structured prompt words, and identifies the header row or header column with semantic attributes of field names.

[0042] Based on its semantic understanding results, the large language model outputs the recognition conclusion strictly in accordance with the JSON format defined in the prompt words. The large language model outputs the parsing results containing the direction of the table header and the corresponding row / column index according to the output specification. The output results clearly indicate the direction of the table header (horizontal or vertical) and its precise index position in the table.

[0043] S23, determine the header area where the field name is located based on the parsing result, read the field name in the header area, establish the mapping relationship between each field name and the corresponding column index in the data area, and generate a standardized header structure for multiple rows of headers.

[0044] Specifically, the system receives the parsing results from the large language model, determines the header area containing the field names based on the header direction and row / column index information in the parsing results, reads the field name text within that header area, and establishes a mapping relationship between each field name and the corresponding column index in the data area. For cases with multiple header rows, the header information is standardized to generate a unified and standardized single-level header structure, ensuring that each field name accurately corresponds to the correct column position in the data area, providing a reliable mapping basis for data extraction in subsequent steps.

[0045] This embodiment combines table sample data with structured prompts and submits them to a large language model for deep semantic analysis. Based on pre-trained knowledge and the comprehensive guidance of role definitions, task definitions, and example guidance within the prompts, the model intelligently and accurately identifies header rows or columns by comparing the semantic attribute differences between rows of data. This overcomes the sensitivity of traditional fixed-rule methods to changes in header format. The model strictly adheres to output specifications to generate standardized parsing results, clearly defining the header direction and precise index positions. Based on this, it establishes an accurate mapping relationship between field names and column indices, standardizing multiple header rows and providing a reliable positioning foundation for subsequent data extraction, effectively improving the intelligence and accuracy of the parsing.

[0046] Based on the above embodiments, as a preferred implementation, in step S3, the corresponding field data is extracted from the data area of ​​the worksheet based on the mapping relationship, specifically including the following steps S31~S37: S31, call the merged cell processing function to detect and process the merged cells in the worksheet, and copy the values ​​of the merged cells to each of the merged cells.

[0047] Before data extraction, merged cells in the worksheet are preprocessed. The merged cell processing function `handle_merged_cells` is called to iterate through all cells in the target worksheet, identify cell ranges with merge attributes, retrieve the original value stored in the main cell of the merged range, and copy the original value completely to every merged cell within the merged range. This ensures that all cells within the merged range have complete numerical information, resolving the data incompleteness issue caused by Excel's merged cells and guaranteeing the accuracy of subsequent data extraction.

[0048] S32, call the data cleaning function to remove all empty rows and columns in the worksheet, and convert the cleaned table data into a two-dimensional list format.

[0049] After merging cells, the `clean_sheet_data` function is called to clean the worksheet data. First, rows where all columns are empty are detected and removed from the dataset. Second, columns where all rows are empty are also removed. After cleaning, the remaining valid table data is converted into a two-dimensional list format, where each element of the outer list corresponds to a row of data, and each element of the inner list corresponds to a column value within that row. This cleanup and conversion reduces the data size for subsequent processing, improves efficiency, and provides a structured data foundation for subsequent row-by-row traversal and indexing.

[0050] S33, starting from the data rows after the header area, read the data corresponding to each column in the data area row by row according to the mapping relationship between the field name and the column index.

[0051] After data cleaning is completed and converted to a two-dimensional list format, the end position of the header area is located based on the header index information determined in step S2. The data rows following the header area are then traversed row by row. For each data row, the column position corresponding to each field is accurately located based on the field name and column index mapping relationship established in step S2. The data value of the corresponding row cell in that column is read, and the extracted field values ​​are preliminarily processed to complete the extraction operation of the original data corresponding to each business field. This row-by-row processing method ensures the integrity of each row of data, and the precise positioning based on the mapping relationship avoids data extraction errors caused by column offsets.

[0052] S34 performs type conversion and format standardization on the extracted field data, handles null and outlier values ​​in the data, and applies preset business rules to verify and correct the data.

[0053] After line-by-line extraction, the extracted field data undergoes cleaning and standardization. First, the data types of each field are converted, converting string-type values ​​to their corresponding numeric types to standardize the data representation; special format data such as dates and times are standardized. Second, null and outlier values ​​are identified and processed; missing data is appropriately filled or marked, and outliers exceeding reasonable ranges are corrected or removed. Simultaneously, pre-configured business rules are applied to validate and correct the data. For example, the correlation validation rules between the communication bit size (ComBitSize) and the communication signal length (ComSignalLength) are handled to ensure that each field conforms to business logic constraints. Through these processes, data type consistency and business compliance are ensured, improving the quality of the final output data.

[0054] S35 calls the data structure reconstruction function to convert the flattened field-value mapping extracted row by row into a nested dictionary structure, automatically fills in empty values ​​for inconsistent field lengths, and builds a data model that meets business requirements.

[0055] Flattened field-value mapping refers to a one-dimensional key-value pair structure formed after data extraction, where the key is the business field name and the value is the extracted original data corresponding to that field, without business hierarchy. Nested dictionary structure is a multi-level dictionary data structure that conforms to business hierarchy logic. It is a standardized data model adapted to downstream business systems and can clearly reflect the hierarchical relationship between business fields.

[0056] In this embodiment, after data cleaning and standardization, the data structure reconstruction function `rebuild_nested_dict` is called to rebuild the data's organizational structure. The flattened field-value mappings extracted row by row are converted into a nested dictionary structure according to business semantics and data hierarchy. During the conversion process, for cases where field lengths differ between different data rows, missing fields are automatically padded with null values ​​to ensure that all data records have a unified and complete set of fields. Finally, a structured data model that meets business requirements is constructed, transforming the data from a flat, row-based format to a hierarchical format centered on business entities, better expressing the inherent relationships between data.

[0057] S36, validate the reconstructed data and filter invalid data rows according to the preset null value rate threshold.

[0058] After the data structure is reconstructed, the validity of the reconstructed data is verified. The proportion of null values ​​in each data row relative to the total number of fields in that row is calculated and compared to a preset null value rate threshold. The null value rate threshold is a pre-defined threshold percentage of null values ​​used to determine the validity of a data row. When the null value percentage of a single data row exceeds this threshold, the row will be considered invalid and filtered out.

[0059] When the null value rate of a data row exceeds the null value rate threshold, the data quality of that row is deemed unacceptable, and it is filtered out as invalid. By setting a reasonable null value rate threshold, valid data is retained, and low-quality data rows are filtered out, thereby further improving the overall quality and usability of the output data.

[0060] S37. Add source traceability information to each valid data field. The traceability information includes the corresponding file path, worksheet name, row number and column number, to establish full-chain traceability of data.

[0061] After data validation and filtering are completed, source traceability information is added to each valid data field. This information records the precise location of the data in the original Excel file, including: the complete file path of the corresponding Excel file, the name of the worksheet, and the row and column numbers of the data value within that worksheet. By embedding source traceability information into the output data, a full-link tracing capability is established from the final structured data result back to the specific cell in the original Excel file. This facilitates subsequent data verification, auditing, and problem localization, improving the traceability and reliability of the data processing process.

[0062] This invention eliminates the interference of table structure complexity on data extraction by merging cells and cleaning data; it achieves precise row-by-row data extraction based on the mapping relationship between field names and column indices, ensuring the accuracy of extraction; through type conversion, format standardization, business rule verification, and data structure reconstruction, it transforms flat data into a nested dictionary structure that meets business needs, improving the standardization of data; and through null value rate threshold filtering and the addition of source traceability information, it ensures high-quality output data and end-to-end traceability. This solution can effectively handle complex table structures including merged cells, multi-row headers, and missing data, and has strong adaptability and data quality assurance capabilities.

[0063] Figure 2 Here is an overall flowchart of the AI-driven intelligent Excel file parsing method provided by this invention, with reference to... Figure 1 and Figure 2 After loading the target Excel file and obtaining the worksheets therein in step S1, when the target Excel file contains multiple worksheets, the method provided in this embodiment of the invention further includes a multi-worksheet parallel processing step, specifically implemented in the following manner: Specifically, when the target Excel file contains multiple worksheets, a dynamic thread pool technique is used to process the entire parsing task of each worksheet in parallel, making full use of computing resources and accelerating the overall parsing progress. First, the dynamic thread pool is initialized. The dynamic thread pool is a thread management mechanism that dynamically adjusts concurrency capabilities based on the hardware resources of the current computing environment. It is used for parallel scheduling of multi-worksheet parsing tasks, balancing processing efficiency and operational stability. In this embodiment, the optimal number of concurrent worker threads is dynamically determined based on the hardware resources of the current computing environment. Specifically, if not explicitly specified, a recommended value is automatically calculated based on the number of CPU cores, while a reasonable upper limit for the number of threads is set to achieve a balance between performance improvement and avoiding resource contention. For example, a function calculation value of the number of CPU cores is used as the baseline number of threads, and an upper limit is used as a constraint to ensure that the number of threads is within a reasonable range.

[0064] Next, task encapsulation is performed. The entire process of parsing each worksheet in the target Excel file is encapsulated into independent task units; that is, one task unit is generated for each worksheet, and each task unit is independent of the others and has no data dependency. All task units are then aggregated to form a task input list.

[0065] Furthermore, the task input list and single-table parsing function are submitted to the thread pool executor, which then distributes each task unit evenly to available worker threads to perform parallel parsing processing on multiple worksheets. The single-table parsing function is a standardized execution unit that integrates the entire parsing logic of a single worksheet. It integrates a complete execution chain, including intelligent header recognition, field name and column index mapping establishment, and field data extraction. It is the minimum execution logic for parallel parsing tasks.

[0066] Specifically, after receiving the task input list, the thread pool executor submits the task list and the single-table parsing function synchronously to the initialized dynamic thread pool. Following load balancing rules, the executor evenly distributes each task unit in the task list to available worker threads within the thread pool. Each worker thread then synchronously executes the full-process parsing task for its corresponding worksheet, achieving parallel parsing of multiple worksheets and significantly reducing the overall parsing time for multi-worksheet Excel files.

[0067] This embodiment utilizes a dynamic thread pool to perform parallel parsing of multiple worksheets, significantly improving the processing speed of large Excel files, effectively shortening data processing time, enhancing overall parsing efficiency, and meeting the business needs for rapid processing of large-scale data.

[0068] Based on the above embodiments, as a preferred implementation method, refer to... Figure 1 and Figure 2 After obtaining the parsing result containing the header information in step S2, the method provided in this embodiment of the invention further includes a human-computer interaction correction step, which is implemented in the following manner: First, the parsing results output by the large language model are validated to determine whether the header data has been completely extracted. The validation process checks whether the parsing results fully cover all the business fields that need to be extracted, and whether the header direction and index position are consistent with the actual table structure. Through validation, it is determined whether there are any anomalies such as missing header data or incorrect header information identification.

[0069] Once the verification confirms that all header data has been successfully extracted and the header information has been correctly identified, proceed directly to step S3 of the data extraction process based on the parsing result.

[0070] When an anomaly is detected, such as missing header data or incorrect header information recognition, a prompt message is returned to the user for confirmation. This prompt message clearly informs the user which specific fields failed to be extracted or which header information was misrecognized.

[0071] After the user receives the prompt and confirms, the system receives the manual mapping relationship between field names and table header information entered by the user through the interactive interface. This manual mapping relationship is a one-to-one correspondence between business field names and corresponding header columns in the target table, entered and confirmed by the user through the interactive interface. Specifically, users can use the interactive interface to visually associate unrecognized field names with their corresponding header columns in the worksheet, or correct incorrectly recognized header mappings, such as manually mapping "Field A" to "Column X" in the worksheet. After receiving user input, the interactive interface generates standardized manual mapping relationship data.

[0072] After obtaining the manually entered mapping relationship from the user, the mapping relationship between field names and column indices is re-established based on this relationship. This re-established mapping relationship will replace or supplement any erroneous or missing parts in the original parsing results of the large language model, forming a complete and accurate correspondence between fields and column indices. Based on this, the data extraction task is re-executed; that is, based on the corrected mapping relationship, the corresponding field data is extracted row by row from the data area of ​​the worksheet, completing the remaining data processing flow.

[0073] This embodiment addresses the shortcomings of traditional Excel parsing methods, such as the inability to dynamically adjust, the inability to correct header recognition errors, and the low accuracy of non-standard table extraction, through a parsing result verification and human-computer interaction correction mechanism. When header recognition errors or field extraction anomalies occur, the system can receive manual mapping correction information from users through a visual interactive interface, quickly reconstruct the mapping relationship between fields and column indexes, and dynamically adjust the parsing process. This eliminates the need for manual rewriting of the parsing script, improving the fault tolerance, accuracy, and practicality of parsing complex non-standard tables while reducing the cost of manual intervention.

[0074] Based on the above embodiments, as a preferred implementation, after receiving the manual mapping relationship between field names and table header information input by the user through the interactive interface, the method provided by this embodiment of the invention further includes a knowledge base update and adaptive parsing step, specifically implemented in the following manner: First, after the user confirms the correction, the mapping relationship between the confirmed field names and the table header information is stored in the knowledge base. This mapping relationship is continuously accumulated as reference experience for subsequent parsing tasks, gradually enriching the coverage of the knowledge base.

[0075] Subsequently, based on the user's confirmation of the corrected mapping relationship, the Excel file parsing and data extraction tasks were performed again.

[0076] When performing subsequent Excel file parsing tasks, the system first queries the knowledge base for historical header mapping records that match the current parsing task. If a matching record is found, the system directly establishes a mapping relationship between field names and column indexes based on the historical mapping relationship, skipping the step of calling the large language model for semantic parsing, thus achieving adaptive parsing and reducing user intervention. If no matching record is found, the system continues to perform header semantic parsing through the large language model, and corrects the mapping relationship through human-computer interaction when necessary. The corrected mapping relationship is then stored in the knowledge base again, enabling continuous updating and improvement of the knowledge base.

[0077] This embodiment stores the corrected header mapping relationship in a knowledge base, prioritizes the reuse of historical records in subsequent parsing, achieves adaptive parsing, continuously reduces manual intervention, and improves the system's automation and self-adaptation capabilities.

[0078] As a preferred implementation, this embodiment provides an AI-driven intelligent parsing method for Excel files, referring to... Figure 2 The method includes the following steps: First, use an Excel parsing library (such as the openpyxl library) to load the target Excel file, read all worksheets in the workbook, and complete the data initialization of each worksheet.

[0079] Next, the AI ​​header parsing stage begins. Structured hints are constructed, combined with the worksheet data, and submitted to a Large Language Model (LLM) for semantic parsing. The parsing results, including header direction and row / column indexes, are obtained, and a mapping relationship between field names and column indexes is established accordingly.

[0080] After the table header is parsed, the parsing results are validated and judged: If the header data is not retrieved, indicating that a field was not recognized or the header information was misidentified, a prompt message is returned to the user for confirmation. The user manually enters the mapping relationship between field names and header information through the interactive interface to complete the confirmation and correction. The user-confirmed and corrected mapping relationship is stored in the knowledge base, and the mapping relationship between field names and column indexes is re-established based on the corrected mapping relationship before data retrieval is performed again.

[0081] Once all header data has been extracted, the system further determines whether the target Excel file contains multiple worksheets. If multiple worksheets are included, a dynamic thread pool technique is used to perform parallel parsing of each worksheet. Specifically, the number of concurrent worker threads is dynamically determined based on the hardware resources of the current computing environment, using a formulaic calculation based on the number of CPU cores, while setting an upper limit on the number of threads. The entire parsing task for each worksheet is encapsulated into an independent task unit, forming a task input list. The task input list, along with a single-table parsing function integrating intelligent header recognition, field mapping establishment, and field data extraction, is submitted to the thread pool via a thread pool executor. The executor then evenly distributes each task unit to available worker threads, achieving parallel parsing of multiple worksheets. If only a single worksheet is included, serial parsing is performed directly.

[0082] Finally, the data formalization stage begins. Based on the established mapping relationships, field data is extracted row by row from the data area of ​​the worksheet. The extracted data is then integrated and formatted uniformly. As needed, the data is output in JSON (JavaScript Object Notation) format or other structured data formats, completing the entire parsing process.

[0083] Figure 3 The structural block diagram of the AI-driven intelligent Excel file parsing system provided by this invention is shown below. Figure 3 The system includes: The file loading module 301 is used to load the target Excel file and obtain the worksheets within it, and to construct structured prompt words; The header parsing module 302 is used to submit the table data of the worksheet and the structured prompt words to the large language model for semantic parsing, obtain the parsing result containing the header information, and establish a mapping relationship between field names and column indexes based on the parsing result; The data extraction and processing module 303 is used to extract corresponding field data from the data area of ​​the worksheet based on the mapping relationship; The multi-worksheet parallel processing module 304 is used to perform parallel processing of the entire parsing task of each worksheet through a dynamic thread pool when the target Excel file contains multiple worksheets. The human-computer interaction correction module 305 is used to receive mapping correction information input by the user and re-execute the data extraction task when the data of a field is not extracted or the data of the header is incorrectly identified in the header parsing result. The knowledge base update module 306 is used to store the user-corrected table header mapping relationship and to use the stored historical mapping relationship for adaptive parsing in subsequent parsing tasks. The data integration and output module 307 is used to integrate the extracted data and output structured data results.

[0084] Those skilled in the art will understand that, for the sake of convenience and brevity, the specific working process of the above system and modules can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0085] Figure 4 A structural block diagram of the electronic device provided by the present invention, such as Figure 4 As shown, the present invention also provides an electronic device, which can be a mobile terminal, desktop computer, laptop, handheld computer, server, or other computing device. The electronic device 400 includes a processor 401 and a memory 402, wherein the memory 402 stores an AI-driven intelligent Excel file parsing program 403.

[0086] In some embodiments, memory 402 may be an internal storage unit of a computer device, such as a hard drive or memory. In other embodiments, memory 402 may be an external storage device of a computer device, such as a plug-in hard drive, smart media card (SMC), secure digital card (SD), flash card, etc. Further, memory 402 may include both internal and external storage units of the computer device. Memory 402 is used to store application software and various types of data installed on the computer device, such as program code installed on the computer device. Memory 402 can also be used to temporarily store data that has been output or will be output. In one embodiment, when the AI-driven Excel file intelligent parsing program 403 is executed by processor 401, it performs the following steps: Load the target Excel file and extract its worksheets, then construct structured prompts; The table data of the worksheet and the structured prompt words are submitted to the large language model for semantic parsing to obtain the parsing results containing the table header information, and a mapping relationship between field names and column indexes is established based on the parsing results; Based on the mapping relationship, extract the corresponding field data from the data area of ​​the worksheet; Integrate the extracted data and output structured data results.

[0087] In some embodiments, processor 401 may be a central processing unit (CPU), microprocessor, or other data processing chip, used to run program code stored in memory 402 or process data, such as executing an AI-driven intelligent Excel file parsing program.

[0088] This embodiment also provides a computer-readable storage medium storing an AI-driven intelligent Excel file parsing program. When the AI-driven intelligent Excel file parsing program is executed by a processor, it implements the steps of an AI-driven intelligent Excel file parsing method.

[0089] The embodiments described above are merely illustrative of several implementations of the present invention, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of the invention. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of the present invention, and these modifications and improvements all fall within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the appended claims.

[0090] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. An AI-driven intelligent parsing method for Excel files, characterized in that, include: Load the target Excel file and extract its worksheets, then construct structured prompts; The table data of the worksheet and the structured prompt words are submitted to the large language model for semantic parsing to obtain the parsing results containing the table header information, and a mapping relationship between field names and column indexes is established based on the parsing results; Based on the mapping relationship, extract the corresponding field data from the data area of ​​the worksheet; Integrate the extracted data and output structured data results.

2. The AI-driven intelligent parsing method for Excel files according to claim 1, characterized in that, The process of loading the target Excel file and obtaining its worksheets, and constructing structured prompts, includes: Use an Excel parser to open and read the target Excel file to obtain all worksheets in the workbook; Construct structured prompt text containing role definitions, task definitions, example guidance, and output specifications as input instructions for the large language model; The role definition is used to specify the professional roles of the target business domain for the large language model adaptation; the task definition is used to instruct the model to perform the core task of recognizing the row and column positions of table header information; the example guidance is used to provide table examples containing header rows and data rows to help the model understand the task standards; and the output specification is used to limit the model output to a structured data format containing header direction and corresponding row / column index information.

3. The AI-driven intelligent parsing method for Excel files according to claim 1, characterized in that, The table data of the worksheet and the structured prompt words are submitted to a large language model for semantic parsing to obtain parsing results containing table header information. Based on these parsing results, a mapping relationship between field names and column indexes is established, specifically including: Extract table sample data from the worksheet that includes at least a header area, combine the table sample data with the structured prompt words, and submit them to the large language model; The large language model performs semantic analysis on the input tabular data based on the role definition, task definition, example guidance and output specifications contained in the structured prompt words, and identifies the header row or header column with semantic attributes of field names; The large language model outputs parsing results, including the header direction and the corresponding row / column index, according to the output specification. Based on the parsing results, determine the header area where the field names are located, read the field names in the header area, establish a mapping relationship between each field name and the corresponding column index in the data area, and generate a standardized header structure for multiple rows of headers.

4. The AI-driven intelligent parsing method for Excel files according to claim 1, characterized in that, Based on the mapping relationship, the corresponding field data is extracted from the data area of ​​the worksheet, including: Call the merged cell processing function to detect and process merged cells in the worksheet, and copy the values ​​of the merged cells to the merged cells. Call the data cleaning function to remove all empty rows and columns from the worksheet and convert the cleaned table data into a two-dimensional list format; Starting from the data rows after the header area, the data corresponding to each column in the data area is read row by row according to the mapping relationship between the field name and the column index; The extracted field data is converted and formatted, null and outlier values ​​are handled, and preset business rules are applied to validate and correct the data. Call the data structure reconstruction function to convert the flattened field-value mapping extracted row by row into a nested dictionary structure, automatically fill in empty values ​​for inconsistent field lengths, and build a data model that meets business requirements; The reconstructed data is validated, and invalid data rows are filtered out according to the preset null value rate threshold. Add source traceability information to each valid data field. The traceability information includes the corresponding file path, worksheet name, row number and column number to establish full-chain data traceability.

5. The AI-driven intelligent parsing method for Excel files according to claim 1, characterized in that, After loading the target Excel file and retrieving its worksheets, the method further includes: When the target Excel file contains multiple worksheets, initialize a dynamic thread pool, determine the number of concurrent worker threads based on the hardware resources of the current computing environment, and set an upper limit for the number of threads; The entire process of parsing each worksheet is encapsulated into an independent task unit, forming a task input list; The task input list and single-table parsing function are submitted to the thread pool executor, which then distributes each task unit evenly to available worker threads to perform parallel parsing processing on multiple worksheets. The single-table parsing function integrates intelligent header recognition, field mapping establishment, and field data extraction into a complete single-table parsing logic.

6. The AI-driven intelligent parsing method for Excel files according to claim 1, characterized in that, After obtaining the parsed result containing the table header information, the method further includes: The parsing results are verified. When an anomaly is detected, such as field data not being extracted or header information being misidentified, a corresponding anomaly message is pushed to the user for confirmation and correction. The system receives the manual mapping relationship between field names and table header information entered by the user through the interactive interface, re-establishes the mapping relationship between field names and column indexes based on the manual mapping relationship, and re-executes the data extraction task.

7. The AI-driven intelligent parsing method for Excel files according to claim 6, characterized in that, After receiving the manual mapping relationship between field names and table header information input by the user through the interactive interface, the method further includes: Store the mapping relationship between the field names and the header information that have been confirmed and corrected by the user in the knowledge base; When performing subsequent Excel file parsing tasks, the system first queries the knowledge base for historical header mapping records that match the current parsing task. If a matching record is found, the system directly establishes a mapping relationship between field names and column indexes based on the historical mapping relationship to complete adaptive parsing.

8. An AI-driven intelligent Excel file parsing system, the system being used to execute the AI-driven intelligent Excel file parsing method according to any one of claims 1-7, characterized in that, The system includes: The file loading module is used to load the target Excel file and obtain the worksheets within it, and to construct structured prompt words; The header parsing module is used to submit the table data of the worksheet and the structured prompt words to the large language model for semantic parsing, obtain the parsing result containing the header information, and establish the mapping relationship between field names and column indexes based on the parsing result; The data extraction and processing module is used to extract corresponding field data from the data area of ​​the worksheet based on the mapping relationship; The data integration and output module is used to integrate the extracted data and output structured data results.

9. The AI-driven intelligent Excel file parsing system according to claim 8, characterized in that, Also includes: The multi-worksheet parallel processing module is used to perform parallel processing of the entire parsing task of each worksheet through a dynamic thread pool when the target Excel file contains multiple worksheets. The human-computer interaction correction module is used to receive mapping correction information input by the user and re-execute the data extraction task when the table header parsing results show that field data has not been extracted or the table header information is incorrectly identified. The knowledge base update module stores the user-corrected header mapping relationship and prioritizes the use of the stored historical mapping relationship for adaptive parsing in subsequent parsing tasks.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the AI-driven intelligent parsing method for Excel files as described in any one of claims 1 to 7.

Images