Introduction

QuantSA uses ExcelDNA to expose functions to Excel, the add-in includes Excel-DNA IntelliSense which gives great feedback to users while they type in QuantSA functions (see example below).

ExcelDna-intellisense.JPG

The Excel layer should be a wrapper layer for the rest of the library as far as possible. No business logic should be implemented in this layer except to the extent that input validation requires some business logic.

Technically any public static method can be exposed by giving it the ExcelDNA ExcelFunctionAttribute, however it is preferable to rather use the attribute QuantSAExcelFunctionAttribute. The latter allows for the function visibility to be controlled at runtime and is extensible for future features.

Function Attributes

When exposing a function to Excel it must have the QuantSAExcelFunctionAttribute and set:

  • Description
  • Name - must start with QSA in the main library
  • Category - should match the name of the public static class in which the function is contained, which should in turn match the location of the underlying function in the main library
  • ExampleSheet - a sheet that shows a user how to create the necessary inputs for the function and call it.
  • IsHidden - The default hidden state of the function. This can be overridden at runtime. Most functions should be false. If the function is sufficiently obscure that it should be hidden from most users then consider putting it in an add-in. In future if we need to deprecate a function it will make sense to hide it so that new users don’t start using it.

Parameter Attributes

Each parameter should have the QuantSAExcelArgument attribute and set the description of the parameter as would be useful to a user of the function. This will in general not be the same description in the documentation of the method being called in the main library which should be written to be useful to someone developing in the QuantSA solution.

Argument attributes can actually be either ExcelDNA’s ExcelArgumentAttribute or QuantSA’s QuantSAExcelArgumentAttribute. The latter extends the former and adds a field Default that can be used by the autogenerated data conversion methods to provide a default value.

Data conversion

Functions exposed to excel can have any inputs including C# reference types and nullable primitives. Nullable primitives are the preferred way to take primitive inputs since they are guaranteed to not be sent in inadvertently with a non null default value. There are three ways that data can be converted from Excel types to the inputs required for your functions:

  • Data types handled natively by ExcelDNA. Examples are doubles, strings and booleans.
  • Data types for which a converter has been written. The converters can be implemented anywhere in the QuantSA.Excel.Functions assembly or in a Plug In assembly. Converters are any classes that implement IInputConverter or IOutputConverter from QuantSA.Excel.Shared.
  • All other types are handled in Excel as references with their instances stored on a global object map in QuantSA.
Tags: developers