Product information batch automatic query method

Abstract

The invention discloses a batch automatic query method for product information. The method comprises the following steps: S10, inputting an account password, a browser path and a product code or a box number to be queried; s20, selecting a module needing to be queried, driving to open a browser, and querying in batches in a query system; s30, downloading a query result to a specified folder for storage; and S40, merging and displaying the downloaded file contents. According to the method, a crawler program is developed in combination with a VBA programming language embedded in Excel and a SeleniumBasic framework, one-button data are automatically inquired and downloaded in batches and automatically merged to a corresponding page, a query page does not need to be concerned in the crawling process, other work items can be processed, the working efficiency of personnel is improved, and mistakes and omissions are reduced.

Description

Technical Field

[0001] The invention belongs to the technical field of information query and relates to a method for batch automatic query of product information. Background Art

[0002] Due to the limitations of SQL statements and factory databases, the factory query system can only query information related to product codes or box numbers within 1,000 pieces at a time. When batch queries are required (such as when the number of product codes or box numbers is greater than 10,000 pieces), it is necessary to manually repeat the query multiple times, and during this period, it is necessary to always pay attention to the query interface, download the query results in time and fill in the next batch of product codes or box numbers. After all queries are completed, they need to be manually copied, pasted and merged. The repetitive work is inefficient and prone to errors. Summary of the invention

[0003] To solve the above problems, the present invention proposes a method for batch automatic query of product information, comprising the following steps:

[0004] S10, enter the account password, browser path and product code or box number to be queried;

[0005] S20, select the module to be queried, drive to open the browser, and query in batches in the query system;

[0006] S30, download the query results to a designated folder for storage;

[0007] S40, merging and displaying the downloaded file contents.

[0008] Preferably, the driving of opening the browser in S20 adopts SeleniumBasic framework combined with ChromeDriver.

[0009] Preferably, in the modules to be queried in S20, the modules include a multi-code query module for different natural years, a current status query module, a current status shipment query module, a box number shipment query module and a fixture mold cavity number query module.

[0010] Preferably, the S20 includes the following steps:

[0011] S21, writing the selected module name to be queried into the corresponding cell of the Excel sheet;

[0012] S22, the driver opens a browser window, inputs the query URL into the address bar and opens it;

[0013] S23, obtaining the login account and password from the fixed cell and logging in;

[0014] S24, opening the corresponding webpage query module according to the module name in the cell;

[0015] S25, whether the query module is a multi-code query module or a fixture cavity number query module;

[0016] If yes, then S26, determine whether the Excel process list area is empty;

[0017] If yes, then S261, select the whole process in the process list drop-down box;

[0018] If not, then S262, select the corresponding process in the process list drop-down box;

[0019] If not, then S27, divide the product code or box number into quantities within 1000;

[0020] S28, fill the segmented product code or box number into the corresponding area of ​​the webpage query module and start the query.

[0021] Preferably, the S30 includes the following steps:

[0022] S31, export the query result to a set folder;

[0023] S32, determine whether all product codes or box numbers have been queried; if yes, proceed to S40; if not, return to S28.

[0024] Preferably, the S40 comprises the following steps:

[0025] S41, close the browser and write the query results in the set folder into the specified Excel form in a loop;

[0026] S42, a pop-up window shows that it is completed.

[0027] The beneficial effects of the present invention include at least: combining the VBA programming language embedded in Excel and the SeleniumBasic framework to develop a crawler program, realize one-click automatic batch query and download of data and automatically merge them into the corresponding page. During the crawling process, there is no need to pay attention to the query page, and other work items can be handled, thereby improving personnel work efficiency and reducing errors.

[0028] The advantages of using VBA+SeleniumBasic in the present invention include at least the following points:

[0029] 1. EXCEL is a commonly used software. Almost every computer in the company has it, which is convenient for program distribution and debugging;

[0030] 2. Considering that most users are non-program developers and have a weak code foundation, this method does not require environment configuration. You only need to fill in the account password and browser path in the corresponding cell;

[0031] 3. The company requires that the account password for crawling web pages be updated every 30 days. This method can be used to directly update the password in the cell after the password is updated without changing the code;

[0032] 4. After crawling, the data is automatically pasted back to the EXCEL file to facilitate further processing by personnel;

[0033] 5. If a user temporarily needs to crawl the content of other pages of the same website, the debugging can be changed directly on the user's computer without the need to change the code in other IDEs. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] Figure 1 A flowchart of a method for batch automatic query of product information according to an embodiment of the present invention;

[0035] Figure 2 The figure is a flow chart of a method for batch automatic query of product information according to a specific embodiment of the present invention. DETAILED DESCRIPTION

[0036] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0037] On the contrary, the present invention covers any substitution, modification, equivalent method and scheme made on the essence and scope of the present invention as defined by the claims. Further, in order to make the public have a better understanding of the present invention, some specific details are described in detail in the detailed description of the present invention below. Those skilled in the art can fully understand the present invention without the description of these details.

[0038] See also Figure 1 , this includes the following steps:

[0039] S10, enter the account password, browser path and product code or box number to be queried;

[0040] S20, select the module to be queried, drive to open the browser, and query in batches in the query system;

[0041] S30, download the query results to a designated folder for storage;

[0042] S40, merging and displaying the downloaded file contents.

[0043] The driver to open the browser in S20 uses the SeleniumBasic framework combined with ChromeDriver.

[0044] In S20, the modules to be queried are selected, including a multi-code query module for different natural years, a current status query module, a current status shipment query module, a box number shipment query module, and a fixture mold cavity number query module.

[0045] In the specific embodiment, see Figure 2 , S20 includes the following steps:

[0046] S21, writing the selected module name to be queried into the corresponding cell of the Excel sheet;

