Excel data batch processing method and related device

Abstract

The application relates to the technical field of data processing, and discloses an excel data batch processing method and related equipment.The method comprises the following steps: obtaining excel batch data to be processed, analyzing the excel batch data to obtain excel analysis data, and performing data volume statistics on the excel analysis data to obtain an excel data volume; a plurality of threads are created, and a queue corresponding to each thread is created according to the thread; the excel analysis data is equally divided according to the excel data volume to obtain a plurality of excel sub-analysis data, and the excel sub-analysis data is evenly distributed to the normal queue for processing; and the method can improve the document processing efficiency.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method and related equipment for batch processing of Excel data. Background Technology

[0002] Currently, importing specific data by uploading an Excel document is a common functional requirement. The industry standard and existing systems generally employ a single-threaded blocking implementation, where the program sequentially parses the data in the document and processes related logic, mostly involving insertion operations. This approach works well when the document volume is small, but when the document volume reaches hundreds of thousands of records, its biggest drawback becomes apparent: long processing time and low efficiency.

[0003] To address the aforementioned technical issues of single-threaded processing, multi-threading can be used. This involves dividing the document data into equal parts according to certain rules and assigning each part to a separate thread for processing. The results are then aggregated after all threads have finished. While multi-threading significantly improves data processing efficiency, it also has drawbacks. Since the amount of data imported from an Excel document varies, it's impossible to find a constant balance between data segmentation and thread creation. Too many threads lead to frequent CPU switching between threads due to CPU time-slice allocation, consuming resources and reducing CPU utilization. Conversely, too few threads result in each thread processing a large amount of data, potentially failing to achieve the maximum efficiency of multi-threading. Furthermore, if some data is invalid due to business logic or formatting issues, processing of most other valid data may fail. Summary of the Invention

[0004] The purpose of this application is to address the shortcomings of existing technologies by providing a method and related equipment for batch processing of Excel data. By dividing tasks according to the complexity of the Excel data and assigning them to threads for processing, it is possible to flexibly process Excel documents with different data volumes, thereby improving document processing efficiency. Furthermore, during document processing, since abnormal business operations can be handled by an exception queue, it is possible to avoid processing interruptions caused by abnormal business operations, thus improving processing efficiency.

[0005] To achieve the above objectives, the technical solution of this application provides a method and related equipment for batch processing of Excel data.

[0006] This application discloses a method for batch processing Excel data, including the following steps:

[0007] Obtain the batch of Excel data to be processed, parse the batch of Excel data to obtain the parsed Excel data, and perform data volume statistics on the parsed Excel data to obtain the total Excel data volume;

[0008] Multiple threads are created, and queues corresponding to the threads are created. The queues include at least one normal queue and one exception queue. The normal queue is used to receive and process the Excel parsing data, and the exception queue is used to receive and process the exception Excel parsing data when a processing exception occurs in the normal queue.

[0009] The Excel data is divided equally according to the amount of data, resulting in multiple sub-data sets. These sub-data sets are then distributed equally among the normal queue for processing.

[0010] Preferably, the step of creating multiple threads and creating a queue corresponding to each thread includes:

[0011] Obtain the number of CPU cores, create the same number of threads as the number of CPU cores, and create a queue with the same number of threads as the number of threads.

[0012] Preferably, the step of dividing the Excel parsed data into multiple Excel sub-parsed data based on the amount of Excel data includes:

[0013] The number of splits is determined based on the complexity of the batch Excel data. The number of tasks is obtained based on the number of splits. The Excel parsing data is then divided equally according to the number of tasks and the amount of Excel data to obtain multiple Excel sub-parsing data.

[0014] Preferably, the step of evenly distributing the Excel sub-parsed data to the normal queue for processing includes:

[0015] Each Excel file is parsed and packaged into a task, and each task is then assigned to the normal queue in equal order for processing.

[0016] Preferably, the step of assigning each task to the normal queue in equal order for processing includes:

[0017] Each task is assigned to the normal queue in the order of the queue for processing, and the processing status of each normal queue is detected.

