CN113657084A

CN113657084A - Method and system for automatically reading Excel content

Info

Publication number: CN113657084A
Application number: CN202110989465.4A
Authority: CN
Inventors: 卢洪波; 蒋雨蒙
Original assignee: Individual
Current assignee: Individual
Priority date: 2021-08-26
Filing date: 2021-08-26
Publication date: 2021-11-16

Abstract

The invention provides a method and a system for automatically reading Excel contents, and relates to the field of data export. The method for automatically reading Excel content comprises the following steps: positioning an Excel file, determining the position of an Excel table to be processed, and dividing Excel data to be processed into a plurality of data groups; reading the same data group, independently displaying one data line of the data group, and stopping reading the Excel file when the last line of the Excel file is read and no data exists in the last line; and newly building an Excel file in the specified storage path, and writing the read records into the corresponding Excel file one by reading the data of a plurality of data groups in the Excel file. The method can improve the processing speed, reduce the workload of workers, reduce the error rate and automatically generate the Excel file according to the preset rule. The invention also provides a system for automatically reading the Excel content.

Description

Method and system for automatically reading Excel content

Technical Field

The invention relates to the field of data export, in particular to a method and a system for automatically reading Excel content.

Background

The Excel export function is the most basic and frequently applied function provided by various information systems, batch export is generally adopted for processing large-scale data export services, and the method consumes excessive service processing time and influences the development of subsequent service applications. The other extreme mode is 'one-time export and continuous waiting', and the method occupies too much memory resources, so that the system performance is reduced, and the user friendliness is reduced.

At present, the whole process is manually combed by auditors, and data are extracted or exported, and the existing method has the following defects:

1. in the sorting and arranging process of the data, the data needs to be manually judged according to experience and then arranged one by one, and time is consumed and accuracy is not high.

2. The Excel file information is manually exported, so that the workload of workers is overlarge, and errors are easily caused;

3. for a data processing robot program, too many segments need to be executed by running a corresponding number of robot program segments at the same time, so that the speed of reading and entering data is too slow.

Disclosure of Invention

The invention aims to provide a method for automatically reading Excel content, which can improve the processing speed, reduce the workload of workers, reduce the error rate and automatically generate an Excel file according to a preset rule.

Another object of the present invention is to provide a system for automatically reading Excel content, which is capable of operating a method for automatically reading Excel content.

The embodiment of the invention is realized by the following steps:

in a first aspect, an embodiment of the present application provides a method for automatically reading Excel content, including locating an Excel file, determining a position of an Excel table to be processed, and dividing Excel data to be processed into a plurality of data groups; reading the same data group, independently displaying one data line in the data group, judging when reading, automatically storing the read data after reading one line in the data group and automatically reading the next line; when the last line of the Excel file is read and no data exists in the last line, stopping reading the Excel file; and newly building an Excel file in the specified storage path, and writing the read records into the corresponding Excel file one by reading the data of a plurality of data groups in the Excel file.

In some embodiments of the present invention, the locating the Excel file, determining the position of the Excel table to be processed, and dividing the Excel data to be processed into a plurality of data groups includes: and positioning the Excel file through file name search, and identifying whether the Excel file is consistent with the file name of the Excel table to be processed.

In some embodiments of the present invention, the above further includes: the Excel data to be processed are acquired and then automatically stored as a first Excel data table, and then other Excel data tables are automatically generated in sequence.

In some embodiments of the present invention, the reading the same data group, and displaying the data group separately in a row, and determining when reading, and automatically saving the read data and automatically reading the next row after reading one row in the data group includes: classifying data of the Excel table to be processed, dividing the data into a plurality of data groups according to a preset rule, and independently reading each data group.

In some embodiments of the present invention, the stopping reading the Excel file when the last line of the Excel file is read and the last line has no data includes: if the content of the next row of data bytes in the data group is the same as that of the previous row of data bytes, the byte length, the start bit and the initial value are directly recorded behind the data.

