C# User-Defined Functions | SpreadsheetWeb Legacy

User-defined functions (UDFs) are custom spreadsheet functions that accept inputs, run reusable logic, and return a result. They can simplify complex formulas and make an Excel model easier to maintain.

This article applies to the SpreadsheetWeb Legacy Platform. It explains how to submit C# UDFs with an application and how SpreadsheetWeb compiles them for server-side calculations. SpreadsheetWeb Legacy introduced C# UDF support in version 4.7.

What is a user-defined function?

A UDF is a function that you create instead of using a function built into Excel.
A UDF typically returns a literal value that a cell can display or use in another formula.
Unlike a macro, a UDF cannot change workbook structure, edit other cells, apply formatting, change environment options, or set properties.
A UDF can accept one or more input values and can call other functions in the same submitted code.

Excel commonly uses VBA for custom workbook logic. SpreadsheetWeb Legacy does not execute VBA on the server. Instead, you provide an equivalent function written in C# so the application can run the calculation in its .NET environment.

Benefits of C# UDFs

The compiled function can run on the server without Microsoft Office.
A single function can replace a difficult series of nested workbook formulas.
Submitted code can reference .NET Framework assemblies that are available on the server.
Overloads and optional parameters are supported.

Considerations before you begin

Creating a UDF requires familiarity with C#.
If the workbook must also calculate in desktop Excel, you may need to maintain both the Excel implementation and the C# implementation.
SpreadsheetWeb Legacy supports C# 6.0 syntax for submitted UDFs.

Add a UDF to an application

Edit the web application, find the User-Defined Functions section, and select Add/Edit.

User-Defined Functions section with the Add/Edit button in SpreadsheetWeb Legacy

Complete the fields in the dialog, and then select Save. SpreadsheetWeb attempts to compile the submitted code and reports the result.

Below are the explanations for the UDF feature menu:

Version Name	A unique identifier for the version name. This can be an internal flag for version control. Note: SpreadsheetWEB imposes a restriction on version name that requires this identifier to be unique, meaning that subsequent uploads of UDF functions cannot share a version name with any prior uploaded versions. If you utilize an existing version, then the Control Panel will clearly warn you to modify the version name.
Directives	A semicolon-delimited list of all imports that the code will require. In C#, this is the equivalent of the using statements at the top of your class file (e.g. if you have using System; using System.Xml; at the top of your class, the valid entry for this field would be System; System.Xml).
Assemblies	The names of the .NET framework assemblies. This is the equivalent of the references required by a project in order for your code to compile. For example, all .NET framework code will likely require a reference to mscorlib. If you also required a reference to the System, System.Net, and System.Linq namespaces, the valid entry would be mscorlib; System; System.Net; System.Core.
Namespace	This string will define the namespace, so any valid namespace (e.g. no spaces or special characters) is appropriate entry. Using a unique name that signifies this particular segment of code will allow easier identification of any runtime errors that appear in the log.
Class Name	This string will define the class name, so any valid class name (e.g. no spaces or special characters) is appropriate entry. Using a unique name that signifies this particular segment of code will allow easier identification of any runtime errors that appear in the log.
Code	User Defined Functions are entered here. Important Note: At compile-time, your code will be used to generate static classes. This means that any functions submitted in the Code section should be static functions (i.e. no access to instance members or non-static functions). Overloads and optional parameters are supported. Simply copy your code into this field.
Results	Upon hitting the Save button, the system will attempt to compile your code. Any compilation errors will be displayed in the Results section, along with a compiler message and the offending line and character position.

C# requirements and behavior

Add the name of each required .NET Framework assembly to the Assemblies field.
Use C# 6.0 syntax.
Submitted UDFs must be static. The compiler reports an error if they are not.
A function called directly from the workbook must also be public.
Overloads and optional parameters are supported.

For example, if cell A1 contains =DoSomething(A2, A3), the submitted code must contain a compatible method such as:

public static object DoSomething(object x, object y)

If A2 and A3 contain numbers or text, you can use double or string parameters instead of object. A helper method called only by DoSomething can use a more restrictive access modifier, such as private.

The following examples show how common VBA functions can be represented as C# UDFs for SpreadsheetWeb Legacy.

Simple Rectangle Area Function

VBA:

Function Area(x As Double, y As Double) As Double
    Area = x * y
End Function

C#:

public static double Area(double x, double y)
{
    return x * y;
}

Get File Size

VBA:

Function GetFileSize(fileName As String) As Long
    GetFileSize = FileLen(fileName)
End Function

C#:

public static long GetFileSize(string fileName)
{
    return new System.IO.FileInfo(fileName).Length;
}

Get Name of Day

VBA:

Function DayName(InputDate As Date) As String
    Dim DayNumber As Integer
    DayNumber = Weekday(InputDate, vbSunday)

    Select Case DayNumber
        Case 1
            DayName = "Sunday"
        Case 2
            DayName = "Monday"
        Case 3
            DayName = "Tuesday"
        Case 4
            DayName = "Wednesday"
        Case 5
            DayName = "Thursday"
        Case 6
            DayName = "Friday"
        Case 7
            DayName = "Saturday"
    End Select
End Function

C#:

public static string DayName(string inputDate)
{
    DateTime outputDate;
    if (DateTime.TryParse(inputDate, out outputDate))
    {
        return outputDate.DayOfWeek.ToString();
    }

    return "You entered an invalid date!";
}