In-Memory Data Grid - achieve unparalleled speed, persistence, and accuracy.RTD and UDF Examples: GS Excel Integration
| Author | Product Version | Reference | Download |
|---|---|---|---|
| Pini Cohen | 6.6 |
Overview
This section includes basic and advanced code examples for using the GigaSpaces-Excel integration with Microsoft Excel User-Defined Functions (UDF) and Real-Time Data (RTD).
Basic Examples
HelloUDF – Performing Excel Functions in Space
using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using GigaSpaces.Core;
using System.Configuration;
using HelloCommon;
//-----------------------------------------------------------
// This class demonstrate how to build a UDF wrapper of a GigaSpaces .NET proxy.
//
// We show how to wrap space operations to be used from within
// the Excel spreadsheet as a regular Excel functions
//
// All public methods are exposed to the Excel user and after the class is registered and added as an "add-in" will apear in the Excel functions list
//-----------------------------------------------------------
namespace HelloUDF
{
[ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)] //required to tag a C# class a UDF
public class HelloUDF
{
private ISpaceProxy _proxy;
public HelloUDF()
{
SpaceInit();
}
public String AddMsg(int id, string msg)
{
String output;
HelloMsg theMsg = new HelloMsg();
theMsg.ID = id;
theMsg.MSG = msg;
theMsg.STATUS = "working";
try
{
_proxy.Update<HelloMsg>(theMsg,
null, //no transactions
long.MaxValue, 0, //lease and write timeouts
UpdateModifiers.UpdateOrWrite);
output = "["+ theMsg.ToString() + "] was written to the space";
}
catch (Exception e)
{
output = e.Message;
}
return output;
}
public String SetToDone(int id)
{
String output;
HelloMsg theMsg = new HelloMsg();
theMsg.ID = id;
theMsg.STATUS = "done";
try
{
_proxy.Update<HelloMsg>(theMsg,
null, //no transactions
long.MaxValue, 0, //lease and write timeouts
UpdateModifiers.PartialUpdate);
output = "setting Messgae ID " + id + " to done...";
}
catch (Exception e)
{
output = e.Message;
}
return output;
}
[ComRegisterFunctionAttribute] //required to tag a C# class a UDF
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
}
[ComUnregisterFunctionAttribute] //required to tag a C# class a UDF
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false);
}
private static string GetSubKeyName(Type type) //required to tag a C# class a UDF
{
string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable";
return s;
}
private bool SpaceInit()
{
try
{
string url = ConfigurationManager.AppSettings["SpaceUrl"]; //an environment variable read from the excel.exe.config file
_proxy = SpaceProxyProviderFactory.Instance.FindSpace(url);
_proxy.OptimisticLocking = true;
return true;
}
catch (Exception ex)
{
ex.ToString();
return false;
}
}
}
}
HelloRTD – Loading Data from Space to Excel
using System;
using System.Configuration;
using System.Runtime.InteropServices;//<-- For our interop attributes.
using Microsoft.Office.Interop.Excel;
using GigaSpaces.Core;
using GigaSpaces.Core.Events;
using HelloCommon;
//-----------------------------------------------------------
// This class demonstrate how to build a RTD wrapper to "push" data from
// GigaSpaces to the Excel spreadsheet
//
// We show how to register on a notification for a change, and then push
// this change to a cell in Excel
//
// Usage of the RTD function within Excel:
// =RTD("HelloRTD",,)
//
//-----------------------------------------------------------
namespace HelloRTD
{
[ComVisible(true), ProgId("HelloRTD")] //the name of the progId parameter in the RTD excel function
public class TestRTD : IRtdServer//this is the interface any C# class must implement to become a RTD
{
private IRTDUpdateEvent _xlRTDUpdate; //the type of object to link between this runtime and the Excel
private ISpaceProxy _proxy; //space proxy
private int _topicID; //Excel cell id to which we will "wire" the notification
private string _eventData; //the data we receive from the space upon notification
IEventRegistration _eventReg; //store the space notification as local object to de-register
#region IRTD Members
/**
* this method is called by Excel when the RTD server start (the first time the workbook calls =RTD(...)
* here we should creat the connection to the space
* CallbackObject - the link between this runtime and the Excel
*/
public int ServerStart(IRTDUpdateEvent CallbackObject)
{
// Hold a reference to the callback object.
_xlRTDUpdate = CallbackObject;
bool isConnected = SpaceInit(); //connect to the space
//All is well, return 1.
return (isConnected ? 1 : 0);
}
/**
* called when closing the Excel
* */
public void ServerTerminate()
{
clean();
}
/**
* topicID - unique ID of the cell in the Excel
* RTDparms - must have at least one parameter
* getNewValues - used by the Excel, no need change
*/
public object ConnectData(int topicID, ref Array RTDparms, ref bool getNewValues)
{
// Registering for notifications on status "done"
HelloMsg notifyTemplate = new HelloMsg();
notifyTemplate.STATUS = "done";
_eventReg = _proxy.DefaultDataEventSession.AddListener
<HelloMsg>(notifyTemplate, Space
Where GigaSpaces data is stored. It is the logical cache that holds data objects in memory and might also hold them in layered in tiering. Data is hosted from multiple SoRs, consolidated as a unified data model._DataChanged);
//store the cell this RTD is written in
_topicID = topicID;
return "This cell is listening for msg with status 'done' ...";
}
/**
* called when deleteing the RTD function from Excel
*/
public void DisconnectData(int topicID)
{
clean();
}
/**
* called by the Excel when _xlRTDUpdate.UpdateNotify() is called
* since we call UpdateNotify in each space event,
* RefreshData is called for each event seperately
*
* topicCount = tell the excel how many cells we updated
* */
public Array RefreshData(ref int topicCount)
{
// This method returns a two-dimensional array of Variant values.
// The first dimension represents a list of topic IDs;
// these topic IDs map to the TopicID parameter in the ConnectData method above.
// This is how Excel associates topics with data.
// The second dimension represents the values associated with the topic IDs.
//1st dimention is always 2 - since we have topic and value
//2nd dimention is the number of cells we want to update in the Excel
// as a result of this specific event
//
// |topicID(0,0)|topicID(0,1)|topicID(0,2)|... topicID(0,n)|
// |value1 (1,0)|value2 (1,1)|value3 (1,2)|... valueN (1,n)|
//
// n = the number of cells we update in each event
//
//since we always update a single cell, we build the following array
object[,] result = new object[2, 1];
result[0, 0] = _topicID;
result[1, 0] = _eventData;
topicCount = 1;
return result;
}
public int Heartbeat()
{
return 1; // the RTD server is up and running, 0 indicates error
}
#endregion
/**
* calleback by GigaSpaces when event occured
* EventArgs is the data of the event
*/
private void Space_DataChanged(object sender, SpaceDataEventArgs<HelloMsg> e)
{
//store in the _receivedData the data we got from the event
_eventData = "Message ID: " + e.Object.ID + " ('" + e.Object.MSG + "') was set to Done!";
//Tell Excel that we have updates. Then the Excel calls back to RefreshData
_xlRTDUpdate.UpdateNotify();
}
private bool SpaceInit()
{
try
{
string url = ConfigurationManager.AppSettings["SpaceUrl"];
_proxy = SpaceProxyProviderFactory.Instance.FindSpace(url);
_proxy.OptimisticLocking = true;
return true;
}
catch (Exception ex)
{
System.Console.Write(ex);
return false;
}
}
private void clean()
{
try
{
//remove the event
if (_eventReg != null)
{
_proxy.DefaultDataEventSession.RemoveListener(_eventReg);
_eventReg = null;
}
// Clear the RTDUpdateEvent reference.
_xlRTDUpdate = null;
}
catch (Exception ex)
{
System.Console.Write(ex);
}
}
}
}
HelloMsg
using System.Text;
using GigaSpaces.Core.Metadata;
namespace HelloCommon
{
[SpaceClass(IncludeProperties = IncludeMembers.Public, IncludeFields = IncludeMembers.None)]
public class HelloMsg
{
private int _id;
private string _msg;
private string _status;
public HelloMsg()
{
_id = -1;
}
// NullValue instructs the space to consider a value as null when an object instance is used as a template
[SpaceID, SpaceRouting, SpaceProperty(Index = SpaceIndexType.Basic, NullValue = -1)]
public int ID
{
get { return _id; }
set { _id = value; }
}
public string MSG
{
get { return _msg; }
set { _msg = value; }
}
public string STATUS
{
get { return _status; }
set { _status = value; }
}
public override string ToString()
{
StringBuilder builder = new StringBuilder();
builder.Append("Id:" + ID +
" ,Msg: " + MSG +
" ,Status: " + STATUS);
return builder.ToString();
}
}
}
RTDSample – Loading Data from Space to Excel
using System;
using System.Collections;
using System.Text;
using System.Runtime.InteropServices;//<-- For our interop attributes.
using Microsoft.Office.Interop.Excel;
using GigaSpaces.Core;
using GigaSpaces.Core.Exceptions;
using GigaSpaces.Core.Events;
using System.Collections.Generic;
using System.Configuration;
//-----------------------------------------------------------
// This class demonstrate how to build a RTD wrapper to "push" data from
// GigaSpaces to the Excel spreadsheet
//
// We show how to register on a notification of a tick change, and then push
// the tick value to the Excel
//
// Usage of the RTD function within Excel:
// =RTD("GSFeader",,[the tick symbol we want to monitor])
//
//-----------------------------------------------------------
namespace GSStreamer
{
[ComVisible(true), ProgId("GSFeeder")] //the name of the progId parameter in the RTD excel function
public class GSStreamer : IRtdServer//this is the interface any C# class must implement to become a RTD
{
private IRTDUpdateEvent _xlRTDUpdate;
private ISpaceProxy _proxy;
private Dictionary<int, TopicTick> _topicIDTable; //used to map between the Excel cellID and the returned data
private Dictionary<string, TopicTick> _tickTable;
public GSStreamer()
{
_topicIDTable = new Dictionary<int, TopicTick>();
_tickTable = new Dictionary<string, TopicTick>();
}
#region IRTD Members
//this method is called by Excel when the RTD server start (the first time the workbook calls =RTD(...)
//here we should creat the connection to the space
public int ServerStart(IRTDUpdateEvent CallbackObject)
{
// Hold a reference to the callback object.
_xlRTDUpdate = CallbackObject;
bool isConnected = SpaceInit();
//All is well, return 1.
return (isConnected ? 1 : 0);
}
public void ServerTerminate()
{
// Clear the RTDUpdateEvent reference.
_xlRTDUpdate = null;
}
//this method is called by Excel for every =RTD(...) execution
//the topicID is a unique identifier for every cell in the excel, we use it to map where to notify back to
public object ConnectData(int topicID, ref Array RTDparms, ref bool getNewValues)
{
string symbol = (string)RTDparms.GetValue(0);
// Reading from the Space
string tickInfo = ReadTick(symbol);
// Registering for notifications using the space proxy
TickInfo.TickInfo notifyTemplate = new TickInfo.TickInfo();
notifyTemplate.Symbol = symbol;
IEventRegistration eventReg = _proxy.DefaultDataEventSession.AddListener<TickInfo.TickInfo>(notifyTemplate, Space_TickChanged);
//creating a new topic (which has a referance to the listner) and store it locally
TopicTick tp = new TopicTick(topicID, symbol, eventReg);
if (!_tickTable.ContainsKey(symbol))
_tickTable.Add(symbol, tp);
if (!_topicIDTable.ContainsKey(topicID))
_topicIDTable.Add(topicID, tp);
return (object)tickInfo;
}
//this method is called by the Excel when a user erase and RTD method from a given cell.
public void DisconnectData(int topicID)
{
try
{
TopicTick tp = _topicIDTable[topicID];
_topicIDTable.Remove(tp.TopicID);
_tickTable.Remove(tp.symbol);
_proxy.DefaultDataEventSession.RemoveListener(tp.EventReg);
}
catch (Exception ex)
{
ex.ToString();
}
}
public Array RefreshData(ref int topicCount)
{
// This method returns a two-dimensional array of Variant values.
// The first dimension represents a list of topic IDs;
// these topic IDs map to the TopicID parameter in the ConnectData method above.
// This is how Excel associates topics with data.
// The second dimension represents the values associated with the topic IDs.
object[,] result = new object[2, _topicIDTable.Count];
//build a array of all the topics that changed to send the Excel
foreach (TopicTick tp in _topicIDTable.Values)
{
if (tp.Changed)
{
tp.Changed = false;
result[0, topicCount] = tp.TopicID;
result[1, topicCount] = ReadTick(tp.symbol); // we can use the symbol attribute: symbol.Pono instead of reading from space
topicCount++; //Tell Excel how many topics we updated.
}
}
//Return the updates.
return result;
}
public int Heartbeat()
{
return 1; // the RTD server is up and running, 0 indicates error
}
#endregion
//this method is invoked when a tick has changed
private void Space_TickChanged(object sender, SpaceDataEventArgs<TickInfo.TickInfo> e)
{
//bookmark the tick that was changed
_tickTable[e.Pono.Symbol].Changed = true;
//Tell Excel that we have updates,
//as a result the excel will call the RefreshData function
_xlRTDUpdate.UpdateNotify();
}
private bool SpaceInit()
{
try
{
string url = ConfigurationManager.AppSettings["SpaceUrl"]; //an environment variable read from the excel.exe.config file
_proxy = SpaceProxyProviderFactory.Instance.FindSpace(url);
_proxy.OptimisticLocking = true;
return true;
}
catch (Exception ex)
{
ex.ToString();
return false;
}
}
private string ReadTick(String symbol)
{
// reading the Person Data from the Spaces
TickInfo.TickInfo template = new TickInfo.TickInfo();
template.Symbol = symbol;
TickInfo.TickInfo tick = _proxy.Read<TickInfo.TickInfo>(template);
if (tick == null)
return ("Not Exist");
else
return tick.ToString();
}
private class TopicTick
{
public int TopicID;
public bool Changed;
public string symbol;
public IEventRegistration EventReg;
public TopicTick(int topicId, string symbol, IEventRegistration eventReg)
{
this.TopicID = topicId;
this.Changed = false;
this.symbol = symbol;
this.EventReg = eventReg;
}
}
}
}
TickInfo
using System;
using System.Collections.Generic;
using System.Text;
using GigaSpaces.Core.Metadata;
namespace TickInfo
{
// SpaceClass(IncludeProperties = IncludeMembers.Public) instructs the space to take only public properties
// SpaceClass(IncludeFields = IncludeMembers.None) instructs the space not to take any fields
[SpaceClass(IncludeProperties = IncludeMembers.Public, IncludeFields = IncludeMembers.None)]
public class TickInfo
{
private string _symbol;
private double _open;
private double _close;
private double _last;
// SpaceID is used to indicate a unique field value. This field value is used to generate the Entry UID
// SpaceProperty(Index = SpaceIndexType.Basic) is used indicate an indexed field
[SpaceID, SpaceRouting, SpaceProperty(Index = SpaceIndexType.Basic)]
public string Symbol
{
get { return _symbol; }
set { _symbol = value; }
}
[SpaceProperty(NullValue = -1)]
public double Open
{
get { return _open; }
set { _open = value; }
}
[SpaceProperty(NullValue = -1)]
public double Close
{
get { return _close; }
set { _close = value; }
}
[SpaceProperty(NullValue = -1)]
public double Last
{
get { return _last; }
set { _last = value; }
}
public TickInfo()
{
this._open = -1;
this._close = -1;
this._last = -1;
}
public override string ToString()
{
StringBuilder builder = new StringBuilder();
builder.Append(" Symbol: " + Symbol);
builder.Append(" Open: " + Open.ToString());
builder.Append(" Close: " + Close.ToString());
builder.Append(" Last: " + Last.ToString());
return builder.ToString();
}
}
}