File analysis processing method and device, storage medium and electronic equipment

Abstract

The application discloses a file analysis processing method and device, a storage medium and electronic equipment. The method comprises the following steps: determining the file data amount of a received Excel file; if the file data amount is greater than a preset threshold, replacing an initial parser built in a file processing system with a custom parser, wherein the custom parser is used for analyzing non-empty cells in the Excel file according to analysis requirements; calling the custom parser to analyze the Excel file according to the version type of the Excel file by using a first processing thread, so as to obtain analyzed data; and writing the analyzed data into a target database by using a second processing thread. The application solves the technical problems of the existing Excel file analysis method, such as complicated operation steps, low analysis efficiency and easy memory overflow.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of file processing, in particular to a file analysis processing method and device, a storage medium and an electronic device. BACKGROUND

[0002] At present, various information systems constructed by enterprises have the function of importing or exporting external data, and there are many ways to import data through Excel. There are many methods for program implementation of Excel data analysis, reading and operation. The most commonly used method is to operate Excel through the application program interface (API) provided by the Apache POI component. The component has no problem in reading a small amount of data (such as several thousand data), but it is easy to cause reading timeout, memory overflow and other problems when importing large amount of Excel data (such as 100,000 or 1,000,000 data).

[0003] The traditional Excel document analysis method uses JDBC API to write data after reading Excel data is completed. This method needs to wait for the data reading to be completed first, and then writes the corresponding database through the program, which leads to a long time consumption, low data analysis efficiency, easy data loss and poor user experience.

[0004] At present, there is no effective solution to the above problems. SUMMARY

[0005] The embodiments of the present application provide a file analysis processing method and device, a storage medium and an electronic device to at least solve the technical problems of complex operation steps, low analysis efficiency and easy memory overflow of the existing Excel file analysis method.

[0006] According to an aspect of the embodiments of the present application, a file analysis processing method is provided, including: determining the file data amount of a received Excel file; if the file data amount is greater than a preset threshold, replacing the initial parser built in the file processing system with a custom parser, wherein the custom parser is used to analyze the non-empty cells in the Excel file according to analysis requirements; using a first processing thread to call the custom parser to analyze the Excel file according to the version type of the Excel file, to obtain parsed data; and using a second processing thread to write the parsed data into a target database.

[0007] Optionally, before the first processing thread is used to call the custom parser according to the version type of the Excel file to parse the Excel file and obtain parsed data, the method further includes: creating a thread manager in the file processing system, wherein the thread manager is used to manage the creation and destruction of multiple processing threads by creating a custom thread pool; creating a cache queue in the file processing system, wherein the cache queue is used to temporarily store the parsed data; creating the first processing thread and the second processing thread in the file processing system, wherein the first processing thread and the second processing thread execute processing tasks asynchronously, the first processing thread is used to execute the task of parsing the Excel file, and the second processing thread is used to execute the task of processing the parsed data.

[0008] Optionally, the first processing thread is used to call the custom parser according to the version type of the Excel file to parse the Excel file and obtain parsed data, including: using the first processing thread to determine the version type of the Excel file, wherein different version types correspond to different parsing processing manners; selecting the custom parser according to the version type; calling the custom parser corresponding to the version type to parse the Excel file and obtain the parsed data, wherein the first processing thread is further used to put the parsed data into a cache queue for temporary storage.

[0009] Optionally, the second processing thread is used to write the parsed data into a target database, including: using the second processing thread to consume the parsed data from the cache queue; using the second processing thread to store the currently consumed parsed data in a temporary set of data to be written into a database, and determining whether the currently consumed parsed data is the last piece of parsed data in the cache queue; determining whether to write the parsed data stored in the temporary set into the target database according to the determination result.

[0010] Optionally, the determination of whether to write the parsed data stored in the temporary set into the target database according to the determination result includes: if the determination result is that the currently consumed parsed data is the last piece of parsed data, writing the parsed data stored in the temporary set into the target database; if the determination result is that the currently consumed parsed data is not the last piece of parsed data, determining whether the number of parsed data stored in the temporary set meets the requirement of writing into the database; and if the number of parsed data stored in the temporary set meets the requirement of writing into the database, writing the parsed data stored in the temporary set into the target database.

[0011] Optionally, the writing of the parsed data stored in the temporary collection into the target database comprises: calling a database access interface by using a second processing thread to write the parsed data stored in the temporary collection into the target database in batches, or calling a write command corresponding to the database characteristics of the target database by using a second processing thread to write the parsed data stored in the temporary collection into the target database.

[0012] Optionally, if the version type is the first version, the custom parser corresponding to the version type is called to parse the Excel file to obtain the parsed data, which comprises: calling a first parser corresponding to the first version to read the Excel file according to the obtained file path, and converting the Excel file into an input stream; creating a workbook corresponding to the Excel file through the input stream, and cyclically traversing each table page contained in the Excel file to obtain a to-be-parsed table page; parsing the to-be-parsed table page to obtain all rows of the to-be-parsed table page; cyclically traversing the all rows of the to-be-parsed table page; when the all rows of the to-be-parsed table page are parsed, returning to cyclically traverse each table page contained in the Excel file until the cyclic traversal of all the table pages in the Excel file and all the rows of the table pages is completed, and then determining that the parsing is completed to obtain the parsed data.

[0013] Optionally, the cyclic traversal and parsing of the all rows of the to-be-parsed table page comprises: parsing a current row in the all rows of the to-be-parsed table page, and determining whether the current row is a first row in the all rows; if the current row is the first row, parsing title content of the to-be-parsed table page, and storing the title content; if the current row is not the first row, cyclically traversing and parsing each cell in the current row, and putting parsed data obtained by parsing the current row into the cache queue.

