Method and device for automatically generating Excel table, computer equipment and medium

Abstract

The invention relates to a method and device for automatically generating an Excel table, computer equipment and a medium, and the method comprises the steps: building an object array based on an Excel table building demand; the object array is traversed, the attribute of each object in the object array is analyzed, a header structure is constructed, and the header structure comprises a single-column parent header and a plurality of columns of child headers; creating a new Excel workbook and a new Excel worksheet through a preset Excel operation library; dynamically generating a header part of the Excel table according to the constructed header structure under the Excel workbook and the worksheet; and filling the data in the object array into the corresponding position of the Excel table according to the header structure corresponding to the header part to generate the Excel table. In the whole process, the information is automatically extracted from the object array by traversing the object array, and a single-column parent header and a plurality of columns of child headers are dynamically generated according to the information, so that data structures with different hierarchies can be automatically adapted, and efficient automatic generation of the Excel table is realized.

Description

Technical Field

[0001] This application relates to the technical field of data processing, and particularly to a method, apparatus, computer device, storage medium, and computer program product for automatically generating Excel tables. Background Art

[0002] In the field of data processing, Excel, as a powerful and flexible tool, is widely used in the creation, management, and analysis of data. Its intuitive user interface and rich function set enable users to efficiently process various types of data.

[0003] However, with the increase in data complexity, especially when dealing with data having a complex hierarchical structure, the traditional method of manually setting the table header gradually reveals its limitations. The traditional way of creating an Excel table relies on manual input and setting of the table header by the user, which is acceptable when dealing with simple or moderately complex data. But when the data has a complex hierarchical structure, such as multi-level classification, sub-classification, or hierarchical nesting, manually setting the table header not only becomes extremely cumbersome but also is very error-prone. In addition, when the data hierarchy changes, the user needs to manually adjust the entire table header structure, which further increases the workload and the possibility of errors.

[0004] It can be seen that there is an urgent need for an efficient solution for automatically generating Excel tables. Summary of the Invention

[0005] Based on this, in view of the above technical problems, it is necessary to provide a method, apparatus, computer device, computer-readable storage medium, and computer program product for efficiently generating Excel tables automatically.

[0006] In a first aspect, this application provides a method for automatically generating an Excel table. The method includes:

[0007] Based on the Excel table creation requirements, construct an object array;

[0008] Traverse the object array and parse the attributes of each object in the object array to construct a table header structure, where the table header structure includes a single-column parent table header and multi-column child table headers;

[0009] Create a new Excel workbook and worksheet through a preset Excel operation library;

[0010] Dynamically generate the table header part of the Excel table under the Excel workbook and worksheet according to the constructed table header structure;

[0011] Fill the data in the object array into the corresponding positions of the Excel table according to the table header structure corresponding to the table header part to generate an Excel table.

[0012] In one embodiment, creating requirements based on an Excel table, and constructing an object array includes:

[0013] Obtain the creation requirements of the Excel table;

[0014] Define data classes based on the creation requirements, where each data class contains attributes describing the characteristics of the corresponding level;

[0015] Collect the defined data classes and construct an object array.

[0016] In one embodiment, defining data classes based on the creation requirements, where each data class contains attributes describing the characteristics of the corresponding level includes:

[0017] Create a parent category named ParentCategory and a subcategory named Education;

[0018] According to the creation requirements, define a private attribute educationList in the ParentCategory class;

[0019] Add an @Excel annotation to the private attribute educationList, and define the required attributes describing the corresponding name parameter of the @Excel annotation in the Education class to create the ParentCategory class and the Education class.

[0020] In one embodiment, traversing the object array and parsing the attributes of each object in the object array, and constructing a header structure includes:

[0021] Traverse the outermost object array in the object array to identify all unique parent category names;

[0022] Parse the attributes of each object in the object array to identify the sub-item list under each parent category;

[0023] Generate a parent header according to the parent category name and generate a child header according to the sub-item list under each parent category;

[0024] Combine the parent header and the child header to construct a header structure.

[0025] In one embodiment, generating a parent header according to the parent category name includes:

[0026] Place the parent category names in sequence in the first row of Excel as the main title bar of Excel to generate a parent header.

[0027] In one embodiment, generating a child-level table header according to the list of child items under each parent category includes:

[0028] Traverse the list of child items under each parent category;