In some embodiments of the present invention, the above further includes: and if the byte content of the next row of data is different from the byte content of the previous row of data, automatically storing the read data after reading one row in the data group and automatically reading the next row to generate new data information.

In some embodiments of the present invention, creating an Excel file in the specified storage path, and writing the read records into the corresponding Excel file one by reading data of a plurality of data sets in the Excel file includes: and generating a plurality of Excel files under the specified storage path, overlapping the Excel files to form a complete Excel file, and checking the generated Excel files under the storage path.

In a second aspect, an embodiment of the present application provides a system for automatically reading Excel content, which includes a table partitioning module, configured to locate an Excel file, determine a position of an Excel table to be processed, and divide Excel data to be processed into a plurality of data groups;

the data reading module is used for reading the same data group, independently displaying one data line in the data group, judging when reading, automatically storing the read data after reading one line in the data group and automatically reading the next line;

the reading stopping module is used for stopping reading the Excel file when the last line of the Excel file is read and no data exists in the last line;

and the new table generating module is used for newly building an Excel file in the specified storage path, and reading records and writing the read records into the corresponding Excel file one by reading the data of a plurality of data groups in the Excel file.

In some embodiments of the invention, the above includes: at least one memory for storing computer instructions; at least one processor in communication with the memory, wherein the at least one processor, when executing the computer instructions, causes the system to: the device comprises a table dividing module, a data reading module, a reading stopping module and a new table generating module.

In a third aspect, embodiments of the present application provide a computer-readable storage medium, on which a computer program is stored, which, when executed by a processor, implements a method such as any one of the methods for automatically reading Excel content.

Compared with the prior art, the embodiment of the invention has at least the following advantages or beneficial effects:

the method can improve the processing speed, reduce the workload of workers, reduce the error rate and automatically generate the Excel file according to the preset rule. The data query and file generation processes of the Excel data export service are separated, files occupying large time and memory are generated and delivered to a background Excel processing robot program for processing, the service processing time is short, the development of subsequent service application is facilitated, the memory resources occupying a single processor are small, the system performance is not influenced, and the use friendliness of workers is improved. The data processing robot program simulates manual data reading and inputting, and the reading and inputting speed is effectively improved by adopting a mode of reading one line of data at a time and inputting one line of data at a time in the process of importing data from an Excel table into a plurality of new Excel tables.

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 embodiments will be briefly described below, it should be understood that the following drawings only illustrate some embodiments of the present invention and therefore should not be considered as limiting the scope, and for those skilled in the art, other related drawings can be obtained according to the drawings without inventive efforts.

Fig. 1 is a schematic diagram illustrating steps of a method for automatically reading Excel content according to an embodiment of the present invention;

FIG. 2 is a detailed step diagram of a method for automatically reading Excel content according to an embodiment of the present invention;

fig. 3 is a schematic diagram of a system module for automatically reading Excel content according to an embodiment of the present invention;

fig. 4 is an electronic device according to an embodiment of the present invention;

FIG. 5 is a schematic diagram of raw data derivation according to an embodiment of the present invention;

fig. 6 is a schematic diagram of generating a new Excel file according to an embodiment of the present invention.

Icon: 10-a table partitioning module; 20-a data reading module; 30-stop reading module; 40-generate new table module; 101-a memory; 102-a processor; 103-communication interface.

Detailed Description

In order to make the objects, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are some embodiments of the present application, but not all embodiments. The components of the embodiments of the present application, generally described and illustrated in the figures herein, can be arranged and designed in a wide variety of different configurations.

Thus, the following detailed description of the embodiments of the present application, presented in the accompanying drawings, is not intended to limit the scope of the claimed application, but is merely representative of selected embodiments of the application. 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 application.

It should be noted that: like reference numbers and letters refer to like items in the following figures, and thus, once an item is defined in one figure, it need not be further defined and explained in subsequent figures.

