Data updating method, device and computer equipment in Excel

Abstract

This application relates to the field of data management in financial technology and discloses a method for updating data in Excel, including: receiving data update parameters input by the user according to preset proxy rules, forming a parameter dictionary corresponding to the data update parameters; mapping the parameter dictionary to a specified hash value; judging whether a server instruction for the user to call the RTD function for real-time data update in Excel has been collected; if so, writing the specified hash value to the server of the RTD function so that the server of the RTD function calls Excel and controls Excel to return the Topic ID corresponding to the specified hash value and the specified hash value; constructing a data processor based on the Topic ID, the data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data. This method solves the problems of complex signatures, limited parameter length and parameter form, and single return value, and realizes the simultaneous modification of the assignment of multiple cells, making the user use process more convenient.

Description

Technical Field

[0001] This application relates to the field of data management in financial technology, and in particular to methods, devices, and computer equipment for updating data in Excel. Background Art

[0002] The original purpose of the RTD function was to update real-time changing data, such as real-time stock quotes, real-time weather forecasts, team game scores, and so on. Excel was later introduced as a mechanism for viewing and updating real-time data. It uses a Push-Pull method. When data needs to be updated, the RTD function sends an update data message to Excel, and Excel actively pulls the new data after receiving the message. For each RTD function, Excel generates a TopicId and uses a character array to pass in the user's parameter values. The backend can push updated data to Excel based on the TopicId. However, in actual use, the interface provided by Microsoft has the following shortcomings: the RTD function signature is complex; the RTDServer accepts parameters in String form, and the function parameter length is limited; the backend data must be bound to the Topic object in the RTDServer when pushing, and can only return a single value, etc., which is not conducive to user use. Summary of the Invention

[0003] The main purpose of this application is to provide a data updating method in Excel, aiming to solve the technical problem that the existing Excel data updating through RTD function has defects and is not conducive to user use.

[0004] This application proposes a data updating method in Excel, including:

[0005] Receive data update parameters input by the user according to preset proxy rules, and form a parameter dictionary corresponding to the data update parameters;

[0006] Map the parameter dictionary to a specified Hash value;

[0007] Determine whether the server instruction for the user to call the RTD function in Excel to update data in real time has been collected;

[0008] If so, the specified hash value is written into the server of the RTD function, so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value;

[0009] A data processor is constructed according to the TopicID. The data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data.

[0010] Preferably, the parameter dictionary includes multiple key:Value pairs, and the step of mapping the parameter dictionary to a specified Hash value includes:

[0011] Select a specified prime number as the initial value and a specified Mersenne prime number as the factor;

[0012] Calculate the first hash value corresponding to the key in the first key-value pair according to Hash = factor * initial value + key.getHashCode(), where the first key-value pair is the first key-value pair sorted in the parameter dictionary, and the first key-value pair, the second key-value pair, and so on to the last key-value pair are arranged in order from left to right in the parameter dictionary;

[0013] According to Hash = factor * first Hash value + Value.getHashCode(), calculate the second Hash value corresponding to Value in the first key-value pair;

[0014] According to Hash = factor * second Hash value + key.getHashCode(), calculate the third Hash value corresponding to the key in the second key-value pair, and calculate the Hash value of the second key-value pair according to the Hash value calculation process of the first key-value pair, until the Hash value of the last key-value pair is iterated;

[0015] The hash value of the last key-value pair is used as the specified hash value corresponding to the parameter dictionary.

[0016] Preferably, after the step of using the hash value of the last key-value pair as the specified hash value corresponding to the parameter dictionary, the method further includes:

[0017] Determine whether there is a hash value in the cache that is the same as the specified hash value;

[0018] If so, adding a distinguishing key-value pair to the parameter dictionary;

[0019] According to the Hash value calculation process of the parameter dictionary, the Hash value of the parameter dictionary to which the distinguishing key-value pairs are added is calculated.

[0020] Preferably, after the step of transferring the specified hash value to the server of the RTD function so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value, the method further includes:

[0021] Retrieve the corresponding parameter dictionary from the cache according to the specified Hash value;

[0022] Fill the parameter dictionary into the object created by the Topic utility class of the RTD function to form the Topic object of the RTD function, wherein the Topic object of the RTD function includes the TopicID and the return value cache reserved position;

[0023] A key-value pair is formed with the TopicID as the key value and the Topic object of the RTD function as the Value value, and is cached in the parameter dictionary to store the return value when the data is updated.

[0024] Preferably, after constructing a data processor according to the TopicID, caching the return value when data is updated, and calling the server of the RTD function to notify Excel to update the data, the process includes:

[0025] Determine whether a notification from Excel indicating that the specified TopicID has expired has been received;

[0026] If so, query the specified key-value pair with the specified TopicID as the key value, and determine the specified RTDTopic corresponding to the Value value in the specified key-value pair;

[0027] Destroy the specified RTDTopic and the data processor corresponding to the specified RTDTopic.

[0028] Preferably, before the step of receiving the data update parameters input by the user according to the preset proxy method and forming a parameter dictionary corresponding to the data update parameters, the method includes:

[0029] Set the proxy function value in String format for the ProgID and ServerName involved in the RTD function signature;

