Method and device for generating Excel file with multiple sheet pages based on multi-dimensional data

Abstract

The invention relates to a method for generating an Excel file with multiple sheet pages based on multi-dimensional data, which is characterized by comprising the following steps of: acquiring original data from a server; the structure of the original data is judged, and a corresponding API is called to convert the data into a prepared data structure of multiple sheet pages; performing document operation page by page on the basis of a preparatory data structure of multiple sheet pages; and calling a file writing method, and generating and storing the document object as an Excel file. According to the method, the data cleaning, sorting and exporting processes are simplified, and the manual data processing time in financial quantitative research is shortened.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a method and device for generating a multi-sheet Excel file based on multi-dimensional data. Background Art

[0002] In financial quantitative systems, multi-dimensional data presentation is common. For high-authority users, the system needs to support the download of multi-dimensional data. Currently, the systems on the market only support the download of single-sheet .xlsx files. However, multi-dimensional and multi-sheet downloads involve data reorganization and transformation, and no relevant system currently supports such a function.

[0003] Therefore, the system cannot provide good support for downloading xlsx files with multi-dimensional data and multiple sheets, which requires users to download multiple xlsx files, wasting a lot of time. Summary of the Invention

[0004] To address the shortcomings of the existing technology, the present invention is based on the xlsx.js tool library and implements the download of multi-dimensional data and multi-sheet xlsx files on the financial quantitative system, eliminating the need for users to download multiple xlsx files. This improves the user experience while reducing the time consumed by financial quantitative workers in studying multi-dimensional data.

[0005] A first aspect of the present invention provides a method for generating a multi-sheet Excel file based on multi-dimensional data, characterized by comprising:

[0006] S1 obtains raw data from the server;

[0007] S2 determines the structure of the original data and calls the corresponding API to convert the data into a preparatory data structure with multiple sheets;

[0008] S3 is based on a multi-sheet prepared data structure and performs document operations page by page.

[0009] S4 calls the file writing method to generate and save the document object as an Excel file.

[0010] Specifically, use the axios or fetch library to send HTTP requests; determine the original data structure to see if it conforms to the table data format of the antd framework; and use the DatasLakeTool data lake tool to encapsulate multi-sheet data conversion.

[0011] Build page-by-page operation logic, specifically including: generate document objects page by page and complete operations on each sheet; use XLSX.utils.book_new() to create the document object wBook; traverse the child elements of multiDataList and build data objects for each sheet page separately; build a single-page object and use XLSX.utils.json_to_sheet(singleSheetData) to generate a single-page object oneSheet, convert the prepared data structure into a sheet format, and facilitate adding it to the document object; dynamically set the table width, calculate the character length of each column of data, and dynamically set the table width oneSheet['! cols'] to avoid invisible data due to the narrow table width and improve the user experience; add a single page to the document object, use XLSX.utils.book_append_sheet(wBook,oneSheet,singleSheetName) to add a single page to the document object, and gradually add each single page to the document object to form a complete multi-sheet file; write the file to complete the file download, use XLSX.writeFile(wBook,${fileName}.xlsx) to generate and download the xlsx file, generate an Excel file containing multiple sheet pages, and realize one-time download of multi-dimensional data.

[0012] Exemplarily, the S1 obtains the original data, including: implementing it through an HTTP request, and the HTTP request is sent using the axios or fetch library.

[0013] Exemplarily, the S2 determines the structure of the original data and calls the corresponding API to convert the data into a preparatory data structure of multiple sheets, including:

[0014] If the original data conforms to the table data structure rendered by the Ant Design framework, call the first API to convert the data into a preparatory data structure of multiple sheets;

[0015] If the original data does not conform to the table data structure rendered by the Ant Design framework, the second API is called to clean the data and convert it into a preparatory data structure of multiple sheets.

[0016] Exemplarily, the S3 performs document operations page by page based on a prepared data structure of multiple sheets, including:

[0017] S301 creates an Excel document object;

[0018] S302 traverses each sheet page data and generates a single sheet object using the json_to_sheet method;

[0019] S303 dynamically adjusts the column width of a single sheet;

[0020] S304 adds a single sheet object to the document object.

[0021] Exemplarily, the first API can convert table data rendered by the Ant Design framework into a prepared data structure including sheet data and sheet name;

