CN115759025A - Excel data conversion method, system, medium and device based on template file - Google Patents
Excel data conversion method, system, medium and device based on template file Download PDFInfo
- Publication number
- CN115759025A CN115759025A CN202211421786.5A CN202211421786A CN115759025A CN 115759025 A CN115759025 A CN 115759025A CN 202211421786 A CN202211421786 A CN 202211421786A CN 115759025 A CN115759025 A CN 115759025A
- Authority
- CN
- China
- Prior art keywords
- data
- template file
- excel
- file
- field
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a method, a system, a medium and equipment for Excel data conversion based on a template file, which comprise the following steps: setting a template file for data conversion, wherein the template file comprises a public header area and a plurality of data table areas; establishing a data mapping rule, wherein the data mapping rule comprises a position corresponding relation between a database and template file data and a position corresponding relation between an Excel file and the template file data; and selecting a corresponding data mapping rule according to the scene requirement to complete the data conversion. The invention realizes the support of more styles of the exported Excel file through the self-defined template, and the invention realizes the support of the exported Excel file by self-defining which columns of the table data in the Excel file need to be combined with the same data cell, which columns are provided for the modification of a client, and other columns are protected and can not be edited.
Description
Technical Field
The invention relates to the technical field of Excel data conversion, in particular to an Excel data conversion method, system, medium and device based on a template file.
Background
The Excel data export function used in the current application system has a single exported Excel file style and does not support the characteristics required by a client notice, such as cell merging, cell protection, reintroduction of analysis data and the like.
The technology adopted by the text increases the support for more form styles through the preset template file, provides the functions required by the client notice, such as merging of the same data cells, protection of non-editable items of the client and the like, and can analyze the modified file made by the client, thereby effectively solving the problems.
Disclosure of Invention
Aiming at the defects in the prior art, the invention aims to provide a method, a system, a medium and equipment for Excel data conversion based on a template file.
The Excel data conversion method based on the template file provided by the invention comprises the following steps:
step S1: setting a template file for data conversion, wherein the template file comprises a public header area and a plurality of data table areas;
step S2: establishing a data mapping rule, wherein the data mapping rule comprises a position corresponding relation between a database and template file data and a position corresponding relation between an Excel file and the template file data;
and step S3: and selecting a corresponding data mapping rule according to the scene requirement to complete the data conversion.
Preferably, the data mapping rule includes:
-determining the type and location of data in the template file using different tags, marking the location of the public area field of the data with $ field.a, where a is the field name, corresponding to the Java object attribute name of the database; b.c, marking the position of table data, wherein b is a table name and corresponds to the attribute name of Li st in a Java object, and c corresponds to the attribute name of the Java object in the Li st;
-data distinguishing different functions using different cell color backgrounds, the cell background colors corresponding to the special functions.
Preferably, a cell list needing to merge the same data, an editable cell list and a protected non-editable cell list are set in the template file.
Preferably, the attributes of the database Java object include a String type and a List < T > type, the String type attribute corresponds to a $ field.a field in the template file, the List < T > type attribute corresponds to a $ table.b.c field in the template file, the attribute name is the same as b, the attribute name of the T object is the same as c, and a row of data in a table in the Excel file corresponds to a T object.
The Excel data conversion system based on the template file provided by the invention comprises the following modules:
a module M1: setting a template file for data conversion, wherein the template file comprises a public header area and a plurality of data table areas;
a module M2: establishing a data mapping rule, wherein the data mapping rule comprises a position corresponding relation between a database and template file data and a position corresponding relation between an Excel file and the template file data;
a module M3: and selecting a corresponding data mapping rule according to the scene requirement to complete the data conversion.
Preferably, the data mapping rule includes:
-determining the type and location of data in the template file using different tags, marking the location of the public area field of the data with $ field.a, where a is the field name, corresponding to the Java object attribute name of the database; b.c, marking the position of table data, wherein b is a table name and corresponds to the attribute name of Li st in a Java object, and c corresponds to the attribute name of the Java object in the Li st;
-data distinguishing different functions using different cell color backgrounds, the cell background colors corresponding to the special functions.
Preferably, a cell list needing to merge the same data, an editable cell list and a protected non-editable cell list are set in the template file.
Preferably, the attributes of the database Java object include a String type and a List < T > type, the String type attribute corresponds to a $ field.a field in the template file, the List < T > type attribute corresponds to a $ table.b.c field in the template file, the attribute name is the same as b, the attribute name of the T object is the same as c, and a row of data in a table in the Excel file corresponds to a T object.
According to the invention, the computer readable storage medium is stored with a computer program, and the computer program realizes the steps of the Excel data conversion method based on the template file when being executed by a processor.
The Excel data conversion equipment based on the template file comprises a memory, a processor and a computer program which is stored on the memory and can run on the processor, wherein the computer program realizes the steps of the Excel data conversion method based on the template file when being executed by the processor.
Compared with the prior art, the invention has the following beneficial effects:
1. the method and the device realize support for more styles of the exported Excel file through the self-defined template.
2. The method can customize which columns of the table data in the Excel file need to be combined with the same data cells, which columns are provided for clients to modify, and other columns are protected and can not be edited.
3. The invention supports the batch import of the data after the selection of the client is finished into the system, and reduces the manual operation times of the service personnel.
Drawings
Other features, objects and advantages of the invention will become more apparent upon reading of the detailed description of non-limiting embodiments with reference to the following drawings:
FIG. 1 is a schematic view of a template file according to the present invention;
FIG. 2 is a schematic diagram of data to be converted according to the present invention;
FIG. 3 is a schematic diagram of a template file configured in accordance with the present invention;
FIG. 4 is a diagram illustrating a template file after data mapping according to the present invention.
Description of reference numerals:
region of interest 1 edit region 3
Region of interest 2 merge region 4
Detailed Description
The present invention will be described in detail with reference to specific examples. The following examples will assist those skilled in the art in further understanding the invention, but are not intended to limit the invention in any way. It should be noted that it would be obvious to those skilled in the art that various changes and modifications can be made without departing from the spirit of the invention. All falling within the scope of the invention.
The invention discloses an Excel data conversion method based on a template file, which has two main difficulties in realizing mutual conversion between database data and an Excel file: firstly, how to locate the position of the data in the table; and how to realize the special function requirement of part of special data. In order to solve the above difficulties, the present invention uses the following means: firstly, the type and the position of data are determined by using different mark symbols, and then the filling and the analysis of the data are realized. And secondly, the data with different functions are distinguished by using backgrounds with different colors, and the background colors of the cells correspond to the special functions (such as merging cells of the same data, protecting the data and not editing the data) through the appointed configuration. The method comprises the following specific steps:
step S1: and setting a template file for data conversion, wherein the template file comprises a common header area and a plurality of data table areas. The template file is provided with a cell list needing to merge the same data, an editable cell list and a protected non-editable cell list.
Referring to fig. 1, as a template file sample in this embodiment, the background color of the cells in the table may be configured with different functions and used as a distinction of information importance degrees, for example:
1. the left information in the table is generally common basic information, repeated data often appears between adjacent rows of data, and the table data marked by the white background and configured with the white background can be longitudinally combined with cells with the same content, so that the table content is more attractive and clear, as shown in a combining area 4 in the figure.
2. As a client notice, the client is required to make corresponding selection according to the information, and in order to prevent the data which does not need to be modified caused by misoperation of the client or service personnel, the fields which need to be edited in the file are marked by yellow backgrounds, as shown in an editing area 3 in the figure, namely, the content of the selected filling value is required to be made by the client. The default cells which are not configured and can be edited are not editable, and if special conditions need to be edited, the cells can be unlocked by using configured passwords.
3. The blue marking information is a field needing attention of customers or business personnel, and is shown as an attention area 1 in the figure. The red label information is a field which needs the emphasis of the customer or the service personnel, as shown in the highlight area 2 in the figure.
According to different types of files, a user can flexibly carry out the association configuration between the background color and the function of the cells.
Step S2: and establishing a data mapping rule, wherein the data mapping rule comprises a position corresponding relation between a database and template file data and a position corresponding relation between an Excel file and the template file data.
The data mapping rule includes: determining the type and position of data in a template file by using different mark symbols, and marking the position of a data public area field by adopting a $ field.a, wherein a is a field name and corresponds to a Java object attribute name of a database; b.c. is used to mark the position of table data, where b is the table name, corresponding to the List attribute name in the Java object, and c corresponds to the Java object attribute name in the List. And distinguishing data of different functions by using backgrounds of different cell colors, wherein the cell background colors correspond to the special functions.
The attributes of the Java objects of the database comprise a String type and a List < T > type, the String type attribute corresponds to a $ field.a field in a template file, the List < T > type attribute corresponds to a $ Table.b.c field in the template file, the attribute name is the same as b, the attribute name of the T object is the same as c, and one row of data in one table in the Excel file corresponds to one T object.
Specifically, the step of generating an Excel file from the Java object via the template file comprises:
1. and filling the public area data into the position corresponding to $ Field in the template file.
2. And filling the Table data into the position corresponding to the $ Table in the template file, and merging the cells of the same data, locking the cells and the like according to the configuration.
Generating a Java object from the Excel file through the template file:
1. and analyzing the corresponding position data of the Excel file according to the position corresponding to the $ Field in the template file and filling the corresponding attribute in the Java object.
2. And analyzing the corresponding position data of the Excel file row by row according to the position corresponding to the $ Table in the template file, filling the corresponding List attribute in the Java object, stopping the current Table data analysis after the data is analyzed to a full-empty row, and entering the next Table data analysis.
And step S3: and selecting a corresponding data mapping rule according to the scene requirement to complete the data conversion. The specific process is as follows: and selecting different templates according to different specific scenes for generating and analyzing, exporting corresponding data in the database into an Excel file, analyzing the Excel file into corresponding data and updating the data into the database, and providing an Excel file preview function.
The invention is further described below with reference to a specific embodiment.
Referring to fig. 2, three data sets are queried according to the related business logic, and based on the configured template file, as shown in fig. 3, finally, the data is mapped to the correct position in the template file according to the mapping rule, and the mapped template file is as shown in fig. 4.
The Excel data conversion system based on the template file can be realized by executing the flow steps of the Excel data conversion method based on the template file, namely, the Excel data conversion method based on the template file can be understood as a preferred embodiment of the Excel data conversion system based on the template file by a person skilled in the art.
The Excel data conversion system based on the template file comprises the following modules:
a module M1: setting a template file for data conversion, wherein the template file comprises a public header area and a plurality of data table areas;
a module M2: establishing a data mapping rule, wherein the data mapping rule comprises a position corresponding relation between a database and template file data and a position corresponding relation between an Excel file and the template file data;
a module M3: and selecting a corresponding data mapping rule according to the scene requirement to complete the data conversion.
Wherein the data mapping rule comprises:
-determining the type and location of data in the template file using different tags, marking the location of the public area field of the data with $ field.a, where a is the field name, corresponding to the Java object attribute name of the database; b.c, marking the position of table data, wherein b is a table name and corresponds to the attribute name of Li st in a Java object, and c corresponds to the attribute name of the Java object in the Li st;
-data distinguishing different functions using different cell color backgrounds, the cell background colors corresponding to the special functions.
The template file is provided with a cell list needing to merge the same data, an editable cell list and a protected non-editable cell list.
The attributes of the Java objects of the database comprise a String type and a List < T > type, the String type attribute corresponds to a $ field.a field in a template file, the List < T > type attribute corresponds to a $ Table.b.c field in the template file, the attribute names are the same as b, the attribute names of the T objects are the same as c, and one row of data in one table in the Excel file corresponds to one T object.
The invention also provides a computer readable storage medium, such as a computer hard disk, etc., which stores a computer program, and the computer program is executed by a processor to realize the steps of the Excel data conversion method based on the template file.
The invention also provides Excel data conversion equipment based on the template file, which comprises a memory, a processor and a computer program which is stored on the memory and can run on the processor, wherein the computer program realizes the steps of the Excel data conversion method based on the template file when being executed by the processor.
Those skilled in the art will appreciate that, in addition to implementing the system and its various devices, modules, units provided by the present invention as pure computer readable program code, the system and its various devices, modules, units provided by the present invention can be fully implemented by logically programming method steps in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers, embedded microcontrollers and the like. Therefore, the system and various devices, modules and units thereof provided by the invention can be regarded as a hardware component, and the devices, modules and units included in the system for realizing various functions can also be regarded as structures in the hardware component; means, modules, units for performing the various functions may also be regarded as structures within both software modules and hardware components for performing the method.
The foregoing description of specific embodiments of the present invention has been presented. It is to be understood that the present invention is not limited to the specific embodiments described above, and that various changes or modifications may be made by one skilled in the art within the scope of the appended claims without departing from the spirit of the invention. The embodiments and features of the embodiments of the present application may be combined with each other arbitrarily without conflict.
Claims (10)
1. A Excel data conversion method based on a template file is characterized by comprising the following steps:
step S1: setting a template file for data conversion, wherein the template file comprises a public header area and a plurality of data table areas;
step S2: establishing a data mapping rule, wherein the data mapping rule comprises a position corresponding relation between a database and template file data and a position corresponding relation between an Excel file and the template file data;
and step S3: and selecting a corresponding data mapping rule according to the scene requirement to complete the data conversion.
2. The template file-based Excel data transformation method according to claim 1, wherein the data mapping rules comprise:
-determining the type and location of data in the template file using different tags, marking the location of the public area field of the data with $ field.a, where a is the field name, corresponding to the Java object attribute name of the database; b.c, marking the position of table data, wherein b is a table name and corresponds to a List attribute name in a Java object, and c corresponds to a Java object attribute name in the List;
-data distinguishing different functions using different cell color backgrounds, the cell background colors corresponding to the special functions.
3. The template file-based Excel data conversion method according to claim 1, characterized in that: the template file is provided with a cell list needing to merge the same data, an editable cell list and a protected non-editable cell list.
4. The template file-based Excel data conversion method according to claim 1, characterized in that: the attributes of the Java object of the database comprise a String type and a List < T > type, the String type attribute corresponds to a $ field.a field in a template file, the List < T > type attribute corresponds to a $ Table.b.c field in the template file, the attribute name is the same as b, the attribute name of the T object is the same as c, and one line of data in a table in the Excel file corresponds to one T object.
5. An Excel data conversion system based on a template file is characterized by comprising the following modules:
a module M1: setting a template file for data conversion, wherein the template file comprises a public header area and a plurality of data table areas;
a module M2: establishing a data mapping rule, wherein the data mapping rule comprises a position corresponding relation between a database and template file data and a position corresponding relation between an Excel file and the template file data;
a module M3: and selecting a corresponding data mapping rule according to the scene requirement to complete the data conversion.
6. The template file-based Excel data transformation system according to claim 5, wherein said data mapping rules comprise:
-determining the type and location of data in the template file using different tags, marking the location of the public area field of the data with $ field.a, where a is the field name, corresponding to the Java object attribute name of the database; b.c, marking the position of table data, wherein b is a table name and corresponds to a List attribute name in a Java object, and c corresponds to a Java object attribute name in the List;
-data distinguishing different functions using different cell color backgrounds, the cell background colors corresponding to the special functions.
7. The template file-based Excel data transformation system according to claim 5, characterized in that: the template file is provided with a cell list needing to merge the same data, an editable cell list and a protected non-editable cell list.
8. The template file-based Excel data conversion system according to claim 5, characterized in that: the attributes of the Java objects of the database comprise a String type and a List < T > type, the String type attribute corresponds to a $ field.a field in a template file, the List < T > type attribute corresponds to a $ Table.b.c field in the template file, the attribute name is the same as b, the attribute name of the T object is the same as c, and one row of data in one table in the Excel file corresponds to one T object.
9. A computer-readable storage medium storing a computer program, wherein the computer program, when executed by a processor, implements the steps of the template file-based Excel data conversion method according to any one of claims 1 to 4.
10. A template file-based Excel data conversion device comprising a memory, a processor and a computer program stored on said memory and executable on said processor, characterized in that said computer program when executed by the processor implements the steps of the template file-based Excel data conversion method according to any one of claims 1 to 4.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211421786.5A CN115759025A (en) | 2022-11-14 | 2022-11-14 | Excel data conversion method, system, medium and device based on template file |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211421786.5A CN115759025A (en) | 2022-11-14 | 2022-11-14 | Excel data conversion method, system, medium and device based on template file |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115759025A true CN115759025A (en) | 2023-03-07 |
Family
ID=85370467
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211421786.5A Pending CN115759025A (en) | 2022-11-14 | 2022-11-14 | Excel data conversion method, system, medium and device based on template file |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115759025A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116521845A (en) * | 2023-05-05 | 2023-08-01 | 四川川大智胜系统集成有限公司 | Method for reading complex electronic form file and electronic equipment |
-
2022
- 2022-11-14 CN CN202211421786.5A patent/CN115759025A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116521845A (en) * | 2023-05-05 | 2023-08-01 | 四川川大智胜系统集成有限公司 | Method for reading complex electronic form file and electronic equipment |
| CN116521845B (en) * | 2023-05-05 | 2024-03-05 | 四川川大智胜系统集成有限公司 | Method for reading complex electronic form file and electronic equipment |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103106188B (en) | The graphical analytic system of data model and graphical analytical approach | |
| US7779000B2 (en) | Associating conditions to summary table data | |
| US7761834B2 (en) | Interactive schematic for use in analog, mixed-signal, and custom digital circuit design | |
| US6516326B1 (en) | System and method for integrating electrical power grid and related data from various proprietary raw data formats into a single maintainable electrically connected database | |
| US7093207B1 (en) | Data analysis flow engine | |
| US6662237B1 (en) | System for documenting application interfaces and their mapping relationship | |
| CN107807954B (en) | Visual modeling multidimensional analysis system and method thereof | |
| US20120047434A1 (en) | Method to preview an undo/redo list | |
| CN101196963B (en) | Indexing system and method of generic chemical structure | |
| CN107704243A (en) | Construction method and device, the computer equipment and storage medium of front-end interface | |
| Ferreira | A primer on process mining: Practical skills with python and graphviz | |
| US8042088B2 (en) | Method and system for implementing stacked vias | |
| CN115759025A (en) | Excel data conversion method, system, medium and device based on template file | |
| CN110728123A (en) | Report generation method, system and related equipment | |
| CN107103035A (en) | This earth's surface data-updating method and device | |
| CN115576950A (en) | Report generation method based on JSON configuration file | |
| CN114547077A (en) | Intelligent processing system and method for basic government affair form data | |
| CN107766519B (en) | Method for visually configuring data structure | |
| CN108830540B (en) | Method, device and system for processing engineering project archive | |
| CN113608903A (en) | Fault management method based on XML language | |
| US10387010B2 (en) | Method of computerized presentation of a document set view for auditing information and managing sets of multiple documents and pages | |
| CN112597023A (en) | Case management method and device based on guide picture, computer equipment and storage medium | |
| JP6948492B2 (en) | Information management device and file management method | |
| US7702643B2 (en) | System and method for metamodel-based gap analysis | |
| CN105808595B (en) | A kind of the data library generating method and device of authority file |
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 |