Excel-based data rapid centralized filling system

Abstract

The invention discloses an Excel-based data rapid centralized filling system. The system comprises a resource configuration module, a business rule module, an authority management module, a WEB end module, a back-end service module, an EXCEL plug-in module and a security and performance optimization module. According to the method, cost is reduced, efficiency is improved, and the filling template can be manufactured through simple training; the management efficiency is improved, and data analysis is quickly realized after the data is filled into the database by quickly using Excel; the data processing capability is enhanced, step-by-step customization processing of the data is realized through service rules, and the method is easy to master; excel filling realizes verification of whether key index data is legal and has write-in and query permissions during data storage through configuration of table dimensions; the data security is guaranteed, authority control is carried out in the aspects of tables, fields, dimensions and the like, and unauthorized use of the data is prevented; a user can select a familiar database or a database with specific requirements according to needs.

Description

Technical Field

[0001] The invention relates to the field of electronic forms, and in particular to an Excel-based data rapid centralized reporting system. Background Art

[0002] There are a lot of data reporting requirements in enterprises. After the data is collected, it usually needs to be summarized and analyzed. However, the needs in different fields vary greatly. Usually, enterprises will adopt customized development solutions. Some simple requirements are collected using online documents.

[0003] However, these methods may face practical problems such as low efficiency, high cost, inconvenient operation, and lack of guarantee of data validity.

[0004] The plug-in function developed based on Excel can not only meet the operating methods of personnel filling out forms who are accustomed to using spreadsheets, but also enable centralized storage of data in a database, making it easier for data aggregation and analysis. It is low-cost but very efficient. Summary of the invention

[0005] The purpose of the present invention is to provide a rapid centralized data reporting system based on Excel in response to the above technical problems.

[0006] An Excel-based data rapid centralized reporting system, including a resource configuration module, a business rule module, a permission management module, a WEB terminal module, a back-end service module, an EXCEL plug-in module, and a security and performance optimization module;

[0007] The data source configuration module provides an administrator interface, allowing configuration of multiple data sources;

[0008] The business rule module uses the SQL statement of the specified database written by the user to enable Excel to process the data after receiving the data;

[0009] The permission setting module provides an administrator interface to set role access permissions;

[0010] The WEB module is developed based on the H5 page style;

[0011] The backend service module is based on Java, free from the general access function based on the database of the data model, and supports a variety of database products;

[0012] The EXCEL plug-in module provides visual area definition, global parameter definition, and column definition based on the Excel management panel, and stores the management data in the template file;

[0013] The security and performance optimization module permissions control each environment.

[0014] Furthermore, in a data rapid centralized reporting system based on Excel, the role access rights include the role's access rights to tables, fields, and dimension values.

[0015] Furthermore, a data rapid centralized reporting system based on Excel, wherein each environment includes template definition, data refresh, data saving, parameter definition and operation.

[0016] Furthermore, a data rapid centralized reporting system based on Excel, wherein the business rule module processes the data including processing, calculation and forming analysis tables.

[0017] Furthermore, a data rapid centralized reporting system based on Excel, the area definition includes defining the corresponding relationship between the data range to be operated and the database, the query conditions when reading data, whether the column is updateable, and the sorting settings.

[0018] Furthermore, in a data rapid centralized reporting system based on Excel, the global parameter definition includes defining the parameter name, type, value, and description.

[0019] Furthermore, in a data rapid centralized reporting system based on Excel, the global parameters are defined in the data area to associate the global parameters with the parameters of the data area in the form of variables;

[0020] The variable location in the data area includes variable description, variable name, and value.

[0021] The beneficial effects of the present invention are as follows: through a rapid centralized data reporting system based on Excel, costs are reduced and efficiency is increased, and reporting templates can be made after simple training; management efficiency is improved, and data analysis can be quickly realized after quickly using Excel to report data to the database; data processing capabilities are enhanced, and step-by-step customized processing of data is realized through business rules, which is easy to use; Excel reporting realizes the verification of whether key indicator data is legal and whether it has write and query permissions when saving data through the configuration of table dimensions; data security is guaranteed, and strict permission control is carried out in terms of tables, fields, dimensions, etc. to prevent unauthorized use of data; users can choose familiar or specific databases according to their needs. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] Figure 1 It is a structural diagram of a data rapid centralized reporting system based on Excel. DETAILED DESCRIPTION