[0022] Exemplarily, the second API can clean and organize the original data in JSON format into a prepared data structure containing sheet data and sheet names, and the cleaning includes field matching, data screening and format conversion.

[0023] Exemplarily, the operation of dynamically adjusting the column width of a single sheet in S303 includes:

[0024] Calculate the column width based on the character length of each column data;

[0025] The S4 calls the file writing method, including: calling

[0026] XLSX.writeFile(wBook, fileName) is implemented, where the fileName includes the file name and extension.

[0027] The present invention also provides a device for generating a multi-sheet Excel file based on multi-dimensional data, characterized by comprising:

[0028] Data acquisition module, used to obtain raw data from the server;

[0029] The data processing module is used to determine the original data structure and call the corresponding API to convert the data into a preparatory data structure of multiple sheets;

[0030] A page processing module for performing document operations page by page based on a prepared data structure;

[0031] The file writing module is used to call the file writing method to generate and save the document object as an Excel file.

[0032] The present invention also provides a computer-readable storage medium, characterized in that a computer program is stored on the storage medium, and when the computer program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

[0033] The present invention also provides an electronic device, characterized in that it includes a processor and a memory;

[0034] The memory is used to store computer programs;

[0035] The processor is configured to execute the method according to any one of claims 1 to 6 by calling a stored computer program.

[0036] The present invention also relates to a computer program product, comprising a computer program and / or instructions, characterized in that the computer program and / or instructions implement the steps of the above method when executed by a processor.

[0037] The beneficial effects of the present invention are: (1) improving the compatibility with different data structures and reducing the workload of users in manually adjusting data formats; (2) automatically adjusting the table width according to the character length to avoid the table width being too narrow to cause data to be invisible, thereby improving the user experience; (3) simplifying the data cleaning, sorting and exporting processes and reducing the manual data processing time in financial quantitative research. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] Figure 1 This is a flow chart of a method for generating a multi-sheet Excel file based on multi-dimensional data according to the present invention.

[0039] Figure 2 This is a schematic diagram of the structure of a device for generating a multi-sheet Excel file based on multi-dimensional data according to the present invention. DETAILED DESCRIPTION

[0040] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. The components of the embodiments of the present invention generally described and shown in the drawings herein can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present invention provided in the drawings is not intended to limit the scope of the claimed invention, but merely represents selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0041] It should be noted that similar reference numerals and letters represent similar items in the following drawings. Therefore, once an item is defined in one drawing, it does not need to be further defined or explained in subsequent drawings. At the same time, in the description of the present invention, the terms "first", "second", etc. are used only to distinguish the description and should not be understood as indicating or implying relative importance.

[0042] The first aspect of the present invention provides a method for generating a multi-sheet Excel file based on multi-dimensional data, such as Figure 1 Shown, including:

[0043] S1 obtains raw data from the server;

[0044] Specifically, use the axios or fetch library to send HTTP requests, and the backend returns multi-dimensional data in JSON format, where each dimension corresponds to a sheet of data. This allows you to dynamically obtain multi-dimensional raw data from the server and provide the basic data source for generating multi-sheet files.

[0045] S2 determines the structure of the original data and calls the corresponding API to convert the data into a preparatory data structure with multiple sheets;

[0046] Specifically, the original data structure is compared to see if it conforms to the AntD table format. Based on the structure of table data rendered by the AntD framework, the data structure of a single sheet is a one-dimensional array whose elements are JSON objects with the following structure: [{key: '1', fund: 'Huaxia Pension Hybrid Fund', net: 1.0312, type: 'Mixed',},{key: '2', fund: 'China Merchants Plan A Money Fund', net: 1.0812, type: 'Currency',},] . This compares and verifies the data structure. If the data is a table rendered by the AntD framework, the first API of the DatasLake Tool is used to convert the AntD table data into XLSL.js data, specifically into a multiDataList data structure for multiple sheets. If the data is not a table rendered by the AntD framework, the second API of the DatasLakeTool is used to convert the JSON data into XLSL.js data, specifically into a multiDataList data structure for multiple sheets. This design improves compatibility with different data structures, automatically adapts to antd and non-antd data, and reduces the workload of users manually adjusting data formats.

[0047] The prepared data for multiple sheet pages has the following structure:

