Cross-application Excel data import processing method and system based on cloud storage

Abstract

The invention provides a cross-application Excel data import processing method and system based on cloud storage, and relates to the technical field of data processing.The method comprises the steps that a processing request of an Excel file from a front-end service application is received through a unified service center; generating a pre-signature URL of the Alicloud OSS, and returning the pre-signature URL to the front end; the front end directly uploads the Excel file to the Alicloud OSS; based on the system configuration record, performing Excel file configuration matching to obtain complete configuration information; based on a Rocket MQ Tag mechanism, delivering the message meeting the condition to a corresponding target business application; the target business application automatically analyzes the Excel file; performing data verification and business logic processing on the analyzed Excel file; counting service logic processing results and generating an error report; and notifying the unified service center to update the processing state according to the business logic processing result.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a cross-application Excel data import and processing method and system based on cloud storage. Background Technology

[0002] With the deepening of enterprise digital transformation and the widespread application of microservice architecture, Excel data import has become a core functional module of various business systems. In large-scale enterprise application environments, there are often multiple independent business systems (such as e-commerce systems, financial systems, supply chain systems, customer management systems, etc.), and each system needs the ability to import and process Excel data.

[0003] Traditional Excel data import solutions generally suffer from the following technical drawbacks: Each business system needs to independently develop its own Excel data import function, including a complete process such as file upload, data parsing, data validation, and error handling, resulting in a large amount of repetitive code and development work, leading to extremely low development efficiency. Furthermore, the Excel import logic of different business systems is independent, making unified management and cross-system collaboration impossible. Summary of the Invention

[0004] In view of the shortcomings of the prior art, the purpose of this invention is to provide a cross-application Excel data import and processing method based on cloud storage. This method solves the problem that existing technologies require each business system to independently develop Excel data import functions, including a complete process such as file upload, data parsing, data validation, and error handling, resulting in a large amount of repetitive code and development work, leading to extremely low development efficiency. Furthermore, the independent Excel import logic of different business systems makes unified management and cross-system collaboration impossible.

[0005] A first aspect of this invention proposes a cross-application Excel data import and processing method based on cloud storage, comprising: S1: Receives processing requests for Excel files from front-end business applications through the RESTful API interface of the unified service center; S2: In response to the processing request, generate a pre-signed URL for Alibaba Cloud OSS and return the pre-signed URL to the front end; S3: The front end uploads the Excel file directly to Alibaba Cloud OSS. When the upload is successful, Alibaba Cloud OSS returns a confirmation of successful file upload and the OSS URL to the front end. S4: Based on the system configuration records, perform Excel file configuration matching to obtain complete configuration information and verify the validity of the OSS URL; S5: Assemble messages based on the complete configuration information, and deliver eligible messages to the corresponding target business applications based on the RocketMQ Tag mechanism; S6: The target business application automatically parses the Excel file; S7: Perform data validation and business logic processing on the parsed Excel file; S8: Calculate the results of business logic processing and generate error reports; S9: Based on the business logic processing result, notify the unified service center to update the processing status.

[0006] A second aspect of this invention provides a cloud storage-based cross-application Excel data import and processing system, comprising: a processor and a memory; The memory stores programs or instructions that can run on the processor, which, when executed by the processor, implement the steps of the cloud storage-based cross-application Excel data import and processing method described in the first aspect.

[0007] A third aspect of the present invention provides a readable storage medium on which a program or instructions are stored, which, when executed by a processor, implement the steps of the cloud storage-based cross-application Excel data import and processing method described in the first aspect.

[0008] The beneficial effects of the technical solutions provided by the embodiments of the present invention include at least the following: (1) In this embodiment of the invention, each business system only needs to introduce the SDK to realize the Excel data import function, without the need for each business system to develop the Excel data import function separately, thus improving development efficiency.

[0009] (2) In this embodiment of the invention, unified configuration management is adopted. By establishing a three-dimensional configuration matrix shared across applications, the import logic of business applications is centralized and standardized. The Excel import rules, template paths, parameter restrictions and permission configurations of all business scenarios are centrally stored and maintained by a unified service center. Each business system does not need to carry out repeated development or independent maintenance configuration, which facilitates true enterprise-level cross-application collaboration and greatly improves the system's flexibility, maintainability and enterprise-level integration capabilities. Attached Figure Description

