CN114416789A - System and method for realizing Excel data import and export based on SQL-like - Google Patents

Abstract

The invention discloses a system and a method for realizing Excel data import and export based on similar SQL, which comprises a display layer, a universal import and export system and a service system, wherein the universal import and export system comprises a task manager, a template analyzer, an actuator and an output device, and the universal import and export system realizes the execution function of the whole task. The process of business development is greatly accelerated, the development task of 2-3 days is shortened to less than one day, most logics which are easy to generate errors are avoided, and the business is supported quickly and stably. After the import and export logic is imported, the system is upgraded in the later period, the functions are newly added, and more universal expansibility is achieved.

Description

Implementing Excel data import and export system and method based on SQL-like

technical field

The invention belongs to the technical field of data processing, and in particular relates to a system and method for implementing Excel data import and export based on SQL.

Background technique

As a management background for businesses and operations, there are usually more management requirements for configuration data and sales data, such as batch online/offline products, order reconciliation, etc., so the system needs to provide more files for processing For the data upload and download functions, the existing Excel data import and export have the following problems: importing and exporting large files takes a long time, and synchronization fails; a single function has a long development cycle and is prone to errors, and code redundancy cannot be shared.

SUMMARY OF THE INVENTION

The technical problem solved by the present invention is to provide a system and method for importing and exporting Excel data based on SQL-like, which can reduce the system load, improve the business development process, and be friendly to customers.

Technical scheme: in order to solve the above-mentioned technical problems, the technical scheme adopted in the present invention is as follows:

A system for importing and exporting Excel data based on class SQL, comprising: a presentation layer, a general importing and exporting system and a business system; the general importing and exporting system includes a task manager, a template parser, an executor and an exporter, and the general importing and exporting system realizes the overall task execution function,

The presentation layer realizes user creation of tasks and result query, and performs task interaction with the general import and export system, first interacts with the task manager, and the task manager realizes the creation of specific tasks;

After the task manager receives the instruction to create the task, it searches for the task template, and submits the asynchronous task to the template parser for parsing;

The template parser realizes template parsing, first obtains the template cache, then judges whether there is a template, if there is no template, the parser is used to perform template parsing, and the parsing result is stored in the template cache area;

The executor implements task execution, and executes according to different business scenarios, importing parsing files and generating error record files depending on business results; exporting to generate business result files, and outputting the results.

Preferably, the specific business execution of the task execution module is implemented by calling an external business execution module, by parsing the file when importing, uploading it to the business execution module by remote calling, and directly downloading data to the executor by remotely calling the business execution module when exporting, Finally, save the execution information and record the results.

A method for importing and exporting Excel data based on SQL-like,

S1: User creation task: the user sends a task creation instruction to the general import and export system;

S2: The general import and export system creates tasks according to user tasks: first, find the task template, and determine whether there is a template. If there is a template, the task is persisted, and a one-step task is submitted to the template parser. If there is no template, the task creation fails. and feedback failure warnings to users;

S3: Template parsing: first obtain the template cache, and then determine whether there is a template. If there is a template, proceed to the next step: task execution; if there is no template, use the parser to parse the template, and store the parsing result in the template cache area ;Template parsing includes import template parsing and export template parsing;

S4: Task execution: When importing, by parsing the file, upload it to the business execution module of the business system remotely, and generate a prompt file for the remote result; when exporting, directly call the business execution module of the business system, generate an export file for the remote result, and finally save the execution information , and record the results. Users can query the results through polling on the results page or go to the specified system location to query the task results. Task execution specifically includes import task execution and export task execution.

Preferably, in step S3, the specific steps of importing template analysis are as follows:

S311: Read the template string, and then divide the string by spaces;

S312: Read the "by" position, read the character before by and the character after by, and assemble into detailed input information;

S313: Read the position of "parent-by", read the previous character of parent-by and the next character of parent-by, and assemble it into a parent class to package the input information;

S314: Read the "form" position, take the last character, and obtain the upload file parameter field;

S315: Read the "unique" position, go to the next character, and obtain the unique configuration;

S316: Read the "exception" position, and then read the as position, including the character before as and the character after as, and assemble the abnormal output information;

S317: Read the "#" position and assemble it into an additional conditional Map.

Preferably, in step S3, the specific steps of exporting template analysis are:

S321: Export template parsing, read the template string and split the string by spaces;

S322: Read the "as" position, read the character before as and the character after as, and assemble it into an output information configuration;

S323: Read the "form" position, take the last character, and assemble the data acquisition method;

S324: Read the "where" position, determine whether the position exists, and if so, read until the keyword into is encountered, assemble the connector and the field, and form a conditional mapping;

