Asynchronous Custom Functions | Document Solutions for Excel, Java 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:

Java	Copy Code
public class AsyncFunction { public static void main(String[] args) { // Register Async custom function. Workbook.AddCustomFunction(new MyAddFunction()); // Implement the Async custom Function. Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("A1").setValue(1); worksheet.getRange("B1").setValue(2); // Add the cell values. worksheet.getRange("C1").setFormula("=MyAdd(A1,B1)"); Object value1 = worksheet.getRange("C1").getValue(); // Display result. The result will be "Busy". System.out.println(value1); Thread.sleep(2000); Object value2 = worksheet.getRange("C1").getValue(); // Display result. The result will be "3". System.out.println(value2); } } // Define Async custom function: MyAddFunction. class MyAddFunction extends AsyncCustomFunction { public MyAddFunction() { super("MyAdd", FunctionValueType.Number, new Parameter[] { new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) }); } @Override public CompletableFuture<Object> evaluateAsync(Object[] arguments, ICalcContext context) { return CompletableFuture.supplyAsync(() -> { try { Thread.sleep(10); } catch (InterruptedException e) { } return (double)arguments[0] + (double)arguments[1]; }); } }

Java

Copy Code

public class AsyncFunction {

    public static void main(String[] args) {
        // Register Async custom function.
        Workbook.AddCustomFunction(new MyAddFunction());
        
        // Implement the Async custom Function.
        Workbook workbook = new Workbook();
        IWorksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.getRange("A1").setValue(1);
        worksheet.getRange("B1").setValue(2);
        
        // Add the cell values.
        worksheet.getRange("C1").setFormula("=MyAdd(A1,B1)");
        Object value1 = worksheet.getRange("C1").getValue();
        
        // Display result. The result will be "Busy".
        System.out.println(value1);
        Thread.sleep(2000);
        Object value2 = worksheet.getRange("C1").getValue();
        
        // Display result. The result will be "3".
        System.out.println(value2);
    }
}

// Define Async custom function: MyAddFunction.
class MyAddFunction extends AsyncCustomFunction {
    public MyAddFunction() {
        super("MyAdd", FunctionValueType.Number, new Parameter[] { new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) });
    }

    @Override
    public CompletableFuture<Object> evaluateAsync(Object[] arguments, ICalcContext context) {
        return CompletableFuture.supplyAsync(() -> {
            try {
                Thread.sleep(10);
            } catch (InterruptedException e) {
            }
            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.