Using Excel as a Real-Time client for Coherence – A Full Working Example
By dxfelcey on May 16, 2009
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:
- For updating properties of Stock objects in the cache
- For receiving update events for Stock objects in the cache
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:
- Coherence for Java and .NET 3.4.2. It should work with more recent versions, though this is the release I have tested the example against.
- Java JDK 1.4.2 (or above)
- Microsoft Visual Studio 2008 and .NET 3.5. I tried to build the example using Visual Studio Express and via the command line but was unable to specify that the Coherence configuration files be embedded resources. Also someone has successfully back-ported the example to Visual Studio 2005 (though I don’t have the code)
- Excel 2003. It should work with later versions, though I am not sure when Excel started supporting the RTD mechanism.
- Office XP Primary Interop Assemblies (for Office 2003). This is need for the Excel integration assemblies.
Have fun trying this out and if anyone has any additional thoughts, recommendations or feedback I’d be keen to hear them.