An Excel RTD Server for Yoctopuce sensors

An Excel RTD Server for Yoctopuce sensors

Almost two years ago, we showed you how to access Yoctopuce devices from Excel by creating your own Excel add-in using Excel-DNA. Today we are going to show something simpler and even better: read any Yoctopuce sensor from Excel, with instant live refresh, without even recompiling a single line of code...




The first enhancement compared to our previous blog post comes directly out of Yoctopuce programming API: since the beginning of the year, it is possible to use the YSensor abstract class to read any sensor, rather than having to use a specific sensor class. This is what will make it possible for you to test our code with any Yoctopuce sensor, without even recompiling our Excel Add-In.

The other improvement idea comes from one of our customer: using a RealTimeData Server (RTD Server in short) for Excel rather than using explicit VisualBasic function calls. A RTD server appears to the user like a simple Excel function, but it is a function that can spontaneously change its value and notify Excel from the change so that all dependent cells are automatically computed again. This is like a direct transposition in Excel of the value change callbacks in our API. And if course we will use a callback function to implement the RTD server, thereby providing a high refresh rate.

The demo


Want to give it a try right now ? First make sure you have a recent .NET Framework installed. Then open Excel, enable "developer" menu and select "Add-ins". Click on "browse..." button to find out which directory Excel is using for Add-Ins. Copy the content of this zip file into it this directory, and select the YoctoRTD.xll plug-in for activation. That's it.

Installing our Excel Add-In
Installing our Excel Add-In



If you have a Yoctopuce sensor connected to your PC, you can now add in any cell the formula


=YSensorValue("any")
 


to see the current value of the first sensor found. And the value changes automagically. Instead of "any", you can add the unique identifier of any sensor function (for instance "LIGHTMK2-24F0F.lightSensor") or the corresponding logical name, if you have configured one. By default, Excel only refresh dynamic values every 2 seconds, but our plug-in adds a menu to easily raise the refresh rate up to 100Hz. Have a look:

  



The implementation


If you are interested to learn how this works and to change this example, here are a few explanations about the code. As in our previous Add-in example based on Excel-DNA, we have started by creating a C# "Class Library" project in VisualStudio Express 2012 (the full project is available for download in this zip file). Apart from the reference to ExcelDNA.Integration and the Yoctopuce API base file yocto_api.cs, we only had to add a single source file to implement the RTD server.

The C# .NET project structure
The C# .NET project structure



Here are a few words about the most important methods found in YoctoRTDServer.cs:

ServerStart()

This is the method invoked by Excel when the user adds for the first time a reference to YSensorValue(). We have to use it to initialize Yoctopuce library, including a timer that will periodically give control to the Yoctopuce library to handle USB events.


    string errmsg = "";
    YAPI.RegisterLogFunction(log);
    if (YAPI.RegisterHub("usb", ref errmsg) == YAPI.SUCCESS) {
        Console.Beep();
        log("USB ready");
        _notifyUpdates = true;
        _timer = new Timer(delegate {
            string dummy = "";
            YAPI.HandleEvents(ref dummy);
        }, null, 0, 10);
    }
    else log(errmsg);
 



ServerTerminate()

This is the opposite method, invoked when the last reference to YSensorValue() is removed (or when the document is closed).


    if (_timer != null) {
        Console.Beep();
        _timer.Change(Timeout.Infinite, Timeout.Infinite);
        _timer = null;
        _notifyUpdates = false;
        YAPI.UnregisterHub("usb");
        YAPI.FreeAPI();
        log("USB closed");
    }
 



ConnectData()

This method is invoked each time that a reference to YSensorValue() is added to the Excel sheet. This method must enable the value change callback for the selected sensor. Excel provides a numeric topicId for the request, that we will have to use each to we need to announce a new value to Excel. So we store this identifier directly in the YSensor object returned by the Yoctopuce API, using the userData attribute which is exactly intended for this kind of purpose:


    sensor = YSensor.FindSensor(sensorId);
    if (!sensor.isOnline()) {
        log("Sensor " + sensorId + " is not online, check USB cable");
    }
    sensor.set_userData(new object[] { this, (int)topicId });
    sensor.registerValueCallback(sensorValueChangeCallBack);
 


The value change callback will only have to save each and every new value reported by the sensor into an internal dictionary containing all "changes to be notified", and to notify Excel (if not yet done) that there are new values to be loaded:


    srv._topics[topicId] = value;
    if (srv._notifyUpdates) {
        srv._notifyUpdates = false;
        srv._callback.UpdateNotify();
    }
 



RefreshData()

This is the method invoked by Excel to retrieve value changes when notified of new values. To save Excel from unnecessary cascaded computations, we only include in this list the sensors whose value has indeed changed, using our dictionary of "changes to be notified".


    // Count changed values
    topicCount = 0;
    foreach(var value in _topics.Values) {
        if (value != "") topicCount++;
    }
    // Create an array of changes to report
    object[,] results = new object[2, topicCount];
    int idx = 0;
    double floatVal = 0;
    foreach (KeyValuePair<int, string> pair in _topics) {
        if (pair.Value != "") {
            results[0, idx] = pair.Key;
            results[1, idx] = double.Parse(pair.Value);
            idx++;
        }
    }
    // Clear reported values from the dictionary
    for (idx = 0; idx < topicCount; idx++) _topics[idx] = "";
    // Enable further updates now that this one is processed
    _notifyUpdates = true;

    return results;
 



That's about it, other parts of YoctoRTDServer.cs are mostly cosmetic stuff to create the custom menu. The real magic that makes all this so easy is provided by Excel-DNA, which is definitely a very good library!

Add a comment 4 comments
Back to blog



1 - Santander Sunday,april 19,2015 1H30

This is great. Will it work on Excel for Mac (Office 2011 for Mac)?

2 - mvuilleu (Yocto-Team)Sunday,april 19,2015 19H12

@Santander: Unfortunately not, because Microsoft has not ported to Mac any of the OLE classes that makes this magic possible...

3 - chan Wednesday,april 13,2016 8H32

Nice!!

Im not a programming person, but i would like to ask whether its possible to get delay temperature value let say by 1 minute. Meaning, in this moment, the temperature value displayed is 1 minute expired instead of current one.

Thanks

4 - mvuilleu (Yocto-Team)Wednesday,april 20,2016 10H10

@chan: do you mean by averaging over the last minute ?

Yoctopuce, get your stuff connected.