[0010] The accompanying drawings are for illustrative purposes only and are not intended to limit the invention. Throughout the drawings, the same reference numerals denote the same parts. Obviously, the drawings described below are merely some embodiments of the present invention, and those skilled in the art can obtain other drawings based on these drawings without any creative effort.

[0011] Figure 1 This is a flowchart illustrating a cross-application Excel data import and processing method based on cloud storage, provided by an embodiment of the present invention.

[0012] Figure 2 This is a schematic diagram of the structure of a cross-application Excel data import and processing system based on cloud storage provided in an embodiment of the present invention. Detailed Implementation

[0013] To enable those skilled in the art to better understand the technical solutions in the embodiments of the present invention, the technical solutions 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, not all embodiments. It should be understood that these descriptions are merely exemplary and are not intended to limit the scope of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0014] The following description, in conjunction with the accompanying drawings, details the cross-application Excel data import and processing method based on cloud storage provided by the present invention through specific embodiments and application scenarios.

[0015] Reference manual attached Figure 1 The diagram illustrates a flowchart of a cross-application Excel data import and processing method based on cloud storage provided by an embodiment of the present invention.

[0016] This invention provides a method for importing and processing cross-application Excel data based on cloud storage, which may include the following steps: S1: Receives processing requests for Excel files from front-end business applications through the RESTful API interface of the unified service center.

[0017] Optionally, the processing request may include a business source identifier, a business type number, a file name, and user identity information.

[0018] It should be noted that the system first performs user authentication and permission checks to ensure the legitimacy of the request.

[0019] Furthermore, unified API interfaces and configuration management enable true enterprise-level cross-application collaboration.

[0020] In this embodiment of the invention, all Excel file processing requests from business applications are centrally received and managed through the RESTful API interface of a unified service center. This standardization at the entry level avoids duplication of effort and inconsistent rules caused by each system developing its own interface. Simultaneously, a unified user authentication and permission check mechanism ensures the security and legitimacy of data access from the source; while unified configuration management allows all business systems to share the same set of import rules and parameter constraints, thereby simplifying the access process and improving scalability. Overall, this approach guarantees the security and compliance of cross-application processing while reducing development and maintenance costs, achieving true enterprise-level collaboration and resource sharing.

[0021] S2: In response to the processing request, generate a pre-signed URL for Alibaba Cloud OSS and return the pre-signed URL to the front end.

[0022] It should be noted that a pre-signed URL is a secure link that temporarily authorizes access to a specific OSS file through cryptographic signing and validity period verification.

[0023] In one possible implementation, S2 specifically includes sub-steps S201 to S206: S201: Use Alibaba Cloud OSS access keys and security keys for identity authentication.

[0024] S202: Construct request parameters including bucket name, object key name, HTTP method type, and expiration time.

[0025] S203: Use the HMAC-SHA256 algorithm to perform local encryption calculations on the request parameters and generate a digital signature.

[0026] HMAC-SHA256 is a key-based hash message authentication code algorithm. It combines the message content with a shared key and uses the SHA-256 hash function to calculate a digital signature to ensure the integrity and authenticity of data during transmission and prevent tampering.

[0027] S204: Add a digital signature to the URL to form a complete pre-signed URL.

[0028] The typical format of a pre-signed URL is: https: / / BucketName.Endpoint / Object?signature parameters.

[0029] It's important to note that a pre-signed URL contains the following key components: bucket domain name and object path, processing parameters, generation date and timestamp, expiration time (3600 seconds, or 1 hour), signature version identifier (OSS4-HMAC-SHA256), access credential information (including access key identifier, date, region, and service type), and digital signature value. These parameters combine to form a complete pre-signed URL, ensuring the security and timeliness of file access. When a third party accesses this URL, OSS verifies the signature parameters; if the parameters have been tampered with or expired, access is denied. In this way, file owners can securely authorize third parties to access files without exposing their keys. This is suitable for short-term file-sharing scenarios, allowing third parties to upload or download files within the validity period, ensuring data security.

[0030] S205: Send the pre-signed URL to the Alibaba Cloud OSS server. The Alibaba Cloud OSS server verifies the signature parameters and returns the final pre-signed URL. The pre-signed URL allows the front-end to directly upload files to the Alibaba Cloud OSS server within a specified time.

