Client-based Excel import preprocessing and server load balancing method and system

Abstract

The invention belongs to the technical field of computers, and particularly relates to an Excel import preprocessing and server load balancing method and system based on a client. The method comprises the following steps: S1, providing a downloading template; s2, the user fills data content according to the downloading template, and uploads the filled Excel file; s3, analyzing the Excel file by the client, converting contents in an Excel page into a text array, and acquiring original data; s4, calling a table field type interface and a check interface of the server side, checking the text array in the step S3, and returning structured data and error prompts; s5, rendering the data returned in the step S4 by using a type sub-component according to the type of the field in the downloading template; and S6, when the client has no error prompt, storing the batch uploaded data to the server. The method has the characteristics that the error information can be pre-judged in advance, the pressure of the server is reduced, and the stability of the server can be ensured.

Description

Technical Field

[0001] The invention belongs to the field of computer technology, and in particular relates to a client-based Excel import preprocessing and server load balancing method and system. Background Art

[0002] In various types of systems, the demand for importing data from database tables through Excel is increasing. The traditional processing method is to upload Excel files on the web page, and the server parses the Excel file, reads the data, and inserts it into the data table. All processing is done on the server side.

[0003] This traditional implementation method often has the following disadvantages: 1. When the Excel file is large or there are many concurrent requests, the pressure on the server is usually high, which can easily cause the server to crash.

[0004] Second, the data written by the user in Excel may not be the data structure that the database table wants to store. For example, if the database field is of time type, the Excel table does not contain a text in the time format. Or, for the personnel field, the Excel table contains Zhang San, but there may be multiple Zhang San users in the system's organizational structure. The server-side processing cannot display the error message immediately and objectively. Generally speaking, after all the insertion actions are completed, an error prompt message is returned to the front-end for display. Information cannot be pre-processed.

[0005] 3. When encountering large data volumes, the backend server often takes a long time to process and cannot give an accurate estimate of the remaining time, so the client needs to wait for a long time. Not only does it have a poor interactive experience, but it is also easy to cause errors such as client request timeouts.

[0006] Based on the above problems, it is very important to design a client-based Excel import preprocessing and server load balancing method and system that can predict error information in advance, reduce server pressure, and ensure server stability. Summary of the invention

[0007] The present invention aims to overcome the problems in the prior art that the existing Excel file import method has high server pressure, error information cannot be preprocessed and the interactive experience is poor. The present invention provides a client-based Excel import preprocessing and server load balancing method and system that can predict error information in advance, reduce server pressure and ensure server stability.

[0008] In order to achieve the above-mentioned object of the invention, the present invention adopts the following technical solutions: The client-based Excel import preprocessing and server load balancing method includes the following steps: S1, provide download template; S2, the user fills in the data content according to the downloaded template and uploads the completed Excel file; S3, the client parses the Excel file, converts the content in the Excel page into a text array, and obtains the original data; S4, calling the table field type interface and the check and verification interface on the server side, checking the text array in step S3, and returning structured data and error prompts; S5, using the type subcomponent to render the data returned in step S4 according to the type of the field in the downloaded template; S6, wait until there is no error prompt on the client, and save the batch upload data to the server.

[0009] Preferably, in step S1, the downloaded template is used to provide the user with an Excel template, and enable the user to fill in data according to the Excel template; the Excel template contains the database table and database table fields that need to be imported.

[0010] Preferably, step S3 comprises the following steps: S31, when the user uploads an Excel file, the client uses the FileReader object (HTML5 file object reader, which allows asynchronous reading of user-selected files on the client) to read the content in the Excel file; S32, after reading, converts the read content into a binary array through a Uint8Array object (JavaScript typed array, used to represent an 8-bit unsigned integer array); S33, using the XLSX plug-in, parsing the binary array, and finally obtaining a preliminary string array, that is, a text array.

[0011] Preferably, step S4 comprises the following steps: S41, calling the table field type interface and the check and verification interface provided by the server, and passing the parsed text array as the parameters of the interface to the backend; S42, the back end determines whether the received text array is filled in correctly based on the pre-designed checking logic; the text array includes the table structure of the Excel template, the field name of the Excel template and the text content corresponding to the Excel template field name.