[0029] According to the attribute structure of each child item in the list of child items, add corresponding sub-header fields under the parent category name adjacent to the list of child items to generate a child-level table header.

[0030] In one embodiment, after filling the data in the object array into the corresponding positions of the Excel table according to the table header structure corresponding to the table header part and generating the Excel table, it includes:

[0031] Store the generated Excel table in a preset storage path.

[0032] In a second aspect, the present application also provides a device for automatically generating an Excel table. The device includes:

[0033] An array construction module for constructing an object array based on the requirements for creating an Excel table;

[0034] A traversal module for traversing the object array and parsing the attributes of each object in the object array to construct a table header structure, where the table header structure includes a single-column parent table header and a multi-column child table header;

[0035] A new creation module for creating a new Excel workbook and worksheet through a preset Excel operation library;

[0036] A table header generation module for dynamically generating the table header part of the Excel table according to the constructed table header structure under the Excel workbook and worksheet;

[0037] A table generation module for filling the data in the object array into the corresponding positions of the Excel table according to the table header structure corresponding to the table header part to generate an Excel table.

[0038] In a third aspect, the present application also provides a computer device. The computer device includes a memory and a processor. The memory stores a computer program, and when the processor executes the computer program, the following steps are implemented:

[0039] Construct an object array based on the requirements for creating an Excel table;

[0040] Traverse the object array and parse the attributes of each object in the object array to construct a table header structure, where the table header structure includes a single-column parent table header and a multi-column child table header;

[0041] Create a new Excel workbook and worksheet through a preset Excel operation library;

[0042] Under the Excel workbook and worksheet, dynamically generate the header part of the Excel table according to the constructed header structure;

[0043] Fill the data in the object array into the corresponding positions of the Excel table according to the header structure corresponding to the header part to generate an Excel table.

[0044] Fourthly, the present application also provides a computer-readable storage medium. On the computer-readable storage medium, a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

[0045] Construct an object array based on the Excel table creation requirements;

[0046] Traverse the object array and parse the attributes of each object in the object array to construct a header structure, where the header structure includes a single-column parent header and multi-column child headers;

[0047] Create a new Excel workbook and worksheet through a preset Excel operation library;

[0048] Under the Excel workbook and worksheet, dynamically generate the header part of the Excel table according to the constructed header structure;

[0049] Fill the data in the object array into the corresponding positions of the Excel table according to the header structure corresponding to the header part to generate an Excel table.

[0050] Fifthly, the present application also provides a computer program product. The computer program product includes a computer program, and when the computer program is executed by a processor, the following steps are implemented:

[0051] Construct an object array based on the Excel table creation requirements;

[0052] Traverse the object array and parse the attributes of each object in the object array to construct a header structure, where the header structure includes a single-column parent header and multi-column child headers;

[0053] Create a new Excel workbook and worksheet through a preset Excel operation library;

[0054] Under the Excel workbook and worksheet, dynamically generate the header part of the Excel table according to the constructed header structure;

[0055] Fill the data in the object array into the corresponding positions of the Excel table according to the table header structure corresponding to the table header part, and generate an Excel table.

[0056] The above method, device, computer device, storage medium and computer program product for automatically generating an Excel table construct an object array based on the Excel table creation requirement; traverse the object array and parse the attributes of each object in the object array to construct a table header structure, where the table header structure includes a single-column parent table header and multi-column child table headers; create a new Excel workbook and worksheet through a preset Excel operation library; dynamically generate the table header part of the Excel table under the Excel workbook and worksheet according to the constructed table header structure; fill the data in the object array into the corresponding positions of the Excel table according to the table header structure corresponding to the table header part, and generate an Excel table. In the whole process, by traversing the object array, information is automatically extracted from the object array, and accordingly, an Excel table with a single-column parent table header and multi-column child table headers is dynamically generated, which can automatically adapt to different hierarchical data structures, thereby simplifying the Excel table creation process and realizing efficient automatic generation of Excel tables. Description of the Drawings

[0057] Figure 1 It is an application environment diagram of the method for automatically generating an Excel table in an embodiment;

[0058] Figure 2 It is a schematic flowchart of the method for automatically generating an Excel table in an embodiment;

[0059] Figure 3 It is a schematic flowchart of the method for automatically generating an Excel table in another embodiment;

[0060] Figure 4 It is a schematic sub-flowchart of S200 in an embodiment;

[0061] Figure 5 It is a structural block diagram of the device for automatically generating an Excel table in an embodiment;