S325: If the "where" position does not exist, read the "into" position, take the last character, and assemble it into an output device;

S326: Read the "#" position and assemble into an additional conditional Map.

Preferably, in step S4, the specific steps for executing the import task are as follows:

S411: Import the execution task, and read the template configuration;

S412: Download the imported file from the file system;

S413: Failed to generate excel file: The generated excel file contains error information;

S414: Read and upload the excel file: read the uploaded excel file line by line to determine whether the batch setting is satisfied, if the batch setting is satisfied, jump to step S416; if the batch setting is not satisfied, jump to step S415;

S415: Determine whether the read data is excel end-of-line data, if it is end-of-line data, go to step S416 and step S417; if it is not end-of-line data, return to continue to execute step S414;

S416: data processing, business interface parameters, uploading data to the business system; if all successes are performed in one time, proceed to step S414, if not all successes in one time, write the failure reason to the excel file;

S417: After the file is read, the excel file stream is closed;

S418: Upload excel to the file server, delete the local excel, and persist the task result.

Preferably, in step S4, the specific steps of exporting task execution are as follows:

S421: Export the execution task and read the template configuration;

S422: Create an empty excel file;

S423: Assemble the interface parameters, and execute the business execution module that invokes the business system to execute specific tasks; after the execution is completed, return data, perform data processing, and write the file to the excel file;

S424: Determine whether the returned data satisfies the continuation condition, if the continuation condition is satisfied, repeat step S423, if the continuation condition is not satisfied, it means that the export of data is completed, and the excel file stream is closed;

S425: Upload excel to the file server, delete the local excel, and persist the task result. Beneficial effect: Compared with the prior art, the present invention has the following advantages:

(1) Regarding the task execution mode (asynchronous), the method of the present invention has passed the verification of production, and no longer has the problem that the past synchronous import and export wait time is too long (gateway timeout), the import and export tasks fail directly, and the execution result cannot be obtained. .

(2) Regarding the repeated historical data, there is no need to increase the load on the system again, and the past data can be queried directly through the history, which greatly improves the user-friendliness of customers.

(3) Regarding the interaction design, it solved the problem that when the import data part fails, if the amount of data is too much, friendly information cannot be prompted on the page.

(4) Regarding the ease of use of development, the process of business development has been greatly accelerated. The development tasks of the past 2-3 days have been shortened to less than one day, and many error-prone places (such as Excel reading and writing occupying system memory are avoided) ) to ensure fast and stable business support.

(5) Regarding function upgrades, after the import and export logic is incorporated, for later system upgrades and new functions, there will be more general extensibility.

Description of drawings

Figure 1 is a block diagram of the Excel data import and export system based on class SQL;

Figure 2 is a flow chart of the method for importing and exporting Excel data based on SQL-like;

Figure 3 is a flow chart of implementing Excel data import template parsing based on SQL-like;

Figure 4 is an example diagram of implementing Excel data import template analysis based on class SQL;

Figure 5 is a flow chart of implementing Excel data export template parsing based on SQL-like;

Figure 6 is an example diagram of the realization of Excel data export template parsing based on class SQL;

Fig. 7 is the execution flow chart of implementing Excel data import task based on class SQL;

Figure 8 is a flow chart of implementing the Excel data export task execution based on SQL-like.

Detailed ways

The present invention will be further illustrated below in conjunction with specific examples, which are implemented on the premise of the technical solutions of the present invention. It should be understood that these examples are only used to illustrate the present invention and not to limit the scope of the present invention.

As shown in Figure 1, the application's SQL-based implementation of Excel data import and export system, including display layer (user), access import and export system and business system, general import and export system includes task manager, template parser, executor and exporter (result record).

The presentation layer implements user creation tasks and result queries, and interacts with the general import and export system. First, it interacts with the task manager, and the task manager realizes the creation of specific tasks. After the access import and export system completes the task execution and outputs the results, the user queries the results.

The general import and export system implements the execution function of the overall task. After the task manager receives the instruction to create the task, it searches for the task template and determines whether there is a template. If there is a template, the task is persisted, and a one-step task is submitted to the template parser. If there is no template, the task creation fails, and the failure warning is fed back to the user.

The template parser implements template parsing. First, it obtains the template cache, and then determines whether there is a template. If there is no template, the parser is used for template parsing, and the parsing result is stored in the template cache area.