[0012] Preferably, step S5 comprises the following steps: S51, according to the table name of the Excel template, calling the server-side interface, returning the fields of the table and the types corresponding to the fields; Set each cell as a type subcomponent; different type subcomponents process different data formats; different type subcomponents have the same functions, including storing data and displaying error data; S52, when the amount of imported Excel data is too large, using various types of subcomponents and pre-designed core processing components to display the imported Excel data in pages; The core processing component is used for data maintenance, processing and backend interaction; each type subcomponent is only responsible for rendering the data of its own cell. If the data changes, it will be synchronized to the data of the core processing component; when switching pages, the type subcomponent only loads the data of the current page; S53, if there is any error information, an error prompt is given and corresponding error data is adjusted.

[0013] Preferably, step S6 comprises the following steps: S61, calling the service batch saving interface of the server, and transferring the data in the type component in batches to the server for saving.

[0014] The present invention also provides a client-based Excel import preprocessing and server load balancing system including: Template providing module, used to provide download templates; The data filling and uploading module is used for users to fill in data content according to the downloaded template and upload the completed Excel file; The parsing module is used by the client to parse the Excel file, convert the content in the Excel page into a text array, and obtain the original data; The check module is used to call the table field type interface and the check verification interface on the server side, check the text array, and return structured data and error prompts; The rendering module is used to render the returned data using the type subcomponent according to the type of the field in the download template; The save module is used to save the batch upload data to the server when there is no error prompt on the client.

[0015] Compared with the prior art, the present invention has the following beneficial effects: (1) the present invention can avoid the insertion of erroneous data to the greatest extent; the front-end preprocessing can display error prompts more intuitively; (2) the present invention can reduce the pressure on the server, and most of the import work is handed over to the client for processing, which greatly reduces the pressure on the server; in an actual project, there is a function of batch importing process data matrices. The original implementation logic is that the back-end processes all the imported data. Since it is impossible to determine the data and format imported by the user, the server often has a CPU full situation; after changing to front-end preprocessing, the CPU full phenomenon no longer occurs. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] Figure 1 A schematic diagram of an Excel template provided by an embodiment of the present invention; Figure 2 for Figure 1 A schematic diagram of a document after importing data; Figure 3 A schematic diagram of a type of subcomponent contained in a cell; Figure 4 A schematic diagram of the relationship between the core processor and subcomponents; Figure 5 An example of an effect rendered after the front-end page is imported into Excel; DETAILED DESCRIPTION

[0017] In order to more clearly illustrate the embodiments of the present invention, the specific implementation methods of the present invention will be described below with reference to the accompanying drawings. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those of ordinary skill in the art, other accompanying drawings and other implementation methods can be obtained based on these accompanying drawings without creative work.

[0018] The present invention provides a client-based Excel import preprocessing and server load balancing method, comprising the following steps: 1. Provide download templates; 2. The user fills in the data content according to the downloaded template and uploads the completed Excel file; 3. The client parses the Excel file, converts the content in the Excel page into a text array, and obtains the original data; 4. Call the table field type interface and check verification interface on the server side to check the text array in step 3 and return structured data and error prompts; 5. According to the type of the field in the downloaded template, use the type subcomponent to render the data returned in step 4; 6. When there is no error prompt on the client, save the batch upload data to the server.

[0019] The following describes in detail the specific implementation steps of the present invention in practical applications in combination with specific example scenarios: 1. Configure the download template. The purpose of the download template is to provide users with Excel templates, and customers can fill in data according to the template. Figure 1 As shown in the figure, the template contains the database table and database table fields that need to be imported. You can add other fields according to your own business.

[0020] 2. The client uploads the Excel file, and the browser pre-parses the content in Excel. When the user uploads the Excel document, the FileReader object (HTML5 file object reader, which allows the client to asynchronously read the file selected by the user) is used to read the file content. After reading, the Uint8Array object (JavaScript typed array, used to represent an 8-bit unsigned integer array) is used to convert the read content into a binary array. Then the XLSX plug-in is used to parse the above binary array. Finally, a preliminary string array is obtained. Figure 2 The following is an imported Excel document. By reading and parsing the code of the XLSL plug-in, the contents of the plain text array can be obtained.