[0014] Optionally, if the version type is the second version, the custom parser corresponding to the version type is called to parse the Excel file to obtain the parsed data, including: reading the Excel file according to the obtained file path by using a reading component; registering a second parser corresponding to the second version, and registering a logic processing class of the second parser, wherein the logic processing class includes: a first parsing method for processing the content of a starting cell, and a second parsing method for processing the content of a last cell; reading each table page contained in the Excel file by using an Excel file reader to obtain a table input stream; parsing all cells in each table page by using the Excel file reader to obtain the parsed data; calling the first parsing method to parse the cells, and constructing a null value as the parsed data if the cells are null, and putting the parsed data into the cache queue until the last cell in the table page is parsed; and when the last cell in the table page is parsed, calling the second parsing method to parse the last cell, and putting the parsed data into the cache queue.

[0015] Optionally, the method further includes: after the last cell in the current table page is parsed, returning to read the next table page contained in the Excel file by using the Excel file reader until all table pages are parsed.

[0016] According to another aspect of the embodiments of the present application, a file parsing processing device is further provided, which includes: a determination module configured to determine a file data volume of a received Excel file; a replacement module configured to replace an initial parser built in a file processing system with a custom parser if the file data volume is greater than a preset threshold, wherein the custom parser is configured to parse non-null cells in the Excel file according to parsing requirements; an acquisition module configured to call the custom parser to parse the Excel file according to a version type of the Excel file by using a first processing thread to obtain parsed data; and a writing module configured to write the parsed data into a target database by using a second processing thread.

[0017] According to another aspect of the embodiments of the present application, a nonvolatile storage medium is further provided, which stores a plurality of instructions, and the instructions are adapted to be loaded and executed by a processor to perform any one of the file parsing processing methods.

[0018] According to another aspect of the embodiments of the present application, an electronic device is provided, which comprises a memory and a processor, the memory stores a computer program, and the processor is configured to execute the computer program to perform any one of the file analysis processing methods.

[0019] In the embodiments of the present application, the file analysis processing is adopted, the file data amount of the received Excel file is determined, if the file data amount is greater than a preset threshold, the initial parser built in the file processing system is replaced by a custom parser, the custom parser is used to analyze the non-empty cells in the Excel file according to the analysis requirement, the custom parser is called by the first processing thread according to the version type of the Excel file to analyze the Excel file, and the parsed data is obtained, the parsed data is written into the target database by the second processing thread, the purpose of realizing the analysis and storage of the Excel file by the custom parser and the multi-processing thread is achieved, the technical effect of simplifying the analysis steps of the Excel file and improving the analysis efficiency is achieved, and the technical problems of the existing Excel file analysis method, such as complicated operation steps, low analysis efficiency and easy memory overflow, are solved. BRIEF DESCRIPTION OF DRAWINGS

[0020] The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiments of the application and together with the description serve to explain the application. In the drawings:

[0021] Figure 1 is a flowchart of a file analysis processing method according to the prior art;

[0022] Figure 2 is a flowchart of a file analysis processing method according to an embodiment of the present application;

[0023] Figure 3 is a schematic diagram of a thread manager processing flow according to an embodiment of the present application;

[0024] Figure 4 is a flowchart of an optional file analysis processing method according to an embodiment of the present application;

[0025] Figure 5 is a flowchart of a file analysis processing method of a first version type according to an embodiment of the present application;

[0026] Figure 6 is a flowchart of a file analysis processing method of a second version type according to an embodiment of the present application;

[0027] Figure 7is a structural schematic diagram of a file parsing processing device according to an embodiment of the present application. DETAILED DESCRIPTION

[0028] In order to make the personnel in the technical field better understand the present application scheme, the technical scheme in the embodiment of the present application will be described clearly and completely in the following with reference to the drawings in the embodiment of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, not all. Based on the embodiments in the present application, all other embodiments obtained by the person skilled in the art without creative labor should belong to the scope of protection of the present application.

[0029] It should be noted that the terms "first", "second" and the like in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and do not necessarily describe a specific order or sequence. It should be understood that the data thus used can be interchanged under appropriate circumstances, so that the embodiments of the present application described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "include" and "have" and any variations thereof are intended to cover non-exclusive inclusion, for example, a process, method, system, product or device including a series of steps or units does not necessarily limit to those steps or units clearly listed, but can include other steps or units not clearly listed or inherent to these processes, methods, products or devices.

[0030] First, for the convenience of understanding the embodiments of the present application, the following will explain some terms or nouns involved in the present application:

[0031] Apache POI: is a free, open source, cross-platform Java API written in Java, which provides API for Java programs to read and write functions of Microsoft Office (World, Excel, PPT, etc.) format documents.

[0032] Memory overflow: refers to the memory required by the application system during running is greater than the maximum memory provided, at this time the program cannot run, the system will prompt memory overflow.

[0033] JDBC: Java Database Connectivity, is an application programming interface in Java language used to provide how to access the database, which provides methods such as query, update, delete, etc. in the database.

[0034] Database: is a warehouse organized, stored and managed according to data structure, is a long-term storage in computer, organized, shareable, unified management of large amount of data collection.

[0035] POI-OOXML: mainly used for parsing Office Excel, including reading, parsing, writing, etc. API, it is a component.

[0036] Embodiment 1

[0037] The prior art Excel file parsing processing method as shown in Figure 1 includes the following steps:

[0038] Step S1, obtaining the Excel file uploaded by the information system;

[0039] Step S2, reading the Excel file through the poi-ooxml component provided by the Apache POI component, and converting the Excel file into an input stream;

[0040] Step S3, creating the Workbook corresponding to Excel through API;

[0041] Step S4, reading each Sheet page contained in Excel through API in a loop;

[0042] Step S5, reading the data of each row in a single Sheet in a loop;

[0043] Step S6, reading each cell of each row of data in a loop, completing the parsing of Excel data, and obtaining the parsed data;

[0044] Step S7, writing the parsed data into the database through JDBC API.

