Turn Your Excel File Into a Web Application

A Typical Workflow Run by Excel

The other day a young lady in the administration department looked busy so I asked her what she was up to. She said that she has to visit several websites to check the currency rate, put them down in Excel and send it to her boss. The boss will then tell her to buy a certain currency at a certain amount. Finally, she has to keep the record in another Excel file. And this is one of her daily routines.

It is the year 2019. I was surprised that she still has to do lots of copy-paste routines and email Excel files around. My pride in being a developer wouldn't allow it, so I offered to write a little program for her. Fortunately, with Keikai, I was able to do it in just 2 hours.

First, I asked her to lay out what she wishes to see in Excel. Then, I grabbed currency data from the internet and populated it to the desired cells. Next, I implemented page navigation to smoothen the workflow and finally I generated a report. That’s it!

Let me describe in detail how each step was done.

Build a UI With Excel

The best thing about using Keikai is that it takes your Excel file and renders it exactly the same in your browser. This means your Excel file is immediately turned into a web application UI.

This enables your business team, like the woman in admin, to join the system development easily by creating an Excel file as the UI.

So the admin sent me her Buy-Currency Excel file: on the first screen, she wanted to see a selected list of countries and their currency, and the currency data should be populated automatically. On the second screen, she wanted to input the buying amount and the exchanged amount, and, lastly, she wanted to see a report generated automatically that contained Dates, Currency, and Amounts.

One-Line Importing

All I need to do here is to import the Excel file, and now the Excel template renders it in my browser just like we designed.

public static final String INTERNAL_BOOK_FOLDER = "/WEB-INF/book/";
...
public static final String BOOK_NAME = "currencyExchange.xlsx";
...
spreadsheet.importAndReplace(BOOK_NAME, new File(Configuration.DEFAULT_BOOK_FOLDER, BOOK_NAME));

Show Data in a Table

Next, I filled in the currency exchange rates retrieved from the European Central Bank by using setValues() to create the desired cells.

for (String currency :CURRENCY_LIST){
    spreadsheet.getRange(startingRow, 6, 1, 2).setValues(currency, rates.get(currency));
    startingRow++;
}

Now, the admin can see the latest exchange rates being updated without visiting various websites.

Formula Supported

The Excel file she gave me contained formulas for calculating the total amount exchanged according to the buying amount and exchange rate. But nothing to worry, I got a formula supported on each cell out-of-the-box. Don't even need to enable it.

My co-worker can now either input a value directly or enter a formula according to required conditions without calculating numbers in her head, e.g. if she has twenty $100 notes and five $500 notes, she can simply input the corresponding formula .

When I call Range::getValue(), I can get the calculated result of that formula. No need to parse the formula by myself. Isn't that great?

Page Navigation

Next, I will take care of page navigation – once she is done with the current page, it'll take her to the next page. This can be done with activate(). The code below listens to the button clicking event and switches to another sheet.

private void addEventListener() {
    ...

    //listen to buy button
    exchangeSheet.getButton("Buy").addAction(buttonShapeMouseEvent -> {
        placeAnOrder();
        listSheet.activate();
    });

    //listen to "buy another" button
    listSheet.getButton("BuyAnother").addAction(buttonShapeMouseEvent -> {
        selectSheet.activate();
    });
}

Control Accessibility

Now that the application workflow has been implemented, but we need to implement some “safety” measure to make sure that she follows the desired path. By default, a user can edit any cell in a typical Excel file or spreadsheet. But when making a web application, usually, we want users to follow the flow we defined. The following APIs are handy for controlling accessibility.

Customize UI

I customize the UI by hiding the toolbar, context menu, and sheet tabs:

private void setupAccessibility() {
    spreadsheet.getUIService().showToolbar(false);
    spreadsheet.getUIService().showSheetTabs(false);
    spreadsheet.getUIService().showContextMenu(false);
    ...
}

I prevent our administrator from making undesired changes with these APIs, like deleting a sheet (page) by accident. There is another easier way through data attributes.

Limit Visible Area