[0062] Figure 6 It is an internal structure diagram of a computer device in an embodiment. Detailed Description of the Embodiments

[0063] In order to make the objectives, technical solutions and advantages of the present application clearer, the present application will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0064] The method for automatically generating an Excel table provided by the embodiments of the present application can be applied to, for exampleFigure 1 In the application environment shown. Among them, the terminal 102 communicates with the server 104 through the network. The data storage system can store the data that the server 104 needs to process. The data storage system can be integrated on the server 104, or placed on the cloud or other network servers. The terminal 102 sends an Excel table automatic generation request to the server 104. The server 104 responds to the request. The server 104 extracts the Excel table creation requirements from the request, and based on the Excel table creation requirements, constructs an object array; traverses the object array and parses the attributes of each object in the object array to construct a header structure, where the header structure includes a single-column parent header and multi-column child headers; creates a new Excel workbook and worksheet through a preset Excel operation library; dynamically generates the header part of the Excel table under the Excel workbook and worksheet according to the constructed header structure; fills the data in the object array into the corresponding positions of the Excel table according to the header structure corresponding to the header part to generate the Excel table. Further, the server 104 can also feedback the generated Excel table to the terminal 102. Among them, the terminal 102 can be but is not limited to various personal computers, laptop computers, smart phones, tablet computers, Internet of Things devices, and portable wearable devices. The Internet of Things devices can be smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, etc. The portable wearable devices can be smart watches, smart bracelets, head-mounted devices, etc. The server 104 can be implemented by an independent server or a server cluster composed of multiple servers.

[0065] In one embodiment, as Figure 2 shown, a method for automatically generating an Excel table is provided. Taking the server 104 in Figure 1 as an example for illustration, it includes the following steps:

[0066] S100: Based on the Excel table creation requirements, construct an object array.

[0067] The Excel table creation requirements are used to determine which data columns (fields) the table needs to include, and whether there is a hierarchical relationship between these fields (for example, some fields may be the children of other fields). Based on these requirements, an object array is constructed, where each object represents a row of data in the table, and the attributes of the object correspond to the respective data columns in the table. Here, according to the Excel table creation requirements, an object array is created. Each object in the array contains attributes corresponding to the table columns. For example, if the table needs to include employee name, department, and position, then each object will contain name, department, and position attributes.

[0068] Specifically, the Excel spreadsheet creation requirements can be directly proposed by the user. The user uploads the Excel spreadsheet creation requirements they need to the server through the terminal. Additionally, it can also be that the user uploads the scenario requirements to the server according to the application scenario, and the server obtains the corresponding Excel spreadsheet creation requirements for the scenario requirements from the pre-cached database.

[0069] S200: Traverse the object array and parse the attributes of each object in the object array to construct a header structure, which includes a single-column parent header and multi-column child headers.

[0070] Traverse the object array and analyze the attributes of each object to determine the structure of the header. If there is a hierarchical relationship between the attributes (e.g., department and position), a multi-column child header will be constructed. Traverse the object array, check the attributes of each object to determine which attributes can be used as headers and their hierarchical relationships. Based on the results of the attribute analysis, construct a header structure. This structure is usually a nested data structure used to represent the hierarchical relationship of the header. For example, if "department" is the parent header and "position" is the child header, this hierarchical relationship needs to be reflected in the header structure.

[0071] S300: Create a new Excel workbook and worksheet through a preset Excel operation library.

[0072] Use a preset Excel operation library (such as Apache POI, NPOI, etc.) to create a new Excel workbook and worksheet. This workbook and worksheet will serve as the basis for generating the Excel spreadsheet. Specifically, select a suitable Excel operation library here. This library should provide functions for creating, editing, and saving Excel files. Use the selected Excel operation library to create a new workbook and create a new worksheet in it. This worksheet will be used to store the Excel spreadsheet we generate.

[0073] S400: Dynamically generate the header part of the Excel spreadsheet under the Excel workbook and worksheet according to the constructed header structure.

[0074] According to the previously constructed header structure, dynamically generate the header part of the Excel spreadsheet in the worksheet. Traverse the header structure and generate the corresponding cells and merged cells (if necessary) in the worksheet according to the hierarchical relationship and attribute information of each header. For example, if "department" is the parent header and "position" is the child header, corresponding cells need to be generated for them in the worksheet, and the cells need to be merged as needed to represent the hierarchical relationship.