[0031] S206: When a third party accesses a pre-signed URL, the Alibaba Cloud OSS server verifies the signature parameters. If the signature parameters have been tampered with or expired, access will be refused.

[0032] In this embodiment of the invention, by generating a pre-signed URL with a digital signature and expiration date, the front end can directly upload Excel files to Alibaba Cloud OSS without exposing the server-side access key. This mechanism improves file upload efficiency, avoids performance bottlenecks caused by application server relay, and ensures access security through HMAC-SHA256 encryption and expiration date control, preventing link tampering or long-term abuse. Simultaneously, file access permissions are finely managed, meeting the flexible sharing needs across application scenarios while ensuring the integrity and security of data transmission, making it suitable for enterprise-level short-term file interaction and batch import scenarios.

[0033] S3: The frontend directly uploads the Excel file to Alibaba Cloud OSS. Upon successful upload, Alibaba Cloud OSS returns a confirmation of successful upload and the OSS URL to the frontend. This direct upload process bypasses the application server, avoiding server bandwidth consumption and performance bottlenecks.

[0034] In one possible implementation, S3 specifically includes sub-steps S301 and S302: S301: When the front end obtains the pre-signed URL, use the HTTP PUT method to directly upload the Excel file to Alibaba Cloud OSS.

[0035] The HTTP PUT method is a request method in the HTTP protocol, typically used by clients to upload or update resources to servers. In direct file transfer scenarios, file content can be written directly to a specified storage path, avoiding forwarding through an intermediate server, thereby improving efficiency and reducing bandwidth pressure.

[0036] S302: Alibaba Cloud OSS returns a confirmation of successful file upload and the OSS URL to the front end.

[0037] In this embodiment of the invention, the front-end directly uploads the Excel file to Alibaba Cloud OSS via a pre-signed URL, eliminating the need for the application server as an intermediary. This significantly reduces server bandwidth and storage pressure, while also minimizing system bottlenecks and potential performance losses. Utilizing the HTTP PUT method, file content can be efficiently and accurately written to the specified storage path, making the upload process faster and more stable. Simultaneously, OSS immediately returns confirmation information and the file access address upon upload completion, ensuring the traceability and reliability of the upload results, thereby improving the overall system's concurrent processing capabilities and user experience.

[0038] S4: Based on the system configuration records, perform Excel file configuration matching to obtain complete configuration information and verify the validity of OSSURL.

[0039] In one possible implementation, S4 specifically includes sub-steps S401 to S406: S401: When submitting an Excel file import task from the front end, enter the source, type, and OSS URL returned when the Alibaba Cloud OSS upload is successful.

[0040] S402: Based on the source and type parameters passed from the front end, search for the corresponding system configuration record in the configuration table.

[0041] It should be noted that the configuration table includes the following key fields: Application Name field identifies the target application handling the service; System Source field identifies the system from which the service originates; Service Type field identifies the specific service scenario; Operation Type field is fixed as an import operation; Maximum Rows field limits the number of rows in the Excel file; Expiration Time field controls the validity period of the file URL; Template Address field stores the download address of the Excel import template. By configuring these fields in combination, precise management of different service scenarios can be achieved.

[0042] In this embodiment of the invention, the Excel import configuration of all business applications is centrally managed. Adding a new Excel import scenario only requires database configuration and no code modification is needed. The system automatically verifies the size, number of rows, and format of the Excel file to ensure data quality across applications. It also supports runtime configuration updates, which take effect automatically in all related applications.

[0043] S403: Based on the found system configuration record, obtain the app_name and determine the target application system for handling this business.

[0044] S404: Verify the integrity of the configuration, including checking the import template address, maximum line count limit, and file expiration time.

[0045] S405: Verify the validity of the OSS URL to ensure that the file has been successfully uploaded to OSS and is accessible.

[0046] S406: If configuration verification fails or the OSS URL is invalid, an error message is returned. If verification succeeds, complete configuration information and file addresses are obtained for subsequent processing.

[0047] It should be noted that this invention supports unified configuration management across applications, improving operational efficiency by more than 80%.

