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
If you have a Yoctopuce sensor connected to your PC, you can now add in any cell the formula
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
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.
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).
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:
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:
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".
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!
