Excel template-based cloud product data loading dynamic processing and analysis method and system

Abstract

The application discloses a cloud product data loading dynamic processing and analysis method and system based on an Excel template, belongs to the field of cloud product data analysis, and has the characteristics that by presetting an Excel table header attribute, utilizing distributed cache preloading, dynamically reading or generating a prototype file in an import and export mode, the dynamic analysis of the data format of the Excel template is realized; through an efficient Excel template data dynamic incremental analysis processing scheme, the dynamic and efficient analysis of the Excel template is realized; through the import and export processing of the dynamically generated product Excel prototype file, the operation of repeatedly entering product information is greatly reduced, the probability of configuration errors caused by manual filling is reduced, the product loading time is shortened, and the interaction efficiency and loading experience are improved.

Description

Technical Field

[0001] This invention belongs to the field of cloud product data parsing, and in particular relates to a method and system for dynamic processing and parsing of cloud product data based on an Excel template. Background Technology

[0002] Existing technical solutions:

[0003] To meet market demands, cloud computing products are constantly emerging, with vastly different specifications and attributes. Efficiently loading and parsing these cloud products has become a critical issue. Existing solutions can predefine a standard format for the product loading model during the product loading process and manually convert the product's output Excel prototype file into a predefined standard format file. While defining the format of the Excel workbook allows for the extraction and parsing of data from different sheets after entering different cloud product attributes, it does not support dynamic processing and efficient parsing of data from Excel templates.

[0004] Point out the technical problems existing in the existing technical solutions:

[0005] Existing technical solutions cannot dynamically parse data based on changes in the data format of Excel templates. Once the Excel template format changes, the corresponding parsing process must also be adjusted and developed, resulting in significant parsing and implementation costs. Furthermore, they do not support preset or preloaded headers and sequences, and cannot dynamically generate prototype files for different scenarios to dynamically read, write, and parse file content.

[0006] Existing technologies cannot perform dynamic and efficient processing based on the dynamic changes in the data content of Excel templates. This technology supports dynamic and efficient incremental parsing of data in Excel templates, and supports processing only the newly added or modified parts of the file, rather than re-parsing the entire file. Summary of the Invention

[0007] The technical problem to be solved by this invention is to address the shortcomings of the prior art by providing a method and system for dynamic processing and parsing of cloud product data based on Excel templates. By pre-setting the header attributes of Excel spreadsheets, utilizing distributed caching for pre-loading, and dynamically reading or generating prototype files through import and export methods, dynamic parsing of the data format of Excel templates is achieved. Through an efficient dynamic incremental parsing and processing scheme for Excel template data, dynamic and efficient parsing of Excel templates is realized.

[0008] To solve the above-mentioned technical problems, the present invention adopts the following technical solution:

[0009] A method for dynamically loading, processing, and parsing cloud product data based on an Excel template, specifically including the following steps:

[0010] Step 1, Pre-setting header attributes: Mark and set the header and sequence of the product prototype for parsing the Excel file and converting it into an operable data structure;

[0011] Step 2, Construct the cache object: Construct the initial cache object by preloading the table header and its sequence identifier configuration through distributed caching;

[0012] Step 3, Read and parse the data: When a request comes in, obtain the distributed cache preload table header and its sequence identifier configuration;

[0013] Step 4, Information Processing and Return: When the request is an export request, the dynamically generated Excel file is output to the client; when the request is an import request, the data in the first format file is updated incrementally using an incremental data approach, and the changed information is assembled into a front-end and back-end incremental information body and transmitted to the front-end in JSON format.

[0014] Step 5, Front-end rendering: Dynamically render the JSON data in the message body onto the page, and apply special effects to the incremental changes.

[0015] As a further preferred embodiment of the present invention, a dynamic processing and parsing method for cloud product data loading based on an Excel template, in step 1, the attribute preset includes attribute name, data type, format, and validation rules.

[0016] As a further preferred embodiment of the cloud product data loading dynamic processing and parsing method based on Excel template of the present invention, in step 2, when an import or export request is received, the header information of the Excel document is quickly and dynamically generated according to preset attributes.

[0017] As a further preferred embodiment of the present invention's method for dynamically processing and parsing cloud product data based on an Excel template, in step 3,

[0018] Step 3.1: When a request comes in, if the request is an export request, the table header is dynamically generated by calculating the table header sequence number; based on the general Excel parsing framework, the product information in the relational database is loaded and the information is written into the Excel document;

[0019] Step 3.2: When a request comes in, if the request is an import request, the import file is parsed into a binary first-format file.

[0020] As a further preferred embodiment of the cloud product data loading dynamic processing and parsing method based on Excel template of the present invention, in step 3.2, the first format file is adapted by the implemented adapter to the dynamically generated template into a standard format binary file that can be processed by the backend.

