Excel file import method and device, computer equipment and storage medium

Abstract

The invention relates to an Excel file import method. The method comprises the steps that a front end receives an Excel file uploaded by a user and sends a file resource address and an import type to a back-end service; obtaining header information of the corresponding Excel file to be imported through the resource address; respectively verifying header information of the Excel file to be imported according to a preset verification rule to obtain a verified Excel file; respectively obtaining a file type and a business standard of the Excel file passing the verification; and analyzing the Excel file in a multi-thread manner according to the file type of the verified Excel file and the business standard. By using templates and strategy modes, general logic is extracted, duplicated codes are reduced, the code reuse rate is increased, and the development speed is increased. And the Excel file is concurrently read by adopting a multi-thread technology when the file is processed, so that the data import speed is remarkably increased, and the user experience is improved.

Description

Technical Field

[0001] The embodiments of the present invention relate to the technical field of data processing, and in particular to a method, device, computer equipment and storage medium for importing Excel files. Background Art

[0002] In the cross-border independent station business, a large number of different types of Excel files need to be imported, such as customers, product reviews, logistics orders, products, buyer blacklists, etc.

[0003] In the prior art, due to the large amount of data in the imported Excel file, a serial reading method of the Excel file data is usually adopted, which does not fully utilize the CPU multi-threading, resulting in poor performance when processing large amounts of data and poor user experience.

[0004] Therefore, in the cross-border independent station business scenario, it is an urgent need to create an efficient, universal and scalable file import method for different types of Excel files. Summary of the invention

[0005] The embodiments of the present invention provide a method, an apparatus, a computer device and a storage medium for importing Excel files, so as to solve the problems of poor performance and poor user experience when a large number of Excel files are imported.

[0006] In a first aspect, an embodiment of the present invention provides a method for importing an Excel file, the method comprising:

[0007] receiving at least one Excel file to be imported, and generating at least one resource address of the Excel file to be imported;

[0008] Obtain the corresponding header information of the Excel file to be imported through the resource address;

[0009] Verify the header information of the Excel file to be imported according to the preset verification rules to obtain an Excel file that passes the verification;

[0010] Respectively obtain the file type and business standard of the Excel file that has passed the verification;

[0011] The Excel file is parsed in multiple threads according to the file type and business standard of the verified Excel file.

[0012] In a second aspect, an embodiment of the present invention further provides a device for importing an Excel file, the device comprising:

[0013] A resource address generating module, used for receiving at least one Excel file to be imported, and generating at least one resource address of the Excel file to be imported;

[0014] A header information acquisition module, used to acquire the corresponding header information of the Excel file to be imported through the resource address;

[0015] A verification module is used to verify the header information of the Excel file to be imported according to preset verification rules to obtain an Excel file that passes the verification;

[0016] A file information acquisition module, used to respectively acquire the file type and business standard of the Excel file that has passed the verification;

[0017] The parsing module is used to parse the Excel file in multiple threads according to the file type and business standard of the verified Excel file.

[0018] In a third aspect, an embodiment of the present invention further provides a computer device, the computer device comprising:

[0019] one or more processors;

[0020] a memory for storing one or more programs,

[0021] When the one or more programs are executed by the one or more processors, the one or more processors implement the Excel file importing method as described in any one of the first aspects.

[0022] In a fourth aspect, an embodiment of the present invention further provides a computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the Excel file importing method as described in any one of the first aspects is implemented.

[0023] In this embodiment, the front end receives the Excel file uploaded by the user and sends the file resource address and import type to the back end service; obtains the header information of the corresponding Excel file to be imported through the resource address; verifies the header information of the Excel file to be imported according to the preset verification rules, and obtains the Excel file that passes the verification; obtains the file type and business standard of the Excel file that passes the verification respectively; and parses the Excel file in multiple threads according to the file type and business standard of the Excel file that passes the verification. By adopting multi-threading technology to concurrently read Excel files, the purpose of increasing the data import speed and improving the user experience is achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] Figure 1A flowchart of a method for importing an Excel file provided in Example 1 of the present invention;

[0025] Figure 2 This is an example diagram of a process flow provided in Embodiment 1 of the present invention;