[0018] If an abnormal processing status is detected in each of the normal queues, the task that has experienced the processing abnormality is forwarded to the abnormal queue.

[0019] Preferably, after the step of evenly distributing the Excel sub-parse data to the normal queue for processing, the method further includes:

[0020] After the first queue in the normal queue has finished processing its tasks, the task processing status of the remaining normal queues is checked.

[0021] If a task to be processed is detected in any of the other normal queues, the first queue extracts the task to be processed and processes it.

[0022] Preferably, after the step of evenly distributing the Excel sub-parse data to the normal queue for processing, the method further includes:

[0023] When any task in any queue is completed, the task is recorded. When all tasks in all queues are completed, the processing results of all tasks are summarized and output.

[0024] This application also discloses an Excel data batch processing device, the device comprising:

[0025] The acquisition module is used to acquire batch Excel data to be processed, parse the batch Excel data to obtain parsed Excel data, and perform data volume statistics on the parsed Excel data to obtain the total Excel data volume.

[0026] A creation module is used to create multiple threads and create queues corresponding to the threads. The queues include at least one normal queue and one exception queue. The normal queue is used to receive and process the Excel parsing data. The exception queue is used to receive and process the abnormal Excel parsing data when a processing exception occurs in the normal queue.

[0027] The processing module is used to divide the Excel parsed data equally according to the amount of Excel data, obtain multiple Excel sub-parsed data, and distribute the Excel sub-parsed data evenly to the normal queue for processing.

[0028] This application also discloses an Excel data batch processing device, which includes a memory and one or more processors. The memory stores computer-readable instructions, which, when executed by one or more processors, cause the one or more processors to perform the steps of the Excel data batch processing method described above.

[0029] This application also discloses a computer-readable storage medium that can be read and written by a processor. The storage medium stores computer instructions that, when executed by one or more processors, cause the one or more processors to perform the steps of the above-described Excel data batch processing method.

[0030] The beneficial effects of this application are: This application divides tasks according to the complexity of Excel data and assigns them to threads for processing, which can flexibly process Excel documents with different amounts of data, improve document processing efficiency, and when processing documents, since abnormal business can be handled by the exception queue, it can avoid processing interruption caused by abnormal business and improve processing efficiency. Attached Figure Description

[0031] Figure 1 This is a flowchart illustrating an embodiment of an Excel batch data processing method according to this application;

[0032] Figure 2 A schematic diagram of the structure of an embodiment of the queue allocation in this application;

[0033] Figure 3 This is a schematic diagram of an Excel data batch processing device according to an embodiment of this application. Detailed Implementation

[0034] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0035] Those skilled in the art will understand that, unless specifically stated otherwise, the singular forms “a,” “an,” “the,” and “the” used herein may also include the plural forms. It should be further understood that the word “comprising” as used in the specification of this application means the presence of the stated features, integers, steps, operations, elements, and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and / or groups thereof.

[0036] The flowchart of an Excel data batch processing method according to the first embodiment of this application is as follows: Figure 1 As shown, this embodiment includes the following steps:

[0037] Step s101: Obtain the batch of Excel data to be processed, parse the batch of Excel data to obtain parsed Excel data, and perform data volume statistics on the parsed Excel data to obtain the total amount of Excel data.

[0038] Specifically, the batch Excel data can be uploaded by the user on the client or it can be data pre-stored in a database; the number of batch Excel data can be one or more copies; after obtaining the batch Excel data uploaded by the user, the batch Excel data can be parsed, which can include reading the batch Excel data. For example, in practical applications, the data in the Excel spreadsheet can be read one by one; after reading, the actual data in the Excel spreadsheet can be obtained; after obtaining these actual data, the data volume can be counted to obtain the total data volume of the document. The total data volume is used to divide these actual data equally and calculate the data volume of each copy.

[0039] Step s102: Create multiple threads and create queues corresponding to the threads. The queues include at least one normal queue and one exception queue. The normal queue is used to receive and process the Excel parsing data. The exception queue is used to receive and process the exception Excel parsing data when a processing exception occurs in the normal queue.