[0075] S500: Fill the data in the object array into the corresponding positions of the Excel table according to the table header structure corresponding to the table header part, and generate an Excel table.

[0076] Traverse the object array, and fill the data of each object into the corresponding positions of the worksheet according to the table header structure, so as to generate a complete Excel table. Specifically, traverse the object array. For each object in the array, according to its attribute values and the table header structure, fill the corresponding data into the corresponding cells of the worksheet. For example, if the object contains name, department, and position attributes, then fill the values of these attributes into the corresponding "Name", "Department", and "Position" columns in the worksheet. After the data filling is completed, a complete Excel table is obtained. This table can be saved, shared, or further processed as needed.

[0077] The above method for automatically generating an Excel table is based on the Excel table creation requirements, constructs an object array; traverses the object array and parses the attributes of each object in the object array to construct a table header structure, where the table header structure includes a single-column parent table header and multi-column child table headers; creates a new Excel workbook and worksheet through a preset Excel operation library; dynamically generates the table header part of the Excel table under the Excel workbook and worksheet according to the constructed table header structure; fills the data in the object array into the corresponding positions of the Excel table according to the table header structure corresponding to the table header part, and generates an Excel table. Throughout the process, by traversing the object array, information is automatically extracted from the object array, and accordingly, a table header with a single-column parent table header and multi-column child table headers is dynamically generated, which can automatically adapt to different hierarchical data structures, thus simplifying the Excel table creation process and realizing efficient automatic generation of Excel tables.

[0078] In one embodiment, as Figure 3 shown, S100 includes:

[0079] S120: Obtain the creation requirements of the Excel table.

[0080] First, obtain the detailed creation requirements of the Excel table from the requester. These requirements may include, but are not limited to: which data columns (i.e., fields) the table needs to contain, what the data types of these fields are, whether there are hierarchical relationships between fields (for example, some fields may be children of other fields), and any special requirements regarding data formats or styles.

[0081] S140: Define data classes based on the creation requirements, where each data class contains attributes describing the characteristics of the corresponding level.

[0082] After clarifying the requirements for creating an Excel table, corresponding data classes need to be defined based on these requirements. Specifically, one or more data classes (e.g., ParentCategory and ChildItem) can be defined according to the actual needs, and each data class contains attributes that describe the characteristics of that level. Each data class represents a level or a group of related fields in the table. For example, if the table needs to contain employee information, and the employee information includes fields such as name, department, and position, then we can define an employee data class that contains attributes such as name, department, and position. In addition, if there is a hierarchical relationship between fields (such as department and position), we also need to reflect this relationship in the data class. This can be achieved by adding additional attributes in the data class or using nested classes.

[0083] S160: Aggregate the defined data classes and construct an object array.

[0084] After defining all the necessary data classes, instances of these classes need to be created according to the actual needs and aggregated into an array. This array is the above-mentioned object array. Each element in the array is an instance of a data class, representing a row of data in the table. For example, if there is an employee data class and an Excel table needs to be generated for multiple employees, then multiple instances of the employee data class can be created and added to the object array. Here, constructing the object array is one of the key steps in generating an Excel table. Through the object array, data can be conveniently organized into the format required by the table, and it provides convenience for subsequent header construction and data filling.

[0085] In one of the embodiments, data classes are defined based on the creation requirements, and each data class contains attributes that describe the characteristics of the corresponding level, including:

[0086] Step 1: Create a parent category named ParentCategory and a subcategory named Education.

[0087] In this embodiment, a data model that can represent a certain hierarchical structure is created. In this example, assume there is a parent category (such as ParentCategory), which can contain multiple subcategories or items (here, taking Education as an example).

[0088] The "Education" class represents a specific subcategory in this example, such as educational experience. In practical applications, this subcategory can be any type of data, such as work experience, skills, certificates, etc. In the "Education" class, define the attributes that describe the characteristics of this category. These attributes should correspond to the data to be displayed in the Excel table. For example, for educational experience, there may be attributes such as schoolName (school name), degree (degree), startDate (enrollment date), and endDate (graduation date), etc.

[0089] Step 2: According to the creation requirements, define a private attribute educationList in the ParentCategory class.

[0090] The ParentCategory class will serve as a container that contains multiple subcategories or project instances. In this class, define a private attribute educationList to store the list of subcategories or projects. This list can contain any type of subcategory or project instance as long as they meet the data model design requirements.