[0045] The above method has at least the following defects: 1) high memory occupation, easy to cause memory overflow. The Apache POI Excel parsing component loads data into memory for reading at one time, which is easy to cause high memory occupation or memory overflow, and the application program crashes. 2) low parsing efficiency and long time consumption. The Apache POI Excel parsing component parses all data in Excel at a time, including empty cell data. If there are a large number of empty cells, it will cause additional memory and time consumption. 3) low database writing efficiency, easy to lose data. The Excel data needs to be parsed before writing into the database, if the data volume is too large, it needs to wait for a long time, if memory overflow occurs, it may not be able to complete data writing, resulting in data loss. 4) complex use steps, need to process the parsing logic by yourself. It is necessary to call API for file reading, parsing, writing and other logic processing, and different APIs need to be called for different versions of Office Excel.

[0046] Based on the above problems, the embodiment of the present application provides a method for file analysis processing, and it should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although the logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that here.

[0047] Figure 2 A flowchart of a method for file analysis processing according to an embodiment of the present application is shown in FIG. 1, which comprises the following steps: Figure 2

[0048] Step S102, determining the file data amount of the received Excel file;

[0049] Step S104, if the file data amount is greater than a preset threshold, replacing the initial parser built-in in the file processing system with a custom parser;

[0050] Step S106, using a first processing thread to call the custom parser according to the version type of the Excel file to analyze the Excel file, and obtaining the parsed data;

[0051] Step S108, using a second processing thread to write the parsed data into a target database.

[0052] Optionally, when the Excel file is received, the file path information of the Excel file is obtained.

[0053] Optionally, the custom parser is used to analyze the non-empty cells in the Excel file according to the analysis requirement, and only one row of data in the non-empty cells is analyzed each time.

[0054] It should be noted that in the case that the file amount is too large, i.e., the file data amount is greater than a preset threshold, the Excel file is prone to have problems of high memory occupation or memory overflow, and the custom parser (SAXParser) only analyzes the contents of the cells with values in the Excel file, and does not consume time and memory to analyze and store the empty cells; in addition, the principle of the document parser is to analyze one row of data in the Excel file each time, and to read and analyze on demand, instead of loading all data into the memory at one time for analysis, so that the memory requirement is low, and the problems of memory overflow and high memory occupation can be effectively avoided.

[0055] Optionally, if the file data amount is less than or equal to the preset threshold, the initial parser built-in in the file processing system is directly used, and the operation process is simplified.

[0056] ​Optionally, the first processing thread can be understood as a producer task thread (ExcelProducer) for performing a task of parsing the Excel file, and the second processing thread can be understood as a consumer task thread (ExcelConsumer) for performing a task of processing the parsed data (such as writing the parsed data into a target database).

[0057] It should be noted that, in the embodiment of the application, two different processing threads (i.e., the first processing thread and the second processing thread) are used to respectively parse the Excel file and write the parsed data into a target database, that is, the operation of parsing the Excel file and writing the parsed data into the target database is decoupled, and through the multi-thread mode, the operation of parsing the Excel file and writing the parsed data into the target database can be performed simultaneously, the concurrency is increased, the linear sequence waiting of task execution is avoided, and the file parsing efficiency and the storage efficiency are improved.

[0058] In the embodiment of the application, the file parsing processing mode is used, the file data amount of the received Excel file is determined, if the file data amount is greater than a preset threshold, the initial parser built in the file processing system is replaced by a custom parser, the custom parser is used to parse non-empty cells in the Excel file according to parsing requirements, the first processing thread is used to call the custom parser to parse the Excel file according to the version type of the Excel file, and the parsed data is obtained, and the second processing thread is used to write the parsed data into a target database, so that the technical effects of simplifying the Excel file parsing steps and improving the parsing efficiency are achieved, and the technical problems of the existing Excel file parsing method, such as complicated operation steps, low parsing efficiency and easy memory overflow, are solved.

[0059] As an optional embodiment, before the first processing thread is used to call the custom parser to parse the Excel file according to the version type of the Excel file, the method further includes:

[0060] Step S202, creating a thread manager in the file processing system;

[0061] Step S204, creating a cache queue in the file processing system;

[0062] Step S206, creating the first processing thread and the second processing thread in the file processing system.

[0063] Optionally, the thread manager is configured to manage the creation and destruction of the plurality of processing threads by creating a custom thread pool; and the cache queue is configured to temporarily store the parsed data.

[0064] Optionally, the first processing thread and the second processing thread are configured to asynchronously execute the processing task, the first processing thread is configured to execute the task of parsing the Excel file, and the second processing thread is configured to execute the task of processing the parsed data (e.g., writing the parsed data into a target database).

[0065] As an optional embodiment, Figure 3 is a schematic diagram of a thread manager processing flow according to an embodiment of the present application, as Figure 3 shown, an Excel file is received through an open interface Open API, and after obtaining the file path information of the Excel file, a thread manager is created, including: creating a thread manager ExecueManager in a file processing system; creating a cache queue CacheQueue in the file processing system; creating a producer task thread ExcelProducer and a consumer task thread ExcelConsumer (i.e., the first processing thread and the second processing thread) in the file processing system, wherein the producer task thread is configured to execute the task of parsing the Excel file by calling an Excel parsing component, and the consumer task thread is configured to execute the task of writing the parsed data into a target database, wherein the target database can include but is not limited to Mysql, GreenPlum, etc.

[0066] In an optional embodiment, the first processing thread is configured to call the custom parser according to the version type of the Excel file to parse the Excel file and obtain parsed data, including:

[0067] Step S302: determining the version type of the Excel file by using the first processing thread, wherein different version types correspond to different parsing processing manners;

[0068] Step S304: selecting the custom parser according to the version type;

[0069] Step S306: calling the custom parser corresponding to the version type to parse the Excel file and obtain the parsed data, wherein the first processing thread is further configured to put the parsed data into a cache queue for temporary storage.

[0070] Optionally, the version type includes at least a first version type (e.g., 2003 version xlsx) and a second version type (e.g., 2007 version xlsx).

[0071] Optionally, different version types correspond to different parsing processing manners and different custom parsers, and a custom parser corresponding to a version type is called to parse an Excel file. A producer task thread ExcelProducer (i.e., a first processing thread) calls different custom parsers according to different version types to parse the above Excel file, and after the parsing is completed, the parsed data is placed in a cache queue CacheQueue for temporary storage, and the producer task thread task is executed.