[0023] In order to have a clearer understanding of the technical features, purposes and effects of the present invention, specific embodiments of the present invention are now described with reference to the accompanying drawings.

[0024] As attached Figure 1 As shown, a data rapid centralized reporting system based on Excel includes a resource configuration module, a business rule module, a permission management module, a WEB end module, a back-end service module, an EXCEL plug-in module, and a security and performance optimization module;

[0025] The data source configuration module provides an administrator interface, allowing configuration of multiple data sources;

[0026] The business rule module uses the SQL statement of the specified database written by the user to enable Excel to process the data after receiving the data;

[0027] The permission setting module provides an administrator interface to set role access permissions;

[0028] The WEB module is developed based on the H5 page style;

[0029] The backend service module is based on Java, free from the general access function based on the database of the data model, and supports a variety of database products;

[0030] The EXCEL plug-in module provides visual area definition, global parameter definition, and column definition based on the Excel management panel, and stores the management data in the template file;

[0031] The security and performance optimization module permissions control each environment.

[0032] Furthermore, in a data rapid centralized reporting system based on Excel, the role access rights include the role's access rights to tables, fields, and dimension values.

[0033] Furthermore, a data rapid centralized reporting system based on Excel, wherein each environment includes template definition, data refresh, data saving, parameter definition and operation.

[0034] Furthermore, a data rapid centralized reporting system based on Excel, wherein the business rule module processes the data including processing, calculation and forming analysis tables.

[0035] Furthermore, a data rapid centralized reporting system based on Excel, the area definition includes defining the corresponding relationship between the data range to be operated and the database, the query conditions when reading data, whether the column is updateable, and the sorting settings.

[0036] Furthermore, in a data rapid centralized reporting system based on Excel, the global parameter definition includes defining the parameter name, type, value, and description.

[0037] Furthermore, in a data rapid centralized reporting system based on Excel, the global parameters are defined in the data area to associate the global parameters with the parameters of the data area in the form of variables;

[0038] The variable location in the data area includes variable description, variable name, and value.

[0039] Specific embodiment 1 System function

[0040] 1. Allow users to customize the data acquisition area in any format. By defining the start row and end row, it can support the scenario where there are multiple data areas in a table;

[0041] 2. Allow users to specify multiple keywords to determine whether data exists when saving again. The product background database service implements a general and batch data processing method to quickly update existing data and insert new data;

[0042] 3. Allow users to define which columns to update when saving again, which can meet the requirement of only modifying certain columns in some scenarios and prevent columns that cannot be updated from being updated by mistake, and all this is unaware to users;

[0043] 4. The system provides flexible parameter definition, supports the generation of auxiliary data that is not filled in the data area, supports dynamic expressions, and can realize the automatic generation of parameters that do not need to be filled in by users, such as data area row number and system time;

[0044] 5. Support one-click refresh to display the completed data in Excel. A lot of technical optimizations have been made to Excel operations to achieve very fast data extraction;

[0045] 6. Supports the definition and selection of global parameters such as year and month that are effective for multiple Sheet pages in the entire template. Global parameters can be controlled by authorization, so that multiple Sheet pages in a workbook can reduce the definition of common parameters;

[0046] 7. Supports users to customize the sorting rules of refreshed data. Users can define the sorting conditions when returning data and use the performance of the database to quickly sort data.

[0047] 8. The service can support a variety of database products, such as SQLServer, Oracle, PostgreSQL, MySQL and the domestic DAMO database. The data access layer implements the dialects of various databases. There is no need to define data models and data entities. It can dynamically generate add, delete, modify and query statements based on the database structure. Users only need to create a data table to fill in the data through the plug-in function.

[0048] 9. From the perspective of online reporting, the product is significantly superior to online forms. First of all, the data is stored in the database for easy aggregation, and reading and writing are subject to strict permission control to prevent unauthorized use.

[0049] Specific embodiment 2 System function solves the problem

[0050] 1) Maximize the user's operating experience, achieve quick start, solve the performance bottleneck of Excel operations, and prevent performance problems caused by multiple Com operations.

[0051] 2) Flexible customization capabilities to adapt to as many application scenarios as possible. Through the definition of data areas, a complete set of data table customization solutions can be implemented, which can adapt to the flexible definition of multiple tables on one page, multiple rows in one table, and different rows and columns in different tables. It is also necessary to support auxiliary columns formed by users' non-filling, so that users can record additional information after filling in the report without noticing.