[0048]

[0049] It is also in the form of an array, embedding descriptive Json format data of each sheet, where singleSheetName is the name of a single sheet and singleSheetData is a one-dimensional array of a single sheet.

[0050] S3 is based on a multi-sheet prepared data structure and performs document operations page by page.

[0051] Specifically, use the xlsl.js tool library to perform page-by-page operations, that is, each sheet page must go through these operations, and the page-by-page operations are divided into the following steps:

[0052] (1) Build the document object of xlsl.js, that is, wBook = XLSX.utils.book_new(). The function of the document object is to add each sheet into it in the future;

[0053] (2) Traverse the sub-element singleSheetItem of mutilDataList, singleSheetItem is

[0054]

[0055] (3) Use the xlsl.js library API to construct a single-sheet object: oneSheet = XLSX.utils.json_to_sheet(); here, oneSheet is the document object to be added to wBook. Set the table width based on the length of the data: oneSheet['!col'] = dynamic sub-character length. Setting the width prevents the default table width from being insufficient, resulting in only partial data being visible.

[0056] In addition, API 1: Converts AntD table data to multiDataList format; API 2: Converts arbitrary JSON data to multiDataList format. This step provides a unified data preprocessing interface to ensure that the data is ultimately converted into a standardized multiDataList structure, while also enabling data organization and cleaning to facilitate subsequent document generation operations.

[0057] S4 calls the file writing method to generate and save the document object as an Excel file.

[0058] Specifically, the final step of the page-by-page operation is to append each generated single-sheet object, oneSheet, to the document object, wBook, using book_apend_sheet() in xlsl.js: XLSX.utils.book_apend_sheet(wBook, oneSheet, sheetName). Each time through the loop, a single sheet is added to the document object, wBook, until the mutilDataList is completely traversed and all single-sheets are appended to wBook. Finally, the generated document objects are written to a disk file using XLSX.writeFile(wBook, `${fileName}.xlsx`), where `${fileName}.xlsx` represents the name of the generated .xlsx file.

[0059] The present invention also provides a device for generating a multi-sheet Excel file based on multi-dimensional data, characterized by comprising:

[0060] Data acquisition module, used to obtain raw data from the server;

[0061] The data processing module is used to determine the original data structure and call the corresponding API to convert the data into a preparatory data structure of multiple sheets;

[0062] A page processing module for performing document operations page by page based on a prepared data structure;

[0063] The file writing module is used to call the file writing method to generate and save the document object as an Excel file.

[0064] By using this device, the above-mentioned processing method can be executed and the corresponding technical effects can be achieved.

[0065] An embodiment of the present invention also provides a computer-readable storage medium capable of implementing all steps of the method in the above embodiment. The computer-readable storage medium stores a computer program that implements all steps of the method in the above embodiment when executed by a processor.

[0066] An embodiment of the present invention also provides an electronic device for executing the above-mentioned method. As an implementation device of the method, the electronic device has at least a processor and a memory, and in particular, the memory stores the data and related computer programs required for executing the method, and the processor calls the data and programs in the memory to execute all the steps of the implementation method and obtains the corresponding technical effect.

[0067] Preferably, the electronic device may include a bus architecture, which may include any number of interconnected buses and bridges, and the bus will include various circuits linked together by one or more processors and memories. The bus may also link together various other circuits such as peripheral devices, voltage regulators, and power management circuits, which are all well known in the art and, therefore, will not be described further herein. The bus interface provides an interface between the bus and the receiver and transmitter. The receiver and transmitter can be the same component, namely a transceiver, which provides a unit for communicating with various other systems over a transmission medium. The processor is responsible for managing the bus and general processing, while the memory can be used to store data used by the processor when performing operations.