[0048] In this embodiment of the invention, a centralized configuration table is used to uniformly manage and match Excel import tasks. Processing rules for different business scenarios can be stored and invoked in a standardized and structured manner, thus avoiding inconsistencies and redundancy caused by scattered configurations across systems. When a new import requirement is added, only the database configuration needs to be updated; no code modification or redeployment is required, greatly improving the flexibility of expansion and maintenance. Simultaneously, automated parameter validation (such as file size, number of rows, format, and validity period) ensures the quality and consistency of data import, reducing the error rate. Unified configuration management also makes the access and operation of cross-application import tasks more efficient, enabling rapid global implementation and significantly improving overall operational efficiency, providing reliable assurance for enterprise-level multi-system collaboration.

[0049] S5: Assemble messages based on complete configuration information, and deliver eligible messages to the corresponding target business applications based on the RocketMQ Tag mechanism.

[0050] RocketMQ's Tag mechanism is a tag-based filtering feature provided by the message queue. Message producers can attach tags when sending messages, and consumers, upon subscription, only receive messages that match their configured tags, enabling precise message routing and efficient consumption across application scenarios. Tags are a message filtering mechanism provided by RocketMQ, allowing producers to specify one or more tags when sending messages. Consumers can selectively consume messages based on these tags, achieving message filtering and classification. Each message can carry a tag, and consumers can subscribe to specific messages based on the tag.

[0051] In one possible implementation, S5 specifically includes sub-steps S501 to S508: S501: The message content assembled in the message queue based on the complete configuration information. The complete configuration information includes app_name, source, and type.

[0052] S502: Attach the app_name as a RocketMQ tag to the message's metadata as a key identifier for message filtering.

[0053] S503: Construct a complete processing message containing resource identifier, business type, OSS file address, and user information.

[0054] S504: The unified service center acts as the producer, sending processed messages with application name tags to the RocketMQ Broker.

[0055] S505: Stores message and tag metadata in the RocketMQ message storage system, and the Broker maintains the mapping relationship between tags and messages.

[0056] S506: The SDK's built-in generic message consumer allows users to subscribe to specified tags for precise consumption.

[0057] S507: When each business application introduces the SDK, configure the consumer's subscription tag to the current application name to ensure that it only consumes messages that belong to itself.

[0058] S508: RocketMQ's Broker uses a message filtering mechanism to accurately deliver messages that meet certain conditions to the corresponding consumer instances based on the tags subscribed by the consumers.

[0059] In this embodiment of the invention, by binding the name of the business application as a tag to the message in RocketMQ, accurate routing and efficient distribution of messages across application scenarios are achieved. Producers only need to send a message once, and the Broker can automatically deliver the message to the matching target application based on the tag filtering mechanism, avoiding erroneous consumption and invalid transmission. Consumers only need to subscribe to their corresponding tag to receive relevant tasks; the access configuration is simple and clear, reducing development and maintenance costs. Simultaneously, this mechanism performs filtering at the Broker layer, significantly improving distribution efficiency; combined with the SDK's built-in consumer and fault tolerance mechanisms, it also ensures the reliability and scalability of message processing, thereby supporting multi-system decoupled collaboration, load balancing, and flexible expansion, truly meeting enterprise-level cross-application data import needs.

[0060] S6: The target business application automatically parses the Excel file.

[0061] It's important to note that business applications need to create a custom Excel import processor that inherits from the generic import processor base class. This processor requires configuration of three key pieces of information: system source identifier (e.g., SaaS system), business type number (e.g., business type 78), and core data processing method. In the core data processing method, the business application iterates through the list of parsed Excel data objects, executing specific business processing logic for each object. If an exception occurs during processing, corresponding error messages are set in the data object, and the SDK automatically collects these error messages and generates an error report. Throughout the process, the SDK automatically handles all the complex logic, including Excel file parsing, data validation, error collection, result statistics, and status updates; the business application only needs to focus on the implementation of the core business logic.

[0062] In one possible implementation, S6 specifically includes sub-steps S601 to S606: S601: The SDK component of the target business application automatically receives processing tasks from the message queue.

[0063] S602: Download the Excel file from OSS to the local temporary directory based on the OSS URL in the message.

[0064] S603: Uses the Excel parsing engine to read file content and automatically identify header rows and data rows.

[0065] S604: Map Excel cell data to object properties based on a predefined data object template.

[0066] S605: Performs data type conversion, converting text, numbers, dates, and other formats into their corresponding data types.

[0067] S606: Generates a list of objects containing all rows of data, each object representing one row of data in the Excel file.

