XLL+ Class Library (6.3)

Importing a .NET Assembly using attributes

This walkthrough demonstrates how to import a .NET assembly, and automatically generate Excel add-in functions for its methods and properties, by adding XLL export attributes to the .NET code.

You can find a copy of all the code used in this walkthrough in the Walkthroughs/NetWrapAttr folder in your XLL+ installation, along with a demonstration spreadsheet, NetWrapAttr.xls.

Please note that this walkthrough is not available under XLL+ for Visual Studio .NET. XLL+ interacts with .NET using C++/CLI, which is only available under Visual Studio 2005 and above.

Run-time requirements

Please note that if you are using Visual Studio 2005, you must have Service Pack 1 installed in order to build a valid XLL.

If the add-in fails to load into Excel at run-time, please see the technical note .NET requirements.

In order to build MyLib.dll, you need the C# project system to be installed on your computer.

Creating the add-in project

  1. From the File menu, select New and then Project to open the New Project dialog.

  2. Select the XLL+ 6 Excel Add-in project template from the list of Visual C++ Projects, and enter NetWrapAttr in the Name box. Under Location, enter an appropriate directory in which to create the project.

  3. Put a tick next to "Create directory for solution", and click OK.

  4. In the "Application Settings" page of the XLL+ AppWizard, put a tick against Common Language Runtime Support (/clr).

  5. Click Finish to create the new add-in project.

For more information about creating projects, see Creating an add-in project in the XLL+ User Guide.

Creating the imported .NET assembly

Now create a new C# project and add it to the solution.

  1. In the Visual Studio Solution Explorer window, right-click and select Add/New Project....

  2. Select the Visual C# project type in the tree on the left-hand side, and select Class Library in the list on the right.

  3. Enter MyLib for the project Name, and accept the default Location. Press OK.

  4. Open the project properties of MyLib, and, in the Build page, towards the bottom of the page, put a tick against XML documentation file.

  5. Open Class1.cs and replace all the code with the code below.

CopyC#
using System;
using System.ComponentModel;

namespace MyLib
{
    /// <summary> 
    /// Contains static calls to test parameter &amp; return types 
    /// </summary> 
    public class TestClass
    {
        // The class is static 
        private TestClass() { }

        /// <summary> 
        /// returns the concatenation of the arguments. 
        /// </summary> 
        /// <param name="a">is a string</param> 
        /// <param name="b">is a number</param> 
        /// <param name="c">is an integer</param> 
        /// <param name="d">is a boolean</param> 
        /// <returns></returns> 
        public static string T1(string a, double b, int c, bool d)
        {
            return String.Format("a={0};b={1};c={2};d={3}",
                a, b, c, d);
        }
        /// <summary> 
        /// returns an array of strings which are concatenations  
        /// of the arguments. 
        /// </summary> 
        /// <param name="a">is a vector of strings</param> 
        /// <param name="b">is a vector of numbers. It must be the same length as a.</param> 
        /// <param name="c">is a vector of integers. It must be the same length as a.</param> 
        /// <param name="d">is a vector of booleans. It must be the same length as a.</param> 
        public static string[] T2(string[] a, double[] b, int[] c, bool[] d)
        {
            if (a.Length != b.Length)
                throw new ArgumentException(String.Format("Expected {0} items for b", a.Length));
            if (a.Length != c.Length)
                throw new ArgumentException(String.Format("Expected {0} items for c", a.Length));
            if (a.Length != d.Length)
                throw new ArgumentException(String.Format("Expected {0} items for d", a.Length));
            string[] res = new string[a.Length];
            for (int i = 0; i < res.Length; i++)
                res[i] = T1(a[i], b[i], c[i], d[i]);
            return res;
        }
        /// <summary> 
        /// is a test function which adds two integers. 
        /// </summary> 
        /// <param name="a">is the first integer.</param> 
        /// <param name="b">is the second integer.</param> 
        /// <returns></returns> 
        public static int T3(int a, int b)
        {
            return a + b;
        }

        /// <summary> 
        /// This function will not be exported, because it is not marked 
        /// with a XllFunction attribute. 
        /// </summary> 
        /// <param name="a">is an argument</param> 
        /// <param name="b">is an argument</param> 
        /// <returns>the sum of the inputs</returns> 
        public static int DontExportMe(int a, int b)
        {
            return a + b;
        }

        /// <summary> 
        /// Returns the type of an argument of unknown type 
        /// </summary> 
        /// <param name="arg">A value of any type</param> 
        /// <returns>The value and type of the input</returns> 
        public static string VarTypeScalar(object arg)
        {
            return String.Format("{0} ({1})", arg, arg == null ? "none" : arg.GetType().Name);
        }