[0021] As a further preferred embodiment of the present invention's method for dynamically processing and parsing cloud product data loading based on an Excel template, in step 5, the front-end and back-end interaction adopts the HTTP protocol based on the REST style.

[0022] A system for dynamic processing and parsing of cloud product data loading based on Excel templates includes a first recording module, a second judgment module, a third positioning module, a fourth processing module, and a fifth update module.

[0023] The first recording module is used to save the state and result of the previous parsing through a distributed cache before performing incremental parsing.

[0024] The second judgment module is used to compare and judge based on the number of parsed rows, columns, cell data, parsing progress, and file timestamp information to monitor whether the content of the Excel file has changed;

[0025] The third positioning module is used to determine which parts are newly added or modified based on the comparison results of the information positioning and changes in the file by the second module.

[0026] The fourth processing module is used to analyze the changed parts that have been located, based on the results processed by the third positioning module.

[0027] The fifth update module is used to update the parsing results and record the parsing progress after incremental parsing is completed.

[0028] As a further preferred embodiment of the cloud product data loading dynamic processing and parsing system based on Excel template of the present invention, the parsing status and results include the number of rows and columns parsed, cell data, parsing progress, and file timestamp information.

[0029] As a further preferred embodiment of the cloud product data loading dynamic processing and parsing system based on Excel templates of the present invention, the fourth processing module is based on the general Excel parsing framework.

[0030] As a further preferred embodiment of the cloud product data loading dynamic processing and parsing system based on Excel template of the present invention, the fifth update module updates the parsing results and records the parsing progress through distributed caching.

[0031] Compared with the prior art, the present invention, employing the above technical solution, has the following technical effects:

[0032] 1. This invention analyzes the characteristics of numerous cloud product prototypes and addresses issues such as poor loading experience, high manual workload, and slow file parsing speed when using standard Excel files in cloud product loading scenarios. It improves upon these issues by pre-setting Excel header attributes, utilizing distributed caching for pre-loading, dynamically generating prototype files, and dynamically reading, writing, and parsing the file content. The backend uses the parsed first-format file for data processing, employing incremental data operations to record each data change before handing it over to the frontend for explicit rendering. By dynamically generating product Excel prototype files for import and export, the invention significantly reduces repetitive product information entry operations, lowers the probability of configuration errors caused by manual input, shortens product loading time, and improves interaction efficiency and loading experience.

[0033] 2. This invention is based on pre-collected product prototype themes, preprocesses the theme's main attributes and sequences, and generates Excel prototype files by dynamically reading and parsing Excel prototype files through distributed caching and preloading.

[0034] 3. When the front-end and back-end of this invention perform multiple import and export interactions, the main theme attribute and sequence cache are obtained from the cache to generate the prototype table header, and the data is changed incrementally starting from the valid data row. Attached Figure Description

[0035] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0036] Figure 1 This is a flowchart of the method of the present invention;

[0037] Figure 2 This is a system schematic diagram of the present invention;

[0038] Figure 3 This is a sequence diagram of the front-end and back-end interaction process for exporting and importing Excel prototype files according to an embodiment of the present invention;

[0039] Figure 4 This is the front-end design prototype page of an embodiment of the present invention;

[0040] Figure 5 This is a schematic diagram of exporting a dynamically generated product Excel prototype according to an embodiment of the present invention. Detailed Implementation

[0041] The technical solution of the present invention will be further described in detail below with reference to the accompanying drawings:

[0042] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present invention. The present invention will be described in detail below with reference to the accompanying drawings and preferred embodiments. The purpose and effects of the present invention will become clearer. It should be understood that the specific embodiments described herein are merely illustrative of the present invention and are not intended to limit the present invention.

[0043] Excel header properties: These are properties used to describe the characteristics or identifiers of each column of data in an Excel file.

[0044] A method for dynamically processing and parsing cloud product data loading based on Excel templates includes the following steps: This invention achieves dynamic parsing of Excel template data formats by pre-setting Excel header attributes, utilizing distributed caching for pre-loading, and dynamically reading or generating prototype files through import / export methods; and achieves dynamic and efficient parsing of Excel template data through an efficient dynamic incremental parsing processing scheme for Excel template data.

[0045] 1. The specific implementation technical solution of the present invention is as follows:

[0046] Firstly, this application provides a solution for dynamically parsing changes in the data format of an Excel template (the main inventive point), applied to the dynamic parsing and information display of Excel template headers, such as... Figure 1 As shown, it includes:

[0047] 1.1 Pre-setting of Table Header Attributes: This involves marking and setting the table headers and sequences of the product prototype. Attribute presets may include information such as attribute name, data type, format, and validation rules. These attributes will be used to parse the Excel file and convert it into a workable data structure for managing and tracking the configuration content.