[0021] 3. In step 2, the Excel content has been converted into a text array. In reality, the types of each field in the database table are different, which may be an organizational structure, a date, or other types. A very important step is to call the inspection interface to convert the content of each row. At the same time, return whether there is an error in each row. First, call the table field type interface to obtain the mapping relationship between all field names and types of the table and save this mapping relationship. Secondly, call the verification interface provided by the server, and pass all the parsed data as the parameters of the interface to the backend. In the inspection interface, the table structure in the template, the field name in the template, and the text data parsed above are passed. The inspection logic is processed according to the backend design of the system itself. After the server obtains the corresponding content of the table structure and the field name of the table structure, it can determine whether the filled content is correct.

[0022] Figure 2 The person filled in the Excel is Zhang San. When it is passed to Zhang San on the server side, because there is no user Zhang San in the system, for this data and this field, the returned parsing result description is that the content cannot be recognized: Zhang San, and the returned field success is false, which means that the parsing failed.

[0023] 4. Front-end rendering of the results of step 3. In the previous steps, the content file in Excel has been successfully converted into the required format data. The next step is to display the format data on the page for users to view and adjust intuitively.

[0024] 4-1. According to the mapping relationship between the field name of the template and the field name and type saved in step 3, deduce the field type of the cell. Figure 3 As shown, each cell is used as a type subcomponent. Depending on the format of the field, the type subcomponents include single-line, multi-line, number, select personnel, select organization (department), drop-down, etc. Different components handle different data formats. For example, the personnel component displays personnel information and calls the personnel selection pop-up window. The drop-down component is a drop-down selection box. Different components have the same function, that is, storing data and displaying incorrect data.

[0025] 4-2. With the type subcomponent rendering cells, the next problem is the amount of data. Generally speaking, the amount of Excel data imported will be very large, so it is necessary to display the imported data in pages. Figure 4 As shown, the paging logic here includes a core processing component and subcomponents of each type of module, namely the type subcomponent above. All data maintenance, processing and backend interaction are in the core processing component. The type subcomponent is only responsible for rendering the data of its own cell. If the data changes, it will be synchronized to the data of the core processing component. When switching pages, the subcomponent only loads the data of the current page.

[0026] 4-3. If there is any error information, adjust the corresponding data. Figure 5 As shown in the figure, because there is no user named Zhang San in the system, an error message will be given. You only need to modify the wrong data. The pre-processing of imported data can avoid errors in imported data to the greatest extent.

[0027] To summarize the key points of this step: 1. Intuitively display the imported data, including prompts for incorrect data; 2. Display Excel data in pages.

[0028] 5. Batch insert data into the database table. In the previous steps, the Excel data has been converted into a unit component visible on the client, and the error message (if any) has been prompted. Next, it is the same as the normal page interaction. Call the server business batch save interface to pass the data in the new component to the server for storage. Considering that the amount of data is generally large, consider inserting data in batches multiple times. After reselecting the erroneous data Zhang San, the data in the personnel xzjg field is transmitted to the server from the original Zhang San to the structured data of a specific person.

[0029] 6. Through the above steps, Excel data can be successfully inserted into the database table.

[0030] It can be seen from the above steps that all Excel steps that originally needed to be processed by the server can be handled by the client through the method of the present invention. The server only needs to provide a table field type interface, a check and verification interface, and a business batch saving interface to achieve visual import operations and server-side load balancing.

[0031] In addition, the present invention also provides a client-based Excel import preprocessing and server load balancing system including: Template providing module, used to provide download templates; The data filling and uploading module is used for users to fill in data content according to the downloaded template and upload the completed Excel file; The parsing module is used by the client to parse the Excel file, convert the content in the Excel page into a text array, and obtain the original data; The check module is used to call the table field type interface and the check verification interface on the server side, check the text array, and return structured data and error prompts; The rendering module is used to render the returned data using the type subcomponent according to the type of the field in the download template; The save module is used to save the batch upload data to the server when there is no error prompt on the client.