[0072] In an optional embodiment, the above writing of the parsed data into the target database by the second processing thread includes:

[0073] Step S402, the parsed data is consumed from the cache queue by the second processing thread;

[0074] Step S404, the parsed data currently consumed is stored into a temporary set to be written into a database by the second processing thread, and it is judged whether the parsed data currently consumed is the last parsed data in the cache queue.

[0075] Step S406, it is determined according to the judgment result whether the parsed data stored in the temporary set is written into the target database.

[0076] Optionally, the consumer task thread (i.e., the second processing thread) consumes the parsed data from the cache queue CacheQueue, stores the parsed data currently consumed into a temporary set Vector to be written into a database, and judges whether the parsed data currently consumed is the last parsed data in the cache queue.

[0077] In an optional embodiment, the above determining according to the judgment result whether the parsed data stored in the temporary set is written into the target database includes:

[0078] Step S502, if the judgment result is that the parsed data currently consumed is the last parsed data, the parsed data stored in the temporary set is written into the target database;

[0079] Step S504, if the judgment result is that the parsed data currently consumed is not the last parsed data, it is judged whether the number of the parsed data stored in the temporary set meets the requirement of writing into a database.

[0080] Step S506, if the number of parsed data stored in the temporary set meets the requirement of writing into the database, write the parsed data stored in the temporary set into the target database.

[0081] Optionally, if the current consumed parsed data is the last parsed data, call the business processing interface JDBC API to write the parsed data stored in the temporary set into the target database; if the current consumed parsed data is not the last parsed data, judge whether the number of parsed data stored in the temporary set Vector meets the requirement of writing into the database; if the number of parsed data stored in the temporary set Vector meets the requirement of writing into the database, call the business processing interface JDBC API to write the parsed data stored in the temporary set into the target database.

[0082] As an optional embodiment, Figure 4 is a flow chart of an optional file parsing processing method according to an embodiment of the present application, as shown in Figure 4As shown, the method mainly includes a business processing flow, a thread manager processing flow, a producer processing flow, and a consumer processing flow, and specifically includes: an Open API receiving an Excel file, and creating an ExecueManager after obtaining file path information of the Excel file; creating and destroying a plurality of processing threads by creating a custom thread pool, temporarily storing the parsed data in a CacheQueue, and creating an ExcelProducer and an ExcelConsumer in the file processing system; the ExcelProducer (i.e., a first processing thread) calling different custom parsers according to different version types, parsing the Excel file, and temporarily storing the parsed data in the CacheQueue after parsing is completed; the ExcelConsumer (i.e., a second processing thread) consuming the parsed data from the CacheQueue and storing the parsed data currently consumed in a temporary set Vector to be written into a database and determining whether the parsed data currently consumed is the last parsed data in the CacheQueue; if the parsed data currently consumed is the last parsed data, a JDBC API is called to write the parsed data stored in the temporary set into the target database; and if the parsed data currently consumed is not the last parsed data, determining whether the number of parsed data stored in the temporary set Vector meets the requirement of writing into the database, and if the number of parsed data stored in the temporary set Vector meets the requirement of writing into the database, calling the JDBC API to write the parsed data stored in the temporary set into the target database.

[0083] It should be noted that the embodiment of the present application replaces the built-in initial parser in the file processing system with a custom parser, which parses data row by row without loading all data into memory at once, thereby achieving on-demand loading; the custom parser only parses cells containing data without consuming additional memory and time to parse empty cells without data; the producer-consumer multithreading mode is used to support parallel Excel synchronous reading and parsing and data writing, thereby reducing file parsing time; the multithreading mode is used to write data in the JDBC API mode, thereby improving database writing efficiency; and after importing project dependencies, the file parsing can be completed by calling the Excel parsing API, which is simple to use and easy to master.

[0084] In an alternative embodiment, the writing of the parsed data stored in the temporary collection into the target database comprises: calling a database access interface by using a second processing thread to write the parsed data stored in the temporary collection into the target database in batches, or calling a write command corresponding to the database characteristics of the target database by using a second processing thread to write the parsed data stored in the temporary collection into the target database.

[0085] Optionally, the parsed data can be written into the target database by calling the business processing interface JDBC API mode, and the data can be subjected to business operation in combination with the database characteristics or commands of the target database.

[0086] In an alternative embodiment, if the version type is the first version, the custom parser corresponding to the version type is called to parse the Excel file to obtain the parsed data, comprising:

[0087] In step S602, a first parser corresponding to the first version is called to read the Excel file according to the obtained file path, and the Excel file is converted into an input stream;

[0088] In step S604, a workbook corresponding to the Excel file is created by using the input stream, and each table page contained in the Excel file is looped and traversed to obtain a to-be-parsed table page;

[0089] In step S606, all rows of the to-be-parsed table page are obtained by parsing the to-be-parsed table page;

[0090] In step S608, the all rows of the to-be-parsed table page are looped and traversed.

[0091] In step S610, when the all rows of the to-be-parsed table page are parsed, the looped and traversed each table page contained in the Excel file is returned until the all rows of all the table pages in the Excel file are looped and traversed, and it is determined that the parsing is completed, and the parsed data is obtained.

[0092] Optionally, the first version can be but is not limited to Office 2003 version Excel file type (xls), and by parsing the Excel file type, Office 2003 version Excel file type (xls) is determined and processed.

[0093] Optionally, a workbook HSSFWorkbook corresponding to the above Excel file is created by an input stream, the number of table pages (i.e. Sheet pages) of the current workbook HSSFWorkbook is obtained, and each table page contained in the above Excel file is traversed to obtain a to-be-parsed table page. After the traversal of the table pages is completed, the data content in the above to-be-parsed table page is parsed, the total number of rows totalRowNumber of the above to-be-parsed table page is obtained, and all rows of the above to-be-parsed table page are traversed. If all the rows are traversed, the parsing is completed.

[0094] In an optional embodiment, the above traversal of all the rows of the above to-be-parsed table page comprises:

[0095] Step S702, a current row in the above all rows of the above to-be-parsed table page is parsed, and it is determined whether the above current row is a first row in the above all rows.

[0096] Step S704, if the above current row is the above first row, the title content of the above to-be-parsed table page is parsed, and the above title content is stored.

[0097] Step S706, if the above current row is not the above first row, each cell in the above current row is parsed in a loop, and the parsed data obtained by parsing the above current row is put into the above cache queue.

[0098] Optionally, a current row Row in the above all rows of the above to-be-parsed table page is parsed, it is determined whether the above current row is a first row in the above all rows. If the above current row is the first row, it is determined that the above current row is a title row. After the title content of the above to-be-parsed table page is parsed, the above title content is stored. If the above current row is not the above first row, each cell in the above current row is parsed in a loop, and the parsed data obtained by parsing the above current row is put into the above cache queue.

[0099] Optionally, if the above cell is a null value, a null value is constructed as the above parsed data, and the above null value is put into the above cache queue.

[0100] As an optional embodiment, Figure 5 is a flowchart of a file parsing processing method according to a first version type of the embodiment of the application, as shown in Figure 5 , the method comprises the following steps:

[0101] Step S11, a file path of an Excel file to be uploaded is obtained, the file type of the above Excel file is parsed, and a file of an Office 2003 version Excel file type (xls) is obtained and processed.

[0102] Step S12, reading the Excel file according to the file path, and converting the Excel file into an input stream;

[0103] Step S13, creating a workbook HSSFWorkbook based on the input stream;

[0104] Step S14, obtaining the number of Sheet pages of the current workbook HSSFWorkbook, and looping through the Sheet pages until all the Sheet pages are traversed;

[0105] Step S15, obtaining the total number of rows totalRowNumber in the Sheet page to be parsed, and looping through all the rows in the Sheet page to be parsed;

[0106] Step S16, looping through each cell in all the rows to obtain parsed data;

[0107] Step S17, putting the parsed data into a cache queue CacheQueue.

[0108] In an optional embodiment, if the version type is the second version, a custom parser corresponding to the version type is called to parse the Excel file to obtain the parsed data, including:

[0109] Step S801, reading the Excel file according to the obtained file path by using a reading component;

[0110] Step S803, registering a second parser corresponding to the second version, and registering a logical processing class of the second parser, wherein the logical processing class includes: a first parsing method for processing the content of a cell read in, and a second parsing method for processing the content of a last cell read;

[0111] Step S804, reading each table page contained in the Excel file by using an Excel file reader to obtain a table input stream;

[0112] Step S805, parsing the table input stream by using the Excel file reader to obtain all the cells in each table page;

[0113] Step S806, calling the first parsing method to parse the cells, constructing a null value as the parsed data if the cells are null, and putting the parsed data into the cache queue until the last cell in the table page is parsed;

[0114] Step S807, when parsing the last cell in the above table page, the second parsing method is called to parse the last cell, and the parsed data is put into the cache queue.

[0115] Optionally, the second version can be but is not limited to Office 2007+ version Excel file type (xlsx), by obtaining the file type, obtaining and processing Office 2007+ version Excel file type (xlsx) file.

[0116] Optionally, the Excel file is read by the reading component, the Excel file is converted into a file stream (i.e. Zip stream), the Zip stream is converted into a Zip file by creating an OPCPackage, and on this basis, an Excel file reader XSSFReader is created.

[0117] Optionally, the second parser can be but is not limited to a custom file parser "org.apache.xerces.parsers.SAXParser"; the logical processing class can be but is not limited to the logical processing class "PagingHandler" of the custom file parser; the first parsing method can be but is not limited to the startElement method, which is used to process the content of the read-in cell; and the second parsing method can be but is not limited to the endElement method, which is used to process the content of the last read cell.

[0118] Optionally, the Excel file reader XSSFReader is used to parse the table input stream (i.e. Sheet input stream), and all cells in each table page are obtained.

[0119] Optionally, the second parser (i.e. custom document parser) processing logic is called, including: the first parsing method (such as the startElement method) is called to parse the read-in cell, if the cell is empty, an empty value is constructed as the parsed data, and the parsed data is put into the cache queue CacheQueue, the above operation is repeatedly executed until the last cell in the table page is parsed; when the last cell in the table page (i.e. Sheet page) is parsed, the second parsing method (such as the endElement method) is called to parse the last cell, and the parsed data is put into the cache queue CacheQueue.

[0120] Optionally, the first parsing method is called to parse the cell, it is judged whether the cell is null value, if the cell is null value, null value is constructed as the parsed data, and the parsed data is put into the cache queue; if the cell is not null value, the cell is parsed to obtain the parsed data, and the parsed data is put into the cache queue, and the above operation is circularly executed until the last cell in the table page is parsed.

[0121] In an optional embodiment, the method further comprises:

[0122] Step S902, after parsing the last cell in the current table page, the next table page (i.e. Sheet page) included in the Excel file is read by using the Excel file reader until all the table pages are parsed.

[0123] Optionally, after parsing the last cell in the current table page, the next table page (i.e. Sheet page) is continuously read until all the Sheet pages are read.

[0124] As an optional embodiment, Figure 6 is a flow chart of a second version type of file parsing processing method according to an embodiment of the application, as shown in Figure 6 the method comprises the following steps:

[0125] Step S21, a file path of an Excel file to be uploaded is acquired, a file type of the Excel file is parsed, an Office 2007+ version Excel file type (xlsx) file is acquired and processed;

[0126] Step S22, the Excel file is read by using a reading component poi-ooxml, and the Excel file is converted into a file stream;

[0127] Step S23, the Zip stream is packaged into a Zip file by constructing an OPCPackage, and an Excel file reader is created on the basis;

[0128] Step S24, each cell included in the Excel file is read by using the Excel file reader;

[0129] Step S25, each cell is parsed by using a self-defined file parser;

[0130] Step S26, it is judged whether the cell is null value;

[0131] Step S27, if the cell is null, then constructing the null as the parsed data, and putting the parsed data into the cache queue;