[0040] Specifically, a certain number of threads can be established first. These threads can be used to process data in an Excel spreadsheet, and the processing can include operations such as writing and importing. After the threads are established, corresponding queues can also be created, with each queue corresponding to one thread, meaning the number of queues is equal. These queues can include normal queues and exception queues. There can be at least one normal queue, and one exception queue. Both normal and exception queues can be used to receive and process data from the Excel spreadsheet. Since data may encounter exceptions during processing, including processing errors and data-related errors such as data format errors or data value errors, exception data needs to be handled by the exception queue. If the exception data is already being processed in the exception queue, no additional operation is required. If the exception data is being processed in the normal queue, the normal queue must stop its current processing and hand over the exception data to the exception queue for processing before proceeding to the next task.

[0041] Optionally, during thread creation, the number of CPU cores can be obtained first. Since the number of CPU cores is determined by the system hardware, once the system hardware is determined, the current number of CPU cores can be determined, and the number of CPU cores determines the data processing speed and efficiency. Then, the number of threads to be created can be determined based on the number of CPU cores. In specific implementations, the number of threads can be equal to the number of CPU cores. After the threads are created, a queue corresponding to the number of threads can be created.

[0042] Step s103: Divide the Excel parsing data equally according to the amount of Excel data to obtain multiple Excel sub-parsing data, and distribute the Excel sub-parsing data evenly to the queue for processing.

[0043] Specifically, this division can include equally dividing the parsed Excel data. In practical applications, since the total amount of data in the Excel file has already been obtained in step s101, it is only necessary to pre-set the number of parts to perform the equal division. For example, assuming the total amount of data is s, and the number of parts is set to 4, then the parsed Excel data is divided into 4 equal parts, resulting in 4 sub-parsed Excel data sets, each with a data size of s / 4. After obtaining each sub-parsed Excel data set, it can be evenly distributed to a queue for processing. During the even distribution process, it is possible that the Excel data will be... The number of sub-data parsed and the number of queues may not be a perfect match for even distribution, but they may not be. Therefore, during the even distribution process, the data can be distributed sequentially according to the queue order. For example, if there are 32 sub-data parsed from an Excel file and there are 6 queues, it is obviously impossible to distribute them evenly. In this case, the Excel sub-data parsed can be distributed one by one according to the queue order. Assuming the queue numbers are 1-6, the first data is allocated to queue 1, the second data is allocated to queue 2, and so on. After the first round of 6 data is distributed, each queue will have one data. Then, the data is distributed to queues 1-6 again, and the above steps are repeated until all the data is distributed.

[0044] Optionally, since the amount of Excel data 's' acquired each time may vary, and the complexity of the data may also differ, the processing efficiency will also vary. Processing complex data will definitely take longer, while processing simple data will take shorter. In this case, the number of splits 'n' can be preset, and this number of splits can be correlated with the complexity of the data. For example, if the data is more complex, the number of splits can be set higher, and if the data is simpler, the number of splits can be set lower. In specific implementation, since Excel data parsing can reveal specific data information, such as data categories and values, the complexity of the Excel data can be obtained, and the number of splits can be determined based on the complexity of the Excel data parsing. Once the number of splits is obtained, the task quantity R can be obtained based on the number of splits 'n'. The task quantity R can be calculated as R = 2. n The document data will be divided into 2 n Once the task quantity R is calculated, the document data can be divided equally according to the amount of Excel data s to obtain Excel sub-parsed data. After equal division, the data volume of each Excel sub-parsed data is s / R.

[0045] Furthermore, after obtaining the sub-parsed data of the Excel file, each sub-parsed data of the Excel file can be encapsulated. This encapsulation can encapsulate each sub-parsed data of the Excel file into a task. Then, each task can be assigned to a queue in the order of the queue for processing. After encapsulation, all the data in each queue will be represented in the form of tasks. The next task will be executed only after one task is completed.