[0030] The String form parameters involved in the RTD function signature are encapsulated into a parameter dictionary in the form of Key and Value, wherein the value corresponding to the Key value refers to the String form parameter.

[0031] The present application also provides a data updating device in Excel, comprising:

[0032] A receiving module, configured to receive data update parameters input by a user according to preset proxy rules, and form a parameter dictionary corresponding to the data update parameters;

[0033] A mapping module, used to map the parameter dictionary to a specified Hash value;

[0034] A judgment module is used to judge whether a server instruction for the user to call the RTD function of real-time update data in Excel is collected;

[0035] A writing module is configured to write the specified hash value to the server of the RTD function if a server instruction of a user calling an RTD function for real-time updating of data in Excel is collected, so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value;

[0036] The data update module is used to construct a data processor according to the TopicID. The data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data.

[0037] Preferably, the parameter dictionary includes multiple key:Value pairs, and the mapping module includes:

[0038] A selection unit is used to select a specified prime number as an initial value and a specified Mersenne prime number as a factor;

[0039] a first calculation unit, configured to calculate a first hash value corresponding to the key in a first key-value pair according to Hash = factor * initial value + key.getHashCode(), where the first key-value pair is the first key-value pair sorted in the parameter dictionary, and the first key-value pair, the second key-value pair, and so on to the last key-value pair are arranged in order from left to right in the parameter dictionary;

[0040] The second calculation unit is used to calculate the second Hash value corresponding to the Value in the first key-value pair according to Hash = factor * first Hash value + Value.getHashCode();

[0041] A third calculation unit is used to calculate a third hash value corresponding to the key in the second key-value pair according to Hash = factor * second hash value + key.getHashCode(), and calculate the hash value of the second key-value pair according to the hash value calculation process of the first key-value pair, until the hash value of the last key-value pair is iterated;

[0042] As a unit, used to use the Hash value of the last key-value pair as the specified Hash value corresponding to the parameter dictionary.

[0043] The present application also provides a computer device, comprising a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps of the above method when executing the computer program.

[0044] The present application also provides a computer-readable storage medium having a computer program stored thereon, and the computer program implements the steps of the above method when executed by a processor.

[0045] This application encapsulates the parameters into a parameter dictionary and maps the parameter dictionary to a unique hash value through hash value calculation. At the same time, it solves the problems of complex signatures and limited parameter length and parameter form by setting proxy rules. By constructing the RTDTopic object and giving it a process, it solves the problem of a single return value and realizes the simultaneous modification of the assignment of multiple cells, making the user experience more convenient. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] Figure 1 This is a flowchart of a method for updating data in Excel according to an embodiment of the present application;

[0047] Figure 2 This is a schematic diagram of the structure of a data updating device in Excel according to an embodiment of the present application;

[0048] Figure 3 This is a schematic diagram of the internal structure of a computer device according to an embodiment of the present application. DETAILED DESCRIPTION

[0049] In order to make the purpose, technical solutions and advantages of this application more clear, the following further describes this application in detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain this application and are not intended to limit this application.

[0050] Reference Figure 1 , this embodiment provides a method for updating data in Excel, including:

[0051] S1: receiving data update parameters input by the user according to preset proxy rules, and forming a parameter dictionary corresponding to the data update parameters;

[0052] S2: Map the parameter dictionary to a specified Hash value;

[0053] S3: Determine whether the server instruction for the user to call the RTD function in Excel to update data in real time is collected;

[0054] S4: If yes, then write the specified hash value into the server of the RTD function, so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value;

[0055] S5: Construct a data processor according to the TopicID. The data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data.

[0056] This application receives data update parameters through a preset proxy method. The proxy method presets a corresponding relationship and automatically converts the parameters input by the user into a parameter form that can be recognized by the RTD (real-time data) function. This simplifies the complexity of user operations without interfering with the RTD function's parameter call process. The user only needs to pay attention to the parameters that need to be passed in, including adding or deleting certain parameters, avoiding the problem of complex RTD function signatures. The RTD function is used to retrieve real-time data from programs that support COM automation. Its syntax is as follows: =RTD(ProgID,server,topic1,topic2,...), where the parameter ProgID is the ProgID name of the registered COM automation add-in that has been installed on the local computer and is enclosed in quotation marks; Server is the name of the server running the add-in; topic1, topic2,... are 1 to 253 parameters, which together represent a unique real-time data. This implementation encapsulates parameters into a parameter dictionary and identifies it by mapping it to a unique hash value, enabling accurate access to the parameter dictionary. This avoids the limitations of parameter type and length when directly calling string functions. Furthermore, by constructing and streamlining the Topic object for the RTD function, it allows for simultaneous modification of multiple cell values, resolving the issue of a single return value and enabling simultaneous modification of multiple cell values, making the user experience more convenient.

[0057] Furthermore, the parameter dictionary includes multiple key:Value pairs, and the step S2 of mapping the parameter dictionary to a specified Hash value includes:

[0058] S21: Select a specified prime number as the initial value and select a specified Mersenne prime number as a factor;

[0059] S22: Calculate the first hash value corresponding to the key in the first key-value pair according to Hash = factor * initial value + key.getHashCode(), where the first key-value pair is the first key-value pair sorted in the parameter dictionary, and the first key-value pair, the second key-value pair, and finally the last key-value pair are arranged in order from left to right in the parameter dictionary;