[0026] Figure 3 A schematic diagram of the structure of an Excel file import device provided in Embodiment 2 of the present invention;

[0027] Figure 4 A schematic diagram of the structure of a computer device provided in Embodiment 3 of the present invention. DETAILED DESCRIPTION

[0028] The present invention will be further described in detail below in conjunction with the accompanying drawings and embodiments. It is to be understood that the specific embodiments described herein are only used to explain the present invention, rather than to limit the present invention. It should also be noted that, for ease of description, only parts related to the present invention, rather than all structures, are shown in the accompanying drawings.

[0029] Embodiment 1

[0030] Figure 1 A flowchart of a method for importing an Excel file provided in the first embodiment of the present invention specifically includes the following steps:

[0031] Step 101, receiving at least one Excel file to be imported, and generating at least one resource address of the Excel file to be imported;

[0032] In an embodiment of the present invention, in a cross-border independent station business scenario, the user selects the Excel file to be uploaded through the front-end interface. There are many types of business files, such as customer Excel, product review Excel, logistics order Excel, product Excel, buyer blacklist Excel, etc. After the front end receives the Excel file uploaded by the user, it sends the Excel file to the back-end static resource server through an HTTP POST request. The static resource server is a server specifically used to store static content (such as pictures, videos, files, etc.). It usually optimizes the storage and retrieval speed of files. After successfully storing the file, the static resource server generates a unique file resource address, i.e., a URL. The URL is the access path of the file on the server, which is used for subsequent file access and processing.

[0033] Further, the back end sends the acquired file URL back to the front end, or directly stores it in the database for subsequent operation. The front end can use this URL to display the file status, or use it for other business logic, which is not limited in this embodiment of the present invention.

[0034] Step 102: Obtain the corresponding header information of the Excel file to be imported through the resource address;

[0035] In an embodiment of the present invention, when the backend service receives a file URL sent by the frontend, this URL points to an Excel file stored on a static resource server, and the backend service uses an HTTP client (such as HttpClient in Java or the requests library in Python) to establish a connection with the static resource server. Through an HTTP GET request, the backend service obtains the input stream (InputStream) of the Excel file pointed to from the static resource server. The Excel header information is usually located in the first line of the file.

[0036] Step 103: Verify the header information of the Excel file to be imported according to the preset verification rules to obtain an Excel file that passes the verification;

[0037] In the embodiment of the present invention, according to the description of step 102, after obtaining the header information of Excel, the header information is verified by a preset verification rule. The verified file is then used by the backend service to read the Excel file stream using a file parsing library (such as Apache POI, openpyxl, etc.) and parse the file content.

[0038] Step 104: respectively obtain the file type and business standard of the Excel file that has passed the verification;

[0039] In an embodiment of the present invention, the file type of an Excel file that has passed the preset verification rules is obtained, such as customer Excel, product review Excel, logistics order Excel, product Excel, buyer blacklist Excel, etc., and then the business standard of the file is obtained, which is usually the business standard purchased by the user, that is, the user's paid service standard.

[0040] It is understandable that providing services of different standards according to user payment standards is set by relevant technical personnel according to actual applications, and the embodiments of the present invention are not limited to this.

[0041] Step 105: parse the Excel file in multiple threads according to the file type and business standard of the Excel file that has passed the verification.

[0042] In an embodiment of the present invention, according to the file type and business standards, the pre-set correct business logic is selected to implement multiple threads to read the respective data areas of the file in parallel, and to implement file processing, such as data processing, to process the read data rows, such as data verification, conversion, storage, etc.

[0043] Preferably, in another embodiment of the present invention, it also includes:

[0044] Step A1: Generate a file parsing adapter according to a preset file type template and a preset parsing strategy corresponding to the file type.

[0045] Preferably, if Figure 2 As shown, design an adapter class that dynamically selects and calls the corresponding business logic implementation according to the import type (such as goods, customers, logistics orders, etc.), that is, the preset file type template and file type are implemented. Implement a registration mechanism that allows the import type to be mapped to the corresponding business logic implementation class at runtime. Create an implementation class for each business logic. These classes encapsulate all the logic required to process specific business data, that is, the preset parsing strategy.