[0132] Step S28, if the cell is not null, then parsing the cell to get the parsed data, and putting the parsed data into the cache queue until the last cell in the table page is parsed.

[0133] It should be noted that, for the foregoing method embodiments, in order to simply describe, they are all expressed as a series of action combinations, but those skilled in the art should know that the present application is not limited to the action sequence described, because according to the present application, certain steps can be performed in other sequences or simultaneously. Secondly, those skilled in the art should know that the embodiments described in the specification all belong to preferred embodiments, and the actions and modules involved are not necessarily essential to the present application.

[0134] From the above description of the embodiments, those skilled in the art can clearly understand that the method according to the above embodiments can be realized by means of software and the necessary general hardware platform, and of course it can also be realized by hardware, but in many cases the former is a better embodiment. Based on such understanding, the technical solutions of the present application can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes a plurality of instructions for making a terminal device (which can be a mobile phone, computer, server, or network device, etc.) execute the method described in each embodiment of the present application.

[0135] Embodiment 2

[0136] In this embodiment, a file parsing processing device is also provided, which is used to implement the above embodiments and preferred embodiments, and those which have been described will not be repeated. As used below, the term "module" "device" can be a combination of software and / or hardware that implements a predetermined function. Although the devices described in the following embodiments are preferably implemented in software, hardware, or a combination of software and hardware is also possible and is contemplated.

[0137] According to the embodiments of the present application, a device embodiment for implementing the above file parsing processing method is also provided, Figure 7 is a structural schematic diagram of a file parsing processing device according to an embodiment of the present application, as Figure 7 shown, the file parsing processing device comprises a determination module 40, a replacement module 42, an acquisition module 44, and a writing module 46, wherein:

[0138] The determination module 40 is configured to determine a file data amount of the received Excel file.

[0139] The replacement module 42 is configured to replace an initial parser built in the file processing system with a custom parser if the file data amount is greater than a preset threshold, wherein the custom parser is configured to parse non-empty cells in the Excel file according to parsing requirements.

[0140] The acquisition module 44 is configured to call the custom parser to parse the Excel file according to a version type of the Excel file by using a first processing thread, to obtain parsed data.

[0141] The writing module 46 is configured to write the parsed data into a target database by using a second processing thread.

[0142] It should be noted that each of the modules can be implemented by using software or hardware. For example, for the hardware, each of the modules can be located in the same processor, or each of the modules can be located in different processors in any combination.

[0143] It should be noted that the determination module 40, the replacement module 42, the acquisition module 44, and the writing module 46 correspond to steps S102 to S108 in Embodiment 1, and the modules have the same instances and application scenarios as the corresponding steps, but are not limited to the content disclosed in Embodiment 1. It should be noted that the modules can be run in a computer terminal as part of an apparatus.

[0144] It should be noted that optional or preferred implementation manners of the present embodiment can be referred to the related description in Embodiment 1, which will not be repeated here.

[0145] The file parsing processing apparatus can further include a processor and a memory, and the determination module 40, the replacement module 42, the acquisition module 44, and the writing module 46 are stored in the memory as program units, and the processor executes the program units stored in the memory to implement corresponding functions.

[0146] The processor includes a core, and the core retrieves corresponding program units from the memory, and the core can be one or more. The memory can include a non-permanent memory in a computer readable medium, a random access memory (RAM), and / or a non-volatile memory such as a read-only memory (ROM) or a flash memory (flash RAM), and the memory includes at least one memory chip.

[0147] According to the embodiments of the present application, an embodiment of a nonvolatile storage medium is also provided. Optionally, in the embodiment, the nonvolatile storage medium comprises a stored program, wherein the program, when executed, controls a device in which the nonvolatile storage medium is located to perform any of the file parsing processing methods.

[0148] Optionally, in the embodiment, the nonvolatile storage medium can be located in any of computer terminals in a computer terminal group in a computer network or in any of mobile terminals in a mobile terminal group, and the nonvolatile storage medium comprises a stored program.

[0149] Optionally, the program, when executed, controls a device in which the nonvolatile storage medium is located to perform the following functions: determining a file data amount of a received Excel file; replacing an initial parser built in a file processing system with a custom parser if the file data amount is greater than a preset threshold, wherein the custom parser is used to parse non-empty cells in the Excel file according to parsing requirements; using a first processing thread to call the custom parser to parse the Excel file according to a version type of the Excel file to obtain parsed data; and using a second processing thread to write the parsed data into a target database.

[0150] Optionally, the program, when executed, controls a device in which the nonvolatile storage medium is located to perform the following functions: creating a thread manager in the file processing system, wherein the thread manager is used to manage creation and destruction of a plurality of processing threads by creating a custom thread pool; creating a cache queue in the file processing system, wherein the cache queue is used to temporarily store the parsed data; and creating the first processing thread and the second processing thread in the file processing system, wherein the first processing thread and the second processing thread asynchronously perform processing tasks, the first processing thread is used to perform a task of parsing the Excel file, and the second processing thread is used to perform a task of processing the parsed data.

[0151] Optionally, the program, when executed, controls a device in which the nonvolatile storage medium is located to perform the following functions: using the first processing thread to determine a version type of the Excel file, wherein different version types correspond to different parsing processing manners; selecting the custom parser according to the version type; and calling the custom parser corresponding to the version type to parse the Excel file to obtain the parsed data, wherein the first processing thread is further used to put the parsed data into the cache queue for temporary storage.

[0152] Optionally, the device where the non-volatile storage medium is located is controlled to perform the following functions during program execution: consuming the parsed data from the cache queue by the second processing thread; storing the currently consumed parsed data in a temporary set to be written into the database, and determining whether the currently consumed parsed data is the last piece of parsed data in the cache queue; determining whether to write the parsed data stored in the temporary set into the target database according to the determination result.