[0048] 1.2 Constructing the Cache Object: Construct the initial cache object and preload the table headers and their sequence identifier configurations through distributed caching. When an import or export request arrives, the table header information of the Excel document is quickly and dynamically generated based on preset attributes.

[0049] 1.3 Reading and Parsing Data: When a request arrives, the distributed cache preloaded header and its sequence identifier configuration are directly obtained. For multiple repeated requests requiring parsing, the incremental data parsing scheme described below is employed; details are provided in section two.

[0050] A1: If it's an export request, the table header is dynamically generated by calculating the header sequence number. Based on a general Excel parsing framework, product information from a relational database is loaded and written to an Excel document.

[0051] A2: If it is an import request, the imported file will be parsed into a first-format binary file. The first-format file mentioned above refers to the dynamically generated template being adapted into a standard binary file that can be processed by the backend through the implemented adapter, ensuring that the imported file can be correctly processed by the backend.

[0052] 1.4 Information Processing and Return: When the request is an export request, the dynamically generated Excel file is output to the client. When the request is an import request, incremental updates are performed on the data in the first format file, and the changed information is assembled into a front-end and back-end incremental information body, which is then transmitted to the front-end in JSON format.

[0053] 1.5 Front-end rendering: The front-end and back-end interaction adopts the HTTP protocol based on REST style, dynamically rendering the JSON data in the message body on the page, and performing special effects processing on incremental changes to improve the user experience.

[0054] Secondly, this application provides a solution for incremental data parsing when the data content of an Excel template changes dynamically multiple times, such as... Figure 2 As shown, it includes:

[0055] The first recording module: Before performing incremental parsing, it saves the status and results of the previous parsing through a distributed cache, including the number of rows, columns, cell data, parsing progress, file timestamps, and other information.

[0056] The second judgment module compares and judges information such as the number of parsed rows, columns, cell data, parsing progress, and file timestamps to monitor whether the content of the Excel file has changed.

[0057] The third positioning module: If the file has changed, it determines which parts were added or modified based on the comparison results of the file information positioning and changes from the second module.

[0058] The fourth processing module: Based on the results of the third positioning module, it parses the located and changed parts, which can be based on a general Excel parsing framework.

[0059] The fifth update module: After incremental parsing is complete, the parsed results are updated through a distributed cache, and the parsing progress is recorded. The parsed results can be merged with previous results, or the parsing progress record can be updated for use in the next incremental parsing.

[0060] This invention analyzes the characteristics of numerous cloud product prototypes and addresses issues such as poor loading experience, high manual workload, and slow file parsing speed when using standard Excel files in cloud product loading scenarios. It improves upon these problems by pre-setting Excel header attributes, utilizing distributed caching for preloading, dynamically generating prototype files, and dynamically reading, writing, and parsing the file content. The backend uses the parsed first-format file for data processing, employing incremental data operations to record each data change before submitting it to the frontend for explicit rendering. By dynamically generating product Excel prototype files for import and export, repetitive product information entry is significantly reduced, lowering the probability of configuration errors caused by manual input, shortening product loading time, and improving interaction efficiency and loading experience.

[0061] like Figure 3 , Figure 4 and Figure 5 As shown, the improved product loading process of this invention, in order to Figure 1 The sequence diagram of the front-end and back-end interaction flow for exporting and importing Excel prototype files is used as an example. The specific steps are as follows:

[0062] Step 101: User action, initiating an export request. The backend pre-sets the attribute information collected in the pre-process and stores it in a distributed cache. The information collected in the pre-process includes:

[0063] A1: Product Specifications and Attributes: The special properties of the product itself, its differences from other products, and the parameter values ​​that affect the product's pricing.

[0064] A2: Product Specifications and Attributes: Product version, regions where the product is supported for sale, etc.

[0065] A3: Pricing cycle and pricing method: The ordering cycle supported by the product, such as days, months, years, etc.; or the ordering method, such as per piece, yuan / GB, etc.

[0066] Step 102: The backend obtains preset information through distributed caching, calculates the values ​​and sequences of the table headers in the preset content, dynamically generates the table header part of the Excel prototype file, then reads the configuration information of the products currently stored in the relational database, and writes it to the file according to the table header classification. Figure 3 The header section, which contains the pre-configured records, shows the following information: type (product specification attributes collected), version (sales product specification attributes collected), and price plan period (supported ordering methods for the product). Based on the dynamically generated prototype document, it offers greater flexibility and better aligns with product managers' design habits compared to a standardized format file.

[0067] Step 103: The generated Excel prototype file is encrypted using the AES algorithm and uploaded to the file management server for data logging, ensuring data traceability during changes. The final file is then output to the user's client.

[0068] Step 104: Users can modify the specific configuration in the exported file based on the existing configuration and then initiate an import request.