The executor implements task execution, and executes it according to different business scenarios, importing parsing files and generating error record files depending on business results; exporting and generating business result files, and the specific business execution of the task execution module is realized by calling an external business execution module. , when importing, it parses the file, uploads it to the business execution module by remote calling, and directly downloads the data to the executor by calling the business execution module remotely when exporting, and finally saves the execution information and records the result. , and record the results. The present invention adopts the method of asynchronous task decoupling, and no longer obtains the results synchronously. The results are guided by the front-end interaction to the task management office for viewing. If you need to view the synchronous results in real time (for example, after uploading data, refresh the current page), the front-end polls the task. result, until success or failure.

The invention also discloses a method for importing and exporting Excel data based on class SQL, as shown in Figure 2, which specifically includes the following steps:

S1: User creation task: the user sends a task creation instruction to the general import and export system;

S2: The general import and export system creates tasks according to user tasks: first, find the task template, and determine whether there is a template. If there is a template, the task is persistent, and the asynchronous task is submitted to the template parser. If there is no template, the task creation fails. and feedback failure warnings to users;

S3: Template parsing: first obtain the template cache, and then determine whether there is a template. If there is a template, proceed to the next step: task execution; if there is no template, use the parser to parse the template, and store the parsing result in the template cache area . The specific content of template parsing includes import template parsing and export template parsing.

As shown in Figure 3, for the import template analysis flowchart, the specific steps of import template analysis are:

S311: Read the template string, and then divide the string by spaces;

S312: Read the position of "by", read the character before by and the character after by, and assemble the detailed input information; the syntax used is a syntax customized by simulating sql syntax.

For example: as shown in Figure 4, the labels ① to ④ are the information of the read "by" position, including the order number, effective time, expiry time, price, information on the shelf and off the shelf, etc. The information in the by position is assembled into Detailed input information. Label ①: Indicates that the orderNo input parameter of the business interface when importing into Excel is taken from the data in the Excel column named "Order No."; Label ②: On the basis of label ①, it supports the deserialization of field types, "YYYY-MM- DD HH:mm:ss" is deserialized into Date type; label ③: On the basis of label ①, it supports the deserialization of field types, and the read number string is deserialized into BigDecimal type; label ④: in label On the basis of ①, field mapping is supported, and reading "1" is mapped to true.

S313: Read the position of "parent-by", read the character before parent-by and the character after parent-by, and assemble the input information into a parent class package.

For example, the symbol ⑤ in Figure 4: operatorId and operatorName are reserved fields, and the insertion condition is brought in by default. The insertion condition is the original information of the imported file, including the creator information.

S314: Read the "form" position, take the last character, and obtain the upload file parameter field;

For example, the symbol ⑥ in Figure 4 describes that the source url of the imported file is the creation task parameter field, and the content is the address of the import file upload server.

S315: Read the "unique" position, go to the last character, and obtain the unique configuration;

For example, label ⑦: describes which fields in the uploaded file can ensure the uniqueness of the data.

S316: Read the "exception" position, and then read the as position (loop processing), including the character before as and the character after as, and assemble into abnormal output information;

For example, label ⑧: describes what information should be displayed in the error message when the import data part fails.

S317: Read the "#" position and assemble it into an additional conditional Map.

所定义的各类信息：标号⑨：描述了业务接口相关信息；标号⑩：描述了批量导入时一批次的数据量；标号

描述了部分导入返回失败时，错误信息的路由位置；标号

描述了上传文件的头信息相关；insert/by/insert-parent/parent-byfrom/unique/exception为保留关键字，描述了整个模板的框架。E.g

Various types of information defined: Label ⑨: Describes business interface-related information; Label ⑩: Describes the amount of data in a batch when importing in batches; Label

Describes the routing location of error information when some import returns fail; label

Describes the header information related to the uploaded file; insert/by/insert-parent/parent-byfrom/unique/exception is a reserved keyword, describing the framework of the entire template.

As shown in Figure 5, in order to export the template parsing flowchart, the specific steps of exporting template parsing are:

S321: Export template parsing, read the template string and split the string by spaces;

S322: Read "as" position: read the character before as and the character after as, and assemble it into an output information configuration;

As shown in Figure 6, the information of the "as" position read by the labels ① to ⑥ includes the order number, effective time, expiration time, price, return reason, price, etc. These information are assembled into the output information configuration; label ①: It is explained that the field when exporting Excel is taken from the orderNo of the business interface, and the header information of the Excel column is the "single number" label ②: On the basis of the label ①, the serialization of the field type is supported, and the Date type is displayed as "YYYY-MM" -DD HH:mm:ss" label ③: On the basis of label ①, it supports the serialization of field types, showing that the BigDecimal type is a number with 2 decimal places. Label ④: On the basis of label ①, it supports field mapping and displays 1->"I don't want it anymore" label ⑤: On the basis of label ③, the Excel column becomes a numeric type, supporting quick pivot. Label ⑥: On the basis of label ①, it supports dynamically changing data columns.