[0153] Optionally, the device where the non-volatile storage medium is located is controlled to perform the following functions during program execution: if the determination result is that the currently consumed parsed data is the last piece of parsed data, writing the parsed data stored in the temporary set into the target database; if the determination result is that the currently consumed parsed data is not the last piece of parsed data, determining whether the number of parsed data stored in the temporary set meets the database writing requirement; and if the number of parsed data stored in the temporary set meets the database writing requirement, writing the parsed data stored in the temporary set into the target database.

[0154] Optionally, the device where the non-volatile storage medium is located is controlled to perform the following functions during program execution: calling a database access interface by the second processing thread to write the parsed data stored in the temporary set into the target database in batches, or calling a write command corresponding to the database characteristics of the target database by the second processing thread to write the parsed data stored in the temporary set into the target database.

[0155] Optionally, the device where the non-volatile storage medium is located is controlled to perform the following functions during program execution: calling a first parser corresponding to the first version to read the Excel file according to the obtained file path and convert the Excel file into an input stream; creating a workbook corresponding to the Excel file through the input stream, and cyclically iterating through each table page contained in the Excel file to obtain a to-be-parsed table page; parsing the to-be-parsed table page to obtain all rows of the to-be-parsed table page; cyclically iterating through the all rows of the to-be-parsed table page; when the all rows of the to-be-parsed table page are parsed, returning to execute the cyclic iteration of each table page contained in the Excel file until the cyclic iteration of all rows of all table pages in the Excel file is completed, and then determining that the parsing is completed to obtain the parsed data.

[0156] Optionally, the program running time controls the device where the nonvolatile storage medium is located to perform the following functions: parsing a current row in all the rows of the table page to be parsed, judging whether the current row is the first row in all the rows; if the current row is the first row, parsing the title content of the table page to be parsed and storing the title content; if the current row is not the first row, circularly traversing and parsing each cell in the current row, and putting the parsed data obtained by parsing the current row into the cache queue.

[0157] Optionally, the program running time controls the device where the nonvolatile storage medium is located to perform the following functions: reading the Excel file according to the obtained file path by using a reading component; registering a second parser corresponding to a second version and a logic processing class of the second parser, wherein the logic processing class includes: a first parsing method for processing the content of the first read-in cell, and a second parsing method for processing the content of the last read cell; reading each table page contained in the Excel file by using the Excel file reader to obtain a table input stream; parsing the table input stream by using the Excel file reader to obtain all the cells in each table page; calling the first parsing method to parse the cells, constructing a null value as the parsed data if the cells are null, and putting the parsed data into the cache queue until the last cell in the table page is parsed; when the last cell in the table page is parsed, calling the second parsing method to parse the last cell and putting the parsed data into the cache queue.

[0158] Optionally, the program running time controls the device where the nonvolatile storage medium is located to perform the following functions: after the last cell in the current table page is parsed, returning to read the next table page contained in the Excel file by using the Excel file reader until all the table pages are parsed.

[0159] According to the embodiments of the present application, an embodiment of a processor is further provided. Optionally, in the embodiment, the processor is used to run a program, wherein the program performs any of the file parsing processing methods when running.

[0160] According to the embodiments of the present application, an embodiment of a computer program product is further provided, which is adapted to execute the program initialized with the steps of any of the file parsing processing methods when executed on a data processing device.

[0161] Optionally, the computer program product described above, when executed on a data processing device, is adapted to execute the program of the following method steps: determining a file data amount of a received Excel file; if the file data amount is greater than a preset threshold, replacing an initial parser built in a file processing system with a custom parser, wherein the custom parser is used to parse non-empty cells in the Excel file according to parsing requirements; using a first processing thread to call the custom parser to parse the Excel file according to a version type of the Excel file, to obtain parsed data; and using a second processing thread to write the parsed data into a target database.

[0162] According to the embodiments of the present application, an embodiment of an electronic device is also provided, which comprises a memory and a processor, the memory stores a computer program, and the processor is configured to execute the computer program to perform any of the file parsing processing methods.

[0163] The serial numbers of the embodiments of the present application are only for description, and do not represent the advantages or disadvantages of the embodiments.

[0164] In the above embodiments of the present application, the description of each embodiment has its own focus, and the parts not described in detail in a certain embodiment can be referred to the related description of other embodiments.

[0165] In the several embodiments provided in the present application, it should be understood that the disclosed technology can be implemented in other ways. Of course, the device embodiments described above are only schematic. For example, the division of the units can be a logical function division, and there can be another division manner in actual implementation, for example, a plurality of units or components can be combined or integrated into another system, or some features can be ignored or not executed. In addition, the coupling or direct coupling or communication connection between the units shown or discussed can be indirect coupling or communication connection through some interface, unit or module, and can be electrical or other forms.

[0166] The units described as separate components can or can not be physically separate, and the components shown as units can or can not be physical units, that is, they can be located in one place, or can be distributed on multiple units. Part or all of the units can be selected according to actual needs to achieve the purpose of the present embodiment scheme.

[0167] In addition, each functional unit in each embodiment of the present application can be integrated in one processing unit, or each unit can exist physically, or two or more units can be integrated in one unit. The integrated unit can be realized in the form of hardware or in the form of a software functional unit.

[0168] The integrated unit, if implemented in the form of a software function unit and sold or used as an independent product, can be stored in a computer-readable nonvolatile storage medium. Based on such understanding, the technical solutions of the present application, essentially or in other words, the part that contributes to the prior art or the whole or part of the technical solutions can be embodied in the form of a software product. The computer software product is stored in a nonvolatile storage medium, including a number of instructions to make a computer device (which can be a personal computer, a server, or a network device, etc.) execute all or part of the steps of the methods described in various embodiments of the present application. The aforementioned nonvolatile storage medium includes: a U disk, a read-only memory (ROM, Read-Only Memory), a random access memory (RAM, Random Access Memory), a mobile hard disk, a magnetic disk or an optical disk, and various media that can store program codes.

[0169] The above is only the preferred embodiment of the present application, and it should be pointed out that for those skilled in the art, without departing from the principles of the present application, a number of improvements and refinements can be made, and these improvements and refinements should be considered as the protection scope of the present application.

Claims

