Excel data analysis extraction method

Abstract

The application discloses an Excel data analysis and extraction method, which comprises the following steps: receiving an Excel uploading request sent by a client by a server, performing eigenvalue calculation and data extraction on the uploaded Excel as a task and adding the task to a task queue, executing the task to be executed in the task queue by a data extraction and analysis center in the server, saving the extracted data, and feeding back the execution result to the task queue to provide the client with the execution result when the client queries the execution result. The technical effect of the application is to provide a method for analyzing and extracting Excel data, and the application can automatically identify the Excel structure to perform data extraction and conversion, greatly reducing the conversion cost and improving the conversion efficiency.

Description

Technical Field

[0001] This invention relates to the field of Excel data processing, and in particular to a method for Excel data analysis and extraction. Background Technology

[0002] In the course of corporate office work, a large amount of document data is inevitably generated. Among them, Excel spreadsheets are frequently used in corporate office work because of their characteristics of statistical and intuitive data display. However, the large number of Excel documents generated cannot be viewed in a unified manner. The usual solution is to use data management tools to extract data from Excel spreadsheets, convert it into database data for storage, and then use it in a unified manner by programs. However, Excel data is complex and messy, making it difficult to achieve accurate extraction and conversion.

[0003] Currently, some companies restrict the format of user input data in Excel to prevent arbitrary data entry. While this can ensure a uniform data format, it results in a single data extraction program. Different data extraction programs need to be developed for different Excel data, leading to poor usability. Summary of the Invention

[0004] To address the technical challenge of converting Excel data to database data in the computer field, where arbitrary input of Excel data differs from the standard input nature of database data, this invention provides a method for Excel data analysis and extraction. This method automatically identifies the Excel structure for data extraction and conversion, significantly reducing conversion costs and improving efficiency. It solves the problems of low efficiency, high conversion costs, and limited usability inherent in existing technologies that rely on specific methods to extract and convert data from specific-format Excel tables.

[0005] To achieve the above-mentioned technical objectives, the technical solution of the present invention is as follows:

[0006] A method for analyzing and extracting data from Excel includes the following steps:

[0007] Step 1: The server receives the Excel upload request sent by the client, and then uses the uploaded Excel to perform feature value calculation and data extraction as a task and adds it to the task queue.

[0008] Step two: The data extraction and analysis center on the server executes the tasks to be executed in the task queue, then saves the extracted data and returns the execution results to the task queue so that they can be provided to the client when the client queries the execution results. When executing the task, feature values ​​are calculated on the Excel data, and the Excel data is extracted based on the feature values. The feature values ​​are formed by converting the location of the Excel data into a coordinate system and binding the Excel data with the coordinate system.

[0009] In the method described above, in step one, the storage medium for the task queue is a database.

[0010] In the method described above, in step two, the data extraction and analysis center consists of multiple independent cloud virtual machines and is used to provide Excel structure analysis services and Excel data extraction services. The Excel structure analysis service is used to analyze and calculate the feature values ​​of the Excel structure, while the Excel data extraction service extracts Excel data based on the feature values.

[0011] In the method described above, in step two, the data extraction and analysis center on the server executes the tasks in the task queue. First, the scheduling center on the server receives the task retrieval request from the data extraction and analysis center. Then, the scheduling center checks whether there are any tasks to be executed in the task queue. If there are, the scheduling center returns the task to the data extraction and analysis center for execution; otherwise, the scheduling center returns an empty value to the data extraction and analysis center.

[0012] In the second step of the method described above, the positioning of Excel data is converted into a coordinate system, which uses x to represent the row coordinates and y to represent the column coordinates in Excel. When reading Excel data, the content of the Excel cell is the target, and the coordinates of the Excel cell are processed and stored together.

[0013] The method described above processes the coordinates of Excel cells by first determining whether the cell is a merged row or merged column cell. If the current cell is neither merged row nor merged column, the row and column coordinates are multiplied by 2 and saved, resulting in even numbers for both coordinates, indicating that the current cell has no merge format. If the current cell is merged row, the row coordinates are multiplied by 2 and added by 1, and the column coordinates are also multiplied by 2 and saved, resulting in odd numbers for both row and column coordinates, indicating that the current data is in a merged row format. If the current cell is merged column, the column coordinates are multiplied by 2 and added by 1, and the row coordinates are also multiplied by 2 and saved, resulting in odd numbers for both row and column coordinates, indicating that the current data is in a merged row and column format.

[0014] The technical advantage of this invention lies in providing a method for analyzing and extracting data from Excel. This invention can automatically identify the structure of Excel data for extraction and transformation, greatly reducing transformation costs and improving transformation efficiency. Attached Figure Description

[0015] Figure 1 This is a schematic diagram of the structure of an embodiment of the present invention;

[0016] Figure 2 This is a method for calculating feature values ​​and processing coordinates according to an embodiment of the present invention. Detailed Implementation

[0017] See Figure 1 This embodiment uses an Excel data conversion and storage system as an example to demonstrate a method for Excel data analysis and extraction. Figure 1 The main components include:

[0018] 1. File upload client, taking BS mode as an example, can be a common browser (IE, Firefox, etc.).

[0019] 2. Task Queue: A service program deployed in the cloud. When a client uploads an Excel document, the task queue saves / caches the relevant information of the Excel document and binds it to the uploading user information. When the scheduling center requests an executable task, it returns the executable task information.

[0020] 3. Scheduling Center: A service program deployed in the cloud. Its main function is to allocate Excel document analysis and extraction tasks according to the system's established rules, or to track the current number of tasks and the number of tasks executed by the data extraction and analysis center to determine whether to increase or decrease the number of task scheduling tasks.