It is to be noted that the term "comprises," "comprising," or any other variation thereof is intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

Some embodiments of the present application will be described in detail below with reference to the accompanying drawings. The embodiments described below and the individual features of the embodiments can be combined with one another without conflict.

Example 1

Referring to fig. 1, fig. 5 and fig. 6, fig. 1 is a schematic diagram illustrating steps of a method for automatically reading Excel content according to an embodiment of the present invention, and the steps are as follows:

s100, positioning an Excel file, determining the position of an Excel table to be processed, and dividing Excel data to be processed into a plurality of data groups;

in some embodiments, the basic information in other tables is obtained and automatically saved one by one in the determined format (blank in the format to be supplemented).

Step S110, reading the same data group, independently displaying one data line for each data line of the data group, judging when reading, automatically storing the read data after reading one line in the data group and automatically reading the next line;

in some embodiments, the information of the nail is automatically saved as a form of the nail after being acquired, and forms of other persons are automatically generated in turn.

In some embodiments, after the reading of the data of each row is completed, the read data is saved.

Step S120, when the last line of the Excel file is read and no data exists in the last line, the Excel file is stopped to be read;

step S130, creating a new Excel file in the specified storage path, and writing the read records into the corresponding Excel file one by reading the data of the data sets in the Excel file.

In some embodiments, the location of the information in the registry, such as 'A', '331082198512234567', 'Linhai City east Dai No. 1', '8-1-10111', is determined by taking A as an example.

The positions of the 'A', '331082198512234567', 'just Hai east Dai No. 1', '8-1-10111' in the 'list' are found, and the information is automatically written into the 'registry' through a written program and is stored as a form named as 'A'.

Example 2

Referring to fig. 2, fig. 2 is a detailed step diagram of a method for automatically reading Excel content according to an embodiment of the present invention, which is shown as follows:

and S200, positioning the Excel file through file name search, and identifying whether the Excel file is consistent with the file name of the Excel table to be processed.

Step S210, automatically saving the Excel data to be processed as a first Excel data table after obtaining the Excel data, and then automatically generating other Excel data tables in sequence.

And step S220, classifying the data of the Excel table to be processed, dividing the data into a plurality of data groups according to a preset rule, and independently reading each data group.

In step S230, if the content of the next row of data bytes in the data set is the same as the content of the previous row of data bytes, the byte length, the start bit and the initial value are recorded directly after the data.

In step S240, if the byte content of the next row of data is different from the byte content of the previous row of data, the read data is automatically saved after reading one row of the data group, and the next row is automatically read, so as to generate new data information.

Step S250, generating a plurality of Excel files under the specified storage path, overlapping the plurality of Excel files to form a complete Excel file, and checking the generated plurality of Excel files under the storage path.

In some embodiments, an Excel data processing robot program divides Excel data into data groups; when the application system sends an Excel data export request to the Excel data processing robot program, the Excel data processing robot program provides the Excel data export request to the multiple processors; when each processor processes an Excel data export request, an Excel file is newly built in a designated storage path, and reading records are written into the corresponding Excel file one by accessing a database; forming a plurality of Excel files under the designated storage path by a plurality of processors; and the user checks and downloads the generated Excel file under the storage path.

The application system sends an Excel data export request to an Excel data processing robot program through a Webservice interface. In order to reduce the complexity of the structure, the Excel data processing robot program divides Excel data into data groups with the same number as processors, one processor exports the data of one data group, and a plurality of processors asynchronously exports all the data of an Excel file.

In some embodiments, the data robot simulates a process of manually operating a computer by a human, automatically opens the Excel table to be processed, automatically reads all Excel data in the Excel table, identifies a data being entered at the moment according to an entry progress, determines the position of the data in a row, namely identifies the row of the data in which the data is located by taking row data as a unit, and determines the position of the row in the whole Excel table.

