Calls methods in a C# .NET assembly

Overview

This project contains three add-in functions that call methods in an associated .NET class library (MyAssembly), authored in C#.

For more information on using your own or 3rd-party .NET assemblies from add-in functions, see Deploying your .NET assemblies in the User Guide.

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 sample fails to load into Excel at run-time, please see the technical note .NET requirements.

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

Project setup

In order to make this project build and run, the following steps were required:

• Check the /clr option in the XLL+ Application Wizard.
• Add a reference to MyAssembly.dll, so that the associated .NET class library is available. (You can add an assembly reference by opening the "Common Properties/References" page of the Project Properties dialog and clicking "Add New Reference...".)

The add-in expects to find the associated .NET class library (MyAssembly.dll) in its own directory, so the Visual Studio solution is configured to deliver the XLL and the .NET assembly to the same directory.

Note that if you wanted to deploy the .NET assembly to a different directory, you could call CXllApp::SetPrivateBinPath() during the XllOpenEx event, to specify the directory or directories where the XLL will search for .NET assemblies.

MyCalculation

The add-in function MyCalculation calls a static .NET method: public static double[,] MyCalculation(double[] vector, double[,] matrix).

The array inputs, AddedVector and Matrix, are converted to .NET arrays using the conversion function ple::clr::toClr().

The array that is returned by the .NET function is converted to a C++ matrix using another conversion function, ple::clr::toXl()

array<double, 2>^ resultArray = MyAssembly::MyStaticClass::MyCalculation(
    toClr(AddedVector), toClr(Matrix));
xloResult = toXl(resultArray);

Because MyAssembly::MyStaticClass::MyCalculation expects the vector to be the same size as the height of the matrix, the XLL+ Function Wizard was used to add a constraint to the height of the matrix.

RevalPayments

The add-in function RevalPayments calls another static .NET method:

public static double RevalPayments(DateTime[] dates, double[] amounts,
    double riskFree, DateTime revalDate)

The helper function ple::clr::toClrDates() is used to convert the date array from Excel's numeric format to an array of System::DateTime values. In addition, ple::clr::toClrDate() is used to convert the ValueDate input, and ple::clr::toClr(<T>) is used to convert the vector of values to a .NET array.

CXlOper* RevalPayments_Impl(CXlOper& xloResult, const CXlOper* Dates_op, 
    const CXlOper* Amounts_op, double RiskFree, long ValueDate)
{
    ...
    //}}XLP_SRC
    xloResult = MyAssembly::MyStaticClass::RevalPayments(
      toClrDates(Dates),
      toClr(Amounts),
      RiskFree,
      toClrDate(ValueDate));
    return xloResult.Ret();
}

Again, the XLL+ Function Wizard was used to add a constraint to the sizes of the two vector arguments.

PrefixArray

The add-in function PrefixArray creates an instance of a .NET class, calls a method, and returns the result.

Again, the conversion helper functions toClr() and toXl() are used to convert inputs and outputs respectively.

// Create an instance of a .NET class
MyAssembly::MyClass^ instance = gcnew MyAssembly::MyClass(toClr(Prefix));
// Use an instance method of the class, and return the result to Excel.
xloResult = toXl(instance->AddPrefix(toClr(StringArray)));

Classes and functions used

ple::clr::toClr | ple::clr::toClrDate | ple::clr::toClrDates | ple::clr::toXl

Sample project

Each sample project is located in a sub-directory of the Samples directory of the XLL+ installation. To use the sample project, open the solution file AssemblyDemo.sln or the project file AssemblyDemo.vcproj.

You can enable debugging under Excel by using the Setup Debugging command in the XLL+ ToolWindow.

When delivered, the help files are excluded from the build. You can enable the help build by selecting the files AssemblyDemo.help.xml and AssemblyDemo.chm in the Solution Explorer, and using the right-click menu to view Properties. Select the page "Configuration Properties/General" and set the "Excluded from build" property to "No". See Generating help in the User Guide for more information.

See Also

List of Sample Projects