Excel RTD Server implementation in C#......Part 2

on
This is a follow up to Excel RTD Server implementation in C#......Part 1


Update: This code (lightly modified) is now on Github at https://github.com/AlignmentSystems/ExcelRTDSimple

So – if you want to try this at home, you need to either use the same development environment or else adapt.  The environment I am using is:
  • Excel 2013 – 64bit install (Version 15)
  • Visual Studio 2013 64bit
  • Windows 8.1 Pro 64bit
To check, you will want to see this:
And:
So, to get started, start up Visual Studio AS ADMINISTRATOR and create a new C# class library project.  Note that if you don’t run VS2013 as admin you’ll get into trouble later.
Now add two classes to the project – a regular code class called “n2RTD” and an installer class “Installer1”.
In the project properties you want something that looks like this:



In n2RTD add the following using directives:

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Diagnostics;
using System.IO;
using System.Runtime.InteropServices;
using System.Runtime.Serialization;using System.Runtime.Serialization.Json;
using XL=Microsoft.Office.Interop.Excel;

Create a namespace – I used

namespace alignmentsystems.n2excel

You may want to use another name…

We will create the most basic RTD functionality I can imagine, a simple implementation that starts a counter and increments upwards by 1 at a time.  The point here is not to create a complex RTD server.  In a later post, that is what I will do…

Within namespace alignmentsystems.n2excel

Add the following class



    public class IncrementUpwards

    {

        public int CurrentValue { get; set; }

    }


And then add this:
  [ComVisibleAttribute(true)]

    [
    Guid("DDAFE6DE-06B7-4FAC-890F-93C1522E6CB2"),
    ProgId("alignmentsystems.node2rtd"),
    ]

    public class n2RTD: XL.IRtdServer
    {

        private readonly Dictionary<int, IncrementUpwards> _topics = new Dictionary<int, IncrementUpwards>();
        private Timer _timer;
        public dynamic ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues)

        {

            var start = Convert.ToInt32(Strings.GetValue(0).ToString());

            GetNewValues = true;

            _topics[TopicID] = new IncrementUpwards { CurrentValue = start };

            return start;

        }

        public void DisconnectData(int TopicID)

        {
            _topics.Remove(TopicID);
        }

        public int Heartbeat()

        {
            return 1;
        }

        public Array RefreshData(ref int TopicCount)

        {
            var data = new object[2, _topics.Count];
            var index = 0;
            foreach(var entry in _topics)
            {
                ++entry.Value.CurrentValue;
                data[0, index] = entry.Key;
                data[1, index] = entry.Value.CurrentValue;
                ++index;
            }
            TopicCount = _topics.Count;
            return data;
        }
        public int ServerStart(XL.IRTDUpdateEvent CallbackObject)
        {
            _timer = new Timer(delegate { CallbackObject.UpdateNotify(); }, null, TimeSpan.Zero, TimeSpan.FromSeconds(3));
            return 1;
        }

        public void ServerTerminate()
        {
            _timer.Dispose();
        }
    }

Let’s just explain what this does before proceeding.  As you should now, this line

public class n2RTD: XL.IRtdServer

Means that the class n2RTD will implement the interface IRtdServer from Microsoft.Office.Interop.Excel.

So, when we add that line of code we are presented with the option to create the boilerplate code for the interface.

public dynamic ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues)
public void DisconnectData(int TopicID)
public int Heartbeat()
public Array RefreshData(ref int TopicCount)
public int ServerStart(XL.IRTDUpdateEvent CallbackObject)
public void ServerTerminate()
We add in the following variables:
private readonly Dictionary<int, IncrementUpwards> _topics = new Dictionary<int, IncrementUpwards>();
private Timer _timer;
These are used within the code to store data and fire a timer to create events that are sent to Excel to update the user interface.
In ConnectData we add the following:
 
var start = Convert.ToInt32(Strings.GetValue(0).ToString());
GetNewValues = true;
_topics[TopicID] = new IncrementUpwards { CurrentValue = start };
return start;
Simply put, this adds the value from the first parameter within the Excel RTD formula to a collection with a key in use of TopicID.
In DisconnectData we add this line:
_topics.Remove(TopicID);

This removes the collection member with the key TopicID
In Heartbeat we add this:
return 1;

Which notifies Excel that the server is working properly.