It should be noted that the entry progress in this embodiment is that after the Excel table is automatically opened, since the data in the Excel table is read first, and then the data in the Excel table is entered into a new Excel file from the Excel table, there are two cases in this process under normal conditions, the first case is that the entry state is not entered at all, that is, the entry progress is zero, and at this time, the entry state needs to be read from the data at the beginning of the Excel table and entered into a new Excel file. And the second method is that data is being recorded, but the recording of all data is not completed, at the moment, the data being read or recorded in the Excel table is automatically identified according to the recording progress of a new Excel file, and the reading and recording are continued from the data until the recording of all data in the Excel table is completed.

Example 3

Referring to fig. 3, fig. 3 is a schematic diagram of a system module for automatically reading Excel content according to an embodiment of the present invention, which is shown as follows:

the table dividing module 10 is used for positioning the Excel file, determining the position of the Excel table to be processed, and dividing the Excel data to be processed into a plurality of data groups;

the data reading module 20 is configured to read the same data group, perform one-line data separate display on the data group, perform judgment during reading, automatically save the read data after reading one line in the data group, and automatically read the next line;

the reading stopping module 30 is used for stopping reading the Excel file when the last line of the Excel file is read and no data exists in the last line;

and the new table generating module 40 is used for newly building an Excel file in the specified storage path, and reading records and writing the data of a plurality of data groups in the Excel file into the corresponding Excel file one by one.

As shown in fig. 4, an embodiment of the present application provides an electronic device, which includes a memory 101 for storing one or more programs; a processor 102. The one or more programs, when executed by the processor 102, implement the method of any of the first aspects as described above.

Also included is a communication interface 103, and the memory 101, processor 102 and communication interface 103 are electrically connected to each other, directly or indirectly, to enable transfer or interaction of data. For example, the components may be electrically connected to each other via one or more communication buses or signal lines. The memory 101 may be used to store software programs and modules, and the processor 102 executes the software programs and modules stored in the memory 101 to thereby execute various functional applications and data processing. The communication interface 103 may be used for communicating signaling or data with other node devices.

The Memory 101 may be, but is not limited to, a Random Access Memory 101 (RAM), a Read Only Memory 101 (ROM), a Programmable Read Only Memory 101 (PROM), an Erasable Read Only Memory 101 (EPROM), an electrically Erasable Read Only Memory 101 (EEPROM), and the like.

The processor 102 may be an integrated circuit chip having signal processing capabilities. The Processor 102 may be a general-purpose Processor 102, including a Central Processing Unit (CPU) 102, a Network Processor 102 (NP), and the like; but may also be a Digital Signal processor 102 (DSP), an Application Specific Integrated Circuit (ASIC), a Field Programmable Gate Array (FPGA) or other Programmable logic device, discrete Gate or transistor logic device, discrete hardware components.

In the embodiments provided in the present application, it should be understood that the disclosed method and system and method can be implemented in other ways. The method and system embodiments described above are merely illustrative, for example, the flowcharts and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of methods and systems, methods and computer program products according to various embodiments of the present application. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

In addition, functional modules in the embodiments of the present application may be integrated together to form an independent part, or each module may exist separately, or two or more modules may be integrated to form an independent part.

