The ApiTest sample demonstrates how you can call the functions of an XLL from other environments, including .NET, VBA and C++.
This sample contains several components:
Open the solution file ApiTest.sln to see and use the four Visual Studio projects.
ApiTest: XLL+ Add-in
This add-in contains a variety of add-in functions which serve to test the various features of the XLL Wrapper Generator.
The functions' names (e.g. ScalarAdd, UseCallback) reflect their purpose.
ApiTestLib: XLL Wrapper Library
This wrapper library was generated from a model file exported from the ApiTest add-in, ApiTest.model.xml.
A number of changes were made to the information file ApiTest.wrapper.xml.
Namespace
The namespace used for the wrapper classes was changed to Planatech.Demos, by changing the Namespace attribute of the Client elements:
CopyXML<Clients> <Client Type="COM" Namespace="Planatech.Demos" Class="ApiTest" ClassGuid="2d42a1e1-3659-4869-81fc-36e7ad4b0889" Interface="IApiTest" InterfaceGuid="fe36ae91-b9e2-4794-8d47-5a204e3e06ea"/> <Client .../> </Clients>
Excluding a function
The version information function was excluded from the list of exported functions:
CopyXML<Function ExportedName="ApiTest_VersionInfo" Wrap="Never"/>
Function return types
A return type was specified for functions which, within Excel, return a variable type - they may return an error value or an error string if the functions fail for some reason.
Specifying the return type has two effects:
- The wrapper function's signature specifies a useful type, rather than object.
- if the add-in function fails the wrapped function will throw an exception with an appropriate error message.
CopyXML<Function ExportedName="ScalarAdd" ReturnType="double"/> <Function ExportedName="StringAdd" ReturnType="string"/> <Function ExportedName="VectorAdd" ReturnType="double[]"/> <Function ExportedName="MatrixAdd" ReturnType="double[,]"/>
In each case, the wrapper function's code handles the appropriate conversion and validation:
CopyC#/// <summary> /// Adds two vectors /// </summary> /// <param name="A">is the first vector</param> /// <param name="B">is the second vector</param> /// <exception cref="XllWrapperException">Will be thrown if any errors /// occur in the call to Excel, /// or if the add-in function returns an error value.</exception> [Description("Adds two vectors")] [DispId(1005)] public double[] VectorAdd(double[] A, double[] B) { CheckEngine(); object res__ = Engine.CallExcelWithErrorCheck("VectorAdd", true, errorPrefixes, A, B); return Unwrap(res__, typeof(double[])) as double[]; }
Dates
Excel add-in functions treat dates as numbers. It is useful for wrapper functions to know that they are in fact dates. The IsDate attribute of the Argument element can be used for this purpose:
CopyXML<Function ExportedName="StartOfMonth" ReturnType="DateTime"> <Argument Name="dt" IsDate="1" WrappedName="Date"/> </Function>
CopyC#/// <summary> /// Returns the start of the month for the specified date /// </summary> /// <param name="Date">is a numeric date</param> /// <exception cref="XllWrapperException">Will be thrown if any errors occur in the call to Excel, /// or if the add-in function returns an error value.</exception> [Description("Returns the start of the month for the specified date")] [DispId(1010)] public DateTime StartOfMonth(DateTime Date) { CheckEngine(); double Date_xldate = DateToExcel(Date); object res__ = Engine.CallExcelWithErrorCheck("StartOfMonth", true, errorPrefixes, Date_xldate); return (DateTime)Unwrap(res__, typeof(DateTime)); }
Names
The WrappedName attribute of the Argument element was used to change the argument names visible in the wrapper function, perhaps to follow standards. Thus X in Excel becomes dtX in C#.
CopyXML<Function ExportedName="MinDates" ReturnType="DateTime[]"> <Argument Name="X" IsDate="1" WrappedName="dtX"/> <Argument Name="Y" IsDate="1" WrappedName="dtY"/> </Function>
CopyC#public DateTime[] MinDates(DateTime[] dtX, DateTime[] dtY) { CheckEngine(); double[] dtX_xldate = DatesToExcel1d(dtX); double[] dtY_xldate = DatesToExcel1d(dtY); object res__ = Engine.CallExcelWithErrorCheck("MinDates", true, errorPrefixes, dtX_xldate, dtY_xldate); return Unwrap(res__, typeof(DateTime[])) as DateTime[]; }
Name changes using the WrappedName attribute can also be useful for avoiding reserved words, and other naming conflicts. The WrappedName attribute can also be applied to function names.
CsClient: C# console application
The code for the CsClient test application can be found in the CsClient sub-directory.
References
The applications has references to two assemblies: the wrapper library ApiTestLib.dll and the XLL+ wrapper support classes XllPlus.ComWrappers.Runtime.dll.
 Note: |