S323: Read the "form" position, take the last character, and assemble the data acquisition method;

S324: Read the "where" position, determine whether the position exists, and if so, read it until the keyword into is encountered, assemble the connector and the field, and form a conditional mapping. Labels ⑦～⑩ as shown in Figure 6, label ⑦: illustrates a parameter conversion mapping label; ⑧: On the basis of label ⑦, the conversion of parameter types, "YYYY-MM-DD HH:mm:ss" is deserialized as Date type; label ⑨: on the basis of label ①, it is a reserved field to record the operator ID; label ⑩: on the basis of label ①, it is a reserved field to record the operator's name.

into Excel说明了数据输出为Excel形式S325: If the "where" position does not exist, read the "into" position, take the last character, and assemble it into an output device. label

into Excel indicates that the data is output in Excel form

上下信息，描述了业务接口的详情；标号

数据游标，描述了业务接口反参数据的位置。select/from/where/into/as/and/＝为保留关键字，描述了整个模板的框架。S326: Read the "#" position and assemble into an additional conditional Map. For example: label

Up and down information, describing the details of the business interface; label

A data cursor, which describes the location of the business interface counter-parameter data. select/from/where/into/as/and/=are reserved keywords that describe the framework of the entire template.

S4: Import through the analysis file, upload it to the business execution module of the business system remotely, and generate a prompt file for the remote result; for export, directly call the business execution module of the business system, generate an export file for the remote result, and finally save the execution information and record the result. Query the result by polling on the result page or query the task result from the specified system location. Task execution specifically includes import task execution and export task execution.

Task execution specifically includes import task execution and export task execution.

As shown in Figure 7, which is a flowchart of import task execution, the specific steps of import task execution are as follows:

S411: Import the execution task, and read the template configuration;

S412: Download the import file from the file system;

S413: Failed to generate excel file: The generated excel file contains error information.

S414: Read and upload the excel file: read the uploaded excel file line by line to determine whether the batch setting is satisfied, and if the batch setting is satisfied, jump to step S416; if the batch setting is not satisfied, jump to step S415;

S415: Determine whether the read data is excel end-of-line data, if it is end-of-line data, go to step S416 and step S417; if it is not end-of-line data, return to step S414;

S416: data processing, business interface parameters, uploading data to the business system; if all successes are performed in one time, proceed to step S414, if not all successes in one time, write the failure reason to the excel file;

S417: After the file is read, the excel file stream is closed;

S418: Upload excel to the file server, delete the local excel, and persist the task result.

As shown in Figure 8, for the flowchart of export task execution, the specific steps of export task execution are as follows:

S421: Export the execution task and read the template configuration;

S422: Create an empty excel file;

S423: Assemble the interface parameters, and execute the business execution module that invokes the business system to execute specific tasks; after the execution is completed, return data, perform data processing, and write the file to the excel file;

S424: Determine whether the returned data satisfies the continuation condition, if the continuation condition is satisfied, repeat step S423, if the continuation condition is not satisfied, it means that the export of data is completed, and the excel file stream is closed;

S425: Upload excel to the file server, delete the local excel, and persist the task result.

The generality of the present invention is mainly reflected in the development of various services, and no additional code processing is required. Different results can be performed based on different configurations using the general code, and how to describe the configuration is very important. Through research, JSON is used in The configuration will be very complicated, and it is impossible to describe a scene intuitively, but the SQL-like style is greatly improved in readability and ease of use for research and development. By developing a parser that parses SQL-like, the configuration interpretation is realized and the process is completed. execution.

The above are only the preferred embodiments of the present invention. It should be pointed out that for those skilled in the art, without departing from the principles of the present invention, several improvements and modifications can be made. It should be regarded as the protection scope of the present invention.

Claims (7)

1. A system for realizing Excel data import and export based on SQL-like data comprises: the system comprises a display layer, a general import and export system and a service system; the general import and export system comprises a task manager, a template parser, an executor and an outputter, and the general import and export system realizes the execution function of the whole task.

The display layer realizes the establishment of tasks and result query by users, performs task interaction with a general import and export system, firstly interacts with a task manager, and realizes the establishment of specific tasks by the task manager;

after receiving a task creating instruction, the task manager searches a task template and submits an asynchronous task to a template analyzer for analysis;

the template analyzer realizes template analysis, firstly, a template cache is obtained, then, whether a template exists or not is judged, if no template exists, the analyzer is used for analyzing the template, and an analysis result is stored in a template cache area;

