CN115828866A

CN115828866A - Excel data importing and exporting method and device

Info

Publication number: CN115828866A
Application number: CN202211163489.5A
Authority: CN
Inventors: 洪志权; 蔡昆颖; 于崇刚; 庄士超; 陈昌炜
Original assignee: Guangdong Hong Kong Macao International Supply Chain Guangzhou Co ltd
Current assignee: Guangdong Hong Kong Macao International Supply Chain Guangzhou Co ltd
Priority date: 2022-09-23
Filing date: 2022-09-23
Publication date: 2023-03-21

Abstract

The invention relates to an Excel data import and export method and a device, comprising the steps of matching imported or exported data with table information of a preset database to generate newly added field information, wherein the acquisition of the preset database comprises uploading the table information of an Excel template; creating a data object, wherein the data object does not contain field information; combining the field names, the field attributes and the field values of the data object and the new added field information respectively to generate a new field name, a new field attribute and a new field value; creating a target object by using a code generation packet, and assigning the new field name, the new field attribute and the new field value to the target object; the data is imported or exported according to the target object, flexible configuration table information is provided, the changed part of the corresponding template can be directly modified when the template is changed, and the target object is generated, so that the advantage that the program code for executing import or export does not need to be changed along with the change of the template is ensured.

Description

Excel data importing and exporting method and device

Technical Field

The invention relates to the technical field of computers, in particular to an Excel data importing and exporting method and device, a readable storage medium and computer equipment.

Background

In an actual business system, a lot of Excel related import and export operations are used, and the import and export of an Excel report relate to line display (namely, the content field attribute displayed by each line of cells is consistent) and fixed cell display (namely, a certain field attribute is fixedly displayed by a certain cell), and sometimes, the same file also relates to a plurality of work table pages.

Most of the current schemes are to define Excel data objects to be read and written, and then carry out matching according to fields of an Excel template. Each import and export Excel needs to define a data object, if each form is developed, too much repeated work is involved, so that the workload is increased, the efficiency is reduced, and the progress of a project is influenced.

In the existing Excel data import and export method, the template needs to be uploaded to the server, if the content of the template changes, the template needs to be uploaded again, the content of the template cannot be accepted, and if the content of the template changes, the code needs to be modified synchronously to ensure the normal realization of the function.

Disclosure of Invention

Based on this, the present invention aims to provide a method and a device for importing and exporting Excel data, which have the advantages of flexible configuration of table information, direct modification of a corresponding change part when a template changes, and no need of uploading the template and modifying codes again.

A method for importing and exporting Excel data comprises the following steps:

matching the imported or exported data with table information of a preset database to generate new field information, wherein the preset database comprises the uploaded table information of the Excel template, and the table information comprises a table name, a business document type, a table form, a cell name, cell contents and a cell form;

creating a data object, wherein the data object does not contain field information;

combining the field names of the data object and the new added field information to generate a new field name, combining the field attributes of the data object and the new added field information to generate a new field attribute, and combining the field values of the data object and the new added field information to generate a new field value;

creating a target object by using a code generation packet, and assigning the new field name, the new field attribute and the new field value to the target object;

and importing or exporting the data according to the target object.

An Excel data import and export device comprises

The data matching module is used for matching the imported or exported data with table information of a preset database to generate newly added field information, wherein the preset database comprises the uploaded table information of the Excel template, and the table information comprises a table name, a business document type, a table form, a cell name, cell contents and a cell form;

the data object generating module is used for creating a data object, and the data object does not contain field information;

the data combination module is used for combining the data object and the field name of the new added field information to generate a new field name, combining the data object and the field attribute of the new added field information to generate a new field attribute, and combining the data object and the field value of the new added field information to generate a new field value;

the target object generating module is used for creating a target object by using a code generating packet and assigning the new field name, the new field attribute and the new field value to the target object;

and the import and export execution module is used for importing or exporting the data according to the target object.

A readable storage medium, characterized by: the readable storage medium stores a computer program, and when the computer program is executed, the method for importing and exporting Excel data can be realized.

A computer device, characterized by: comprising a processor and the above-mentioned readable storage medium, the processor can execute the computer program stored in the readable storage medium.

Compared with the prior art, the embodiment provided by the invention at least has the following beneficial effects:

in the application, the table information is manually set through uploading an Excel template and/or through a template configuration page to obtain the preset database, data needing to be imported or exported are matched with the database, a target object is obtained through dynamic assignment in combination with the data object, and then the target object is utilized for importing or exporting. In this application, if the Excel template changes, can directly pass through the part that the template configuration page corresponds changes is adjusted, need not upload the template again, simultaneously, will in this application form information uploads to in the database, through dynamic assignment acquisition target object realization import and export, guaranteed when the Excel template changes, can not influence right the code that data import and export for the developer need not to develop the code once more to different templates, makes the import and export of Excel more nimble convenient.

For a better understanding and practice, the invention is described in detail below with reference to the accompanying drawings.

Drawings

FIG. 1 is a flowchart illustrating the steps of an Excel data import and export method according to an embodiment of the present application;

FIG. 2 is a flowchart of a step of uploading an Excel template in the Excel data import and export method according to the embodiment of the present application;

FIG. 3 is a flowchart of a step of manually setting table information through a template configuration page in the Excel data import and export method according to the embodiment of the present application;

fig. 4 is a schematic diagram of an Excel data import and export apparatus provided in an embodiment of the present application.

Detailed Description

In order to make the objects, technical solutions and advantages of the present application more clearly understood, the present application is further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.

Referring to fig. 1, fig. 1 is a flowchart illustrating steps of importing and exporting Excel data according to an embodiment of the present application in fig. 1.

An Excel data import and export method comprises the following steps:

s101, matching the imported or exported data with table information of a preset database to generate newly added field information, wherein the preset database comprises the uploaded table information of an Excel template, and the table information comprises a table name, a business document type, a table form, a cell name, cell contents and a cell form;

s102, creating a data object, wherein the data object does not contain field information;

s103, combining the field names of the data object and the new added field information to generate a new field name, combining the field attributes of the data object and the new added field information to generate a new field attribute, and combining the field values of the data object and the new added field information to generate a new field value;

s104, creating a target object by using a code generation packet, and assigning the new field name, the new field attribute and the new field value to the target object;

and S105, importing or exporting the data according to the target object.

In this embodiment, the Excel data import and export method is mainly executed by a computer, the preset database is obtained/updated by manually configuring table information through an upload template and a template configuration page, the computer obtains newly added field information by matching data to be imported or exported with the table information in the preset database, the computer creates a data object not containing the field information, the data object and the newly added field information are combined to generate target field information, and the computer imports or exports the data by using the field information. According to the Excel data import and export method, the table information is stored in the database, and is obtained through the database when the table information is imported or exported, so that repeated use of one-time configuration is realized, the table information in the database can be flexibly configured through the template configuration page, the method is more flexible and convenient, and when the table information needs to be newly added and imported or exported, the table information can be directly adjusted through the template configuration page without redeveloping codes.

For step S101, matching the imported or exported data with table information of a preset database to generate new field information, where the preset database includes the uploaded table information of the Excel template, and the table information includes a table name, a service document type, a table form, a cell name, cell contents, and a cell form;

the preset database is a database which is set in advance and used for matching data needing import or export operation, and the table information stored in the database is key information included in an Excel table. The table information stored in the database is obtained by uploading an Excel template, and further can be obtained by setting the table information of the Excel template through a template configuration page. In one embodiment, the form information includes a form name, a business document type, a form, a cell name, cell contents, and a cell form. The new field information is obtained by matching the data to be imported or exported with the table information in the database.

In an embodiment, referring to fig. 2, fig. 2 is a flowchart illustrating a step of uploading an Excel template in an embodiment of the present application, where the step of obtaining table information of the preset database by uploading the Excel template includes the following steps:

s201, importing an Excel template, and acquiring a table form of the Excel template according to the Excel template;

s202, if the table form is a list form, acquiring the table name and the service document type of the Excel template, identifying the cell name, the cell content and the cell form, storing the table name, the service document type, the cell name, the cell content and the cell form as table information into the preset database, and marking the table name, the service document type, the cell name, the cell content and the cell form as a list form;

s203, if the form is a fixed form, acquiring the form name and the service receipt type of the Excel template, identifying the cell name, the statically filled cell content, the dynamically filled cell content and the cell form, storing the form name, the service receipt type, the cell name, the statically filled cell content, the dynamically filled cell content and the cell form as form information into a preset database, and marking the form information as the fixed form.

For step S201, importing an Excel template, and obtaining a table form of the Excel template according to the Excel template;

the Excel template is obtained by storing a certain format and characters through a formula, and can be directly called to generate an Excel table when in use. By using the Excel template to obtain the table form of the Excel, relevant contents can be directly matched during import and/or export, and data and formulas do not need to be set.

The tabular form includes a list form and a fixed tabular form, wherein the list form is an Excel table made by a list style, and in one embodiment, the list form is as shown in the following table:

	A	B	C	D
					1	platform	Shop name	Date	Flow rate
2	platform	shopname	date	flow
					3

The fixed table form is a table including fixed contents and changed contents in an Excel table, and in one embodiment, the fixed table form is as shown in the following table:

in this embodiment, the tabular form is divided into a list form and a fixed tabular form, so that when the Excel data is imported or exported, the Excel data is conveniently imported into a table in a corresponding tabular form.

For step S202, if the table form is a list form, obtaining a table name and a service document type of the Excel template, identifying a cell name, a cell content, and a cell form, storing the table name, the service document type, the cell name, the cell content, and the cell form as table information into the preset database, and marking as a list form;

the table name is a name of the table, and is used to indicate what Excel table that needs to be imported or exported is, for example: the names of the tables are 2022 year platform flow tables, 2022 first quarter profit tables, etc. The service document type is what content the form corresponds to, and in one embodiment, the service document type includes a platform flow table, a warehousing detail table and a profit table. The cell name is a name populated in the cell; the cell content is the content which needs to be filled in the cell; the cell form refers to information such as whether the cells need to be merged, the number of the cells needing to be merged, and the cell format.

In one embodiment, for identifying cell names, cell contents, and cell forms, excel template contents may be identified using a package in Java that supports reading and writing of Office, which is an open source library of Apache software foundation, POI provides API to Java program functions to read and write Microsoft Office format archive.

For the uploaded Excel template, in this embodiment, a table form of the Excel template is first determined, and if the table form is a list form, a table name and a service document type of the Excel template are first obtained, and then the Excel template is read by using a package supporting office reading and writing in Java, so as to obtain a cell name, a cell content, and a cell form of the Excel template. And finally, the acquired form name, business document type cell name, cell content and cell form are stored in the preset database as form information and marked in a list form, so that when data is imported or exported, the form name, the business document type cell name, the cell content and the cell form corresponding to the data are identified through the form information in the preset database, and the data is conveniently imported or exported.

For step S203, if the table form is a fixed table form, obtaining the table name and the service document type of the Excel template, identifying the cell name, the statically filled cell content, the dynamically filled cell content and the cell form, storing the table name, the service document type, the cell name, the statically filled cell content, the dynamically filled cell content and the cell form as table information into a preset database, and marking as a fixed table form;

if the form is judged to be a fixed form, compared with the form in the list form, the fixed form also needs to identify cell contents which need static filling and cell contents which need dynamic filling, and store the cell contents into the database, wherein the static filling is to fill the cell contents in the Excel form as fixed contents, and the dynamic filling is to fill the contents in the Excel form, in which the cell contents can change. In one embodiment, if there is a # #inthe cell contents, it indicates that the cell contents are dynamically populated, otherwise, the cell contents are statically populated.

In an embodiment, please refer to fig. 3, where fig. 3 is a flowchart illustrating a step of manually setting table information to update the database through a template configuration page in the embodiment of the present application, where the step of setting the template through the template configuration page is as follows:

s301, opening a template configuration page, and selecting a table form of an Excel table;

s302, if the form is a list form, filling a form name, a business document type, a cell name, cell contents and a cell form in the template configuration page, and storing the form name, the business document type, the cell name, the cell contents and the cell form as form information into a preset database and marking the form as the list form;

and S303, if the form is a fixed form, filling a form name, a service receipt type, a cell name, statically filled cell content, dynamically filled cell content and a cell form in the target configuration page, storing the form name, the service receipt type, the cell name, the statically filled cell content, the dynamically filled cell content and the cell form in a preset database, and marking as fixed form information.

For step S301, opening a template configuration page, and selecting a table form of an Excel table;

the template configuration page is a page for configuring the Excel template, the Excel template can be manually configured through the Excel template configuration page, and if the uploaded Excel template in step S201 needs to be changed, the corresponding change position can be directly adjusted through the template configuration page without re-uploading the template for identification. And setting the table information of the Excel table through the template configuration page, wherein the table form of the required Excel table is selected to be a list form or a fixed table form.

For step S302, if the form is a list form, filling a form name, a service document type, a cell name, a cell content, and a cell form in the template configuration page, and storing the form name, the service document type, the cell name, the cell content, and the cell form as form information in a preset database, and marking as a list form;

the form information of the Excel form is set through the template configuration page, in the embodiment, the form of the Excel template is selected firstly, if the form is the list form, the form name and the service receipt type of the Excel template are filled in through the template configuration page firstly, and then the cell name, the cell content and the cell form of the Excel template are filled in. And finally, the acquired form name, the business document type cell name, the cell content and the cell form are stored in the preset database as form information and marked in a list form, so that the form name, the business document type cell name, the cell content and the cell form corresponding to the data are identified through the form information in the preset database when the data are imported and/or exported, and the data are conveniently imported or exported.

For step S303, if the form is a fixed form, filling a form name, a service receipt type, a cell name, statically filled cell content, dynamically filled cell content, and a cell form in the target configuration page, and storing the form name, the service receipt type, the cell name, the statically filled cell content, the dynamically filled cell content, and the cell form in a preset database and marking as fixed form information;

if the form is selected as a fixed form, compared to the form in the list form, the fixed form further needs to fill in the cell contents that need to be statically filled and the cell contents that need to be dynamically filled, and stores the cell contents in the database.

In this embodiment, the preset database information is acquired by uploading an Excel template and manually setting the table information through a template configuration page, so that convenience in acquiring the table information is ensured, and abundant database information is provided for importing or exporting the data. And the preset database is acquired/updated by setting the table information through the template configuration page, so that the table information in the preset data can be timely adjusted when the table information changes, and the corresponding table information is adjusted only through the template configuration page without uploading the template analysis again for the Excel template with only part of the content changing, so that the operation of importing or exporting the Excel data is more flexible. Furthermore, the preset database is obtained according to the template configuration page adjusting cell information, and the fact that codes do not need to be adjusted again to adapt to a new Excel template when the Excel template is updated is guaranteed.

For step S102, a data object is created, where the data object does not contain field information;

the data object is a composite information representation understood by software, and in this embodiment, the data object is an empty data object, does not include field information, and is used for matching with the newly added field information to obtain target field information.

For step S103, combining the field names of the data object and the new added field information to generate a new field name, combining the field attributes of the data object and the new added field information to generate a new field attribute, and combining the field values of the data object and the new added field information to generate a new field value;

the data object and the new added field information both comprise field names, field attributes and field values, and for the acquired data object and the new added field information, the field names, the field attributes and the field values in the data object and the new added field information are respectively combined to acquire a group of new field names, new field attributes and new field values for acquiring a target object.

wherein the Code generation package is a Code generation class library which can extend the Java classes and implement the Java interface at runtime.

In one embodiment, said assigning said new field name, new field attribute and new field value to said target object comprises the steps of:

generating corresponding field information for the target object by using the new field name and the new field attribute information;

and generating a static BeanMap by using the target object, and assigning the new field name and the new field value to the static BeanMap.

And the field information comprises a field name and a field attribute, and the new field name and the new field attribute are assigned to the target object, so that the field information of the target object is acquired.

And generating a static BeanMap by using the target object, and assigning the new field name and the new field value to the static BeanMap, wherein the static BeanMap is generated by the target, so the static BeanMap corresponds to the value of the target object, the target object can acquire the field value, and the target object is finally a new object obtained by combining the data object and the new added field information.

In one embodiment, steps S103-S104 may be further understood as:

acquiring field information of the data object by using a tool kit for operating JAVA entity class, and respectively storing the field information into a first set and a second set, wherein keys of the first set are field names, and values are field attributes; the key of the second set is a field name, and the value is a field value; the tool package for operating the JAVA entity class is a tool package for operating JAVA BEAN provided by Apache open source organization, and can conveniently operate the attribute of the BEAN object.

Similarly, the newly added field information is stored in a third set and a fourth set, wherein the key of the third set is the field name, and the value is the field attribute; the key of the fourth set is the field name and the value is the field value.

Combining the first and third sets to form a fifth set including new field attributes, and combining the second and fourth sets to form a sixth set including new field values.

Generating corresponding field information by using the fifth set of information for the target object;

and creating a static BeanMap by using the target object, and assigning the sixth set to the static BeanMap.

For step S105, importing or exporting the data according to the target object;

the data import or export is performed based on the target object acquired from steps S101-S104.

In one embodiment, the importing of the data further comprises the steps of:

according to the target object, performing library writing storage or data display on the data;

when the import operation is performed, the target object is obtained through processing in steps S101 to S104, then, according to the table information obtained by the target object, the data of the corresponding table is imported from the Excel table into the corresponding file for library writing storage or data display, and finally, the data to be imported is stored or displayed.

In one embodiment, the deriving of the data further comprises the steps of:

and according to the target object, combining a cell form matched by the data and the table information of the database, and filling the data into a corresponding cell by using a packet supporting office reading and writing in the Java.

When the export operation is performed, firstly, the target object is obtained through processing in steps S101-104, according to the target object, firstly, the cells are merged and format-adjusted in combination with the cell form matched by the data and the table information of the database, and then, the data is filled into the corresponding cells by using the packet supporting reading and writing of office in the Java.

In the embodiment, the preset database is obtained by uploading the Excel template and manually configuring the cell information through the template configuration page, the object to be imported or exported is matched with the information in the preset database to obtain the newly added field information, the target object is obtained by dynamic assignment, and the data is imported or exported according to the target object, so that the accuracy in importing or exporting is ensured, meanwhile, when the data is imported or exported, the target object obtained by processing after being matched with the database is used, so that when the Excel template is changed, the database information can be adjusted through the template configuration page, so that the template adjustment for importing or exporting the data is more flexible and convenient, moreover, the code required to be executed in importing or exporting operation does not need to be synchronously changed, developers do not need to redevelop the changed template, and the workload of the developers is reduced.

Referring to fig. 4, fig. 4 is a schematic diagram of an Excel data import and export apparatus provided in an embodiment of the present application, and the present application further provides an Excel data import and export apparatus including:

the data matching module 11 is configured to match the imported or exported data with table information of a preset database to generate newly added field information, where the preset database includes the uploaded table information of the Excel template, and the table information includes a table name, a service document type, a table form, a cell name, cell contents, and a cell form;

a data object generating module 12, configured to create a data object, where the data object does not include field information;

a data combining module 13, configured to combine the field names of the data object and the new added field information to generate a new field name, combine the field attributes of the data object and the new added field information to generate a new field attribute, and combine the field values of the data object and the new added field information to generate a new field value;

a target object generation module 14, configured to create a target object using the code generation package, and assign the new field name, the new field attribute, and the new field value to the target object;

and an import and export execution module 15, configured to import or export the data according to the target object.

The present application also provides a readable storage medium characterized in that: the readable storage medium stores therein a computer program, and when the computer program is executed, the method for importing and exporting Excel data according to the above embodiments may be implemented.

The application also provides a computer device, which comprises a processor and the readable storage medium, wherein the processor can execute the computer program stored in the readable storage medium.

The above-mentioned embodiments only express several embodiments of the present invention, and the description thereof is more specific and detailed, but not construed as limiting the scope of the invention. It should be noted that various changes and modifications can be made by those skilled in the art without departing from the spirit of the invention, and these changes and modifications are all within the scope of the invention.

Claims

1. A method for importing and exporting Excel data is characterized by comprising the following steps: the method comprises the following steps:

and importing or exporting the data according to the target object.

2. The method for importing and exporting Excel data according to claim 1, comprising the steps of: further comprising the steps of:

obtaining a table form of the Excel template according to the Excel template;

if the table form is a list form, acquiring the table name and the service receipt type of the Excel template, identifying the cell name, the cell content and the cell form in the Excel template, storing the table name, the service receipt type, the cell name, the cell content and the cell form as table information into the preset database, and marking the table name, the service receipt type, the cell name, the cell content and the cell form as a list form;

if the form is a fixed form, acquiring the form name and the service receipt type of the Excel template, identifying the cell name, the statically filled cell content, the dynamically filled cell content and the cell form in the Excel template, storing the form name, the service receipt type, the cell name, the statically filled cell content, the dynamically filled cell content and the cell form as form information into a preset database, and marking the form as the fixed form.

3. The method of Excel data import and export according to claim 1, wherein: the Excel template can also be set through a template configuration page.

4. The method for importing and exporting Excel data according to claim 3, comprising the steps of: the method further comprises the step of setting the Excel template through the template configuration page: opening a template configuration page, and selecting a table form of an Excel table;

if the form is a list form, filling a form name, a service document type, a cell name, cell contents and a cell form in the template configuration page, storing the form name, the service document type, the cell name, the cell contents and the cell form as form information into a preset database, and marking the form as the list form;

if the form is a fixed form, filling a form name, a business document type, a cell name, statically filled cell contents, dynamically filled cell contents and a cell form in the target configuration page, storing the form name, the business document type, the cell name, the statically filled cell contents, the dynamically filled cell contents and the cell form in a preset database, and marking as fixed form information.

5. The method for importing and exporting Excel data according to any one of claims 1 to 4, wherein the method comprises the following steps: said creating a target object using the code generation package, assigning said new field attributes and new field values to said target object comprising the steps of:

6. The method for importing and exporting Excel data according to claim 1, comprising the steps of: the step of data import further comprises:

and according to the target object, performing library writing storage or data display on the data.

7. The method for importing and exporting Excel data according to claim 1, comprising the steps of: the step of data derivation further comprises:

and filling the data into the corresponding cells by utilizing a packet supporting office reading and writing in the Java according to the target object and the cell form matched by the data and the table information of the database.

8. An Excel data import and export device is characterized in that: comprises that

9. A readable storage medium, characterized by: the readable storage medium stores a computer program, and when the computer program is executed, the method for importing and exporting Excel data according to any one of claims 1 to 7 can be realized.

10. A computer device, characterized by: comprising a processor and a readable storage medium as claimed in claim 9, the processor being capable of executing a computer program stored by the readable storage medium.