Method and system for exporting and importing user-defined service sheet excel

Through user-defined templates and Odoo Web interface configuration, combined with the use of openpyxl and xlrd libraries, efficient Excel export and import of business documents is achieved, solving the problems of high development and maintenance costs in the existing technology, and improving the filling efficiency and accuracy and ensures the normativeness and unity of logic.

CN119005151APending Publication Date: 2024-11-22WUHAN TUYUAN TECHNOLOGY CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410947898.7
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-15
Publication Date
2024-11-22

AI Technical Summary

Technical Problem

In the prior art, the export and import functions of business documents are complex in the internal business logic and diverse in the fields, which require independent development, which increases high development costs and maintenance costs, and it is difficult to ensure the unity of standardization and development quality.

Method used

Using Excel export and import methods and systems based on user-defined templates, the template is configured through the Odoo web interface, and the Excel file is parsed using openpyxl and xlrd libraries to realize the export and import function, supports multi-row table data import, and simulates user input scenarios during import Fill in default values ​​and cascading calculation logic.

Benefits of technology

It reduces development and maintenance costs, improves reporting efficiency and accuracy, enhances flexibility and adaptability, and ensures the standardization of different functional logics and the unity of development quality.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119005151A_ABST
    Figure CN119005151A_ABST
Patent Text Reader

Abstract

The invention provides a user-defined service sheet excel export and import system which comprises a template interface display configuration module, an excel template file configuration module, a file downloading module and a file uploading module, a template configuration interface is provided, a user is allowed to customize the structure, the style, the data mapping relation and the like of an Excel template, and the user-defined Excel template can be used for exporting and importing the Excel template. And the flexibility and adaptability of the system are greatly improved. Meanwhile, the import and export function completely simulates the service logic of a web end interface input service sheet, and the accuracy and filling efficiency of data are ensured. The problems that in the prior art, export and import functions of Excel need to independently develop export and import logic of Excel due to complex internal business logic and various inter-field logic business lists, a large amount of development cost is consumed, and high personalized development cost and maintenance cost are generated along with increase of projects are solved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of data processing and office automation technology, and in particular to a method and system for exporting and importing custom business orders in Excel. Background Art

[0002] With the improvement of the degree of informatization of enterprises, various information systems generally provide Excel export and import functions in order to improve efficiency in filling out business documents. However, the existing technical solutions need to independently develop Excel export and import logic for specific business orders, especially those with complex internal business logic and diverse logic between fields, to ensure that the corresponding export and import can fully match the complex logic of the business order. This not only consumes a lot of development costs, but also generates high personalized development costs and maintenance costs as the number of projects increases. In addition, the independent development method is difficult to ensure the uniformity of different functional logic specifications and development quality. Therefore, the present invention proposes a business order Excel export and import method and configuration system based on user-defined templates, aiming to solve the above problems. At the same time, it is a solution for ordinary users. It does not require code writing technical background, and simple Excel template definition and configuration can achieve efficient and accurate data entry experience. Summary of the invention

[0003] The present invention proposes a method and system for customizing business order export and import in Excel, which solves the problem that the export and import functions of Excel in the prior art require independent development of Excel export and import logic due to complex internal business logic and business orders with diverse logic between fields, which consumes a lot of development costs, and also generates high personalized development costs and maintenance costs as the number of projects increases. The technical solution of the present invention is implemented as follows:

[0004] A system for customizing business order excel export and import, including a template interface display configuration module, an excel template file configuration module, a file download module and a file upload module.

[0005] The template interface display configuration module is a Web interface based on Odoo, including an original template submodule. The user selects the adapted basic Excel template file through the original template submodule, and specifies the import and export model, application model view, enabling conditions, file download button display logic, download file name, upload button display logic, and whether to enable import matching through other submodules. After the interface display configuration is completed, it is dynamically parsed into the interface of the business order through the download template submodule and the upload template submodule;

[0006] The excel template file configuration module includes a template file description submodule to explain and guide the configuration in detail to support the import of data for defining multiple rows in one excel; after configuring each header field, each line field, and the optional value set for filling each V-tab in the excel template file, upload the file to the template configuration described in the above;

[0007] The file download module uses openpyxl to read the excel file in the configuration template, parse each header field, each line field, and the optional value set for filling each V-tab. At the same time, it reads the value of each field of the business order and the attributes of each field in the view, and combines these attributes with the template excel file to control the optional value set of each line field in the V-tab to ensure consistency with the operation in the system;

[0008] When the user uploads the updated download file, the file upload module reads and parses the values ​​and formulas of the given columns in the file through openpyxl and xlrd. When the verification is passed, based on the user importing single data, it simulates the scenario where the user enters the same data in the interface, fills in the missing default values, and implements the cascade calculation logic between fields.