the executor executes tasks according to different service scenes, imports an analysis file and generates an error record file depending on a service result; and exporting a generated service result file and outputting a result.

2. The system according to claim 1, wherein the system for importing and exporting Excel data based on SQL-like implementation is characterized in that: the specific service execution of the task execution module is realized by calling an external service execution module, the specific service execution is realized by analyzing a file during import, uploading the specific service execution to a service execution module through remote calling, directly downloading data to an actuator through the remote calling service execution module during export, and finally storing execution information and recording a result.

3. A method for realizing Excel data import and export based on SQL-like is characterized in that:

s1: the user creates a task: a user sends a task creating instruction to the general import and export system;

s2: the general import and export system carries out task creation according to the user task: firstly, searching a task template, judging whether the template exists, if so, persisting the task, submitting an asynchronous task to a template analyzer, if not, failing to create the task, and feeding a failure warning back to a user;

s3: template analysis: firstly, obtaining a template cache, then judging whether a template exists, and if so, carrying out the next step: executing the task; if no template exists, the template is analyzed by using an analyzer, and the analysis result is stored in a template cache region; the template analysis comprises import template analysis and export template analysis;

s4: and (3) task execution: when the client is imported, the file is analyzed and remotely uploaded to a service execution module of a service system, and a prompt file is generated by a remote result; and during exporting, directly calling a service execution module of the service system, generating a export file by remote results, finally storing execution information, recording results, and inquiring results or inquiring task results at a specified system position by a user through polling on a result page. The task execution specifically includes import task execution and export task execution.

4. The method for realizing Excel data import and export based on SQL like of claim 3, wherein: in step S3, the specific steps of importing template analysis are:

s311: reading the template character string and then dividing the character string according to spaces;

s312: reading the position of the 'by', reading the previous character of the by and the next character of the by, and assembling into detailed input information;

s313: reading the position of parent-by, reading the previous character of the parent-by and the next character of the parent-by, and assembling into parent package input information;

s314: reading the 'form' position, taking the next character, and acquiring the parameter field of the uploaded file;

s315: reading the position of the unique, and taking the next character to obtain the unique configuration;

s316: reading the 'exception' position, and reading the as position backwards, wherein the as position comprises a character before the as and a character after the as, and assembling the as position and the as position into abnormal output information;

s317: the "#" position is read and an additional conditional Map is assembled.

5. The method for realizing Excel data import and export based on SQL like of claim 3, wherein: in step S3, the specific steps of deriving template analysis are:

s321: deriving template analysis, reading template character strings and dividing the character strings according to spaces;

s322: reading the position of the 'as', reading the previous character and the next character of the as, and assembling into an output information configuration;

s323: reading the 'form' position, taking the next character, and assembling to obtain a data mode;

s324: reading the 'where' position, judging whether the position exists, if so, reading until the keyword intos is encountered, assembling a connector and a field to form condition mapping;

s325: if the 'where' position does not exist, reading the 'into' position, taking the next character, and assembling into an output device;

s326: the "#" position is read and an additional conditional Map is assembled.

6. The method for realizing Excel data import and export based on SQL like of claim 3, wherein: in step S4, the specific steps for executing the import task are as follows:

s411: importing an execution task, and reading template configuration;

s412: downloading an import file from a file system;

s413: generating a failure excel file: the generated excel file contains error information;

s414: reading an uploaded excel file: reading the uploaded excel file line by line, judging whether batch setting is met, and jumping to the step S416 if the batch setting is met; if the batch setting is not satisfied, jumping to step S415;

s415: judging whether the read data is excel line tail data or not, and if the read data is the excel line tail data, performing step S416 and step S417; if not, returning to continue the step S414;

s416: processing data, uploading data to a service system by using service interface parameters; if the single time is completely successful, continuing to execute the step S414, and if the single time is not completely successful, writing failure reasons into the excel file;

s417: triggering excel file stream closing after the file is read;

s418: and uploading the excel to a file server, deleting the local excel, and then persisting the task result.

7. The method for realizing Excel data import and export based on SQL like of claim 3, wherein: in step S4, the specific steps of deriving task execution are as follows:

s421: exporting an execution task, and reading template configuration;

s422: creating an empty excel file;

s423: assembling interface parameters, and executing a specific task by a service execution module of a calling service system; after the execution is finished, returning data, performing data processing, and writing the file into an excel file;

s424: judging whether the returned data meet the continuing condition, if so, repeatedly executing the step S423, and if not, indicating that the data export is finished, and closing the excel file stream;

s425: and uploading the excel to a file server, deleting the local excel, and then persisting the task result.

Images