[0091] Step 3: Add the @Excel annotation to the private attribute educationList and define the required attributes in the Education class that describe the corresponding name parameter of the @Excel annotation to create the ParentCategory class and the Education class.

[0092] The @Excel annotation is used to specify how to map the attributes of a Java object to the columns in an Excel table. This usually involves parameters of the annotation, such as name (specifying the column name in the Excel table) and other possible parameters (such as specifying the data type, format, etc.). For each attribute in the subcategory class, the @Excel annotation needs to be added and the name parameter is specified as the corresponding column name in the Excel table. In this way, when exporting the data of the subcategory to the Excel table, the value of each attribute will be correctly mapped to the corresponding column.

[0093] In practical applications, ParentCategcry uses the annotation @Excel(name = "Educational Experience", repeat = 3) private List <education>The "educationList" represents the parent title, and the "repeat" attribute in this annotation indicates the number of repetitions, that is, 3 Education record objects are retrieved from this "ParentCategcry" as the child table headers and filling data. As mentioned above, 3 "ListEducation>" records are retrieved as an object array. Of course, there are other similar ones, such as multiple qualification certificate records, multiple emergency contact records, multiple work resume records, etc. as object arrays. In actual applications, during the process of retrieving data from "ParentCategcry", the number of data items currently contained in "ParentCategcry" can be further identified. If the number of data items contained is less than 3 (for example, only 2 items), at this time, the first 2 cells are filled according to the actual situation, and the remaining 1 item is replaced with an empty cell; if the number of data items contained is greater than 3 (for example, there are 4 or 5 items), then first sort in reverse order according to a certain field (such as graduation time), and only the first 3 items of data are extracted, and the latter 2 items of data will be discarded; if it just contains 3 items of data, then fill according to the actual situation, without adding empty cells and without discarding data.

[0094] In one embodiment, as Figure 4 shown, S200 includes:

[0095] S220: Traverse the outermost object array in the object array to identify all unique parent category names.

[0096] Traverse the outermost layer of the object array. Here, the object array can be understood as a collection containing multiple data objects, and each data object represents a row in the Excel table. During the traversal process, mainly focus on the attributes of the objects, especially those attributes that can be used as the parent of the table header, such as "department", "product type", etc. The goal here is to identify all unique parent category names, which will form the parent part of the table header.

[0097] S240: Parse the attributes of each object in the object array to identify the sub-item list under each parent category.

[0098] After determining the parent category name, further parse each object in the object array to identify the possible sub-items under each parent category. These sub-items can be specific attribute values or more detailed categories. For example, under the parent category of "department", there may be sub-items such as "Sales Department", "Marketing Department", etc.; and under "product type", there may be sub-items such as "electronic products", "clothing", etc.

[0099] S260: Generate the parent table header according to the parent category name and generate the child table header according to the sub-item list under each parent category.

[0100] After identifying the parent category name and the list of sub-items, generate the table header based on this information. The parent category name is directly used as the parent table header, while the list of sub-items under each parent category is used to generate the corresponding child table headers. If the list of sub-items is long or needs to be grouped for display, the structure of the child table headers can be further refined.

[0101] S280: Combine the parent table header and the child table headers to construct the table header structure.

[0102] Combine the generated parent table header and child table headers to form a complete table header structure. This structure is usually a nested data structure used to represent the hierarchical relationship and column layout of the table header. For example, an array containing the parent table header and a list of child table headers can be used to represent the table header structure.

[0103] In this embodiment, by traversing the object array, identifying the parent category name and the list of sub-items, generating the parent and child table headers, and combining the table header structure, etc., the table header part of the Excel table is successfully constructed. This process provides an important foundation for the subsequent generation of the Excel table.

[0104] In one of the embodiments, generating the parent table header according to the parent category name includes:

[0105] Place the parent category names in sequence in the first row of Excel to serve as the main title bar of Excel and generate the parent table header.

[0106] Identify which category names will be regarded as the parent category names. These names usually represent the main classifications or themes of the data in the Excel table. For example, in an Excel table of sales data, the parent category names may include "Product Information", "Sales Data", "Customer Information", etc. Before starting to generate the table header, a new Excel workbook and worksheet are created. This worksheet will serve as the basis for generating the Excel table. Place the parent category names in sequence in the first row of the Excel worksheet according to a predetermined order. These names will become the main title bar of the Excel table, that is, the parent table header.