[0009] As a preferred technical solution, the attributes of each field in the view include mandatory attributes, field attributes, optional domain attributes and context attributes.

[0010] As a preferred technical solution, after the interface display configuration is completed, the download template and upload template buttons are dynamically parsed into the business order interface through the get_views method of the Odoo framework.

[0011] As a preferred technical solution, the scenario of simulating the user entering the same data in the interface is realized by calling the system's default_get and onchange.

[0012] As a preferred technical solution, the Excel template file configuration module provides a template file description tab to provide detailed descriptions and guidance on the configuration to support the import of data for multiple row tables defined in one Excel; the layout of each sheet tab in the template refers to the tab layout in Odoo. In the Excel template, users can define the fields that need to be exported to the row table, as well as the matching formula for each field when it is subsequently imported into the system, and can also dynamically control whether a column is editable; at the same time, a V-row field tab is provided in the Excel template file, which corresponds to the L-row field and is used to control the corresponding relationship field in the L tab.

[0013] As a preferred technical solution, the file upload module also includes an import matching module. During import, the system will check the file label. If it is found that it is not the latest downloaded, a corresponding prompt will be given, and the relationship field in the L tab will be converted into a field value readable by the system through the corresponding field column of the corresponding V tab, or the field in the L tab will be dynamically matched to a value readable by the system through a matching formula; when reading and parsing the file, the attributes of each row of the field in the current business order view are obtained, and then the data imported by the user is verified to be valid, and a corresponding prompt will be given.

[0014] A method for customizing the export and import of business orders in Excel, using the above-mentioned system for customizing the export and import of business orders in Excel, comprises the following steps:

[0015] Step S1: template configuration, guiding the user to download a basic Excel template file applicable to the solution through the template interface display configuration module, and specifying the import and export model, application model view, enabling conditions, file download button display logic, download file name, upload button display logic, and whether to enable import matching;

[0016] Step S2: Adjust the initial template according to the business order logic and upload the configuration back;

[0017] Step S3: Download the EXCEL file on the business form and fill it out;

[0018] Step S4: Upload the latest downloaded EXCEL file on the business order. The system will update the business order after data processing, and support adding, deleting and modifying the business order data.

[0019] Compared with the prior art, this solution has the following beneficial effects:

[0020] 1) Reduce development and maintenance costs: Through user-defined templates, the need for independent development for different business units is reduced, thereby reducing development and maintenance costs.

[0021] 2) Improve reporting efficiency and accuracy: The import and export functions completely simulate the business logic of entering business orders on the web interface, ensuring data accuracy and reporting efficiency.

[0022] 3) Enhanced flexibility and adaptability: When adjusting business logic, you only need to adjust the custom template to adapt to the changes, without having to redevelop or optimize the export and import logic.

[0023] 4) Ensure standardization: Through a unified configuration method, the uniformity of different functional logic specifications and development quality is ensured. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0025] Figure 1 A method flow chart of a method for exporting and importing a custom business order in Excel according to the present invention;

[0026] Figure 2 A cross-sectional view of a template interface display configuration module of the present invention;

[0027] Figure 3 The present invention displays a download / upload button on the corresponding service sheet according to the interface configuration;

[0028] Figure 4 One of the excel template configuration interface diagrams of the present invention;

[0029] Figure 5 This is the second diagram of the configuration interface of the Excel template of the present invention;

[0030] Figure 6 This is the third diagram of the excel template configuration interface of the present invention;

[0031] Figure 7 The business single excel file downloaded by the present invention based on the configuration template is updated with error dates and days;

[0032] Figure 8 Upload the excel file for the present invention, check whether the required fields are filled in, and check whether the values ​​filled in each column match the field type of the system. DETAILED DESCRIPTION

[0033] The technical solution of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0034] Reference Figures 1 to 8The present invention relates to the field of data processing and office automation technology. Based on the open source framework Odoo (a development framework derived from the Python programming language), using the Python programming language, combined with the WPS (Excel) office software, the openpyxl library, and the xlrd library, a set of solutions integrating template configuration, business order export, and business order import is designed. This solution uses the openpyxl library to read and update Excel files, and retains their various properties, such as formulas, charts, etc. This solution mainly includes several core functional blocks: template configuration, excel template file configuration, file download, and file upload. According to the functional modules, it can be divided into: interface display configuration module, excel template file configuration module, file download module, file upload module, specifically:

[0035] Template interface display configuration module: Design a template configuration interface through the Odoo web interface, provide a button 'Download original template', users can download the basic Excel template file applicable to the solution, and specify the import and export model, application model view, enable conditions, file download button display logic, download file name, upload button display logic, and whether to enable import matching (after enabling, ensure that only the latest downloaded Excel file of the business order can be uploaded each time to avoid data overwriting problems). After the interface display configuration is completed, the 'Download template' and 'Upload template' buttons are dynamically parsed into the interface of the business order through the framework's get_views method.