[0021] 4. Data Extraction and Analysis Center: The core of the system, its main function is to perform structural analysis and data extraction on uploaded Excel documents based on Excel data analysis and extraction methods, complete specific data transformation tasks, output data for storage, write the execution results back to the task queue, and update the corresponding task status.

[0022] Based on the above system architecture, the implementation steps of this embodiment include:

[0023] 1. The server receives the Excel upload request initiated by the user using the client.

[0024] 2. The server adds the uploaded Excel file to the task queue; the storage medium for the task queue is a database (such as MySQL, SQL Server, Oracle, Redis, etc.).

[0025] 3. The Data Extraction and Analysis Center consists of multiple independent cloud-based virtual machines. It offers two types of services: Excel structure analysis and Excel data extraction. The Excel structure analysis service primarily analyzes and calculates the characteristic values ​​of the Excel structure; the Excel data extraction service mainly extracts precise data from Excel based on these characteristic values.

[0026] 4. The data extraction and analysis center polls the scheduling center to obtain tasks to be executed.

[0027] 5. After receiving a request from the data extraction and analysis center, the scheduling center checks the task queue for any tasks to be executed. If so, it returns the task to the data extraction and analysis center for execution. The data extraction and analysis center executes the task and outputs the data, which is then stored in the database. Simultaneously, it stores the execution result in the database corresponding to the task queue. The next time the client queries the task execution status, it will receive the task execution result. Otherwise, it returns an empty value, and the data extraction and analysis center enters the next polling cycle.

[0028] 6. The data extraction and analysis center will perform Excel document structure analysis and data extraction tasks according to the Excel data analysis and extraction methods.

[0029] The specific logic of Excel data analysis and extraction methods is as follows:

[0030] The main challenge in extracting data from Excel lies in the presence of merged rows or columns. Extracting by row or column inevitably results in the loss of this merged formatting. This method preserves this formatting by calculating feature values ​​from the Excel row and column data.

[0031] Feature value calculation method: Convert the Excel data into a coordinate system, with row coordinates represented by x and column coordinates by y. For example, the coordinates of the data in the first row and first column are (x1, y1). When reading Excel data, store the content of the Excel cell as the target, and process its coordinates before storing.

[0032] See Figure 2 The coordinate processing method in this embodiment includes: if the program determines that the current cell is neither row-merged nor column-merged, then the row coordinate and column coordinate are multiplied by 2 and saved. If the row coordinate and column coordinate are even numbers, it indicates that the current cell has no merged format; if the current cell is row-merged, then the row coordinate is multiplied by 2 and added by 1 and stored, and the column coordinate is multiplied by 2 and stored. If the row coordinate is odd and the column coordinate is even, it indicates that the current data is row-merged; if the current cell is column-merged, then the column coordinate is multiplied by 2 and added by 1 and saved, and the row coordinate is multiplied by 2 and saved. If the column coordinate is odd and the row coordinate is even, it indicates that the current data is column-merged; if the current cell is both row-merged and column-merged, then the row coordinate is multiplied by 2 and added by 1 and saved, and the column coordinate is multiplied by 2 and added by 1 and saved. If the row coordinate is odd and the column coordinate is odd, it indicates that the current data is row-column merged.

[0033] Each data point is associated with a coordinate value, resulting in a formatted Excel file stored in the database. To recreate the file as an Excel document, the coordinate values ​​are used to generate the document.

Claims

1. A method for analyzing and extracting data from Excel, characterized in that, Includes the following steps: Step 1: The server receives the Excel upload request sent by the client, and then uses the uploaded Excel to perform feature value calculation and data extraction as a task and adds it to the task queue. Step two: The data extraction and analysis center on the server executes the tasks to be executed in the task queue, then saves the extracted data and returns the execution results to the task queue so that they can be provided to the client when the client queries the execution results. When executing the task, feature values ​​are calculated on the Excel data, and the Excel data is extracted based on the feature values. The feature values ​​are formed by converting the location of the Excel data into a coordinate system and binding the Excel data with the coordinate system. In step two, the location of the Excel data is converted into a coordinate system, which uses x to represent the row coordinates and y to represent the column coordinates. When reading the Excel data, the content of the Excel cell is the target, and the coordinates of the Excel cell are processed and stored together. To process the coordinates of an Excel cell, first determine if the cell is a merged row or merged column cell. If the current cell is neither merged row nor merged column, multiply both the row and column coordinates by 2 before saving. This results in even numbers for both coordinates, indicating that the current cell has no merged format. If the current cell is merged row, multiply both the row and column coordinates by 2 before saving. This results in odd numbers for the row coordinates and even numbers for the column coordinates, indicating that the current data is in a merged row format. If the current cell is merged column, multiply both the column and row coordinates by 2 before saving. This results in odd numbers for both the row and column coordinates, indicating that the current data is in a merged row and column format.

2. The method according to claim 1, characterized in that, In step one, the storage medium for the task queue is a database.

3. The method according to claim 1, characterized in that, In step two, the data extraction and analysis center consists of multiple independent cloud virtual machines and is used to provide Excel structure analysis services and Excel data extraction services. The Excel Structure Analysis Service is used to analyze and calculate the characteristic values ​​of the Excel structure. The Excel data extraction service extracts Excel data based on feature values.

4. The method according to claim 1, characterized in that, In step two, the data extraction and analysis center on the server executes tasks in the task queue. First, the scheduling center on the server receives the task retrieval request from the data extraction and analysis center. Then, the scheduling center checks if there are any tasks to be executed in the task queue. If there are, the scheduling center returns the task to the data extraction and analysis center for execution; otherwise, the scheduling center returns an empty value to the data extraction and analysis center.

Images