[0107] In this embodiment, the parent table header is automatically generated, reducing manual operations; ensuring that the table header structure is consistent with the original data structure; significantly improving the efficiency and accuracy of large-scale data processing.

[0108] In one of the embodiments, generating the child table headers according to the list of sub-items under each parent category includes:

[0109] Step 1: Traverse the list of sub-items under each parent category.

[0110] Traverse each previously determined parent category and obtain the list of child items under it. These lists of child items usually represent the specific data items or classifications under each parent category. For example, in an Excel table of sales data, if "Product Information" is a parent category, the list of child items under it may include "Product Name", "Model", "Price", etc.

[0111] Step 2: According to the attribute structure of each child item in the list of child items, add the corresponding sub-heading fields under the parent category name adjacent to the list of child items to generate the sub-level table header.

[0112] After obtaining the list of child items under each parent category, further analyze the attribute structure of these child items. This includes determining the name, data type (such as text, number, date, etc.), and whether it contains a nested structure (such as sub-categories or sub-properties) of each child item. This information is crucial for correctly placing the sub-heading fields in Excel later. According to the analyzed attribute structure of the child items, add the corresponding sub-heading fields under the parent category name adjacent in the Excel worksheet. These sub-heading fields will become the sub-level table headers of the Excel table, used to identify the specific data items or classifications under each parent category. When all the sub-heading fields under the parent categories are added to the Excel worksheet according to the predetermined logic and format, the generation of the sub-level table header is completed. At this time, the header part of the Excel table already contains all the necessary parent and sub-level heading fields, providing a clear framework for filling in the specific data later.

[0113] In this embodiment, a multi-level table header structure is automatically generated to adapt to complex data display requirements; the hierarchical relationship of the data is maintained to improve the readability of the table and the organization of the data; it can flexibly adapt to data structures of different depths without manual adjustment of the table header.

[0114] In one of the embodiments, after filling the data in the object array into the corresponding positions in the Excel table according to the table header structure corresponding to the header part and generating the Excel table, it includes:

[0115] Store the generated Excel table in the preset storage path.

[0116] After generating the Excel table, determine a preset storage path here to save the table. This storage path can be a folder in the local file system or a network storage location (such as a cloud drive, shared folder, etc.). The selection of the preset storage path should be based on the actual application scenario and requirements to ensure that users can easily access and manage the generated Excel table. After determining the preset storage path, the generated Excel table can be stored in this path. This step usually involves using the save or export function provided by the Excel operation library or tool to save the table as an Excel file in the.xlsx or.xls format.

[0117] Overall, in a specific application example, the method for automatically generating an Excel table in this application includes the following steps:

[0118] Step 1: Define the data model. Step 1 includes:

[0119] Step 101, define one or more data classes according to actual needs (for example: ParentCategory and ChildItem), and each class contains attributes describing the characteristics of this level.

[0120] Step 102, use these classes to construct an object array representing the entire data set.

[0121] Step 2: Initialize the Excel document. Step S201: Create a new Excel workbook using Apache POI or other relevant libraries. Step 2 includes:

[0122] Step 202: Select or create the target worksheet to store the finally generated data table.

[0123] Step 3: Generate the parent table headers. Step 3 includes:

[0124] Step 301: Traverse the outermost object array to identify all unique parent category names.

[0125] Step 302: Place these parent categories in sequence in the first row of Excel as the main title bar.

[0126] Step 4: Generate the child table headers. Step 4 includes:

[0127] Step 401: For each parent category, continue to traverse the list of its sub-items in depth.

[0128] Step 402: According to the specific attributes of the sub-items, add appropriate sub-title fields directly below the corresponding parent title.

[0129] Step 403: If there are more levels, recursively apply the same logic until the finest granularity is reached.

[0130] Step S5: Fill in data. Step 5 includes:

[0131] Step 501: Extract information from the original object array according to the established multi-level header framework and fill it into the corresponding cells.

[0132] Step 502: Pay attention to maintaining the correct alignment relationship between the data and the header.

[0133] Step 6: Save the file. Step 6 includes:

[0134] Step 601: After completing the data entry, call the method provided by the API to save the current workbook to the specified location.

[0135] Step 602: Export the final result as an.xlsx format file and allow the user to directly download it.