[0046] Optionally, during task allocation, since the queue includes a normal queue and an exception queue, and tasks can be initially assigned to the normal queue, the normal queue can be retrieved first. After retrieving the normal queue, each task is then assigned to it in the correct order and equally for processing. Subsequently, during task processing, the exception queue remains idle, meaning no tasks are processed there, until an exception occurs in the normal queue. In this case, the exception data is then passed to the exception queue for processing. Figure 2 As shown.

[0047] Alternatively, during the initial task allocation, tasks can be evenly distributed to the normal queue and the abnormal queue. That is, in the initial transition state, the normal queue and the abnormal queue are treated equally. The abnormal data is only handed over to the abnormal queue for processing when the normal queue encounters abnormal data. In other words, the normal queue only processes normal data, while the abnormal queue processes both normal data and abnormal data.

[0048] Furthermore, although each task has the same amount of data, the data itself is different, meaning the complexity varies. Therefore, the processing speed of each queue differs. Typically, after one queue has processed all its tasks, other queues still have tasks waiting to be processed. This is detrimental to the overall system efficiency. Therefore, after any normal queue has processed all its tasks, the task processing status of the remaining normal queues can be checked. Checking the task processing status of the remaining normal queues can include checking whether there are still tasks waiting to be processed in the other normal queues. Tasks waiting to be processed do not include tasks currently being processed. Taking the first normal queue as an example, which can be any normal queue, if the first queue has processed all its tasks and then detects tasks waiting to be processed in any of the other normal queues, the first queue can extract and process these tasks, thereby improving the efficiency of business processing.

[0049] Optionally, during the process of detecting the task processing status, it is also possible to detect whether there are still normal data tasks waiting to be processed in the abnormal queue. If it is detected that there are tasks waiting to be processed in any of the other normal queues, or that there are normal data tasks waiting to be processed in the abnormal queue, then the waiting service can be obtained and processed. The method of obtaining can include random acquisition, that is, a task can be randomly obtained from the queue with tasks to be processed and processed until all queues have completed their tasks.

[0050] Furthermore, when the queue is processing tasks, it can process tasks one by one. Therefore, when any task is completed, the task can be recorded. The task record can include the data volume and data source of the task. Then, the processing result of the task can be set as successful and cached. When all tasks in all queues are completed, the processing results of all tasks are summarized. The summary result can include the data volume and data source of all successful tasks, and the summary result can be output.

[0051] In this embodiment, tasks are divided according to the complexity of the Excel data and handled by threads. This allows for flexible processing of Excel documents with different data volumes, improving document processing efficiency. Furthermore, during document processing, since abnormal business operations can be handled by an exception queue, processing interruptions caused by abnormal business operations can be avoided, further improving processing efficiency.

[0052] An embodiment of this application provides a structure for an Excel data batch processing device as follows: Figure 3 As shown, it includes:

[0053] The system comprises an acquisition module 301, a creation module 302, and a processing module 303; wherein the acquisition module 301 is connected to the creation module 302, and the creation module 302 is connected to the processing module 303; the acquisition module 301 is used to acquire batch Excel data to be processed, parse the batch Excel data to obtain parsed Excel data, and perform data volume statistics on the parsed Excel data to obtain the Excel data volume; the creation module 302 is used to create multiple threads, and create queues corresponding to the threads, wherein the queues include at least one normal queue and one abnormal queue, the normal queue is used to receive and process the parsed Excel data, and the abnormal queue is used to receive and process the abnormal parsed Excel data when a processing abnormality occurs in the normal queue; the processing module 303 is used to divide the parsed Excel data equally according to the Excel data volume to obtain multiple sub-parsed Excel data, and distribute the sub-parsed Excel data equally to the normal queue for processing.

[0054] This application also discloses an Excel data batch processing device, which includes a memory and one or more processors. The memory stores computer-readable instructions. When the computer-readable instructions are executed by one or more processors, the one or more processors perform the steps in the Excel data batch processing method described in the above embodiments.

[0055] This application also discloses a computer-readable storage medium that can be read and written by a processor. The storage medium stores computer-readable instructions, which, when executed by one or more processors, cause one or more processors to perform the steps in the Excel data batch processing method described in the above embodiments.

