Asynchronous Functions | Document Solutions for Excel, .NET Edition

Asynchronous Functions

In This Topic

Asynchronous function is 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 enables functions to perform asynchronous calculations by deriving them from AsyncCustomFunction class. EvaluateAsync method calculates the function asynchronously. DsExcel also provides an enumeration value "Busy" in CalcError enumeration that indicates that a cell is calculating an async formula.

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 do 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.

Asynchronous Image Function

DsExcel supports an asynchronous IMAGE formula function that enables you to add images to the cells from a URL path. It provides alt_text, sizing, height, and width parameters to set alternative text, image sizing, height, and width of the image. source parameter of this function allows you to provide a URL path using the “https“ protocol of the image. To handle this web request, DsExcel provides WebRequestHandler property of Workbook class to enable you to access and customize the web request handler for the application. WebRequestHandler property is of IWebRequestHandler interface type. This defines an interface for handling web requests asynchronously and provides a way to send GET requests to a specified URI.

Asynchronous functions deliver the results asynchronously or concurrently, but some operations depend on the calculation results of other functions. DsExcel provides WaitForCalculationToFinish method of IWorkbook interface that ensures all necessary computations have been performed before proceeding with any other operations that depend on the calculation results. It waits for the completion of all calculations, including asynchronous ones. This method blocks the current thread until all calculations have finished.

Refer to the following example code to add and use an asynchronous IMAGE function:

C#	Copy Code
static void Main(string[] args) { // Set a custom web request handling class to send all network requests. Workbook.WebRequestHandler = new WebRequestHandler(); // Initialize Workbook. var workbook = new Workbook(); // Get the active sheet. var sheet = workbook.ActiveSheet; // Set IMAGE function. sheet.Range["A1:F10"].Formula = "=IMAGE(\"https://support.content.office.net/en-us/media/926439a2-bc79-4b8b-9205-60892650e5d3.jpg\", \"Pyramid\")"; // Calculate all formulas so the asynchronous image function will run. workbook.Calculate(); // Block the current thread until all asynchronous functions have finished to avoid #BUSY! error in the exported file. workbook.WaitForCalculationToFinish(); // Save the workbook. workbook.Save("AsyncImageFunction.pdf"); workbook.Save("AsyncImageFunction.xlsx"); } // Create custom web request handling class. public class WebRequestHandler : IWebRequestHandler { public async Task<WebRequestResult> GetAsync(string requestUri) { var result = new WebRequestResult(); using (HttpClient? client = new HttpClient()) { try { HttpResponseMessage? response = await client.GetAsync(requestUri); result.StatusCode = (int)response.StatusCode; if (response.IsSuccessStatusCode) { result.ResponseContent = await response.Content.ReadAsByteArrayAsync(); } } catch (HttpRequestException) { result.IsConnectionFailed = true; } } return result; } }

Copy Code

static void Main(string[] args)
{
    // Set a custom web request handling class to send all network requests.
    Workbook.WebRequestHandler = new WebRequestHandler();

    // Initialize Workbook.
    var workbook = new Workbook();

    // Get the active sheet.
    var sheet = workbook.ActiveSheet;

    // Set IMAGE function.
    sheet.Range["A1:F10"].Formula = "=IMAGE(\"https://support.content.office.net/en-us/media/926439a2-bc79-4b8b-9205-60892650e5d3.jpg\", \"Pyramid\")";

    // Calculate all formulas so the asynchronous image function will run.
    workbook.Calculate();

    // Block the current thread until all asynchronous functions have finished to avoid #BUSY! error in the exported file.
    workbook.WaitForCalculationToFinish();

    // Save the workbook.
    workbook.Save("AsyncImageFunction.pdf");
    workbook.Save("AsyncImageFunction.xlsx");
}

// Create custom web request handling class.
public class WebRequestHandler : IWebRequestHandler
{
    public async Task<WebRequestResult> GetAsync(string requestUri)
    {
        var result = new WebRequestResult();
        using (HttpClient? client = new HttpClient())
        {
            try
            {
                HttpResponseMessage? response = await client.GetAsync(requestUri);
                result.StatusCode = (int)response.StatusCode;
                if (response.IsSuccessStatusCode)
                {
                    result.ResponseContent = await response.Content.ReadAsByteArrayAsync();
                }
            }
            catch (HttpRequestException)
            {
                result.IsConnectionFailed = true;
            }
        }
        return result;
    }
}