[0136] In the above specific application example, the method for automatically generating an Excel table in this application has the following remarkable technical effects: The automated process reduces human errors and the need for manual operations, significantly improving work efficiency. It is applicable to various complex header layouts, enhancing the clarity of data presentation. The flexible design allows for easy adjustment to adapt to different data sets.

[0137] It should be understood that although the steps in the flowcharts involved in the above embodiments are sequentially shown according to the arrows, these steps do not necessarily need to be executed in the order indicated by the arrows. Unless there is a clear description in this article, the execution of these steps does not have a strict order limit, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above embodiments may include multiple steps or multiple stages. These steps or stages do not necessarily need to be executed at the same time, but can be executed at different times. The execution order of these steps or stages does not necessarily need to be sequential, but can be executed alternately or in turn with at least a part of other steps or steps or stages in other steps.

[0138] Based on the same inventive concept, the embodiments of this application also provide an apparatus for automatically generating an Excel table for implementing the method for automatically generating an Excel table involved above. The solution provided by this apparatus for solving problems is similar to the solution described in the above method. Therefore, the specific limitations in one or more embodiments of the apparatus for automatically generating an Excel table provided below can refer to the limitations on the method for automatically generating an Excel table in the above text, and will not be repeated here.

[0139] In one embodiment, as Figure 5 As shown, a device for automatically generating an Excel table is provided, comprising:

[0140] An array construction module 100 is used to create an object array based on an Excel table creation requirement;

[0141] The traversal module 200 is used to traverse the object array, parse the attributes of each object in the object array, and construct a header structure, which includes a single-column parent header and multiple-column child headers;

[0142] A new module 300 is used to create a new Excel workbook and worksheet through a preset Excel operation library;

[0143] The header generation module 400 is used to dynamically generate the header part of the Excel table according to the constructed header structure under the Excel workbook and worksheet;

[0144] The table generation module 500 is used to fill the data in the object array into the corresponding position of the Excel table according to the header structure corresponding to the header part, so as to generate an Excel table.

[0145] In one embodiment, the array construction module 100 is further used to obtain the creation requirements of an Excel table; define data classes based on the creation requirements, each data class including attributes describing corresponding hierarchical characteristics; and collect the defined data classes to construct an object array.

[0146] In one of the embodiments, the array building module 100 is also used to create a parent category named ParentCategory and a child category named Education; according to the creation requirements, a private attribute educationList is defined in the ParentCategory class; an @Excel annotation is added to the private attribute educationList, and the required attributes describing the name parameter corresponding to the @Excel annotation are defined in the Education class to create the ParentCategory class and the Education class.

[0147] In one of the embodiments, the traversal module 200 is also used to traverse the outermost object array in the object array to identify all unique parent category names; parse the properties of each object in the object array to identify the list of child items under each parent category; generate a parent header according to the parent category name, and generate a child header according to the list of child items under each parent category; combine the parent header and the child header to construct a header structure.

[0148] In one of the embodiments, the traversal module 200 is further used to place the parent category names in sequence in the first row of Excel to serve as the main title bar of Excel to generate a parent table header.

[0149] In one embodiment, the traversal module 200 is further configured to traverse the list of sub-items under each parent category; according to the attribute structures of the respective sub-items in the list of sub-items, add corresponding sub-heading fields under the parent category names adjacent to the list of sub-items to generate a sub-level table header.

[0150] In one embodiment, the table generation module 500 is further configured to store the generated Excel table to a preset storage path.

[0151] Each module in the above device for automatically generating an Excel table can be implemented in whole or in part by software, hardware, and their combination. The above modules can be embedded in the processor of the computer device in hardware form or be independent of it, or can be stored in the memory of the computer device in software form, so as to facilitate the processor to call and execute the operations corresponding to the above respective modules.

[0152] In one embodiment, a computer device is provided. The computer device can be a server, and its internal structure diagram can be as Figure 6 shown. The computer device includes a processor, a memory, and a network interface connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store preset data. The network interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, it implements a method for automatically generating an Excel table.

[0153] Those skilled in the art can understand that Figure 6 the structure shown in

[0154] In one embodiment, a computer device is provided, including a memory and a processor. A computer program is stored in the memory, and when the processor executes the computer program, it implements the above method for automatically generating an Excel table.

[0155] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by the processor, it implements the above method for automatically generating an Excel table.

[0156] In one embodiment, a computer program product is provided, including a computer program which, when executed by a processor, implements the above method for automatically generating an Excel table.