[0046] Preferably, in another embodiment of the present invention, step 105 may include:

[0047] Sub-step 51, importing the verified Excel file into the file parsing adapter, and calling and matching the preset parsing strategy according to the file type of the verified Excel file;

[0048] Specifically, Figure 2 As shown, after completing the mapping of the preset file type template and file type with the corresponding business logic implementation class in the registration mechanism, a unified interface is defined, and all business logic implementation classes must be called through this interface to achieve that different types of Excel files can call these classes indiscriminately.

[0049] Sub-step 52: sending the Excel files that have passed the verification to the concurrent message queues respectively according to the preset parsing strategy;

[0050] Specifically, Figure 2 As shown, first, use a thread pool (such as ExecutorService in Java) to manage threads to improve resource utilization and performance. Second, divide the reading task of the imported Excel file into multiple subtasks, each of which is responsible for reading a part of the file, that is, based on the file size and number of rows. Then, through the publish / subscribe mode of the message queue, publish the subtask to the queue.

[0051] Sub-step 53: concurrently parsing the verified Excel file on the concurrent message queue.

[0052] Specifically, after the consumer obtains tasks from the message queue concurrently, it uses multi-threading or multi-process to parse the file.

[0053] The task of reading the imported Excel file is divided into multiple subtasks, each of which is responsible for reading a part of the file. This allows multiple threads to read their respective data areas in parallel, and then pass the read data rows to the business logic implementation called by the adapter, thus realizing the concurrent processing capability of the consumer.

[0054] Understandably, ensuring that data remains consistent and complete during concurrent processing.

[0055] In actual applications, in order to ensure that the communication of the message queue is secure, encryption and authentication mechanisms are used. The specific settings are implemented by relevant technical personnel according to actual applications, and the embodiments of the present invention are not limited to this.

[0056] Preferably, in another embodiment of the present invention, it also includes:

[0057] Step A2: Return the verified Excel file parsing result.

[0058] Specifically, after each concurrent task is completed, the result (including success, failure, error information, etc.) is sent to a central collector or database and fed back to the front-end user or the service that calls the API.

[0059] In actual applications, front-end users can set up an interface to periodically poll the back-end service to obtain the latest import status and results, and can also proactively notify users of import results through a notification system, such as by email, text message, or in-app notification. The specific form of the above data feedback is set by relevant technicians according to actual applications, and the embodiments of the present invention do not limit this.

[0060] Preferably, in another embodiment of the present invention, the header information includes the number of headers and the name of the header; the preset verification rule includes whether the number of headers and the name of the header meet the preset requirements.

[0061] In the embodiment of the present invention, the header information generally includes the number of headers and the name of the header, and the header number check generally checks whether the number of columns in the header meets the business requirements. The header name check generally checks whether the column name of the header is consistent with the predefined column name.

[0062] Optionally, the header information verification may also include data type verification, that is, checking whether the data type of a specific column is correct.

[0063] It can be understood that the preset verification rules for the header information are set by relevant technical personnel according to actual applications, and the embodiments of the present invention are not limited thereto.

[0064] In this embodiment, the front end receives the Excel file uploaded by the user and sends the file resource address and import type to the back-end service; obtains the header information of the corresponding Excel file to be imported through the resource address; verifies the header information of the Excel file to be imported according to the preset verification rules, and obtains the Excel file that passes the verification; obtains the file type and business standard of the Excel file that passes the verification respectively; and parses the Excel file in multiple threads according to the file type and business standard of the Excel file that passes the verification. By using templates and strategy patterns, common logic is extracted, duplicate code is reduced, code reuse rate is increased, and development speed is accelerated. And when processing files, multi-threading technology is used to concurrently read Excel files, which significantly improves the data import speed and improves the user experience.

[0065] It should be noted that, for the sake of simplicity, the method embodiments are described as a series of action combinations, but those skilled in the art should be aware that the embodiments of the present invention are not limited by the order of the actions described, because according to the embodiments of the present invention, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all preferred embodiments, and the actions involved are not necessarily required by the embodiments of the present invention.

[0066] Embodiment 2

