While preparing for an integration project with some of David Heckerman's biology-related work, I thought of educating myself on how Excel 2007, .NET, and Web Services could be combined to play together.
A lot of scientists use Excel in their day-to-day activities. So it makes sense to make functionality exposed through Web Services available as custom Excel functions, which can be added through "automation addins". It is possible to write such addins as .NET classes and exposed as COM servers through COM Interop.
The example below shows an Excel worksheet where the user can define the symbol for two currencies ('from' and 'to') and the ammount to be converted. The custom function retrieves the exchange rate between the two given currencies from the Web Service. If you change the values in the B1 or B2 cells, the conversion will be recalculated, as you'd expect. The 'ExchangeRate' function is the name of the custom function.
Of course, there is the BIG isssue of synchrony. Excel doesn't know that the call to the function may take a long time. Although there is a way to enable multithreaded evaluation even for custom functions, this is not possible from within .NET (or, at least, I am not aware of how). Here's the code. Once you build your DLL, you can register it by going to Excel->Options->Addins->(press the "Go" button)->Automation and locate the class. Make sure you say 'no' to the question you'll get about "mscorlib.dll". If you have any problems, drop me a line.
//------------------------------
// Copyright (c) Microsoft Corporation. All rights reserved.
//------------------------------
namespace Microsoft.TechnicalComputing.Sample
{
using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
// Added through "Web References"->Add (http://www.webservicex.net/CurrencyConvertor.asmx?WSDL)
using Microsoft.TechnicalComputing.Sample.net.webservicex.www;
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
[Guid(ExchangeRateSample.TypeGuid)]
[ProgId("Microsoft.ExchangeRate")]
publicclassExchangeRateSample
{
public ExchangeRateSample() {}
internalconststring TypeGuid = "13600B26-D84B-4da2-98FE-66FACC5BD2C7";
staticstring SubKeyName = @"CLSID\{" + ExchangeRateSample.TypeGuid + @"}\Programmable";
[ComRegisterFunction]
publicstaticvoid RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(ExchangeRateSample.SubKeyName);
}
[ComUnregisterFunction]
publicstaticvoid UnRegisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(ExchangeRateSample.SubKeyName);
}
publicdouble ExchangeRate(string from, string to)
{
CurrencyConvertor svc = newCurrencyConvertor();
return svc.ConversionRate(
(Currency)Enum.Parse(typeof(Currency), from),
(Currency)Enum.Parse(typeof(Currency), to));
}
}
}
3 responses to “User-Defined Functions in Excel 2007 and Web Services”
“Although there is a way to enable multithreaded evaluation even for custom functions, this is not possible from within .NET”
Um… I don’t follow this. Do you mean that multithreaded evaluation can be enabled using some feature in Excel? Or …?
And, could you elaborate on what .NET doesn’t allow? I just don’t follow which part isn’t possible.
Hi Elis,
Excel is a great example of dataflow evaluation. You change a cell, and only the necessary computations, which depend on the value of that cell, get evaluated. The evaluation of parts of the dataflow graph can happen concurrently. Excel has introduced this functionality (don’t know since which version).
I read somewhere on the Web that in order for a user defined function to participate in a concurrent evaluation, it has to tell Excel that is capable of performing its operations in a sidefect-free manner. You see, a UDF can depend on state and so multithreaded execution may not be safe.
Apparently COM automation addins in C++ have a way to express that but i don’t think there is a way to do the same from .NET and COM Interop. I may be wrong because I didn’t research the issue further.
Do let me know if you know something more on the subject. I’d be interested to know.
Cheers,
.savas.
Cool.. sort of relates to something I’ve been playing with lately; procedural languages inside databases. So far I’ve gotten postgresql to run Ruby programs; I can write a Ruby program that will fetch something from a webservice (e.g. a currency as in your example), and define it as a “stored procedure” of sorts. That way I can do “select ruby_get_currency(“USD”)”. Or, perhaps more interestingly I can set whatever function I want to be triggered by an insert/update/delete operation. Which of course again can call other functions (directly, or by adding/removing rows/executing statements). Naturally, these don’t have to be the same language (postgresql supports a whole bunch of languages); so I can write my program as a set of procedures in Ruby/Java/PHP/Python/whatever, then orchestrate the execution of the program by means of some SQL (doesn’t even have to be that much SQL: “select init()” will do). Naturally, as I’m able to “call out” from my Ruby program, I can call a webservice remotely, which can then make a database call, which can then invoke more functions (defined by me, or others..).
Dunno what the use is of course, but kinda cool: “Database Oriented Programming”.. Ha! Maybe it can even do multi-query optimizations? I guess you wouldn’t need to synchronize your programs either (handled by the db).
Anyways, hope you’re well. Speak soon.
Cheers,
E.