Also, to let her concentrate on the application, I decided to show a proper area instead of showing her the whole sheet (16,384 columns and 1,048,576 rows) so the lady won't scroll to the end of the world (sheet):

private void setupVisibleArea() {
    selectSheet.setVisibleArea("A1:O17");
    exchangeSheet.setVisibleArea("A1:L12");
    listSheet.setVisibleArea("A:M");
}

Protect a Sheet to Be Read-Only

But hiding toolbar and the context menu are not enough because users can still edit cells arbitrarily. Therefore, I enabled sheet protection to make the whole sheet read-only so that users wouldn’t accidentally delete a currency row or change a currency rate.

public static final SheetProtection PROTECTION = new SheetProtection.Builder().setPassword("").setAllowSelectLockedCells(true).setAllowFiltering(true).build();
...
listSheet = spreadsheet.getWorksheet("list");
...
listSheet.protect(PROTECTION);

Since there are 17 different permissions of a SheetProtection, I create aSheetProtectionobject with a Builder pattern API, SheetProtection.Builder which is a more readable code style.

Limit Editable Area

Now that I’ve made the whole sheet read-only, users can’t edit it anywhere. I need to unlock the cell where the user needs to input the buying cost.

Unlock a Cell

private void unlockCostInput() {
    Range costCell = spreadsheet.getRangeByName(exchangeSheet.getSheetId(), "cost");
    CellStyle unlockedStyle = costCell.createCellStyle();
    Protection protection = unlockedStyle.createProtection();
    protection.setLocked(false);
    unlockedStyle.setProtection(protection);
    costCell.setCellStyle(unlockedStyle);
    exchangeSheet.protect(PROTECTION);
}

• Line 2: I get a range by a defined name, and it can avoid code changes even if I move the input cell address. This approach has better code change resistance than using a cell address, like C5.

So that cell (D7) becomes the only one editable cell in the read-only sheet:

Transaction Report

In the last page, I will show a list of currency transaction in a table.

private void placeAnOrder() {
    listSheet.unprotect("");
    Range costCell = spreadsheet.getRangeByName(exchangeSheet.getSheetId(), COST_CELL);
    if (!costCell.toString().isEmpty()) {
        Double cost = costCell.getRangeValue().getCellValue().getDoubleValue();
        if (cost > 0) {
            Double amount = spreadsheet.getRangeByName(exchangeSheet.getSheetId(), "amount").getRangeValue().getCellValue().getDoubleValue();
            Range orderTable1stRow = spreadsheet.getRange(BOOK_NAME, listSheet.getSheetId(), "C6:G6");
            orderTable1stRow.getEntireRow().insert(Range.InsertShiftDirection.ShiftDown, Range.InsertFormatOrigin.RightOrBelow);
            orderTable1stRow.setValues(DateUtil.getExcelDate(new Date()), cost, destinationCurrency, destinationRate, amount);
        }
    }
    listSheet.protect(PROTECTION);
}

• Line 2: Since it's a protected sheet, I need to unprotect it before setting cell values.
• Line 9-10: Insert a new row and set values in a row at once.

Keikai also saves your efforts to maintain a table's style including background color, borders, font, and filters. When inserting and deleting rows, you just need to call insert(), and Keikai handles all changes on the related styles for you.

Filter Rows Without Coding

Since I enabled the filter in the last sheet of her Excel file, Keikai also enables it after importing. After the user places an order X many times, they may wish to filter some rows according to the criteria, e.g. list only the EUR to USD transactions:

Filtered Result

Even if a filter was not enabled in the Excel file, I can enable the filter programmatically through Keikai API setAutoFilter().

The whole usage looks like this:

Through these steps, I have turned a traditional workflow of sending Excel forms into a web application. Then, these forms immediately become your system UI, and all my colleagues can effortlessly migrate to this Keikai-based web system because they can execute workflows in the way they same way they know without extra learning.

Source Code

I hope you enjoyed reading my article. The complete source code of "buy currency" application mentioned in this article is available at GitHub.

Just run the project and visit http://localhost:8080/dev-ref/case/exchange.