[0157] Those of ordinary skill in the art can understand that all or part of the processes in the above embodiment methods can be completed by instructing relevant hardware through a computer program. 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 various methods. Among them, any reference to a memory, database, or other medium used in the various embodiments provided in this application can include at least one of non-volatile and volatile memories. Non-volatile memory can include Read-Only Memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetoresistive random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM), etc. The databases involved in the various embodiments provided in this application can include at least one of relational databases and non-relational databases. Non-relational databases can include distributed databases based on blockchain, etc., without limitation. The processors involved in the various embodiments provided in this application can be general-purpose processors, central processors, graphics processors, digital signal processors, programmable logic devices, data processing logics based on quantum computing, etc., without limitation.

[0158] The technical features of the above embodiments can be combined arbitrarily. For the sake of brevity of description, not all possible combinations of the various technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, it should be considered to be within the scope described in this specification.

[0159] The above embodiments merely illustrate several implementation manners of the present application. The description thereof is relatively specific and detailed, but it should not be construed as a limitation to the patent scope of the present application. It should be noted that for those of ordinary skill in the art, without departing from the concept of the present application, several modifications and improvements can still be made, and these all fall within the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the appended claims.< / education>

Claims

1. A method for automatically generating an Excel table, characterized in that, The method includes: Creating requirements based on an Excel table and constructing an object array; Traversing the object array and parsing the attributes of each object in the object array to construct a header structure, where the header structure includes a single-column parent header and multi-column child headers; Creating a new Excel workbook and worksheet through a preset Excel operation library; Dynamically generating the header part of the Excel table under the Excel workbook and worksheet according to the constructed header structure; Filling the data in the object array into the corresponding positions of the Excel table according to the header structure corresponding to the header part to generate an Excel table.

2. The method according to claim 1, wherein The creating requirements based on an Excel table and constructing an object array includes: Obtaining the creating requirements of the Excel table; Defining data classes based on the creating requirements, where each data class contains attributes describing the characteristics of the corresponding level; Aggregating the defined data classes to construct an object array.

3. The method according to claim 2, characterized in that The defining data classes based on the creating requirements, where each data class contains attributes describing the characteristics of the corresponding level includes: Creating a parent category named ParentCategory and a child category named Education; Defining a private attribute educationList in the ParentCategory class according to the creating requirements; Adding an @Excel annotation to the private attribute educationList and defining the required attributes for describing the corresponding name parameter of the @Excel annotation in the Education class to create the ParentCategory class and the Education class.

4. The method according to claim 1, characterized in that The traversing the object array and parsing the attributes of each object in the object array to construct a header structure includes: Traversing the outermost object array in the object array to identify all unique parent category names; Parsing the attributes of each object in the object array to identify the sub-item list under each parent category; Generating a parent header according to the parent category name and generating child headers according to the sub-item list under each parent category; Combining the parent header and the child headers to construct a header structure.

5. The method according to claim 4, wherein Generating a parent header according to the parent category name includes: Sequentially placing the parent category names in the first row of Excel as the main title bar of Excel to generate a parent header.

6. The method according to claim 5, characterized in that Generating child headers according to the sub-item list under each parent category includes: Traversing the sub-item list under each parent category; Adding corresponding sub-title fields under the parent category name adjacent to the sub-item list according to the attribute structure of each sub-item in the sub-item list to generate child headers.

7. The method according to claim 1, wherein After filling the data in the object array into the corresponding positions of the Excel table according to the header structure corresponding to the header part to generate an Excel table, it includes: Storing the generated Excel table to a preset storage path.

8. An apparatus for automatically generating an Excel table, characterized in that, The device includes: An array construction module for creating requirements based on an Excel table and constructing an object array; A traversal module, configured to traverse the object array, parse the attributes of each object in the object array, and construct a header structure, where the header structure includes a single-column parent header and multiple-column child headers; A new creation module, configured to create a new Excel workbook and worksheet through a preset Excel operation library; A header generation module, configured to dynamically generate the header part of an Excel table according to the constructed header structure in the Excel workbook and worksheet; A table generation module, configured to fill the data in the object array into the corresponding positions of the Excel table according to the header structure corresponding to the header part, and generate an Excel table.

9. A computer device, comprising a memory and a processor, the memory storing a computer program, characterized in that, When the processor executes the computer program, the steps of the method according to any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images