This project has moved. For the latest updates, please go here.

Excel - DNA Reference

Data type marshaling

The allowed function parameter and return types are:
  • Double
  • String
  • DateTime -- returns a double to Excel (maybe string is better to return?)
  • Double[] -- if only one column is passed in, takes that column, else first row is taken
  • Double[,]
  • Object
  • Object[] -- if only one column is passed in, takes that column, else first row is taken
  • Object[,]
  • Boolean (bool) -- returns an Excel bool (maybe string is better to return to Excel?)
  • Int32 (int)
  • Int16 (short)
  • UInt16 (ushort)
  • Decimal
  • Int64 (long)

incoming function parameters of type Object will only arrive as one of the following:
  • Double
  • String
  • Boolean
  • ExcelDna.Integration.ExcelError
  • ExcelDna.Integration.ExcelMissing
  • ExcelDna.Integration.ExcelEmpty
  • Object[,] containing an array with a mixture of the above types
  • ExcelReference -- (Only if AllowReference=true in ExcelArgumentAttribute causing R type instead of P)

function parameters of type Object[] or Object[,] will receive an array containing a mixture of the above types (excluding Object[,])

return values of type Object are allowed to be:
  • Double
  • String
  • DateTime
  • Boolean
  • Double[]
  • Double[,]
  • Object[]
  • Object[,]
  • ExcelDna.Integration.ExcelError
  • ExcelDna.Integration.ExcelMissing.Value // Converted by Excel to be 0.0
  • ExcelDna.Integration.ExcelEmpty.Value // Converted by Excel to be 0.0
  • ExcelDna.Integration.ExcelReference
  • Int32 (int)
  • Int16 (short)
  • UInt16 (ushort)
  • Decimal
  • Int64 (long)
otherwise return #VALUE! error

return values of type Object[] and Object[,] are processed as arrays of the type Object, containing a mixture of the above, excluding the array types.

Special return values

The following invalid return values, are returned to Excel as indicated
  • Object[0] => #VALUE
  • Object[0.0] => #VALUE
  • String.Empty => “”
  • ExcelEmpty.Value => 0
  • null => #NUM

Public types in ExcelDna library

Intended for use within user code (all in the namepace ExcelDna.Integration) are the following:

Attributes

  • ExcelFunctionAttribute - for user-defined functions
