ExcelDNA Custom Marshalling

[Michael]

Hi,

I wanted to reintroduce the subject of custom marshalling. I know that
there have been several discussions in the past regarding custom
marshalling and its use in ExcelDNA.

I spent a couple of hours this week prototyping an excel method
generator which used ExcelDNA.

Background:
First a bit of background would be in order. We use ExcelDNA mixed
with COM (legacy) in our front office quant library. We also make use
of handles which reference complicated objects (see quantlib excel for
examples of what this is).
* I wanted to avoid the need to create ExcelDNA methods that would use
boiler plate code to do similar code.
* I wanted to make testing easier by testing the functionality, not
the marshalling

The existing code:
public static double GetInterestRate(string yieldCurve, object
dayBasis) {

// check yield curve exists, else throw exception


}

The Goal:
public class YieldCurve {

public double InterestRate( DateTime date, DayBasis dayBasis
DayBasis.Actual_365) {
}

}

regards
Michael

[Govert van Drimmelen]

Hi Michael,

I still have vague long-term plans to put in place a flexible custom
marshaling story, with some other plumbing changes, but there is
nothing as yet.

Suppose your code looks like this:

public class YieldCurve {
public double InterestRate( DateTime date,

DayBasis dayBasis)
{ ... } }

And you suggest the (generated?) worksheet function should look like
this:

public static double GetInterestRate(string yieldCurve,
object dayBasis)
{
// check yield curve exists, else throw exception
}

How would you like to specify the implementation of the generated UDF?
* How do you suggest to get from 'string yieldCurve' to a YieldCurve
object?
- Should we just 'new one' based on a constructor that takes a
string?
- Should the string be a key into a Dictionary that is populated
otherwise (created how?)

* How would you suggest telling Excel-DNA about the mapping - an
attribute, or some registration in the AutoOpen?

I think the heart of the problem is not automatically generating the
wrappers (which I'm still keen to do in future), but getting a good
understanding of the implementation patterns for this. Apart from
generating the (likely to be very thin) wrapper functions, most of the
work can already be done in your add-in, and indeed many users are
doing it. But the implementations I've seen suggest there are quite a
few different ways of looking at the problem.

In the end I am keen to do the custom marshaling, but it is likely to
reduce your code by one or two lines per function, from a one- or two-
line wrapper to a one-line attribute markup.

So as a start, lets make an example that does what you like inside the
add-in, and then think about how to generate the wrappers at runtime
once we know what we need.

Regards,
Govert

[Michael]

Hi Govert,

I think I suffered from some premature posting :) ... I didn't quite
finish my post before I submitted it, so here is some additions which
I excluded. Hopefully I can answer your questions at the end.

Some obvious advantages of custom marshalling by generating wrapper
functions:
-------------------------------------------------------------------------------------
* reduce the boiler plate marshalling code needed
eg public static double DoSomething(object input) {
double myInput = 2;
if !(input is ExcelMissing) {
myInput = toDouble(input);
}
return myInput * 4;
}
* make testing easier Assert.Equal(mycomplexObject,
excelMethod(...,List<DateTime>))
* use defaults eg public double DoIt(double arr = 4)
* wrap error handlers around each parameter so you know if the
marshalling or validation failed on that parameter
* use more complex input types for my functions eg List<DateTime> or
enums (eg better type safety)
* use reverse lookups of real types from excel (eg have a method that
describes an excel function and what it really expects eg double
myfunction(mydaybasisenum dayBasis) generates a method double
myfunction([ExcelRealType(TypeName=typeof(mydaybasisenum))] object
dayBasis)
* customise the errors slightly better (I know about the hooks)
* cater for ExcelMissing, ExcelError
* use methods on objects (ie not only static methods need to be used)
* instrumentation
* logging

An example:
-----------
The existing ExcelDNA code would look like this:

[ExcelFunction....]
public static double GetInterestRate(string yieldCurve, object date,
object dayBasis) {

// if in function wizard do something else
....
// check yield curve handle exists in cache, else throw
exception
....
// convert other parameters
....
// if they are missing, use the default
// if they are arrays, perhaps convert them to lists and replace
ExcelMissing to nulls?
return yc.InterestRate(....);

}
public class Y...

My goal was to only write the bare minimum code and leave the rest to
a framework.
new code:
public class YieldCurve {

[ExcelBoundMethod]

public double InterestRate( DateTime date, DayBasis dayBasis
DayBasis.Actual_365) {

//real code goes here
}
}

The solution in brief:
----------------------
A simple solution would be to introduce compile time aspects, but as I
see there is not much support for this in C#, I made used of
Microsoft's T4 templates.

The implementation becomes fairly simple. In the T4 template, iterate
through all the methods in every assembly which have the
ExcelBoundMethod attribute (I don't use ExcelFunction, to avoid
interfering with ExcelDNA). Then generate static ExcelDNA signatures
for each method, declaring the input parameters as Excel types.
Furthermore, generate marshalling code to marshal the Excel types into
C# types.

On to some of your points:
--------------------------
I guess there are 2 ways of extending DNA. Either keeping DNA simple
and having users generate wrapper methods which get compiled into
their projects, or letting DNA provide a marshalling interface and
allowing the users to register 'marhallers' of complex types. (Also,
attributing parameters would work).

I went the way of generating method bindings via T4. The ability to
inject code into method gives you lot's of flexibility. It means I can
do funky things besides marshalling... (instrumentation, logging etc)
I like java's compile time aspect's personally.

Just quickly on Object instantiation - There are a couple of
techniques I have used or thought about.
* For simpler objects, you could easy Attribute a constructor, which
could be generated at compile time as a DNA method. This returns a
unique string id to a dictionary entry. Another technique would be to
reflect on a list of name value pairs and reflectively set on an
object (which is added to the dictionary). A further method would be
to provide factory methods for each object. Often objects in a domain
have a hierarchy anyway. eg A market may have a YieldCurve object.

It there is any interest, (and I have the time), maybe I could expand
on this a bit.

thanks
Michael Tavares

[Dimedrol]

One big topic is default parameter values. We would be able to use at
least some of ExcelDNA marshalling functionality if default values
were available. Even if missing arguments were defaulting to C#
default type values, it would be better. But ExcelMissing is a real
pain in the butt. Those who really want to handle ExcelMissing
properly might indeed import all the parameters as the C# object type,
but with the current ExcelDNA design everyone has to do it.

One very special case is passing across nullable types, such as
arrays. Personally, I don't care about ExcelMissing, and to me null
value for double[] array is as good as ExcelMissing. Actually, let me
take that back. To handle ExcelMissing I would have to marshall Excel
double[] array as C# object and then convert it, value by value, into
C# double[]. So, to me, it would be a much more sensible behavior if
ExcelDNA passed missing double[] parameter as a null C# value rather
than as anything else (and there are many different options one can
think of: ExcelMissing, ExcelError, DBNull, MyClass.Null,
YourClass.Null, ExcelSomethingElse with plans to be changed in the
future, etc., etc.).

But that is just me. I am sorry about not sounding as ecstatic about
ExcelDNA as everyone else in this forum. To rectify that, I would say
that ExcelDNA looks like it has a lot of potential, but at present
marshalling is not one of ExcelDNA's strongest features.