[0047] S22, the driver opens a browser window, inputs the query URL into the address bar and opens it;

[0048] S23, obtaining the login account and password from the fixed cell and logging in;

[0049] S24, opening the corresponding webpage query module according to the module name in the cell;

[0050] S25, whether the query module is a multi-code query module or a fixture cavity number query module;

[0051] If yes, then S26, determine whether the Excel process list area is empty;

[0052] If yes, then S261, select the whole process in the process list drop-down box;

[0053] If not, then S262, select the corresponding process in the process list drop-down box;

[0054] If not, then S27, divide the product code or box number into quantities within 1000;

[0055] S28, fill the segmented product code or box number into the corresponding area of ​​the webpage query module and start the query.

[0056] S30 includes the following steps:

[0057] S31, export the query result to a set folder;

[0058] S32, determine whether all product codes or box numbers have been queried; if yes, proceed to S40; if not, return to S28.

[0059] S40 includes the following steps:

[0060] S41, close the browser and write the query results in the set folder into the specified Excel form in a loop;

[0061] S42, a pop-up window shows that it is completed.

[0062] The query system corresponding to the above method includes a database for providing product information. The source of product information is that when the product is posted in the factory, the product posting information will be uploaded to the database; the information maintenance module is developed using the VBA programming language embedded in Excel. Users need to maintain the account and password for personal login to the query system in the specified area of ​​Excel, and the browser path. If a multi-code query or a fixture mold cavity number query module is used to query the specified area, the process list needs to be maintained in the specified area of ​​Excel; the query module is divided into 2021-2024 machine model multi-code query / current status query / current status shipment query / box number shipment query / fixture mold cavity number query modules according to the query system module; the search engine uses the SeleniumBasic framework combined with the ChromeDriver driver to simulate personnel manually opening the Chrome browser to open the corresponding query module in the query system for batch query; the data output module uses the query system data export function to save the query results as Excel files in the specified path, and automatically reads the Excel files in the specified path in a loop and merges them into the specified Excel sheet after the query is completed.

[0063] The specific steps can be divided into the following steps:

[0064] S11, fill the account password into cells C1 & C2 in the batch query form page of the Excel table;

[0065] S12, copy the browser target path to cell E2 in the same form page;

[0066] S13, paste the product code or box number starting from cell A28 in the same form page;

[0067] S14, click the Click button in the system to select the module to be queried;

[0068] S211, select in the 2021-2024 model multi-code query / current status query / current status shipment query / box number shipment query / fixture mold cavity number query module;

[0069] S221, the driver automatically opens the corresponding query module, calls the crawler code, and then calls the merge code;

[0070] Taking 2024-Multi-code Query as a specific embodiment, the driver automatically opens the query webpage module BU5-Production Management System-2024 Model-Barcode Related Query-Multi-code Query for batch query;

[0071] The crawler code operation process is: first specify the browser file download path; then query the web page login account and password; assign the button to the cell, and assign the cell value to the variable; open the corresponding query interface according to the variable value.

[0072] The specific query process is as follows: when the product code is 1 piece, fill in the SN (serial number) in the SN query box on the query interface, click the query button corresponding to the model year on the query interface, and click the export button to export the query information;

[0073] When the number of product codes is less than or equal to 200, query them all at once, or divide the number of product codes to be queried all at once into 200 / 500 / 1000 batches for query considering database limitations, query speed and functions of different modules;

[0074] However, if the number of product codes is greater than 200, the query will be conducted in batches of 200 at a time; after the query, the query content will be downloaded to the specified folder.

[0075] The merge code process is: traverse the .xlsx files in the specified folder, load the file contents into an array and assign them to sheet2.

[0076] After the query is completed, the downloaded file content will be automatically copied and pasted to the details page and a pop-up window will appear to remind the user that the query has been completed.

[0077] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for batch automatic query of product information, characterized in that: The following steps are involved: S10, enter the account password, browser path and product code or box number to be queried; S20, select the module to be queried, drive to open the browser, and query in batches in the query system; S30, download the query results to a designated folder for storage; S40, merging and displaying the downloaded file contents.

2. The method for batch automatic query of product information according to claim 1, characterized in that: The S20 driver opens the browser using the SeleniumBasic framework combined with ChromeDriver.

3. The method for batch automatic query of product information according to claim 1, characterized in that: In the module to be queried in S20, the modules include a multi-code query module for different natural years, a current status query module, a current status shipment query module, a box number shipment query module and a fixture mold cavity number query module.

4. The method for batch automatic query of product information according to claim 3, characterized in that: The S20 includes the following steps: S21, writing the selected module name to be queried into the corresponding cell of the Excel sheet; S22, the driver opens a browser window, inputs the query URL into the address bar and opens it; S23, obtaining the login account and password from the fixed cell and logging in; S24, opening the corresponding webpage query module according to the module name in the cell; S25, whether the query module is a multi-code query module or a fixture cavity number query module; If yes, then S26, determine whether the Excel process list area is empty; If yes, then S261, select the whole process in the process list drop-down box; If not, then S262, select the corresponding process in the process list drop-down box; If not, then S27, divide the product code or box number into quantities within 1000; S28, fill the segmented product code or box number into the corresponding area of ​​the webpage query module and start the query.

5. The method for batch automatic query of product information according to claim 4, characterized in that: The S30 includes the following steps: S31, export the query result to a set folder; S32, determine whether all product codes or box numbers have been queried; if yes, proceed to S40; if not, return to S28.

6. The method for batch automatic query of product information according to claim 5, characterized in that: The S40 comprises the following steps: S41, close the browser and write the query results in the set folder into the specified Excel form in a loop; S42, a pop-up window shows that it is completed.

Images