Excel exporting method, electronic equipment and storage medium

Abstract

The invention provides an Excel exporting method, electronic equipment and a storage medium. The method comprises an automation mode and a user-defined mode. The method comprises the following steps: (1) in an automatic mode, configuring Excel export parameters, acquiring service data by utilizing a Java reflection mechanism, and further generating and exporting an Excel file by utilizing an Excel export framework; and (2) in the custom mode, customizing a method for acquiring the business data in the abstract class, inheriting and rewriting the method for acquiring the business data in the business implementation class, dynamically acquiring the business data, and generating and exporting an Excel file by utilizing an Excel export framework. Through the two modes, the reusability and development efficiency of codes can be improved. And if the subsequent requirements change, only the business data processing needs to be modified. If the Excel framework needs to be modified, only code logic related to Excel needs to be modified. Code expansibility is improved, and high cohesion and low coupling of codes are presented.

Description

Technical Field

[0001] The invention relates to a network communication industry, in particular to an Excel export method adopting an automatic or custom processing mode. Background Art

[0002] Usually, the methods for dealing with the need to export Excel tables with complex headers and multiple sheets (forms) in projects are as follows: (1) For different business scenarios, different Excel export methods need to be written in the Controller layer.

[0003] (2) For different business scenarios, the parameters passed in by the front end need to be judged and verified one by one.

[0004] (3) The business data processing method and the EXCEL export framework method are not separated. If the demand changes or the EXCEL framework needs to add new additional functions, each business layer needs to be processed, the workload increases rapidly, and the development cost is increased.

[0005] The disadvantages of the above processing methods are: 1) The input parameters of the front end need to be repeatedly verified. 2) Different Excel export methods need to be written in the control layer for different business scenarios. This results in a lot of duplicate code, which does not meet the basic object-oriented characteristics of making the code "easier to maintain, expand and increase the reuse rate". 3) The business data processing method and the Excel framework method are not separated, resulting in high coupling. If you need to add new functions to the Excel framework method, you need to rewrite a lot of different business classes. Summary of the invention

[0006] The purpose of the present invention is to provide an Excel export method, which separates the project-related business data processing and the Excel export framework without interfering with each other.

[0007] In a first aspect, an Excel export method is provided, the method including two modes: automatic and custom;

[0008] (1) In the automated mode, configure the parameters for EXCEL export, use the reflection mechanism of JAVA to obtain business data, and then use the Excel export framework to generate and export Excel files; (2) In custom mode, customize the method for obtaining business data in the abstract class, inherit and rewrite the method for obtaining business data in the business implementation class, dynamically obtain business data, and then use the Excel export framework to generate and export Excel files.

[0009] As a preferred solution of the present invention, the Excel export method in the automation mode includes:

[0010] (1.1) The front end transmits the parameters exported by EXCEL, including the EXCEL name, EXCEL header type, sheet name and display order, sheet query key fields, business keywords and business related information; (1.2) Based on business keywords, sheet query key fields and business-related information, JAVA's reflection mechanism is used to obtain business data; (1.3) According to the EXCEL header type, set the header object template for a single sheet or multiple sheets; (1.4) Pass the EXCEL name, sheet name, header object template and business data to the Excel export framework to generate and export the Excel file.

[0011] As a preferred embodiment of the present invention, step (1.3) comprises: When the EXCEL header type is single, it means that one or more sheets share a header object template; When the EXCEL header type is multiple, it means that multiple sheets use different header object templates.

[0012] As a preferred solution of the present invention, the IExcelExportAutoService interface is called in the control layer, and ExcelExportAutoServiceImpl is defined to implement the automatic method downloadAutoExcel() of the interface.

[0013] As a preferred solution of the present invention, the Excel export method in the custom mode includes: (2.1) The front end transmits the parameters exported by EXCEL, including the EXCEL name, sheet name and display order, sheet query key fields, title header, mapping relationship between title and field, business interface name and business parameters; (2.2) The backend obtains the business implementation class instance through the business interface name and calls the custom method provided by the abstract class to obtain business data; (2.3) Rewrite the method of obtaining business data in the business implementation class, and dynamically obtain business data based on the mapping relationship between title and field and the key fields of sheet query; (2.4) Pass the EXCEL name, sheet name and display order, title header and business data to the Excel export framework to generate and export the Excel file.