[0068] In this embodiment of the invention, by integrating a unified SDK into business applications and implementing a custom import processor, the parsing, field mapping, and type conversion of Excel files can be completed automatically. Developers only need to focus on their core business logic, greatly reducing integration costs and development complexity. The SDK uniformly handles file download, header recognition, data object generation, and exception handling, ensuring consistency and standardization in the parsing process across different business systems, reducing redundant coding and potential errors. Simultaneously, the parsing results are provided directly as a list of objects, facilitating subsequent data validation and business logic processing, enabling efficient expansion and rapid integration across application environments.

[0069] S7: Perform data validation and business logic processing on the parsed Excel file.

[0070] In one possible implementation, S7 specifically includes sub-steps S701 to S704: S701: Performs multi-level validation on a list of data objects, including mandatory field checks, data format validation, and enumeration value range checks.

[0071] S702: Passes the verified data to the processor of the business application to execute specific business logic.

[0072] S703: For data that fails to be validated, mark the specific error reason and error type in the object.

[0073] S704: For data that fails to be processed, capture the exception information and mark it as a processing failure.

[0074] In this embodiment of the invention, by introducing multi-layered validation and business logic processing into the data import process, non-compliant data can be identified and isolated at an early stage, preventing erroneous data from entering the business system and thus ensuring overall data quality and system stability. Data that passes validation can efficiently enter the business processing flow, while data that fails validation or is processed abnormally is clearly marked with the reason, facilitating subsequent tracking and correction. This layered control improves the accuracy and reliability of data processing while reducing the cost of manual investigation, making the entire import process more intelligent and controllable.

[0075] S8: Calculate the results of business logic processing and generate error reports.

[0076] In one possible implementation, S8 specifically includes sub-steps S801 to S20: S801: Initialize the statistics counter to record the total number of rows in the Excel file, the number of rows that passed data validation, the number of rows that failed data validation, the number of rows that were successfully processed by business logic, and the number of rows that failed to be processed by business logic.

[0077] S802: Traverse the parsed list of data objects and perform validation on each data object.

[0078] S803: Call the data processing method implemented by the business application on the data object that has passed the verification.

[0079] S804: In the data processing method, the business application set an error message to indicate that the data object processing failed.

[0080] S805: Update the corresponding counter based on the error message status of the data object.

[0081] S806: Create a list of error data objects to store all data records containing error information.

[0082] S807: Traverse all data objects and filter out records containing error information.

[0083] S808: Add the data object that failed to be validated to the error list, and mark the error type as "Validation failed".

[0084] S809: Add the data object that failed to process the business logic to the error list, and mark the error type as "processing failed".

[0085] S810: Determine if the error data list is empty; if it is empty, do not generate an error report file.

[0086] S811: If erroneous data exists, create a new Excel file based on the header structure of the original Excel file.

[0087] S812: Add an "Error Reason" column to the existing columns to display specific error information.

[0088] S813: Write a list of error data objects to a new Excel file, with each row containing the original data and the corresponding error reason.

[0089] S814: Sets the format of an Excel file, including header style and column width auto-adjustment.

[0090] S815: The unique filename for generating error report files, which typically includes a timestamp and business type identifier.

[0091] S816: Call the OSS client to upload the error report Excel file to the specified storage bucket.

[0092] S817: Obtain the access address of the uploaded file for subsequent downloading and viewing.

[0093] S818: Records information related to the error report file, including the file name, file size, and upload time.

[0094] S819: Calculate the success rate of processing, the failure rate of verification, and the failure rate of business processing.

[0095] S820: Construct a processing result object containing all statistical information for subsequent state updates.

[0096] In this embodiment of the invention, by introducing a unified statistical and error reporting mechanism into the import process, the system can comprehensively record the success and failure of data processing and present the problem data and specific reasons intuitively in the form of a visualized error Excel file. This not only facilitates users to quickly locate and fix errors, reducing manual troubleshooting costs, but also provides quantitative references for operation and maintenance through statistical indicators (success rate, failure rate, etc.), helping to continuously optimize business processes. Simultaneously, error reports are automatically generated and stored in OSS, ensuring that the results are traceable, downloadable, and reusable, greatly improving the transparency, reliability, and maintainability of data import across application scenarios.

[0097] S9: Based on the business logic processing results, notify the unified service center to update the processing status.

