JetXLL is a quick, easy and affordable way to create custom worksheet functions and complete XLL addins for Excel using managed code (such as C#). There are plenty of other ways to do this (see the links at the end of this article), but, we feel that JetXLL makes it very easy for people who are comfortable writing managed code to expose it to Excel. With JetXLL you can access functionality that Excel's 'C API' exposes without needing to understand the complexity of writing an XLL from scratch in a language such as C or C++.
Managed code can return 'results objects' to Excel which can be passed by name to other JetXLL functions and which can be queried and manipulated within the sheet.
It's very easy to expose your managed code as an Excel worksheet function using JetXLL. First add a reference to the JetXLL.dll to your code and then, simply decorate the class with the [AddinClass] attribute and the functions that you want to expose to Excel with the [WorksheetFunction] attribute. Edit the JetXLL configuration file so that JetXLL will load your assembly and that's it. When the JetXLL addin is loaded it will load your assembly, parse your code for its attributes and expose your functions to Excel.
The simple example above shows how to expose a static function as an Excel worksheet function. You can now type =AddTwoInts() into an Excel cell and provide the two numbers that the function requires (=AddTwoInts(A1, B2) for example) and Excel will call your C# code and place the value that you return into the spreadsheet cell for you.
There's much more to JetXLL, you can expose member functions and indicate which constructor that Excel should call on your classes. You can create Command Functions which can be called from menus, toolbar buttons and macros. You can build custom Excel menus or Toolbars which can call your Command functions. If you have functions that take a while to execute you can use the [AsyncWorksheetFunction] attribute to have JetXLL call the function on a separate thread and you can use the IAsyncWorksheetFunctionCall interface to pass back interim results which are displayed by Excel as your calculation progresses.
If you need to push Real Time Data into your Excel spreadsheets then you can use JetXLL's RTD support to implement a data source without the need to register any COM objects on your workstation and without needing to understand the complexity of the Excel RTD API.
JetXLL provides direct support for "trigger" arguments to Excel worksheet functions. Trigger arguments are dummy arguments that are passed to worksheet functions purely to trigger them (by providing an otherwise impossible dependency to another part of the sheet), they can also be used to prevent 'expensive' calls being made whilst you are configuring the worksheet, or to allow you to 'turn off' aeas of the worksheet using a single trigger cell which can be set to FALSE. Trigger arguments are supported by JetXLL in such a way that they can be automatically added to a managed functions worksheet function signature and then managed completely by JetXLL. Your managed code is never called and marshalling of additional arguments is not done if the trigger argument evaluates to FALSE.
This worksheet function takes an implicit trigger argument. By default the name of the argument is 'Trigger', the help string is 'Trigger the function call if TRUE' and the message returned if the call is not made is 'Call not made'. All of these are configurable. If the optional trigger argument is not TRUE then the call is not made.
JetXLL is currently in a beta testing phase. If you'd like to be involved with the beta test and try out JetXLL during this time then please get in touch with us by emailing us at beta (at) jetxll.net.
Some alternative ways to create managed XLLs
Alternatively you might want to take a look at Visual Studio Tools for Office (VSTO).
Leave a comment