Report data attribute processing method and device based on custom function
By building a custom function library and attribute mapping table, and combining nested calls and real-time data binding technology, the problem of cumbersome and error-prone spreadsheet report attribute settings has been solved, achieving efficient and accurate report data processing.
Patent Information
- Application Number
- CN202511917830.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-18
- Publication Date
- 2026-03-20
AI Technical Summary
Existing spreadsheets rely on manual operation for report attribute settings, which is inefficient and prone to errors. Automated tools are difficult to maintain and lack flexibility, making it difficult to adapt to dynamic business needs.
Build a custom function library, define standardized function interfaces and parameter passing mechanisms, establish an attribute mapping table, realize nested calls between custom functions and built-in functions, and combine real-time data binding technology to automate the processing of report data attributes.
It has achieved fully automated management of report attribute information, improved compilation efficiency, ensured data accuracy and timeliness, reduced maintenance costs, and adapted to the dynamic changes of complex business logic.
Smart Images
Figure CN121706749A_ABST
Abstract
Description
Technical Field
[0001] This invention belongs to the field of data processing technology, and in particular relates to a method and apparatus for processing report data attributes based on user-defined functions. Background Technology
[0002] As enterprises accelerate their digital transformation, spreadsheet software, as a core tool for financial statement preparation, business analysis, and business data management, is seeing its application depth and breadth continuously expand. In the daily operations of enterprises, finance personnel and business analysts frequently need to prepare various periodic reports, such as monthly business analysis reports, quarterly financial reports, and annual budgets. These reports typically contain a large amount of attribute information regarding the business period (e.g., "Q1 2024", "2023") and the data reporting unit (e.g., "Group Headquarters", "Shanghai Branch", "Finance Department"). The accuracy of this information directly affects the readability of the report data and the correctness of decision-making.
[0003] In practice, setting report attributes primarily relies on manual input or copying and modifying static templates. Business personnel often need to manually enter the year, month, or unit name typically through the keyboard in the report header, footer, or specific description areas. With the surge in the number of reports and the expansion of business lines, this traditional operating model has exposed many drawbacks. First, manual input is inefficient and prone to human error. For example, when reusing the previous month's report template, operators are likely to overlook updates to the period or unit, leading to discrepancies between report attributes and actual data, resulting in serious "data misinterpretation." Second, the existing attribute setting methods lack unified standards and management. Different personnel may use inconsistent abbreviations or full names for the same unit, leading to a chaotic report format after aggregation and increasing the difficulty of subsequent data cleaning.
[0004] To address these issues, existing technologies attempt to automate certain processes using built-in text concatenation formulas or Visual Basic for Applications (VBA) scripts within spreadsheets. However, these methods have significant technical limitations. While built-in formulas can handle simple string concatenation, they often become extremely verbose, difficult to read, and hard to maintain when dealing with complex business logic mappings (such as mapping "Period 1" to a specific date range based on fiscal year rules). While VBA scripts are powerful, their development and debugging are challenging, making them difficult for ordinary business personnel to master. Furthermore, scripts are typically bound to specific files, hindering logic reuse and unified updates across the enterprise. In addition, in widely used online collaborative spreadsheet systems, script execution is often strictly limited for security and cross-platform compatibility reasons, making it difficult to promote script-based solutions.
[0005] In summary, existing spreadsheet data processing technologies suffer from low report generation efficiency, frequent attribute errors, and difficulty in adapting to dynamically changing business needs due to over-reliance on manual operation, difficulties in maintaining automated tools, and a lack of flexible mapping mechanisms in report attribute management. Therefore, there is an urgent need for a technical solution that can intelligently, dynamically, and with low barriers to entry process report data attributes to decouple business logic from presentation format and improve the intelligence level of reporting systems. Summary of the Invention
[0006] This invention provides a method and apparatus for processing report data attributes based on user-defined functions, which solves the problems of cumbersome and error-prone report attribute settings, rigid logic lacking flexibility, and high maintenance costs in the prior art.
[0007] In a first aspect, embodiments of the present invention provide a method for processing report data attributes based on a user-defined function. This method is applied to a spreadsheet data processing system and includes the following steps: Build a custom function library specifically for retrieving report data attributes. The custom function library includes functions for retrieving period and functions for retrieving unit. By defining standardized function interfaces, parameter passing mechanisms and return value formats, a standardized framework is provided for function calls. The business option processing module receives the business options selected by the user in the worksheet of the spreadsheet. The business options include business period options or data entry unit options. The business options are used as input parameters of the custom function for verification to ensure that only selections that meet the preset business rules trigger the subsequent process. Establish an attribute mapping table between business options and data attributes. The attribute mapping table stores the dynamic correspondence between different business options and their corresponding standard attribute values. Based on the business options selected by the user and the function return type, query the attribute mapping table to automatically find and convert the corresponding standard attribute values. The module enables nested calls between custom functions and built-in spreadsheet functions. It parses nested function expressions, executes each function in a predetermined order, and passes intermediate results to achieve complex logical processing of standard attribute values. The results display and update module displays the acquired and processed data attribute values in real time in designated cells of the spreadsheet, establishing a data binding mechanism that automatically refreshes the content displayed in the designated cells when data changes or user operations are detected.
[0008] Preferably, the step of constructing a custom function library specifically for retrieving report data attributes includes: When the spreadsheet data processing system starts, the custom function library is loaded into memory, and a function registry is created to record the metadata and execution entry point of each custom function. The metadata includes at least the function name, parameter list and return value type. The function name is used to uniquely identify a custom function in a spreadsheet, the parameter list contains necessary business options information, and the return value type determines the final display format of the data in the cell; By traversing each function object in the custom function library and calling the registered function interface, the static function definition is converted into a callable object that the spreadsheet data processing system can recognize, thereby completing the function library initialization process. This allows users to directly call the function to retrieve time and unit functions in the spreadsheet's formula editing bar.
[0009] Preferably, the step of receiving the business options selected by the user in the worksheet of the spreadsheet through the business options processing module specifically includes: Real-time monitoring of user operation events in spreadsheets; when a user selects business options in the worksheet, the option parser is activated. The option parser converts user selection events in the graphical user interface into standardized function call parameters, which include the specific business option value selected by the user and the option type identifier. The subsequent processing path is determined based on the option type identifier. If the option type identifier indicates a period type, the period acquisition function is called for processing. If the option type identifier indicates a unit type, the unit acquisition function is called for processing. Perform integrity and validity checks on the business option values to verify whether they are within the preset range allowed by the spreadsheet data processing system. If the verification fails, return an exception message through the error handling mechanism. If the verification passes, pass the valid business option values to the attribute mapping and conversion module.
[0010] Preferably, the step of establishing an attribute mapping table between business options and data attributes specifically includes: Construct a two-dimensional data structure mapping table as an attribute mapping relationship table, where the row key is defined as the user's preset business options and the column key is defined as the function's return type; The business options cover time dimensions such as year, quarter, and month, as well as different reporting unit dimensions. The return types cover display formats such as standard date format, short identifier, and full name description. Maintain the data structure of the attribute mapping table, and support dynamic configuration and updating of key-value pairs in the mapping table through configuration files or management interface to achieve separation of business rules and display logic; When performing attribute mapping, the verified business option value is used as the first index, and the return value type requirement of the currently called custom function is used as the second index. A joint query is performed in the attribute mapping relationship table to locate the corresponding standard attribute value and directly extract it as the mapping result.
[0011] Preferably, the steps for implementing nested calls between custom functions and built-in spreadsheet functions through nested function calls to the execution module specifically include: Construct a function call stack and context management mechanism to support multi-level nested function execution environments; When a nested function expression string containing a custom function is received, the function parsing engine is activated to perform syntax parsing on the nested function expression string and identify the main function and the nested sub-functions that are used as parameters. Create an independent function execution context for each function call. The function execution context contains the variable space and parameter space of the current function and is used to isolate data in different function execution processes. The parsed function sequence is executed sequentially from the inside out. First, the innermost custom function is executed, and the standard attribute value obtained by the innermost custom function through the attribute mapping table is obtained. The standard attribute value is then passed as an intermediate result to the outer function. After receiving the intermediate results, the outer function combines its own logic to further format, calculate, or conditionally judge the data until the outermost function finishes execution, generating the final complex data processing result.
[0012] Preferably, the step of displaying the acquired and processed data attribute values in real time in a designated cell of a spreadsheet through the result display and update module specifically includes: Create a cell rendering engine, which is responsible for receiving the final complex data processing results and converting the data into visual text or graphic elements according to preset display formatting rules; Define the target cell reference to precisely locate and write the visual text or graphic elements into the target cell of the spreadsheet; Establish a data binding mechanism to dynamically link the displayed content of the target cell with the execution result of the custom function; The event listening mechanism is activated to monitor data change events or user interaction events in the spreadsheet in real time. Once the source data used as the function input changes or the user reselects a business option, the recalculation process of the custom function is immediately triggered. The display content in the target cell is automatically refreshed through the result display and update module to ensure the real-time performance and accuracy of the report attribute information.
[0013] Preferably, the method further includes an update mechanism based on user feedback, specifically including: Collect user feedback data on the accuracy of attribute acquisition and display effect during actual use; Based on the feedback data, the attribute mapping table was optimized and adjusted, and the correspondence between business options and standard attribute values was updated. Alternatively, based on new business needs, new custom functions can be added to the custom function library, defining new function names and parameter lists. These custom functions can then be registered with the spreadsheet data processing system by building a custom function library specifically for retrieving report data attributes, thereby enabling the functional iteration and adaptive enhancement of the spreadsheet data processing system.
[0014] Preferably, the nested function call execution module also includes an error handling and notification mechanism during execution: If a syntax error, parameter type mismatch, or mapping lookup failure is encountered during the parsing or execution of a nested function expression, the current execution flow will be immediately interrupted. Capture exception information and generate user-understandable error messages based on the exception type; Error messages are fed back to the spreadsheet interface through the results display and update module, or recorded in the spreadsheet data processing system log for troubleshooting purposes, preventing the entire spreadsheet data processing system from crashing due to an error in a single link.
[0015] Preferably, the method supports processing multiple types of business options: When the business option is a business period, the period retrieval function in the custom function library supports retrieving standard time formats for years, quarters, and months, and supports converting the period into a specific date string by nested calls to date formatting functions; When the business option is "data entry unit", the unit retrieval function in the custom function library supports retrieving the full name, abbreviation or code of the data entry unit, and supports generating a report title or explanatory text containing data entry unit information by nesting text processing functions; The attribute mapping relationship table maintains multiple sets of mapping rules for different business scenarios. The spreadsheet data processing system automatically selects the applicable subset of mapping rules for querying based on the current report type.
[0016] Secondly, embodiments of the present invention provide a report data attribute processing device based on a user-defined function. The device is applied to a spreadsheet data processing system and includes: The custom function building module is used to build a custom function library specifically for retrieving report data attributes. The custom function library includes period retrieval functions and unit retrieval functions, and defines standardized function interfaces, parameter passing mechanisms and return value formats. It also creates a function registry to record function metadata and execution entry points. The business option processing module is used to receive and verify the business options selected by the user in the worksheet of the spreadsheet. The business options include business period options or data entry unit options. The business option processing module contains an option parser, which is used to convert the user's selection into standardized parameters and perform validity checks. The attribute mapping and transformation module is used to establish and maintain an attribute mapping relationship table between business options and data attributes. The attribute mapping relationship table stores the dynamic correspondence between different business options and their corresponding standard attribute values, and queries the attribute mapping relationship table to obtain the standard attribute values based on the input business options and return types. The nested function call execution module is used to implement nested calls between custom functions and built-in spreadsheet functions. The nested function call execution module includes a function parsing engine, a call stack, and a context management mechanism, which is responsible for parsing nested function expressions, managing variable space, and passing intermediate execution results. The results display and update module is used to display the acquired and processed data attribute values in a specified cell of a spreadsheet in real time. The results display and update module includes a cell rendering engine and an event listening mechanism, and achieves real-time automatic updates of the displayed content as the data changes through data binding.
[0017] Beneficial effects: This invention provides a report data attribute processing method based on custom functions. By constructing a standardized custom function library and establishing a dynamic mapping mechanism between business options and data attributes, combined with function nesting and real-time data binding technology, it achieves fully automated and intelligent management of report attribute information throughout the entire process. This effectively overcomes the limitations of existing technologies, such as low efficiency, susceptibility to errors, difficulty in script maintenance, and strong coupling between business logic and display format due to reliance on manual input. Specifically, by constructing a custom function library containing modules for obtaining period and unit, and establishing an attribute mapping relationship table, the system automatically queries the mapping table for conversion based on the user-selected business options and function return types. This enables centralized management and dynamic configuration of business rules. When business rules change, only the mapping table needs to be updated without modifying thousands of report files, significantly improving maintainability. Combined with result display and updates based on data binding and event listening, it can monitor data changes in real time and automatically refresh the displayed content, ensuring that report attribute information is always synchronized with the business status. This avoids information misleading due to data update lag, thereby significantly improving the timeliness and accuracy of report data and ensuring that the system maintains efficient and stable operation when processing large-scale, multi-dimensional, and complex reports. Attached Figure Description
[0018] Various other advantages and benefits will become apparent to those skilled in the art upon reading the following detailed description of preferred embodiments. The accompanying drawings are for illustrative purposes only and are not intended to limit the invention. Furthermore, the same reference numerals denote the same parts throughout the drawings. In the drawings: Figure 1 This is a schematic diagram of the electronic device structure of the hardware operating environment involved in the embodiments of the present invention; Figure 2 This is a flowchart of the steps of a report data attribute processing method based on a custom function provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the functional modules of a report data attribute processing device based on a custom function provided in an embodiment of the present invention. Detailed Implementation
[0019] To more clearly illustrate the technical solutions in the embodiments of the invention or the prior art, the invention will be briefly introduced below in conjunction with the accompanying drawings and descriptions of the embodiments or the prior art. Obviously, the following description of the structure of the drawings is merely some embodiments of the invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. It should be noted that the description of these embodiments is for the purpose of aiding understanding the invention, but does not constitute a limitation on the invention. The solution of the present invention will be further described below in conjunction with the accompanying drawings.
[0020] Reference Figure 1 , Figure 1 This is a schematic diagram of the electronic device structure of the hardware operating environment involved in the embodiments of the present invention.
[0021] like Figure 1 As shown, the electronic device may include: a processor 1001, such as a central processing unit (CPU), a communication bus 1002, a user interface 1003, a network interface 1004, and a memory 1005. The communication bus 1002 is used to enable communication between these components. The user interface 1003 may include a display screen or an input unit such as a keyboard; optionally, the user interface 1003 may also include a standard wired interface or a wireless interface. The network interface 1004 may optionally include a standard wired interface or a wireless interface (Wi-Fi interface). The memory 1005 may be a high-speed random access memory (RAM) or a stable non-volatile memory (NVM), such as a disk drive. Optionally, the memory 1005 may also be a storage device independent of the aforementioned processor 1001.
[0022] Those skilled in the art will understand that Figure 1 The structure shown does not constitute a limitation on the electronic device and may include more or fewer components than shown, or combine certain components, or have different component arrangements.
[0023] like Figure 1 As shown, the memory 1005, serving as a storage medium, may include an operating device, a data storage module, a network communication module, a user interface module, and an electronic program. In the illustrated electronic device, the network interface 1004 is primarily used for data communication with a network server; the user interface 1003 is primarily used for data interaction with the user; the processor 1001 and memory 1005 in the electronic device of this invention can be disposed within the electronic device. The electronic device uses the processor 1001 to call the report data attribute processing device based on a custom function stored in the memory 1005, and executes the report data attribute processing method based on a custom function provided in this embodiment of the invention.
[0024] Reference Figure 2This invention provides a method for processing report data attributes based on custom functions, applicable to a spreadsheet data processing system. This system can be built on a browser-based online collaborative office platform or integrated into locally installed desktop spreadsheet software. Its core lies in introducing a "functional" attribute acquisition mechanism to replace traditional static text input, thereby achieving dynamic perception and automatic rendering of report context information. The method may include the following steps S101 to S105: S101. Construct a custom function library specifically for obtaining report data attributes. The custom function library includes period retrieval functions and unit retrieval functions. By defining standardized function interfaces, parameter passing mechanisms, and return value formats, a standardized framework is provided for function calls.
[0025] In traditional spreadsheet development, extended functionality typically exists in the form of plugins or macros, lacking a unified standard. This embodiment first builds a lightweight custom function library (CFL) at the system's underlying layer.
[0026] S1011. Loading and Registration of Custom Function Libraries: During system startup (e.g., when a user opens a spreadsheet file or during the initialization phase of loading a web page), the bootloader loads the custom function library into memory 1005. The loading process includes reading the compiled function code file (such as JavaScript, Python compiled bytecode, or DLL dynamic link library) and performing integrity verification.
[0027] Next, a global function registry is created. This registry is an efficient key-value pair storage structure used to record the metadata and execution entry point pointer for each custom function. Metadata is data describing the characteristics of a function, and includes at least: The function name is a unique string identifier that identifies the custom function in the spreadsheet formula bar. For example, a function to retrieve a period might be named GET_PERIOD, and a function to retrieve units might be named GET_ORG. To avoid conflicts with built-in spreadsheet functions (such as SUM and AVERAGE), a specific namespace prefix is usually used, such as RPT.GET_PERIOD. The parameter list details the input parameters the function accepts. This includes the parameter name, data type (String, Number, Boolean), whether it is required, and its default value. For example, the GET_PERIOD function might include two parameters: type (period type) and format (return format). The return value type defines the data type returned to the cell after the function executes, such as text, number, or date object. This determines the final default display format of the data in the cell.
[0028] S1012. Conversion of static definitions to callable objects: By traversing each function object in the custom function library and calling the underlying registered function interface (such as Excel's Add-In interface or Web SpreadJS's addCustomFunction interface), the static function code definition is converted into a callable object that can be recognized by the spreadsheet calculation engine (CalculationEngine).
[0029] This process completes the initialization of the function library. At this point, the custom function is no longer an isolated code snippet, but becomes a link in the spreadsheet's calculation chain. When the user enters "=" and type the function name in the spreadsheet's formula bar, the registry can be read through reflection, providing IntelliSense and auto-completion features. This allows the user to directly call functions obtained during and after the function is called, just like using built-in functions.
[0030] S102. The business option processing module receives the business options selected by the user in the worksheet of the spreadsheet. The business options include business period options or data reporting unit options. The business options are used as input parameters of the custom function for verification to ensure that only selections that meet the preset business rules trigger the subsequent process.
[0031] The business options processing module acts as a bridge between the user interface (UI) and the underlying computing logic.
[0032] S1021. Real-time monitoring and parsing of user operation events: An event listener is deployed on the front end to monitor user operation events in the spreadsheet in real time. When a user selects a business option in the worksheet (for example, the user switches the report year in a drop-down list control or changes the reporting unit in the sidebar configuration panel), the option parser is immediately started.
[0033] The option parser's role is to convert unstructured selection events (such as click events and value change events) generated by the user in the graphical user interface (GUI) into standardized function call parameters. The generated parameter object contains two key fields: The specific business option value represents the content actually selected by the user, such as "2025", "Q3", or "XXX Branch". The option type identifier is used to distinguish which business attribute the option belongs to, such as "PERIOD" (period) or "ORG" (unit).
[0034] S1022. Processing path distribution: Based on the parsed option type identifier, the module determines the subsequent processing path.
[0035] If the option type identifier indicates a period type, the route is routed to the period retrieval function for processing. This module is responsible for handling time-related logic, such as fiscal year offsets and quarter start and end date calculations.
[0036] If the option type identifier indicates a unit type, the process is routed to the unit retrieval function. This module is responsible for handling logic related to organizational structure, such as organization name normalization, organizational hierarchy resolution, and organization code matching.
[0037] S1023. Parameter integrity and validity checks: Before passing parameters to the calculation engine, rigorous defensive programming checks must be performed. Business option values are checked for integrity and validity to verify that they are within the allowed preset range (Allowlist).
[0038] For example, for the month option, verify that the input value is an integer between 1 and 12; for the unit option, verify that it exists in the maintained dictionary of standard units.
[0039] If validation fails (e.g., the user entered "13th month" illegally), an error message is returned through the error handling mechanism (such as throwing a #VALUE! error or popping up a warning box) to prevent illegal data from polluting the report. If validation succeeds, the valid business option value is passed to the subsequent attribute mapping and conversion module.
[0040] S103. Establish an attribute mapping relationship table between business options and data attributes. The attribute mapping relationship table stores the dynamic correspondence between different business options and corresponding standard attribute values. Based on the business option selected by the user and the function return type, query the attribute mapping relationship table to automatically find and convert the corresponding standard attribute value.
[0041] The attribute mapping table is the core component for decoupling business logic in this embodiment.
[0042] S1031. Construction of a two-dimensional mapping table. Preferably, a two-dimensional data structure mapping table is constructed.
[0043] Row Key: Defined as a user-preset business option. It covers time dimension options such as year, quarter, and month (e.g., Key_2025, Key_Q1) and different reporting unit dimension options (e.g., Key_Org_SH).
[0044] Column Key: Defined as the return type or display attribute of a function. It covers standard date format (Standard_Date), short label (Short_Label), full description (Full_Description), start date (Start_Date), end date (End_Date), etc.
[0045] For example, for the row key Key_Org_SH, the value corresponding to the column key Short_Label is "Shanghai Branch", the value corresponding to the column key Full_Description is "China XX Group Shanghai Branch", and the value corresponding to the column key Org_Code is "021".
[0046] S1032. Dynamic maintenance of the mapping table: Maintain the data structure of this attribute mapping table, supporting dynamic configuration and updates of key-value pairs in the mapping table via configuration files (such as JSON, XML) or the administrator backend interface. This means that when a company's business rules change (for example, a company stipulates that the fiscal year starts on April 1st instead of January 1st), the administrator only needs to modify the date definition for "Q1" in the mapping table, without modifying any code or redeploying the software. This achieves a complete separation of business rules and presentation logic.
[0047] S1033. Joint query positioning: During attribute mapping processing, a high-efficiency table lookup operation is performed. Using the verified business option value as the first index (row positioning) and the return type requirement of the currently called custom function as the second index (column positioning), a joint query is performed in the attribute mapping table to directly extract the corresponding standard attribute value as the mapping result.
[0048] Let the query function be The mapping table is The operation can then be represented as This mechanism ensures extremely low time complexity for queries, typically O(n log n). .
[0049] S104. The module implements nested calls between custom functions and built-in spreadsheet functions by using nested function calls. It parses nested function expressions, executes each function in a predetermined order, and passes intermediate results to achieve complex logical processing of standard attribute values.
[0050] This step is key to achieving high flexibility, allowing users to combine custom functions and spreadsheet's powerful built-in functions (such as TEXT, CONCATENATE, IF, VLOOKUP, etc.) like building blocks.
[0051] S1041. Call Stack and Context Construction: To support complex formula calculations, a function call stack and context management mechanism are constructed. The call stack records the hierarchical relationship of function calls, ensuring that child functions execute before their parent functions. The context management mechanism maintains the scope of variables and prevents naming conflicts.
[0052] S1042. Parsing of Nested Expressions: When a nested function expression string containing a user-defined function is received (e.g., ="Deadline:"&TEXT(GET_PERIOD("EndDate"), "yyyy-mm-dd")), the parser engine is activated. The parser engine parses the string into an Abstract Syntax Tree (AST) through lexical and syntactic analysis. By traversing the AST, the main function (the outermost concatenation operator &) and the nested sub-functions (TEXT and GET_PERIOD) passed as parameters are identified.
[0053] S1043. Execution context isolation: An independent execution context is created for each function call. This context contains the current function's variable space and argument space, used to isolate data from different function execution processes. For example, the internal variables of the GET_PERIOD function will not leak and affect the external TEXT function.
[0054] S1044. Execution Sequence from Inside to Outside: The execution engine executes the parsed function sequence in a bottom-up order: Innermost execution: First, the innermost custom function GET_PERIOD("EndDate") is executed. This function queries the mapping table through the steps mentioned in S103 and returns a standard attribute value (e.g., the date object 2025-03-31). Intermediate result passing: This standard attribute value is passed as an intermediate result to the outer function TEXT. At this point, the formula context is equivalent to TEXT(2025-03-31, "yyyy-mm-dd"). Outer logic processing: After receiving the intermediate result, the outer function processes the data according to its own logic. The TEXT function formats the date object into the string "2025-03-31". Final result generation: The processed result continues to be passed upwards, and finally concatenates with the string "Deadline:" to generate the final complex data processing result "Deadline: 2025-03-31".
[0055] S1045. Error Handling and Notification Mechanism: During execution, the module has a built-in error handling mechanism. If a syntax error (such as mismatched parentheses), parameter type mismatch (such as passing text to a function that requires numbers), or mapping lookup failure (such as requesting a non-existent period type) is encountered during the parsing or execution of nested function expressions, the current execution flow will be immediately interrupted.
[0056] The exception capture module captures exception information and generates user-understandable error messages (such as #PARAM! or #MAP_ERR!) based on the exception type. This information is then fed back to the interface through the results display module or logged for troubleshooting, preventing the entire report calculation from crashing due to an error in a single step.
[0057] S105. The obtained and processed data attribute values are displayed in real time in the specified cells of the spreadsheet through the result display and update module. A data binding mechanism is established to automatically refresh the display content in the specified cells when data changes or user operations are detected.
[0058] S1051. Rendering and Positioning: Preferably, a Cell Rendering Engine is created. This engine is responsible for receiving the final complex data processing results and converting the data into visual text or graphic elements according to preset display formatting rules (such as font, color, and background color). The target cell reference (e.g., Sheet1!A1) is determined, and the rendered content is precisely positioned and written to the target cell of the spreadsheet.
[0059] S1052. Data Binding and Dynamic Association: To achieve a dynamic effect where "a single change affects the whole system," a data binding mechanism is established. The content displayed in the target cell is dynamically associated with the execution result of a custom function. In the dependency graph, a dependency edge is created from the "business option control" to the "target cell."
[0060] S1053, Event Listening and Auto Refresh: Activate the event listening mechanism to monitor data change events or user interaction events in the spreadsheet in real time. Once a change is detected in the source data used as function input (e.g., the user modifies the value of a referenced cell) or the user reselects a business option (e.g., switching years in a dropdown list), a dirty check or dependency notification is immediately triggered.
[0061] Based on dependencies, the recalculation process of the custom function is immediately triggered. The entire process from S102 to S104 is executed again, generating new results. The results display and update module automatically refreshes the content displayed in the target cells, ensuring that the report attribute information is always real-time and accurate.
[0062] S106. Iteration and extension (update mechanism based on user feedback): The method in this embodiment also includes an update mechanism based on user feedback.
[0063] Feedback collection includes a built-in feedback channel to collect user feedback on the accuracy of attribute acquisition and display during actual use. For example, a user might report that the full name of the organization, "Shanghai XXX Branch," is too long in a specific report and requests the use of an abbreviation. Mapping optimization involves administrators optimizing and adjusting the attribute mapping table based on feedback data, updating the correspondence between business options and standard attribute values (such as adjusting precision configuration). Functionality expansion allows developers to extend the custom function library with new custom functions based on new business requirements, defining new function names and parameter lists, and registering them through the initialization steps described in S101. This enables feature iteration and adaptive enhancement.
[0064] S107. Specific scenarios for handling multiple types of business options: This method supports handling multiple types of business options. Specific scenarios are listed below: For business period processing, when the business option is "Business Period", the period retrieval function in the custom function library supports retrieving standard time formats for year, quarter, and month. It also supports nested calls to date formatting functions such as DATE, YEAR, and MONTH to convert the period into a specific date string. For example, "2025-Q1" can be converted to "January to March 2025". For reporting unit processing, when the business option is "Data Reporting Unit", the unit retrieval function in the custom function library supports retrieving the unit's full name (e.g., "XXX Group Shanghai Branch"), abbreviation (e.g., "Shanghai Branch"), or organization code (e.g., "ORG_001"). It also supports nested calls to text processing functions (e.g., CONCATENATE) to generate standardized report titles. For example, if the unit is selected as "Shanghai XXX Branch", the formula "Report Body: XXX Group Shanghai Branch" can be automatically generated using the formula ="Report Body: "&GET_ORG_NAME("Full"). Multiple sets of rules are maintained, and the attribute mapping relationship table maintains multiple sets of mapping rules for different business scenarios (such as internal management reports and external audit reports). Based on the current report type context, the system automatically selects the applicable subset of mapping rules for querying, ensuring that the same business option presents attribute values that conform to the scenario specifications in different reports.
[0065] As can be seen from the detailed description of the above embodiments, the present invention reconstructs the processing flow of report attributes using functions, which not only realizes automated data acquisition and display, but also endows the report spreadsheet data processing system with extremely high flexibility and maintainability through nested calls and dynamic mapping mechanisms.
[0066] This invention also provides a report data attribute processing device based on a user-defined function, referring to... Figure 3 The diagram illustrates a functional block diagram of a report data attribute processing device 300 based on a user-defined function according to the present invention. The device may include the following modules: The custom function building module 301 is used to build a custom function library specifically for obtaining report data attributes. The custom function library includes period retrieval functions and unit retrieval functions, and defines standardized function interfaces, parameter passing mechanisms and return value formats. It also creates a function registry to record function metadata and execution entry points. The business option processing module 302 is used to receive and verify the business options selected by the user in the worksheet of the spreadsheet. The business options include business period options or data entry unit options. The business option processing module includes an option parser, which is used to convert the user's selection into standardized parameters and perform validity checks. The attribute mapping and conversion module 303 is used to establish and maintain an attribute mapping relationship table between business options and data attributes. The attribute mapping relationship table stores the dynamic correspondence between different business options and corresponding standard attribute values, and queries the attribute mapping relationship table to obtain standard attribute values according to the input business options and return types. The nested function call execution module 304 is used to implement nested calls between custom functions and built-in spreadsheet functions. The nested function call execution module includes a function parsing engine, a call stack, and a context management mechanism, and is responsible for parsing nested function expressions, managing variable space, and passing intermediate execution results. The results display and update module 305 is used to display the acquired and processed data attribute values in a specified cell of a spreadsheet in real time. The results display and update module includes a cell rendering engine and an event listening mechanism, and achieves real-time automatic updates of the displayed content as the data changes through data binding.
[0067] Based on the same inventive concept, another embodiment of the present invention provides an electronic device, including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus. Memory, used to store computer programs; When a processor executes a program stored in memory, it implements the report data attribute processing method based on a user-defined function according to the present invention.
[0068] The communication bus mentioned above can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This communication bus can be divided into address bus, data bus, control bus, etc. For ease of illustration, only one thick line is used in the diagram, but this does not indicate that there is only one bus or one type of bus. The communication interface is used for communication between the aforementioned terminal and other devices. The memory can include Random Access Memory (RAM), or non-volatile memory, such as at least one disk storage device. Optionally, the memory can also be at least one storage device located remotely from the aforementioned processor.
[0069] The processors mentioned above can be general-purpose processors, including central processing units (CPUs), network processors (NPs), etc.; they can also be 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, or discrete hardware components.
[0070] Furthermore, to achieve the above objectives, embodiments of the present invention also propose a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the report data attribute processing method based on a user-defined function according to embodiments of the present invention.
[0071] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, apparatus, or computer program products. Therefore, embodiments of the present invention can take the form of entirely hardware embodiments, entirely software embodiments, or embodiments combining software and hardware aspects. Furthermore, embodiments of the present invention can take the form of computer program products implemented on one or more computer-usable hardware devices (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0072] The embodiments of the present invention are described with reference to flowchart illustrations and / or block diagrams of methods, terminal devices (apparatus), and computer program products according to embodiments of the invention. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations 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, special-purpose computer, embedded processor, or other programmable data processing terminal device to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing terminal device, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0073] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing terminal device to operate in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0074] These computer program instructions can also be loaded onto a computer or other programmable data processing terminal equipment, causing a series of operational steps to be performed on the computer or other programmable terminal equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable terminal equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0075] Finally, it should be noted that in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. "And / or" indicates that either one or both can be chosen. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or terminal device that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or terminal device. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or terminal device that includes the element.
[0076] The above are merely specific embodiments of the present invention, but the scope of protection of the present invention is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in the present invention, and these modifications or substitutions should all be covered within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.
Claims
1. A method for processing report data attributes based on user-defined functions, characterized in that, The method is applied to a spreadsheet data processing system and includes the following steps: A custom function library is built specifically for obtaining report data attributes. The custom function library includes period retrieval functions and unit retrieval functions. By defining standardized function interfaces, parameter passing mechanisms and return value formats, a standardized framework is provided for function calls. The business option processing module receives the business options selected by the user in the worksheet of the spreadsheet. The business options include business period options or data reporting unit options. The business options are used as input parameters of a custom function for verification to ensure that only selections that meet the preset business rules trigger the subsequent process. Establish an attribute mapping table between business options and data attributes. The attribute mapping table stores the dynamic correspondence between different business options and corresponding standard attribute values. Based on the business option selected by the user and the function return type, query the attribute mapping table to automatically find and convert the corresponding standard attribute value. The module implements nested calls between the custom function and the built-in spreadsheet function by using a nested function call execution module. It parses the nested function expressions, executes each function in a predetermined order, and passes intermediate results to achieve complex logical processing of the standard attribute values. The system also displays the acquired and processed data attribute values in real time in designated cells of a spreadsheet through a results display and update module, establishing a data binding mechanism that automatically refreshes the content displayed in the designated cells when data changes or user operations are detected.
2. The report data attribute processing method based on a user-defined function according to claim 1, characterized in that, The step of building a custom function library specifically for obtaining report data attributes specifically includes: When the spreadsheet data processing system starts, the custom function library is loaded into memory, and a function registry is created to record the metadata and execution entry point of each custom function. The metadata includes at least the function name, parameter list and return value type. The function name is used to uniquely identify the custom function in the spreadsheet, the parameter list contains necessary business option information, and the return value type determines the final display format of the data in the cell; By traversing each function object in the custom function library and calling the registered function interface, the static function definition is converted into a callable object that the spreadsheet data processing system can recognize, thereby completing the function library initialization process. This allows users to directly call the period retrieval function and the unit retrieval function in the spreadsheet's formula editing bar.
3. The report data attribute processing method based on a user-defined function according to claim 2, characterized in that, The step of receiving the business options selected by the user in the worksheet of the spreadsheet through the business option processing module specifically includes: Real-time monitoring of user operation events in spreadsheets; when a user selects business options in the worksheet, the option parser is activated. The option parser converts the selection events generated by the user in the graphical user interface into standardized function call parameters, which include the specific business option value selected by the user and the option type identifier. The subsequent processing path is determined based on the option type identifier. If the option type identifier indicates a period type, the period acquisition function is called for processing. If the option type identifier indicates a unit type, the unit acquisition function is called for processing. The integrity and validity of the business option values are checked to verify whether they are within the preset range allowed by the spreadsheet data processing system. If the verification fails, an error message is returned through the error handling mechanism. If the verification passes, the valid business option values are passed to the attribute mapping and conversion module.
4. The report data attribute processing method based on a user-defined function according to claim 3, characterized in that, The step of establishing the attribute mapping relationship table between business options and data attributes specifically includes: Construct a two-dimensional data structure mapping table as the attribute mapping relationship table, where the row key is defined as the user-preset business options and the column key is defined as the function return type; The business options cover time dimension options such as year, quarter, and month, as well as different reporting unit dimension options. The return types cover display formats such as standard date format, short identifier, and full name description. Maintain the data structure of the attribute mapping table, and support dynamic configuration and updating of key-value pairs in the mapping table through configuration files or management interfaces to achieve separation of business rules and display logic; When performing attribute mapping, the verified business option value is used as the first index, and the return value type requirement of the currently called custom function is used as the second index. A joint query is performed in the attribute mapping relationship table to locate the corresponding standard attribute value and directly extract it as the mapping result.
5. The report data attribute processing method based on a user-defined function according to claim 4, characterized in that, The steps for implementing nested calls between the custom function and the spreadsheet's built-in functions through the function nesting call execution module specifically include: Construct a function call stack and context management mechanism to support multi-level nested function execution environments; When a nested function expression string containing a custom function is received, the function parsing engine is activated to perform syntax parsing on the nested function expression string and identify the main function and the nested sub-functions that are used as parameters. An independent function execution context is created for each function call. The function execution context contains the variable space and parameter space of the current function and is used to isolate data in different function execution processes. The parsed function sequence is executed sequentially from the inside out. First, the innermost custom function is executed, and the standard attribute value obtained by the innermost custom function through the attribute mapping table is obtained. The standard attribute value is then passed to the outer function as an intermediate result. After receiving the intermediate results, the outer function performs further formatting, calculation, or conditional judgment on the data based on its own logic until the outermost function finishes execution, generating the final complex data processing result.
6. The report data attribute processing method based on a user-defined function according to claim 5, characterized in that, The step of displaying the acquired and processed data attribute values in real time in a specified cell of a spreadsheet through the result display and update module specifically includes: Create a cell rendering engine, which is responsible for receiving the final complex data processing results and converting the data into visual text or graphic elements according to preset display formatting rules; Determine the target cell reference, accurately locate the visualized text or graphic element, and write it into the target cell of the spreadsheet; Establish a data binding mechanism to dynamically associate the displayed content of the target cell with the execution result of the custom function; An event listening mechanism is activated to monitor data change events or user interaction events in the spreadsheet in real time. Once a change is detected in the source data used as function input or the user reselects a business option, the recalculation process of the custom function is immediately triggered. The display content in the target cell is automatically refreshed through the result display and update module to ensure the real-time performance and accuracy of the report attribute information.
7. The report data attribute processing method based on a user-defined function according to claim 1, characterized in that, The method also includes an update mechanism based on user feedback, specifically including: Collect user feedback data on the accuracy of attribute acquisition and display effect during actual use; The attribute mapping table is optimized and adjusted based on the feedback data, and the correspondence between the business options and the standard attribute values is updated. Alternatively, based on new business needs, new custom functions can be extended into the custom function library, defining new function names and parameter lists. These new functions can then be registered into the spreadsheet data processing system through the step of building a custom function library specifically for obtaining report data attributes, thereby enabling functional iteration and adaptive enhancement of the spreadsheet data processing system.
8. The report data attribute processing method based on a user-defined function according to claim 5, characterized in that, The nested function call execution module also includes an error handling and notification mechanism during execution: If a syntax error, parameter type mismatch, or mapping lookup failure is encountered during the parsing or execution of the nested function expression, the current execution flow will be immediately interrupted. Capture exception information and generate user-understandable error messages based on the exception type; The error message is fed back to the spreadsheet interface through the result display and update module, or recorded in the log of the spreadsheet data processing system for troubleshooting purposes, to prevent the entire spreadsheet data processing system from crashing due to an error in a single link.
9. The report data attribute processing method based on a user-defined function according to claim 1, characterized in that, The method supports processing multiple types of business options: When the business option is a business period, the period retrieval function in the custom function library supports retrieving standard time formats for years, quarters, and months, and supports converting the period into a specific date string by nested calls to date formatting functions; When the business option is data entry unit, the unit retrieval function in the custom function library supports retrieving the full name, abbreviation, or code of the data entry unit, and supports generating a report title or explanatory text containing the data entry unit information by nested calls to text processing functions. The attribute mapping relationship table maintains multiple sets of mapping rules for different business scenarios, and the spreadsheet data processing system automatically selects the applicable subset of mapping rules for querying based on the current report type.
10. A report data attribute processing device based on a user-defined function, characterized in that, The device is used in a spreadsheet data processing system and includes: A custom function building module is used to build a custom function library specifically for obtaining report data attributes. The custom function library includes period retrieval functions and unit retrieval functions, and defines standardized function interfaces, parameter passing mechanisms and return value formats. A function registry is created to record function metadata and execution entry points. The business option processing module is used to receive and verify the business options selected by the user in the worksheet of the spreadsheet. The business options include business period options or data entry unit options. The business option processing module includes an option parser, which is used to convert the user's selection into standardized parameters and perform validity checks. The attribute mapping and conversion module is used to establish and maintain an attribute mapping relationship table between business options and data attributes. The attribute mapping relationship table stores the dynamic correspondence between different business options and corresponding standard attribute values, and queries the attribute mapping relationship table to obtain the standard attribute values according to the input business option and return type. The nested function call execution module is used to implement nested calls between the custom function and the built-in functions of the spreadsheet. The nested function call execution module includes a function parsing engine, a call stack, and a context management mechanism, which is responsible for parsing nested function expressions, managing variable space, and passing intermediate execution results. The results display and update module is used to display the acquired and processed data attribute values in a specified cell of a spreadsheet in real time. The results display and update module includes a cell rendering engine and an event listening mechanism, and realizes real-time automatic updates of the displayed content as the data changes through data binding.