1. A file parsing and processing method, characterized in that, include: Determine the amount of data in the received Excel file; If the file data volume exceeds a preset threshold, the initial parser built into the file processing system will be replaced with a custom parser, wherein the custom parser is used to parse the non-empty cells in the Excel file according to the parsing requirements; The first processing thread calls the custom parser to parse the Excel file based on its version type, and obtains the parsed data. The parsed data is written to the target database using a second processing thread; The step of using a first processing thread to call the custom parser to parse the Excel file based on its version type and obtain the parsed data includes: using the first processing thread to determine the version type of the Excel file, wherein different version types correspond to different parsing processing methods; selecting the custom parser based on the version type; and calling the custom parser corresponding to the version type to parse the Excel file and obtain the parsed data. The first processing thread is also used to temporarily store the parsed data in a cache queue.

2. The method according to claim 1, characterized in that, Before the first processing thread calls the custom parser to parse the Excel file based on its version type to obtain the parsed data, the method further includes: A thread manager is created in the file processing system, wherein the thread manager is used to manage the creation and destruction of multiple processing threads by creating a custom thread pool; A cache queue is created in the file processing system, wherein the cache queue is used to temporarily store the parsed data; In the file processing system, a first processing thread and a second processing thread are created, wherein the first processing thread and the second processing thread execute processing tasks asynchronously. The first processing thread is used to parse the Excel file, and the second processing thread is used to process the parsed data.

3. The method according to claim 1, characterized in that, The step of using a second processing thread to write the parsed data into the target database includes: The second processing thread consumes the parsed data from the cache queue; The second processing thread stores the currently consumed parsed data into a temporary set to be written to the database, and determines whether the currently consumed parsed data is the last parsed data in the cache queue; Based on the judgment result, determine whether to write the parsed data stored in the temporary set into the target database.

4. The method according to claim 3, characterized in that, The step of determining whether to write the parsed data stored in the temporary set into the target database based on the judgment result includes: If the determination result is that the currently consumed parsed data is the last parsed data, then the parsed data stored in the temporary set is written into the target database; If the judgment result is that the currently consumed parsed data is not the last parsed data, then it is determined whether the number of parsed data stored in the temporary set meets the requirements for writing to the database. If the amount of parsed data stored in the temporary set meets the requirement for writing to the database, then the parsed data stored in the temporary set will be written to the target database.

5. The method according to claim 4, characterized in that, The step of writing the parsed data stored in the temporary set into the target database includes: A second processing thread is used to call the database access interface to write the parsed data stored in the temporary set into the target database in batches, or... The second processing thread calls the write command corresponding to the database characteristics of the target database to write the parsed data stored in the temporary set into the target database.

6. The method according to claim 1, characterized in that, If the version type is version 1, then the custom parser corresponding to the version type is invoked to parse the Excel file and obtain the parsed data, including: The first parser corresponding to the first version is invoked to read the Excel file according to the obtained file path, and the Excel file is converted into an input stream; A workbook corresponding to the Excel file is created through the input stream, and each table page contained in the Excel file is traversed in a loop to obtain the table page to be parsed; The table page to be parsed is parsed to obtain all rows of the table page; Loop through and parse all rows of the table page to be parsed; When all rows of the table page to be parsed are completed, the process returns to iterating through each table page contained in the Excel file until all rows of all table pages in the Excel file are completed. Then, the parsing is considered complete, and the parsed data is obtained.

7. The method according to claim 6, characterized in that, The loop traversal and parsing of all rows of the table page to be parsed includes: Analyze the current row among all rows of the table page to be parsed, and determine whether the current row is the first row among all rows; If the current row is the first row, then parse the title content of the table page to be parsed and store the title content; If the current row is not the first row, then iterate through and parse each cell in the current row, and put the parsed data obtained from parsing the current row into the cache queue.

8. The method according to claim 1, characterized in that, If the version type is version 2, then the custom parser corresponding to the version type is invoked to parse the Excel file and obtain the parsed data, including: The Excel file is read using a reading component based on the obtained file path; Register a second parser corresponding to the second version, and register a logic processing class for the second parser, wherein the logic processing class includes: a first parsing method for processing the content of the first cell to be read, and a second parsing method for processing the content of the last cell to be read; An Excel file reader is used to read each table page contained in the Excel file to obtain a table input stream; The table input stream is parsed using an Excel file reader to obtain all cells in each table page; The first parsing method is called to parse the cell. If the cell is empty, an empty value is constructed as the parsed data, and the parsed data is put into the cache queue until the last cell in the table page is parsed. When parsing the last cell in the table page, the second parsing method is called to parse the last cell, and the parsed data is placed into the cache queue.

9. The method according to claim 8, characterized in that, The method further includes: After parsing the last cell in the current table page, the process returns to reading the next table page contained in the Excel file using the Excel file reader, until all table pages have been parsed.

10. A file parsing and processing apparatus, characterized in that, include: The determination module is used to determine the amount of data in the received Excel file; The replacement module is used to replace the initial parser built into the file processing system with a custom parser if the file data volume is greater than a preset threshold. The custom parser is used to parse the non-empty cells in the Excel file according to the parsing requirements. The acquisition module is used by the first processing thread to call the custom parser to parse the Excel file according to the version type of the Excel file, and obtain the parsed data; The writing module is used to write the parsed data into the target database using a second processing thread. The acquisition module is further configured to use the first processing thread to determine the version type of the Excel file, wherein different version types correspond to different parsing processing methods; select the custom parser according to the version type; call the custom parser corresponding to the version type to parse the Excel file and obtain the parsed data; wherein the first processing thread is further configured to put the parsed data into a cache queue for temporary storage.

11. A non-volatile storage medium, characterized in that, The non-volatile storage medium stores multiple instructions, which are adapted to be loaded by a processor and executed by the file parsing processing method according to any one of claims 1 to 9.

12. An electronic device comprising a memory and a processor, characterized in that, The memory stores a computer program, and the processor is configured to run the computer program to perform the file parsing processing method according to any one of claims 1 to 9.

Images