[0014] As a preferred solution of the present invention, the SpringApplicationContextUtil.getBean(servicecode) method is used in the control layer to obtain the instance object corresponding to the service, and then the downloadCustomeExcel() method in the AbstractDownloadCustomExcelService abstract class is called; In the downloadCustomeExcel() method, open a getdatas() abstract method. If the downloadCustomeExcel() method is inherited in the business layer, rewrite the getdatas() abstract method to obtain business data.

[0015] In a second aspect, a computer-readable storage medium storing one or more programs is also provided. The one or more programs include instructions, which, when executed by a computing device, cause the computing device to perform the method as described above.

[0016] According to a third aspect, an electronic device is also provided, comprising one or more processors, one or more memories and one or more programs, wherein the one or more programs are stored in the one or more memories and are configured to be executed by the one or more processors, and the one or more programs include instructions for executing the method described above.

[0017] The present invention implements an automated interface at the business logic layer, which can export single or multiple Excel worksheets and support the processing of complex headers. This design is based on the template design pattern, and handles the acquisition and processing logic of business data by opening an abstract method of an abstract class. This method allows developers to flexibly write implementation code according to different business needs, and complete the export operation through a unified Excel export framework. Whether it is processing simple data or complex business scenarios, this design can effectively meet the needs. The invention realizes the flexibility and scalability of the system through the design of a three-tier architecture, combined with abstract classes, enumeration classes and template design patterns. In particular, when dealing with business needs such as large-scale data interaction and complex Excel exports, the system can provide efficient and standardized code solutions to ensure the accuracy and ease of use of data processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] Figure 1 It is an implementation flow chart of the present invention.

[0019] Figure 2 It is the algorithm logic flow chart of the present invention. DETAILED DESCRIPTION

[0020] The backend of the present invention adopts Spring Boot technology, the front end adopts JavaScript technology and jQuery framework, and the overall structure is divided into three layers: data access layer, business logic layer and control layer.

[0021] Controller: Responsible for interacting with the user interface. The controller obtains data from the user's interface input and passes it to the business logic layer for processing. After processing, the controller returns the result to the front end for display. The controller can be understood as a communication intermediary that coordinates the communication between the user and the system.

[0022] Data Access Layer (DAO): Mainly responsible for interacting with the database, it is responsible for operations such as adding, deleting, modifying, and querying data to ensure that the system data can be correctly stored and accessed. The database can be regarded as a warehouse for storing data, and the data access layer is responsible for managing the storage and access operations of this data.

[0023] Business logic layer (Service): responsible for processing the core logic of the system according to actual business needs. If the business needs to interact with the database, the business logic layer will call the relevant methods of the data access layer to complete the data operation; for business logic that does not involve the database, the business logic layer directly processes the corresponding functions and feeds the results back to the control layer.

[0024] In programming, a class is a template or blueprint for defining a class of things, which contains the attributes and methods of the object. For example, you can define a "car" class, which includes the car's brand, color, speed and other characteristics. The class does not directly represent a specific object, but it provides rules for generating specific objects. The specific "object" is an instance created based on the class, and each instance can have different specific attributes.

[0025] An abstract class is a special form of a class that provides some basic functions while allowing subclasses to implement specific parts according to actual conditions. The design of an abstract class is a way to provide a common framework for different scenarios, which can reuse existing logic and provide a basis for flexible business expansion.

[0026] Enumeration classes are used to define some fixed constants to avoid using "magic numbers" or "magic strings" that are difficult to understand in the code. The so-called "magic numbers" refer to specific numbers or characters used directly in the code, but these values ​​have no clear meaning or explanation, resulting in poor readability and maintainability of the code. By using enumeration classes, these constants can be clearly expressed, enhancing the readability and security of the code.

[0027] The template design pattern is a common code design pattern that defines a basic process framework for business processing, but leaves the specific operation details to subclasses. This pattern allows code duplication to be reduced when processing similar business logic, and can flexibly implement different business logic according to different demand scenarios.

[0028] Java's reflection mechanism is the ability to inspect and manipulate classes, objects, methods, properties, etc. at runtime. Usually, the program determines the type and calls the method at compile time, while reflection allows you to dynamically obtain class information or call its methods and access its properties during program execution.