[0067] Figure 3 The structure block diagram of an Excel file import device provided in the second embodiment of the present invention may specifically include the following modules:

[0068] The resource address generating module 201 is used to receive at least one Excel file to be imported, and respectively generate at least one resource address of the Excel file to be imported;

[0069] A header information acquisition module 202, used to acquire the header information of the corresponding Excel file to be imported through the resource address;

[0070] The verification module 203 is used to verify the header information of the Excel file to be imported according to the preset verification rules, and obtain the Excel file that passes the verification;

[0071] The file information acquisition module 204 is used to respectively acquire the file type and business standard of the Excel file that has passed the verification;

[0072] The parsing module 205 is used to parse the Excel file in multiple threads according to the file type and business standard of the verified Excel file.

[0073] Preferably, in another embodiment of the present invention, it also includes:

[0074] The file parsing adapter generation module is used to generate a file parsing adapter according to a preset file type template and a preset parsing strategy corresponding to the file type.

[0075] Preferably, in another embodiment of the present invention, the parsing module 205 is further used for:

[0076] Importing the verified Excel file into the file parsing adapter, and calling and matching the preset parsing strategy according to the file type of the verified Excel file;

[0077] Sending the verified Excel files to the concurrent message queues respectively according to the preset parsing strategy;

[0078] The verified Excel file on the concurrent message queue is parsed concurrently.

[0079] Preferably, in another embodiment of the present invention, it also includes:

[0080] The result returning module is used to return the parsing result of the Excel file that has passed the verification.

[0081] Preferably, in another embodiment of the present invention, the header information includes the number of headers and the name of the header; the preset verification rule includes whether the number of headers and the name of the header meet the preset requirements.

[0082] The device for importing Excel files provided in the embodiment of the present invention can execute the method for importing Excel files provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0083] Embodiment 3

[0084] Figure 4 A schematic diagram of the structure of a computer device provided in Embodiment 3 of the present invention. Figure 4 A block diagram of an exemplary computer device 12 suitable for use in implementing embodiments of the present invention is shown. Figure 4 The computer device 12 shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0085] like Figure 4 As shown, the computer device 12 is in the form of a general-purpose computing device. The components of the computer device 12 may include, but are not limited to: one or more processors or processing units 16, a system memory 28, and a bus 18 that connects various system components (including the system memory 28 and the processing unit 16).

[0086] Bus 18 represents one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, a processor or a local bus using any of a variety of bus architectures. By way of example, these architectures include, but are not limited to, an Industry Standard Architecture (ISA) bus, a Micro Channel Architecture (MAC) bus, an Enhanced ISA bus, a Video Electronics Standards Association (VESA) local bus, and a Peripheral Component Interconnect (PCI) bus.

[0087] The computer device 12 typically includes a variety of computer system readable media. These media can be any available media that can be accessed by the computer device 12, including volatile and non-volatile media, removable and non-removable media.

[0088] The system memory 28 may include computer system readable media in the form of volatile memory, such as random access memory (RAM) 30 and / or cache memory 32. The computer device 12 may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, the storage system 34 may be used to read and write non-removable, non-volatile magnetic media ( Figure 4 not shown, usually called a "hard drive"). Although Figure 4 Not shown, a disk drive for reading and writing to a removable non-volatile disk (e.g., a "floppy disk"), and an optical disk drive for reading and writing to a removable non-volatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical medium) may be provided. In these cases, each drive may be connected to the bus 18 via one or more data medium interfaces. The memory 28 may include at least one program product having a set (e.g., at least one) of program modules that are configured to perform the functions of the various embodiments of the present invention.

[0089] A program / utility 40 having a set (at least one) of program modules 42 may be stored, for example, in the memory 28, such program modules 42 including but not limited to an operating system, one or more application programs, other program modules, and program data, each of which or some combination may include an implementation of a network environment. The program modules 42 generally perform the functions and / or methods of the embodiments described herein.