[0068] Additionally, the electronic device may further include components such as a communication module, an input unit, an audio processor, a display, and a power supply. The processor (or controller, operating control) employed may include a microprocessor or other processor device and / or logic device, which receives input and controls the operation of various components of the electronic device. The memory may be one or more of a cache, flash memory, a hard drive, removable media, volatile memory, non-volatile memory, or other suitable devices, and may store the aforementioned data and information. It may also store programs for executing the relevant information, and the processor may execute the programs stored in the memory to implement information storage or processing. The input unit is used to provide input to the processor, and may, for example, be a keypad or touch input device. The power supply is used to provide power to the electronic device. The display is used to display objects such as images and text, and may, for example, be an LCD display. The communication module is a transmitter / receiver that sends and receives signals via an antenna. The communication module (transmitter / receiver) is coupled to the processor to provide input signals and receive output signals, similar to the case of a conventional mobile communication terminal. Based on different communication technologies, multiple communication modules can be provided in the same electronic device, such as a cellular network module, a Bluetooth module, and / or a wireless local area network module. The communication module (transmitter / receiver) is also coupled to a speaker and a microphone via an audio processor to provide audio output via the speaker and receive audio input from the microphone, thereby implementing common telecommunications functions. The audio processor may include any suitable buffer, decoder, amplifier, etc. In addition, the audio processor is also coupled to a central processing unit, enabling local recording via the microphone and playback of stored audio via the speaker.

[0069] It will be understood by those skilled in the art that embodiments of the present invention may be provided as methods, systems, or computer program products. Thus, the present invention may take the form of an entirely hardware embodiment, an entirely 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 magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0070] 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 flowcharts and / or block diagrams, as well as combinations of processes and / or blocks in the flowcharts and / or block diagrams, 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 flowcharts and / or block diagrams. Figure 1 a process or multiple processes and / or boxes Figure 1 A system that specifies the functions of a box or boxes.

[0071] These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture including an instruction system that is implemented in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0072] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 The present invention is described in detail below. ...

[0073] The above description is merely a preferred embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any changes or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in the present invention are intended to be covered by the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be based on the scope of protection of the claims.

Claims

1. A method for generating a multi-sheet Excel file based on multi-dimensional data, characterized in that: include: S1 obtains raw data from the server; S2 determines the structure of the original data and calls the corresponding API to convert the data into a preparatory data structure with multiple sheets; S3 is based on a multi-sheet prepared data structure and performs document operations page by page. S4 calls the file writing method to generate and save the document object as an Excel file.

2. The method according to claim 1, characterized in that The S1 obtains the original data, including: implementing it through an HTTP request, and the HTTP request is sent using the axios or fetch library.

3. The method according to claim 1, characterized in that S2 determines the structure of the original data and calls the corresponding API to convert the data into a preparatory data structure of multiple sheets, including: If the original data conforms to the table data structure rendered by the Ant Design framework, call the first API to convert the data into a preparatory data structure of multiple sheets; If the original data does not conform to the table data structure rendered by the Ant Design framework, the second API is called to clean the data and convert it into a preparatory data structure of multiple sheets.

4. The method according to claim 1, characterized in that The S3 performs document operations page by page based on the prepared data structure of multiple sheets, including: S301 creates an Excel document object; S302 traverses each sheet page data and generates a single sheet object using the json_to_sheet method; S303 dynamically adjusts the column width of a single sheet; S304 adds a single sheet object to the document object.

5. The method according to claim 3, characterized in that The first API can convert the table data rendered by the Ant Design framework into a prepared data structure containing sheet data and sheet name; The second API can clean and organize the original data in JSON format into a preliminary data structure containing sheet data and sheet names, and the cleaning includes field matching, data screening and format conversion.

6. The method according to claim 4, characterized in that The operation of dynamically adjusting the column width of a single sheet in S303 includes: calculating the column width according to the character length of each column of data; The S4 calls a file writing method, including: implementing by calling XLSX.writeFile(wBook, fileName), where the fileName includes a file name and an extension.

7. A device for generating a multi-sheet Excel file based on multi-dimensional data, characterized in that: include: Data acquisition module, used to obtain raw data from the server; The data processing module is used to determine the original data structure and call the corresponding API to convert the data into a preparatory data structure of multiple sheets; A page processing module for performing document operations page by page based on a prepared data structure; The file writing module is used to call the file writing method to generate and save the document object as an Excel file.

8. A computer-readable storage medium, characterized in that The storage medium stores a computer program, which implements the method according to any one of claims 1 to 6 when executed by a processor.

9. An electronic device, characterized in that: including processor and memory; The memory is used to store computer programs; The processor is configured to execute the method according to any one of claims 1 to 6 by calling a stored computer program.

10. A computer program product comprising a computer program and / or instructions, characterized in that When the computer program and / or instructions are executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images