[0029] The Excel export method proposed in the present invention includes two modes: The first mode is to use an automated method to export to Excel. In the enumeration class, configure the header object class, business interface, business method, and database type (such as mysql or sqlite, etc.) to be exported to Excel, and use the reflection mechanism of JAVA to export to Excel. This method is suitable for application scenarios where the table fields in the database and the table headers to be displayed do not change much; The second mode is to adopt a custom data processing mode in order to have greater flexibility. The front end controls the display content and corresponding fields of the EXCEL header. The back end adopts a template method design mode, provides an abstract interface method for obtaining business data, processes complex business logic, and finally exports data through the EXCEL framework.

[0030] Through the above two modes, the code reusability and development efficiency can be improved. If the subsequent requirements change, only the business data processing needs to be modified. If the Excel framework needs to be modified, only the Excel-related code logic needs to be modified. Improve code scalability and present high cohesion and low coupling of the code.

[0031] The following is combined with Figure 2 , detailed description of the implementation mode of the present invention.

[0032] 1. Open a page in a project and click the Export button.

[0033] 2-1. If the automatic method (i.e. excelflag="auto") is used for export, the specific execution process is as follows:

[0034] (1) The corresponding parameters passed into the front desk are as follows: ①、excelname: indicates the EXCEL export name.

[0035] ②, headindicator: indicates the types of headers in the EXCEL file. "single" means a single sheet or multiple sheets share a header, and "multi" means multiple sheets use different headers.

[0036] ③、sheetstr: indicates the name of each sheet in the specified EXCEL table and the corresponding display order.

[0037] ④.sheetquerykey: represents the business-related query key field corresponding to the sheet in the EXCEL table, so as to obtain the corresponding business data for different sheets.

[0038] ⑤. servicecode: means obtaining the corresponding keyword of a specific business from the enumeration class, so as to obtain the relevant information contained in the keyword, such as the title object class (ie, template class), business interface, and business method.

[0039] ⑥、serviceparamsstr:indicates business-related information, including database source (mysql or sqlite or redis), business query conditions (there may be multiple business query parameters), pageno (indicates the current page displayed) and pagesize (how many items are displayed on the page) used for paging, and is passed to the backend in the form of json string.

[0040] (2) In the interface implementation class, according to the three parameters servicecode, sheetquerykey and serviceparamsstr passed in by the front end, the JAVA reflection mechanism is used to obtain the relevant data of the corresponding business. Call the IExcelExportAutoService interface from the Controller layer, define ExcelExportAutoServiceImpl to implement the automatic method downloadAutoExcel() of the interface; the detailed process is as follows: ①. Convert sheetstr to sheetList to get how many sheets an Excel needs.

[0041] ②. Call the keywords in the enumeration class to obtain the data information to be exported, including the Excel title object class (there may be multiple), the business layer interface class, and the method of obtaining business data. The keyword definition can be configured according to the time granularity of day / week / month. For example: / / 1. Daily granularity - city settlement flow matrix analysis - > city flow - > CTPMAP_CITY D_CtpmapCityFlow(Arrays.asList(CtpmapCityFlowDto.class), "ctpmapCityService", "getCtpmapCityDataList"), The parameters are explained as follows: "D_CtpmapCityFlow": indicates the business keyword, D indicates day, W indicates week, and M indicates month. That is, servicecode; “Arrays.asList(CtpmapCityFlowDto.class)”: Excel title object header (there may be multiple); "ctpmapCityService": represents the business layer interface class; “getCtpmapCityDataList”: indicates the method of obtaining business data; ③. Use fastjson2 to parse the serviceparamsstr business-related parameters to obtain the database source (mysql, sqlite, redis, etc.), business-related query conditions (there may be multiple business query parameters), pageno (showing which page is currently displayed), and pagesize (how many items are displayed on this page).

[0042] ④. According to whether pageno and pagesize exist, use Java's reflection mechanism to obtain paged data or non-paged data. Sheetquerykey is used to obtain different data information on multiple sheets in EXCEL.

[0043] ⑤. If data acquisition fails, an exception will be thrown and the returned data will be empty.