Name
Description
Category (by default the name of the add-in)
HelpTopic
IsVolatile (! suffix)
IsMacroType (# suffix)
IsThreadSafe (??? suffix)
IsClusterSafe (& suffix)
  • ExcelArgumentAttribute - for the arguments of user-defined functions
Name
Description
AllowReference (R type) - Arguments of type object may receive ExcelReference.
  • ExcelCommandAttribute - for macro commands
Name
Description
HelpTopic
ShortCut (does not seem to work ?)
MenuName (default is library name)
MenuText
IsHidden
  • ExcelComClassAttribute

Helper classes

  • ExcelReference - contains a sheet reference. Get/SetValue to read/write to the cells.
  • ExcelError - an enum listing the different Excel errors
  • ExcelDnaUtil - contains Application property (for COM access), WindowHandle and IsInFunctionWizard, * * ExcelVersion, ExcelLimits.
  • ExcelCommandBarUtil - has LoadCommandBars method to laod command bar xml from a string.
  • CustomTaskPaneFactory - creates CustomTaskPanes.

Wrapper for the Excel 97 / Excel 2007 SDK API

  • XlCall class
XlCall.Excel wraps Excel4/Excel12 (but easy to call), also constants for all the API functions and commands.
  • XlCallException - is thrown when the call to Excel fails.
  • XlCall.TryExcel - does not throw exception on fail, but returns an XlCallReturn enum value.
  • ExcelDna.Integration.Integration contains the static method 'RegisterMethods' which allow dynamic registration of methods.

Excel COM interface access

ExcelDna.Integration.ExcelDnaUtils.Application returns the Excel Application COM object.
From VB this can be used late-bound quite easily.
From C# 4 late-binding through the dynamic 'type' is recommended.

Ribbon

To support the Excel 2007 / 2010 Ribbon interface, the addin (in a .dna file or ExternalLibrary) must contain at least one public class that is a direct subclass of ExcelDna.Integration.CustomUI.ExcelRibbon.
This class can also implement ExcelDna.Integration.IExcelAddIn, but need not.
For each such class, ExcelDna will dynamically register and load a COM add-in in the AutoOpen call (after calling all the IExcelAddin.AutoOpen methods). This will trigger the loading of the Ribbon ui, and Excel calls the ExcelRibbon.GetCustomUI method. This is a virtual method, with a default implementation that retrieves the ribbon xml from the .dna file. An add-in can override the default GetCustomUI method to explicitly return an xml appropriate string. All callback methods that the Ribbon calls must be implemented as public methods in the class derived from ExcelRibbon.

My goal for the multi-version customUI support is to allow you to create a single add-in that contains UI customization for each version. The idea is not to make a unified customization layer - the add-in could contain different code for different versions.

Custom Task Panes

Support under ExcelDna.Integration.CustomUI.
The CustomTaskPane class defines the interfaces related to CTP's.
A CTP must contain a UserControl (derived from System.Windows.Forms.UserControl).
Create a new CustomTaskPane containing an instance of MyUserControl by calling:
    CustomTaskPane myCTP = 
        CustomTaskPaneFactory.CreateCustomTaskPane(typeof(MyUserControl), myTitle);

COM server support

COM visible classes in ExternalLibrary tags marked ComServer='true', and COM visible classes that implement IRtdServer can be activated through the .xll directly. Even if the add-in is not loaded in Excel, such objects can be created in VBA.

These classes are (persistently) registered by calling "Regsvr32 <MyAddin>.xll" or by ComServer.DllRegisterServer(), and
unregistered by "Regsvr32 /u <MyAddin>.xll or by ComServer.DllUnregisterServer().

Such classes can be accessed directly as RTD servers or from VBA using CreateObject("MyServer.ItsProgId"), and will be loaded in the add-in's AppDomain.
(The add-in need not be loaded for registered classes to be accessed through COM.)

A type library (.tlb) can be created for the assembly using tlbexp.exe, and will be registered if available. If the assembly is packed in the .xll, the type library will be packed too.

Packing

ExcelDnaPack can pack the .dna files and dependent assemblies into a single .xll file.
The Samples\Packing directory has a number of samples of how to use the packing feature.
To run the samples, run PackAll.bat - the packed libraries are placed into the out directory.

Default references and imports

A reference to the ExcelDna.Integration assembly in the .xll file that is
loading a .dna file is always added when the .dna file is compiled.

In addition, the following references are added to each project, unless a DefaultReferences="false" attribute is set on the <Project>:
System.dll
System.Data.dll
System.Xml.dll
For RuntimeVersion="v4.0" the following are added too:
System.Core.dll
System.Data.DataExtensions.dll
System.Xml.Linq.dll
Microsoft.CSharp.dll (C# projects only)

For VB projects, the following imports are added, unless a DefaultImports="false" attribute is set on the <Project>:
Microsoft.VisualBasic
System
System.Collections
System.Collections.Generic
System.Data
System.Diagnostics
ExcelDna.Integration
For RuntimeVersion="v4.0" the following are added too:
System.Linq
System.Xml.Linq

?? Microsoft.Office.Core (for Ribbon)??

Image Resolution

Image is only used if the Path is supplied.
Image Name must match (case sensitive) the image tag in the Ribbon markup.
1. If the Path starts with "packed:" the image is retrieved from resources.
2. If the Path contains .....

Reference Resolution

The <Reference> element has Path (AssemblyPath is deprecated) and Name attributes.
When compiling at runtime, the references are resolved as follows:
1. If Path starts with "packed:" the assembly is retrieved from resources, written to a temp file, and the temp file is added to the compiler's references list.
2. Else If Path is not null and not empty, the assembly is search relative to the .dna file and the xll directory, and in the framework directory. If found, Path is added to the compiler's references list.
3. Else the Name is passed to LoadWithPartialName for resolution.
4. Otherwise the reference is ignored.

When packing (Reference elements with the Pack="true" attribute), the reference is resolved as follows:
1. If Path starts with "packed:" the reference is ignored.
2. If Path is not null and not empty, the assemlby is looked for according to the Path Resolution rules.
3. If no file is found, Assembly.Load is attempted with the Path. If the load succeeeds, the assembly location is used to locate and pack the assembly.
4. Otherwise, Assembly.LoadWithPartialName with Name is tried. If the load succeeeds, the assembly location is used to locate and pack the assembly.
5. If the assembly is still not located, the reference is ignored for packing.

Path Resolution

For assemblies and .dna files, the following path resolution is done.
Given a path containing a file name, maybe rooted, maybe with some or no directory info, we attempt to find the file as follows:
1. If the file is found at the path, we are done.
2. Check the path - if it is rooted replace directory with .dna file's directory.
3. If the path is not rooted, try the whole path relative to the .dna file location (prepend the .dna file's directory).
4. Else try 2 or 3 using current AppDomain's BaseDirectory.

ExplicitExports

If the ExplicitExports attribute is set to "true" on a Project or ExternalLibrary node, only functions and methods explicitly marked by an
ExcelFunction or ExcelCommand attribute are exported. Otherwise, all public static methods with compatible signatures are exported.

AutoOpen/AutoClose

Cleanup is only done when the add-in is removed from the add-in manager.
When File->Open is used to reopen the .xll, it is closed and opened, causing the .dna file to be re-read.

Last edited Jul 11, 2013 at 10:38 AM by govert, version 9