|---|
|
In each case the reference is to the assembly (.dll) file; the type library file (.tlb) is not used by .NET clients. |
A copy of the XLL+ wrapper support classes' assembly, XllPlus.ComWrappers.Runtime.dll, can be found in the same directory as the wrapper assembly. It is placed there by Visual Studio.
Initializing the engine
The engine initalization and termination takes place in TestContext.RunWithExcel(). The engine is initialized using Create. and terminated when Dispose() is called at the end of the using block. Alternatively, Destroy could be called to terminate the engine.
CopyC#public void RunWithExcel(string[] args) { using (ExcelWrapperEngine engine = new ExcelWrapperEngine()) { engine.Create(); engine.LoadXll(GetXllPath()); TestRunner run = new TestRunner(args); run.Run(engine); } } private string GetXllPath() { return Path.Combine(Path.GetDirectoryName(typeof(Program).Assembly.Location), "ApiTest.xll"); }
GetXllPath() uses a standard technique to find the XLL file - it looks in the same directory as the wrapper assembly.
.NET wrapper classes
Immediately after a wrapper object is created, its Engine property is set.
CopyC#private void TestScalarAdd(IWrapperEngine engine) { try { using (ApiTest api = new ApiTest()) { api.Engine = engine; Console.Out.WriteLine("ScalarAdd(1.2, 2.3)={0}", api.ScalarAdd(1.2, 2.3)); } } catch (XllWrapperException ex) { Console.Out.WriteLine(ex.ToString()); } }
Wrapper classes are lightweight, and they support IDisposable, so it is convenient to put them within a using block. The wrapper object is disposed automatically at the end of the using block.
CppClient: C++ console application using COM
The code for the CppClient test application can be found in the CppClient sub-directory.
References
The application contains two references to COM modules: one for the wrapper library, and one for the XLL+ wrapper support classes. In each case the reference is to the type library (.tlb) file, not to the assembly (.dll) file.
CopyC++#import "..\..\..\bin\XllPlus.ComWrappers.Runtime.tlb" #import "..\ApiTestLib\bin\Release\ApiTestLib.tlb"
Initializing the engine
The engine initialization takes place in TestRunner::Run().
The engine is terminated using Destroy.
| Note: |
|---|
|
As usual with COM objects, the pointers reference the primary interface of the object, i.e. IExcelWrapperEngine rather than the class. |
Special consideration is also needed for the value used for the wrapper class's Engine property. The IExcelWrapperEngine interface is cast to a to a IWrapperEngine interface explicitly. The reference is dropped when it is no longer required.
CopyC++void Run() { try { IExcelWrapperEnginePtr engineInstance(__uuidof(ExcelWrapperEngine)); engineInstance->Create(); engineInstance->LoadXll(GetXllPath()); IWrapperEnginePtr engine(engineInstance); // ... Use the engine ... engine.Detach(); engineInstance->Destroy(); } catch(_com_error e) { _tprintf(_T("%s\n"), (LPCTSTR)e.Description()); } }
COM wrapper classes
The wrapper class implements a default interface, IApiTest. As usual in C++ COM programming, pointers are for the default interface, not the object.
CopyC++void TestScalarAdd(IWrapperEnginePtr& engine) { try { ApiTestLib::IApiTestPtr api(__uuidof(ApiTestLib::ApiTest)); api->Engine = engine; _tprintf(_T("ScalarAdd(1.2, 2.3)=%lf\n"), api->ScalarAdd(1.2, 2.3)); } catch(_com_error e) { _tprintf(_T("%s\n"), (LPCTSTR)e.Description()); } }
Array arguments
Array arguments are represented as SAFEARRAY types in the COM code. The sample code uses a few array helper methods to ease the task of reading and writing COM arrays.
VbaClient: VBA module in Excel spreadsheet
VbaClient.xls can be found in the VbaClient sub-directory of the sample.
References
The Tools/References... menu was used to open the References dialog. Then the Browse... button was used to navigate to the directories containing each type library.
In each case the reference was made to the type library (.tlb file), not the assembly (.dll) file.
The XLL+ wrapper support classes' type library file, XllPlus.ComWrappers.Runtime.tlb, is in the bin sub-directory of the XLL+ installation (e.g. C:\Program Files\Planatech\XllPlus\6.0\VS9.0\bin\XllPlus.ComWrappers.Runtime.tlb).
The wrapper objects' type library is in the same directory as the wrapper assembly: ..\ApiTestLib\bin\release\ApiTestLib.tlb.
Initializing the engine
Because the wrapper library is being used from Excel VBA, the engine is initialized using Attach, passing the existing instance of Excel. When the engine is no longer required, Detach is called.
CopyVBAPublic Sub Run() Dim engine As New ExcelWrapperEngine engine.Attach Application engine.LoadXll GetXllPath ' ... engine.Detach End Sub
 Important Note: |
|---|
|
You should not call Create from Excel VBA. This will create a new hidden instance of Excel instead of using the current instance. |
The full path to the XLL file is calculated by GetXllPath:
CopyVBAPrivate Function GetXllPath() As String GetXllPath = ThisWorkbook.Path & "\..\Debug\ApiTest.xll" End Function
Wrapper classes
The VBA wrapper class ApiTestVba was used instead of the standard wrapper class, to deal with the special issues arising from the VBA implementation of COM.
CopyVBAPrivate Sub TestScalarAdd(engine As IWrapperEngine) Err.Clear On Error Resume Next Dim api As New ApiTestVba Set api.Engine = engine If Err.Number <> 0 Then Debug.Print Err.Description Else Debug.Print "ScalarAdd(1.2, 2.3)"; Dim result As Double result = api.ScalarAdd(1.2, 2.3) If Err.Number <> 0 Then Debug.Print " throws "; Err.Description Else Debug.Print "="; result End If End If End Sub
See Also
