tutorial · Aug 27, 2018

Keikai Getting Started

Hawk Chen
Developer, Keikai.

Keikai - Spreadsheet for Big Data

Keikai is a web spreadsheet designed for big data. With frame-based rendering and super light-weight data structure, it brings true Excel functionalities into your web application swiftly. Finally, there is a good reason for your business users to move on from Excel!

Keikai works in a client-server architecture. A Keikai UI client running in your browser renders sheets with canvases and fires user events to Keikai server. You can create your own web application that controls Keikai to fulfill your business needs with Keikai Java client.

This introductory article demonstrates how you can start up a Keikai spreadsheet and manipulate cells with Java client API to implement your application logic .

Run your 1st Keikai Spreadsheet

To quickly get a feeling about how Keikai works, just check Keikai tutorial project. There are two demos in the tutorial project, an Online Editor Demo and a Budget Summary Application Demo. Follow the instructions in Readme to set them up in your local environment and play them in your browser.

Installation

Before using Keikai, you need to install a Keikai server. Follow the Readme instructions in Keikai tutorial project.

Prerequisite

To better understand this article and how tutorial project works, we assume you have basic konwledge of Java EE web components (JSP and servlet) and HTML.

Create a Page to Display Spreadsheet

To make things simple, we are running our 1st Keikai in a JSP page. First, put a <div> as a container, an anchor element, which Keikai renders a spreadsheet at. It can be any place you like on the page. Then specify id attribute so that you can pass it to Keikai to identify the anchor element.

editor.jsp

<div id="spreadsheet" style="height: 90%" ></div>

Load Keikai Client

The page also needs to load Keikai client JavaScript. The script URL is dynamically generated by Keikai, so you can specify it with a variable in EL:

editor.jsp

<script async defer src="${keikaiJs}"></script>

Now the page is ready.

Obtain a Spreadsheet

The next step is to connect to Keikai server and get Keikai client JavaScript URL. Then write that JavaScript URL into the JSP I just created. To do so, I create a servlet to accept a request first and forward the request to the JSP with a Keikai JavaScript URL as an attribute.

The servlet calls MyEditor, which implements all my application logic, to get Keikai JavaScript URL.

This can be done in 2 steps:

  1. Connect to Keikai server to get a Spreadsheet object with Keikai.newClient()

  2. Get Keikai client JavaScript URL with Spreadsheet.getURI(anchorElementID)

MyEditor.java - get a Spreadsheet

    private Spreadsheet spreadsheet;
    ...
    public MyEditor(String keikaiServerAddress) {
        spreadsheet = Keikai.newClient(keikaiServerAddress);
        // close spreadsheet Java client when a browser disconnect to keikai server to avoid memory leak
        spreadsheet.setUiActivityCallback(new UiActivity() {
            public void onConnect() {
            }

            public void onDisconnect() {
                spreadsheet.close();
            }
        });
    }
  • line 4: In the MyEditor.java, it actually calls an overloaded newClient() with 2 parameters. I will explain it in the later section.

MyEditor.java - get a Keikai UI Client JavaScript

    public String getJavaScriptURI(String elementId) {
        return spreadsheet.getURI(elementId);
    }

In order to make ${keikaiJs} resolve to Keikai JavaScript URL, you need to store keikaiJs as an attribute by calling setAttribute().

request.setAttribute(Configuration.KEIKAI_JS, keikaiJs);

After the 2 simple steps above, you can now visit the page with Chrome and try out Keikai at http://localhost:8080/tutorial/editor

In order to show you the usage in the simplest way, we used a Java Server Page (JSP), but it's not limited to JSP. You can use any Java UI technology/framework you like as long as the technology/framework can load JavaScript dynamically.

Use Keikai

You can manipulate Keikai in two ways – via spreadsheet UI (with a browser) or via spreadsheet Java API.

Spreadsheet UI

Using Keikai spreadsheet with your browser is just like using Excel or Google Sheet. From top to bottom, there is a toolbar, a formula bar, a sheet, and a worksheet tab. Anyone can pick it up easily and start editing in the way he or she is used to, without having to read a user manual.

spreadsheet UI

You can move the focus with arrow keys, enter text, numbers, or formulas in a cell, click buttons on the toolbar, and switch sheets.

Hotkeys

The currently supported hotkeys are:

  • Ctrl-C | Copy
  • Ctrl-X | Cut
  • Ctrl-V | Paste
  • Ctrl-Z | Undo
  • Ctrl-Y | Redo
  • Delete | Clear Content
  • Esc | Clear or Copy/Cut Clipboard
  • Shift + Up/Down/Left/Right | Update Selection
  • Ctrl + Up/Down/Left/Right | Move the Focus to the Edge

Spreadsheet Java Client API

Keikai also comes with a complete set of Java API which let you control Keikai to achieve spreadsheet related functions including editing, like set/get data, style, format, manipulating cells/rows/columns, and all other operations that you can do on a toolbar/context menu. In the following sections, you will learn how to use these APIs.

Load an xlsx File

When visiting a page with Keikai spreadsheet in your browser, it gives you a blank sheet. In most cases, you will probably like to load your existing Excel file or template for further editing. There are 2 ways to load an existing file:

Upload

The easiest way to import a file is to click the upload button on the toolbar to upload an xlsx file. Note that xlsx is the only format that Keikai accepts.

Import

Another way is to import a file with the API of Spreadsheet below:

MyEditor.java

spreadsheet.importAndReplace(defaultXlsx, defaultFile);

In the tutorial project, we load xlsx files from the default folder /WEB-INF/book/.

Access Cell Values

Next, you may want to change a cell's value or insert a column. Then you must know Range API.

Range API

A Range object can represent a single cell or a range of cells in a worksheet. It is the main class that allows you to manipulate values, styles, and formats of cells. You can also perform an user action such as "insert"/"delete" cell/row/column, "merge", "sort", "filter", or "auto fill" or any other operations you can do on a toolbar/context menu.

Keikai Java client offers different ways to get a Range for different purposes, including:

One cell:

spreadsheet.getRange(0, 0); //rowIndex, columnIndex
spreadsheet.getRange("A1");

Multiple cells

spreadsheet.getRange(0, 0, 2, 2);  //rowIndex, columnIndex, numberOfRows, numberOfColumns
spreadsheet.getRange("A1:B2");

Active cell

Active cell means the cell being selected currently in a browser.

spreadsheet.getActiveCell();

If you didn't specify a sheet index in those methods, by default it will return cells in the active sheet, which is the currently selected sheet.

To select cells in a specific sheet:

// bookName, sheetIndex, rowIndex, columnIndex
spreadsheet.getRange("app.xlsx", 0, 0, 0);
// bookName, sheetIndex, numberOfRows, numberOfColumns
spreadsheet.getRange("app.xlsx", 0, 2, 2);

For complete API list, please refer to Javadoc.

Set a Cell Value

You can pass a String or a Number to set a cell's value.

range.setValue("text");
range.setValue(123);
range.setValue(3.5);

If the range contains multiple cells, then it will fill all cells in the range with a same value.

Get a Cell Value

The simplest way is:

String text = range.getValue();
Number number = range.getValue();

This method will return String or Number, so you have to assign to the expected type.

If you want a specific return type, you can call the following methods:

range.getRangeValue().getCellValue().getStringValue();
range.getRangeValue().getCellValue().getDoubleValue();
range.getRangeValue().getCellValue().getBooleanValue();
range.getRangeValue().getCellValue().getDateValue();

Get Multiple Cell Values at Once

From the architecture above, you can know that each method calling of Keikai Java client requires to communicate with Keikai server via the network. To have better performance and shorter network transmission time, it's better to get multiple values at once than one cell by one cell.

List<String> cellValues = spreadsheet.getRange(row, col, 1, 4).getValues();
String value = cellValues.get(0);

Insert Rows/Columns

To insert one/multiple rows, you have to select rows or columns first. Then, call insert() to insert the same number of rows in the range. For example, if the range contains 3 rows, then insert() will insert 3 rows.

Insert 3 Rows

Range range = spreadsheet.getRange("1:3")
range.insert(InsertShiftDirection.ShiftDown, InsertFormatOrigin.LeftOrAbove);

The 1st parameter specifies the direction Keikai shifts existing rows. We usually shift rows down so we specify InsertShiftDirection.ShiftDown here. The 2nd parameter specifies where Keikai should copy the style from. In this case, the new 3 rows will copy the styles from the row above them.

Insert 3 Columns

The same rule applies to column insertion. You need to get a column range and call insert() with similar parameters.

spreadsheet.getRange("A:C");
range.insert(InsertShiftDirection.ShiftToRight, InsertFormatOrigin.LeftOrAbove);

Delete Rows/Columns

The deletion API is simpler than insertion because you only need to specify the shift direction:

Range range = spreadsheet.getRange("1:3");
range.delete(DeleteShiftDirection.ShiftUp);

Convert to Entire Row/Column

Sometimes you only have a range of cells returned by a method: Range range = findRange(); //return a range of A1:A3

But you want to perform an operation that works on the whole row/column. Keikai offers an easy way to turn your range selection into row/column selection, just call:

range.getEntireRow();
range.getEntireColumn();

If range is A1:A3, then getEntireRow() returns a range 1:3, row 1 to row 3.

For details, please refer to Javadoc.

Applying Styles & Formats

To apply a style, we have to:

  1. Create the style related object, e.g. Font, Borders, or PatternFill

  2. Assign the style object to the Range

For example, to make a cell text bold:

Font font = range.createFont();
font.setBold(true);
range.setFont(font);

You can follow a similar way to apply borders:

Borders borders = range.createBorders(Borders.BorderIndex.EdgeBottom);
borders.setStyle(Border.Style.Thin);
borders.setColor("#000000");
range.setBorders(borders);

Number/Date/Time Format

setNumberFormat() can be used to set numbers, dates, or time formats:

range.setNumberFormat("###");
range.setNumberFormat("@");
range.setNumberFormat("yyyy-mm-dd");
range.setNumberFormat("h:mm:ss");

At this point, you already can load an xlsx file in Keikai and access cells to perform basic operations like getting/setting values, insertion, deletion, and formatting. In the next section we will quickly go through some of the advanced features that you can do with Range.

Advanced Features

Data Validation

Data validation allows you to make a list of the entries that restrict the values allowed in a cell. Input values will be checked against the restrictions. In the Budget Summary Application Demo of the tutorial project, quantity fields are restricted to accept only positive integers. It will prompt a warning when a non-positive integer is being inputted.

DataValidation validation = selectedRange.createDataValidation();
validation.setFormula1("=A1:A4");
validation.setType(DataValidation.Type.List); //currently-supported type
validation.setAlertStyle(DataValidation.AlertStyle.Stop);
validation.setInputTitle("my input title");
validation.setInputMessage("please input");
validation.setErrorTitle("sorry");
validation.setErrorMessage("error value");
selectedRange.setDataValidation(validation);

Freeze

You can lock rows/columns or an area so that the area is always displayed on the screen. To freeze cells in Keikai:

Select a cell then call range.freezePanes()

You can explore more methods at Range Javadoc.

Add Event Listeners

If you plan to create an application based on Keikai or integrate Keikai with your existing system, you definitely need to implement your application logic according to user actions. When a user interacts with Keikai spreadsheet, Keikai will send events to the server and invoke the corresponding event listeners you added.

For instance, if you need to perform some batch check when a user clicks a cell, you can add a listener like:

RangeEventListener rangeEventListener = new RangeEventListener() {
    @Override
    public void onEvent(RangeEvent rangeEvent) throws Exception {
        // implement your application logic
    }
};
spreadsheet.addEventListener(Events.ON_CELL_CLICK, rangeEventListener);

For complete event list, please check Events Javadoc.

Hide Toolbar and Toolbar Buttons

Keikai spreadsheet UI is customizable. In some cases, you may need only the data grid without letting the user use the toolbar, just like the Budget Summary Application Demo in the tutorial project. You can simply hide the entire toolbar with a data attribute specified on the anchor element:

app.jsp

<div id="spreadsheet" data-show-toolbar="false" >

Hide Buttons

If you wish to hide some of the buttons on the toolbar, you need to provide a different Settings when getting Spreadsheet.

MyEditor.java

spreadsheet = Keikai.newClient(keikaiServerAddress, getSettings());

Then override the toolbar configuration in Settings with :

MyEditor.java

    protected Settings getSettings() {
        Settings settings = Settings.DEFAULT_SETTINGS.clone();
        String customToolbarConfig = "{\"items\": \"upload,newBook,exportToFile|" +
                "paste,cut,copy|" +
                "fontName,fontSize,fontItalic,fontBold,fontUnderline,fontStrike," +
                "fontColor,border,fillColor|" +
                "verticalAlign,horizontalAlign,wrapText,mergeAndCenter|" +
                "numberFormat|" +
                "insert,delete|" +
                "clear,sortAndFilter|" +
                "gridlines,protectSheet|" +
                "freeze,hyperlink\"}";
        settings.set(Settings.Key.SPREADSHEET_CONFIG, Maps.toMap("toolbar", customToolbarConfig));
        return settings;
    }

Toolbar config is a JSON format string. In the Online Editor Demo of the tutorial project, we hide "Save Book" button by removing saveBook from the string.

Work with a Database

In the Budget Summary Application Demo of tutorial project, we demonstrated a simple idea to work with a database. Keikai allows you to use any preferred database, but you need to implement a persistence layer to communicate with your database by yourself. Then retrieve or save data via the persistence layer.

Take Budget Summary Application Demo as an example, there are several classes:

  • AppServlet: Works as a controller, handles HTTP requests. It calls MyApp to achieve business function.
  • MyApp: Service layer. Implements application logic with Keikai Java client API. It relies on SampleDataDao to communicate (query/save) with the database.
  • SampleDataDao: Persistence layer. Connects to a HSQL database with JDBC. This DAO (Data Access Object) class is responsible for query and save expense data into the database.

From this simple architecture, it is pretty clear that communicating with a database is independent of Keikai Java client. Therefore, you can implement the database connection in your preferred way.

Welcome Your Feedback

In this getting started article we have covered all the basics of using Keikai, including installation and cell operations through UI and API. Hope this article can help you get started with Keikai easily. If you are interested in any other topics, feel free to send us your comments.