        /// <summary> 
        /// Returns the types of a vector of values of unknown type 
        /// </summary> 
        /// <param name="arg">is a vector of values of unknown type</param> 
        /// <returns>a vector containing the values and types of the inputs</returns> 
        public static string[] VarTypeVector(object[] arg)
        {
            string[] res = new string[arg.Length];
            for (int i = 0; i < arg.Length; i++)
                res[i] = VarTypeScalar(arg[i]);
            return res;
        }

        /// <summary> 
        /// Returns the types of a matrix of values of unknown type 
        /// </summary> 
        /// <param name="arg">is a matrix of values of unknown type</param> 
        /// <returns>a matrix containing the values and types of the inputs</returns> 
        public static string[,] VarTypeMatrix(object[,] arg)
        {
            string[,] res = new string[arg.GetLength(0), arg.GetLength(1)];
            for (int i = 0; i < arg.GetLength(0); i++)
                for (int j = 0; j < arg.GetLength(1); j++)
                    res[i, j] = VarTypeScalar(arg[i, j]);
            return res;
        }

        /// <summary> 
        /// Returns a result of variable type 
        /// </summary> 
        /// <param name="type">indicates the type of value to return</param> 
        /// <returns>a value of variable type</returns> 
        public static object VarRet(int type)
        {
            switch (type)
            {
                case 0: return "A string";
                case 1: return true;
                case 2: return (double)123.456;
            }
            throw new ArgumentException("type must 0, 1 or 2", "type");
        }

        /// <summary> 
        /// A public enumeration 
        /// </summary> 
        public enum Periods
        {
            Monthly = 12,
            Quarterly = 4,
            SemiAnnual = 2,
            Annual = 1
        }
        /// <summary> 
        /// Calculates the discount factor for a given period and interest rate 
        /// </summary> 
        /// <param name="rate">is the interest rate</param> 
        /// <param name="period">is a time period</param> 
        /// <returns></returns> 
        public static double DiscountFactor(double rate, Periods period)
        {
            return Math.Exp((-1.0 / ((double)(int)period)) * rate);
        }
    }
}

Adding attributes to the exported functions

To add attributes to the .NET assembly, follow these steps:

  1. Select the MyLib project in the Solution Explorer, and add a reference to the XLL+ runtime library.

    Right-click MyLib and click on "Add Reference...". Select the "Browse" page and navigate to the bin sub-directory of your XLL+ installation folder, typically [Program Files]\Planatech\XllPlus\6.0\VS8.0\bin. Select Psl.XL6.XnReflect.Runtime.dll and press OK.

  2. In Class1.cs, add the following line to the list of using statements:

    CopyC#
    using System;
using System.ComponentModel;
using XllPlus;

  3. Add a XllClassExport attribute to the class:

    CopyC#
    [XllClassExport(Category=XlCategory.UserDefined, Prefix="Test.")] 
public class TestClass
{
    ...

    This attribute will control the category and prefix of all exported methods of the class. Unless otherwise specified, exported functions will be named Test.Function (where Function represents the method name) and will appear in the "User Defined" category in the Excel Formula Wizard.

  4. Add an XllFunction attribute to the method TestClass.T1:

    CopyC#
    [XllFunction] 
public static string T1(string a, double b, int c, bool d)
{
    return String.Format("a={0};b={1};c={2};d={3}",
        a, b, c, d);
}

    This attribute will cause the function to be exported, as an Excel add-in function named Test.T1.

  5. Add an XllFunction attribute, with parameters, to the method TestClass.T2:

    CopyC#
    [XllFunction("TestArrays", Prefix="")] 
public static string[] T2(string[] a, double[] b,
    int[] c, bool[] d)
{
    ...
}

    This attribute will cause the function to be exported as an Excel add-in function named TestArrays, with no prefix.

  6. Add an XllFunction attribute to the method TestClass.T3:

    CopyC#
    [XllFunction] 
public static int T3(int a, int b)
{
    return a + b;
}

    This attribute will cause the function to be exported as an Excel add-in function named TestClass.T3.

  7. Add XllArgument attributes to the arguments of TestClass.T3:

    CopyC#
    [XllFunction]
public static int T3(
    [XllArgument("First", Flags=XlArgumentFlags.Optional, DefaultValue="100")] 
    int a,
    [XllArgument("Second", Flags=XlArgumentFlags.Optional, DefaultValue="-99")] 
    int b)
{
    return a + b;
}

    These attributes will change the names of the arguments a and b to First and Second respectively. They also provide default values for each argument which will be used if the argument is omitted.

  8. Note that no attribute is specified for the method TestClass.DontExportMe. Consequently, the function is not exported to Excel.

  9. Add XllFunction attributes to the methods TestClass.VarTypeScalar, TestClass.VarTypeVector and TestClass.VarTypeMatrix:

    CopyC#
    [XllFunction] 
public static string VarTypeScalar(object arg)
{
    return String.Format("{0} ({1})", arg, arg == null ? "none" : arg.GetType().Name);
}

[XllFunction] 
public static string[] VarTypeVector(object[] arg)
{
    ...
}

[XllFunction] 
public static string[,] VarTypeMatrix(object[,] arg)
{
    ...
}

    The attribute will cause each of these methods to be exported to Excel. The arguments of each method are object types, and these are treated specially by the code importer; they can contain one or more values of any Excel value type: string, number, boolean or empty. Each cell value will be converted to a value of the appropriate type (System.String, System.Double, System.Boolean or null) before the .NET method is invoked.

  10. Add an XllFunction attribute to the method TestClass.VarRet:

    CopyC#
    [XllFunction] 
public static object VarRet(int type)
{
    ...
}

    This attribute will cause the function to be exported as an Excel add-in function named TestClass.VarRet.

  11. Add an XllArgument attribute to the type argument of TestClass.VarRet:

    CopyC#
    [XllFunction]
public static object VarRet(
    [XllArgument(Flags=XlArgumentFlags.ShowValueListInFormulaWizard, ValueList="0,String;1,Boolean;2,Number")] 
    int type)
{
    ...
}

    This attribute will add a value list for the argument. The value list will appear in a drop-down list when the function appears in the Excel Formula Wizard:

  12. Add an XllFunction attribute to the method TestClass.DiscountFactor:

    CopyC#
    [XllFunction] 
public static double DiscountFactor(double rate, Periods period)
{
    return Math.Exp((-1.0 / ((double)(int)period)) * rate);
}

    This attribute will cause the function to be exported as an Excel add-in function named TestClass.DiscountFactor.

  13. Add an XllArgument attribute to the period argument of TestClass.DiscountFactor:

    CopyC#
    [XllFunction]
public static double DiscountFactor(double rate,
    [XllArgument(Flags = XlArgumentFlags.ShowValueListInFormulaWizard)] Periods period)
{
    return Math.Exp((-1.0 / ((double)(int)period)) * rate);
}

    This attribute will add a value list for the argument. The value list will appear in a drop-down list when the function appears in the Excel Formula Wizard. Because Periods is an Enum type, there is no need to specify the value list: it is generated automatically by the assembly importer.

Add a reference to the assembly

Before we can import the .NET assembly into the XLL+ project, we must create a reference to it.

  1. In Solution Explorer, find the node for the project NetWrapAttr and right-click it. Select Properties.

  2. In the Project Properties window, select the node "Common Properties/References", and click the "Add New Reference..." button.

  3. You may have to wait a long time for the "Add Reference" window to appear.

  4. In the "Add Reference" window, select the tab "Projects", select "MyLib" in the list, and press OK.

  5. In the Project Properties window, click OK to save your changes.

  6. You should now build the solution (Shift+Ctrl+B), and make sure that both projects built successfully.

Importing the assembly

In these steps you will set up a link between the two projects, once it is done, the build process will include an extra step, which inspects the .NET assembly and generates wrapper add-in functions for all the exported methods and properties. Whenever the .NET assembly is changed, this code will be regenerated during the next build.

  1. Open the main source file of the add-in project, NetWrapAttr.cpp.

  2. Activate the XLL Add-ins tool window (View/Other Windows/XLL Add-ins).

  3. Click on Import Assemblies... in the Tools menu.

  4. In the list of assemblies, put a check against MyLib.

  5. Click OK to save your changes and return to Visual Studio.

The completed project

If you inspect the add-in project in Solution Explorer, you will see that two new files have been added. In the folder "NetWrap/Imported files" are MyLib.import.xml and MyLib_Wrappers.cpp.

MyLib.import.xml contains instructions for importing the .NET assembly. When it is built, the XLL+ reflection tool, XNREFLECT.EXE, is run, which generates the C++ code for the XLL add-in functions, and write it to MyLib_Wrappers.cpp.

  1. Build the project. You will observe a new step in the build process, when MyLib_Wrappers.cpp is generated.

  2. Inspect MyLib_Wrappers.cpp. Each of the functions is simply a wrapper for a .NET method, and contains code to validate inputs, translate them to CLR data forms, invoke the .NET method and finally convert the result to Excel data form. If any method throws an exception, it will be caught and reported back to Excel.

  3. Use F5 to run and debug the add-in. Place break-points in the C# code, and you will see that the debugger seamlessly steps between the C++ and C# modules.

See Importing .NET assemblies in the User Guide for more details.

See Also

Importing a .NET Assembly | Samples and Walkthroughs

© 1997-2010 Planatech Solutions Ltd. All rights reserved.