[0044] (3) Export the EXCEL files of different scenarios according to the headindicator parameter. If headindicator is "single", it means that a single sheet and multiple sheets share the same header object template. If headindicator is "multi", it means that multiple sheets display different header object templates.

[0045] (4) Finally, pass the EXCEL export name, sheet name, header object template, and business data into the EXCEL framework for export.

[0046] 2-2. If the custom data processing mode (i.e. excelflag="custom") is used for export, the specific execution process is as follows: (1) The corresponding parameters passed into the front desk are as follows: ①、excelname: indicates the EXCEL export name.

[0047] ②, sheetstr: indicates the name of each sheet in the specified EXCEL table and the corresponding display order.

[0048] ③.sheetquerykey: represents the business-related query key field corresponding to the sheet in the EXCEL table, so as to obtain the corresponding business data for different sheets.

[0049] ④、headstr: indicates the title header displayed in Excel (single-layer header, multi-layer header). The single header example is as follows: "Website name|Company name|Average traffic|Peak traffic|95 peak (all day)|Local outflow rate amplitude|..."; the multi-layer header example is as follows: "Key customer name, key customer name|Summary, inflow (Gbps)|Summary, outflow (Gbps)|Summary, net coverage traffic (Gbps)|Xuancheng City, inflow (Gbps)|Xuancheng City, outflow (Gbps)" ⑤. headmapsql: indicates that the name displayed in the title header corresponds to the mapping field in the database.

[0050] ⑥, servicecode: indicates the name of the interface corresponding to the calling business layer data.

[0051] ⑦、serviceparamsstr:represents business-related parameters (there may be multiple parameters for business queries), which are passed to the backend in the form of JSON string.

[0052] (2) In the Controller layer, use the SpringApplicationContextUtil.getBean(servicecode) method to obtain the instance object corresponding to the business. Then call the downloadCustomeExcel() method in the AbstractDownloadCustomExcelService abstract class.

[0053] (3) Open an abstract method getdatas() in the downloadCustomeExcel() method. If the downloadCustomeExcel() method is inherited in the business layer, the abstract method getdatas() must be rewritten to obtain specific business data. The specific data processing flow is as follows: ①. Get the column header data to be displayed in Excel according to headstr; ②. Get the sheet data to be displayed in Excel according to sheetstr; ③. Get the field column name of the database corresponding to the title in Excel according to headmapsql; ④. Use fastjson2 to parse the serviceparamsstr business-related parameters to obtain the database source (mysql, sqlite, redis, etc.), business-related query conditions, pageno (which page to display), and pagesize (how many items are displayed on this page).

[0054] ⑤. Pass the parsed information of serviceparamsstr and sheetquerykey to the specific implementation business method, which overrides the abstract method in the abstract class to obtain the corresponding business data. Sheetquerykey is used to obtain different data information on multiple sheets in EXCEL.

[0055] ⑥. If data acquisition fails, an exception will be thrown and the returned data will be empty.

[0056] (4) Finally, pass the EXCEL export name, sheet name, EXCEL header and business data into the EXCEL framework for export.

[0057] The present invention separates the business data processing related to the project from the Excel export framework. If the project requirements change, only the business data processing part needs to be modified, and the EXCEL export framework does not need to be changed. If the EXCEL export file needs to add new additional functions, only the EXCEL-related content needs to be modified, and there is no need to modify it everywhere. The layers are clear and do not interfere with each other. The workload of developers is greatly reduced and work efficiency is improved. In this way, the extensibility of the code can be improved, and the code is presented with high cohesion and low coupling. For example, the project needs to add a watermark to the EXCEL export file or set the title style and content style, etc.

[0058] In the automation mode of the present invention, an enumeration class can be used as a configuration file, and then the reflection mechanism characteristics of JAVA can be used to automatically obtain the business data corresponding to the requirements; a single sheet and multiple sheets can be supported to share a header object template; and multiple sheets can be supported to display different header object templates.

[0059] In the custom mode of the present invention, it provides greater flexibility and freedom. For example, the title header and the title header mapping field can be controlled by the front-end parameter transmission; in addition, the title header supports single-line header and multi-line header display.

[0060] Whether using the automated mode or the custom mode, the same Excel file supports the export of a single sheet or multiple sheets. To a certain extent, it can reduce development costs and workload and improve development efficiency. It is not limited to a certain project and is applicable to any project, with a very high degree of scalability.