[0036] Excel template file configuration module: In the basic Excel file template, a template file description tab is provided (detailed description and guidance of configuration), in which file_download_time is used to record the file download time and can be used to implement the import of only the latest business order file, header is used to map the business itself, header / field_a is used to map the business order field_a field, line is used to map the single line to be imported, line / line_a_ids, line / line_b_ids are used to map the data of the business order a line form and the data of the business order b line table to be imported respectively. This solution supports the import of data defined in multiple lines and tables in one Excel. At the same time, the layout of each sheet tab in the template also refers to the tab layout in Odoo, so that when users use Excel import, they still feel that they are directly entering data in the system (this is based on the introduction of the openypyxl library). In the Excel template, users can define the fields that need to be exported to the line table, as well as the matching formula for each field when it is subsequently imported into the system, and can also dynamically control whether a column is editable (for example, the column corresponding to field a can be updated only when the business is downloaded in the draft state). At the same time, the template file provides a V-row field tab, which corresponds to the L-row field and is used to control the optional value set of the corresponding relationship field (such as m2o, selection) in this business order in the L tab (this is a core function of this solution). Through precise control of the optional value set, users can normally enter data with strong logical associations when leaving the Odoo system, without entering junk data (such as entering the supplier of organization B into the business order of organization A). In the V tab, the execution definition requires the value set to control the single-row field name and when importing, use the attribute of this field as the key value for import matching, which can be id, code, etc. We configure the excel template file and upload the file to the template configuration in the above Odoo.

[0037] File download module: In our system, the corresponding export and import models are configured. In the corresponding view, when the document meets the display logic of the download button, we can see the download button. When downloading, the solution will use openpyxl to read the excel file in the configuration template, parse the header fields, line fields, and fill in the optional value sets of each V-tab. In this process, the program will read the values ​​of each field in the business order and the attributes of each field in the view (such as required attributes, field attributes, optional domain attributes, context attributes), and combine these attributes with the template excel file to control the optional value set of each line field in the V-tab to ensure consistency with the operation in the system.

[0038] File upload module: When the user uploads the updated downloaded file, this solution will use openpyxl and xlrd to read and parse the values ​​and formulas of the given columns in the file. If import matching is configured, the system will check the file during import. If it is found that it is not the latest download, it will prompt: 'The file you uploaded is not the latest download, please upload the latest file or download it again! ', convert the relational field in the L tab into a field value that can be read by the system through the corresponding field column of the corresponding V tab, or dynamically match the field in the L tab to a value that can be read by the system through the matching formula. When reading and parsing the file again, this solution will obtain the attributes of each row field of the current business single view (such as mandatory, field type), and then verify whether the data imported by the user is valid, such as prompting: 'a in the yth row of table x must be entered! ', 'b in the yth row of table x is not a valid date (time, number)! ', etc. When all the verifications are passed, this solution will import the single data based on the user, simulate the scenario where the user enters the same data in the interface (implemented by calling the system's default_get and onchange), fill in the missing default values, and the cascade calculation logic between fields. This is also the uniqueness of this solution and its superiority over other export and import solutions, as well as the key to fully realizing complex business orders, user-configurable, simple, and accurate import.

[0039] Compared with the existing technology, this solution has the following technical features:

[0040] User-defined templates: Users can simply configure and generate customized Excel export and import templates through the system interface according to business needs to meet personalized needs.

[0041] Efficient reporting: The import and export functions completely simulate the business logic of entering business orders on the web interface to ensure data accuracy and reporting efficiency.

[0042] Flexibility and adaptability: When adjusting business logic, there is no need to redevelop or optimize the export and import logic. You can adapt to the changes by simply adjusting the custom template.

[0043] Cost reduction: Significantly reduce development costs, personalized development costs and functional maintenance costs.

[0044] Standardization: Through a unified configuration method, the uniformity of different functional logic specifications and development quality is ensured.

[0045] In terms of methods and configuration:

[0046] Template configuration: The system provides a template configuration interface where users can customize the structure, style, data mapping relationship, etc. of the Excel template and save it as a custom template.

[0047] Export operation: The system automatically generates an Excel file based on the template configured by the user and fills in the business data. The user can directly download the file.

[0048] Excel file content: contains not only business data, but also defines Excel formulas and valid optional value sets for corresponding fields, which is convenient for users to refer to and check.

[0049] Import operation: Users upload Excel files, and the system parses the file content according to the template configuration and imports the data into the business system. If non-compliant data is encountered, the system will issue an error prompt or process it.

[0050] Data processing: During import, the system simulates the user interface filling scenario and automatically processes default values, dynamically calculated fields, etc. to ensure accurate import results.

[0051] Template management: The system provides template management functions, supporting template viewing, editing, deletion and other operations.

[0052] The key point of this solution is the generation and application of user-defined templates. By providing a template configuration interface, users are allowed to customize the structure, style, data mapping relationship, etc. of the Excel template, which greatly improves the flexibility and adaptability of the system. At the same time, the import and export functions fully simulate the business logic of entering business orders on the web interface, ensuring the accuracy of data and reporting efficiency.

[0053] Compared with the prior art, this solution has the following beneficial effects:

[0054] 1) Reduce development and maintenance costs: Through user-defined templates, the need for independent development for different business units is reduced, thereby reducing development and maintenance costs.