In another aspect, embodiments of the present application provide a computer-readable storage medium, on which a computer program is stored, which, when executed by the processor 102, implements the method according to any one of the first aspect described above. The functions, if implemented in the form of software functional modules and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present application or portions thereof that substantially contribute to the prior art may be embodied in the form of a software product stored in a storage medium and including instructions for causing a computer device (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 application. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory 101 (ROM), a Random Access Memory 101 (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.

In summary, the method and system for automatically reading the Excel content provided by the embodiment of the application can improve the processing speed, reduce the workload of workers, reduce the error rate, and automatically generate the Excel file according to the preset rule. The data query and file generation processes of the Excel data export service are separated, files occupying large time and memory are generated and delivered to a background Excel processing robot program for processing, the service processing time is short, the development of subsequent service application is facilitated, the memory resources occupying a single processor are small, the system performance is not influenced, and the use friendliness of workers is improved. The data processing robot program simulates manual data reading and inputting, and the reading and inputting speed is effectively improved by adopting a mode of reading one line of data at a time and inputting one line of data at a time in the process of importing data from an Excel table into a plurality of new Excel tables.

The above is only a preferred embodiment of the present invention, and is not intended to limit the present invention, and various modifications and changes will occur to those skilled in the art. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

It will be evident to those skilled in the art that the present application is not limited to the details of the foregoing illustrative embodiments, and that the present application may be embodied in other specific forms without departing from the spirit or essential attributes thereof. The present embodiments are therefore to be considered in all respects as illustrative and not restrictive, the scope of the application being indicated by the appended claims rather than by the foregoing description, and all changes which come within the meaning and range of equivalency of the claims are therefore intended to be embraced therein. Any reference sign in a claim should not be construed as limiting the claim concerned.

Claims

1. A method for automatically reading Excel content is characterized by comprising the following steps:

positioning an Excel file, determining the position of an Excel table to be processed, and dividing Excel data to be processed into a plurality of data groups;

reading the same data group, independently displaying one data line in the data group, judging when reading, automatically storing the read data after reading one line in the data group and automatically reading the next line;

when the last line of the Excel file is read and no data exists in the last line, stopping reading the Excel file;

and newly building an Excel file in the specified storage path, and writing the read records into the corresponding Excel file one by reading the data of a plurality of data groups in the Excel file.

2. The method according to claim 1, wherein the positioning the Excel file, determining the position of the Excel table to be processed, and dividing the Excel data to be processed into a plurality of data groups comprises:

and positioning the Excel file through file name search, and identifying whether the Excel file is consistent with the file name of the Excel table to be processed.

3. The method for automatically reading Excel contents according to claim 2, further comprising:

the Excel data to be processed are acquired and then automatically stored as a first Excel data table, and then other Excel data tables are automatically generated in sequence.

4. The method as claimed in claim 1, wherein the reading of the same data group, the displaying of the data group in a row, the determining during the reading, and the automatic saving of the read data and the automatic reading of the next row after the reading of one row in the data group comprise:

classifying data of the Excel table to be processed, dividing the data into a plurality of data groups according to a preset rule, and independently reading each data group.

5. The method according to claim 1, wherein stopping reading the Excel file when the last line of the Excel file is read and the last line has no data comprises:

if the content of the next row of data bytes in the data group is the same as that of the previous row of data bytes, the byte length, the start bit and the initial value are directly recorded behind the data.

6. The method for automatically reading Excel contents according to claim 5, further comprising:

and if the byte content of the next row of data is different from the byte content of the previous row of data, automatically storing the read data after reading one row in the data group and automatically reading the next row to generate new data information.

7. The method according to claim 1, wherein the creating of an Excel file in the designated storage path, the writing of the read records into the corresponding Excel file one by reading data of a plurality of data sets in the Excel file comprises:

and generating a plurality of Excel files under the specified storage path, overlapping the Excel files to form a complete Excel file, and checking the generated Excel files under the storage path.

8. A system for automatically reading Excel content, comprising:

the table dividing module is used for positioning the Excel file, determining the position of an Excel table to be processed and dividing Excel data to be processed into a plurality of data groups;

9. The system for automatically reading Excel contents according to claim 8, comprising:

at least one memory for storing computer instructions;

at least one processor in communication with the memory, wherein the at least one processor, when executing the computer instructions, causes the system to perform: the device comprises a table dividing module, a data reading module, a reading stopping module and a new table generating module.

10. A computer-readable storage medium, on which a computer program is stored which, when being executed by a processor, carries out the method according to any one of claims 1-7.