[0098] In one possible implementation, S9 specifically includes sub-steps S901 to S903: S901: Business applications send business logic processing results to the unified service center through the remote procedure call interface.

[0099] S902: Update the import task status to completed, and record processing statistics and error file addresses.

[0100] S903: The unified service center will persistently store the processing results for subsequent querying.

[0101] In this embodiment of the invention, processing results are promptly fed back to the unified service center through business applications, enabling centralized status management and end-to-end tracking of cross-application import tasks. Upon task completion, the unified service center updates the processing status and persistently saves statistical information and error file addresses, ensuring results can be queried and traced at any time. This not only improves the transparency and consistency of data processing but also allows users and operations personnel to monitor task progress and results in real time, quickly locate problems, and reduce communication and troubleshooting costs in cross-system collaboration, thereby significantly improving system controllability and reliability.

[0102] In one possible implementation, the cloud storage-based cross-application Excel data import processing method further includes: S10: Provides query for processing results and download of error files.

[0103] Specifically, users can query the progress and results of Excel import processing through a unified interface. If erroneous data is found, the system provides a download link for the erroneous Excel file. Users can download the erroneous file, correct the data, and re-import.

[0104] In this embodiment of the invention, a unified interface provides functions for querying import results and downloading error files. Users can monitor the progress and results of Excel data processing in real time and directly download the system-generated error report file when errors are found. This not only significantly reduces the difficulty of problem localization and repair but also avoids repeated manual investigation and communication, improving the efficiency of data correction and secondary import. Simultaneously, the centralized query and download entry enhances system usability and user experience, ensuring that data processing across application scenarios is transparent, traceable, and easily managed in a closed loop.

[0105] This invention innovatively proposes a cross-application Excel import processing architecture that achieves "one-time integration, benefits all applications." It adopts a three-tier architecture, with a unified service center interacting with various business applications via a message queue. The unified service center provides a unified API interface, configuration management, and status tracking; the message queue layer handles intelligent message distribution, asynchronous decoupling, and retry mechanisms; and the business application layer, through SDK integration, is responsible for Excel data processing, data validation, and error collection. The three layers exchange data and synchronize status through standardized interfaces. This results in a 90% improvement in development efficiency: business applications only need to import the SDK and implement a single data processing method to obtain complete Excel import processing capabilities.

[0106] The beneficial effects of the technical solutions provided by the embodiments of the present invention include at least the following: (1) In this embodiment of the invention, each business system only needs to introduce the SDK to realize the Excel data import function, without the need for each business system to develop the Excel data import function separately, thus improving development efficiency.

[0107] (2) In this embodiment of the invention, unified configuration management is adopted. By establishing a three-dimensional configuration matrix shared across applications, the import logic of business applications is centralized and standardized. The Excel import rules, template paths, parameter restrictions and permission configurations of all business scenarios are centrally stored and maintained by a unified service center. Each business system does not need to carry out repeated development or independent maintenance configuration, which facilitates true enterprise-level cross-application collaboration and greatly improves the system's flexibility, maintainability and enterprise-level integration capabilities.

[0108] Reference manual attached Figure 2 The diagram shows a schematic representation of a cloud storage-based cross-application Excel data import and processing system provided by an embodiment of the present invention.

[0109] This invention provides a cloud storage-based cross-application Excel data import and processing system 20, comprising: a processor 201 and a memory 202; The memory 202 stores programs or instructions that can run on the processor 201. When the program or instructions are executed by the processor 201, they implement the steps of the above-described cross-application Excel data import and processing method based on cloud storage and achieve the same technical effect. To avoid repetition, the present invention will not elaborate further.

[0110] It should be understood that the processor 201 in this embodiment of the invention may be a central processing unit (CPU), or it may be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor may be a microprocessor or any conventional processor.

[0111] It should also be understood that the memory 202 in the embodiments of the present invention can be volatile memory or non-volatile memory, or may include both volatile and non-volatile memory. The non-volatile memory can be read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), or flash memory. The volatile memory can be random access memory (RAM), which is used as an external cache. By way of example, but not limitation, many forms of random access memory (RAM) are available, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate synchronous DRAM (DDR SDRAM), enhanced synchronous DRAM (ESDRAM), synchronous linked DRAM (SLDRAM), and direct rambus RAM (DR RAM).

