Embed a Spreadsheet Into Your Web App

Communication Between a Web Page and Keikai

After embedding Keikai, you can see the file rendered in the HTML page, and you can edit and scroll. But it still lacks communication between the HTML page and Keikai itself. I will explain how to do this by implementing "search product" and "show vendor contact information."

Java Controller

There is a Java controller applied on Keikai at the server-side. If you check search-stock.zul, you will see a class name at apply:

xxxxxxxxxx

<spreadsheet ... apply="io.keikai.devref.usecase.embed.StockSearchComposer"/>

The controller can receive the events from the client-side, query products from a database, and contain event listeners with application logic like exporting and accessing cell values.

Fire Events to the Server

ZK framework supports the event-driven programming model. So the main way to communicate with the Keikai controller is to fire an event in JavaScript. The Keikai spreadsheet will create a JavaScript widget in the browser, and it provides the function fire() to fire events to the Keikai Java controller.

Export XLSX and PDF

I have defined the event name, onExportExcel, in the Java controller. So, what I now have to do is to fire the event at the client-side to invoke the event listener of the Java controller:

xxxxxxxxxx

$('#exprtExcel').on('click', () => {

    Controller.fireEvent('onExportExcel', null);

});

The ZK framework contains jQuery by default, so you can use $('#exprtExcel').on() to register an onclick listener on the button Export to Excel.

XLSX Exporter

In the server-side controller, I implement exporting with Keikai's Exporter in a few lines.

xxxxxxxxxx

private Exporter exporter = Exporters.getExporter();

    @Listen("onExportExcel = spreadsheet")

    public void exportExcel() throws IOException {

        File file = File.createTempFile(Long.toString(System.currentTimeMillis()), "temp");

        try (FileOutputStream fos = new FileOutputStream(file);) {

            exporter.export(spreadsheet.getBook(), fos);

        }

        Filedownload.save(new AMedia(spreadsheet.getBook().getBookName(), "xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", file, true));

    }

Exporting a PDF is quite similar, please check the source code on GitHub.

JavaScript Controller on the HTML Page

To avoid a potential function name conflict, I put the event firing functions into a JavaScript class called Controller:

xxxxxxxxxx

class Controller{

    /** fire an event to invoke an event listener at the server-side */

    static fireEvent(eventName, parameter){

         Controller.getSpreadsheet().fire(eventName, parameter, {toServer:true});

    }

    /** get keikai spreadsheet js widget by ID selector "$id" */

    static getSpreadsheet(){

        return zk.Widget.$('$spreadsheet');

    };

ZK Widget Selector

zk.Widget.$(mySelector) is a function that can get a ZK widget reference by a selector syntax. The ID spreadsheet is the value specified in the "id" attribute of in stock-search.zul.

Search Products With Criteria

When a user clicks the "Search" button, this application can show products in the sheet based on the criteria.

Firing an Event at Client-side

You can follow a similar pattern to invoke the onSearch event listener, but this time I need to pass search criteria as the second parameter.

$('#search').on('click', () => {

    Controller.fireEvent('onSearch', Controller.getFilterCriteria());

});

Extract user input as a JSON object:

xxxxxxxxxx

class Controller{

    ...

    static getFilterCriteria(){

        return {"category": $('#category').val(),

                "min": $('#min').val(),

                "max": $('#max').val()};

    }

Receive the Parameter

Through event.getData(), I can get a JSON object sent from the client-side and convert it to FilterCriteria:

xxxxxxxxxx

    @Listen("onSearch = spreadsheet")

    public void search(Event event) {

        FilterCriteria criteria = convertCriteria((JSONObject) event.getData());

        List<Product> result = productService.query(criteria);

        populateResult(result);

    }

• Line 1: The annotation to register an onSearch event listener on the component spreadsheet.
• Line 4: The productService just represents a business layer class – it can return a list of products upon given criteria. You can imagine that class as any of your business classes, just to perform a business operation.

Populate the Result

To populate the product list into the sheet on the browser, you need to call an important API Range. There are two basic steps:

1. Create a Range object that represents one or multiple cells, e.g. Ranges.rangeByName(resultSheet, "ReportTable")
2. Call Range methods to get or set data on that range, e.g. setCellValues()

xxxxxxxxxx

    /**

     * populate search result into a sheet

     */

    private void populateResult(List<Product> result) {

        ...

        //fill searched products

        Range currentRow = Ranges.rangeByName(resultSheet, "ReportTable").toCellRange(0, 0).toRowRange(); //start from the first row

        for (Product p : result) {

            currentRow.setCellValues(p.getId(), p.getCategory(), p.getName(), p.getVendor(), p.getQuantity(), p.getPrice());

            currentRow = currentRow.toShiftedRange(1, 0);

        }

        ...

After invoking the event listener, Keikai will communicate with the client-side widgets to render cell values on the sheet. I don't need to take care of communication and rendering details.

For more APIs, please refer to the Keikai Developer Reference Guide.

Show a Vendor Contact

When a user clicks on a cell, it will show the vendor contact information at the bottom of the page from the server-side. This demonstrates how a sheet interacts with a page (triggered inside a sheet).

Invoke JavaScript Functions

The Java method, Clients.evalJavaScript(), allows you to call a JavaScript function in a browser. That's how Java controllers call the client-side JavaScript controller's function, Controller.showVendor().

In this example, I assume the server has the vendor data. Therefore, I register anEvents.ON_CELL_CLICK listener that queries a vendor by its name. Then, every click on a cell invokes this method:

xxxxxxxxxx

    @Listen(Events.ON_CELL_CLICK + " = spreadsheet")

    public void showVendor(CellMouseEvent event){

        //get vendor name

        Range vendorCell = RangeHelper.getTargetRange(event).toRowRange().toCellRange(0, 3);

        String name = vendorCell.getCellData().getStringValue();

        VendorService.Vendor vendor = VendorService.query(name);

        if (vendor != null){

            Clients.evalJavaScript("Controller.showVendor(" + gson.toJson(vendor) + ")");

        }

    }

• Line 4: I locate the cell of the vendor name based on the row the user clicked and find its fourth column cell.
• Line 5: Get the cell value as a string.
• Line 8: Invoke a JavaScript function with a vendor JSON object as a parameter

Render the Vendor Contact

At the client-side, I simply just render the vendor contact on the page with jQuery:

xxxxxxxxxx

class Controller{

...

    /** render vendor info on the page

    */

    static showVendor(vendor){

        $('#name').text(vendor.name);

        $('#tel').text(vendor.tel);

        $('#email').text(vendor.email);

    }

}

Summary

To wrap up, I have demonstrated how you can embed a spreadsheet into any web page, and how the spreadsheet can interact with the web page in both directions. I hope this helps you bring sme spreadsheet power into your web applications.

Get the Source

The GitHub repository contains the runnable code so that you can try it by yourself.