[0061] By adopting the method of the present invention, when an abnormal error occurs in business data, the download of the EXCEL file will not be interrupted, and the EXCEL file will be downloaded normally, except that the EXCEL file with a title and empty content is displayed.

[0062] It should be noted that the above-mentioned sequence of the embodiments of the present application is only for description and does not represent the advantages and disadvantages of the embodiments. And the above-mentioned specific embodiments of the present application are described. Other embodiments are within the scope of the attached claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0063] Each embodiment in this application is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device, equipment and storage medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0064] Based on the same technical solution, the present invention also discloses a computer-readable storage medium storing one or more programs, wherein the one or more programs include instructions, and when the instructions are executed by a computing device, the computing device executes the above Excel export method.

[0065] Based on the same technical solution, the present invention also discloses a computing device, including one or more processors, one or more memories and one or more programs, wherein the one or more programs are stored in the one or more memories and are configured to be executed by the one or more processors, and the one or more programs include instructions for executing the above-mentioned Excel export method.

[0066] It will be appreciated by those skilled in the art that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Furthermore, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0067] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0068] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0069] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process in the computer or other programmable device. Figure 1 A process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0070] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application may include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0071] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the attached claims.

Claims

1. An Excel export method, characterized in that: The method includes two modes: automatic and customized; (1) In the automated mode, configure the parameters for EXCEL export, use the reflection mechanism of JAVA to obtain business data, and then use the Excel export framework to generate and export Excel files; (2) In custom mode, customize the method for obtaining business data in the abstract class, inherit and rewrite the method for obtaining business data in the business implementation class, dynamically obtain business data, and then use the Excel export framework to generate and export Excel files.

2. The method according to claim 1, characterized in that The Excel export methods in automation mode include: (2.1) The front end transmits the parameters exported by EXCEL, including the EXCEL name, EXCEL header type, sheet name and display order, sheet query key fields, business keywords and business related information; (2.2) Based on business keywords, sheet query key fields and business-related information, use Java's reflection mechanism to obtain business data; (2.3) According to the EXCEL header type, set the header object template for a single sheet or multiple sheets; (2.4) Pass the EXCEL name, sheet name, header object template and business data to the Excel export framework to generate and export the Excel file.

3. The method according to claim 2, characterized in that Step (2.3) includes: When the EXCEL header type is single, it means that one or more sheets share a header object template; When the EXCEL header type is multiple, it means that multiple sheets use different header object templates.

4. The method according to claim 2, characterized in that: Call the IExcelExportAutoService interface in the control layer and define ExcelExportAutoServiceImpl to implement the automatic method downloadAutoExcel() of the interface.

5. The method according to claim 1, characterized in that The Excel export methods in custom mode include: (5.1) The front end transmits the parameters exported by EXCEL, including the EXCEL name, sheet name and display order, sheet query key fields, title header, mapping relationship between title and field, business interface name and business parameters; (5.2) The backend obtains the business implementation class instance through the business interface name and calls the custom method provided by the abstract class to obtain business data; (5.3) Rewrite the method of obtaining business data in the business implementation class, and dynamically obtain business data based on the mapping relationship between title and field and the key fields of sheet query; (5.4) Pass the EXCEL name, sheet name and display order, title and business data to the Excel export framework to generate and export the Excel file.

6. The method according to claim 5, characterized in that Use SpringApplicationContextUtil.getBean(servicecode) method in the control layer to obtain the instance object corresponding to the business, and then call the downloadCustomeExcel() method in the AbstractDownloadCustomExcelService abstract class; In the downloadCustomeExcel() method, open a getdatas() abstract method. If the downloadCustomeExcel() method is inherited in the business layer, rewrite the getdatas() abstract method to obtain business data.

7. A computer-readable storage medium storing one or more programs, wherein the one or more programs include instructions, characterized in that: When the instructions are executed by a computing device, the computing device is caused to perform the method according to any one of claims 1 to 6.

8. An electronic device, characterized in that: The method comprises one or more processors, one or more memories and one or more programs, wherein the one or more programs are stored in the one or more memories and are configured to be executed by the one or more processors, and the one or more programs include instructions for executing the method as claimed in any one of claims 1 to 6.

Images