Asynchronous Custom Functions | Document Solutions for Excel, .NET Edition

Asynchronous Custom Functions

In This Topic

Asynchronous functions are any function that delivers its result asynchronously or concurrently. Asynchronous functions have a non-blocking architecture, so the execution of one task isn't dependent on another. Tasks can run simultaneously. Running asynchronous functions can improve performance by allowing several calculations to run at the same time. DsExcel provides AsyncCustomFunction class that enables you to add asynchronous functions by deriving the function class from AsyncCustomFunction class.

Refer to the following example code to add and use a custom Asynchronous function:

C#	Copy Code
internal class Program { static void Main(string[] args) { // Register Async custom function. Workbook.AddCustomFunction(new MyAddFunction()); // Implement the Async custom Function. Workbook workbook = new Workbook(); var worksheet = workbook.Worksheets[0]; worksheet.Range["A1"].Value = 1; worksheet.Range["B1"].Value = 2; // Add the cell values. worksheet.Range["C1"].Formula = "=MyAdd(A1,B1)"; var value = worksheet.Range["C1"].Value; // Display result. The result will be "Busy". Console.WriteLine($"get value first time:{value}"); Thread.Sleep(2000); value = worksheet.Range["C1"].Value; // Display result. The result will be "3". Console.WriteLine($"get value second time:{value}"); } } // Define Async custom function: MyAddFunction. public sealed class MyAddFunction : AsyncCustomFunction { public MyAddFunction() : base("MyAdd", FunctionValueType.Number, new Parameter[] { new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) }) { } async protected override Task<object> EvaluateAsync(object[] arguments, ICalcContext context) { await Task.Delay(1000); return (double)arguments[0] + (double)arguments[1]; } }

Copy Code

internal class Program
{
    static void Main(string[] args)
    {
        // Register Async custom function.
        Workbook.AddCustomFunction(new MyAddFunction());

        // Implement the Async custom Function.
        Workbook workbook = new Workbook();
        var worksheet = workbook.Worksheets[0];
        worksheet.Range["A1"].Value = 1;
        worksheet.Range["B1"].Value = 2;

        // Add the cell values.
        worksheet.Range["C1"].Formula = "=MyAdd(A1,B1)";
        var value = worksheet.Range["C1"].Value;

        // Display result. The result will be "Busy".
        Console.WriteLine($"get value first time:{value}");
        Thread.Sleep(2000);
        value = worksheet.Range["C1"].Value;

        // Display result. The result will be "3".
        Console.WriteLine($"get value second time:{value}");
    }
}
// Define Async custom function: MyAddFunction.
public sealed class MyAddFunction : AsyncCustomFunction
{
    public MyAddFunction()
        : base("MyAdd", FunctionValueType.Number, new Parameter[] { new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) })
    {

    }

    async protected override Task<object> EvaluateAsync(object[] arguments, ICalcContext context)
    {
        await Task.Delay(1000);
        return (double)arguments[0] + (double)arguments[1];
    }
}

Limitations

The AsyncCustomFunction's parameters does not accept any reference because the asynchronous function may run in another thread, and it will cause multi-thread conflicts if you use a reference. Similarly, using objects such as IWorksheet and IWorkbook is not allowed within asynchronous functions.