[0056] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. This computer program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the embodiments of the methods described above. The aforementioned storage medium can be a non-volatile storage medium such as a magnetic disk, optical disk, or read-only memory (ROM), or random access memory (RAM).

[0057] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

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

Claims

1. A method for batch processing Excel data, characterized in that, The Excel data batch processing method includes: Obtain batch Excel data to be processed, parse the batch Excel data to obtain parsed Excel data, and perform data volume statistics on the parsed Excel data to obtain the Excel data volume. The parsing includes reading the batch Excel data. Multiple threads are created, and queues corresponding to the threads are created. The queues include at least one normal queue and one exception queue. The normal queue is used to receive and process the Excel parsed data, and the exception queue is used to receive and process the abnormal Excel parsed data when a processing exception occurs in the normal queue. The Excel data is divided equally based on its volume to obtain multiple sub-data sets. These sub-data sets are then evenly distributed to the normal queues for processing. This process includes: determining the number of splits based on the complexity of the batch Excel data; determining the number of tasks based on the number of splits; dividing the Excel data equally based on the number of tasks and the volume of the Excel data to obtain multiple sub-data sets; encapsulating each sub-data set into a task; and equally distributing each task to the normal queues in order; detecting the processing status of each normal queue; and forwarding the task with the abnormal processing status to the abnormal queue if any normal queue is detected as abnormal.

2. The Excel data batch processing method as described in claim 1, characterized in that, The step of creating multiple threads and creating a queue corresponding to each thread includes: Obtain the number of CPU cores, create the same number of threads as the number of CPU cores, and create a queue with the same number of threads as the number of threads.

3. The Excel data batch processing method as described in claim 1, characterized in that, After the step of evenly distributing the Excel sub-parsed data to the normal queue for processing, the process further includes: After the first queue in the normal queue has finished processing its tasks, the task processing status of the remaining normal queues is checked. If a task to be processed is detected in any of the other normal queues, the first queue extracts the task to be processed and processes it.

4. The Excel data batch processing method as described in claim 1, characterized in that, After the step of evenly distributing the Excel sub-parsed data to the normal queue for processing, the process further includes: When any task in any queue is completed, the task is recorded. When all tasks in all queues are completed, the processing results of all tasks are summarized and output.

5. An Excel data batch processing device, characterized in that, The Excel data batch processing device includes: The acquisition module is used to acquire batch Excel data to be processed, parse the batch Excel data to obtain parsed Excel data, and perform data volume statistics on the parsed Excel data to obtain the Excel data volume. The parsing includes reading the batch Excel data. A creation module is used to create multiple threads and create queues corresponding to the threads. The queues include at least one normal queue and one exception queue. The normal queue is used to receive and process the Excel parsing data. The exception queue is used to receive and process the abnormal Excel parsing data when a processing exception occurs in the normal queue. The processing module is used to divide the Excel parsed data equally according to the volume of Excel data to obtain multiple Excel sub-parsed data, and to distribute the Excel sub-parsed data evenly to the normal queue for processing. This includes: determining the number of splits based on the complexity of the Excel batch data; obtaining the number of tasks based on the number of splits; dividing the Excel parsed data equally according to the number of tasks and the volume of Excel data to obtain multiple Excel sub-parsed data; encapsulating each Excel sub-parsed data into a task; and distributing each task equally to the normal queue in queue order for processing; distributing each task equally to the normal queue in queue order for processing, and detecting the processing status of each normal queue; if any normal queue is detected to have an abnormal processing status, forwarding the task with the abnormal processing status to the abnormal queue.

6. An Excel data batch processing device, characterized in that, The Excel data batch processing device includes a memory and one or more processors. The memory stores computer-readable instructions, which, when executed by one or more processors, cause the one or more processors to perform the steps of the Excel data batch processing method as described in any one of claims 1 to 4.

7. A computer-readable storage medium, characterized in that, The storage medium can be read and written by a processor, and the storage medium stores computer instructions. When the computer instructions are executed by one or more processors, the one or more processors cause the one or more processors to perform the steps of the Excel data batch processing method as described in any one of claims 1 to 4.

Images