[0032] The innovative features of the present invention are as follows: 1. Visualized import process. Compared with the pure server-side processing Excel import analysis. The pre-processing solution allows users to know which stage the import is in as much as possible. At the same time, pre-processing paging and error handling prompts can reduce the pressure on the browser and the occurrence of import errors.

[0033] 2. Server load balancing solution. For the scenario of data import, the present invention has made a full analysis and scientifically disassembled the implementation logic, moving part of the front-end implementation to the front-end and retaining part of the back-end implementation in the back-end, which greatly improves the interactive experience, data accuracy, performance and other indicators.

[0034] The above description is only a detailed description of the preferred embodiments and principles of the present invention. For ordinary technicians in this field, according to the ideas provided by the present invention, there will be changes in the specific implementation methods, and these changes should also be regarded as the protection scope of the present invention.

Claims

1. The client-based Excel import preprocessing and server load balancing method is characterized by: The method comprises the following steps: S1, provide download template; S2, the user fills in the data content according to the downloaded template and uploads the completed Excel file; S3, the client parses the Excel file, converts the content in the Excel page into a text array, and obtains the original data; S4, calling the table field type interface and the check and verification interface on the server side, checking the text array in step S3, and returning structured data and error prompts; S5, using the type subcomponent to render the data returned in step S4 according to the type of the field in the downloaded template; S6, wait until there is no error prompt on the client, and save the batch upload data to the server.

2. The method for Excel import preprocessing and server load balancing based on client according to claim 1, characterized in that: In step S1, the downloaded template is used to provide the user with an Excel template and enable the user to fill in data according to the Excel template; the Excel template contains the database table and database table fields that need to be imported.

3. The client-based Excel import preprocessing and server load balancing method according to claim 2, characterized in that: Step S3 includes the following steps: S31, when the user uploads an Excel file, the client uses the FileReader object to read the content in the Excel file; S32, after reading, converts the read content into a binary array through the Uint8Array object; S33, using the XLSX plug-in, parsing the binary array, and finally obtaining a preliminary string array, that is, a text array.

4. The client-based Excel import preprocessing and server load balancing method according to claim 3, characterized in that: Step S4 includes the following steps: S41, calling the table field type interface and the check and verification interface provided by the server, and passing the parsed text array as the parameters of the interface to the backend; S42, the back end determines whether the received text array is filled in correctly based on the pre-designed checking logic; the text array includes the table structure of the Excel template, the field name of the Excel template and the text content corresponding to the Excel template field name.

5. The method for Excel import preprocessing and server load balancing based on client according to claim 4, characterized in that: Step S5 includes the following steps: S51, according to the table name of the Excel template, calling the server-side interface, returning the fields of the table and the types corresponding to the fields; Set each cell as a type subcomponent; different type subcomponents process different data formats; different type subcomponents have the same functions, including storing data and displaying error data; S52, when the amount of imported Excel data is too large, using various types of subcomponents and pre-designed core processing components to display the imported Excel data in pages; The core processing component is used for data maintenance, processing and backend interaction; each type subcomponent is only responsible for rendering the data of its own cell. If the data changes, it will be synchronized to the data of the core processing component; when switching pages, the type subcomponent only loads the data of the current page; S53, if there is any error information, an error prompt is given and corresponding error data is adjusted.

6. The client-based Excel import preprocessing and server load balancing method according to claim 5, characterized in that: Step S6 includes the following steps: S61, calling the service batch saving interface of the server, and transferring the data in the type component in batches to the server for saving.

7. A client-based Excel import preprocessing and server load balancing system, used to implement the client-based Excel import preprocessing and server load balancing method according to any one of claims 1 to 6, characterized in that: The client-based Excel import preprocessing and server load balancing system includes: Template providing module, used to provide download templates; The data filling and uploading module is used for users to fill in data content according to the downloaded template and upload the completed Excel file; The parsing module is used by the client to parse the Excel file, convert the content in the Excel page into a text array, and obtain the original data; The check module is used to call the table field type interface and the check verification interface on the server side, check the text array, and return structured data and error prompts; The rendering module is used to render the returned data using the type subcomponent according to the type of the field in the download template; The save module is used to save the batch upload data to the server when there is no error prompt on the client.

Images