[0112] The above embodiments can be implemented, in whole or in part, by software, hardware (such as circuits), firmware, or any other combination thereof. When implemented using software, the above embodiments can be implemented, in whole or in part, as a computer program product. The computer program product includes one or more computer instructions or computer programs. When the computer instructions or computer programs are loaded or executed on a computer, all or part of the processes or functions described in the embodiments of the present invention are generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more sets of available media. The available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium. A semiconductor medium can be a solid-state drive.

[0113] It should be understood that, in various embodiments of the present invention, the order of the above-mentioned process numbers does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0114] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this invention.

[0115] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working processes of the devices, apparatuses, and units described above can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here.

[0116] In the several embodiments provided by this invention, it should be understood that the disclosed devices, apparatuses, and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another device, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0117] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0118] In addition, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.

[0119] If the aforementioned functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, essentially, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0120] This invention provides a readable storage medium comprising: storing a program or instructions on the readable storage medium, wherein when the program or instructions are executed by a processor, the program or instructions implement the steps of the above-described cross-application Excel data import and processing method based on cloud storage, and can achieve the same technical effect. To avoid repetition, this invention will not elaborate further.

[0121] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the embodiments of the present invention, and are not intended to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention. Any changes or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in the present invention should be included within the protection scope of the present invention.

Claims

1. A method for importing and processing cross-application Excel data based on cloud storage, characterized in that, include: S1: Receives processing requests for Excel files from front-end business applications through the RESTful API interface of the unified service center; S2: In response to the processing request, generate a pre-signed URL for Alibaba Cloud OSS and return the pre-signed URL to the front end; S3: The front end uploads the Excel file directly to Alibaba Cloud OSS. When the upload is successful, Alibaba Cloud OSS returns a confirmation of successful file upload and the OSS URL to the front end. S4: Based on the system configuration records, perform Excel file configuration matching to obtain complete configuration information and verify the validity of the OSSURL; S5: Assemble messages based on the complete configuration information, and deliver eligible messages to the corresponding target business applications based on the RocketMQ Tag mechanism; S6: The target business application automatically parses the Excel file; S7: Perform data validation and business logic processing on the parsed Excel file; S8: Calculate the results of business logic processing and generate error reports; S9: Based on the business logic processing result, notify the unified service center to update the processing status.

2. The method for importing and processing cross-application Excel data based on cloud storage according to claim 1, characterized in that, S2 specifically includes: S201: Use Alibaba Cloud OSS access key and security key for identity authentication; S202: Construct request parameters including bucket name, object key name, HTTP method type, and expiration time; S203: Use the HMAC-SHA256 algorithm to perform local encryption calculations on the request parameters and generate a digital signature; S204: Add the digital signature to the URL to form a complete pre-signed URL; S205: Send the pre-signed URL to the Alibaba Cloud OSS server. The Alibaba Cloud OSS server verifies the signature parameters and returns the final pre-signed URL. The pre-signed URL allows the front-end to directly upload files to the Alibaba Cloud OSS server within a specified time. S206: When a third party accesses the pre-signed URL, the Alibaba Cloud OSS server verifies the signature parameters. If the signature parameters are tampered with or expired, access is refused.

3. The method for importing and processing cross-application Excel data based on cloud storage according to claim 1, characterized in that, S3 specifically includes: S301: When the front end obtains the pre-signed URL, it uses the HTTP PUT method to directly upload the Excel file to the Alibaba Cloud OSS; S302: The Alibaba Cloud OSS returns a confirmation of successful file upload and the OSS URL to the front end.

4. The method for importing and processing cross-application Excel data based on cloud storage according to claim 1, characterized in that, S4 specifically includes: S401: When the front end submits the Excel file import task, it enters the source, type, and OSS URL returned when the Alibaba Cloud OSS upload is successful; S402: Based on the source and type parameters passed in from the front end, search for the corresponding system configuration record in the configuration table; S403: Based on the system configuration record found, obtain the app_name and determine the target application system for processing the service; S404: Verify the integrity of the configuration, including checking the import template address, maximum line count limit, and file expiration time; S405: Verify the validity of the OSS URL to ensure that the file has been successfully uploaded to OSS and is accessible. S406: When configuration verification fails or the OSS URL is invalid, an error message is returned; when verification succeeds, complete configuration information and file address are obtained for subsequent processing.