[0090] The computer device 12 may also communicate with one or more external devices 14 (e.g., keyboards, pointing devices, displays 24, etc.), may communicate with one or more devices that enable a user to interact with the computer device 12, and / or may communicate with any device that enables the computer device 12 to communicate with one or more other computing devices (e.g., network cards, modems, etc.). Such communication may be performed via an input / output (I / O) interface 22. Furthermore, the computer device 12 may also communicate with one or more networks (e.g., local area networks (LANs), wide area networks (WANs), and / or public networks, such as the Internet) via a network adapter 20. As shown, the network adapter 20 communicates with other modules of the computer device 12 via a bus 18. It should be understood that, although not shown in the figure, other hardware and / or software modules may be used in conjunction with the computer device 12, including, but not limited to, microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0091] The processing unit 16 executes various functional applications and data processing by running the programs stored in the system memory 28, such as implementing the Excel file import method provided in the embodiment of the present invention.

[0092] Embodiment 4

[0093] Embodiment 4 of the present invention further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the various processes of the above-mentioned Excel file import method are implemented, and the same technical effect can be achieved. To avoid repetition, it will not be repeated here.

[0094] Among them, computer-readable storage media may include, for example, but are not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or devices, or any combination of the above. More specific examples of computer-readable storage media (a non-exhaustive list) include: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this document, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, device or device.

[0095] Note that the above are only preferred embodiments of the present invention and the technical principles used. Those skilled in the art will understand that the present invention is not limited to the specific embodiments described herein, and that various obvious changes, readjustments and substitutions can be made by those skilled in the art without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in more detail through the above embodiments, the present invention is not limited to the above embodiments, and may include more other equivalent embodiments without departing from the concept of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims

1. A method for importing an Excel file, characterized in that: The method comprises: receiving at least one Excel file to be imported, and generating at least one resource address of the Excel file to be imported; Obtain the corresponding header information of the Excel file to be imported through the resource address; Verify the header information of the Excel file to be imported according to the preset verification rules to obtain an Excel file that passes the verification; Respectively obtain the file type and business standard of the Excel file that has passed the verification; The Excel file is parsed in multiple threads according to the file type and business standard of the verified Excel file.

2. The method according to claim 1, characterized in that Also includes: Generate a file parsing adapter according to the preset file type template and the preset parsing strategy corresponding to the file type.

3. The method according to claim 2, characterized in that The step of multi-threaded parsing of the Excel file according to the file type and business standard of the verified Excel file comprises: Importing the verified Excel file into the file parsing adapter, and calling and matching the preset parsing strategy according to the file type of the verified Excel file; Sending the verified Excel files to the concurrent message queues respectively according to the preset parsing strategy; The verified Excel file on the concurrent message queue is parsed concurrently.

4. The method according to claim 1, characterized in that: After the multi-threaded parsing of the Excel file according to the file type and business standard of the verified Excel file, the method further includes: Returns the parsing result of the Excel file that passes the verification.

5. The method according to claim 2, characterized in that: The header information includes the number of headers and the name of the header; the preset verification rule includes whether the number of headers and the name of the header meet the preset requirements.

6. A device for importing Excel files, characterized in that: The device comprises: A resource address generating module, used for receiving at least one Excel file to be imported, and generating at least one resource address of the Excel file to be imported; A header information acquisition module, used to acquire the corresponding header information of the Excel file to be imported through the resource address; A verification module is used to verify the header information of the Excel file to be imported according to preset verification rules to obtain an Excel file that passes the verification; A file information acquisition module, used to respectively acquire the file type and business standard of the Excel file that has passed the verification; The parsing module is used to parse the Excel file in multiple threads according to the file type and business standard of the verified Excel file.

7. The device according to claim 6, characterized in that Also includes: The file parsing adapter generation module is used to generate a file parsing adapter according to a preset file type template and a preset parsing strategy corresponding to the file type.

8. The device according to claim 7, characterized in that The parsing module is also used for: Importing the verified Excel file into the file parsing adapter, and calling and matching the preset parsing strategy according to the file type of the verified Excel file; Sending the verified Excel files to the concurrent message queues respectively according to the preset parsing strategy; The verified Excel file on the concurrent message queue is parsed concurrently.

9. A computer device, characterized in that: The computer device comprises: one or more processors; a memory for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the Excel file importing method according to any one of claims 1 to 5.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method for importing an Excel file according to any one of claims 1 to 5 is implemented.

Images