[0060] S23: Calculate the second Hash value corresponding to the Value in the first key-value pair according to Hash = Factor * First Hash Value + Value.getHashCode();

[0061] S24: Calculate the third hash value corresponding to the key in the second key-value pair according to Hash = factor * second hash value + key.getHashCode(), and calculate the hash value of the second key-value pair according to the hash value calculation process of the first key-value pair, until the hash value of the last key-value pair is iterated;

[0062] S25: Use the Hash value of the last key-value pair as the specified Hash value corresponding to the parameter dictionary.

[0063] This embodiment sets the parameter dictionary to a unique int Hash value by performing the following algorithm on the parameter dictionary. The initial Hash value is selected as a prime number, such as int hash = 17; a Mersenne prime number is selected as a factor, and the above-mentioned Mersenne prime number is, for example, 31 (i.e., 2^5-1), then the Hash values ​​corresponding to the key-value pairs key:value are taken out in turn, and then the Hash value of the last key-value pair in the parameter dictionary is calculated in turn according to the calculation formula in an iterative manner, as the Hash value of the entire parameter dictionary, and the parameters entered by the user are mapped to a Hash value. Hash: {"arg1":object1, "arg2":object2}, the mapping relationship is put into a cache, and the Hash value is passed in. The original function is optimized to: MyRTDServer.Call(hash.ToString()). The calculation formula is: Hash = 31 * Hash + key.getHashCode(); Hash = 31 * Hash + value.getHashCode(). key.getHashCode() and value.getHashCode() indicate that each key-value pair's key and value correspond to a different HashCode(). After the user enters the parameters, all parameters are packaged as key-value pairs, and a unique hash value is generated and stored in the cache. This hash value is then passed as the sole parameter to the RTD function's server.

[0064] Furthermore, after the step S25 of using the hash value of the last key-value pair as the designated hash value corresponding to the parameter dictionary, the following steps are included:

[0065] S26: Determine whether there is a hash value identical to the specified hash value in the cache;

[0066] S27: If yes, add a distinguishing key-value pair to the parameter dictionary;

[0067] S28: According to the Hash value calculation process of the parameter dictionary, a Hash value of the parameter dictionary to which the distinguishing key-value pairs are added is calculated.

[0068] In this embodiment, if the hash value of the parameter dictionary passed in is the same as the hash value already in the cache, a distinguishing key-value pair can be added to the parameter dictionary to distinguish them so that each parameter dictionary has a unique hash value corresponding to it. The above-mentioned distinguishing key-value pairs include "funcName":"myFunc" or "functionld":"1234", but it is simpler to use the distinguishing key-value pair "funcName":"myFunc", that is, the parameter dictionary contains the following content: {"funcName":"myFunc","arg1":object1,"arg2":object2}. The above-mentioned distinguishing key-value pairs can be arranged in any key-value pair position in the parameter dictionary.

[0069] Furthermore, after step S4 of transferring the specified hash value to the server of the RTD function so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value, the following steps are included:

[0070] S41: Retrieving a corresponding parameter dictionary from the cache according to the specified Hash value;

[0071] S42: Fill the parameter dictionary into the object created by the Topic utility class of the RTD function to form the Topic object of the RTD function, wherein the Topic object of the RTD function includes the TopicID and the return value cache reserved position;

[0072] S43: A key-value pair is formed with the TopicID as the key value and the Topic object of the RTD function as the Value value, and is cached in the parameter dictionary to store the return value when the data is updated.

[0073] When the RTDServer receives a request, Excel returns a TopicID and the passed-in Hash value. Based on the Hash value, it retrieves the corresponding parameter dictionary from the cache and constructs a Topic object for the RTD function. The process for constructing an RTD function Topic object is as follows: RTDTopic:{Int TopicId;Map Arguments; / / {"funcName":"myFunc","arg1":object1,"arg2":object2},Object Value;}. That is, using the TopicID as the key value, the RTDTopic is cached in the parameter dictionary, and a corresponding processor is constructed for each RTDTopic. The processor extracts the parameter value and, based on business logic, retrieves and processes data in a multi-threaded manner in the background. When data is updated, the return value is cached in RTDTopic.Value, and a method in the RTD Server is called to notify Excel. When Excel retrieves a value from the RTD Server, it finds the corresponding RTDTopic in the cache based on the TopicID and returns RTDTopic.Value to Excel. Because there are multiple key-value pairs in the parameter dictionary, different cells can be updated through multiple different assignments, avoiding the problem of multiple cells having the same value assigned due to a single return value.

[0074] Furthermore, a data processor is constructed according to the TopicID. The data processor caches the return value when the data is updated, and after step S5 of calling the server of the RTD function to notify Excel to update the data, the following steps are included:

[0075] S51: Determine whether a notification from Excel that the specified TopicID has expired has been received;

[0076] S52: If yes, query the specified key-value pair with the specified TopicID as the key value, and determine the specified RTDTopic corresponding to the Value value in the specified key-value pair;

[0077] S53: Destroy the specified RTDTopic and the data processor corresponding to the specified RTDTopic.