[0069] B1: In the import request, the preset attributes will be obtained from the distributed cache first, and the header information will be calculated directly. There is no need to repeatedly read the header data in the parsing file. It supports parsing from the valid data row, which improves parsing efficiency.

[0070] B2: The configuration information is uniformly parsed into a binary primary format file that can be analyzed by the backend. The file content is verified according to preset rules, and non-compliant configurations or abnormal issues in the conversion process are directly reported to the frontend.

[0071] B3: Record the results of this parsing and temporarily store them using a distributed cache.

[0072] Step 105: Users can repeatedly perform import and export operations. In subsequent import operations, the results of the previous import will be retrieved and compared to monitor data changes. The backend will use incremental changes to persist the data to the database, record the parsing results in a distributed cache, identify the changed data in the request, and return the interaction information body to the frontend in JSON format.

[0073] Step 106: The front end dynamically renders the returned interactive information and highlights the changed data in red to inform users, effectively improving their data awareness when performing import and export operations.

[0074] It will be understood by those skilled in the art that the above descriptions are merely preferred examples of the invention and are not intended to limit the invention. Although the invention has been described in detail with reference to the foregoing examples, those skilled in the art can still modify the technical solutions described in the foregoing examples or make equivalent substitutions for some of the technical features. All modifications and equivalent substitutions made within the spirit and principles of the invention should be included within the scope of protection of the invention. All technical features in this embodiment can be freely combined according to actual needs.

[0075] Finally, it should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for dynamically loading, processing, and parsing cloud product data based on an Excel template, characterized in that: Specifically, it includes the following steps: Step 1, Pre-setting header attributes: Mark and set the header and sequence of the product prototype for parsing the Excel file and converting it into an operable data structure; Step 2, Construct the cache object: Construct the initial cache object by preloading the table header and its sequence identifier configuration through distributed caching; Step 3, Read and parse the data: When a request comes in, obtain the distributed cache preload table header and its sequence identifier configuration; Step 4, Information Processing and Return: When the request is an export request, the dynamically generated Excel file is output to the client; When the request is an import request, the data in the first format file is updated incrementally using the data incremental method. The changed information is then assembled into a front-end and back-end incremental information body and transmitted to the front-end in JSON format. Step 5, Front-end rendering: Dynamically render the JSON data in the message body onto the page, and apply special effects to the incremental changes.

2. The method for dynamic processing and parsing of cloud product data based on an Excel template according to claim 1, characterized in that: In step 1, the preset attributes include attribute name, data type, format, and validation rules.

3. The method for dynamic processing and parsing of cloud product data based on an Excel template according to claim 1, characterized in that: In step 2, when an import or export request comes in, the header information of the Excel document is quickly and dynamically generated based on preset attributes.

4. The method for dynamic processing and parsing of cloud product data based on an Excel template according to claim 1, characterized in that: In step 3, Step 3.1: When a request comes in, if the request is an export request, the table header is dynamically generated by calculating the table header sequence number; based on the general Excel parsing framework, the product information in the relational database is loaded and the information is written into the Excel document; Step 3.2: When a request comes in, if the request is an import request, the import file is parsed into a binary first-format file.

5. The method for dynamic processing and parsing of cloud product data based on an Excel template according to claim 1, characterized in that: In step 3.2, the first format file is adapted by the implemented adapter to transform the dynamically generated template into a standard format binary file that can be processed by the backend.

6. The method for dynamic processing and parsing of cloud product data based on an Excel template according to claim 1, characterized in that: In step 5, the front-end and back-end interactions use the REST-style HTTP protocol.

7. A system for dynamically processing and parsing cloud product data loading based on an Excel template according to any one of claims 1 to 6, characterized in that: It includes a first recording module, a second judgment module, a third positioning module, a fourth processing module, and a fifth update module; The first recording module is used to save the state and result of the previous parsing through a distributed cache before performing incremental parsing. The second judgment module is used to compare and judge based on the number of parsed rows, columns, cell data, parsing progress, and file timestamp information to monitor whether the content of the Excel file has changed; The third positioning module is used to determine which parts are newly added or modified based on the comparison results of the information positioning and changes in the file by the second module. The fourth processing module is used to analyze the changed parts that have been located, based on the results processed by the third positioning module. The fifth update module is used to update the parsing results and record the parsing progress after incremental parsing is completed.

8. The cloud product data loading, dynamic processing, and parsing system based on an Excel template according to claim 7, characterized in that: The parsing status and results include the number of rows and columns parsed, cell data, parsing progress, and file timestamp information.

9. A cloud product data loading, dynamic processing, and parsing system based on an Excel template according to claim 7, characterized in that: The fourth processing module is based on the general Excel parsing framework.

10. A cloud product data loading, dynamic processing, and parsing system based on an Excel template according to claim 7, characterized in that: The fifth update module updates the parsed results through a distributed cache and records the parsing progress.

Images