CN111639473A - Excel file analysis method and device based on java, computer equipment and storage medium - Google Patents
Excel file analysis method and device based on java, computer equipment and storage medium Download PDFInfo
- Publication number
- CN111639473A CN111639473A CN202010333943.1A CN202010333943A CN111639473A CN 111639473 A CN111639473 A CN 111639473A CN 202010333943 A CN202010333943 A CN 202010333943A CN 111639473 A CN111639473 A CN 111639473A
- Authority
- CN
- China
- Prior art keywords
- excel file
- preset
- format
- header
- excel
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/103—Formatting, i.e. changing of presentation of documents
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/12—Use of codes for handling textual entities
- G06F40/151—Transformation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/177—Editing, e.g. inserting or deleting of tables; using ruled lines
- G06F40/18—Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/20—Natural language analysis
- G06F40/205—Parsing
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Health & Medical Sciences (AREA)
- Artificial Intelligence (AREA)
- Audiology, Speech & Language Pathology (AREA)
- Computational Linguistics (AREA)
- General Health & Medical Sciences (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses an excel file analyzing method, device, computer equipment and storage medium based on java, wherein an excel file input by a user is received and then subjected to format processing, a header in the excel file subjected to format processing is extracted, then data processing is carried out on the excel file according to the extracted information, after the processing is finished, the line number of the content of the excel file is judged by using a line number judging tool in a resource pool so as to determine the line number of the excel file, and a POI tool or an easysexcel tool is selected according to the line number to read the line number so as to obtain the content in the excel file. The method is based on the data processing technology, is not only suitable for analyzing the excel files of various types, but also avoids the problem of memory overflow in the analyzing process, and improves the efficiency of analyzing the excel files.
Description
Technical Field
The invention relates to the technical field of data processing, in particular to an excel file analysis method and device based on java, computer equipment and a storage medium.
Background
POI is the most used framework for realizing excel resolution at present, and most of POI is used in a user model mode. Although the userModel mode is convenient to operate and simple to use, it consumes a large amount of memory, for example, several megabits of memory are consumed for file parsing. Although many applications adopt such a mode at present, mainly because the development effort of the existing technology is small, when an excel file with a large memory needs to be analyzed, the development effort of the existing technology must be increased, so that the memory consumption is huge, and the analysis efficiency is low. Therefore, the problems that memory overflow is easy to occur and efficiency is low when the excel file is analyzed exist in the prior art.
Disclosure of Invention
The embodiment of the invention provides an excel file analyzing method and device based on java, computer equipment and a storage medium, and aims to solve the problems that in the prior art, when an excel file is analyzed, memory overflow is easy to occur and the efficiency is low.
In a first aspect, an embodiment of the present invention provides an excel file parsing method based on java, which includes:
receiving an excel file input by a user, and carrying out format processing on the excel file according to a preset format processing rule to generate an excel file after format processing;
extracting the header in the excel file after format processing according to a preset header extraction rule to obtain data information of the header in the excel file;
performing data processing on the excel file according to the data information to obtain a data-processed excel file;
judging the line number of the data in the excel file after the data processing;
if the line number of the data in the excel file after the data processing is not larger than a preset threshold value, reading the excel file after the data processing according to a preset POI tool to obtain the content in the excel file;
and if the line number of the data in the excel file after the data processing is larger than a preset threshold value, reading the excel file after the data processing according to a preset easysexcel tool so as to obtain the content in the excel file.
In a second aspect, an embodiment of the present invention provides an excel file parsing apparatus based on java, including:
the format processing unit is used for receiving an excel file input by a user, and performing format processing on the excel file according to a preset format processing rule to generate an excel file after format processing;
the header extraction unit is used for extracting the header in the excel file after the format processing according to a preset header extraction rule so as to obtain data information of the header in the excel file;
the data processing unit is used for carrying out data processing on the excel file according to the data information to obtain a data-processed excel file;
the line number judging unit is used for judging the line number of the data in the excel file after the data processing according to a preset line number judging rule so as to determine the line number of the data in the excel file after the data processing;
the first reading unit is used for reading the excel file after the data processing according to a preset POI tool to obtain the content in the excel file if the line number of the data in the excel file after the data processing is not larger than a preset threshold value;
and the second reading unit is used for reading the excel file after the data processing according to a preset easysexcel tool to obtain the content in the excel file if the line number of the data in the excel file after the data processing is larger than a preset threshold value.
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 executable on the processor, where the processor, when executing the computer program, implements the method for parsing an excel file based on java as described in the first aspect.
In a fourth aspect, the embodiment of the present invention further provides a computer-readable storage medium, where the computer-readable storage medium stores a computer program, and the computer program, when executed by a processor, causes the processor to execute the method for parsing excel files based on java according to the first aspect.
The embodiment of the invention provides an excel file analyzing method, an excel file analyzing device, computer equipment and a storage medium based on java, wherein an excel file input by a user is received and then subjected to format processing, a header in the excel file subjected to format processing is extracted, then data processing is performed on the excel file according to the extracted information, after the processing is completed, the line number of the excel file is judged, and a POI tool or an easy excel tool is selected according to the line number to read the POI tool or the easy excel tool so as to obtain the content in the excel file. By the method, the problem of memory overflow in the traditional excel file analyzing process is solved, the excel file analyzing efficiency is improved, and the method is suitable for analyzing various types of excel files.
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 excel file parsing method based on java according to an embodiment of the present invention;
fig. 2 is a schematic sub-flow diagram of an excel file parsing method based on java according to an embodiment of the present invention;
fig. 3 is another schematic sub-flow diagram of an excel file parsing method based on java according to an embodiment of the present invention;
fig. 4 is another schematic sub-flow diagram of an excel file parsing method based on java according to an embodiment of the present invention;
fig. 5 is another schematic sub-flow diagram of an excel file parsing method based on java according to an embodiment of the present invention;
fig. 6 is a schematic block diagram of an excel file parsing apparatus based on java according to an embodiment of the present invention;
fig. 7 is a schematic block diagram of a subunit of an excel file parsing apparatus based on java according to an embodiment of the present invention;
fig. 8 is a schematic block diagram of another subunit of the apparatus for parsing excel files based on java according to the embodiment of the present invention;
fig. 9 is a schematic block diagram of another subunit of the apparatus for parsing excel files based on java according to the embodiment of the present invention;
fig. 10 is a schematic block diagram of another subunit of the apparatus for parsing excel files based on java according to the embodiment of the present invention;
FIG. 11 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 flowchart of an excel file parsing method based on java according to an embodiment of the present invention. The excel analysis method based on java is applied to a user terminal, and is executed through application software installed in the user terminal, wherein the user terminal is terminal equipment, such as a desktop computer, a notebook computer, a tablet computer or a mobile phone and the like, used for executing the excel analysis method based on java to realize the analysis of the excel file. In the embodiment of the invention, a terminal device for realizing the excel analysis method is pre-configured with a Java development tool and a toolkit for analyzing an excel file, for example, a project for analyzing the excel file is established in the Java development tool, and the toolkit for analyzing the excel file is imported in the project, wherein the toolkit can be a jar package which is needed for analyzing the excel file and is analyzed by using a POI tool, and can also be a jar package which is analyzed by using an easysexecel tool, and is used for analyzing the excel by calling the imported toolkit, wherein POI (Point interference Implementation, fuzzy Implementation of compact edition) is a cross-platform Java POI API of a free open source written by Java Apche software foundation, and Apache provides API for a Java program to have a function of reading and writing Microsoft Office format archives; easy excel (Ali open source) is encapsulation and optimization aiming at Apache POI technology, mainly solves the problem of memory consumption of the POI technology and provides better API use.
As shown in fig. 1, the method includes steps S110 to S160.
S110, receiving an excel file input by a user, and performing format processing on the excel file according to a preset format processing rule to generate a formatted excel file.
The method comprises the steps of receiving an excel file input by a user, and carrying out format processing on the excel file according to a preset format processing rule to generate the excel file after format processing. Specifically, when receiving an excel file to be analyzed, a user terminal performs format processing on the excel file according to a preset format processing rule so that the format of the excel file conforms to a preset format, and since excels of different versions have multiple formats, for example: the suffix of the excel file of the excel 2003 version is xls, and the suffix of the excel file of the excel 2007 version is xlsx, so that when the excel file input by a user is received, it is impossible to completely ensure that the format types of the excel file input by each user are consistent. Therefore, before the excel file is analyzed, the received excel file needs to be processed in a unified format.
In one embodiment, as shown in FIG. 2, step S110 includes sub-steps S111 and S112.
S111, receiving an excel file input by a user, and judging whether the format of the excel file conforms to a preset format.
Receiving an excel file input by a user, and judging whether the format of the excel file conforms to a preset format. Specifically, the format of the excel file can be determined by judging a suffix of the excel file. For example, the preset format is an xlsx type, and if the suffix of the excel file is an xls suffix, the format of the excel file input by the user is determined to be the preset format.
And S112, if the format of the excel file does not conform to the preset format, carrying out format conversion on the excel file according to a preset conversion tool to obtain the excel file conforming to the preset format and storing the excel file into a preset storage path.
And if the format type of the excel file does not accord with the preset format, carrying out format conversion on the excel file according to a preset conversion tool so as to obtain the excel file which accords with the preset format and storing the excel file into a preset storage path. Specifically, the conversion tool is a jar package imported into a project for analyzing an excel file, the conversion tool may convert the excel file with xls suffix into an excel file with xlsx suffix, or convert the excel file with xls suffix into an excel file with xls suffix, and the conversion tool performs format conversion on the excel file which does not conform to the preset format according to the preset format. For example, if the preset format is an xls format and the excel file is in an xlsx format, the format of the excel file is converted into the xls format by the format conversion tool, and the excel file after format conversion is stored in a preset storage path.
And if the format of the excel file conforms to the preset format, storing the excel file conforming to the preset format into a preset storage path. Specifically, the preset format is a preset excel format, the preset format may be an xls format or an xlsx format, the preset storage path may be a local warehouse or a private warehouse, and when the excel file conforming to the preset format needs to be called, the excel file only needs to be called from the preset storage path. For example, when the preset format is xls format and the format of the excel file is also xls format, the excel file is stored into a preset storage path.
And S120, extracting the header in the excel file after the format processing according to a preset header extraction rule to obtain data information of the header in the excel file.
And extracting the header in the excel file after the format processing according to a preset header extraction rule to obtain the data information of the header in the excel file. Specifically, the preset header extraction rule is used for extracting the contents of the header in the excel file after format processing to obtain the data information in the header of the excel file.
For example, an excel file containing part of the customer information is shown in table 1:
TABLE 1
As can be seen from table 1, the header information of the excel file includes account number, age, gender, occupation, academic calendar, total amount of assets, transaction times and latest transaction time, so the information extracted from table 1 according to the preset header extraction rule includes account number, age, gender, occupation, academic calendar, total amount of assets, transaction times and latest transaction time.
In an embodiment, as shown in fig. 3, step S120 includes substeps S121 and S122.
And S121, judging the header in the excel file after format processing according to a preset header judgment rule to determine whether the excel file contains the header.
And judging the header in the excel file after format processing according to a preset header judgment rule so as to determine whether the excel file contains the header. The preset header judgment rule can be used for judging the header of the excel file containing partial client information in the table 1 so as to determine the header of the excel file containing partial client information. For example, the excel file in table 1 is subjected to header judgment through the header judgment tool to determine that the excel file in table 1 contains a header.
And S122, if the excel file comprises a header, extracting characters from the header in the excel file after format processing according to a preset character extraction component to obtain data information of the header in the excel file.
And if the excel file comprises a header, performing character extraction on the header in the excel file after format processing according to a preset character extraction component to obtain data information of the header in the excel file. Specifically, after the header in the excel file is determined according to a preset header judgment rule, character extraction can be performed on the header through a preset character extraction component, so as to obtain related information of the header. The character extraction component is a jar packet imported into a project of analyzing the excel file, for example, header information such as account number, age, gender, occupation, academic calendar, total amount of assets, transaction times and latest transaction time is obtained after the header of the excel file containing part of customer information is subjected to character extraction by the character extraction component.
And S130, performing data processing on the excel file according to the data information to obtain the excel file after data processing.
And performing data processing on the excel file according to the data information to obtain the excel file after data processing. Specifically, the data information extracted from the header of the excel file is compared with preset data information to determine whether all the data information in the header of the excel file exists in the preset data information, so that the data in the excel file is optimized, wherein the preset data information is the data information in the header of the excel file which is preset before the excel file is analyzed. When the data information part in the header of the excel file exists in the preset data information, removing the whole column of data corresponding to the data information which does not exist in the preset data information and sequencing the data of each column in the excel file according to a preset sequencing rule so as to accord with the preset sequencing; and when all the data information in the header of the excel file exists in the preset data information, sequencing the data in each column of the excel file according to a preset sequencing rule so as to accord with the preset sequencing. For example, when the data information in the header of the excel file is "age, gender, academic calendar, account number, total amount of assets" in sequence, and the preset data information is "account number, age, gender, occupation, academic calendar, total amount of assets, transaction times, and last transaction time", the data in each column in the excel file may be sorted so that the data information in the header of the excel file is "account number, age, gender, total amount of assets" in sequence; when the data information in the header of the excel file is "age, gender, academic calendar, account number, total amount of assets, and interest in sequence", and the preset data information is "account number, age, gender, occupation, academic calendar, total amount of assets, transaction times, and latest transaction time", the column of data of "interest in the header of the excel file may be removed, and then the data in each column of the removed excel file is sorted to make the data information in the header of the excel file be" account number, age, gender, total amount of assets ", in sequence, wherein the preset sorting rule in this embodiment is rule information sorted according to the preset data information arrangement sequence.
And S140, judging the line number of the data in the excel file after the data processing.
And judging the line number of the data in the excel file after the data processing. Specifically, the rule information of the memory size of the excel file input by the user is indirectly estimated by judging the number of lines of the content in the excel file after the data processing, a large amount of memory is consumed when a conventional excel analysis tool (such as a POI tool) analyzes a dozen KB excel file, and when the conventional excel analysis tool analyzes a dozen of or dozens of megabytes of files, not only a huge memory is consumed, but also a large amount of codes are written, and the consumed time and energy are huge.
S150, if the line number of the data in the excel file after the data processing is not larger than a preset threshold value, reading the excel file after the data processing according to a preset POI tool so as to obtain the content in the excel file.
And if the line number of the data in the excel file after the data processing is not larger than a preset threshold value, reading the excel file after the data processing according to a preset POI tool to obtain the content in the excel file. Specifically, before an item for analyzing an excel file is created in a java development tool, a jar package of a preset POI tool is imported into the item, when the line number of data in the excel file after data processing is not larger than a preset threshold value, it can be estimated that the memory required to be consumed by the excel file is small, and the preset POI tool can be called for analyzing to obtain the data in the excel file. In this embodiment, the preset threshold may be set to 1000, that is, a line number 1000 behavior limit of data in an excel file is set, when the line number in the excel file is lower than 1000 lines, it may be determined that a memory of the excel file is small, and when the line number in the excel file exceeds 1000 lines, it may be determined that the memory of the excel file is large.
In one embodiment, as shown in fig. 4, step S150 includes sub-steps S151, S152, and S153.
And S151, reading the excel file after the data processing according to a preset input stream tool to obtain a workbook.
And reading the excel file after data processing according to a preset input stream tool to obtain a workbook. The excel file is usually presented in the form of a workbook, and the input stream tool exists in the POI tool and is used for reading the data-processed excel file from a specified path to obtain the workbook of the data-processed excel file. For example, when the excel file after data processing is in the D disk of the local disk, the excel file after data processing is read from the D disk of the local disk only by the input stream tool and is displayed in the form of a workbook.
S152, obtaining a plurality of worksheets in the workbook.
A plurality of worksheets in the workbook is obtained. Specifically, the relation between the workbook and the worksheets is similar to the relation between books and pages, usually, the workbook contains 3 worksheets, worksheet labels can be seen below an Excel interface, the default names are Sheet1, Sheet2 and Sheet3, the content in each worksheet is relatively independent, and the worksheets can be switched among different worksheets by clicking the worksheet labels. The method comprises the following steps that generally, the workbook contains three worksheets with names of Sheet1, Sheet2 and Sheet3, data in an excel file are generally stored in the worksheet with the name of Sheet1, and when all contents of the excel file are stored in the worksheet with the name of Sheet1, the worksheet with the name of Sheet1 is obtained from the workbook, namely the worksheet to be read is obtained; when the entire contents of the excel file are stored in worksheets named Sheet1 and Sheet2, respectively, the worksheets named Sheet1 and Sheet2 are merged from the workbook to generate a worksheet to be read.
S153, reading the data in the worksheet according to a preset sheet reading component to obtain the content in the excel file.
And reading the data in the worksheet according to a preset sheet reading component to obtain the content in the excel file. Specifically, the form reading component exists in the POI tool and is used for reading data information in all cells in the worksheet so as to obtain the content in the excel file. For example, the form reading component may sequentially read the data of each row in the worksheet from top to bottom in the worksheet, and may sequentially read the data of each cell from left to right in the process of reading each row of data in the worksheet.
And S160, if the line number of the data in the excel file after the data processing is larger than a preset threshold value, reading the excel file after the data processing according to a preset easysexcel tool to obtain the content in the excel file.
And if the line number of the data in the excel file after the data processing is larger than a preset threshold value, reading the excel file after the data processing according to a preset easysexcel tool so as to obtain the content in the excel file. Specifically, because the POI tool can only read the content in the excel file with a small memory, when the excel file with a large memory needs to be read, the memory overflow phenomenon occurs, and the analysis process is complicated. Therefore, before creating a project for analyzing an excel file in a java development tool, a preset jar package of an easyxcel tool needs to be imported into the project, a preset threshold value for analyzing the line number of the excel file is set, and when the line number of data in the excel file after data processing is larger than the preset threshold value, the preset easyxcel tool can be called for analyzing to obtain the content in the excel file. In this embodiment, the preset threshold may be set to 1000, that is, a line number 1000 behavior limit of data in an excel file is set, and when the line number in the excel file is higher than 1000 lines, it may be determined that the memory of the excel file is large, and a preset easy excel tool may be called to perform analysis to obtain the content in the excel file.
In one embodiment, as shown in fig. 5, step S160 includes sub-steps S161, S162, and S163.
And S161, reading the excel file after data processing according to a preset buffer input stream tool to obtain a workbook.
And reading the excel file after data processing according to a preset buffer input stream tool to obtain a workbook. The excel file is usually presented in the form of a workbook, and the buffer input stream tool exists in the easy excel tool and is used for reading the data processed excel file from a specified path to obtain the workbook of the data processed excel file and caching the workbook. For example, when the excel file after data processing is in the D disk of the local disk, the excel file after data processing is only required to be acquired from the D disk of the local disk through the buffer input stream tool and displayed in the form of a workbook, and the workbook is cached at the same time.
And S162, analyzing each line of the workbook through a preset analysis monitor to obtain each line of data of the excel file after data processing, wherein the analysis monitor does not analyze the header in the excel file.
And analyzing each line of the workbook through a preset analysis monitor to obtain each line of data of the excel file after data processing, wherein the analysis monitor does not analyze the header in the excel file. Specifically, the analysis monitor is established on the basis of calling an easysexcel tool to analyze each line of the Excel file after data processing, the invoke method is called by the analysis monitor to obtain cell contents every time the analysis monitor analyzes one line, and the doAfterAllanalyzed method is executed when the whole Excel file is analyzed, so that the problem of memory overflow caused by overlarge file is solved.
And S163, reading the data of each line according to a preset synchronous reading rule and importing the read content into a preset entity model to obtain the content in the excel file.
And reading the data of each line according to a preset synchronous reading rule and importing the read contents into a preset entity model to obtain the contents in the excel file. Specifically, the preset synchronous reading rule is used for rule information for reading the data of each line, wherein the content of reading the data of each line by the easysexcel tool through the synchronous reading rule does not include the header of the excel file, so that the entity model needs to be established in the easysexcel tool, and the entity model includes the header of the excel file. And after the content of each line of data is read through the synchronous reading rule, importing the content into the entity model to obtain the complete content of the excel file, and finally exporting the entity model to obtain the content in the excel file. The entity model needs to inherit a BaseRowModel, and for an Excel data list header needing to be analyzed, an annotation @ ExcelProperty (for example, value ═ name, index ═ 0) needs to be marked on a corresponding entity attribute of the entity model. Here index is a necessary parameter to indicate which column in Excel corresponds to, and now only de-mapping of data list header in Excel according to index is supported, so value only serves as one function of additional description field.
The embodiment of the invention also provides an excel file analyzing device 100 based on java, which is used for executing any embodiment of the excel file analyzing based on java. Specifically, referring to fig. 6, fig. 6 is a schematic block diagram of an excel file parsing apparatus 100 based on java according to an embodiment of the present invention.
As shown in fig. 6, the java-based excel file parsing apparatus 100 includes a format processing unit 110, a header extracting unit 120, a processing unit 130, a line number determining unit 140, a first reading unit 150, and a second reading unit 160.
The format processing unit 110 is configured to receive an excel file input by a user, and perform format processing on the excel file according to a preset format processing rule to generate a formatted excel file.
When a user terminal receives an excel file needing to be analyzed, format processing is carried out on the excel file through a preset format processing rule so that the format of the excel file conforms to a preset format.
In other embodiments of the present invention, as shown in fig. 7, the format processing unit 110 includes a format determining unit 111 and a format converting unit 112.
And the format judging unit 111 is used for judging whether the format of the excel file conforms to a preset format.
And determining the format of the excel file by judging the suffix of the excel file. When the suffix of the excel file is xls, judging that the excel file is a file in an xls format; and when the suffix of the excel file is xlsx, judging that the excel file is a file in an xlsx format.
And the format conversion unit 112 is configured to, if the format type of the excel file does not conform to the preset format, perform format conversion on the excel file according to a preset conversion tool to obtain the excel file conforming to the preset format, and store the excel file into a preset storage path.
According to the conversion tool, the excel file with the xls suffix can be converted into the excel file with the xlsx suffix, and the excel file with the xlsx suffix can be converted into the excel file with the xls suffix. And the conversion tool is used for converting the format of the excel file which does not conform to the preset format according to the preset format.
And a header extraction unit 120, configured to extract a header in the excel file after the format processing according to a preset header extraction rule, so as to obtain data information of the header in the excel file.
In this embodiment, the content of the first line in the excel file after the format processing is extracted through a preset header extraction rule to obtain the data information of the header in the excel file.
In other inventive embodiments, as shown in fig. 8, the header extraction unit 120 includes a header judgment unit 121 and a character extraction unit 122.
And a header judging unit 121, configured to judge, according to a preset header judgment rule, a header in the excel file after the format processing to determine whether the excel file contains the header.
In this embodiment, all the line numbers in the excel file after the format processing are judged through the header judgment rule, and it is determined whether the first line in the line numbers is the header of the excel file.
And the character extraction unit 122 is configured to, if the excel file includes a header, perform character extraction on the header in the excel file after the format processing according to a preset character extraction component to obtain data information of the header in the excel file.
And after the header in the excel file is determined according to a preset header judgment rule, character extraction can be carried out on the header through the character extraction component so as to obtain related information of the header.
And the data processing unit 130 is configured to perform data processing on the excel file according to the data information to obtain a data-processed excel file.
And after the data information is extracted from the header of the excel file, comparing the data information with preset data information to obtain whether the data information in the header of the excel file exists in the preset data information or not, thereby optimizing the data in the excel file. When the data information part in the header of the excel file exists in the preset data information, removing the whole column of data corresponding to the data information which does not exist in the preset data information and sequencing the data of each column in the excel file according to a preset sequencing rule so as to accord with the preset sequencing; and when all the data information in the header of the excel file exists in the preset data information, sequencing the data in each column of the excel file according to a preset sequencing rule so as to accord with the preset sequencing.
The line number judging unit 140 is configured to judge a line number of data in the excel file after the data processing.
And indirectly estimating the memory size of the excel file input by the user by judging the line number of the content in the excel file after the data processing. In the embodiment of the invention, when the number of lines in the excel file is set to be within a certain preset number of lines, the memory of the excel file is judged to be small, and when the number of lines exceeds a certain number of lines, the memory of the excel file is judged to be large.
The first reading unit 150 is configured to, if the number of lines of the data in the excel file after the data processing is not greater than a preset threshold, read the excel file after the data processing according to a preset POI tool to obtain content in the excel file.
Generally, the POI tool is only suitable for parsing a file with a smaller memory, and in this embodiment, the POI tool may read all information in the excel file after the data processing to obtain the content in the excel file. In this embodiment, the preset threshold is set to 1000, that is, a line number 1000 behavior limit of data in an excel file is set, when the line number in the excel file is lower than 1000 lines, it can be determined that a memory of the excel file is small, that is, the excel file after data processing can be acquired by using an input stream tool in the POI tool, the excel file after data processing generates a worksheet by using a worksheet generation component in the POI tool, and then data in the worksheet is read by using a form reading component in the POI tool, so as to finally obtain content in the excel file.
In other inventive embodiments, as shown in fig. 9, the first reading unit 150 includes a first obtaining unit 151, a work sheet generating unit 152, and a first content reading unit 153.
A first obtaining unit 151, configured to read the data-processed excel file according to a preset input stream tool to obtain a workbook.
And reading the excel file after data processing according to a preset input stream tool to obtain a workbook. And the input stream tool is existed in the POI tool and is used for reading the data processed excel file from the specified path to obtain a workbook of the data processed excel file.
A worksheet generating unit 152 for acquiring a plurality of worksheets in the workbook.
The excel file is usually shown in the form of a workbook, the relation between the workbook and the worksheet is similar to the relation between a book and a page, the workbook usually contains three worksheets with names of Sheet1, Sheet2 and Sheet3, data in the excel file is generally stored in the worksheet with the name of Sheet1, and when all contents of the excel file are stored in the worksheet with the name of Sheet1, the worksheet with the name of Sheet1 is obtained from the workbook, and the worksheet to be read is obtained; when the entire contents of the excel file are stored in worksheets named Sheet1 and Sheet2, respectively, the worksheets named Sheet1 and Sheet2 are merged from the workbook to generate a worksheet to be read.
A first content reading unit 153, configured to read data in the worksheet according to a preset form reading component to obtain content in the excel file.
And reading the data in the worksheet according to a preset sheet reading component to obtain the content in the excel file. Specifically, the form reading component is configured to read data information in all cells in the worksheet to obtain the content in the excel file. For example, the form reading component may sequentially read the data of each row in the worksheet from top to bottom in the worksheet, and may sequentially read the data of each cell from left to right in the process of reading each row of data in the worksheet.
The second reading unit 160 is configured to, if the number of lines of the data in the excel file after the data processing is greater than a preset threshold, read the excel file after the data processing according to a preset easysexcel tool to obtain the content in the excel file.
Before creating an item for analyzing an excel file in a java development tool, a preset jar package of an easy excel tool needs to be imported into the item, a preset threshold value for analyzing the line number of the excel file is set, and when the line number of data in the excel file after data processing is larger than the preset threshold value, the preset easy excel tool can be called for analyzing to obtain the content in the excel file. The analysis efficiency of the excel file can be greatly improved by using the easy excel tool to replace a POI tool to analyze the excel file.
In another embodiment of the present invention, as shown in fig. 10, the second reading unit 160 includes a second obtaining unit 161, a parsing monitoring unit 162, and a second content reading unit 163.
A second obtaining unit 161, configured to read the data processed excel file according to a preset buffered input stream tool to obtain a workbook.
And reading the excel file after data processing according to a preset buffer input stream tool to obtain a workbook. The buffer input stream tool exists in the easy excel tool and is used for reading the excel file after data processing from a specified path to obtain a workbook of the excel file after data processing and caching the workbook.
And the analysis monitoring unit 162 is configured to analyze each line of the workbook through a preset analysis monitor to obtain each line of data of the excel file after data processing, where the analysis monitor does not analyze a header in the excel file.
Analyzing each line of the workbook through a preset analysis monitor to obtain each line of data of the Excel file after data processing, wherein the analysis monitor does not analyze a header in the Excel file, a traditional analysis method (such as a POI tool) can analyze the Excel file only after the whole Excel is completely loaded, the analysis monitor in the embodiment calls an invoke method to obtain cell contents every time the analysis monitor analyzes one line, and the doAfter Allanalyzed method is executed when the whole Excel file is analyzed, so that the problem of memory overflow caused by overlarge memory when the file is analyzed is solved.
The second content reading unit 163 is configured to read the data in each line according to a preset synchronous reading rule and import the read content into a preset entity model, so as to obtain the content in the excel file.
And after the content of each line of data is read through the synchronous reading rule, importing the content into the entity model to obtain the complete content of the excel file, and finally exporting the entity model to obtain the content in the excel file.
The excel file analyzing device based on java provided by the embodiment of the invention is used for executing the excel file analyzing method based on java, receiving an excel file input by a user, and performing format processing on the excel file according to a preset format processing rule to generate an excel file after format processing; extracting the header in the excel file after format processing according to a preset header extraction rule to obtain data information of the header in the excel file; performing data processing on the excel file according to the data information to obtain a data-processed excel file; judging the line number of the data in the excel file after the data processing; if the line number of the data in the excel file after the data processing is not larger than a preset threshold value, reading the excel file after the data processing according to a preset POI tool to obtain the content in the excel file; and if the line number of the data in the excel file after the data processing is larger than a preset threshold value, reading the excel file after the data processing according to a preset easysexcel tool so as to obtain the content in the excel file.
Referring to fig. 11, fig. 11 is a schematic block diagram of a computer device according to an embodiment of the present invention.
Referring to fig. 11, 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 java based excel file parsing method.
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 java-based 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. 11 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: receiving an excel file input by a user, and carrying out format processing on the excel file according to a preset format processing rule to generate an excel file after format processing; extracting the header in the excel file after format processing according to a preset header extraction rule to obtain data information of the header in the excel file; performing data processing on the excel file according to the data information to obtain a data-processed excel file; judging the line number of the data in the excel file after the data processing; if the line number of the data in the excel file after the data processing is not larger than a preset threshold value, reading the excel file after the data processing according to a preset POI tool to obtain the content in the excel file; and if the line number of the data in the excel file after the data processing is larger than a preset threshold value, reading the excel file after the data processing according to a preset easysexcel tool so as to obtain the content in the excel file.
Those skilled in the art will appreciate that the embodiment of the apparatus 500 illustrated in fig. 11 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 those illustrated, 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. 11, 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: receiving an excel file input by a user, and carrying out format processing on the excel file according to a preset format processing rule to generate an excel file after format processing; extracting the header in the excel file after format processing according to a preset header extraction rule to obtain data information of the header in the excel file; performing data processing on the excel file according to the data information to obtain a data-processed excel file; judging the line number of the data in the excel file after the data processing; if the line number of the data in the excel file after the data processing is not larger than a preset threshold value, reading the excel file after the data processing according to a preset POI tool to obtain the content in the excel file; and if the line number of the data in the excel file after the data processing is larger than a preset threshold value, reading the excel file after the data processing according to a preset easysexcel tool so as to obtain the content in the excel file.
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 based on java is characterized by comprising the following steps:
receiving an excel file input by a user, and carrying out format processing on the excel file according to a preset format processing rule to generate an excel file after format processing;
extracting the header in the excel file after format processing according to a preset header extraction rule to obtain data information of the header in the excel file;
performing data processing on the excel file according to the data information to obtain a data-processed excel file;
judging the line number of the data in the excel file after the data processing;
if the line number of the data in the excel file after the data processing is not larger than a preset threshold value, reading the excel file after the data processing according to a preset POI tool to obtain the content in the excel file;
and if the line number of the data in the excel file after the data processing is larger than a preset threshold value, reading the excel file after the data processing according to a preset easysexcel tool so as to obtain the content in the excel file.
2. The method for analyzing excel files based on java according to claim 1, wherein the step of performing format processing on the excel files according to preset format processing rules to generate formatted excel files comprises:
judging whether the format of the excel file conforms to a preset format or not;
and if the format of the excel file does not conform to the preset format, carrying out format conversion on the excel file according to a preset conversion tool to obtain the excel file conforming to the preset format and storing the excel file into a preset storage path.
3. The method for analyzing excel files based on java according to claim 1, wherein extracting the header in the excel files after format processing according to a preset header extraction rule to obtain the data information of the header in the excel files comprises:
judging the header in the excel file after format processing according to a preset header judgment rule to determine whether the excel file contains the header;
and if the excel file comprises a header, performing character extraction on the header in the excel file after format processing according to a preset character extraction component to obtain data information of the header in the excel file.
4. The method for parsing excel file according to the java based on claim 1, wherein reading the excel file after data processing according to a preset POI tool to obtain the content in the excel file comprises:
reading the excel file after data processing according to a preset input stream tool to obtain a workbook;
acquiring a plurality of worksheets in the workbook;
and reading the data in the worksheet according to a preset sheet reading component to obtain the content in the excel file.
5. The method for parsing excel file based on java according to claim 1, wherein reading the excel file after data processing according to a preset easysexcel tool to obtain contents in the excel file comprises:
reading the excel file after data processing according to a preset buffer input stream tool to obtain a workbook;
analyzing each line of the workbook through a preset analysis monitor to obtain each line of data of the excel file after data processing, wherein the analysis monitor does not analyze a header in the excel file;
and reading each line of data according to a preset synchronous reading rule and importing the read content into a preset entity model to obtain the content in the excel file.
6. An excel file analysis device based on java is characterized by comprising:
the format processing unit is used for receiving an excel file input by a user, and performing format processing on the excel file according to a preset format processing rule to generate an excel file after format processing;
the header extraction unit is used for extracting the header in the excel file after the format processing according to a preset header extraction rule so as to obtain data information of the header in the excel file;
the data processing unit is used for carrying out data processing on the excel file according to the data information to obtain a data-processed excel file;
the line number judging unit is used for judging the line number of the data in the excel file after the data processing;
the first reading unit is used for reading the excel file after the data processing according to a preset POI tool to obtain the content in the excel file if the line number of the data in the excel file after the data processing is not larger than a preset threshold value;
and the second reading unit is used for reading the excel file after the data processing according to a preset easysexcel tool to obtain the data in the excel file if the line number of the data in the excel file after the data processing is larger than a preset threshold value.
7. The java based excel file parsing apparatus as claimed in claim 6, wherein said format processing unit comprises:
the format judging unit is used for judging the excel file according to a preset format judging rule to obtain whether the format of the excel file conforms to a preset format or not;
and the format conversion unit is used for carrying out format conversion on the excel file according to a preset conversion tool if the format type of the excel file does not conform to the preset format so as to obtain the excel file conforming to the preset format and store the excel file into the preset storage path.
8. The java based excel file parsing apparatus as claimed in claim 6, wherein said header extraction unit comprises:
the header judging unit is used for judging the header in the excel file after format processing according to a preset header judging rule so as to determine whether the excel file contains the header;
and the character extraction unit is used for extracting characters from the header in the excel file after format processing according to a preset character extraction component to obtain data information of the header in the excel file if the excel file contains the header.
9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the java based excel file parsing method 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 perform the java based excel file parsing method according to any one of claims 1 to 5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010333943.1A CN111639473A (en) | 2020-04-24 | 2020-04-24 | Excel file analysis method and device based on java, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010333943.1A CN111639473A (en) | 2020-04-24 | 2020-04-24 | Excel file analysis method and device based on java, computer equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111639473A true CN111639473A (en) | 2020-09-08 |
Family
ID=72332845
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010333943.1A Pending CN111639473A (en) | 2020-04-24 | 2020-04-24 | Excel file analysis method and device based on java, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111639473A (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112861486A (en) * | 2021-04-25 | 2021-05-28 | 成都淞幸科技有限责任公司 | Data integration method, device, equipment and storage medium of semi-structured file |
| CN112988866A (en) * | 2021-03-01 | 2021-06-18 | 亿企赢网络科技有限公司 | Method and device for exporting excel file, electronic equipment and storage medium |
| CN113326681A (en) * | 2021-05-25 | 2021-08-31 | 上海微盟企业发展有限公司 | Data processing method, device, equipment and computer readable storage medium |
| CN115269515A (en) * | 2022-09-22 | 2022-11-01 | 泰盈科技集团股份有限公司 | Processing method for searching specified target document data |
| CN115659934A (en) * | 2022-12-09 | 2023-01-31 | 泰盈科技集团股份有限公司 | Method for calculating and storing data of different worksheet columns in table document |
| CN115983222A (en) * | 2023-03-20 | 2023-04-18 | 苏州清研精准汽车科技有限公司 | EasyExcel-based file data reading method, device, equipment and medium |
| CN116029271A (en) * | 2023-02-01 | 2023-04-28 | 南京维拓科技股份有限公司 | Method for importing tree-type structure data in batches based on easy excel |
-
2020
- 2020-04-24 CN CN202010333943.1A patent/CN111639473A/en active Pending
Cited By (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112988866A (en) * | 2021-03-01 | 2021-06-18 | 亿企赢网络科技有限公司 | Method and device for exporting excel file, electronic equipment and storage medium |
| CN112861486A (en) * | 2021-04-25 | 2021-05-28 | 成都淞幸科技有限责任公司 | Data integration method, device, equipment and storage medium of semi-structured file |
| CN113326681A (en) * | 2021-05-25 | 2021-08-31 | 上海微盟企业发展有限公司 | Data processing method, device, equipment and computer readable storage medium |
| CN115269515A (en) * | 2022-09-22 | 2022-11-01 | 泰盈科技集团股份有限公司 | Processing method for searching specified target document data |
| CN115269515B (en) * | 2022-09-22 | 2022-12-09 | 泰盈科技集团股份有限公司 | Processing method for searching specified target document data |
| CN115659934A (en) * | 2022-12-09 | 2023-01-31 | 泰盈科技集团股份有限公司 | Method for calculating and storing data of different worksheet columns in table document |
| CN115659934B (en) * | 2022-12-09 | 2023-03-07 | 泰盈科技集团股份有限公司 | Method for calculating and storing different worksheet column data in table document |
| CN116029271A (en) * | 2023-02-01 | 2023-04-28 | 南京维拓科技股份有限公司 | Method for importing tree-type structure data in batches based on easy excel |
| CN115983222A (en) * | 2023-03-20 | 2023-04-18 | 苏州清研精准汽车科技有限公司 | EasyExcel-based file data reading method, device, equipment and medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111639473A (en) | Excel file analysis method and device based on java, computer equipment and storage medium | |
| CN110520859B (en) | More intelligent copy/paste | |
| CN111240688B (en) | excel file analysis method and device, computer equipment and storage medium | |
| US11416768B2 (en) | Feature processing method and feature processing system for machine learning | |
| US10191968B2 (en) | Automated data analysis | |
| US8213719B2 (en) | Editing 2D structures using natural input | |
| CN108717437B (en) | Search result display method and device and storage medium | |
| CN112650533B (en) | Interface document generation method and device and terminal equipment | |
| EP2756433B1 (en) | Efficiently providing multiple metadata representations of the same type | |
| US20060271567A1 (en) | System and method for user edit merging with preservation of unrepresented data | |
| CN112199331A (en) | Automatic generation method, system, terminal and storage medium of interface document | |
| US20200320291A1 (en) | Techniques to determine document recognition errors | |
| CN102289497A (en) | Document preview image generating system and method | |
| WO2011075612A1 (en) | Methods and apparatuses for abstract representation of financial documents | |
| US20230359827A1 (en) | Representing Confidence in Natural Language Processing | |
| EP4010813A1 (en) | Spreadsheet table transformation | |
| CN113138760B (en) | Page generation method and device, electronic equipment and medium | |
| CN111897778B (en) | Computer file merging method, device and system | |
| CN111767703B (en) | Form data acquisition method, device and system | |
| CN111783482A (en) | Text translation method and device, computer equipment and storage medium | |
| CN111651696A (en) | Product label customizing method and device, computer storage medium and electronic equipment | |
| US7818534B2 (en) | Determination of sampling characteristics based on available memory | |
| CN116340271B (en) | Opening method and device of open format document, electronic equipment and storage medium | |
| CN111796803B (en) | Template report generation method based on multi-stage container restocking | |
| US20210342523A1 (en) | Systems and Methods for Data Driven Document Creation and Modification |
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 |