[0055] 2) Improve reporting efficiency and accuracy: The import and export functions completely simulate the business logic of entering business orders on the web interface, ensuring data accuracy and reporting efficiency.

[0056] 3) Enhanced flexibility and adaptability: When adjusting business logic, you only need to adjust the custom template to adapt to the changes, without having to redevelop or optimize the export and import logic.

[0057] 4) Ensure standardization: Through a unified configuration method, the uniformity of different functional logic specifications and development quality is ensured.

[0058] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A system for exporting and importing customized business orders in Excel, characterized in that: Including template interface display configuration module, Excel template file configuration module, file download module and file upload module, The template interface display configuration module includes a Web interface based on Odoo, and also includes an original template submodule. The user selects an adapted basic Excel template file through the original template submodule, and specifies the import and export model, application model view, enabling conditions, file download button display logic, download file name, upload button display logic, and whether to enable import matching through other submodules. After the interface display configuration is completed, it is dynamically parsed into the interface of the business order through the download template submodule and the upload template submodule; The excel template file configuration module includes a template file description submodule, which explains and guides the configuration to support the import of data for multiple row tables defined in one excel; after configuring each header field, each line field, and the optional value set of each V-tab in the excel template file, the file is uploaded to the configuration template; The file download module uses openpyxl to read the excel file in the configuration template, parses each header field, each line field, and the optional value set for filling each V-tab, and at the same time, reads the value of each field of the business order and the attributes of each field in the view, and combines these attributes with the template excel file to control the optional value set of each line field in the V-tab to ensure consistency with the operation in the system; When the user uploads the updated download file, the file upload module reads the values ​​and formulas of each column in the parsed file through openpyxl and xlrd. When the verification is passed, based on the user importing single data, it simulates the scenario where the user enters the same data in the interface, and fills in the missing default values ​​and cascade calculation logic between fields.

2. A system for exporting and importing custom business orders in Excel as claimed in claim 1, characterized in that: The attributes of each field in the view include mandatory attributes, field attributes, optional domain attributes, and context attributes.

3. A system for exporting and importing custom business orders in Excel as claimed in claim 1, characterized in that: After the interface display configuration is completed, the download template and upload template buttons are dynamically parsed into the business order interface through the get_views method of the Odoo framework.

4. A system for exporting and importing custom business orders in Excel as claimed in claim 1, characterized in that: By calling the system's default_get and onchange, we can simulate the scenario where the user enters the same data on the interface.

5. A system for exporting and importing customized business orders in Excel as claimed in claim 1, characterized in that: The Excel template file configuration module provides a template file description tab to explain and guide the configuration to support the data import of multiple row tables defined in one Excel; the layout of each sheet tab in the template refers to the tab layout in Odoo. In the Excel template, the user can define the fields that need to be exported to the row table, as well as the matching formula for each field when it is subsequently imported into the system, and can also dynamically control whether a column is editable; at the same time, a V-row field tab is provided in the basic Excel template file, which corresponds to the L-row field and is used to control the corresponding relationship field in the L tab.

6. A system for exporting and importing customized business orders in Excel as claimed in claim 1, characterized in that: The file upload module also includes an import matching module. When importing, the system will check the label of the file. If it is found that it is not the latest downloaded, a corresponding prompt will be given, and the relationship field in the L tab will be converted into a field value readable by the system through the corresponding field column of the corresponding V tab, or the field in the L tab will be dynamically matched to a value readable by the system through the matching formula; when reading and parsing the file, the attributes of each row field of the current business order view are obtained, and then the data imported by the user is verified to be valid, and a corresponding prompt will be given.

7. A method for exporting and importing a custom business order in Excel, using the system for exporting and importing a custom business order in Excel as described in any one of claims 1 to 6, characterized in that: The following steps are involved: Step S1: template configuration, guiding the user to download a basic Excel template file applicable to the solution through the template interface display configuration module, and specifying the import and export model, application model view, enabling conditions, file download button display logic, download file name, upload button display logic, and whether to enable import matching; Step S2: Adjust the initial template according to the business order logic and upload the configuration back; Step S3: Download the EXCEL file on the business form and fill it out; Step S4: Upload the latest downloaded EXCEL file on the business order. The system will update the business order after data processing, and support adding, deleting and modifying the business order data.