[0078] When Excel issues a notification that a TopicId has expired, the corresponding RTDTopic and background processor will be destroyed. When the RTD Server lifecycle ends, all RTDTopics and processors will be destroyed to clean up data in a timely manner and free up memory space. In RTD Server, RTDTopic is managed in a fine-grained manner, including the creation, acquisition, destruction, and update notifications of the above-mentioned RTDTopic objects. In the data processing program, users do not need to worry about the lifecycle of the Topic and the interaction between the RTD Server and Excel. They only need to focus on data acquisition and processing, which simplifies the user's operation process.

[0079] Furthermore, before the step S1 of receiving the data update parameters input by the user according to the preset proxy method and forming a parameter dictionary corresponding to the data update parameters, the following steps are included:

[0080] S101: Setting a proxy function value in String format for the ProgID and ServerName involved in the RTD function signature;

[0081] S102: Encapsulate the String form parameters involved in the RTD function signature into a parameter dictionary in the form of Key and Value, wherein the value corresponding to the Key value refers to the String form parameter.

[0082] The original RTD function signature is defined as follows: =RTD(ProgID,Server,String1,[String2],...); ProgID and Server are the same for the same RTD server. A proxy method can be provided within the RTD server implementation class, such as MyRTDServer.Call(String1,[String2],...). External functions then simply call this method and pass in their own parameters. For example: ObjectmyFunc(object arg1,object arg2){; / / Do your logic here; Return MyRTDServer.Call(arg1,arg2)}. Custom methods can process user-input parameters, adding or removing certain parameters, and then pass them in the MyRTDServer.Call call, thus circumventing the complex RTD function signature. To address this issue, the RTD server implementation can set a unique String value when registering with Excel. The two parameters, RealTimeServerProgID and ServerName, which are the same across all methods, are then provided as a unified proxy function value. Users only need to pass in the parameters required by the function itself. Because the parameter type of the RTD function is limited to String and the total length is limited, the parameters in MyRTDServer.Call(String1,[String2],...) can be encapsulated as a parameter dictionary, such as {"arg1":object1,"arg2":object2}.

[0083] Reference Figure 2 , an Excel data updating device according to an embodiment of the present application includes:

[0084] Receiving module 1, used to receive data update parameters input by the user according to preset proxy rules, and form a parameter dictionary corresponding to the data update parameters;

[0085] Mapping module 2, used to map the parameter dictionary to a specified Hash value;

[0086] A judgment module 3 is used to judge whether a server instruction for the user to call the RTD function of real-time update data in Excel is collected;

[0087] A writing module 4 is configured to write the specified hash value to the server of the RTD function if a server instruction of a user calling an RTD function for real-time data update in Excel is collected, so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value;

[0088] The data updating module 5 is used to construct a data processor according to the TopicID. The data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data.

[0089] This application receives data update parameters through a preset proxy method. The proxy method presets a corresponding relationship and automatically converts the parameters input by the user into a parameter form that can be recognized by the RTD (real-time data) function. This simplifies the complexity of user operations without interfering with the RTD function's parameter call process. The user only needs to pay attention to the parameters that need to be passed in, including adding or deleting certain parameters, avoiding the problem of complex RTD function signatures. The RTD function is used to retrieve real-time data from programs that support COM automation. Its syntax is as follows: =RTD(ProgID,server,topic1,topic2,...), where the parameter ProgID is the ProgID name of the registered COM automation add-in that has been installed on the local computer and is enclosed in quotation marks; Server is the name of the server running the add-in; topic1, topic2,... are 1 to 253 parameters, which together represent a unique real-time data. This implementation encapsulates parameters into a parameter dictionary and identifies it by mapping it to a unique hash value, enabling accurate access to the parameter dictionary. This avoids the limitations of parameter type and length when directly calling string functions. Furthermore, by constructing and streamlining the Topic object for the RTD function, it allows for simultaneous modification of multiple cell values, resolving the issue of a single return value and enabling simultaneous modification of multiple cell values, making the user experience more convenient.

[0090] Furthermore, the parameter dictionary includes multiple key:Value pairs, and the mapping module 2 includes:

[0091] A selection unit is used to select a specified prime number as an initial value and a specified Mersenne prime number as a factor;

[0092] a first calculation unit, configured to calculate a first hash value corresponding to the key in a first key-value pair according to Hash = factor * initial value + key.getHashCode(), where the first key-value pair is the first key-value pair sorted in the parameter dictionary, and the first key-value pair, the second key-value pair, and so on to the last key-value pair are arranged in order from left to right in the parameter dictionary;

[0093] The second calculation unit is used to calculate the second Hash value corresponding to the Value in the first key-value pair according to Hash = factor * first Hash value + Value.getHashCode();

[0094] A third calculation unit is used to calculate a third hash value corresponding to the key in the second key-value pair according to Hash = factor * second hash value + key.getHashCode(), and calculate the hash value of the second key-value pair according to the hash value calculation process of the first key-value pair, until the hash value of the last key-value pair is iterated;

[0095] As a unit, used to use the Hash value of the last key-value pair as the specified Hash value corresponding to the parameter dictionary.

[0096] This embodiment sets the parameter dictionary to a unique int Hash value by performing the following algorithm on the parameter dictionary. The initial Hash value is selected as a prime number, such as int hash = 17; a Mersenne prime number is selected as a factor, and the above-mentioned Mersenne prime number is, for example, 31 (i.e., 2^5-1), then the Hash values ​​corresponding to the key-value pairs key:value are taken out in turn, and then the Hash value of the last key-value pair in the parameter dictionary is calculated in turn according to the calculation formula in an iterative manner, as the Hash value of the entire parameter dictionary, and the parameters entered by the user are mapped to a Hash value. Hash: {"arg1":object1, "arg2":object2}, the mapping relationship is put into a cache, and the Hash value is passed in. The original function is optimized to: MyRTDServer.Call(hash.ToString()). The calculation formula is: Hash = 31 * Hash + key.getHashCode(); Hash = 31 * Hash + value.getHashCode(). key.getHashCode() and value.getHashCode() indicate that each key-value pair's key and value correspond to a different HashCode(). After the user enters the parameters, all parameters are packaged as key-value pairs, and a unique hash value is generated and stored in the cache. This hash value is then passed as the sole parameter to the RTD function's server.

[0097] Furthermore, the mapping module 2 includes:

[0098] A determination unit, configured to determine whether a hash value identical to the specified hash value exists in the cache;

[0099] an adding unit, configured to add a distinguishing key-value pair to the parameter dictionary if a hash value identical to the specified hash value exists;

[0100] The fourth calculation unit is used to calculate the Hash value of the parameter dictionary to which the distinguishing key-value pairs are added according to the Hash value calculation process of the parameter dictionary.

[0101] In this embodiment, if the hash value of the parameter dictionary passed in is the same as the hash value already in the cache, a distinguishing key-value pair can be added to the parameter dictionary to distinguish them so that each parameter dictionary has a unique hash value corresponding to it. The above-mentioned distinguishing key-value pairs include "funcName":"myFunc" or "functionld":"1234", but it is simpler to use the distinguishing key-value pair "funcName":"myFunc", that is, the parameter dictionary contains the following content: {"funcName":"myFunc","arg1":object1,"arg2":object2}. The above-mentioned distinguishing key-value pairs can be arranged in any key-value pair position in the parameter dictionary.

[0102] Furthermore, after step S4 of transferring the specified hash value to the server of the RTD function so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value, the following steps are included:

[0103] S41: Retrieving a corresponding parameter dictionary from the cache according to the specified Hash value;

[0104] S42: Fill the parameter dictionary into the object created by the Topic utility class of the RTD function to form the Topic object of the RTD function, wherein the Topic object of the RTD function includes the TopicID and the return value cache reserved position;

[0105] S43: A key-value pair is formed with the TopicID as the key value and the Topic object of the RTD function as the Value value, and is cached in the parameter dictionary to store the return value when the data is updated.

[0106] When the RTD Server receives a request, Excel returns a TopicID and the passed-in Hash value. Based on the Hash value, Excel retrieves the corresponding parameter dictionary from the cache to construct a Topic object for the RTD function. The process for constructing an RTD function Topic object is as follows: RTDTopic:{Int TopicId;Map Arguments; / / {"funcName":"myFunc","arg1":object1,"arg2":object2},Object Value;}. That is, using the TopicID as the key value, the RTDTopic is cached in the parameter dictionary, and a corresponding handler is constructed for each RTDTopic. The handler extracts the parameter value and, based on business logic, retrieves and processes data in a multi-threaded manner in the background. When data is updated, the return value is cached in RTDTopic.Value, and a method in the RTD Server is called to notify Excel. When Excel retrieves a value from the RTD Server, it finds the corresponding RTDTopic in the cache based on the TopicID and returns RTDTopic.Value to Excel. Because there are multiple key-value pairs in the parameter dictionary, different cells can be updated through multiple different assignments, avoiding the problem of multiple cells having the same value assigned due to a single return value.

[0107] Furthermore, a data processor is constructed according to the TopicID. The data processor caches the return value when the data is updated, and after step S5 of calling the server of the RTD function to notify Excel to update the data, the following steps are included:

[0108] S51: Determine whether a notification from Excel that the specified TopicID has expired has been received;

[0109] S52: If yes, query the specified key-value pair with the specified TopicID as the key value, and determine the specified RTDTopic corresponding to the Value value in the specified key-value pair;

[0110] S53: Destroy the specified RTDTopic and the data processor corresponding to the specified RTDTopic.

[0111] When Excel issues a notification that a TopicId has expired, the corresponding RTDTopic and background processor will be destroyed. When the RTD Server lifecycle ends, all RTDTopics and processors will be destroyed to clean up data in a timely manner and free up memory space. In RTD Server, RTDTopic is managed in a fine-grained manner, including the creation, acquisition, destruction, and update notifications of the above-mentioned RTDTopic objects. In the data processing program, users do not need to worry about the lifecycle of the Topic and the interaction between the RTD Server and Excel. They only need to focus on data acquisition and processing, which simplifies the user's operation process.

[0112] Furthermore, before the step S1 of receiving the data update parameters input by the user according to the preset proxy method and forming a parameter dictionary corresponding to the data update parameters, the following steps are included:

[0113] S101: Setting a proxy function value in String format for the ProgID and ServerName involved in the RTD function signature;

[0114] S102: Encapsulate the String form parameters involved in the RTD function signature into a parameter dictionary in the form of Key and Value, wherein the value corresponding to the Key value refers to the String form parameter.

[0115] The original RTD function signature is defined as follows: =RTD(ProgID,Server,String1,[String2],...); ProgID and Server are the same for the same RTD server. A proxy method can be provided within the RTD server implementation class, such as MyRTDServer.Call(String1,[String2],...). External functions then simply call this method and pass in their own parameters. For example: Object myFunc(object arg1,object arg2){; / / Do your logic here; Return MyRTDServer.Call(arg1,arg2)}. Custom methods can process user-input parameters, adding or removing certain parameters, and then pass them in the MyRTDServer.Call call, circumventing the complexity of the RTD function signature. To address this, the RTD server implementation can set a unique String value when registering with Excel. The two parameters, RealTimeServerProgID and ServerName, which are the same across all methods, are then provided as a unified proxy function value. Users only need to pass in the parameters required by the function itself. Because the parameter type of the RTD function is limited to String and the total length is limited, the parameters in MyRTDServer.Call(String1,[String2],...) can be encapsulated as a parameter dictionary, such as {"arg1":object1,"arg2":object2}.

[0116] Reference Figure 3 In the embodiment of the present application, a computer device is also provided. The computer device may be a server, and its internal structure may be as follows: Figure 3 As shown. The computer device includes a processor, a memory, a network interface and a database connected via a system bus. The processor of the computer design is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store all data required for the data update process in Excel. The network interface of the computer device is used to communicate with an external terminal via a network connection. When the computer program is executed by the processor, it implements the data update method in Excel.

[0117] The above-mentioned processor executes the above-mentioned data update method in Excel, including: receiving data update parameters input by the user according to preset proxy rules, forming a parameter dictionary corresponding to the data update parameters; mapping the parameter dictionary to a specified hash value; judging whether a server instruction of the user calling the RTD function for real-time data update in Excel is collected; if so, writing the specified hash value into the server of the RTD function, so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value; constructing a data processor according to the TopicID, the data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data.

[0118] The above-mentioned computer device encapsulates parameters into a parameter dictionary and maps the parameter dictionary to a unique hash value through hash value calculation. At the same time, it solves the problems of complex signatures and limited parameter length and parameter form by setting proxy rules. By constructing the RTDTopic object and giving it a process, it solves the problem of a single return value and realizes the simultaneous modification of the assignment of multiple cells, making the user experience more convenient.

[0119] In one embodiment, the parameter dictionary includes multiple pairs of key:Value key-value pairs, and the above-mentioned processor maps the parameter dictionary to a specified Hash value, including: selecting a specified prime number as an initial value and selecting a specified Mersenne prime as a factor; calculating a first Hash value corresponding to the key in the first key-value pair according to Hash=factor*initial value+key.getHashCode(), wherein the first key-value pair is the first key-value pair sorted in the parameter dictionary, and the first key-value pair, the second key-value pair, and the last key-value pair are arranged from left to right in the parameter dictionary; calculating a second Hash value corresponding to the Value in the first key-value pair according to Hash=factor*first Hash value+Value.getHashCode(); calculating a third Hash value corresponding to the key in the second key-value pair according to Hash=factor*second Hash value+key.getHashCode(), and calculating the Hash value of the second key-value pair according to the Hash value calculation process of the first key-value pair, until the Hash value of the last key-value pair is iterated out; and using the Hash value of the last key-value pair as the specified Hash value corresponding to the parameter dictionary.

[0120] In one embodiment, after the above-mentioned processor uses the hash value of the last key-value pair as the specified hash value corresponding to the parameter dictionary, it includes: determining whether there is a hash value identical to the specified hash value in the cache; if so, adding a distinguishing key-value pair to the parameter dictionary; and calculating the hash value of the parameter dictionary to which the distinguishing key-value pair is added according to the hash value calculation process of the parameter dictionary.

[0121] In one embodiment, the above-mentioned processor passes the specified Hash value into the server of the RTD function so that the server of the RTD function calls Excel, and controls Excel to return the TopicID corresponding to the specified Hash value and the specified Hash value. After that, the following steps are included: taking out the corresponding parameter dictionary from the cache according to the specified Hash value; filling the parameter dictionary into the object created by the Topic utility class of the RTD function to form the Topic object of the RTD function, and the Topic object of the RTD function includes the TopicID and the return value cache reserved bit; forming a key-value pair with the TopicID as the key value and the Topic object of the RTD function as the Value value, and caching it in the parameter dictionary to store the return value when the data is updated.

[0122] In one embodiment, the above-mentioned processor constructs a data processor based on the TopicID. The data processor caches the return value when the data is updated, and after the step of calling the server of the RTD function to notify Excel to update the data, it includes: determining whether a notification is received from Excel that the specified TopicID has expired; if so, querying the specified key-value pair with the specified TopicID as the key value, and determining the specified RTDTopic corresponding to the Value value in the specified key-value pair; destroying the specified RTDTopic and the data processor corresponding to the specified RTDTopic.

[0123] In one embodiment, the above-mentioned processor receives the data update parameters input by the user according to the preset proxy method, and before the step of forming the parameter dictionary corresponding to the data update parameters, it includes: setting the proxy function value in String form for the ProgID and ServerName involved in the RTD function signature; encapsulating the String form parameters involved in the RTD function signature into a parameter dictionary in the form of Key and Value, wherein the assignment corresponding to the Key value refers to the String form parameter.

[0124] Those skilled in the art will understand that Figure 3The structure shown in is merely a block diagram of a portion of the structure related to the present application solution and does not constitute a limitation on the computer device to which the present application solution is applied.

[0125] An embodiment of the present application also provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, a method for updating data in Excel is implemented, including: receiving data update parameters input by a user according to preset proxy rules, and forming a parameter dictionary corresponding to the data update parameters; mapping the parameter dictionary to a specified hash value; determining whether a server instruction for a user to call an RTD function for real-time data update in Excel is collected; if so, writing the specified hash value to the server of the RTD function, so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value, as well as the specified hash value; constructing a data processor according to the TopicID, wherein the data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data.

[0126] The above-mentioned computer-readable storage medium encapsulates parameters into a parameter dictionary and maps the parameter dictionary to a unique hash value through hash value calculation. At the same time, it solves the problems of complex signatures and limited parameter length and parameter form by setting proxy rules. By constructing the RTDTopic object and giving it a process-based approach, it solves the problem of a single return value and realizes the simultaneous modification of the assignment of multiple cells, making the user experience more convenient.

[0127] In one embodiment, the parameter dictionary includes multiple pairs of key:Value key-value pairs, and the above-mentioned processor maps the parameter dictionary to a specified Hash value, including: selecting a specified prime number as an initial value and selecting a specified Mersenne prime as a factor; calculating a first Hash value corresponding to the key in the first key-value pair according to Hash=factor*initial value+key.getHashCode(), wherein the first key-value pair is the first key-value pair sorted in the parameter dictionary, and the first key-value pair, the second key-value pair, and the last key-value pair are arranged from left to right in the parameter dictionary; calculating a second Hash value corresponding to the Value in the first key-value pair according to Hash=factor*first Hash value+Value.getHashCode(); calculating a third Hash value corresponding to the key in the second key-value pair according to Hash=factor*second Hash value+key.getHashCode(), and calculating the Hash value of the second key-value pair according to the Hash value calculation process of the first key-value pair, until the Hash value of the last key-value pair is iterated out; and using the Hash value of the last key-value pair as the specified Hash value corresponding to the parameter dictionary.

[0128] In one embodiment, after the above-mentioned processor uses the hash value of the last key-value pair as the specified hash value corresponding to the parameter dictionary, it includes: determining whether there is a hash value identical to the specified hash value in the cache; if so, adding a distinguishing key-value pair to the parameter dictionary; and calculating the hash value of the parameter dictionary to which the distinguishing key-value pair is added according to the hash value calculation process of the parameter dictionary.

[0129] In one embodiment, the above-mentioned processor passes the specified Hash value into the server of the RTD function so that the server of the RTD function calls Excel, and controls Excel to return the TopicID corresponding to the specified Hash value and the specified Hash value. After that, the following steps are included: taking out the corresponding parameter dictionary from the cache according to the specified Hash value; filling the parameter dictionary into the object created by the Topic utility class of the RTD function to form the Topic object of the RTD function, and the Topic object of the RTD function includes the TopicID and the return value cache reserved bit; forming a key-value pair with the TopicID as the key value and the Topic object of the RTD function as the Value value, and caching it in the parameter dictionary to store the return value when the data is updated.

[0130] In one embodiment, the above-mentioned processor constructs a data processor based on the TopicID. The data processor caches the return value when the data is updated, and after the step of calling the server of the RTD function to notify Excel to update the data, it includes: determining whether a notification is received from Excel that the specified TopicID has expired; if so, querying the specified key-value pair with the specified TopicID as the key value, and determining the specified RTDTopic corresponding to the Value value in the specified key-value pair; destroying the specified RTDTopic and the data processor corresponding to the specified RTDTopic.

[0131] In one embodiment, the above-mentioned processor receives the data update parameters input by the user according to the preset proxy method, and before the step of forming the parameter dictionary corresponding to the data update parameters, it includes: setting the proxy function value in String form for the ProgID and ServerName involved in the RTD function signature; encapsulating the String form parameters involved in the RTD function signature into a parameter dictionary in the form of Key and Value, wherein the assignment corresponding to the Key value refers to the String form parameter.

[0132] Those skilled in the art will appreciate that all or part of the processes in the above-mentioned embodiment methods can be implemented by instructing the relevant hardware through a computer program, and the above-mentioned computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media provided in this application and used in the embodiments may include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (SSRSDRAM), enhanced SDRAM (ESDRAM), Synchronous Link DRAM (SLDRAM), Rambus direct RAM (RDRAM), direct RAM bus dynamic RAM (DRDRAM), and RAM bus dynamic RAM (RDRAM), etc.

[0133] It should be noted that, in this document, the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, apparatus, article, or method comprising a series of elements includes not only those elements but also other elements not explicitly listed, or elements inherent to such process, apparatus, article, or method. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of other identical elements in the process, apparatus, article, or method comprising the element.

[0134] The above description is only a preferred embodiment of the present application and does not limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present application.

Claims

1. A data updating method in Excel, characterized in that: include: Receive data update parameters input by the user according to preset proxy rules, and form a parameter dictionary corresponding to the data update parameters; Map the parameter dictionary to a specified Hash value; Determine whether the server instruction for the user to call the RTD function in Excel to update data in real time has been collected; If so, the specified hash value is written into the server of the RTD function, so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value; A data processor is constructed according to the TopicID. The data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data.

2. The Excel data updating method according to claim 1, characterized in that: The parameter dictionary includes multiple key:value pairs. The step of mapping the parameter dictionary to a specified hash value includes: Select a specified prime number as the initial value and a specified Mersenne prime number as the factor; Calculate the first hash value corresponding to the key in the first key-value pair according to Hash = factor * initial value + key.getHashCode(), where the first key-value pair is the first key-value pair sorted in the parameter dictionary, and the first key-value pair, the second key-value pair, and so on to the last key-value pair are arranged in order from left to right in the parameter dictionary; According to Hash = factor * first Hash value + Value.getHashCode(), calculate the second Hash value corresponding to Value in the first key-value pair; According to Hash = factor * second Hash value + key.getHashCode(), calculate the third Hash value corresponding to the key in the second key-value pair, and calculate the Hash value of the second key-value pair according to the Hash value calculation process of the first key-value pair, until the Hash value of the last key-value pair is iterated; The hash value of the last key-value pair is used as the specified hash value corresponding to the parameter dictionary.

3. The Excel data updating method according to claim 2, characterized in that: After the step of using the hash value of the last key-value pair as the specified hash value corresponding to the parameter dictionary, the method further includes: Determine whether there is a hash value in the cache that is the same as the specified hash value; If so, adding a distinguishing key-value pair to the parameter dictionary; According to the Hash value calculation process of the parameter dictionary, the Hash value of the parameter dictionary to which the distinguishing key-value pairs are added is calculated.

4. The Excel data updating method according to claim 1, characterized in that: After the step of transferring the specified hash value to the server of the RTD function so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value, the method includes: Retrieve the corresponding parameter dictionary from the cache according to the specified Hash value; Fill the parameter dictionary into the object created by the Topic utility class of the RTD function to form the Topic object of the RTD function, wherein the Topic object of the RTD function includes the TopicID and the return value cache reserved position; A key-value pair is formed with the TopicID as the key value and the Topic object of the RTD function as the Value value, and is cached in the parameter dictionary to store the return value when the data is updated.

5. The Excel data updating method according to claim 1, characterized in that: After constructing a data processor according to the TopicID, caching the return value when data is updated, and calling the server of the RTD function to notify Excel to update the data, the method includes: Determine whether a notification from Excel indicating that the specified TopicID has expired has been received; If so, query the specified key-value pair with the specified TopicID as the key value, and determine the specified RTDTopic corresponding to the Value value in the specified key-value pair; Destroy the specified RTDTopic and the data processor corresponding to the specified RTDTopic.

6. The Excel data updating method according to claim 1, characterized in that: Before the step of receiving the data update parameters input by the user according to the preset proxy method and forming a parameter dictionary corresponding to the data update parameters, the method includes: Set the proxy function value in String format for the ProgID and ServerName involved in the RTD function signature; The String form parameters involved in the RTD function signature are encapsulated into a parameter dictionary in the form of Key and Value, where the value corresponding to the Key value refers to the String form parameter.

7. A data updating device in Excel, characterized in that: include: A receiving module, configured to receive data update parameters input by a user according to preset proxy rules, and form a parameter dictionary corresponding to the data update parameters; A mapping module, used to map the parameter dictionary to a specified Hash value; A judgment module is used to judge whether a server instruction for the user to call the RTD function of real-time update data in Excel is collected; A writing module is configured to write the specified hash value to the server of the RTD function if a server instruction of a user calling an RTD function for real-time updating of data in Excel is collected, so that the server of the RTD function calls Excel and controls Excel to return the TopicID corresponding to the specified hash value and the specified hash value; The data update module is used to construct a data processor according to the TopicID. The data processor caches the return value when the data is updated, and calls the server of the RTD function to notify Excel to update the data.

8. The Excel data updating device according to claim 7, characterized in that: The parameter dictionary includes multiple key:Value pairs, and the mapping module includes: A selection unit is used to select a specified prime number as an initial value and a specified Mersenne prime number as a factor; a first calculation unit, configured to calculate a first hash value corresponding to the key in a first key-value pair according to Hash = factor * initial value + key.getHashCode(), where the first key-value pair is the first key-value pair sorted in the parameter dictionary, and the first key-value pair, the second key-value pair, and so on to the last key-value pair are arranged in order from left to right in the parameter dictionary; The second calculation unit is used to calculate the second Hash value corresponding to the Value in the first key-value pair according to Hash = factor * first Hash value + Value.getHashCode(); A third calculation unit is used to calculate a third hash value corresponding to the key in the second key-value pair according to Hash = factor * second hash value + key.getHashCode(), and calculate the hash value of the second key-value pair according to the hash value calculation process of the first key-value pair, until the hash value of the last key-value pair is iterated; As a unit, used to use the Hash value of the last key-value pair as the specified Hash value corresponding to the parameter dictionary.

9. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

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

Images