The most code in this class is within RefreshData where we have the following

      var data = new object[2, _topics.Count];
            var index = 0; 
            foreach(var entry in _topics)
            {
                ++entry.Value.CurrentValue;
                data[0, index] = entry.Key;
                data[1, index] = entry.Value.CurrentValue;
                ++index;
            }
            TopicCount = _topics.Count;
            return data;

This iterates through the _topics collection and increments every value up by one.
In ServerStart we add the following:

_timer = new Timer(delegate { CallbackObject.UpdateNotify(); }, null, TimeSpan.Zero, TimeSpan.FromSeconds(3));

return 1;

This creates a timer instance and sets the interval to every three seconds which is then passed to the CallbackObject.UpdateNotify() method to let Excel know that there is an update waiting for it.
Finally in ServerTerminate we have one line of code           

_timer.Dispose();

Which frees up the resources associated with the instance of the Timer.
In installer1 add the following:
namespace alignmentsystems.n2excel

{

    [RunInstaller(true)]
    public partial class RTDServerInstaller  : System.Configuration.Install.Installer
    {
        public RTDServerInstaller()
        {
            InitializeComponent();
        }

        [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Demand)]
        public override void Commit(System.Collections.IDictionary DeploymentState)
        {
            base.Commit(DeploymentState);
            DirectorySecurity dirSec = Directory.GetAccessControl(DeploymentState["DeploymentDirectory"].ToString());
            FileSystemAccessRule fsar = new FileSystemAccessRule("Users", FileSystemRights.FullControl, InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow);
            dirSec.AddAccessRule(fsar);
            Directory.SetAccessControl(DeploymentState["DeploymentDirectory"].ToString(), dirSec);
        }

        [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Demand)]

        public override void Install(System.Collections.IDictionary stateSaver)
        {
            base.Install(stateSaver);
            stateSaver.Add("DeploymentDirectory", Context.Parameters["rtd_DeploymentDirectory"].ToString());
            RegAsm("/codebase");
        }

        [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Demand)]

        public override void Uninstall(System.Collections.IDictionary stateSaver)
        {
            RegAsm("/u");
            base.Uninstall(stateSaver);
        }

        private static void RegAsm(string parameters)
        {
            var deployment_directory_partial = Path.GetFullPath(Path.Combine(RuntimeEnvironment.GetRuntimeDirectory(), @"..\.."));
            var target_regasm_path_array = new[]
            {
                string.Concat(deployment_directory_partial, "\\Framework\\", RuntimeEnvironment.GetSystemVersion(), \\regasm.exe),
                string.Concat(deployment_directory_partial, "\\Framework64\\", RuntimeEnvironment.GetSystemVersion(), \\regasm.exe)
            };

            var pathToCompiledCode = Assembly.GetExecutingAssembly().Location;
            foreach (var target_regasm_path in target_regasm_path_array)
            {
                if (!File.Exists(target_regasm_path))
                    continue;

                var process = new Process
                {
                    StartInfo =
                    {
                        CreateNoWindow = true,
                        ErrorDialog = false,
                        UseShellExecute = false,
                        FileName = target_regasm_path,
                        Arguments = string.Format("\"{0}\" {1}", pathToCompiledCode, parameters)
                    }
               };

                using (process)
                {
                    process.Start();
                    process.WaitForExit();
                }
            }
        }
    }
}

This will work on 32bit and 64bit versions of Excel, but I have only tested on 64 bit versions.
The last few things to do are:
  1. Set the deployment platform to x64
  2. Do NOT set the build event property “Register for COM interop”.  This is not needed.
  3. In the project property screen set “Sign the assembly” to true.
  4. Build the project
  5. Start an instance of a command prompt AS ADMINISTRATOR
  6. In the command window navigate to  c:\Windows\Microsoft.NET\Framework64\v4.0.30319
  7. In the command window execute regasm "path\to\bin\AlignmentSystemsRTD.dll" /codebase
  8. Start up an instance of Excel and in a cell type =RTD("alignmentsystems.node2rtd",,1234)
  9. Hit enter on that cell and wait for three seconds or so.  You’ll see the cell update to 1235 and so on.
That’s the most basic C# implementation I could think of.  In a later post I will combine this with a node.js, express, socket.io, primus.io webserver that serves up JSON.  The C# code will then have to connect to the webserver, receive the JSON, parse it out and send it to Excel.  But that’s for another day…

 

Comments

Post a Comment