5. The cross-application Excel data import and processing method based on cloud storage according to claim 1, characterized in that, S5 specifically includes: S501: Assemble the message content in the message queue according to the complete configuration information; the complete configuration information includes app_name, source, and type. S502: Attach the app_name as a RocketMQ tag to the message's metadata as a key identifier for message filtering; S503: Construct a complete processing message containing resource identifier, business type, OSS file address, and user information; S504: Using the unified service center as a producer, send the processed message with the application name tag to the RocketMQ Broker; S505: Stores message and tag metadata in the RocketMQ message storage system, and the Broker maintains the mapping relationship between tags and messages; S506: The SDK's built-in general message consumer subscribes to specified tags for precise consumption; S507: When each business application introduces the SDK, configure the consumer's subscription tag to the current application name to ensure that it only consumes messages that belong to itself; S508: RocketMQ's Broker uses a message filtering mechanism to accurately deliver messages that meet certain conditions to the corresponding consumer instances based on the tags subscribed by the consumers.

6. The method for importing and processing cross-application Excel data based on cloud storage according to claim 1, characterized in that, S6 specifically includes: S601: The SDK component of the target business application automatically receives processing tasks from the message queue; S602: Download the Excel file from OSS to the local temporary directory according to the OSS URL in the message; S603: Uses the Excel parsing engine to read file content and automatically identify header rows and data rows; S604: Map Excel cell data to object properties based on a predefined data object template; S605: Performs data type conversion, converting text, numbers, dates, and other formats into their corresponding data types; S606: Generates a list of objects containing all rows of data, each object representing one row of data in the Excel file.

7. The method for importing and processing cross-application Excel data based on cloud storage according to claim 1, characterized in that, Specifically, S7 includes: S701: Perform multi-level validation on a list of data objects, including mandatory field checks, data format validation, and enumeration value range checks; S702: Passes the verified data to the processor of the business application to execute specific business logic; S703: For data that fails to be validated, mark the specific error reason and error type in the object; S704: For data that fails to be processed, capture the exception information and mark it as a processing failure.

8. The method for importing and processing cross-application Excel data based on cloud storage according to claim 1, characterized in that, S8 specifically includes: S801: Initialize the statistics counter to record the total number of rows in the Excel file, the number of rows that passed data validation, the number of rows that failed data validation, the number of rows that were successfully processed by business logic, and the number of rows that failed to be processed by business logic. S802: Traverse the parsed list of data objects and perform validation on each data object; S803: Call the data processing method implemented by the business application on the data object that has passed the verification; S804: In the data processing method, the business application failed to set an error message flag for the data object. S805: Update the corresponding counter based on the error message status of the data object; S806: Create a list of error data objects to store all data records containing error information; S807: Traverse all data objects and filter out records containing error messages; S808: Add the data object that failed the verification to the error list, and mark the error type as "Verification failed"; S809: Add the data object that failed to process the business logic to the error list, and mark the error type as "processing failed"; S810: Check if the error data list is empty; if it is empty, do not generate an error report file. S811: If erroneous data exists, create a new Excel file based on the header structure of the original Excel file; S812: Add an "Error Reason" column to the existing columns to display specific error information; S813: Write a list of error data objects to a new Excel file, with each row containing the original data and the corresponding error reason; S814: Set the format of the Excel file, including header style and column width auto-adjustment; S815: The unique filename for generating error report files, which typically includes a timestamp and a business type identifier; S816: Call the OSS client to upload the error report Excel file to the specified storage bucket; S817: Obtain the access address of the uploaded file for subsequent downloading and viewing; S818: Records relevant information about the error report file, including the file name, file size, and upload time; S819: Calculate the success rate of processing, the failure rate of verification, and the failure rate of business processing; S820: Construct a processing result object containing all statistical information for subsequent state updates.

9. The method for importing and processing cross-application Excel data based on cloud storage according to claim 1, characterized in that, S9 specifically includes: S901: The business application sends the business logic processing result to the unified service center through the remote procedure call interface; S902: Update the import task status to completed, and record processing statistics and error file addresses; S903: The unified service center will persistently store the processing results for subsequent querying.

10. A cross-application Excel data import and processing system based on cloud storage, characterized in that, include: Processor and memory; The memory stores programs or instructions that can run on the processor, which, when executed by the processor, implement the steps of the cloud storage-based cross-application Excel data import and processing method as described in any one of claims 1 to 9.

Images