CN111736805B - Method and device for processing Excel table - Google Patents
Method and device for processing Excel table Download PDFInfo
- Publication number
- CN111736805B CN111736805B CN201910227888.5A CN201910227888A CN111736805B CN 111736805 B CN111736805 B CN 111736805B CN 201910227888 A CN201910227888 A CN 201910227888A CN 111736805 B CN111736805 B CN 111736805B
- Authority
- CN
- China
- Prior art keywords
- cell
- excel table
- data
- excel
- processing
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000012545 processing Methods 0.000 title claims abstract description 141
- 238000000034 method Methods 0.000 title claims abstract description 67
- 238000006243 chemical reaction Methods 0.000 claims description 20
- 238000012795 verification Methods 0.000 claims description 20
- 238000004590 computer program Methods 0.000 claims description 9
- 230000008569 process Effects 0.000 description 15
- 238000010586 diagram Methods 0.000 description 8
- 238000004458 analytical method Methods 0.000 description 7
- 238000004891 communication Methods 0.000 description 6
- 230000006870 function Effects 0.000 description 5
- 230000003287 optical effect Effects 0.000 description 4
- 238000012986 modification Methods 0.000 description 3
- 230000004048 modification Effects 0.000 description 3
- 230000009471 action Effects 0.000 description 2
- 230000008901 benefit Effects 0.000 description 2
- 239000000835 fiber Substances 0.000 description 2
- 230000000644 propagated effect Effects 0.000 description 2
- 239000004065 semiconductor Substances 0.000 description 2
- 238000006467 substitution reaction Methods 0.000 description 2
- 238000013461 design Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 239000004973 liquid crystal related substance Substances 0.000 description 1
- 239000013307 optical fiber Substances 0.000 description 1
- 238000003672 processing method Methods 0.000 description 1
- 238000011282 treatment Methods 0.000 description 1
- 239000002699 waste material Substances 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/20—Software design
- G06F8/24—Object-oriented
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/70—Software maintenance or management
- G06F8/72—Code refactoring
Landscapes
- Engineering & Computer Science (AREA)
- Software Systems (AREA)
- General Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Document Processing Apparatus (AREA)
Abstract
The invention provides a method and a device for processing an Excel table, and relates to the technical field of data processing. One embodiment of the method comprises the following steps: s101, invoking a template class for an Excel table by the data to be processed in an annotation mode; s102, processing the data to be processed according to the template class and the unit class. According to the embodiment, the universal list part of the Excel table can be processed, the personalized part of the Excel table can be processed, hard coding is not needed, a processing program is saved, and the processing efficiency is improved.
Description
Technical Field
The invention relates to the field of data processing, in particular to a method and a device for processing an Excel table.
Background
Excel tables are commonly used as a data report, and generally require writing, parsing, importing, exporting, and other processing operations. The current common processing method is to use API (Application Programming Interface), i.e. the class and method encapsulated in the application programming interface, to implement the processing operation on the Excel table. In the actual operation process, the Excel table is found to contain not only a general list part, but also a personalized part for carrying out merging, splitting and other treatments according to actual requirements, and the problem that the personalized part needs to be treated respectively exists.
Currently, for an Excel table containing a general list part, when processing operations such as analysis, writing and the like are performed on the Excel table, the processing operations on the Excel table can be realized by using a common code, and the common code can be reused; when the Excel form containing the personalized part is subjected to processing operations such as analysis, writing and the like, the processing operation on the Excel form needs to be independently realized in a hard coding mode, and codes of the hard coding part cannot be reused, so that the complexity of a processing program is increased, and a certain resource waste is caused.
Disclosure of Invention
In view of this, the embodiment of the invention provides a method and a device for processing an Excel table, which can process an individualized part of the Excel table, process a general list part of the Excel table, and repeatedly use codes for processing the Excel table.
To achieve the above object, according to a first aspect of the embodiments of the present invention, there is provided a method for processing an Excel table, including the steps of: invoking a template class for the Excel table by annotating data to be processed, wherein the template class indicates a template and a cell class for the Excel table as attributes of the template class, and the cell class indicates position information and types of cells composing the Excel table; and processing the data to be processed by using classes and methods encapsulated in an API for processing the Excel table according to the template class and the unit class.
Optionally, the type of the cell indicates that the cell is one of: cells, rows, and sets of cells of an Excel table. In the case that the type of the cell indicates that the cell is a cell of the Excel table, the location information of the cell includes an abscissa and an ordinate of the cell; in the case that the type of the cell indicates that the cell is a row of the Excel table, the location information of the cell includes a start row number of the row; in the case where the type of the cell indicates that the cell is a set of cells of the Excel table, the location information of the cell includes a start row number, a start column number, a number of row merges, and a number of column merges of the set of cells.
Optionally, the data to be processed is an Excel table; the processing is to parse the Excel table into character strings or text.
Optionally, the data to be processed is a character string or text; the processing is writing the character string or text into an Excel table.
Optionally, in the case that the type of the cell indicates that the cell is a cell of an Excel table, according to an abscissa and an ordinate of the cell, the character string or the text is parsed from the cell; in the case that the type of the cell indicates that the cell is a row of an Excel table, according to the starting row number of the row, the character string or the text is analyzed from the row; and under the condition that the type of the cell indicates that the cell is a set of cells of an Excel table, analyzing the character string or the text from the set of cells according to the starting row number, the starting column number, the row merging number and the column merging number of the set of cells.
Optionally, in the case that the type of the cell indicates that the cell is a cell of an Excel table, writing the character string or text into the cell according to the abscissa and the ordinate of the cell; writing the character string or text into the row according to the starting row number of the row when the type of the cell indicates that the cell is the row of the Excel table; and writing the character string or the text into the set of cells according to the starting row number, the starting column number, the row merging number and the column merging number of the set of cells when the type of the cell indicates that the cell is the set of cells of the Excel table.
Optionally, the template class further indicates a verifier corresponding to the Excel table, the verifier indicating a data rule, a verification object reference, a hint information applied to the cell.
Optionally, when the processing is to parse the Excel table into a character string or a text, converting the data to be processed parsed in the unit into a predetermined data type; and when the processing is writing the character string or the text into an Excel table, converting the data to be processed into a predetermined data type and writing the predetermined data type into a unit.
Optionally, the API for the Excel sheet is an Apache POI.
To achieve the above object, according to a second aspect of the embodiments of the present invention, there is provided an apparatus for processing an Excel table, including: a calling module and a processing module; the calling module is used for calling the template class for the Excel table by the data to be processed in an annotating mode; wherein the template class indicates a template for the Excel table and a cell class indicating position information and types of cells constituting the Excel table as attributes of the template class; and the processing module is used for processing the data to be processed by using classes and methods encapsulated in the API for processing the Excel table according to the template class and the unit class called by the calling module.
Optionally, the type of the cell indicates that the cell is one of: a set of cells, rows, cells of an Excel table; in the case that the type of the cell indicates that the cell is a cell of the Excel table, the location information of the cell includes an abscissa and an ordinate of the cell; in the case that the type of the cell indicates that the cell is a row of the Excel table, the location information of the cell includes a start row number of the row; in the case where the type of the cell indicates that the cell is a set of cells of the Excel table, the location information of the cell includes a start row number, a start column number, a number of row merges, and a number of column merges of the set of cells.
Optionally, the data to be processed is an Excel table; the processing module is used for analyzing the Excel table into character strings or texts.
Optionally, the data to be processed is a character string or text; the processing module is used for writing the character string or the text into an Excel table.
Optionally, in the case that the type of the cell indicates that the cell is a cell of an Excel table, the processing module is configured to parse the character string or the text from the cell according to an abscissa and an ordinate of the cell; in the case that the type of the cell indicates that the cell is a row of an Excel table, according to a starting row number of the row, the processing module is used for analyzing the character string or the text from the row; and in the case that the type of the cell indicates that the cell is a set of cells of an Excel table, according to a starting row number, a starting column number, a row merging number and a column merging number of the set of cells, the processing module is used for analyzing the character string or the text from the set of cells.
Optionally, in the case that the type of the cell indicates that the cell is a cell of an Excel table, the processing module is configured to write the character string or the text into the cell according to an abscissa and an ordinate of the cell; in the case that the type of the cell indicates that the cell is a row of an Excel table, the processing module is configured to write the character string or text into the row according to a starting row number of the row; in the case that the type of the cell indicates that the cell is a set of cells of an Excel table, the processing module is configured to write the character string or text into the set of cells according to a start row number, a start column number, a row merging number, and a column merging number of the set of cells.
Optionally, the template class further indicates a verifier corresponding to the Excel table, the verifier indicating a data rule, a verification object reference, a hint information applied to the cell.
Optionally, the processing module is configured to parse the Excel table into a character string or a text, and the format converter is configured to convert the data to be processed parsed in the unit into a predetermined data type; when the processing of the processing module is writing the character string or the text into an Excel table, the format converter is used for converting the data to be processed into a preset data type and then writing the data into the Excel table unit.
Optionally, the API for the Excel sheet is an Apache POI.
To achieve the above object, according to a third aspect of the embodiments of the present invention, there is provided a server for processing an Excel table, comprising: one or more processors; and a storage device for storing one or more programs that, when executed by the one or more processors, cause the one or more processors to implement the method as described in any of the methods of processing Excel sheets described above.
To achieve the above object, according to a fourth aspect of the embodiments of the present invention, there is provided a computer-readable medium having stored thereon a computer program, characterized in that the program, when executed by a processor, implements a method as described in any one of the above methods of processing an Excel table.
One embodiment of the above invention has the following advantages or benefits: when the Excel table is processed, the general list part of the Excel table can be processed, and the personalized combined or split cells of the Excel table can be processed; the personalized part does not need to independently write codes for processing, and the codes can be reused; the method saves the program for processing the Excel form, reduces the processing difficulty, improves the processing efficiency and saves the resources to a certain extent; in addition, the technical scheme provided by the embodiment of the invention can also be used for processing the CSV form table, and has wide universality.
Further effects of the above-described non-conventional alternatives are described below in connection with the embodiments.
Drawings
In order to more clearly illustrate the technical solutions of the disclosed embodiments of the present invention, the drawings that need to be used in the description of the embodiments will be briefly introduced below, and it is obvious that the drawings in the following description are only some embodiments of the disclosure of the present invention, and other drawings can be obtained according to these drawings without inventive effort for a person skilled in the art.
FIGS. 1a and 1b are a flowchart of a method for processing an Excel table and a corresponding annotation relationship according to an embodiment of the present invention;
FIG. 2 is a flow chart of a method of processing an Excel sheet provided in accordance with another embodiment of the present invention;
FIG. 3 is a flow chart of a method of processing an Excel sheet provided in accordance with yet another embodiment of the present invention;
FIG. 4 is a schematic diagram of an apparatus for processing Excel sheets according to still another embodiment of the present invention;
FIG. 5 is an exemplary system architecture diagram in which embodiments of the present invention may be applied;
fig. 6 is a schematic diagram of a computer system suitable for use in implementing an embodiment of the invention.
Detailed Description
The following description of the embodiments of the present invention will be made clearly and completely with reference to the accompanying drawings, in which it is apparent that the embodiments described are only some embodiments of the present invention, but not all embodiments. The following description of at least one exemplary embodiment is merely exemplary in nature and is in no way intended to limit the invention, its application, or uses. Also, for clarity and conciseness, descriptions of well-known techniques are omitted from the following description. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
Example 1
Referring to fig. 1a and 1b, an embodiment of the present invention provides a method for processing an Excel table, and a corresponding annotation relationship, where the method may include the following steps:
step S101: invoking a template class for the Excel table by means of annotation.
Referring to fig. 1b, a template class acts on class level to mark a class as an Excel template class, and indicates the name of the Excel table and a unit class as an attribute of the template class; and a cell class acting on an attribute field of the Excel template class, indicating attributes of cells constituting an Excel table, for corresponding to one cell of the Excel table, and including type and location information of the cell.
The type of cell indicates that the cell is one of a cell, a row, or a set of cells of an Excel table. In the case that the type of the cell indicates that the cell is a cell of an Excel table, the position information of the corresponding cell includes an abscissa and an ordinate of the cell; in the case where the type of the cell indicates that the cell is a row of an Excel table, the location information of the cell includes a start row number for processing the Excel table; in the case where the type of the cell indicates that the cell is a column of an Excel table, the location information of the cell includes a start column number for processing the Excel table; in the case where the type of the cell indicates that the cell is a set of cells of the Excel table, the location information of the cell includes a start row number, a start column number, a number of row merges, a number of column merges of the set of cells.
Optionally, the template class further indicates a verifier corresponding to the Excel table, for verifying validity of the data; the cell class indicates a verification format for a verifier; the verifier indicates a data rule, a verification object reference and a prompt message applied to the unit; the check format is a regular expression.
Optionally, the unit class further indicates a predetermined data type for converting a data format, including a data type and a data format; the data type is the data type of the unit during storage, the default is character strings, and the data format is the data format of the unit during storage;
step S102: and processing the data to be processed according to the template class and the unit class.
In the case of parsing an Excel table into a character string or text, the data to be processed is the Excel table, and according to different type attributes of the cells indicated in the cell class, using an Excel parsing API, and according to the position information of the cells, respectively acquiring the data of the cells with specified coordinates from the Excel table:
extracting data from cells with specified coordinates according to the abscissa and the ordinate of the cells under the condition that the type of the cells indicates that the cells are cells of an Excel table, and filling the data into a template object; extracting data from a row with specified coordinates according to the starting row number of the row and filling the data into a template object under the condition that the type of the cell indicates that the cell is a row of an Excel table; and extracting data from the set of cells with specified coordinates and filling the data into a template object according to the starting row number, the starting column number, the row merging number and the column merging number of the set of cells when the type of the cell indicates that the cell is the set of cells of the Excel table.
After all the different types of units sequentially complete the processing procedure, the template object, i.e. the character string or the text, containing the data of the processed Excel form can be returned.
In the case of writing a character string or text into an Excel table, the data to be processed is the character string or text, an Excel writing API is used according to different type attributes of the cells indicated in the cell class, and the data in the character string or text are respectively written into the cells of the specified coordinates of the Excel table according to the position information of the cells:
writing the character string or the text into the cell according to the abscissa and the ordinate of the cell when the type of the cell indicates that the cell is the cell of an Excel table; writing the character string or text into the row according to the starting row number of the row when the type of the cell indicates that the cell is the row of the Excel table; and writing the character string or the text into the set of cells according to the starting row number, the starting column number, the row merging number and the column merging number of the set of cells when the type of the cell indicates that the cell is the set of cells of the Excel table.
After all the different types of units sequentially complete the processing procedure, the finally obtained Excel table can be returned.
Optionally, the validity of the data to be processed can also be checked. Referring to fig. 1b, a data checker is obtained according to a template class, and data to be processed is checked according to a data check format indicated in a unit class; using the data checker to check whether the data of the current unit to be processed referenced by the check object is matched with the regular expression; if the data is matched, the data validity check is passed, and the data of the current unit to be processed is subjected to subsequent processing; if the data is not matched with the data, the data validity check is not passed, a prompt message is sent when the data validity check is not passed, and the data of the next unit to be processed is continuously processed. If the verification is passed, the data after the verification is filled into the template object and returned, and if the verification is not passed, the data of the next Excel table unit is continuously processed.
Optionally, the format of the data to be processed may also be converted. Referring to fig. 1b, according to the data type and data format indicated in the unit class, format conversion is performed on the data to be processed, and subsequent processing is performed on the converted data. If the Excel table is analyzed, the data in the Excel table unit is subjected to format conversion, and then is filled into the template object and returned; in the case of writing an Excel table, the format-converted data is written into the Excel table.
Optionally, the above operation is performed on the Excel table by using the class and method encapsulated in the Apache POI.
Optionally, a context for transferring additional data, which is a carrier carrying data, may be invoked, and any data may be stored for the subsequent processing operation to take the result of the previous processing operation.
Example two
Referring to fig. 2 and 1b, another embodiment of the present invention provides a method for processing an Excel table, and corresponding annotation, where the method may include the following steps:
step S201: invoking a template class for the Excel table by means of annotation.
And calling an analysis inlet of the Excel table, and processing the Excel table by using an Excel analysis API, such as classes and methods encapsulated in an Apache POI. Calling an analytical method parameter of ExcelService, and transmitting 3 parameters for the analytical method parameter: excel file stream, excel template class (template), delivers context of additional data. The Excel table provided by the Excel file stream is the data to be processed.
And obtaining a parser (excelParser) for the template class from a parser factory (excelParserFactor) packaged in the Apache POI, and performing parsing processing on data in an Excel table. When custom parsing is required, custom parsing of an Excel table can also be achieved by providing two classes of custom, namely, a parser factory (excelParserFactor) and a parser (excelParser).
And according to the template class, acquiring the annotation corresponding to the template class. Referring to fig. 2, the template class indicates the name of an Excel table, i.e., indicates a template for the Excel table; the template class also indicates the unit class of the attribute field of the action template class and creates a verifier; the created verifier is a data verifier (verifier) for verifying the validity of the data; the cell class is used for corresponding to one cell of Excel, and indicates the type and position information, the data format and type (CellType) and the verification format (validRule) of the cell. Wherein, the type and position information of the cell can be used for positioning the Excel table cell so as to process the Excel table cell; the data format and type specify the preset format of the data for realizing the conversion of the data format; and the verification format is used for providing data validity verification format for the data verifier.
Step S202: and verifying the validity of the data to be processed according to the template class and the unit class.
And acquiring a data verifier (Validator) from a verifier factory (Validator) packaged in the Apache POI according to the annotation information indicated by the template class.
According to the type of the cell indicated in the cell class and the position information of the corresponding cell, using the parser (excelParser) obtained from the Apache POI, obtaining the data (cellValue) of the currently specified coordinate cell from the Excel table: extracting data (cellValue) from a cell of a specified coordinate according to the abscissa and the ordinate of the cell when the type of the cell indicates that the cell is a cell of an Excel table; extracting data (cellValue) from a row of a specified coordinate according to a starting row number of the row in the case that the type of the cell indicates that the cell is a row of an Excel table; in the case that the type of the cell indicates that the cell is a set of cells of an Excel table, data (cellValue) is extracted from the set of cells of a specified coordinate according to a start line number, a start column number, a line merging number, and a column merging number of the set of cells.
Using the data verifier (verifier) obtained above, the validity of the data (cellValue) currently extracted from the cell of the specified coordinates is verified according to the data validity verification format (valid rule) indicated in the cell class. Referring to fig. 2, a verification format (valid rule) indicates a data rule, a verification object reference, and hint information; the data rule is a regular expression, the prompt information is the prompt information sent when the data validity check is not passed, and the check object reference indicates the current data (cellValue) to be checked extracted from the unit. If the current data (cellValue) to be checked is matched with the regular expression indicated by the check format (validRule), the data validity check is passed, and the data (cellValue) after the check is passed can be subjected to subsequent processing; if the current data (cellValue) to be checked is not matched with the regular expression indicated by the check format (validRule), the data validity check is different, prompt information is sent out when the data check fails, and the next current data (cellValue) to be checked is continuously processed.
Step S203: and carrying out format conversion on the data to be processed according to the template class and the unit class.
And obtaining a format converter (converter) from a format converter factory (converteFactor) packaged in the Apache POI according to the corresponding data type (CellType) indicated in the cell class for the data (cellValue) passing the validity check.
And according to the data format indicated in the cell class, converting the data (cellValue) passing through the validity verification into a format required during storage by using the obtained format converter (converter), and obtaining the data after format conversion.
Step S204: and processing the data to be processed according to the template class and the unit class.
Filling the obtained data after format conversion into a template object;
and repeatedly executing the operation until all the units of different types complete the data validity verification and format conversion, filling the data after the format conversion into a template object, obtaining a template object containing the processed Excel form data, namely a character string or a file, and returning the obtained character string or file.
Example III
Referring to fig. 3 and 1b, another embodiment of the present invention provides a method for processing an Excel table, and corresponding annotation, where the method may include the following steps:
Step S301: invoking a template class for the Excel table by means of annotation.
And calling a write-in entry of the Excel table, and processing the Excel table by using an Excel write API, such as classes and methods encapsulated in an Apache POI. Calling a write method write of ExcelService, and transferring 2 parameters for the write method write: the data (data) that needs to be written, the context (context) of the extra data is transferred. The data to be written is the data to be processed, is of a template type, and can be a character string or a text.
A writer (ExcelWriter) for the template class is acquired from a writer factory (ExcelWriter factory) packaged in the Apache POI for writing processing of data in the Excel table. When custom writing is required, custom writing to an Excel table can also be achieved by providing the two classes of customization, namely, writer factory (ExcelWriter) and writer (ExcelWriter).
And writing the template class corresponding to the data according to the requirement, and acquiring the annotation corresponding to the template class. Referring to fig. 2, the template class indicates the name of an Excel table, i.e., indicates a template for the Excel table; the template class also indicates a cell class acting on an attribute field of the template class for corresponding to one cell of Excel, indicating the type and location information of the cell, data format, and data type (dataType). Wherein, the type and position information of the cell can be used for positioning the Excel table cell so as to process the Excel table cell; the data format and type (dataType) specify a predetermined format of data for implementing conversion of the data format, and the data type defaults to a string.
Step S302: and carrying out format conversion on the data to be processed according to the template class and the unit class.
Traversing the cell type field indicated by the cell class in the template, and respectively carrying out data format conversion on the data which needs to be written into Excel table cells of different types.
And acquiring a format converter (converter) from a format converter factory (converteFactor) packaged in the Apache POI according to a data type (dataType) of the data which is required to be written currently in the unit class, and performing data format conversion.
And according to the data format indicated in the cell class, converting the data (cellvalue) to be written into the format required by storage currently by using the obtained format converter (converter), and obtaining the data after format conversion.
Step S303: and processing the data to be processed according to the template class and the unit class.
Writing the obtained data after format conversion into an Excel table unit by using the writer (ExcelWriter) obtained in step S301 according to the cell type and the position information indicated in the corresponding cell class: writing the data after format conversion into the corresponding cells according to the abscissa and the ordinate of the cells under the condition that the type of the cells indicates that the cells are cells of an Excel table; writing the data after format conversion into a corresponding row according to the initial row number of the row under the condition that the type of the cell indicates that the cell is the row of the Excel table; and under the condition that the type of the cell indicates that the cell is a set of cells of an Excel table, writing the data after format conversion into the corresponding set of cells according to the starting row number, the starting column number, the row merging number and the column merging number of the set of cells.
And repeatedly executing the format conversion and the writing operation until all the units of different types indicated by the unit types in the template are processed completely, obtaining an Excel table containing data to be written, namely an Excel corresponding file stream, and returning.
Example IV
Referring to fig. 4, another embodiment of the present invention provides an apparatus 400 for processing an Excel table, which may specifically include: a calling module 401 and a processing module 402; wherein,
the calling module 401 is configured to call, by means of annotation, a template class for the Excel table for data to be processed; wherein the template class indicates a template for the Excel table and a cell class indicating position information and types of cells constituting the Excel table as attributes of the template class; the processing module 402 is configured to process the data to be processed according to the template class and the unit class called by the calling module, by using a class and a method encapsulated in an API for processing the Excel table.
Optionally, the type of the cell indicates that the cell is one of: a set of cells, rows, cells of an Excel table; in the case that the type of the cell indicates that the cell is a cell of the Excel table, the location information of the cell includes an abscissa and an ordinate of the cell; in the case that the type of the cell indicates that the cell is a row of the Excel table, the location information of the cell includes a start row number of the row; in the case where the type of the cell indicates that the cell is a set of cells of the Excel table, the location information of the cell includes a start row number, a start column number, a number of row merges, and a number of column merges of the set of cells.
Optionally, the data to be processed is an Excel table; the processing module 402 processes to parse the Excel table into character strings or text.
Optionally, the data to be processed is a character string or text; the processing module 402 processes the string or text to write the string or text to an Excel table.
Optionally, in the case that the type of the cell indicates that the cell is a cell of an Excel table, the processing module 402 is configured to parse the character string or text from the cell according to an abscissa and an ordinate of the cell; in the case that the type of the cell indicates that the cell is a row of an Excel table, the processing module 402 is configured to parse the character string or text from the row according to a starting row number of the row; in the case that the type of the cell indicates that the cell is a set of cells of an Excel table, the processing module 402 is configured to parse the character string or the text from the set of cells according to a start row number, a start column number, a row merging number, and a column merging number of the set of cells.
Optionally, in the case that the type of the cell indicates that the cell is a cell of an Excel table, the processing module 402 is configured to write the character string or text into the cell according to an abscissa and an ordinate of the cell; in the case that the type of the cell indicates that the cell is a row of an Excel table, the processing module 402 is configured to write the character string or text into the row according to a starting row number of the row; in the case that the type of the cell indicates that the cell is a set of cells of an Excel table, the processing module 402 is configured to write the character string or text into the set of cells according to a start row number, a start column number, a row merging number, and a column merging number of the set of cells.
Optionally, the template class further indicates a verifier corresponding to the Excel table, where the verifier is used to indicate a data rule, a verification object reference, and a hint information applied to the cell.
Optionally, the processing module 402 further includes a format converter 403, where the processing of the processing module 402 is to parse the Excel table into a character string or a text, the format converter 403 is configured to convert the data to be processed parsed in the unit into a predetermined data type; when the processing of the processing module 402 is writing the character string or text into an Excel table, the format converter 403 is configured to convert the data to be processed into a predetermined data type and write the predetermined data type into the unit.
Optionally, the API for the Excel sheet is an Apache POI.
Specifically, based on any one of the embodiments, the method and the device for processing an Excel table provided by the invention not only can process a general list part in the Excel table, but also can process personalized combined or split cells and a set thereof. Therefore, the method for processing the table provided by the embodiment of the invention can also be used for processing the table in the CSV form.
The embodiment of the invention also provides a server for processing the Excel table, which comprises the following steps: one or more processors; and a storage device for storing one or more programs which, when executed by the one or more processors, cause the one or more processors to implement the method provided by any of the embodiments described above.
FIG. 5 illustrates an exemplary system architecture of a method of processing an Excel sheet or an apparatus of processing an Excel sheet to which embodiments of the present invention may be applied, and may include: terminal devices 501, 502, 503, a network 504, and a server 505. The network 504 is used as a medium to provide communication links between the terminal devices 501, 502, 503 and the server 505. The network 504 may include various connection types, such as wired, wireless communication links, or fiber optic cables, among others.
A user may interact with the server 505 via the network 504 using the terminal devices 501, 502, 503 to receive or send messages or the like. The terminal devices 501, 502, 503 may be a variety of electronic devices having a display screen and supporting web browsing, including but not limited to smartphones, tablets, laptop and desktop computers, and the like.
The server 505 may be a server providing various services, such as a background management server providing support for Excel sheets or data streams (text or character strings) provided by the user using the terminal devices 501, 502, 503. The background management server may analyze and process the received data such as Excel table, and feed back the processing result (e.g., text file obtained after analysis) to the terminal device.
It should be noted that, the method for processing an Excel table provided in the embodiment of the present invention is generally executed by the server 505, and accordingly, the device for processing an Excel table is generally disposed in the server 505.
It should be understood that the number of terminal devices, networks and servers in fig. 5 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.
Referring now to FIG. 6, there is illustrated a schematic diagram of a computer system 600 suitable for use in implementing an embodiment of the present invention. The terminal device shown in fig. 6 is only an example, and should not impose any limitation on the functions and the scope of use of the embodiment of the present invention.
As shown in fig. 6, the computer system 600 includes a Central Processing Unit (CPU) 601, which can perform various appropriate actions and processes according to a program stored in a Read Only Memory (ROM) 602 or a program loaded from a storage section 608 into a Random Access Memory (RAM) 603. In the RAM603, various programs and data required for the operation of the system 500 are also stored. The CPU601, ROM602, and RAM603 are connected to each other through a bus 604. An input/output (I/O) interface 605 is also connected to bus 604.
The following components are connected to the I/O interface 605: an input portion 606 including a keyboard, mouse, etc.; an output portion 607 including a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, a speaker, and the like; a storage section 608 including a hard disk and the like; and a communication section 609 including a network interface card such as a LAN card, a modem, or the like. The communication section 609 performs communication processing via a network such as the internet. The drive 610 is also connected to the I/O interface 605 as needed. Removable media 611 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is installed as needed on drive 610 so that a computer program read therefrom is installed as needed into storage section 608.
In particular, according to embodiments of the present disclosure, the processes described above with reference to flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method shown in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication portion 609, and/or installed from the removable medium 611. The above-described functions defined in the system of the present invention are performed when the computer program is executed by a Central Processing Unit (CPU) 601.
The computer readable medium shown in the present invention may be a computer readable signal medium or a computer readable storage medium, or any combination of the two. The computer readable storage medium can be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or a combination of any of the foregoing. More specific examples of the computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the context of this document, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In the present invention, however, the computer-readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, with the computer-readable program code embodied therein. Such a propagated data signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination of the foregoing. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.
The flowcharts and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. 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 or flowchart illustration, and combinations of blocks in the block diagrams 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.
The modules involved in the embodiments of the present invention may be implemented in software or in hardware. The described modules may also be provided in a processor, for example, as: a processor comprises a calling module and a processing module. The names of these modules do not limit the modules themselves in some cases, and for example, the process may also be described as "parse an Excel table or write a process".
As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be present alone without being fitted into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to include: calling a template class acting on the Excel form by the data to be processed in an annotation mode, wherein the template class indicates a template corresponding to the Excel form and a unit class acting on a template attribute field; and processing the data to be processed according to the template class and the unit class, and returning the processed result.
According to the technical scheme of the embodiment of the invention, when the Excel table is processed, the general list part of the Excel table can be processed, and the personalized combined or split cells of the Excel table can be processed; the personalized part does not need to independently write codes for processing, and the codes can be reused; the method saves the program for processing the Excel form, reduces the processing difficulty, improves the processing efficiency and saves the resources to a certain extent; in addition, the technical scheme provided by the embodiment of the invention can also be used for processing the CSV form table, and has wide universality.
The above embodiments do not limit the scope of the present invention. It will be apparent to those skilled in the art that various modifications, combinations, sub-combinations and alternatives can occur depending upon design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included in the scope of the present invention.
Finally, it should be noted that: the above embodiments are only for illustrating the technical solution of the present invention, and not for limiting the same; although the invention has been described in detail with reference to the foregoing embodiments, it will be understood by those of ordinary skill in the art that: the technical scheme described in the foregoing embodiments can be modified or some or all of the technical features thereof can be replaced by equivalents; such modifications and substitutions do not depart from the spirit of the invention.
Claims (14)
1. A method of processing an Excel sheet, comprising:
invoking a template class for the Excel table by annotating data to be processed, wherein the template class indicates a template and a cell class for the Excel table as attributes of the template class, and the cell class indicates position information and types of cells composing the Excel table;
The type of the cell indicates that the cell is one of: a set of cells, rows, cells of an Excel table;
in the case that the type of the cell indicates that the cell is a cell of the Excel table, the location information of the cell includes an abscissa and an ordinate of the cell;
in the case that the type of the cell indicates that the cell is a row of the Excel table, the location information of the cell includes a start row number of the row;
in the case that the type of the cell indicates that the cell is a set of cells of the Excel table, the location information of the cell includes a start row number, a start column number, a row merging number, a column merging number of the set of cells;
the template class also indicates a verifier corresponding to the Excel table, the verifier indicating data rules, verification object references, hint information applied to the cell;
when the processing is that the Excel table is analyzed into a character string or a text, converting the data to be processed analyzed in the unit into a preset data type; when the processing is writing the character string or the text into an Excel table, converting the data to be processed into a predetermined data type and writing the predetermined data type into a unit; for the data to be processed, which passes the validity verification of the verifier, according to the corresponding data type indicated in the unit class, acquiring a format converter from a format converter factory packaged in the Apache POI to perform data format conversion;
Processing the data to be processed by using classes and methods encapsulated in an API for processing the Excel table according to the template class and the unit class;
the method further comprises the steps of: obtaining a parser for a template class from a parser factory packaged in an Apache POI (point of interest) for parsing data in an Excel table; when the custom parsing is needed, the custom parsing of the Excel table is performed by providing a custom parser factory and a custom parser.
2. The method of processing an Excel sheet according to claim 1, characterized in that,
the data to be processed is an Excel table;
the processing is to parse the Excel table into character strings or text.
3. The method of processing an Excel sheet according to claim 1, characterized in that,
the data to be processed is a character string or a text;
the processing is writing the character string or text into an Excel table.
4. The method of processing an Excel sheet according to claim 2, characterized in that,
when the type of the cell indicates that the cell is a cell of an Excel table, according to the abscissa and the ordinate of the cell, the character string or the text is analyzed from the cell;
In the case that the type of the cell indicates that the cell is a row of an Excel table, according to the starting row number of the row, the character string or the text is analyzed from the row;
and under the condition that the type of the cell indicates that the cell is a set of cells of an Excel table, analyzing the character string or the text from the set of cells according to the starting row number, the starting column number, the row merging number and the column merging number of the set of cells.
5. The method of processing an Excel sheet according to claim 3, characterized in that,
writing the character string or the text into the cell according to the abscissa and the ordinate of the cell when the type of the cell indicates that the cell is the cell of an Excel table;
writing the character string or text into the row according to the starting row number of the row when the type of the cell indicates that the cell is the row of the Excel table;
and writing the character string or the text into the set of cells according to the starting row number, the starting column number, the row merging number and the column merging number of the set of cells when the type of the cell indicates that the cell is the set of cells of the Excel table.
6. The method of processing an Excel sheet according to claim 1, wherein the API for the Excel sheet is an Apache POI.
7. An apparatus for processing an Excel sheet, comprising: a calling module and a processing module; wherein,
the calling module is used for calling a template class for the Excel table by annotating data to be processed, wherein the template class indicates a template and a cell class for the Excel table as attributes of the template class, and the cell class indicates position information and types of cells composing the Excel table; the type of the cell indicates that the cell is one of: a set of cells, rows, cells of an Excel table;
in the case that the type of the cell indicates that the cell is a cell of the Excel table, the location information of the cell includes an abscissa and an ordinate of the cell;
in the case that the type of the cell indicates that the cell is a row of the Excel table, the location information of the cell includes a start row number of the row;
in the case that the type of the cell indicates that the cell is a set of cells of the Excel table, the location information of the cell includes a start row number, a start column number, a row merging number, a column merging number of the set of cells;
The template class also indicates a verifier corresponding to the Excel table, the verifier indicating data rules, verification object references, hint information applied to the cell;
when the processing is that the Excel table is analyzed into a character string or a text, converting the data to be processed analyzed in the unit into a preset data type; when the processing is writing the character string or the text into an Excel table, converting the data to be processed into a predetermined data type and writing the predetermined data type into a unit; for the data to be processed, which passes the validity verification of the verifier, according to the corresponding data type indicated in the unit class, acquiring a format converter from a format converter factory packaged in the Apache POI to perform data format conversion;
the processing module is used for processing the data to be processed by using classes and methods encapsulated in the API for processing the Excel form according to the template class and the unit class called by the calling module;
the processing module is further used for acquiring a parser for the template class from a parser factory packaged in the Apache POI and carrying out parsing processing on data in the Excel form; when the custom parsing is needed, the custom parsing of the Excel table is performed by providing a custom parser factory and a custom parser.
8. The apparatus for processing an Excel sheet according to claim 7, characterized in that,
the data to be processed is an Excel table;
the processing module is used for analyzing the Excel table into character strings or texts.
9. The apparatus for processing an Excel sheet according to claim 7, characterized in that,
the data to be processed is a character string or a text;
the processing module is used for writing the character string or the text into an Excel table.
10. The apparatus for processing Excel sheet according to claim 8, characterized in that,
in the case that the type of the cell indicates that the cell is a cell of an Excel table, according to the abscissa and the ordinate of the cell, the processing module is used for analyzing the character string or the text from the cell;
in the case that the type of the cell indicates that the cell is a row of an Excel table, according to a starting row number of the row, the processing module is used for analyzing the character string or the text from the row;
and in the case that the type of the cell indicates that the cell is a set of cells of an Excel table, according to a starting row number, a starting column number, a row merging number and a column merging number of the set of cells, the processing module is used for analyzing the character string or the text from the set of cells.
11. The apparatus for processing Excel sheet according to claim 9, characterized in that,
in the case that the type of the cell indicates that the cell is a cell of an Excel table, the processing module is used for writing the character string or the text into the cell according to the abscissa and the ordinate of the cell;
in the case that the type of the cell indicates that the cell is a row of an Excel table, the processing module is configured to write the character string or text into the row according to a starting row number of the row;
in the case that the type of the cell indicates that the cell is a set of cells of an Excel table, the processing module is configured to write the character string or text into the set of cells according to a start row number, a start column number, a row merging number, and a column merging number of the set of cells.
12. The apparatus for processing an Excel sheet according to claim 7, wherein the API for the Excel sheet is an Apache POI.
13. A server for processing Excel sheets, comprising:
one or more processors;
storage means for storing one or more programs,
When executed by the one or more processors, causes the one or more processors to implement the method of any of claims 1-6.
14. A computer readable medium on which a computer program is stored, characterized in that the program, when being executed by a processor, implements the method of any of claims 1-6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910227888.5A CN111736805B (en) | 2019-03-25 | 2019-03-25 | Method and device for processing Excel table |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910227888.5A CN111736805B (en) | 2019-03-25 | 2019-03-25 | Method and device for processing Excel table |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111736805A CN111736805A (en) | 2020-10-02 |
| CN111736805B true CN111736805B (en) | 2024-04-05 |
Family
ID=72645756
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910227888.5A Active CN111736805B (en) | 2019-03-25 | 2019-03-25 | Method and device for processing Excel table |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111736805B (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105868171A (en) * | 2015-01-21 | 2016-08-17 | 中国移动（深圳）有限公司 | Checking method and device of Excel file |
| CN105867886A (en) * | 2015-01-19 | 2016-08-17 | 阿里巴巴集团控股有限公司 | Method and device for writing table |
| KR20180121193A (en) * | 2017-04-28 | 2018-11-07 | 홍익대학교세종캠퍼스산학협력단 | Method to control version of excel-based architecture design file |
| CN109325046A (en) * | 2018-10-25 | 2019-02-12 | 深圳壹账通智能科技有限公司 | Database script generation method, device, medium and electronic equipment |
| CN109344375A (en) * | 2018-08-24 | 2019-02-15 | 周宁 | A kind of report tool and method based on office software |
-
2019
- 2019-03-25 CN CN201910227888.5A patent/CN111736805B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105867886A (en) * | 2015-01-19 | 2016-08-17 | 阿里巴巴集团控股有限公司 | Method and device for writing table |
| CN105868171A (en) * | 2015-01-21 | 2016-08-17 | 中国移动（深圳）有限公司 | Checking method and device of Excel file |
| KR20180121193A (en) * | 2017-04-28 | 2018-11-07 | 홍익대학교세종캠퍼스산학협력단 | Method to control version of excel-based architecture design file |
| CN109344375A (en) * | 2018-08-24 | 2019-02-15 | 周宁 | A kind of report tool and method based on office software |
| CN109325046A (en) * | 2018-10-25 | 2019-02-12 | 深圳壹账通智能科技有限公司 | Database script generation method, device, medium and electronic equipment |
Non-Patent Citations (4)
| Title |
|---|
| Efthimia Aivaloglou,David Hoepelman,Felienne Hermans.Parsing Excel formulas: A grammar and its application on four large datasets.JOURNAL OF SOFTWARE: EVOLUTION AND PROCESS.2017,全文. * |
| Java开发小技巧（六）：使用Apache POI读取Exce;kMacro;https://www.cnblogs.com/zkh101/p/9786974.html;全文 * |
| POI 3.17 读取Excel（模板）、导出Excel;phil_jing;https://www.cnblogs.com/phil_jing/p/15615862.html;全文 * |
| 文本表格控件设计及应用;邵顺增;;计算机应用与软件(07);全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111736805A (en) | 2020-10-02 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN113031946A (en) | Method and device for rendering page component | |
| CN112631924A (en) | Automatic testing method and device, computer equipment and storage medium | |
| CN113760729A (en) | Code detection method and device | |
| CN113626223A (en) | Interface calling method and device | |
| CN112286815A (en) | Interface test script generation method and related equipment thereof | |
| CN113076294A (en) | Information sharing method and device | |
| CN112631588A (en) | File generation method and device, electronic equipment and computer readable medium | |
| CN116302218B (en) | Function information adding method, device, equipment and storage medium | |
| CN111736805B (en) | Method and device for processing Excel table | |
| CN113760274B (en) | Front-end assembly logic injection method and device | |
| CN113377376A (en) | Data packet generation method, data packet generation device, electronic device, and storage medium | |
| CN112947941A (en) | Method and device for adding exception handling code | |
| CN111475403A (en) | Dynamic generation method of test script and related device | |
| CN112287097B (en) | Method and device for analyzing markup language text, storage medium and electronic equipment | |
| CN113760731A (en) | Test method and device | |
| CN113792531B (en) | Text editing method and system based on markdown grammar expansion | |
| CN109376023B (en) | Method and equipment for generating calling information and issuing return code group | |
| CN110858240A (en) | Front-end module loading method and device | |
| CN113760706B (en) | Webpage debugging method and device | |
| CN117076319A (en) | Page terminal compatibility testing method and device | |
| CN116627478A (en) | File data processing method and device, electronic equipment and readable storage medium | |
| CN117278623A (en) | Method and device for processing request data, computer equipment and storage medium | |
| CN117874385A (en) | File processing and target page generating method, device and equipment | |
| CN112581247A (en) | Financial report generation method and device | |
| CN115269049A (en) | Remote plug-in loading method and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |