On my previous posting about using Excel as a Coherence client I talked about how this could be done but gave no example code. In this posting I will explain how to do this in more detail and provide the code to a working example that you can modify to use your own objects. In the example a .NET client loads Stock objects into a Coherence cache and then randomly updates the Stock prices. An Excel spreadsheet can then be opened that is a client for the Coherence cache, receiving changes to the Stock objects – the price changes. The prices can also be updated from the spreadsheet, so the communications is both ways.
Stock price updates are pushed down to the Excel spreadsheet using the standard RTD (Read Time Data) server mechanism. As for the cache updates from Excel, they are send through a Coherence COM interface. VBA User Defined Functions (UDF’s) provide the interface in Excel for specifying where event data goes or which cell updates get mapped onto cache objects. These are added to Excel via the Add-in option from the Tools menu.
The UDF’s also hide some of the complexity of the interfaces from users. Here are he 3 UDF’s that provide the interface to Coherence:
In the UDF’s above “dist-stocks” is the name of the Coherence cache, “ORCL” is the key for the object you wish to update or receive change events from (this must be a string in the example) and “StockPrice” is the name of the property you want to update or get the latest value of. “E4” in the UpdateDoubleProperty() is the cell to take the new property value from – when it changes.
If properties of objects in the cache that you wish to receive updates about or change are in nested objects you can specify the target property via a “.” or if the target is in an object nested in a List or array you can use the “[ ]” operator. So for instance if the cache contained a Portfolio object with nested Stock objects in an array, you could specify the target Stock price for one of the nested Stocks as “Stocks.StockPrice”. This would get the 3rd Stock in a property of the Portfolio object called “Stocks” and from that Stock the “StockPrice” property. At the moment the UDF functions only support String and Double properties – but they could easily be enhanced to support other properties. This property specification method can also be nested as much as you like and provide as convenient way of specifying a target property as a string in Excel.
The easiest way to see how this all works in practice is to download the example and try it out – there is a readme.txt file in the example that explains how to set it up and try it out. Some of the things you will need to run the example are:
Have fun trying this out and if anyone has any additional thoughts, recommendations or feedback I’d be keen to hear them.