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

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 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 setWebRequestHandler method of Workbook class to enable you to access and customize the web request handler for the application. setWebRequestHandler method 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:

Java	Copy Code
public class AsyncImageFunction { public static void main(String[] args) throws Exception { // Set a custom web request handling class to send all network requests. Workbook.setWebRequestHandler(new WebRequestHandler()); // Initialize Workbook. Workbook workbook = new Workbook(); // Get the active sheet. IWorksheet sheet = workbook.getActiveSheet(); // Set IMAGE function. IRange range = sheet.getRange("A1:F10"); range.setFormula("=IMAGE(\"https://support.content.office.net/en-us/media/926439a2-bc79-4b8b-9205-60892650e5d3.jpg\")"); // 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. class WebRequestHandler implements IWebRequestHandler { @Override public CompletableFuture<WebRequestResult> getAsync(String requestUri) { WebRequestResult result = new WebRequestResult(); return CompletableFuture.supplyAsync(() -> { try { URL url = new URL(requestUri); HttpURLConnection connection = (HttpURLConnection) url.openConnection(); connection.setRequestMethod("GET"); int responseCode = connection.getResponseCode(); result.setStatusCode(responseCode); if (responseCode >= 200 && responseCode < 300) { result.setResponseContent(toByteArray(connection.getInputStream())); } else { System.out.println("Request failed with status code: " + responseCode); } } catch (IOException e) { result.setConnectionFailed(true); } return result; }); } public byte[] toByteArray(InputStream inputStream) { try (ByteArrayOutputStream output = new ByteArrayOutputStream()) { byte[] buffer = new byte[4096]; int bytesRead; while ((bytesRead = inputStream.read(buffer)) != -1) { output.write(buffer, 0, bytesRead); } return output.toByteArray(); } catch (IOException e) { throw new RuntimeException("Error converting InputStream to byte array", e); } } }

Java

Copy Code

public class AsyncImageFunction {

    public static void main(String[] args) throws Exception {
        // Set a custom web request handling class to send all network requests.
        Workbook.setWebRequestHandler(new WebRequestHandler());
        
        // Initialize Workbook.
        Workbook workbook = new Workbook();
        
        // Get the active sheet.
        IWorksheet sheet = workbook.getActiveSheet();
                
        // Set IMAGE function.
        IRange range = sheet.getRange("A1:F10");
        range.setFormula("=IMAGE(\"https://support.content.office.net/en-us/media/926439a2-bc79-4b8b-9205-60892650e5d3.jpg\")");
        
        // 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.
class WebRequestHandler implements IWebRequestHandler {
    @Override
    public CompletableFuture<WebRequestResult> getAsync(String requestUri) {
        WebRequestResult result = new WebRequestResult();
        return CompletableFuture.supplyAsync(() -> {
            try {
                URL url = new URL(requestUri);
                HttpURLConnection connection = (HttpURLConnection) url.openConnection();
                connection.setRequestMethod("GET");
                int responseCode = connection.getResponseCode();
                result.setStatusCode(responseCode);
                if (responseCode >= 200 && responseCode < 300) {
                    result.setResponseContent(toByteArray(connection.getInputStream()));
                } else {
                    System.out.println("Request failed with status code: " + responseCode);
                }
            } catch (IOException e) {
                result.setConnectionFailed(true);
            }
            return result;
        });
    }
    public byte[] toByteArray(InputStream inputStream) {
        try (ByteArrayOutputStream output = new ByteArrayOutputStream()) {
            byte[] buffer = new byte[4096];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                output.write(buffer, 0, bytesRead);
            }
            return output.toByteArray();
        } catch (IOException e) {
            throw new RuntimeException("Error converting InputStream to byte array", e);
        }
    }
}