[0052] 3) To ensure data security and validity, it is necessary to define permissions for database tables operated by users and verify key indicators before saving to prevent the occurrence of illegal data.

[0053] 4) To solve batch data operations based on the Excel model, it is necessary to be able to accurately define the keywords of each type of data, quickly verify the legitimacy of the data, and be able to implement high-performance storage operations for large amounts of data.

[0054] Specific embodiment 3 System and user

[0055] 1. Data range definition based on Excel

[0056] Implementation method: Develop an Excel plug-in panel, define the range of the data area on the visual interface, and set parameters and conditions;

[0057] User experience: Since a visual interface is provided, definition is relatively easy, and users mainly focus on how to define query conditions, update conditions, and parameters.

[0058] 2. Global parameter definition

[0059] Implementation method: Various types of input conditions can be configured through a visual parameter definition interface, and different conditions can be met when collecting data with templates;

[0060] User experience: Since the Web side has been configured with corresponding dimensions and dimension values, and has authorization functions, users usually use the drop-down box method when using it, which is very convenient to operate.

[0061] 3. Backend data access capabilities

[0062] Implementation method: Based on the general data engine of Jianxi, the batch saving function of various databases is realized, and the performance has been verified;

[0063] User experience: Users only need to focus on data during the reporting process. They can paste data from other sheets or fill it out manually. After completion, they only need to click the Save button to save data in batches with one click.

[0064] Specific embodiment 4 background general storage interface core code

[0065] Each database has different syntax for batch saving. Take DAMO as an example, as shown in the following code:

[0066]

[0067]

[0068]

[0069]

[0070] This solution reduces costs and increases efficiency through a rapid centralized data reporting system based on Excel. Reporting templates can be created after simple training. Management efficiency is improved by quickly reporting data to the database using Excel, and data analysis can be quickly realized. Data processing capabilities are enhanced, and step-by-step customized processing of data is achieved through business rules, which is easy to use. Excel reporting can verify whether key indicator data is legal and whether it has write and query permissions when saving data through the configuration of table dimensions. Data security is guaranteed, and strict permission control is implemented in terms of tables, fields, dimensions, etc. to prevent unauthorized use of data. Users can choose a familiar or specific database according to their needs.

[0071] The above shows and describes the basic principles and main features of the present invention and the advantages of the present invention. It should be understood by those skilled in the art that the present invention is not limited to the above embodiments. The above embodiments and descriptions are only for explaining the principles of the present invention. Without departing from the spirit and scope of the present invention, the present invention may have various changes and improvements, which fall within the scope of the present invention to be protected. The scope of protection of the present invention is defined by the attached claims and their equivalents.

Claims

1. A data rapid centralized reporting system based on Excel, characterized in that: Including resource configuration module, business rule module, authority management module, WEB terminal module, back-end service module, EXCEL plug-in module, security and performance optimization module; The data source configuration module provides an administrator interface, allowing configuration of multiple data sources; The business rule module uses the SQL statement of the specified database written by the user to enable Excel to process the data after receiving the data; The permission setting module provides an administrator interface to set role access permissions; The WEB module is developed based on the H5 page style; The backend service module is based on Java, free from the general access function based on the database of the data model, and supports a variety of database products; The EXCEL plug-in module provides visual area definition, global parameter definition, and column definition based on the Excel management panel, and stores the management data in the template file; The security and performance optimization module permissions control each environment.

2. According to the Excel-based data rapid centralized reporting system of claim 1, it is characterized in that: The role access rights include the role's access rights to tables, fields, and dimension values.

3. The Excel-based data rapid centralized reporting system according to claim 1 is characterized in that: Each environment includes defining templates, refreshing data, saving data, parameter definition and running.

4. The Excel-based data rapid centralized reporting system according to claim 1 is characterized in that: The business rule module processes data including processing, calculation and forming analysis tables.

5. The Excel-based data rapid centralized reporting system according to claim 1 is characterized in that: The area definition includes defining the corresponding relationship between the data range to be operated and the database, the query conditions when reading data, whether the column is updateable, and the sorting settings.

6. The Excel-based data rapid centralized reporting system according to claim 1 is characterized in that: The global parameter definition includes defining the parameter name, type, value, and description.

7. The Excel-based data rapid centralized reporting system according to claim 6 is characterized in that: The global parameter definition in the data area associates the global parameter with the parameter in the data area in the form of a variable; The variable location in the data area includes variable description, variable name, and value.

Images