Skip to content

Creating Excel functions via C#

QueryStorm lets you define new Excel functions using C#. The process is simple:

  1. Connect via the C# engine
  2. Write your function
  3. Make sure the function is public and static
  4. Decorate the function with a [ExcelFunc] or [ExcelFunc(permanent:true)] attribute
  5. Run the script

Once you run the script that contains the function, the function will be available in Excel.

Example

Let's assume we need to be able to convert numbers into English words in our spreadsheet. Excel doesn't come with a function for this, but we can easily write one in C# and register it with Excel:

using Humanizer;

[ExcelFunc(permanent:true)]
public static object ToWords(int i)
{
    return i.ToWords();
}

The example above uses a fantastic open source library called Humanizer, that comes built in with QueryStorm. Humanizer adds extensions methods to many .NET types and allows you to convert .NET objects into many useful human-readable representations.

Once we run the query, we can immediately start using the new ToWords function in Excel:

Excel function via C#

Modifying a function

Modifying a function is easy. Just change the C# code and re-run it. Once that's done, Excel will start using the new implementation of the function. To refresh existing calculations, select any cell in the sheet and press F9.

Permanent and temporary functions

The [ExcelFunc] attribute takes an optional Boolean parameter named permanent. This parameter is set to false by default, meaning that the function will be available to all workbooks but only until Excel is closed. To register the function permanently, use [ExcelFunc(permanent:true)]. This will make the function permanently available in Excel for the current Windows user.

How it works

QueryStorm includes an ExcelDNA module that manages C# user-defined functions. Every time the user runs a C# script, this module analyzes the code in search of public static functions that are decorated with the [ExcelFunc] attribute. If it finds any, it will immediately register them with Excel. If it finds any functions with permanent set to true, it will copy the compiled code into the %appdata%\querystorm\functions folder and register the function in the %appdata%\querystorm\functions\catalog.json file. The module checks this file each time Excel loads, and immediately registers with Excel any functions that are declared there.

Un-registering a function

Should you ever need to unregister a function, you can do so by deleting it from the catalog.json file. You can also do it from C# in QueryStorm:

Resolve<QueryStorm.Core.Services.UDFStore>().RemoveFunction("ToWords")

You simply ask QueryStorm for the UDFStore instance (via DI) and then call the RemoveFunction method.

Once Excel is restarted, the function will no longer be available.

Aggregate functions

Functions can take parameters that are 1D or 2D arrays:

[ExcelFunc]
public static string Join(string [] values)
{
    return string.Join("; ", values);
}

If a parameter is declared as a 1D array it will be able to accept a series of values in a row or a column. A 2D array is appropriate if the input values will have more than one row and more than one column.

Functions can have as many parameters as you need, and you can mix single value parameters with array parameters.

Deploying functions to other machines

If your workbook uses your C# function and you want this workbook and the function to be available on other machines, you should do the following:

  1. Embed the C# script (that contains the function) into the workbook
  2. Create an automation job that will run the script whenever the workbook is opened

If you do not have control over the end user machine, consider making the function temporary (i.e. permanent=false) so you don't inadvertently overwrite other permanent functions that are registered on the end user machine under the same name.

Dependencies

Your script can reference other dlls using the #r directive. It can reference dlls that are present in the GAC or any other dll with an absolute path (can be a network share) that is reachable from the end-user machine.

Not yet supported

The following items are not yet supported, but might be supported in an upcoming version:

  • asynchronous functions
  • function/parameter tooltips
  • compiled functions in the workbook (instead of plain C# code)
  • bundling dependency dlls
  • support for nuget dependencies

Comments