CN111240688B - 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
- CN111240688B CN111240688B CN202010026250.8A CN202010026250A CN111240688B CN 111240688 B CN111240688 B CN 111240688B CN 202010026250 A CN202010026250 A CN 202010026250A CN 111240688 B CN111240688 B CN 111240688B
- Authority
- CN
- China
- Prior art keywords
- worksheet
- excel file
- preset
- workbook
- data
- 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.)
- Active
Links
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 present invention relates to the field of computer technologies, and in particular, to a method and apparatus for resolving an excel file, a computer device, and a storage medium.
Background
The function of importing and exporting an Excel file is very common, and in the prior art, an Apache POI technology is generally used for implementing the importing and exporting of the Excel file, and the Apache POI is an open source toolkit provided by the Apache organization and used for importing and converting microsoft Office (mainly used for Excel). However, the Apache POI has more complicated steps when processing an excel file with a more complex form. Although the Easyexcel technology provided by the alebab can directly import an Easyexcel tool package when processing the excel file, inherits the corresponding class, and can be implemented by tagging the corresponding field, the stability is inferior to that of an Apache POI, and the Easyexcel can be used only by performing a large number of operations such as testing, and a large amount of manpower and material resources are required.
Disclosure of Invention
The embodiment of the invention provides an excel file analysis method, an excel file analysis system, computer equipment and a storage medium, and aims to solve the problem that the efficiency of excel file analysis by adopting an Apache POI technology in the prior art method is low.
In a first aspect, an embodiment of the present invention provides a method for parsing 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 excel file parsing apparatus, including:
the system comprises a workbook generation unit, a first analysis unit and a second analysis unit, wherein the workbook generation 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;
a worksheet acquisition unit, configured to acquire a worksheet of the excel file from the workbook according to a preset acquisition 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 in the memory and capable of running 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, where the computer program when executed by a processor causes the processor to execute the method for parsing an excel file in 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 required for the description of the embodiments will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
Fig. 1 is a flow chart of an excel file parsing method provided in an embodiment of the present invention;
fig. 2 is a schematic sub-flowchart of an excel file parsing method according to an embodiment of the present invention;
fig. 3 is another schematic sub-flowchart of an excel file parsing method according to an embodiment of the present invention;
fig. 4 is another schematic sub-flowchart of an excel file parsing method according to an embodiment of the present invention;
fig. 5 is a schematic block diagram of an excel file parsing device provided in an embodiment of the present invention;
fig. 6 is a schematic block diagram of a computer device according to an embodiment of the present invention.
Detailed Description
The following description of the embodiments of the present invention will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are some, but not all embodiments of the invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
It should be understood that the terms "comprises" and "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 this specification 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 the present specification and the appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes such combinations.
Referring to fig. 1, fig. 1 is a flow chart of an excel file parsing method according to an embodiment of the present invention. The method is applied to the user terminal, and the method is executed through application software installed in the user terminal, and the user terminal is terminal equipment, such as a desktop computer, a notebook computer, a tablet computer or a mobile phone, for executing the method for analyzing the excel file to analyze the excel file. In the embodiment of the invention, the terminal equipment for analyzing the excel file is preconfigured with the Apache POI toolkit, for example, a java item for analyzing the excel file is established in a java development tool, a preset Apache POI toolkit is imported in the java item to analyze the excel file, and a plurality of tools in the Apache POI toolkit are called to analyze the excel file.
As shown in fig. 1, the method includes steps S110 to S140.
S110, 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.
And 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. 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 xls format, the first parsing rule may parse the excel file to generate the workbook, and when the excel file input by the user is in xls format, the first parsing rule may still parse the excel file to generate the workbook.
In one embodiment, as shown in FIG. 2, step S110 includes sub-steps S111, S112, and S113.
S111, judging whether the format of the excel file accords with a preset format.
And judging whether the format of the excel file accords with a preset format. Specifically, the preset format is a format set for an excel file to be parsed, and the excel file can be parsed only when the excel file accords with the preset format. The format of the excel file may be determined according to the suffix name of the excel file, and usually, the suffix names of the excel file include xls and xlsx, and the preset format may be set to xls format or xlsx format.
And S112, if the format of the excel file does not accord with 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 accord with 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 in the Apache POI toolkit for converting the format of an excel file which does not conform to a preset format so as to conform to the preset format. For example, if the preset format is xlsx format and the excel file is an xls format file, the format of the excel file does not conform to the preset format, and the format conversion tool can be used to convert the excel file into an xlsx format file.
S113, analyzing the excel file in the preset format according to a preset analysis tool to generate the workbook.
And analyzing the excel file in the preset format according to a preset analysis tool to generate the workbook. Specifically, the parsing tool is a tool in an Apache POI toolkit for parsing an excel file conforming to the preset format to generate a workbook. The method comprises the steps of obtaining a workbook from an excel file in a preset format so as to obtain a worksheet of the excel file from the workbook.
S120, acquiring 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 the worksheet in the excel file corresponding to the workbook from the workbook. Typically, a workbook includes at least one worksheet, and when the workbook includes a plurality of worksheets, only the worksheets containing the data to be processed need be obtained from the workbook.
In one embodiment, as shown in FIG. 3, step S120 includes sub-steps S121 and S122.
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 an Apache POI tool package and is used for processing a workbook to obtain the processed workbook, and because the workbook generally comprises at least one worksheet, a user can input data to be input into one or more worksheets in the workbook according to own habits of the user and the data amount when inputting the data into the workbook, and the processing tool is used for sorting the one or more worksheets in the workbook so that only one worksheet exists in the workbook. For example, when three worksheets exist in the workbook, only one worksheet stores data to be processed, and the worksheets which do not contain the data in the workbook are deleted by the processing tool; when three worksheets exist in the workbook, the data to be processed in the three worksheets can be combined through the processing tool to obtain the workbook with only one worksheet; when three worksheets exist in the workbook, only two worksheets in the three worksheets have data to be processed, the worksheets which do not contain the data to be processed can be deleted through the processing tool, and the other two worksheets are combined to obtain the workbook which only contains one worksheet.
S122, extracting 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 a worksheet of the excel file. Specifically, the preset extraction tool is an extraction table used for extracting a processed workbook in an Apache POI toolkit to obtain data, and when the extraction tool extracts the workbook after processing, the extraction tool can sequentially extract each row of the data stored in the workbook after processing, or can extract all the data stored in the workbook after processing 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 an excel file submitted by a user. And receiving the generalized parameters of the data in the excel file and the parameters of the relationship between the cell subscripts and the object variables in the excel file through the second parsing rule, wherein the generalized parameters of the data in the excel file can instantiate the data in the excel file, and the comparison table of the variable names and the subscripts in the excel file can be obtained through the parameters of the relationship between the cell and the object variables in the excel file.
In one embodiment, as shown in FIG. 4, step S130 includes sub-steps S131, S132, S133, S134, and S135.
S131, instantiating the worksheet according to a preset first parameter to generate an instantiated worksheet.
And instantiating the worksheet according to a preset first parameter to generate an instantiated worksheet. Specifically, a preset first parameter is used for instantiating a worksheet, namely initializing the worksheet to obtain the category of each row of data in the worksheet, and the variable name corresponding to each row of data in the worksheet is marked by using a corresponding reference number. In this embodiment, the first parameter is a generic parameter of the excel file data, and the worksheet generates an instantiated worksheet through the generic parameter of the excel file data.
S132, carrying out circulation processing on the worksheet according to a preset circulation rule to obtain data of each row of the worksheet.
And carrying out circulation processing on the worksheet according to a preset circulation rule to obtain each row of the worksheet. Specifically, the existing circulation rule may be adopted to circulate the worksheet to obtain the data of each row of the worksheet, that is, circulate the data in the worksheet to obtain the data of one row of the worksheet, and then continue to circulate the worksheet to obtain the data of the next row, so as to finally obtain the data of each row of the worksheet.
S133, inquiring the data of each row of the worksheet according to a preset second parameter to obtain the data of each cell corresponding to each row in the worksheet, and obtaining the variable name of each row according to a preset comparison table.
And inquiring the data of each row of the worksheet according to a preset second parameter to acquire the data of each cell corresponding to each row in the worksheet, and acquiring the variable name of each row according to a preset comparison table. Specifically, the worksheet is subjected to query through the second parameters after each line of data is circularly obtained according to the circulation rule to obtain the data in each cell corresponding to each line in the worksheet, and the variable names corresponding to each line in the worksheet can be obtained through a preset comparison table after the data is obtained.
In another embodiment, the lookup table is generated according to the second parameter, where the lookup table includes a cell index and a variable name of each row in the worksheet.
Specifically, in this embodiment, the comparison table is a comparison table of cell subscripts and variable names in the excel, the second parameter is a parameter of a relationship between the cell subscripts and object variables in the excel, and the comparison table is obtained through the parameter of the relationship between the cell subscripts and the object variables in the excel.
S134, mapping the variable names of each row to the variables of the instantiated worksheet based on a java reflection mechanism to replace the variable names of the instantiated worksheet.
The java-based reflection mechanism maps the variable names of each row to the variables of the instantiated worksheet to replace the variable names of the instantiated worksheet. Specifically, the variable names of each row obtained from the comparison table are mapped to the variables of each row in the instantiated worksheet through the Field method of the java reflection mechanism, so that the variable names in the instantiated worksheet are replaced.
And S135, assigning the cell data to the variable of the instantiated worksheet so as 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 second parsing rule is used for processing the worksheet to generate object list data of the excel file, wherein the object list data of the excel file is the processed data of the excel file, and the processed data of the excel file can be stored in a preset database, and the preset database is a database for storing the processed data of the excel file.
The embodiment of the invention provides an excel file analysis method, which 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; 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 excel file analysis device, and the excel file analysis is used for any embodiment of an excel file analysis method. 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 parsing device of the excel file may be configured in the user terminal.
As shown in fig. 5, the parsing apparatus of an excel file includes a workbook generation unit 110, a worksheet acquisition unit 120, a generation unit 130, and a save unit 140.
The workbook generating unit 110 is configured to, if receiving an excel file input by a user, parse the excel file according to a preset first parsing rule to generate a workbook.
And 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. Specifically, the first parsing rule is used for parsing the excel file into a workbook.
In another embodiment, the workbook generation unit 110 includes a format determination unit 111, a format conversion unit 112, and a parsing unit 113.
And a format determining unit 111, configured to determine whether the format of the excel file conforms to a preset format.
And judging whether the format of the excel file accords with a preset format. Specifically, the preset format is a format set for an excel file to be parsed, and the excel file can be parsed only when the excel file accords with the preset format. The format of the excel file may be determined according to the suffix name of the excel file, and usually, the suffix names of the excel file include xls and xlsx, and the preset format may be set to xls format or xlsx format.
And a format conversion unit 112, configured to convert the excel file into an excel file in 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 if the format of the excel file does not accord with 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 in the Apache POI toolkit for converting the format of an excel file which does not conform to a preset format so as to conform to the preset format.
And 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 in the preset format according to a preset analysis tool to generate the workbook. Specifically, the parsing tool is a tool in an Apache POI toolkit for parsing an excel file conforming to the preset format to generate a workbook. The method comprises the steps of 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 obtaining unit 120, configured to obtain a worksheet of the excel file from the workbook according to a preset obtaining 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 the worksheet in the excel file corresponding to the workbook from the workbook. Typically, a workbook includes at least one worksheet, and when the workbook includes a plurality of worksheets, only the worksheets containing the data to be processed need be obtained from the workbook.
In another embodiment, the worksheet acquisition unit 120 includes a workbook processing unit 121 and a worksheet extraction unit 122.
The workbook processing unit 121 is 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 an Apache POI tool package and is used for processing a workbook to obtain the processed workbook, and because the workbook generally comprises at least one worksheet, a user can input data to be input into one or more worksheets in the workbook according to own habits of the user and the data amount when inputting the data into the workbook, and the processing tool is used for sorting the one or more worksheets in the workbook so that only one worksheet exists in the workbook.
And a worksheet extraction unit 122, 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 a worksheet of the excel file. Specifically, the preset extraction tool is an extraction table used for extracting a processed workbook in an Apache POI toolkit to obtain data, and when the extraction tool extracts the workbook after processing, the extraction tool can sequentially extract each row of the data stored in the workbook after processing, or can extract all the data stored in the workbook after processing at one time.
And the generating unit 130 is configured to parse the worksheet according to a preset second parsing 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 an excel file submitted by a user. And receiving the generalized parameters of the data in the excel file and the parameters of the relationship between the cell subscripts and the object variables in the excel file through the second parsing rule, wherein the generalized parameters of the data in the excel file can instantiate the data in the excel file, and the comparison table of the variable names and the subscripts in the excel file can be obtained through the parameters of the relationship between the cell and the object variables in the excel file.
In another embodiment, the generating unit 130 includes an instantiation unit 131, a loop unit 132, an obtaining unit 133, a mapping unit 134, and an assignment 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 a preset first parameter to generate an instantiated worksheet. Specifically, a preset first parameter is used for instantiating a worksheet, namely initializing the worksheet to obtain the category of each row of data in the worksheet, and the variable name corresponding to each row of data in the worksheet is marked by using a corresponding reference number. In this embodiment, the first parameter is a generic parameter of the excel file data, and the worksheet generates an instantiated worksheet through the generic parameter of the excel file data.
And a circulation unit 132 for performing circulation processing on the worksheet according to a preset circulation rule to obtain data of each row of the worksheet.
And carrying out circulation processing on the worksheet according to a preset circulation rule to obtain each row of the worksheet. Specifically, the existing circulation rule may be adopted to circulate the worksheet to obtain the data of each row of the worksheet, that is, circulate the data in the worksheet to obtain the data of one row of the worksheet, and then continue to circulate the worksheet to obtain the data of the next row, so as to finally obtain the data of each row of the worksheet.
And an obtaining unit 133, configured to query 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 reference table.
And inquiring the data of each row of the worksheet according to a preset second parameter to acquire the cell data corresponding to each row in the worksheet, and acquiring the variable name of each row according to a preset comparison table. Specifically, the worksheet is subjected to query through the second parameters after each line of data is circularly obtained according to the circulation rule to obtain the data in the cell corresponding to each line in the worksheet, and the variable names corresponding to each line in the worksheet can be obtained through a preset comparison table after the data is obtained.
And the mapping unit 134 is used for mapping the variable names of each row to the variables of the instantiated worksheet based on the java reflection mechanism to replace the variable names of the instantiated worksheet.
The java-based reflection mechanism maps the variable names of each row to the variables of the instantiated worksheet to replace the variable names of the instantiated worksheet. Specifically, the variable names of each row obtained from the comparison table are mapped to the variables of each row in the instantiated worksheet through the Field method of the java reflection mechanism, so that the variable names in the instantiated worksheet are replaced.
And an assigning unit 135, configured to assign the cell data to the variable of the instantiated worksheet to obtain object list data of the excel file.
In other embodiments of the invention, the generating unit 130 further includes a look-up table generating unit 136.
And a comparison table generating unit 136, configured to generate the comparison table according to the second parameter, where the comparison table includes a unit cell index and a variable name of each row in the worksheet.
And generating the comparison table according to the second parameter, wherein the comparison table comprises a cell index and a variable name of each row in the working table. Specifically, in this embodiment, the comparison table is a comparison table of cell subscripts and variable names in the excel, the second parameter is a parameter of a relationship between the cell subscripts and object variables in the excel, and the comparison table is obtained through the parameter of the relationship between the cell subscripts and the object variables in the excel.
And a storage unit 140, configured to store the object list data in a preset database.
And storing the object list data into a preset database. Specifically, the second parsing rule is used for processing the worksheet to generate object list data of the excel file, wherein the object list data of the excel file is the processed data of the excel file, and the processed data of the excel file can be stored in a preset database, and the preset database is a database for storing the processed data of the excel file.
The excel file analysis device provided by the embodiment of the invention is used for executing the excel file analysis method, and 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, a memory, and a network interface 505, which are connected by a system bus 501, wherein 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, may cause the processor 502 to perform a parsing method for an excel file.
The processor 502 is used to provide computing and control capabilities to support the operation of the overall device 500.
The internal memory 504 provides an environment for the execution of a computer program 5032 in the non-volatile storage medium 503, which computer program 5032, when executed by the processor 502, causes the processor 502 to execute a method for parsing an excel file.
The network interface 505 is used for network communication, such as providing for transmission of data information, etc. It will be appreciated by those skilled in the art that the structure shown in fig. 6 is merely a block diagram of a portion of the structure associated with the present inventive arrangements and is not limiting of the apparatus 500 to which the present inventive arrangements are applied, and that a particular apparatus 500 may include more or fewer components than shown, or may combine certain components, or have a different arrangement of components.
Wherein the processor 502 is configured to execute a computer program 5032 stored in a memory to perform 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 is not limiting of the specific construction of the apparatus 500, and in other embodiments, the apparatus 500 may include more or less components than illustrated, or certain components may be combined, or a different arrangement of components. For example, in some embodiments, the device 500 may include only the memory and the processor 502, and in such embodiments, the structure and the function of the memory and the processor 502 are consistent with the embodiment shown in fig. 6, and will not be described herein.
It should be appreciated that in an embodiment of the invention, the processor 502 may be a central processing unit (Central Processing Unit, CPU), the processor 502 may also be other general purpose processors 502, digital signal processors 502 (Digital Signal Processor, DSP), application specific integrated circuits (Application Specific Integrated Circuit, ASIC), off-the-shelf programmable gate arrays (Field-Programmable Gate Array, FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or the like. Wherein the general purpose processor 502 may be the microprocessor 502 or the processor 502 may be any conventional processor 502 or the like.
In another embodiment of the 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 will be clearly understood by those skilled in the art that, for convenience and brevity of description, specific working procedures of the apparatus, device and unit described above may refer to corresponding procedures in the foregoing method embodiments, which are not repeated herein. Those of ordinary skill in the art will appreciate that the elements and algorithm steps described in connection with the embodiments disclosed herein may be embodied in electronic hardware, in computer software, or in a combination of the two, and that the elements and steps of the examples have been generally described in terms of function in the foregoing description to clearly illustrate 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 solution. 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 several embodiments provided by the present invention, it should be understood that the disclosed apparatus, device and method may be implemented in other manners. For example, the apparatus embodiments described above are merely illustrative, and for example, the division of the units is merely a logical function division, there may be another division manner in actual implementation, or units having the same function may be integrated into one unit, for example, multiple units or components may be combined or may be integrated into another system, or some features may be omitted, or not performed. In addition, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices, or elements, or may be an electrical, mechanical, or other form of connection.
The units described as separate units may or may not be physically separate, and units shown 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 may be selected according to actual needs to achieve the purpose of the embodiment of the present invention.
In addition, each functional unit in the embodiments of the present invention may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit. The integrated units may be implemented in hardware or in software functional units.
The integrated units may be stored in a storage medium if implemented in the form of software functional units and sold or used as stand-alone products. Based on such understanding, the technical solution of the present invention may be essentially or a part contributing to the prior art, or all or part of the technical solution may be embodied in the form of a software product stored in a storage medium, comprising several instructions for causing an apparatus 500 (which may be a personal computer, a server, a network device, etc.) to perform all or part of the steps of the method according to the embodiments of the present invention. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a magnetic disk, an optical disk, or other various media capable of storing program codes.
While the invention has been described with reference to certain preferred embodiments, it will be understood by those skilled in the art that various changes and substitutions of equivalents may be made and equivalents will be apparent to those skilled in the art without departing from the scope of the invention. Therefore, the protection scope of the invention is subject to the protection scope of the claims.
Claims (6)
1. The method for analyzing the excel file is characterized by comprising the following steps of:
if an excel file input by a user is received, judging whether the format of the excel file accords with a preset format or not;
if the format of the excel file does not accord with the preset format, converting the excel file into the excel file with the preset format according to a preset format conversion tool;
analyzing the excel file in the preset format according to a preset analysis tool to generate a workbook;
acquiring a worksheet of the excel file from the workbook according to a preset acquisition rule;
instantiating the worksheet according to a preset first parameter to generate an instantiated worksheet, wherein the worksheet is instantiated according to the preset first parameter to initialize the worksheet so as to obtain the category of each line of data in the worksheet, the variable name corresponding to each line of data in the worksheet is marked by using a corresponding label, and the first parameter is the generic parameter of the excel file data;
performing circulation processing on the worksheet according to a preset circulation rule to obtain data of each row of the worksheet;
generating a comparison table according to the second parameter, wherein the comparison table comprises cell subscripts in the worksheet and variable names of each row;
inquiring the data of each row of the worksheet according to a preset second parameter to obtain data of each cell corresponding to each row in the worksheet, and obtaining the variable name of each row according to a preset comparison table;
mapping the variable names of each row onto the variables of the instantiated worksheet based on a java-based reflection mechanism to replace the variable names of the instantiated worksheet;
assigning the cell data to the variable of the instantiated worksheet to obtain object list data of the excel file;
and storing the object list data into a preset database.
2. The method for parsing an excel file according to claim 1, wherein the step of obtaining the worksheet of the excel file from the workbook according to a preset obtaining rule includes:
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 a worksheet of the excel file.
3. An excel file parsing device, comprising:
the format judging unit is used for judging whether the format of the excel file accords with a preset format or not if the excel file input by a user is received;
the format conversion unit is used for converting the excel file into the excel file with the preset format according to a preset format conversion tool if the format of the excel file does not accord with the preset format;
the analysis unit is used for analyzing the excel file in the preset format according to a preset analysis tool to generate a workbook;
a worksheet acquisition unit, configured to acquire a worksheet of the excel file from the workbook according to a preset acquisition rule;
an instantiation unit, configured to instantiate the worksheet according to a preset first parameter to generate an instantiated worksheet, where the worksheet is instantiated according to the preset first parameter to initialize the worksheet to obtain a category of each line of data in the worksheet, a variable name corresponding to each line of data in the worksheet is marked by using a corresponding label, and the first parameter is a generic parameter of the excel file data;
the circulation unit is used for carrying out circulation processing on the worksheet according to a preset circulation rule so as to obtain data of each row of the worksheet;
the control table generating unit is used for generating a control table according to the second parameter, wherein the control table comprises unit cell subscripts in the working table and variable names of each row;
the acquisition unit is used for inquiring the data of each row of the worksheet according to a preset second parameter so as to acquire the data of each cell corresponding to each row in the worksheet and acquire the variable name of each row according to a preset comparison table;
the mapping unit is used for mapping the variable names of each row to the variables of the instantiated worksheet based on the java reflection mechanism so as to replace the variable names of the instantiated worksheet;
an assignment unit, configured to assign the cell data to a variable of the instantiated worksheet to obtain object list data of the excel file;
and the storage unit is used for storing the object list data into a preset database.
4. The apparatus for parsing an excel file according to claim 3, wherein the worksheet acquisition unit includes:
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.
5. 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 of claims 1-2 when executing the computer program.
6. 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 perform the method of parsing an excel file according to any one of claims 1 to 2.
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 CN111240688A (en) | 2020-06-05 |
| CN111240688B true 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) |
Families Citing this family (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| 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 |
| CN113486097B (en) * | 2021-06-21 | 2023-03-24 | 上海百秋新网商数字科技有限公司 | Big data export method, device, equipment and storage medium |
Citations (5)
| 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 |
| 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 |
Family Cites Families (1)
| 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 |
-
2020
- 2020-01-10 CN CN202010026250.8A patent/CN111240688B/en active Active
Patent Citations (5)
| 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 |
| 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 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111240688A (en) | 2020-06-05 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111240688B (en) | excel file analysis method and device, computer equipment and storage medium | |
| CN110162544B (en) | Heterogeneous data source data acquisition method and device | |
| US20090313335A1 (en) | Managing Software Component Versions within a Service Oriented Architecture | |
| CN110554958B (en) | Graph database testing method, system, device and storage medium | |
| CN115242896B (en) | Dynamic message parsing method and device, electronic equipment and computer readable storage medium | |
| CN114238948A (en) | Application program detection method and device, electronic equipment and storage medium | |
| CN113434582B (en) | Service data processing method and device, computer equipment and storage medium | |
| CN111209326A (en) | Data query method and device, electronic 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 | |
| CN111142863A (en) | Page generation method and device | |
| CN116204428A (en) | Test case generation method and device | |
| CN112817922B (en) | Log dynamic printing method and device, computer equipment and storage medium | |
| CN116010499A (en) | Method and device for determining analysis rule and electronic equipment | |
| CN115099196A (en) | Method and device for converting message into data entity | |
| CN110471708B (en) | Method and device for acquiring configuration items based on reusable components | |
| US20240095400A1 (en) | Anonymization protocols | |
| CN112162951A (en) | Information retrieval method, server and storage medium | |
| CN113779018A (en) | Data processing method and device | |
| CN111144086B (en) | Log formatting method and device, electronic equipment and storage medium | |
| CN113377672B (en) | Test method, device and equipment | |
| CN114205276B (en) | Performance test method and device for product management system and electronic equipment | |
| US20230289354A1 (en) | Endpoint scan and profile generation | |
| US20230333836A1 (en) | System to identify and characterize code changes | |
| CN117742779A (en) | Method, device, equipment and storage medium for checking resource configuration information |
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 |