CN111240688A - excel file analysis method and device, computer equipment and storage medium - Google Patents
excel file analysis method and device, computer equipment and storage medium Download PDFInfo
- Publication number
- CN111240688A CN111240688A CN202010026250.8A CN202010026250A CN111240688A CN 111240688 A CN111240688 A CN 111240688A CN 202010026250 A CN202010026250 A CN 202010026250A CN 111240688 A CN111240688 A CN 111240688A
- Authority
- CN
- China
- Prior art keywords
- excel file
- preset
- worksheet
- workbook
- format
- 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.)
- Granted
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/42—Syntactic analysis
- G06F8/427—Parsing
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Abstract
The invention discloses an excel file analysis method, an excel file analysis device, computer equipment and a storage medium, wherein the method comprises the following steps: if an excel file input by a user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook; acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule; analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file; and storing the object list data into a preset database. By the method, unnecessary repeated data processing work is reduced, development efficiency is greatly improved, and cost is saved.
Description
Technical Field
The invention relates to the technical field of computers, in particular to an excel file analysis method and device, computer equipment and a storage medium.
Background
The import and export functions of Excel files are very common, and in the prior art, the import and export of Excel files are usually realized by using an Apache POI technology, which is an open source toolkit provided by Apache organization and used for importing and converting operations aiming at microsoft Office (mainly aiming at Excel). But the steps of processing excel files with more complicated table styles are more complicated by the Apache POI. Although the Easyexcel technology provided by the Alibama can be realized by directly importing an Easyexcel toolkit when processing the excel file, inheriting the corresponding class and adding a label on the corresponding field, the stability of the Easyexcel technology is not good as that of the Apache POI, and the Easyexcel technology can be used only by carrying out a large number of tests and other operations, so that a large amount of manpower and material resources are required.
Disclosure of Invention
The embodiment of the invention provides an excel file analyzing method, an excel file analyzing system, computer equipment and a storage medium, and aims to solve the problem that the efficiency of analyzing an excel file by adopting an Apache POI technology in the prior art is not high.
In a first aspect, an embodiment of the present invention provides an analysis method for an excel file, including:
if an excel file input by a user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook;
acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule;
analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file;
and storing the object list data into a preset database.
In a second aspect, an embodiment of the present invention provides an apparatus for parsing an excel file, including:
the workbook generating unit is used for receiving an excel file input by a user and analyzing the excel file according to a preset first analysis rule to generate a workbook;
the worksheet acquiring unit is used for acquiring the worksheet of the excel file from the workbook according to a preset acquiring rule;
the generating unit is used for analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file;
and the storage unit is used for storing the object list data into a preset database.
In a third aspect, an embodiment of the present invention further provides a computer device, which includes a memory, a processor, and a computer program stored on the memory and executable on the processor, where the processor implements the method for parsing an excel file according to the first aspect when executing the computer program.
In a fourth aspect, an embodiment of the present invention further provides a storage medium, where the storage medium stores a computer program, and the computer program, when executed by a processor, causes the processor to execute the method for parsing an excel file according to the first aspect.
The embodiment of the invention provides an excel file analysis method, an excel file analysis device, computer equipment and a storage medium, wherein the method comprises the following steps: if an excel file input by a user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook; acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule; analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file; and storing the object list data into a preset database. By the method, unnecessary repeated data processing work is reduced, development efficiency is greatly improved, and cost is saved.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings needed to be used in the description of the embodiments are briefly introduced below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and it is obvious for those skilled in the art to obtain other drawings based on these drawings without creative efforts.
Fig. 1 is a schematic flow chart of an analysis method of an excel file according to an embodiment of the present invention;
FIG. 2 is a schematic sub-flow diagram of an analysis method of an excel file according to an embodiment of the present invention;
FIG. 3 is another schematic sub-flow diagram of an excel file parsing method according to an embodiment of the present invention;
FIG. 4 is another schematic sub-flow diagram of an analysis method of an excel file according to an embodiment of the present invention;
fig. 5 is a schematic block diagram of an analysis apparatus for an excel file according to an embodiment of the present invention;
FIG. 6 is a schematic block diagram of a computer device provided by an embodiment of the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some, not all, embodiments of the present invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
It will be understood that the terms "comprises" and/or "comprising," when used in this specification and the appended claims, specify the presence of stated features, integers, steps, operations, elements, and/or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and/or groups thereof.
It is also to be understood that the terminology used in the description of the invention herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. As used in the specification of the present invention and the appended claims, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise.
It should be further understood that the term "and/or" as used in this specification and the appended claims refers to and includes any and all possible combinations of one or more of the associated listed items.
Referring to fig. 1, fig. 1 is a schematic flow chart illustrating an analysis method of an excel file according to an embodiment of the present invention. The method for analyzing the excel file is applied to a user terminal, and is executed through application software installed in the user terminal, wherein the user terminal is terminal equipment for executing the method for analyzing the excel file so as to analyze the excel file, such as a desktop computer, a notebook computer, a tablet computer or a mobile phone. In the embodiment of the invention, Apache POI toolkit is configured in advance for terminal equipment for analyzing the excel file, for example, a java item for analyzing the excel file is established in a java development tool, a preset Apache POI toolkit is imported into the java item to analyze the excel file, and a plurality of tools in the Apache POI toolkit are called to realize the analysis of the excel file.
As shown in fig. 1, the method includes steps S110 to S140.
S110, if the excel file input by the user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook.
And if the excel file input by the user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook. Specifically, the first parsing rule is used for parsing the excel file into a workbook. For example, when the excel file input by the user is in the xls format, the excel file can be analyzed through the first analysis rule to generate the workbook, and when the excel file input by the user is in the xlsx format, the excel file can still be analyzed through the first analysis rule to generate the workbook.
In an embodiment, as shown in fig. 2, step S110 includes sub-steps S111, S112 and S113.
And S111, judging whether the format of the excel file conforms to a preset format.
And judging whether the format of the excel file conforms to a preset format or not. Specifically, the preset format is a format set for an excel file to be analyzed, and the excel file can be subsequently analyzed only if the excel file conforms to the preset format. The format of the excel file can be determined according to a suffix name of the excel file, the suffix name of the excel file usually includes xls and xlsx, and the preset format can be set to be xls format or xlsx format.
And S112, if the format of the excel file does not conform to the preset format, converting the excel file into the excel file with the preset format according to a preset format conversion tool.
And if the format of the excel file does not conform to the preset format, converting the excel file into the excel file with the preset format according to a preset format conversion tool. Specifically, the format conversion tool is a tool used for converting the format of the excel file which does not conform to the preset format in the Apache POI kit to conform to the preset format. For example, if the predetermined format is xlsx format and the excel file is xls format file, the format of the excel file does not conform to the predetermined format, and the excel file can be converted into xlsx format file by using a format conversion tool.
S113, analyzing the excel file in the preset format according to a preset analysis tool to generate the workbook.
And analyzing the excel file with the preset format according to a preset analysis tool to generate the workbook. Specifically, the analysis tool is a tool used for analyzing the excel file conforming to the preset format in the Apache POI tool kit to generate the workbook. Obtaining a workbook from an excel file in a preset format so as to obtain a worksheet of the excel file from the workbook.
And S120, acquiring the worksheet of the excel file from the workbook according to a preset acquisition rule.
And acquiring the worksheet of the excel file from the workbook according to a preset acquisition rule. Specifically, the obtaining rule is used for obtaining a worksheet in an excel file corresponding to a workbook from the workbook. At least one worksheet is usually included in a workbook, and when a plurality of worksheets are included in the workbook, only the worksheets including the data to be processed need to be obtained from the workbook.
In an embodiment, as shown in fig. 3, step S120 includes substeps S121 and S122.
And S121, processing the workbook according to a preset processing tool to obtain a processed workbook.
And processing the workbook according to a preset processing tool to obtain a processed workbook. Specifically, the preset processing tool is a workbook in an Apache POI toolkit, which is used for processing a workbook to obtain a processed workbook, and since the workbook usually includes at least one worksheet, when a user inputs data into the workbook, the user inputs data to be input into one or more worksheets in the workbook according to the user's own habits and data amount, and the processing tool is used for sorting one or more worksheets existing in the workbook so that only one worksheet exists in the workbook. For example, when there are three worksheets in the workbook, only one of which stores data to be processed, the worksheets in the workbook that do not contain data are deleted by the processing tool; when three worksheets exist in the workbook and data to be processed exists in the three worksheets, the three worksheets in the workbook can be merged through the processing tool to obtain the workbook with only one worksheet; when three worksheets exist in the workbook and only two of the three worksheets have data to be processed, the worksheets not containing the data to be processed can be deleted through the processing tool, and the other two worksheets are combined to obtain the workbook containing only one worksheet.
And S122, extracting the processed workbook according to a preset extraction tool to obtain the worksheet of the excel file.
And extracting the processed workbook according to a preset extraction tool to obtain the worksheet of the excel file. Specifically, the preset extraction tool is used for extracting the processed workbook to obtain a worksheet containing data in the Apache POI kit, and the extraction tool can sequentially extract each line of data stored in the processed workbook when the worksheet of the processed workbook is extracted, or extract all the data stored in the processed workbook at one time.
S130, analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file.
And analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file. Specifically, the second parsing rule is used for parsing the generated worksheet to obtain data of the excel file submitted by the user. And receiving the generic parameters of the data in the excel file and the parameters of the relationship between the subscripts of the cells and the object variables in the excel file through the second analysis rule, wherein the generic parameters of the data in the excel file can instantiate the data in the excel file, and the parameter of the relationship between the cells and the object variables in the excel file can obtain the comparison table of the variable names and the subscripts in the excel file.
In an embodiment, as shown in fig. 4, step S130 includes sub-steps S131, S132, S133, S134, and S135.
S131, instantiating the worksheet according to the preset first parameters to generate an instantiated worksheet.
And instantiating the worksheet according to the preset first parameters to generate an instantiated worksheet. Specifically, the preset first parameter is used for instantiating a worksheet, that is, the worksheet is initialized to obtain the category of each line of data in the worksheet, and the variable name corresponding to each line of data in the worksheet is marked by using a corresponding label. In this embodiment, the first parameter is a generic parameter of the excel file data, and the worksheet generates an instantiated worksheet according to the generic parameter of the excel file data.
And S132, performing circulation processing on the worksheet according to a preset circulation rule to obtain data of each line of the worksheet.
And circularly processing the worksheet according to a preset circulation rule to obtain each line of the worksheet. Specifically, existing loop rules may be adopted to loop the worksheet to obtain data of each line of the worksheet, that is, to loop the data in the worksheet to obtain data of one line in the worksheet, and then to continue to loop the worksheet to obtain data of the next line, so as to finally obtain data of each line in the worksheet.
S133, inquiring data of each line of the worksheet according to a preset second parameter to obtain each cell data corresponding to each line in the worksheet, and obtaining a variable name of each line according to a preset comparison table.
And inquiring the data of each row of the worksheet according to a preset second parameter to obtain each cell data corresponding to each row in the worksheet and obtain the variable name of each row according to a preset comparison table. Specifically, after each line of data is circularly obtained by the worksheet according to the circulation rule, the data in each cell corresponding to each line in the worksheet is obtained by querying through the second parameter, and after the data is obtained, the variable name corresponding to each line in the worksheet can be obtained through a preset comparison table.
In another embodiment, the look-up table is generated according to the second parameter, wherein the look-up table contains the index of the cell in the working table and the name of the variable in each row.
Specifically, in this embodiment, the comparison table is a comparison table of the cell subscript and the variable name in the excel, the second parameter is a parameter related to the cell subscript and the object variable in the excel, and the comparison table is obtained through the parameter related to the cell subscript and the object variable in the excel.
And S134, mapping the variable name of each line to the variable of the instantiated worksheet to replace the variable name of the instantiated worksheet based on the java reflection mechanism.
And mapping the variable name of each line onto the variable of the instantiated worksheet to replace the variable name of the instantiated worksheet by the java-based reflection mechanism. Specifically, the variable name of each row acquired from the comparison table is mapped to the variable of each row in the instantiated worksheet through a Field method carried by a java reflection mechanism, so that the variable name in the instantiated worksheet is replaced.
And S135, assigning the cell data to the variables of the instantiated worksheet to obtain the object list data of the excel file.
And S140, storing the object list data into a preset database.
And storing the object list data into a preset database. Specifically, the worksheet is processed according to the second parsing rule to generate object list data of the excel file, the object list data of the excel file is the processed excel file data and can be stored in a preset database, and the preset database is a database used for storing the processed excel file data.
The embodiment of the invention provides an excel file analysis method, which comprises the steps of receiving an excel file input by a user, and analyzing the excel file according to a preset first analysis rule to generate a workbook; acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule; analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file; and storing the object list data into a preset database. By the method, unnecessary repeated data processing work is reduced, development efficiency is greatly improved, and cost is saved.
The embodiment of the invention also provides an analysis device of the excel file, and the analysis of the excel file is used in any embodiment of the analysis method of the excel file. Specifically, referring to fig. 5, fig. 5 is a schematic block diagram of an excel file parsing apparatus according to an embodiment of the present invention. The excel file parsing device can be configured in a user terminal.
As shown in fig. 5, the analysis apparatus of an excel file includes a workbook generation unit 110, a worksheet acquisition unit 120, a generation unit 130, and a saving unit 140.
The workbook generating unit 110 is configured to, if the workbook generating unit is used for receiving an excel file input by a user, analyze the excel file according to a preset first analysis rule to generate a workbook.
And if the excel file input by the user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook. Specifically, the first parsing rule is used for parsing the excel file into a workbook.
In another embodiment, the workbook generating unit 110 includes a format determining unit 111, a format converting unit 112, and a parsing unit 113.
And the format judging unit 111 is used for judging whether the format of the excel file conforms to a preset format.
And judging whether the format of the excel file conforms to a preset format or not. Specifically, the preset format is a format set for an excel file to be analyzed, and the excel file can be subsequently analyzed only if the excel file conforms to the preset format. The format of the excel file can be determined according to a suffix name of the excel file, the suffix name of the excel file usually includes xls and xlsx, and the preset format can be set to be xls format or xlsx format.
And a format conversion unit 112, configured to, if the format of the excel file does not conform to the preset format, convert the excel file into an excel file in a preset format according to a preset format conversion tool.
And if the format of the excel file does not conform to the preset format, converting the excel file into the excel file with the preset format according to a preset format conversion tool. Specifically, the format conversion tool is a tool used for converting the format of the excel file which does not conform to the preset format in the Apache POI kit to conform to the preset format.
The parsing unit 113 is configured to parse the excel file in the preset format according to a preset parsing tool to generate the workbook.
And analyzing the excel file with the preset format according to a preset analysis tool to generate the workbook. Specifically, the analysis tool is a tool used for analyzing the excel file conforming to the preset format in the Apache POI tool kit to generate the workbook. Obtaining a workbook from an excel file in a preset format so as to obtain a worksheet of the excel file from the workbook.
And a worksheet acquiring unit 120, configured to acquire a worksheet of the excel file from the workbook according to a preset acquisition rule.
And acquiring the worksheet of the excel file from the workbook according to a preset acquisition rule. Specifically, the obtaining rule is used for obtaining a worksheet in an excel file corresponding to a workbook from the workbook. At least one worksheet is usually included in a workbook, and when a plurality of worksheets are included in the workbook, only the worksheets including the data to be processed need to be obtained from the workbook.
In another embodiment, the worksheet acquiring unit 120 includes a workbook processing unit 121 and a worksheet extracting unit 122.
A workbook processing unit 121, configured to process the workbook according to a preset processing tool to obtain a processed workbook.
And processing the workbook according to a preset processing tool to obtain a processed workbook. Specifically, the preset processing tool is a workbook in an Apache POI toolkit, which is used for processing a workbook to obtain a processed workbook, and since the workbook usually includes at least one worksheet, when a user inputs data into the workbook, the user inputs data to be input into one or more worksheets in the workbook according to the user's own habits and data amount, and the processing tool is used for sorting one or more worksheets existing in the workbook so that only one worksheet exists in the workbook.
And the worksheet extracting unit 122 is configured to extract the processed workbook according to a preset extraction tool to obtain a worksheet of the excel file.
And extracting the processed workbook according to a preset extraction tool to obtain the worksheet of the excel file. Specifically, the preset extraction tool is used for extracting the processed workbook to obtain a worksheet containing data in the Apache POI kit, and the extraction tool can sequentially extract each line of data stored in the processed workbook when the worksheet of the processed workbook is extracted, or extract all the data stored in the processed workbook at one time.
The generating unit 130 is configured to analyze the worksheet according to a preset second analysis rule to generate object list data of the excel file.
And analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file. Specifically, the second parsing rule is used for parsing the generated worksheet to obtain data of the excel file submitted by the user. And receiving the generic parameters of the data in the excel file and the parameters of the relationship between the subscripts of the cells and the object variables in the excel file through the second analysis rule, wherein the generic parameters of the data in the excel file can instantiate the data in the excel file, and the parameter of the relationship between the cells and the object variables in the excel file can obtain the comparison table of the variable names and the subscripts in the excel file.
In another embodiment, the generating unit 130 comprises an instantiating unit 131, a looping unit 132, an obtaining unit 133, a mapping unit 134 and an assigning unit 135.
An instantiation unit 131, configured to instantiate the worksheet according to a preset first parameter to generate an instantiated worksheet.
And instantiating the worksheet according to the preset first parameters to generate an instantiated worksheet. Specifically, the preset first parameter is used for instantiating a worksheet, that is, the worksheet is initialized to obtain the category of each line of data in the worksheet, and the variable name corresponding to each line of data in the worksheet is marked by using a corresponding label. In this embodiment, the first parameter is a generic parameter of the excel file data, and the worksheet generates an instantiated worksheet according to the generic parameter of the excel file data.
And a circulation unit 132, configured to perform circulation processing on the worksheet according to a preset circulation rule to obtain data of each row of the worksheet.
And circularly processing the worksheet according to a preset circulation rule to obtain each line of the worksheet. Specifically, existing loop rules may be adopted to loop the worksheet to obtain data of each line of the worksheet, that is, to loop the data in the worksheet to obtain data of one line in the worksheet, and then to continue to loop the worksheet to obtain data of the next line, so as to finally obtain data of each line in the worksheet.
The obtaining unit 133 is configured to query data of each line of the worksheet according to a preset second parameter to obtain cell data corresponding to each line in the worksheet, and obtain a variable name of each line according to a preset comparison table.
And inquiring the data of each row of the worksheet according to a preset second parameter to obtain the cell data corresponding to each row in the worksheet and obtain the variable name of each row according to a preset comparison table. Specifically, after each line of data is circularly obtained by the worksheet according to the circulation rule, the data in the cells corresponding to each line in the worksheet is obtained by querying through the second parameter, and after the data is obtained, the variable name corresponding to each line in the worksheet can be obtained through a preset comparison table.
A mapping unit 134, configured to map the variable name of each row onto the variable of the instantiated worksheet to replace the variable name of the instantiated worksheet based on the reflection mechanism of java.
And mapping the variable name of each line onto the variable of the instantiated worksheet to replace the variable name of the instantiated worksheet by the java-based reflection mechanism. Specifically, the variable name of each row acquired from the comparison table is mapped to the variable of each row in the instantiated worksheet through a Field method carried by a java reflection mechanism, so that the variable name in the instantiated worksheet is replaced.
And the assigning unit 135 is used for assigning the cell data to the variables of the instantiated worksheet to obtain the object list data of the excel file.
In other embodiments of the present invention, the generating unit 130 further includes a comparison table generating unit 136.
And a look-up table generating unit 136, configured to generate the look-up table according to the second parameter, where the look-up table includes the index of the cell in the working table and the name of the variable in each row.
And generating the comparison table according to the second parameter, wherein the comparison table comprises the subscripts of the cells in the working table and the variable names of each row. Specifically, in this embodiment, the comparison table is a comparison table of the cell subscript and the variable name in the excel, the second parameter is a parameter related to the cell subscript and the object variable in the excel, and the comparison table is obtained through the parameter related to the cell subscript and the object variable in the excel.
A saving unit 140, configured to save the object list data into a preset database.
And storing the object list data into a preset database. Specifically, the worksheet is processed according to the second parsing rule to generate object list data of the excel file, the object list data of the excel file is the processed excel file data and can be stored in a preset database, and the preset database is a database used for storing the processed excel file data.
The analysis device of the excel file is used for executing the analysis method of the excel file, if the excel file input by a user is received, the excel file is analyzed according to a preset first analysis rule to generate a workbook; acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule; analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file; and storing the object list data into a preset database. By the method, unnecessary repeated data processing work is reduced, development efficiency is greatly improved, and cost is saved.
Referring to fig. 6, fig. 6 is a schematic block diagram of a computer device according to an embodiment of the present invention.
Referring to fig. 6, the device 500 includes a processor 502, memory, and a network interface 505 connected by a system bus 501, where the memory may include a non-volatile storage medium 503 and an internal memory 504.
The non-volatile storage medium 503 may store an operating system 5031 and a computer program 5032. The computer program 5032, when executed, causes the processor 502 to perform a method for parsing an excel file.
The processor 502 is used to provide computing and control capabilities that support the operation of the overall device 500.
The internal memory 504 provides an environment for running the computer program 5032 in the non-volatile storage medium 503, and when the computer program 5032 is executed by the processor 502, the processor 502 can be caused to execute the excel file parsing method.
The network interface 505 is used for network communication, such as providing transmission of data information. Those skilled in the art will appreciate that the configuration shown in fig. 6 is a block diagram of only a portion of the configuration associated with aspects of the present invention and does not constitute a limitation of the apparatus 500 to which aspects of the present invention may be applied, and that a particular apparatus 500 may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
Wherein the processor 502 is configured to run the computer program 5032 stored in the memory to implement the following functions: if an excel file input by a user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook; acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule; analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file; and storing the object list data into a preset database.
Those skilled in the art will appreciate that the embodiment of the apparatus 500 shown in fig. 6 does not constitute a limitation on the specific construction of the apparatus 500, and in other embodiments, the apparatus 500 may include more or fewer components than shown, or some components may be combined, or a different arrangement of components. For example, in some embodiments, the apparatus 500 may only include the memory and the processor 502, and in such embodiments, the structure and function of the memory and the processor 502 are the same as those of the embodiment shown in fig. 6 and are not repeated herein.
It should be understood that in the present embodiment, the Processor 502 may be a Central Processing Unit (CPU), and the Processor 502 may also be other general-purpose processors 502, a Digital Signal Processor 502 (DSP), an Application Specific Integrated Circuit (ASIC), an off-the-shelf Programmable Gate Array (FPGA) or other Programmable logic device, discrete Gate or transistor logic device, discrete hardware component, etc. The general-purpose processor 502 may be a microprocessor 502 or the processor 502 may be any conventional processor 502 or the like.
In another embodiment of the present invention, a computer storage medium is provided. The storage medium may be a non-volatile computer-readable storage medium. The storage medium stores a computer program 5032, wherein the computer program 5032 when executed by the processor 502 performs the steps of: if an excel file input by a user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook; acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule; analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file; and storing the object list data into a preset database.
It is clear to those skilled in the art that, for convenience and brevity of description, the specific working processes of the above-described apparatuses, devices and units may refer to the corresponding processes in the foregoing method embodiments, and are not described herein again. Those of ordinary skill in the art will appreciate that the elements and algorithm steps of the examples described in connection with the embodiments disclosed herein may be embodied in electronic hardware, computer software, or combinations of both, and that the components and steps of the examples have been described in a functional general in the foregoing description for the purpose of illustrating clearly the interchangeability of hardware and software. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the implementation. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present invention.
In the embodiments provided by the present invention, it should be understood that the disclosed apparatus, device and method can be implemented in other ways. For example, the above-described embodiments of the apparatus are merely illustrative, and for example, the division of the units is only a logical division, and there may be other divisions when the actual implementation is performed, or units having the same function may be grouped into one unit, for example, a plurality of units or components may be combined or may be integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may also be an electric, mechanical or other form of connection.
The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment of the present invention.
In addition, functional units in the embodiments of the present invention may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit can be realized in a form of hardware, and can also be realized in a form of a software functional unit.
The integrated unit, if implemented in the form of a software functional unit and sold or used as a stand-alone product, may be stored in a storage medium. Based on such understanding, the technical solution of the present invention essentially contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product stored in a storage medium and including instructions for causing a device 500 (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present invention. And the aforementioned storage medium includes: various media capable of storing program codes, such as a usb disk, a removable hard disk, a Read-Only Memory (ROM), a magnetic disk, or an optical disk.
While the invention has been described with reference to specific embodiments, the invention is not limited thereto, and various equivalent modifications and substitutions can be easily made by those skilled in the art within the technical scope of the invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.
Claims (10)
1. An excel file analysis method is characterized by comprising the following steps:
if an excel file input by a user is received, analyzing the excel file according to a preset first analysis rule to generate a workbook;
acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule;
analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file;
and storing the object list data into a preset database.
2. The method for analyzing the excel file according to claim 1, wherein the analyzing the excel file according to a preset first analysis rule to generate a workbook comprises:
judging whether the format of the excel file conforms to a preset format or not;
if the format of the excel file does not conform to the preset format, converting the excel file into an excel file with a preset format according to a preset format conversion tool;
and analyzing the excel file with the preset format according to a preset analysis tool to generate the workbook.
3. The method for parsing an excel file according to claim 1, wherein the retrieving the worksheet of the excel file from the workbook according to a preset retrieval rule comprises:
processing the workbook according to a preset processing tool to obtain a processed workbook;
and extracting the processed workbook according to a preset extraction tool to obtain the worksheet of the excel file.
4. The method for parsing an excel file according to claim 1, wherein the parsing the worksheet according to a preset second parsing rule to generate object list data of the excel file comprises:
instantiating the worksheet according to a preset first parameter to generate an instantiated worksheet;
performing cyclic processing on the worksheet according to a preset cyclic rule to obtain data of each line of the worksheet;
inquiring data of each row of the worksheet according to a preset second parameter to obtain each cell data corresponding to each row in the worksheet and obtain a variable name of each row according to a preset comparison table;
mapping the variable name of each line onto the variable of the instantiated worksheet to replace the variable name of the instantiated worksheet based on the java reflection mechanism;
and assigning the cell data to variables of the instantiated worksheet to obtain the object list data of the excel file.
5. The method for parsing an excel file according to claim 4, wherein before the querying each row of the worksheet according to the preset second parameter to obtain the cell data corresponding to each row in the worksheet and obtaining the variable name of each row according to the preset comparison table, the method further comprises:
and generating the comparison table according to the second parameter, wherein the comparison table comprises the subscripts of the cells in the working table and the variable names of each row.
6. An analysis device for an excel file is characterized by comprising:
the workbook generating unit is used for analyzing the excel file according to a preset first analysis rule to generate the workbook if the excel file input by a user is received;
the worksheet acquiring unit is used for acquiring the worksheet of the excel file from the workbook according to a preset acquiring rule;
the generating unit is used for analyzing the worksheet according to a preset second analysis rule to generate object list data of the excel file;
and the storage unit is used for storing the object list data into a preset database.
7. The apparatus for parsing an excel file according to claim 6, wherein the workbook generating unit comprises:
the format judging unit is used for judging whether the format of the excel file conforms to a preset format or not;
the format conversion unit is used for converting the excel file into an excel file with a preset format according to a preset format conversion tool if the format of the excel file does not conform to the preset format;
and the analysis unit is used for analyzing the excel file with the preset format according to a preset analysis tool so as to generate the workbook.
8. The apparatus for parsing an excel file according to claim 6, wherein the worksheet retrieving unit comprises:
the workbook processing unit is used for processing the workbook according to a preset processing tool to obtain a processed workbook;
and the worksheet extraction unit is used for extracting the processed workbook according to a preset extraction tool so as to obtain the worksheet of the excel file.
9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the method of parsing an excel file according to any one of claims 1 to 5 when executing the computer program.
10. A computer-readable storage medium, characterized in that the computer-readable storage medium stores a computer program which, when executed by a processor, causes the processor to execute the method of parsing an excel file according to any one of claims 1 to 5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010026250.8A CN111240688B (en) | 2020-01-10 | 2020-01-10 | excel file analysis method and device, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010026250.8A CN111240688B (en) | 2020-01-10 | 2020-01-10 | excel file analysis method and device, computer equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111240688A true CN111240688A (en) | 2020-06-05 |
| CN111240688B CN111240688B (en) | 2023-08-18 |
Family
ID=70870357
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010026250.8A Active CN111240688B (en) | 2020-01-10 | 2020-01-10 | excel file analysis method and device, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111240688B (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111859205A (en) * | 2020-07-14 | 2020-10-30 | 上海悦易网络信息技术有限公司 | Analytic method and equipment for batch processing tasks |
| CN111931460A (en) * | 2020-07-02 | 2020-11-13 | 上海微亿智造科技有限公司 | Variable-row-supporting Excel file analysis method and system |
| CN112099801A (en) * | 2020-11-03 | 2020-12-18 | 广州市玄武无线科技股份有限公司 | Excel analysis method and system based on metadata driving |
| CN112988866A (en) * | 2021-03-01 | 2021-06-18 | 亿企赢网络科技有限公司 | Method and device for exporting excel file, electronic equipment and storage medium |
| CN113486097A (en) * | 2021-06-21 | 2021-10-08 | 上海百秋电子商务有限公司 | Big data export method, device, equipment and storage medium |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107168695A (en) * | 2017-04-18 | 2017-09-15 | 北京思特奇信息技术股份有限公司 | A kind of excel data analysis methods and system |
| CN107908396A (en) * | 2017-10-17 | 2018-04-13 | 广东广业开元科技有限公司 | A kind of chart data Online Processing System and method based on browser |
| CN109460219A (en) * | 2018-09-28 | 2019-03-12 | 西南电子技术研究所（中国电子科技集团公司第十研究所） | The method of rapid serial Interface Control File |
| US20190138282A1 (en) * | 2016-03-25 | 2019-05-09 | Pablo Daniel Palma Keller | System and methods for development of visual business applications |
| CN109783558A (en) * | 2018-12-28 | 2019-05-21 | 益萃网络科技（中国）有限公司 | Introduction method, device, computer equipment and the storage medium of Excel data |
| CN110134398A (en) * | 2018-02-02 | 2019-08-16 | 阿里巴巴集团控股有限公司 | Analytic method, system and the equipment of list data |
-
2020
- 2020-01-10 CN CN202010026250.8A patent/CN111240688B/en active Active
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20190138282A1 (en) * | 2016-03-25 | 2019-05-09 | Pablo Daniel Palma Keller | System and methods for development of visual business applications |
| CN107168695A (en) * | 2017-04-18 | 2017-09-15 | 北京思特奇信息技术股份有限公司 | A kind of excel data analysis methods and system |
| CN107908396A (en) * | 2017-10-17 | 2018-04-13 | 广东广业开元科技有限公司 | A kind of chart data Online Processing System and method based on browser |
| CN110134398A (en) * | 2018-02-02 | 2019-08-16 | 阿里巴巴集团控股有限公司 | Analytic method, system and the equipment of list data |
| CN109460219A (en) * | 2018-09-28 | 2019-03-12 | 西南电子技术研究所（中国电子科技集团公司第十研究所） | The method of rapid serial Interface Control File |
| CN109783558A (en) * | 2018-12-28 | 2019-05-21 | 益萃网络科技（中国）有限公司 | Introduction method, device, computer equipment and the storage medium of Excel data |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111931460A (en) * | 2020-07-02 | 2020-11-13 | 上海微亿智造科技有限公司 | Variable-row-supporting Excel file analysis method and system |
| CN111931460B (en) * | 2020-07-02 | 2021-09-10 | 上海微亿智造科技有限公司 | Variable-row-supporting Excel file analysis method and system |
| CN111859205A (en) * | 2020-07-14 | 2020-10-30 | 上海悦易网络信息技术有限公司 | Analytic method and equipment for batch processing tasks |
| CN112099801A (en) * | 2020-11-03 | 2020-12-18 | 广州市玄武无线科技股份有限公司 | Excel analysis method and system based on metadata driving |
| CN112988866A (en) * | 2021-03-01 | 2021-06-18 | 亿企赢网络科技有限公司 | Method and device for exporting excel file, electronic equipment and storage medium |
| CN113486097A (en) * | 2021-06-21 | 2021-10-08 | 上海百秋电子商务有限公司 | Big data export method, device, equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111240688B (en) | 2023-08-18 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111240688A (en) | excel file analysis method and device, computer equipment and storage medium | |
| US10191968B2 (en) | Automated data analysis | |
| CN111639473A (en) | Excel file analysis method and device based on java, computer equipment and storage medium | |
| WO2018210096A1 (en) | Rule engine-based rule configuration method, terminal and device, and storage medium | |
| CN109002424B (en) | File format conversion method and device, computer equipment and storage medium | |
| CN112800095A (en) | Data processing method, device, equipment and storage medium | |
| CN111694572A (en) | Code format conversion method, device, computer equipment and storage medium | |
| CN113704307A (en) | Data query method, device, server and computer readable storage medium | |
| CN114090671A (en) | Data import method and device, electronic equipment and storage medium | |
| WO2019161620A1 (en) | Application dependency update method, terminal and device, and storage medium | |
| CN116244387A (en) | Entity relationship construction method, device, electronic equipment and storage medium | |
| CN113434582B (en) | Service data processing method and device, computer equipment and storage medium | |
| CN113360300B (en) | Interface call link generation method, device, equipment and readable storage medium | |
| CN112346775B (en) | Index data general processing method, electronic device and storage medium | |
| CN111782820A (en) | Knowledge graph creating method and device, readable storage medium and electronic equipment | |
| JP2017513252A (en) | Optimized data condenser and method | |
| CN111708957A (en) | Method, device and computer equipment for updating front-end page based on index and value index | |
| CN110852050A (en) | Method and device for configuring report generation system and electronic equipment | |
| CN113779362A (en) | Data searching method and device | |
| CN110471708B (en) | Method and device for acquiring configuration items based on reusable components | |
| CN112948264A (en) | Test task execution method, device, system, storage medium and electronic device | |
| CN110780970A (en) | Data screening method, device, equipment and computer readable storage medium | |
| CN110728118A (en) | Cross-data-platform data processing method, device, equipment and storage medium | |
| CN117240819B (en) | Mail configuration method, device, equipment and computer readable storage medium | |
| CN111144086B (en) | Log formatting method and device, electronic 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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |