CN111680477A - Method and device for exporting spreadsheet file, computer equipment and storage medium - Google Patents
Method and device for exporting spreadsheet file, computer equipment and storage medium Download PDFInfo
- Publication number
- CN111680477A CN111680477A CN202010348340.9A CN202010348340A CN111680477A CN 111680477 A CN111680477 A CN 111680477A CN 202010348340 A CN202010348340 A CN 202010348340A CN 111680477 A CN111680477 A CN 111680477A
- Authority
- CN
- China
- Prior art keywords
- query
- data
- parameter
- name
- executed
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/177—Editing, e.g. inserting or deleting of tables; using ruled lines
- G06F40/18—Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F21/00—Security arrangements for protecting computers, components thereof, programs or data against unauthorised activity
- G06F21/60—Protecting data
- G06F21/602—Providing cryptographic facilities or services
-
- H—ELECTRICITY
- H04—ELECTRIC COMMUNICATION TECHNIQUE
- H04L—TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION
- H04L9/00—Cryptographic mechanisms or cryptographic arrangements for secret or secure communications; Network security protocols
- H04L9/06—Cryptographic mechanisms or cryptographic arrangements for secret or secure communications; Network security protocols the encryption apparatus using shift registers or memories for block-wise or stream coding, e.g. DES systems or RC4; Hash functions; Pseudorandom sequence generators
- H04L9/0643—Hash functions, e.g. MD5, SHA, HMAC or f9 MAC
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- Computational Linguistics (AREA)
- General Health & Medical Sciences (AREA)
- Computer Security & Cryptography (AREA)
- Health & Medical Sciences (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Software Systems (AREA)
- Computer Hardware Design (AREA)
- Bioethics (AREA)
- Mathematical Physics (AREA)
- Power Engineering (AREA)
- Computer Networks & Wireless Communication (AREA)
- Signal Processing (AREA)
- Artificial Intelligence (AREA)
- Audiology, Speech & Language Pathology (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the application belongs to the technical field of big data, and relates to a method, a device, computer equipment and a storage medium for exporting an electronic form file, wherein the method comprises the following steps: creating a query module comprising a header, a title attribute, a structured query language to be executed, a spreadsheet name, query parameters and a data number, wherein the structured query language to be executed comprises query conditions and execution statements, and mapping the condition names of the query conditions with the parameter names of the query parameters; when receiving a query instruction, reading the name of the electronic form, the number of data, query parameters, title attributes and a header; replacing the corresponding condition name by the acquired parameter name according to the mapping relation between the parameter name and the condition name; executing a structured query language to be executed, and acquiring service data from a database; and generating the spreadsheet according to the obtained header, the service data, the spreadsheet name and the data number. The present disclosure also relates to blockchain techniques, where the traffic data may be stored in blockchain nodes.
Description
Technical Field
The present application relates to the field of big data technologies, and in particular, to a method and an apparatus for exporting a spreadsheet file, a computer device, and a storage medium.
Background
Exporting an exception file is a common function of a management system, and currently, there are two common implementation manners: firstly, a set of system is independently developed aiming at a specific service scene, data corresponding to a specified field is searched from a database, an express tool is selected to be generated, the data is filled into a sheet of the express in a traversing manner, an express file is derived by calling a stream, but the derived data is changed, a new field is added each time, the field needs to be added in each link, the system is large in change range, and more details are changed, so that omission is easy; the new service needs to export different data and needs to rewrite one set, so that the reusability is low and the flexibility is poor. Secondly, all needed data are backed up in a specified table, all fields are listed in a page, a user selects the needed data, the system records each user specified export field, inquires the data and exports an Excle file. However, when all data are put in one table, the fields are more, and the data volume is larger, the export time is longer, the performance is poorer, the number of fields of which the data need to be redundant is too large, and the real-time data is difficult to export.
Disclosure of Invention
The embodiment of the application aims to provide a method, a device, computer equipment and a storage medium for exporting spreadsheet files, so that the response speed of a system is improved, and the pressure of the system is reduced.
In order to solve the foregoing technical problem, an embodiment of the present application provides a method for exporting a spreadsheet file, which adopts the following technical solutions:
a method of exporting a spreadsheet file, comprising the steps of:
creating a query template, wherein the query template comprises a header, a title attribute, a structured query language to be executed, an electronic form name, query parameters and a data number, the header corresponds to the title attribute one by one, the structured query language to be executed comprises query conditions and execution statements, the query conditions comprise condition names, the query parameters comprise parameter names, and the condition names and the parameter names are mapped one by one;
when a query instruction is received, reading the electronic form name, the data number, the query parameter, the title attribute and a header corresponding to the title attribute in the query template;
replacing the corresponding condition name in the structured query language to be executed by the acquired parameter name according to the mapping relation between the parameter name and the condition name in the query parameter so as to complete the assembly of the structured query language to be executed;
executing the structured query language to be executed, and acquiring service data from a database; and
and generating and outputting the spreadsheet according to the obtained header, the service data, the spreadsheet name and the data number.
Further, the step of reading the spreadsheet name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template includes:
presetting one-to-one corresponding query codes for the parameter names of the query parameters;
if the received query instruction carries a query code, determining and acquiring a parameter name of a corresponding query parameter in the query template according to the query code;
if the received query instruction does not carry a preset query code, parameter names of all query parameters are obtained;
and reading the electronic form name, the data number, the title attribute and a header corresponding to the title attribute in the query template.
Further, after the step of creating a query template, the method further comprises:
encrypting the query template through a preset key to generate a ciphertext, and storing the ciphertext into a database;
when receiving a query instruction, the step of reading the spreadsheet name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template comprises the following steps:
when an inquiry instruction carrying a secret key is received, the secret key carried by the inquiry instruction is obtained, and the ciphertext is decrypted through the secret key to obtain the inquiry template;
and reading the electronic form name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template.
Further, after the step of mapping the condition names to the parameter names one by one, the method includes: and setting the query condition in the query template in a placeholder mode.
Further, the step of executing the structured query language to be executed and acquiring the service data from the database includes:
acquiring the data number of the query template, and determining the quantity of business data acquired from a database according to the structured query language to be executed;
comparing the amount of the service data with the data number;
if the quantity of the business data is less than or equal to the quantity of the data, executing the structured query language to be executed, and acquiring the business data from a database;
and if the quantity of the business data is greater than the data quantity, executing the structured query language to be executed, and acquiring the business data from the database for multiple times, wherein the quantity of the business data acquired each time is less than or equal to the data quantity.
Further, the step of executing the structured query language to be executed and acquiring the service data from the database includes:
acquiring the data number of the query template, and determining the quantity of business data acquired from a database according to the structured query language to be executed;
comparing the amount of the service data with the data number;
if the quantity of the service data is larger than the data quantity, identifying grade marks carried out on the parameter names in advance, wherein the grade marks at least comprise a first grade and a second grade;
deleting and backing up the parameter names marked at other levels except the first level, and sending the deleted parameter names to appointed personnel through a mail;
and replacing all parameter names in the structured query language to be executed with the parameter names marked by the primary grade, executing the structured query language to be executed, and acquiring service data from a database.
In order to solve the foregoing technical problem, an embodiment of the present application further provides an apparatus for exporting an electronic form file, which adopts the following technical solutions:
an apparatus for exporting a spreadsheet file, comprising:
the system comprises a creating module, a searching module and a processing module, wherein the creating module is used for creating a query template, the query template comprises a header, a title attribute, a structured query language to be executed, an electronic form name, query parameters and a data number, the header corresponds to the title attribute one by one, the structured query language to be executed comprises query conditions and execution statements, the query conditions comprise condition names, the query parameters comprise parameter names, and the condition names and the parameter names are mapped one by one;
the reading module is used for reading the electronic form name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template when a query instruction is received;
the replacing module is used for replacing the corresponding condition name in the structured query language to be executed by using the acquired parameter name according to the mapping relation between the parameter name and the condition name in the query parameter so as to finish the assembly of the structured query language to be executed;
the execution module is used for executing the structured query language to be executed and acquiring service data from a database; and
and the generating module is used for generating and outputting the spreadsheet according to the acquired header, the service data, the spreadsheet name and the data number.
Furthermore, the reading module comprises a setting submodule, an obtaining submodule and a reading submodule;
the setting submodule is used for setting one-to-one corresponding query codes for the parameter names of the query parameters in advance;
the acquisition sub-module is used for determining and acquiring the parameter names of the corresponding query parameters in the query template according to the query codes when the received query instruction carries the query codes, and acquiring the parameter names of all the query parameters when the received query instruction does not carry the preset query codes;
the reading sub-module is used for reading the electronic form name, the data number, the title attribute and the header corresponding to the title attribute in the query template.
In order to solve the above technical problem, an embodiment of the present application further provides a computer device, which adopts the following technical solutions:
a computer device comprising a memory having stored therein a computer program and a processor implementing the steps of the method of exporting a spreadsheet file as described above when executing said computer program.
In order to solve the above technical problem, an embodiment of the present application further provides a computer-readable storage medium, which adopts the following technical solutions:
a computer-readable storage medium having stored thereon a computer program which, when being executed by a processor, carries out the steps of the method of exporting a spreadsheet file as described above.
Compared with the prior art, the embodiment of the application mainly has the following beneficial effects:
the method introduces a template mode, firstly defines a template, sets rules, reads template information according to the set rules, and finishes the export of the electronic forms. In the process of analyzing the template, the assembled database execution statement is obtained by analyzing the parameter name of the query parameter and replacing the condition name in the database execution statement with the parameter name according to the mapping relation, so that the database execution statement is executed, the required data value is obtained from the database, the condition that one set of export function needs to be rewritten when different data values are obtained every time is avoided, the development cost is reduced, the efficiency is improved, and meanwhile, when the system runs, the parameter name can be dynamically modified without interfering the execution of the database execution statement. And the system acquires the corresponding data value according to the parameter name without exporting all the data values in the database at one time, thereby avoiding data redundancy, improving the response speed of the system and reducing the pressure of the system.
Drawings
In order to more clearly illustrate the solution of the present application, the drawings needed for describing the embodiments of the present application will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present application, and that other drawings can be obtained by those skilled in the art without inventive effort.
FIG. 1 is an exemplary system architecture diagram in which the present application may be applied;
FIG. 2 is a flow diagram of one embodiment of a method of exporting a spreadsheet file according to the present application;
FIG. 3 is a schematic diagram illustrating one embodiment of an apparatus for exporting a spreadsheet file according to the present application;
FIG. 4 is a schematic block diagram of one embodiment of a computer device according to the present application.
Reference numerals: 200. a computer device; 201. a memory; 202. a processor; 203. a network interface; 300. means for exporting the spreadsheet file; 301. a creation module; 302. a reading module; 303. a replacement module; 304. an execution module; 305. and generating a module.
Detailed Description
Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs; the terminology used in the description of the application herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the application; the terms "including" and "having," and any variations thereof, in the description and claims of this application and the description of the above figures are intended to cover non-exclusive inclusions. The terms "first," "second," and the like in the description and claims of this application or in the above-described drawings are used for distinguishing between different objects and not for describing a particular order.
Reference herein to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment can be included in at least one embodiment of the application. The appearances of the phrase in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. It is explicitly and implicitly understood by one skilled in the art that the embodiments described herein can be combined with other embodiments.
In order to make the technical solutions better understood by those skilled in the art, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings.
As shown in fig. 1, the system architecture 100 may include terminal devices 101, 102, 103, a network 104, and a server 105. The network 104 serves as a medium for providing communication links between the terminal devices 101, 102, 103 and the server 105. Network 104 may include various connection types, such as wired, wireless communication links, or fiber optic cables, to name a few.
The user may use the terminal devices 101, 102, 103 to interact with the server 105 via the network 104 to receive or send messages or the like. The terminal devices 101, 102, 103 may have various communication client applications installed thereon, such as a web browser application, a shopping application, a search application, an instant messaging tool, a mailbox client, social platform software, and the like.
The terminal devices 101, 102, 103 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, e-book readers, MP3 players (Moving Picture experts Group Audio Layer III, mpeg compression standard Audio Layer 3), MP4 players (Moving Picture experts Group Audio Layer IV, mpeg compression standard Audio Layer 4), laptop portable computers, desktop computers, and the like.
The server 105 may be a server providing various services, such as a background server providing support for pages displayed on the terminal devices 101, 102, 103.
It should be noted that the method for exporting the spreadsheet file provided by the embodiment of the present application is generally performed by a server/terminal device, and accordingly, the apparatus for exporting the spreadsheet file is generally disposed in the server/terminal device.
It should be understood that the number of terminal devices, networks, and servers in fig. 1 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.
With continued reference to FIG. 2, a flowchart of one embodiment of a method of exporting a spreadsheet file according to the present application is shown. The method for exporting the spreadsheet file comprises the following steps:
s1: creating a query template, wherein the query template comprises a header, a title attribute, a structured query language to be executed, an electronic form name, query parameters and a data number, the header corresponds to the title attribute one by one, the structured query language to be executed comprises query conditions and execution statements, the query conditions comprise condition names, the query parameters comprise parameter names, and the condition names and the parameter names are mapped one by one.
In this embodiment, specifically, when the code is written, the query template includes the following five parts:
ExportModels, header and title attributes; sqlModels, structured query language to be executed; sheetName: excle table name; queryParams: inquiring parameters; countNum: the number of data. For example, the code is: "exportModels" [ { "filedName": id "," headerName ": item id" } ]; wherein "filedName": id "is a title attribute, and" headerName ": item id" is a header. And (4) introducing a template mode, defining the template, and directly calling the template when needed.
In step S1, after the step of mapping the condition names to the parameter names, the method includes: and setting the query condition in the query template in a placeholder mode.
In this embodiment, the query condition exists in the query template in a placeholder manner, which achieves prevention of sql injection, that is, when the to-be-executed structured query language is subsequently executed, it can be ensured that the executed to-be-executed structured query language is not tampered by the sql injection manner, and partial potential safety hazards are eliminated.
S2: when a query instruction is received, reading the electronic form name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template.
In this embodiment, the header and the title attribute are pre-filled contents in the template, and can be modified according to actual needs, because the header and the title attribute have a one-to-one correspondence, and after the computer preferentially identifies the title attribute, the header is positioned according to the title attribute. And reading the name and the number of the data of the electronic form to complete the electronic form generated subsequently.
Specifically, in step S2, the step of reading the spreadsheet name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template includes:
presetting one-to-one corresponding query codes for the parameter names of the query parameters;
if the received query instruction carries a query code, determining and acquiring a parameter name of a corresponding query parameter in the query template according to the query code;
if the received query instruction does not carry a preset query code, parameter names of all query parameters are obtained;
and reading the electronic form name, the data number, the title attribute and a header corresponding to the title attribute in the query template.
In the embodiment, the parameter name is determined according to the query code, so that the acquired data is controlled without modifying the parameter name when the system is used, and only the corresponding query code needs to be simply input, so that the data acquisition is conveniently and dynamically controlled. If the received query instruction does not carry the query code, all query parameters are obtained, and then the service data which accord with all query conditions in the database are obtained.
In this embodiment, the electronic device (e.g., the server/terminal device shown in fig. 1) on which the method of exporting the spreadsheet file operates may receive the query instruction through a wired connection or a wireless connection. It should be noted that the wireless connection means may include, but is not limited to, a 3G/4G connection, a WiFi connection, a bluetooth connection, a WiMAX connection, a Zigbee connection, a uwb (ultra wideband) connection, and other wireless connection means now known or developed in the future.
Furthermore, at step S1, namely after the step of creating the query template, the method further includes:
encrypting the query template through a preset key to generate a ciphertext, and storing the ciphertext into a database;
in step S2, that is, when receiving the query instruction, the step of reading the spreadsheet name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template includes:
when an inquiry instruction carrying a secret key is received, the secret key carried by the inquiry instruction is obtained, and the ciphertext is decrypted through the secret key to obtain the inquiry template;
and reading the electronic form name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template.
In this embodiment, the application may select a common encryption method to encrypt the query template, including but not limited to any one of MD5 encryption, HMAC encryption, and base64 encryption. According to the method and the device, the query template is encrypted, so that the safety of the query template is guaranteed, other personnel are prevented from randomly acquiring the query template, and the query template is prevented from being tampered.
S3: and replacing the corresponding condition name in the structured query language to be executed by using the acquired parameter name according to the mapping relation between the parameter name and the condition name in the query parameter so as to finish the assembly of the structured query language to be executed.
In this embodiment, the parameter name of the obtained query parameter is substituted for the condition name of the query condition in the structured query language to be executed, so as to obtain the assembled structured query language to be executed; the parameter names can be dynamically modified when the system runs, and the execution of the database execution statements is not interfered.
S4: and executing the structured query language to be executed, and acquiring service data from a database.
In this embodiment, the corresponding data values are obtained according to the parameter names, and all the obtained data values constitute the service data; the required data values are obtained from the database according to the structured query language to be executed which is automatically assembled by the computer, so that the situation that one set of export function needs to be rewritten every time different data values are obtained is avoided, the development cost is reduced, and the efficiency is improved. Specifically, for example, name: zhang III; the name is a parameter name, and Zhang three is a data value; further explaining, "name" is called "parameter name" in the query parameter of the page, and "attribute name" in the database. The query parameters comprise parameter names, the parameter names and the attribute names in the database are in one-to-one mapping relation, the attribute names are obtained according to the parameter names, and corresponding data values are searched in the database according to the attribute names. The parameter names in the application can be modified according to actual needs in the actual operation process.
Specifically, in step S4, that is, the step of executing the structured query language to be executed, the step of obtaining the service data from the database includes:
acquiring the data number of the query template, and determining the quantity of business data acquired from a database according to the structured query language to be executed;
comparing the amount of the service data with the data number;
and if the quantity of the business data is less than or equal to the data quantity, executing the structured query language to be executed, and acquiring the business data from a database.
And if the quantity of the business data is greater than the data quantity, executing the structured query language to be executed, and acquiring the business data from the database for multiple times, wherein the quantity of the business data acquired each time is less than or equal to the data quantity.
In this embodiment, if the quantity of the service data is less than or equal to the data quantity, it indicates that the quantity of the service data to be acquired from the database meets the requirement, and the structured query language to be executed may be directly executed. The method and the device have the advantages that the data number is preset in the query template, the quantity of the service data acquired at each time is smaller than or equal to the data number, if the quantity of the service data required to be acquired is too large, the service data can be communicated with related personnel, query parameters are changed, query conditions are changed, the quantity of the acquired service data is effectively controlled, quick response and operation of a system are guaranteed, meanwhile, overload work of the system is avoided, and the service life of the system is prolonged.
In addition, in step S4, the step of executing the structured query language to be executed to obtain the business data from the database includes:
acquiring the data number of the query template, and determining the quantity of business data acquired from a database according to the structured query language to be executed;
comparing the amount of the service data with the data number;
if the quantity of the service data is larger than the data quantity, identifying grade marks carried out on the parameter names in advance, wherein the grade marks at least comprise a first grade and a second grade;
deleting and backing up the parameter names marked at other levels except the first level, and sending the deleted parameter names to appointed personnel through a mail;
and replacing all parameter names in the structured query language to be executed with the parameter names marked by the primary grade, executing the structured query language to be executed, and acquiring service data from a database.
In this embodiment, the present application may also directly delete parameter names of other level marks except the first level in the assembled structured query language to be executed, without performing a replacement action again. But performing the alternate action facilitates logging for later tracing. According to the method and the device, the number of the acquired service data is reduced by deleting the parameter names with lower grade marks, so that the operation and response speed of the system are guaranteed while the main service data are acquired.
S5: and generating and outputting the spreadsheet according to the obtained header, the service data, the spreadsheet name and the data number.
In the present embodiment, the spreadsheet of the present application includes, but is not limited to, excle tables; an excel tool pre-stored in the system can be called, and the spreadsheet is generated and output according to the obtained header, the service data, the name of the spreadsheet and the number of data.
According to the method and the device, the template mode is introduced, the template is defined at first, the rule is set, the template information is read according to the set rule, and the electronic form is exported. In the process of analyzing the template, the assembled database execution statement is obtained by analyzing the parameter name of the query parameter and replacing the condition name in the database execution statement with the parameter name according to the mapping relation, so that the database execution statement is executed, the required data value is obtained from the database, the condition that one set of export function needs to be rewritten when different data values are obtained every time is avoided, the development cost is reduced, the efficiency is improved, and meanwhile, when the system runs, the parameter name can be dynamically modified without interfering the execution of the database execution statement. And the system acquires the corresponding data value according to the parameter name without exporting all the data values in the database at one time, thereby avoiding data redundancy, improving the response speed of the system and reducing the pressure of the system.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by a computer program, which can be stored in a computer-readable storage medium, and can include the processes of the embodiments of the methods described above when the computer program is executed. The storage medium may be a non-volatile storage medium such as a magnetic disk, an optical disk, a Read-Only Memory (ROM), or a Random Access Memory (RAM).
It should be understood that, although the steps in the flowcharts of the figures are shown in order as indicated by the arrows, the steps are not necessarily performed in order as indicated by the arrows. The steps are not performed in the exact order shown and may be performed in other orders unless explicitly stated herein. Moreover, at least a portion of the steps in the flow chart of the figure may include multiple sub-steps or multiple stages, which are not necessarily performed at the same time, but may be performed at different times, which are not necessarily performed in sequence, but may be performed alternately or alternately with other steps or at least a portion of the sub-steps or stages of other steps.
With further reference to fig. 3, as an implementation of the method shown in fig. 2, the present application provides an embodiment of an apparatus for exporting a spreadsheet file, where the embodiment of the apparatus corresponds to the embodiment of the method shown in fig. 2, and the apparatus may be applied to various electronic devices.
As shown in fig. 3, the apparatus 300 for exporting a spreadsheet file according to the present embodiment comprises: a creation module 301, a reading module 302, a replacement module 303, an execution module 304, and a generation module 305. Wherein: the creating module 301 is configured to create a query template, where the query template includes a header, a title attribute, a structured query language to be executed, a spreadsheet name, query parameters, and a data number, and the header corresponds to the title attribute one to one, the structured query language to be executed includes query conditions and execution statements, the query conditions include condition names, the query parameters include parameter names, and the condition names and the parameter names are mapped one to one; the reading module 302 is configured to, when a query instruction is received, read a spreadsheet name, a data number, a query parameter, a title attribute, and a header corresponding to the title attribute in the query template; the replacing module 303 is configured to replace, according to the mapping relationship between the parameter name and the condition name in the query parameter, the corresponding condition name in the structured query language to be executed with the acquired parameter name, so as to complete the assembly of the structured query language to be executed; the execution module 304 is configured to execute the structured query language to be executed, and acquire service data from a database; the generating module 305 is configured to generate and output an electronic form according to the obtained header, the service data, the electronic form name, and the data number.
In this embodiment, the present application introduces a template mode, defines a template, sets a rule, reads template information according to the set rule, and completes exception export. The method has the advantages that injection of sql is prevented by using placeholders in database execution statements, in the process of analyzing a template, parameter names of query parameters are analyzed, the parameter names are used for replacing condition names in the database execution statements according to a mapping relation, and the assembled database execution statements are obtained, so that the database execution statements are executed, needed data values are obtained from the database, the situation that one set of export functions need to be rewritten every time different data values are obtained is avoided, development cost is reduced, efficiency is improved, and meanwhile, when a system runs, the parameter names can be dynamically modified without interfering execution of the database execution statements. And the system acquires the corresponding data value according to the parameter name without exporting all the data values in the database at one time, thereby avoiding data redundancy, improving the response speed of the system and reducing the pressure of the system.
The reading module 302 is provided with a sub-module, an obtaining sub-module and a reading sub-module; the setting submodule is used for setting one-to-one corresponding query codes for the parameter names of the query parameters in advance; the acquisition sub-module is used for determining and acquiring the parameter names of the corresponding query parameters in the query template according to the query codes when the received query instruction carries the query codes, and acquiring the parameter names of all the query parameters when the received query instruction does not carry the preset query codes; the reading sub-module is used for reading the electronic form name, the data number, the title attribute and the header corresponding to the title attribute in the query template.
The execution module 304 comprises a determination submodule, a comparison submodule and an execution submodule, wherein the determination submodule is used for acquiring the data number of the query template and determining the quantity of business data acquired from a database according to the structured query language to be executed; the comparison submodule is used for comparing the quantity of the service data with the data quantity; the execution submodule is used for executing the structured query language to be executed when the quantity of the business data is less than or equal to the data quantity, acquiring the business data from the database, executing the structured query language to be executed when the quantity of the business data is greater than the data quantity, and acquiring the business data from the database for multiple times, wherein the quantity of the business data acquired each time is less than or equal to the data quantity.
In addition, the execution submodule comprises an identification unit, a deletion unit and a replacement unit; the identification unit is used for identifying grade marks which are carried out on the parameter names in advance when the quantity of the service data is greater than the data quantity, wherein the grade marks at least comprise a primary grade and a secondary grade; the deleting unit is used for deleting and backing up the parameter names marked at other levels except the first level, and sending the deleted parameter names to a designated person through a mail; the replacing unit is used for replacing all parameter names in the structured query language to be executed by using the parameter names marked by the primary grade, executing the structured query language to be executed and acquiring service data from a database.
In some optional implementations of this embodiment, the apparatus 300 further includes: the device comprises a setting module and an encryption module, wherein the setting module is used for setting the query conditions in the query template in a placeholder manner; the encryption module is used for encrypting the query template through a preset secret key to generate a ciphertext and storing the ciphertext into a database. The reading module 302 further includes a decryption sub-module, and the decryption sub-module is configured to, when receiving a query instruction carrying a key, obtain the key carried by the query instruction, decrypt the ciphertext with the key, obtain the query template, and transmit the obtained query template to the reading sub-module.
In order to solve the technical problem, an embodiment of the present application further provides a computer device. Referring to fig. 4, fig. 4 is a block diagram of a basic structure of a computer device according to the present embodiment.
The computer device 200 comprises a memory 201, a processor 202, a network interface 203 communicatively connected to each other via a system bus. It is noted that only computer device 200 having components 201 and 203 is shown, but it is understood that not all of the illustrated components are required and that more or fewer components may alternatively be implemented. As will be understood by those skilled in the art, the computer device is a device capable of automatically performing numerical calculation and/or information processing according to a preset or stored instruction, and the hardware includes, but is not limited to, a microprocessor, an Application Specific Integrated Circuit (ASIC), a Programmable Gate Array (FPGA), a Digital Signal Processor (DSP), an embedded device, and the like.
The computer device can be a desktop computer, a notebook, a palm computer, a cloud server and other computing devices. The computer equipment can carry out man-machine interaction with a user through a keyboard, a mouse, a remote controller, a touch panel or voice control equipment and the like.
The memory 201 includes at least one type of readable storage medium including a flash memory, a hard disk, a multimedia card, a card type memory (e.g., SD or DX memory, etc.), a Random Access Memory (RAM), a Static Random Access Memory (SRAM), a Read Only Memory (ROM), an Electrically Erasable Programmable Read Only Memory (EEPROM), a Programmable Read Only Memory (PROM), a magnetic memory, a magnetic disk, an optical disk, etc. In some embodiments, the storage 201 may be an internal storage unit of the computer device 200, such as a hard disk or a memory of the computer device 200. In other embodiments, the memory 201 may also be an external storage device of the computer device 200, such as a plug-in hard disk, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card), or the like, provided on the computer device 200. Of course, the memory 201 may also include both internal and external storage devices of the computer device 200. In this embodiment, the memory 201 is generally used for storing an operating system installed in the computer device 200 and various application software, such as program codes of methods for exporting spreadsheet files. Further, the memory 201 may also be used to temporarily store various types of data that have been output or are to be output.
The processor 202 may be a Central Processing Unit (CPU), controller, microcontroller, microprocessor, or other data Processing chip in some embodiments. The processor 202 is generally operative to control overall operation of the computer device 200. In this embodiment, the processor 202 is configured to execute the program code stored in the memory 201 or process data, for example, execute the program code of the method for exporting the spreadsheet file.
The network interface 203 may comprise a wireless network interface or a wired network interface, and the network interface 203 is generally used for establishing communication connection between the computer device 200 and other electronic devices.
In the embodiment, the parameter names can be dynamically modified when the system runs, and the execution of the database execution statements is not interfered. And the system acquires the corresponding data value according to the parameter name without exporting all the data values in the database at one time, thereby avoiding data redundancy and improving the response speed of the system.
The present application provides yet another embodiment, which provides a computer-readable storage medium storing a program for exporting a spreadsheet file, the program for exporting a spreadsheet file being executable by at least one processor to cause the at least one processor to perform the steps of the method for exporting a spreadsheet file as described above.
In the embodiment, the parameter names can be dynamically modified when the system runs, and the execution of the database execution statements is not interfered. And the system acquires the corresponding data value according to the parameter name without exporting all the data values in the database at one time, thereby avoiding data redundancy and improving the response speed of the system.
The block chain is a novel application mode of computer technologies such as distributed data storage, point-to-point transmission, a consensus mechanism, an encryption algorithm and the like. A block chain (Blockchain), which is essentially a decentralized database, is a series of data blocks associated by using a cryptographic method, and each data block contains information of a batch of network transactions, so as to verify the validity (anti-counterfeiting) of the information and generate a next block. The blockchain may include a blockchain underlying platform, a platform product service layer, an application service layer, and the like.
Through the above description of the embodiments, those skilled in the art will clearly understand that the method of the above embodiments can be implemented by software plus a necessary general hardware platform, and certainly can also be implemented by hardware, but in many cases, the former is a better implementation manner. Based on such understanding, the technical solutions of the present application may be embodied in the form of a software product, which is stored in a storage medium (such as ROM/RAM, magnetic disk, optical disk) and includes instructions for enabling a terminal device (such as a mobile phone, a computer, a server, an air conditioner, or a network device) to execute the method according to the embodiments of the present application.
It is to be understood that the above-described embodiments are merely illustrative of some, but not restrictive, of the broad invention, and that the appended drawings illustrate preferred embodiments of the invention and do not limit the scope of the invention. This application is capable of embodiments in many different forms and is provided for the purpose of enabling a thorough understanding of the disclosure of the application. Although the present application has been described in detail with reference to the foregoing embodiments, it will be apparent to one skilled in the art that the present application may be practiced without modification or with equivalents of some of the features described in the foregoing embodiments. All equivalent structures made by using the contents of the specification and the drawings of the present application are directly or indirectly applied to other related technical fields and are within the protection scope of the present application.
Claims (10)
1. A method of exporting a spreadsheet file, comprising the steps of:
creating a query template, wherein the query template comprises a header, a title attribute, a structured query language to be executed, an electronic form name, query parameters and a data number, the header corresponds to the title attribute one by one, the structured query language to be executed comprises query conditions and execution statements, the query conditions comprise condition names, the query parameters comprise parameter names, and the condition names and the parameter names are mapped one by one;
when a query instruction is received, reading the electronic form name, the data number, the query parameter, the title attribute and a header corresponding to the title attribute in the query template;
replacing the corresponding condition name in the structured query language to be executed by the acquired parameter name according to the mapping relation between the parameter name and the condition name in the query parameter so as to complete the assembly of the structured query language to be executed;
executing the structured query language to be executed, and acquiring service data from a database; and
and generating and outputting the spreadsheet according to the obtained header, the service data, the spreadsheet name and the data number.
2. The method of exporting spreadsheet files according to claim 1, said step of reading the spreadsheet name, the number of data, the query parameter, the title attribute and the header corresponding to said title attribute in said query template comprises:
presetting one-to-one corresponding query codes for the parameter names of the query parameters;
if the received query instruction carries a query code, determining and acquiring a parameter name of a corresponding query parameter in the query template according to the query code;
if the received query instruction does not carry a preset query code, parameter names of all query parameters are obtained;
and reading the electronic form name, the data number, the title attribute and a header corresponding to the title attribute in the query template.
3. The method of exporting a spreadsheet file according to claim 1, after said step of creating a query template, further comprising:
encrypting the query template through a preset key to generate a ciphertext, and storing the ciphertext into a database;
when receiving a query instruction, the step of reading the spreadsheet name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template comprises the following steps:
when an inquiry instruction carrying a secret key is received, the secret key carried by the inquiry instruction is obtained, and the ciphertext is decrypted through the secret key to obtain the inquiry template;
and reading the electronic form name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template.
4. The method of exporting a spreadsheet file according to claim 1, after said step of mapping said condition names to said parameter names one to one, further comprising:
and setting the query condition in the query template in a placeholder mode.
5. The method for exporting a spreadsheet file according to any of the claims 1 to 4, wherein said step of executing said structured query language to be executed to obtain business data from a database comprises:
acquiring the data number of the query template, and determining the quantity of business data acquired from a database according to the structured query language to be executed;
comparing the amount of the service data with the data number;
if the quantity of the business data is less than or equal to the quantity of the data, executing the structured query language to be executed, and acquiring the business data from a database;
and if the quantity of the business data is greater than the data quantity, executing the structured query language to be executed, and acquiring the business data from the database for multiple times, wherein the quantity of the business data acquired each time is less than or equal to the data quantity.
6. The method for exporting a spreadsheet file according to any of the claims 1 to 4, wherein said step of executing said structured query language to be executed to obtain business data from a database comprises:
acquiring the data number of the query template, and determining the quantity of business data acquired from a database according to the structured query language to be executed;
comparing the amount of the service data with the data number;
if the quantity of the service data is larger than the data quantity, identifying grade marks carried out on the parameter names in advance, wherein the grade marks at least comprise a first grade and a second grade;
deleting and backing up the parameter names marked at other levels except the first level, and sending the deleted parameter names to appointed personnel through a mail;
and replacing all parameter names in the structured query language to be executed with the parameter names marked by the primary grade, executing the structured query language to be executed, and acquiring service data from a database.
7. An apparatus for exporting a spreadsheet file, comprising:
the system comprises a creating module, a searching module and a processing module, wherein the creating module is used for creating a query template, the query template comprises a header, a title attribute, a structured query language to be executed, an electronic form name, query parameters and a data number, the header corresponds to the title attribute one by one, the structured query language to be executed comprises query conditions and execution statements, the query conditions comprise condition names, the query parameters comprise parameter names, and the condition names and the parameter names are mapped one by one;
the reading module is used for reading the electronic form name, the data number, the query parameter, the title attribute and the header corresponding to the title attribute in the query template when a query instruction is received;
the replacing module is used for replacing the corresponding condition name in the structured query language to be executed by using the acquired parameter name according to the mapping relation between the parameter name and the condition name in the query parameter so as to finish the assembly of the structured query language to be executed;
the execution module is used for executing the structured query language to be executed and acquiring service data from a database; and
and the generating module is used for generating and outputting the spreadsheet according to the acquired header, the service data, the spreadsheet name and the data number.
8. The method of exporting a spreadsheet file according to claim 7, wherein said reading module comprises a setting sub-module, an obtaining sub-module and a reading sub-module;
the setting submodule is used for setting one-to-one corresponding query codes for the parameter names of the query parameters in advance;
the acquisition sub-module is used for determining and acquiring the parameter names of the corresponding query parameters in the query template according to the query codes when the received query instruction carries the query codes, and acquiring the parameter names of all the query parameters when the received query instruction does not carry the preset query codes;
the reading sub-module is used for reading the electronic form name, the data number, the title attribute and the header corresponding to the title attribute in the query template.
9. A computer device, characterized by comprising a memory in which a computer program is stored and a processor which, when executing said computer program, carries out the steps of the method of exporting a spreadsheet file according to any of the claims 1 to 6.
10. A computer-readable storage medium, having stored thereon a computer program which, when being executed by a processor, carries out the steps of the method of exporting a spreadsheet file according to anyone of the claims 1 to 6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010348340.9A CN111680477A (en) | 2020-04-28 | 2020-04-28 | Method and device for exporting spreadsheet file, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010348340.9A CN111680477A (en) | 2020-04-28 | 2020-04-28 | Method and device for exporting spreadsheet file, computer equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111680477A true CN111680477A (en) | 2020-09-18 |
Family
ID=72452215
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010348340.9A Pending CN111680477A (en) | 2020-04-28 | 2020-04-28 | Method and device for exporting spreadsheet file, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111680477A (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112182107A (en) * | 2020-09-29 | 2021-01-05 | 中国平安财产保险股份有限公司 | Method and device for acquiring list data, computer equipment and storage medium |
| CN112949275A (en) * | 2021-03-12 | 2021-06-11 | 中国平安财产保险股份有限公司 | Method, device, equipment and storage medium for importing electronic forms into database |
| CN112965982A (en) * | 2021-03-16 | 2021-06-15 | 中国平安财产保险股份有限公司 | Table processing method, device, equipment and storage medium |
| CN116756779A (en) * | 2023-08-17 | 2023-09-15 | 青岛诺亚信息技术有限公司 | Electronic form data objectification storage system and method |
-
2020
- 2020-04-28 CN CN202010348340.9A patent/CN111680477A/en active Pending
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112182107A (en) * | 2020-09-29 | 2021-01-05 | 中国平安财产保险股份有限公司 | Method and device for acquiring list data, computer equipment and storage medium |
| CN112182107B (en) * | 2020-09-29 | 2023-11-03 | 中国平安财产保险股份有限公司 | List data acquisition method, device, computer equipment and storage medium |
| CN112949275A (en) * | 2021-03-12 | 2021-06-11 | 中国平安财产保险股份有限公司 | Method, device, equipment and storage medium for importing electronic forms into database |
| CN112965982A (en) * | 2021-03-16 | 2021-06-15 | 中国平安财产保险股份有限公司 | Table processing method, device, equipment and storage medium |
| CN112965982B (en) * | 2021-03-16 | 2023-03-21 | 中国平安财产保险股份有限公司 | Table processing method, device, equipment and storage medium |
| CN116756779A (en) * | 2023-08-17 | 2023-09-15 | 青岛诺亚信息技术有限公司 | Electronic form data objectification storage system and method |
| CN116756779B (en) * | 2023-08-17 | 2023-11-17 | 青岛诺亚信息技术有限公司 | Electronic form data objectification storage system and method |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111680477A (en) | Method and device for exporting spreadsheet file, computer equipment and storage medium | |
| CN111814179A (en) | User authority management and data control method and related equipment thereof | |
| CN111914135A (en) | Data query method and device, electronic equipment and storage medium | |
| WO2022095518A1 (en) | Automatic interface test method and apparatus, and computer device and storage medium | |
| CN112199442A (en) | Distributed batch file downloading method and device, computer equipment and storage medium | |
| CN115017107A (en) | Data retrieval method and device based on privacy protection, computer equipment and medium | |
| CN112182004B (en) | Method, device, computer equipment and storage medium for checking data in real time | |
| CN115757495A (en) | Cache data processing method and device, computer equipment and storage medium | |
| CN113254445A (en) | Real-time data storage method and device, computer equipment and storage medium | |
| CN112468409A (en) | Access control method, device, computer equipment and storage medium | |
| CN113377372A (en) | Business rule analysis method and device, computer equipment and storage medium | |
| CN112363997B (en) | Data version management method, device and storage medium | |
| CN112436943B (en) | Request deduplication method, device, equipment and storage medium based on big data | |
| CN112416934A (en) | hive table incremental data synchronization method and device, computer equipment and storage medium | |
| CN113434906A (en) | Data query method and device, computer equipment and storage medium | |
| CN113010542A (en) | Service data processing method and device, computer equipment and storage medium | |
| CN112286815A (en) | Interface test script generation method and related equipment thereof | |
| CN116956326A (en) | Authority data processing method and device, computer equipment and storage medium | |
| CN116842012A (en) | Method, device, equipment and storage medium for storing Redis cluster in fragments | |
| CN113626438B (en) | Data table management method, device, computer equipment and storage medium | |
| CN113283759B (en) | Account risk portrait updating method, device, equipment and storage medium | |
| CN115543428A (en) | Simulated data generation method and device based on strategy template | |
| CN115203672A (en) | Information access control method and device, computer equipment and medium | |
| CN112416875A (en) | Log management method and device, computer equipment and storage medium | |
| CN113554315A (en) | Service data tracking method and device, computer equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |