CN115828866A - A method and device for importing and exporting Excel data - Google Patents
A method and device for importing and exporting Excel dataDownload PDFInfo
- Publication number
- CN115828866A CN115828866ACN202211163489.5ACN202211163489ACN115828866ACN 115828866 ACN115828866 ACN 115828866ACN 202211163489 ACN202211163489 ACN 202211163489ACN 115828866 ACN115828866 ACN 115828866A
- Authority
- CN
- China
- Prior art keywords
- cell
- name
- data
- information
- excel
- 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
Description
Translated fromChinese技术领域technical field
本发明涉及计算机技术领域，特别是涉及一种Excel数据导入及导出方法及装置、可读存储介质和计算机设备。The invention relates to the technical field of computers, in particular to an Excel data import and export method and device, a readable storage medium and computer equipment.
背景技术Background technique
在实际的业务系统中，会用到较多Excel相关的导入及导出操作，而Excel报表的导入和导出，会涉及按行显示(即每行单元格显示的内容字段属性一致)和按固定单元格显示(即某个单元格固定显示某个字段属性)，有时同一份文件还会涉及多个工作表页。In the actual business system, many Excel-related import and export operations will be used, and the import and export of Excel reports will involve displaying by row (that is, the content field attributes displayed by each row of cells are consistent) and by fixed unit Cell display (that is, a certain cell fixedly displays a certain field attribute), and sometimes the same file involves multiple worksheet pages.
目前大多数方案是先定义好要读写的Excel数据对象，然后根据Excel模板的字段进行匹配。每个导入导出的Excel都要定义一个数据对象，如果对每一份表格进行开发处理的话会涉及过多的重复工作，不仅加大工作量，也会降低了效率，影响项目的进度。Most of the current solutions are to first define the Excel data objects to be read and written, and then match them according to the fields of the Excel template. Each imported and exported Excel must define a data object. If each form is developed and processed, it will involve too much repetitive work, which will not only increase the workload, but also reduce efficiency and affect the progress of the project.
而且现有的Excel数据导入及导出方法，需要上传模板至服务器中，若所述模板内容发生变化的话，则需要重新上传模板，对于模板的内容无法取舍，而且若模板发生变化，则同步需要修改代码保证功能的正常实现。Moreover, the existing Excel data import and export methods need to upload the template to the server. If the content of the template changes, the template needs to be uploaded again. There is no choice for the content of the template, and if the template changes, the synchronization needs to be modified. The code guarantees the normal implementation of the function.
发明内容Contents of the invention
基于此，本发明的目的在于，提供一种Excel数据导入及导出方法及装置，其具有灵活的配置表格信息，模板发生变化时可以直接修改对应的变化部分，不需要重新上传模板以及修改代码的优点。Based on this, the object of the present invention is to provide a method and device for importing and exporting Excel data, which has flexible configuration form information, and can directly modify the corresponding changed part when the template changes, without re-uploading the template and modifying the code advantage.
一种Excel数据导入及导出的方法，包括以下步骤：A method for Excel data import and export, comprising the following steps:
将导入或导出的数据与预设的数据库的表格信息进行匹配，生成新加字段信息，其中，所述预设的数据库包括上传的Excel模板的表格信息，所述表格信息包括表格名称、业务单据类型、表格形式、单元格名称、单元格内容以及单元格形式；Match the imported or exported data with the table information of the preset database to generate newly added field information, wherein the preset database includes the table information of the uploaded Excel template, and the table information includes the table name, business documents type, table form, cell name, cell content, and cell form;
创建一个数据对象，所述数据对象中不包含字段信息；Create a data object that does not contain field information;
将所述数据对象和所述新加字段信息的字段名组合生成一个新字段名，将所述数据对象和所述新加字段信息的字段属性组合生成一个新字段属性，将所述数据对象和所述新加字段信息的字段值组合生成一个新字段值；Combining the data object and the field name of the newly added field information to generate a new field name, combining the data object and the field attribute of the newly added field information to generate a new field attribute, combining the data object and The field value combination of the newly added field information generates a new field value;
使用代码生成包创建一个目标对象，将所述新字段名、新字段属性和新字段值赋值给所述目标对象；Create a target object using a code generation package, and assign the new field name, new field attribute and new field value to the target object;
根据所述目标对象进行所述数据的导入或导出。The import or export of the data is performed according to the target object.
一种Excel数据导入及导出装置，包括An Excel data import and export device, comprising
数据匹配模块，用于将导入或导出的数据与预设的数据库的表格信息进行匹配，生成新加字段信息，其中，所述预设的数据库包括上传的Excel模板的表格信息，所述表格信息包括表格名称、业务单据类型、表格形式、单元格名称、单元格内容以及单元格形式；The data matching module is used to match the imported or exported data with the table information of the preset database to generate new field information, wherein the preset database includes the table information of the uploaded Excel template, and the table information Including table name, business document type, table form, cell name, cell content and cell form;
数据对象生成模块，用于创建一个数据对象，所述数据对象中不包含字段信息；A data object generating module, configured to create a data object, the data object does not contain field information;
数据组合模块，用于将所述数据对象和所述新加字段信息的字段名组合生成一个新字段名，将所述数据对象和所述新加字段信息的字段属性组合生成一个新字段属性，将所述数据对象和所述新加字段信息的字段值组合生成一个新字段值；A data combination module, configured to combine the data object and the field name of the newly added field information to generate a new field name, and combine the data object and the field attribute of the newly added field information to generate a new field attribute, Combining the data object and the field value of the newly added field information to generate a new field value;
目标对象生成模块，用于使用代码生成包创建一个目标对象，将所述新字段名、新字段属性和新字段值赋值给所述目标对象；A target object generation module, configured to use a code generation package to create a target object, and assign the new field name, new field attribute and new field value to the target object;
导入及导出执行模块，用于根据所述目标对象进行所述数据的导入或导出。The import and export execution module is used to import or export the data according to the target object.
一种可读存储介质，其特征在于：所述可读存储介质中存储有计算机程序，所述计算机程序被执行时，可以实现上述Excel数据导入及导出的方法。A readable storage medium, characterized in that: a computer program is stored in the readable storage medium, and when the computer program is executed, the above-mentioned method for importing and exporting Excel data can be realized.
一种计算机设备，其特征在于：包括处理器和上述可读存储介质，所述处理器可以执行所述可读存储介质存储的计算机程序。A computer device is characterized by including a processor and the above-mentioned readable storage medium, and the processor can execute the computer program stored in the readable storage medium.
与现有技术相比，本发明提供的实施例至少具有以下有益效果：Compared with the prior art, the embodiments provided by the present invention have at least the following beneficial effects:
本申请中，通过包括上传Excel模板和/或通过模板配置页面手动设置所述表格信息获取所述预设的数据库，将需要进行导入或导出的数据与所述数据库进行匹配，结合数据对象通过动态赋值获取目标对象，然后利用所述目标对象进行导入或导出。在本申请中，若所述Excel模板发生变化，可以直接通过所述模板配置页面对发生变化对应的部分进行调整，不需要进行重新上传模板，同时，本申请中将所述表格信息上传至所述数据库中，通过动态赋值获取目标对象实现导入和导出，保证了在所述Excel模板发生变化时，不会影响到对所述数据导入和导出的代码，使得开发人员无需针对不同的模板再次开发代码，使得Excel的导入和导出更加灵活便捷。In this application, by including uploading an Excel template and/or manually setting the table information through the template configuration page to obtain the preset database, the data that needs to be imported or exported is matched with the database, combined with the data object through dynamic The assignment gets the target object, and then uses the target object to import or export. In this application, if the Excel template changes, you can directly adjust the part corresponding to the change through the template configuration page without re-uploading the template. At the same time, in this application, the form information is uploaded to the In the above database, the import and export of the target object is obtained through dynamic assignment, which ensures that when the Excel template changes, the code for importing and exporting the data will not be affected, so that developers do not need to develop again for different templates The code makes the import and export of Excel more flexible and convenient.
为了更好地理解和实施，下面结合附图详细说明本发明。For better understanding and implementation, the present invention will be described in detail below in conjunction with the accompanying drawings.
附图说明Description of drawings
图1为本申请实施例中Excel数据导入及导出方法的步骤流程图；Fig. 1 is the step flowchart of Excel data import and export method in the embodiment of the present application;
图2为本申请实施例Excel数据导入及导出方法中上传Excel模板的步骤流程图；Fig. 2 is the flow chart of the steps of uploading the Excel template in the Excel data import and export method of the embodiment of the present application;
图3为本申请实施例Excel数据导入及导出方法中通过模板配置页面手动设置表格信息的步骤流程图；Fig. 3 is the flow chart of the steps of manually setting form information through the template configuration page in the Excel data import and export method of the embodiment of the present application;
图4为本申请实施例中提供的一种Excel数据导入及导出装置的示意图。FIG. 4 is a schematic diagram of an Excel data import and export device provided in the embodiment of the present application.
具体实施方式Detailed ways
为了使本申请的目的、技术方案及优点更加清楚明白，以下结合附图及实施例，对本申请进行进一步详细说明。应当理解，此处描述的具体实施例仅仅用以解释本申请，并不用于限定本申请。In order to make the purpose, technical solution and advantages of the present application clearer, the present application will be further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described here are only used to explain the present application, and are not intended to limit the present application.
请参阅图1，图1为图1为本申请实施例中Excel数据导入及导出的步骤流程图。Please refer to FIG. 1 . FIG. 1 is a flow chart of the steps of importing and exporting Excel data in the embodiment of the present application.
一种Excel数据导入及导出方法，包括以下步骤：An Excel data import and export method, comprising the following steps:
S101，将导入或导出的数据与预设的数据库的表格信息进行匹配，生成新加字段信息，其中，所述预设的数据库包括上传的Excel模板的表格信息，所述表格信息包括表格名称、业务单据类型、表格形式、单元格名称、单元格内容以及单元格形式；S101, matching the imported or exported data with the form information of the preset database to generate newly added field information, wherein the preset database includes the form information of the uploaded Excel template, and the form information includes form name, Business document type, table format, cell name, cell content and cell format;
S102，创建一个数据对象，所述数据对象中不包含字段信息；S102, create a data object, the data object does not contain field information;
S103，将所述数据对象和所述新加字段信息的字段名组合生成一个新字段名，将所述数据对象和所述新加字段信息的字段属性组合生成一个新字段属性，将所述数据对象和所述新加字段信息的字段值组合生成一个新字段值；S103, combine the data object and the field name of the newly added field information to generate a new field name, combine the data object and the field attribute of the newly added field information to generate a new field attribute, and combine the data The object and the field value of the newly added field information are combined to generate a new field value;
S104，使用代码生成包创建一个目标对象，将所述新字段名、新字段属性和新字段值赋值给所述目标对象；S104, using the code generation package to create a target object, assigning the new field name, new field attribute and new field value to the target object;
S105，根据所述目标对象进行所述数据的导入或导出。S105. Import or export the data according to the target object.
本实施例中，所述Excel数据导入及导出方法主要是由计算机执行，通过上传模板和模板配置页面手动配置表格信息获取/更新所述预设的数据库，所述计算机通过将需要进行导入或导出的数据与所述预设的数据库中的表格信息进行匹配，获取新加字段信息，所述计算机创建一个不包含字段信息的数据对象，将所述数据对象与所述新加字段信息进行合并生成目标字段信息，所述计算机利用所述字段信息对所述数据进行导入或导出。本申请中的Excel数据导入及导出方法通过将表格信息保存至数据库中，在进行导入或导出的时候，通过数据库获取表格信息，实现了一次配置多次使用，而且通过模板配置页面可以灵活的配置数据库中的表格信息，更加灵活方便，并且在需要新增导入或导出时，可以直接通过模板配置页面进行调整，不需要重新开发代码。In this embodiment, the Excel data import and export method is mainly performed by a computer, and the preset database is acquired/updated by uploading templates and manually configuring form information on the template configuration page, and the computer imports or exports the required Match the data with the table information in the preset database to obtain newly added field information, the computer creates a data object that does not contain field information, and merges the data object and the newly added field information to generate Target field information, the computer uses the field information to import or export the data. The Excel data import and export method in this application saves the table information in the database, and obtains the table information through the database when importing or exporting, so as to realize one-time configuration and multiple use, and it can be flexibly configured through the template configuration page The table information in the database is more flexible and convenient, and when new import or export needs to be added, it can be adjusted directly through the template configuration page without redeveloping the code.
对于步骤S101，将导入或导出的数据与预设的数据库的表格信息进行匹配，生成新加字段信息，其中，所述预设的数据库包括上传的Excel模板的表格信息，所述表格信息包括表格名称、业务单据类型、表格形式、单元格名称、单元格内容以及单元格形式；For step S101, the imported or exported data is matched with the form information of the preset database to generate newly added field information, wherein the preset database includes the form information of the uploaded Excel template, and the form information includes the form Name, business document type, table format, cell name, cell content, and cell format;
其中所述预设的数据库是提前设置好的用于对需要进行导入或导出操作的数据进行匹配的数据库，所述数据库中存储的所述表格信息是Excel表格包括的关键信息。所述数据库中存储的表格信息是通过上传Excel模板获取，进一步地，也可以通过模板配置页面设置所述Excel模板的表格信息的方式获取。在一个实施例中，所述表格信息包括表格名称、业务单据类型、表格形式、单元格名称、单元格内容以及单元格形式。所述新加字段信息是通过将需要导入或导出的数据与所述数据库中的表格信息进行匹配获取的。The preset database is a database set in advance for matching data that needs to be imported or exported, and the table information stored in the database is the key information included in the Excel table. The form information stored in the database is obtained by uploading an Excel template, and further, can also be obtained by setting the form information of the Excel template through the template configuration page. In one embodiment, the form information includes form name, business document type, form form, cell name, cell content and cell form. The newly added field information is obtained by matching the data to be imported or exported with the table information in the database.
在一个实施例中，请参阅图2，图2为本申请实施例中上传Excel模板的步骤流程图，通过上传Excel模板获取所述预设的数据库的表格信息包括以下步骤：In one embodiment, referring to FIG. 2, FIG. 2 is a flow chart of steps for uploading an Excel template in the embodiment of the present application, and obtaining the form information of the preset database by uploading the Excel template includes the following steps:
S201，导入Excel模板，根据所述Excel模板获取所述Excel模板的表格形式；S201, importing an Excel template, and obtaining the form form of the Excel template according to the Excel template;
S202，若所述表格形式为列表形式，获取所述Excel模板的表格名称和业务单据类型，识别出单元格名称、单元格内容以及单元格形式，将所述表格名称、业务单据类型、单元格名称、单元格内容以及单元格形式作为表格信息保存至所述预设的数据库中，并标记为列表形式；S202. If the form is in list form, obtain the form name and business document type of the Excel template, identify the cell name, cell content and cell form, and set the form name, business document type, and cell The name, cell content and cell form are stored in the preset database as table information, and marked as a list form;
S203，若所述表格形式为固定表格形式，获取所述Excel模板的表格名称和业务单据类型，识别出单元格名称，静态填充的单元格内容、动态填充的单元格内容以及单元格形式，将所述表格名称、业务单据类型、单元格名称、静态填充的单元格内容、动态填充的单元格内容以及单元格形式作为表格信息保存至预设的数据库中，并标记为固定表格形式。S203, if the table form is a fixed table form, obtain the table name and business document type of the Excel template, identify the cell name, statically filled cell content, dynamically filled cell content and cell form, and The table name, business document type, cell name, statically filled cell content, dynamically filled cell content and cell form are stored as form information in a preset database and marked as a fixed form form.
对于步骤S201，导入Excel模板，根据所述Excel模板获取所述Excel模板的表格形式；For step S201, import an Excel template, and obtain the tabular form of the Excel template according to the Excel template;
其中，所述Excel模板是将一定的格式、文字通过公式保存下来，在使用时可以直接被调用生成Excel表格。通过使用Excel模板获取Excel的表格形式，在进行导入和/或导出时，可以直接匹配相关内容，无需再对数据和公式进行设置。Wherein, the Excel template saves a certain format and text through a formula, and can be directly called to generate an Excel table when used. By using the Excel template to obtain the form of Excel, when importing and/or exporting, you can directly match the relevant content without setting the data and formulas.
所述表格形式包括列表形式和固定表格形式，其中，所述列表形式是通过列表样式制作的Excel表格，在一个实施例中，所述列表形式如下表所示：The tabular form includes a tabular form and a fixed tabular form, wherein the tabular form is an Excel form made by a tabular style, and in one embodiment, the tabular form is as shown in the following table:
所述固定表格形式是Excel表格中包括固定内容和变化的内容的表格，在一个实施例中，所述固定表格形式如下表所示：Described fixed tabular form is the form that comprises fixed content and the content of change in Excel form, and in one embodiment, described fixed tabular form is as shown in the following table:
在本实施例中，通过将所述表格形式分为列表形式和固定表格形式，便于在进行Excel数据导入或导出时，对所述Excel数据导入至对应表格形式的表格。In this embodiment, by dividing the table form into a list form and a fixed form form, it is convenient to import the Excel data into a table in a corresponding form form when importing or exporting Excel data.
对于步骤S202，若所述表格形式为列表形式，获取所述Excel模板的表格名称和业务单据类型，识别出单元格名称、单元格内容以及单元格形式，将所述表格名称、业务单据类型、单元格名称、单元格内容以及单元格形式作为表格信息保存至所述预设的数据库中，并标记为列表形式；For step S202, if the form is in list form, obtain the form name and business document type of the Excel template, identify the cell name, cell content and cell form, and set the form name, business document type, The cell name, cell content and cell form are stored in the preset database as table information, and marked as a list form;
其中，所述表格名称是所述表格的名称，用于表明需要进行导入或导出的Excel表格是什么表格，例如：表格的名称是2022年平台流量表、2022第一季度利润表等。所述业务单据类型是所述表格对应的是什么样的内容，在一个实施例中，所述业务单据类型包括平台流量表、入库明细表、利润表。所述单元格名称是所述单元格中填充的名称；所述单元格内容是所述单元格需要填充的内容；所述单元格形式是指所述单元格是否需要合并、需要合并单元格的数量以及单元格格式等信息。Wherein, the table name is the name of the table, which is used to indicate what kind of Excel table needs to be imported or exported, for example: the name of the table is the 2022 platform flow table, the 2022 first quarter profit table, etc. The business document type is what kind of content the table corresponds to. In one embodiment, the business document type includes a platform flow table, a storage list, and a profit statement. The cell name is the name filled in the cell; the cell content is the content to be filled in the cell; the cell form refers to whether the cell needs to be merged and the cell needs to be merged Information such as quantity and cell format.
在一个实施例中，对于识别单元格名称、单元格内容以及单元格形式，可以使用Java中支持对office读写的包来对Excel模板内容进行识别，其中所述Java中支持对office读写的包是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。In one embodiment, for identifying the cell name, cell content and cell form, a package that supports reading and writing of office in Java can be used to identify the content of the Excel template, wherein the package that supports reading and writing of office in the Java The package is an open source library of the Apache Software Foundation, and POI provides an API for Java programs to read and write Microsoft Office format files.
对于上传的Excel模板，在本实施例中首先判断所述Excel模板的表格形式，若所述表格形式为列表形式，则首先获取所述Excel模板的表格名称和业务单据类型，然后使用所述Java中支持对office读写的包对所述Excel模板进行读取，从而获取所述Excel模板的单元格名称，单元格内容以及单元格形式。最后，将获取的所述表格名称、业务单据类型单元格名称、单元格内容以及单元格形式作为表格信息保存至所述预设的数据库中，并标记为列表形式，用于在进行数据进行导入或导出时，通过与所述预设的数据库中的表格信息，识别出所述数据对应的表格名称、业务单据类型单元格名称、单元格内容以及单元格形式，方便进行数据的导入或导出。For the uploaded Excel template, in this embodiment first judge the form form of the Excel template, if the form form is a list form, then first obtain the form name and business document type of the Excel template, and then use the Java The package that supports reading and writing to office reads the Excel template, so as to obtain the cell name, cell content and cell form of the Excel template. Finally, save the obtained table name, business document type cell name, cell content, and cell form as table information in the preset database, and mark it as a list form for data import Or when exporting, through the table information in the preset database, identify the table name, business document type cell name, cell content and cell form corresponding to the data, so as to facilitate data import or export.
对于步骤S203，若所述表格形式为固定表格形式，获取所述Excel模板的表格名称和业务单据类型，识别出单元格名称，静态填充的单元格内容、动态填充的单元格内容以及单元格形式，将所述表格名称、业务单据类型、单元格名称、静态填充的单元格内容、动态填充的单元格内容以及单元格形式作为表格信息保存至预设的数据库中，并标记为固定表格形式；For step S203, if the form is a fixed form, obtain the form name and business document type of the Excel template, identify the cell name, statically filled cell content, dynamically filled cell content, and cell form , saving the table name, business document type, cell name, statically filled cell content, dynamically filled cell content, and cell form as form information in a preset database, and marking it as a fixed form form;
若判断所述表格形式为固定表格形式，相较于所述列表形式的表格，所述固定表格形式还需要识别出需要静态填充的单元格内容和需要动态填充的单元格内容并保存至所述数据库中，其中所述静态填充是对Excel表格中单元格内容为固定内容进行填充，所述动态填充是对Excel表格中单元格内容会发生变化的内容进行填充。在一个实施例中，若所述单元格内容中存在##，则表明所述单元格内容为动态填充，否则所述单元格内容为静态填充。If it is judged that the table form is a fixed table form, compared with the table in the list form, the fixed form form also needs to identify the cell content that needs to be filled statically and the cell content that needs to be dynamically filled and save it to the In the database, the static filling is to fill the cell content in the Excel table with fixed content, and the dynamic filling is to fill the cell content in the Excel table with changing content. In one embodiment, if ## exists in the cell content, it indicates that the cell content is dynamically filled, otherwise, the cell content is statically filled.
在一个实施例中，请参阅图3，图3为本申请实施例中通过模板配置页面手动设置表格信息更新所述数据库的步骤流程图，通过模板配置页面设置所述模板的步骤：In one embodiment, please refer to FIG. 3. FIG. 3 is a flow chart of steps for manually setting form information to update the database through the template configuration page in the embodiment of the present application, and the steps for setting the template through the template configuration page:
S301，打开模板配置页面，选择Excel表格的表格形式；S301, open the template configuration page, and select the form of the Excel table;
S302，若所述表格形式为列表形式，则在所述模板配置页面填写表格名称、业务单据类型、单元格名称、单元格内容以及单元格形式，并将所述表格名称、业务单据类型、单元格名称、单元格内容以及单元格形式作为表格信息保存至预设的数据库中，并标记为列表形式；S302. If the form is in the form of a list, fill in the form name, business document type, cell name, cell content and cell form on the template configuration page, and set the form name, business document type, unit Save the cell name, cell content and cell form as table information to the preset database, and mark it as a list form;
S303，若所述表格形式为固定表格形式，则在所述目标配置页面填写表格名称、业务单据类型、单元格名称、静态填充的单元格内容、动态填充的单元格内容以及单元格形式，并将所述表格名称、业务单据类型、单元格名称、静态填充的单元格内容、动态填充的单元格内容以及单元格形式保存至预设的数据库中，并标记为固定表格信息。S303. If the form is a fixed form, fill in the form name, business document type, cell name, statically filled cell content, dynamically filled cell content, and cell form on the target configuration page, and The table name, business document type, cell name, statically filled cell content, dynamically filled cell content, and cell form are saved in a preset database and marked as fixed table information.
对于步骤S301，打开模板配置页面，选择Excel表格的表格形式；For step S301, open the template configuration page, and select the form of the Excel table;
其中，所述模板配置页面是用于配置所述Excel模板的页面，通过所述Excel模板配置页面可以手动配置所述Excel模板，若步骤S201中上传的Excel模板需要发生变化，也可以通过所述模板配置页面直接调整对应的变化位置，不需要再重新上传模板进行识别。通过所述模板配置页面设置所述Excel表格的表格信息，首先需要选择需要的Excel表格的表格形式为列表形式或固定表格形式。Wherein, 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, if the Excel template uploaded in step S201 needs to change, it can also be configured through the The template configuration page directly adjusts the corresponding change position, and there is no need to re-upload the template for identification. To set the form information of the Excel form through the template configuration page, you first need to select the required form of the Excel form as a list form or a fixed form.
对于步骤S302，若所述表格形式为列表形式，则在所述模板配置页面填写表格名称、业务单据类型、单元格名称、单元格内容以及单元格形式，并将所述表格名称、业务单据类型、单元格名称、单元格内容以及单元格形式作为表格信息保存至预设的数据库中，并标记为列表形式；For step S302, if the form is in the form of a list, fill in the form name, business document type, cell name, cell content and cell form on the template configuration page, and set the form name, business document type , cell name, cell content and cell form are stored as table information in the preset database and marked as a list form;
通过所述模板配置页面设置所述Excel表格的表格信息，在本实施例中首先选择所述Excel模板的表格形式，若所述表格形式为列表形式，则首先通过所述模板配置页面填写所述Excel模板的表格名称和业务单据类型，然后填写所述Excel模板的单元格名称，单元格内容以及单元格形式。最后，将获取的所述表格名称、业务单据类型单元格名称、单元格内容以及单元格形式作为表格信息保存至所述预设的数据库中，并标记为列表形式，用于在进行数据进行导入和/或导出时，通过与所述预设的数据库中的表格信息，识别出所述数据对应的表格名称、业务单据类型单元格名称、单元格内容以及单元格形式，方便进行数据的导入或导出。The form information of described Excel table is set by described template configuration page, first selects the form form of described Excel template in the present embodiment, if described form form is list form, then first fills in described by described template configuration page The table name and business document type of the Excel template, and then fill in the cell name, cell content and cell form of the Excel template. Finally, save the obtained table name, business document type cell name, cell content, and cell form as table information in the preset database, and mark it as a list form for data import And/or when exporting, through the table information in the preset database, identify the table name, business document type cell name, cell content and cell form corresponding to the data, so as to facilitate data import or export.
对于步骤S303，若所述表格形式为固定表格形式，则在所述目标配置页面填写表格名称、业务单据类型、单元格名称、静态填充的单元格内容、动态填充的单元格内容以及单元格形式，并将所述表格名称、业务单据类型、单元格名称、静态填充的单元格内容、动态填充的单元格内容以及单元格形式保存至预设的数据库中，并标记为固定表格信息；For step S303, if the form is a fixed form, fill in the form name, business document type, cell name, statically filled cell content, dynamically filled cell content, and cell form on the target configuration page , and save the table name, business document type, cell name, statically filled cell content, dynamically filled cell content, and cell form in a preset database, and mark it as fixed table information;
若选择所述表格形式为固定表格形式，相较于所述列表形式的表格，所述固定表格形式还需要填写需要静态填充的单元格内容和需要动态填充的单元格内容并保存至所述数据库中，在一个实施例中，若所述单元格内容中存在##，则表明所述单元格内容为动态填充，否则所述单元格内容为静态填充。If the form is selected as a fixed form, compared to the list form, the fixed form also needs to fill in the cell content that needs to be filled statically and the cell content that needs to be dynamically filled and saved to the database In, in one embodiment, if ## exists in the cell content, it indicates that the cell content is dynamically filled, otherwise the cell content is statically filled.
在本实施例中，通过上传Excel模板和通过模板配置页面手动设置所述表格信息两种方式获取所述预设的数据库信息，保证了获取表格信息的便利性，为所述数据的导入或导出提供了丰富的数据库信息。而且通过模板配置页面设置表格信息获取/更新所述预设的数据库，保证了在所述表格信息发生变化时，可以及时的调整所述预设的数据中的表格信息，而且对于仅部分内容发生变化的Excel模板不需要重新上传模板解析，只需要通过所述模板配置页面调整对应的表格信息，使得Excel数据在进行导入或导出时的操作更加灵活。进一步地，根据所述模板配置页面调整单元格信息，获取所述预设的数据库，保证了在所述Excel模板更新时，不需要重新调整代码以适应新的Excel模板。In this embodiment, the preset database information is obtained by uploading an Excel template and manually setting the table information through the template configuration page, which ensures the convenience of obtaining the table information, and provides the import or export of the data Provides a wealth of database information. Moreover, the preset database is acquired/updated by setting the table information on the template configuration page, which ensures that when the table information changes, the table information in the preset data can be adjusted in time, and only part of the content occurs The changed Excel template does not need to be re-uploaded for template analysis, but only needs to adjust the corresponding table information through the template configuration page, making the operation of Excel data more flexible when importing or exporting. Further, the cell information is adjusted according to the template configuration page, and the preset database is obtained, which ensures that when the Excel template is updated, the code does not need to be readjusted to adapt to the new Excel template.
对于步骤S102，创建一个数据对象，所述数据对象中不包含字段信息；For step S102, create a data object, which does not contain field information;
其中，所述数据对象是一种软件理解的复合信息表示，本实施例中所述数据对象是一个空的数据对象，不包括字段信息，用于与所述新加字段信息进行匹配获取目标字段信息。Wherein, the data object is a composite information representation that software understands. In this embodiment, the data object is an empty data object that does not include field information, and is used to match with the newly added field information to obtain the target field. information.
对于步骤S103，将所述数据对象和所述新加字段信息的字段名组合生成一个新字段名，将所述数据对象和所述新加字段信息的字段属性组合生成一个新字段属性，将所述数据对象和所述新加字段信息的字段值组合生成一个新字段值；For step S103, combine the data object and the field name of the newly added field information to generate a new field name, combine the data object and the field attribute of the newly added field information to generate a new field attribute, and Combining the above data object and the field value of the newly added field information to generate a new field value;
所述数据对象和所述新加字段信息中均包括字段名，字段属性以及字段值，对于获取的数据对象和所述新加字段信息，将所述数据对象和所述新加字段信息中的字段名、字段属性和字段值分别进行组合从而获取一组新字段名、新字段属性以及新字段值，用于获取目标对象。Both the data object and the newly added field information include a field name, field attribute and field value, and for the acquired data object and the newly added field information, the data object and the newly added field information The field name, field attribute and field value are respectively combined to obtain a set of new field name, new field attribute and new field value, which are used to obtain the target object.
S104，使用代码生成包创建一个目标对象，将所述新字段名、新字段属性和新字段值赋值给所述目标对象；S104, using the code generation package to create a target object, assigning the new field name, new field attribute and new field value to the target object;
其中，所述代码生成包是一个Code生成类库，它可以在运行期扩展Java类与实现Java接口。Wherein, the code generation package is a code generation class library, which can extend Java classes and implement Java interfaces during runtime.
在一个实施例中，所述将所述新字段名、新字段属性和新字段值赋值给所述目标对象包括以下步骤：In one embodiment, said assigning said new field name, new field attribute and new field value to said target object comprises the following steps:
对所述目标对象使用所述新字段名和新字段属性信息生成相应的字段信息；Using the new field name and new field attribute information for the target object to generate corresponding field information;
利用所述目标对象生成一个静态BeanMap，将所述新字段名和新字段值赋值给所述静态BeanMap。A static BeanMap is generated by using the target object, and the new field name and new field value are assigned to the static BeanMap.
其中，所述字段信息包括字段名和字段属性，通过所述新字段名和新字段属性赋值给所述目标对象，从而获取所述目标对象的字段信息。Wherein, the field information includes a field name and a field attribute, and the field information of the target object is obtained by assigning the new field name and the new field attribute to the target object.
通过使用所述目标对象，生成一个静态BeanMap，并将所述新字段名和新字段值赋值给所述静态BeanMap，因所述静态BeanMap是由所述目标所述生成的，所以所述静态BeanMap与所述目标对象的值相对应，使得所述目标对象可以获取到所述字段值，所以目标对象最终为所述数据对象和新加字段信息合并后的新对象。By using the target object, generate a static BeanMap, and assign the new field name and new field value to the static BeanMap, because the static BeanMap is generated by the target, so the static BeanMap and The value of the target object is corresponding, so that the target object can obtain the field value, so the target object is finally a new object after combining the data object and the newly added field information.
在一个实施例中，对于步骤S103-S104进一步可以理解为：In one embodiment, steps S103-S104 can be further understood as:
通过使用操作JAVA实体类的工具包获取所述数据对象的字段信息，并将所述字段信息分别保存至第一集合和第二集合中，其中第一集合的键为字段名，值为字段属性；第二集合的键为字段名，值为字段值；所述操作JAVA实体类的工具包是Apache开源组织提供的用于操作JAVA BEAN的工具包，可以很方便的对bean对象的属性进行操作。Obtain the field information of the data object by using the toolkit for manipulating the JAVA entity class, and save the field information into the first collection and the second collection respectively, wherein the key of the first collection is the field name, and the value is the field attribute The key of the second set is the field name, and the value is the field value; the toolkit for operating the JAVA entity class is a toolkit for operating JAVA BEAN provided by the Apache open source organization, and can easily operate the properties of the bean object .
同样的，将所述新加字段信息保存至第三集合和第四集合中，其中，第三集合的键为字段名，值为字段属性；第四集合的键为字段名，值为字段值。Similarly, the newly added field information is stored in the third set and the 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 .
将所述第一集合和第三集合组合形成一个包括新的字段属性的第五集合，将所述第二集合和第四集合合并形成一个包括新的字段值的第六集合。The first set and the third set are combined to form a fifth set including new field attributes, and the second set and fourth set are combined to form a sixth set including new field values.
通过对所述目标对象使用第五集合的信息生成相应的字段信息；generating corresponding field information by using the fifth set of information on the target object;
使用所述目标对象创建一个静态BeanMap，将所述第六集合赋值给所述静态BeanMap。A static BeanMap is created by using the target object, and the sixth set is assigned to the static BeanMap.
对于步骤S105，根据所述目标对象进行所述数据的导入或导出；For step S105, importing or exporting the data according to the target object;
根据从步骤S101-S104获取的所述目标对象，从而进行所述数据导入或导出。The data import or export is performed according to the target object obtained from steps S101-S104.
在一个实施例中，所述数据的导入还包括以下步骤：In one embodiment, the import of the data also includes the following steps:
根据所述目标对象，进行所述数据的写库保存或数据展示；According to the target object, perform database storage or data display of the data;
在进行导入操作时，首先通过步骤S101-104进行处理获取所述目标对象，然后，根据所述目标对象获取到的表格信息，将对应表格的数据从Excel表格中导入至对应的文件中进行写库保存或数据展示，最终将需要导入的数据保存或展示出来。When performing the import operation, first process and obtain the target object through steps S101-104, and then, according to the table information obtained by the target object, import the data of the corresponding table from the Excel table into the corresponding file for writing Library storage or data display, and finally save or display the data that needs to be imported.
在一个实施例中，所述数据的导出还包括以下步骤：In one embodiment, the export of the data also includes the following steps:
根据所述目标对象，结合所述数据与所述数据库的表格信息匹配到的单元格形式，利用所述Java中支持对office读写的包将所述数据填充到对应的单元格中。According to the target object, in combination with the cell form matched by the data and the table information of the database, the data is filled into the corresponding cell by using the package in Java that supports reading and writing of office.
在进行导出操作时，首先通过步骤S101-104进行处理获取所述目标对象，根据所述目标对象，首先结合所述数据与所述数据库的表格信息匹配到的单元格形式，对单元格进行合并、格式调整，之后使用所述Java中支持对office读写的包，将所述数据填充至对应的单元格中。When the export operation is performed, the target object is first processed through steps S101-104, and according to the target object, the cells are merged in combination with the cell form matched by the data and the table information of the database 1. Adjust the format, and then use the package in Java that supports reading and writing to office to fill the data into the corresponding cells.
在本实施例中，通过上传Excel模板和通过模板配置页面手动配置单元格信息，从而获取所述预设的数据库，将需要导入或导出的对象与所述预设的数据库中的信息进行匹配从而获取新加字段信息，最后通过动态赋值获取目标对象，根据所述目标对象对所述数据进行导入或导出，保证了在进行导入或导出时的准确性，同时在进行导入或导出时，使用的是通过与所述数据库匹配后经过处理获取的所述目标对象，保证了在Excel模板发生变化时，可以通过模板配置页面对所述数据库信息进行调整，使得对数据导入或导出进行模板调整时更加灵活便捷，再者，也不需要对导入或导出操作需要执行的代码进行同步变动，更无需开发人员对改变的模板进行再次开发，减少了开发人员的工作量。In this embodiment, the preset database is obtained by uploading an Excel template and manually configuring cell information through the template configuration page, and matching the objects that need to be imported or exported with the information in the preset database Obtain the newly added field information, and finally obtain the target object through dynamic assignment, and import or export the data according to the target object to ensure the accuracy of the import or export. At the same time, the used It is the target object obtained through processing after matching with the database, which ensures that when the Excel template changes, the database information can be adjusted through the template configuration page, making it easier to adjust the template for data import or export. It is flexible and convenient. Moreover, there is no need to make synchronous changes to the code that needs to be executed for import or export operations, and it is not necessary for developers to redevelop the changed templates, which reduces the workload of developers.
请参阅图4，图4为本申请实施例中提供的一种Excel数据导入及导出装置的示意图，本申请还提供了一种Excel数据导入及导出装置，包括：Please refer to Fig. 4. Fig. 4 is a schematic diagram of an Excel data import and export device provided in the embodiment of the present application. The present application also provides a kind of Excel data import and export device, including:
数据匹配模块11，用于将导入或导出的数据与预设的数据库的表格信息进行匹配，生成新加字段信息，其中，所述预设的数据库包括上传的Excel模板的表格信息，所述表格信息包括表格名称、业务单据类型、表格形式、单元格名称、单元格内容以及单元格形式；The
数据对象生成模块12，用于创建一个数据对象，所述数据对象中不包含字段信息；Data object
数据组合模块13，用于将所述数据对象和所述新加字段信息的字段名组合生成一个新字段名，将所述数据对象和所述新加字段信息的字段属性组合生成一个新字段属性，将所述数据对象和所述新加字段信息的字段值组合生成一个新字段值；A
目标对象生成模块14，用于使用代码生成包创建一个目标对象，将所述新字段名、新字段属性和新字段值赋值给所述目标对象；The target
导入及导出执行模块15，用于根据所述目标对象进行所述数据的导入或导出。The import and
本申请还提供了一种可读存储介质，其特征在于：所述可读存储介质中存储有计算机程序，所述计算机程序被执行时，可以实现上述实施例所述的Excel数据导入及导出的方法。The present application also provides a readable storage medium, which is characterized in that: a computer program is stored in the readable storage medium, and when the computer program is executed, the Excel data import and export described in the above embodiment can be realized method.
本申请还提供了一种计算机设备，包括处理器和上述的可读存储介质，所述处理器可以执行所述可读存储介质存储的计算机程序。The present application also provides a computer device, including a processor and the above-mentioned readable storage medium, and the processor can execute the computer program stored in the readable storage medium.
以上所述实施例仅表达了本发明的几种实施方式，其描述较为具体和详细，但并不能因此而理解为对发明专利范围的限制。应当指出的是，对于本领域的普通技术人员来说，在不脱离本发明构思的前提下，还可以做出若干变形和改进，这些都属于本发明的保护范围。The above-mentioned embodiments only express several implementation modes of the present invention, and the descriptions thereof are relatively specific and detailed, but should not be construed as limiting the patent scope of the invention. It should be pointed out that those skilled in the art can make several modifications and improvements without departing from the concept of the present invention, and these all belong to the protection scope of the present invention.
Claims (10)
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211163489.5ACN115828866A (en) | 2022-09-23 | 2022-09-23 | A method and device for importing and exporting Excel data |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211163489.5ACN115828866A (en) | 2022-09-23 | 2022-09-23 | A method and device for importing and exporting Excel data |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115828866Atrue CN115828866A (en) | 2023-03-21 |
Family
ID=85523863
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211163489.5APendingCN115828866A (en) | 2022-09-23 | 2022-09-23 | A method and device for importing and exporting Excel data |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115828866A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN119576443A (en)* | 2024-11-04 | 2025-03-07 | 浪潮卓数大数据产业发展有限公司 | A method to generate data from unordered Excel based on Java |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109783779A (en)* | 2018-12-27 | 2019-05-21 | 北京航天福道高技术股份有限公司 | A kind of data EXCEL imports and exports the tool of conversion |
| CN109977160A (en)* | 2019-03-28 | 2019-07-05 | 上海中通吉网络技术有限公司 | Data manipulation method, device, equipment and storage medium |
| CN113849506A (en)* | 2021-09-15 | 2021-12-28 | 北京京东振世信息技术有限公司 | Data import method, device, equipment and medium |
| CN114528338A (en)* | 2022-01-19 | 2022-05-24 | 浪潮卓数大数据产业发展有限公司 | Method, device, equipment and medium for dynamically exporting field |
- 2022
- 2022-09-23CNCN202211163489.5Apatent/CN115828866A/enactivePending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109783779A (en)* | 2018-12-27 | 2019-05-21 | 北京航天福道高技术股份有限公司 | A kind of data EXCEL imports and exports the tool of conversion |
| CN109977160A (en)* | 2019-03-28 | 2019-07-05 | 上海中通吉网络技术有限公司 | Data manipulation method, device, equipment and storage medium |
| CN113849506A (en)* | 2021-09-15 | 2021-12-28 | 北京京东振世信息技术有限公司 | Data import method, device, equipment and medium |
| CN114528338A (en)* | 2022-01-19 | 2022-05-24 | 浪潮卓数大数据产业发展有限公司 | Method, device, equipment and medium for dynamically exporting field |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN119576443A (en)* | 2024-11-04 | 2025-03-07 | 浪潮卓数大数据产业发展有限公司 | A method to generate data from unordered Excel based on Java |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109948134B (en) | Report automatic generation method, system, electronic equipment and storage medium | |
| CN111367976B (en) | Method and device for exporting EXCEL file data based on JAVA reflection mechanism | |
| CN104484216B (en) | Service interface document and on-line testing instrument generation method, device | |
| TWI577539B (en) | Computer implementation method for runtime system, computer readable storage memory and system | |
| CN110705237B (en) | Automatic document generation method, data processing device and storage medium | |
| CN113609820B (en) | Method, device and equipment for generating word file based on extensible markup language file | |
| US20130061124A1 (en) | Managing forms in electronic documents | |
| CN105094832A (en) | WYSIWYG method and system for dynamically generating user interface | |
| CN113254455B (en) | Dynamic configuration method and device of database, computer equipment and storage medium | |
| CN104731588B (en) | Page layout document generating method and page layout file creating apparatus | |
| CN109829141B (en) | Project table generation method and device | |
| CN103559184A (en) | Form page display method and device | |
| CN105589959A (en) | Form processing method and form processing system | |
| CN111090976B (en) | Method and device for generating inspection document based on dynamic data | |
| CN115293124A (en) | Software engineering document automatic generation method and device | |
| CN110909273B (en) | Page generation method and related system | |
| CN114327435A (en) | Technical document generation method and device and computer readable storage medium | |
| CN113010169A (en) | Method and apparatus for converting UI diagram into code file | |
| CN115828866A (en) | A method and device for importing and exporting Excel data | |
| CN102171696B (en) | Method and device for generating customized data | |
| CN105573973A (en) | Method for performing data correlation on EXCEL data in PPT file | |
| CN112104544B (en) | Method and device for sending dynamic mail based on Freemarker | |
| CN111124482B (en) | Configuration method and device for document information | |
| CN104657340B (en) | Extensible script-based Word report generation system and method | |
| CN108536660A (en) | A method and terminal for generating a custom report |
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 |