Build a Web Application Based on Your Excel Files

Many companies have operated their business with Excel for decades. It is powerful yet easy to use, however, one of the biggest issues with Excel is that it is hard to integrate an Excel file with other systems and services, especially with a database. If you upload an Excel file as an attachment to a system and have to download it and open it up in Excel whenever you need to edit the file, you are just using Excel in a standalone manner. This is not an integrated solution.

In this article, I will show you how you can make your Excel file work seamlessly in a database-driven web application.

Database Table Access Application

Assume you have a table containing trade records in a database and you need to show these trade records in a spreadsheet-like UI so that users can edit, filter, and calculate the data with Excel formulas. Besides, your users are capricious — the color, format, and style of the UI may need to be changed from time to time, so it should be easy to update in the future.

You may also like:  Turn Your Excel File Into a Web Application

Template-Based Approach

To fulfill the requirements above, I'd like to introduce you to a template-based approach: use an Excel file as a template and populate the data from the database into the template.

In an architectural view, I divide the application into 3 parts: Model, View, Controller.

• Model: the data from the database
• View: the template file
• Controller: listener to events from the View and populate data from the database

Mapping them to my implementation, it is:

Keikai Spreadsheet (View) — DatabaseComposer (Controller) — MyDataService (Model)

Implementation

Overview

Assume there is a trade record table:

Prepare an Excel file as a template:

Combining the template and the data, my resulting web application will look like:

An end-user can:

• edit cells and save back to the database
• reload the data from the database

Build the UI

In this application, I rely on an open source web spreadsheet component Keikai. Keikai is based on ZK UI framework, hence I can build the UI in a ZUL, leveraging other ZK components as well. ZUL is an XML format language where each tag represents a component, so I can create the UI like:

xxxxxxxxxx

<spreadsheet id="ss" width="100%" height="200px"

                showFormulabar="true" showContextMenu="false" showToolbar="false"

                showSheetbar="false" maxVisibleRows="11" maxVisibleColumns="4"

                src="/WEB-INF/books/tradeTemplate.xlsx"></div>

<div style="margin: 10px 5px 10px 0px; text-align: right">

    <button id="save" label="Save to Database" ></button>

    <button id="load" label="Load from Database" disabled="true"></button>

</div>

Line 4: you can load a file dynamically via Java API, Spreadsheet::setSrc or Importer.

Keikai can load an Excel file and render its content in a browser. Once rendered, end users can view and edit the spreadsheet in the browser in an Excel-like manner.

Controller

The controller for Keikai is a Java class that extends ZK SelectorComposer, and it interacts with the database via MyDataService.

xxxxxxxxxx

public class DatabaseComposer extends SelectorComposer<Component> {

    private MyDataService dataService = new MyDataService();

    @Wire

    private Spreadsheet ss;

 ...   

}

line 4: With @Wire on a member field, the underlying ZK framework can inject keikai Spreadsheet object created according to the zul, so that you can control keikai with its method.

Apply to the Page

We need to link DatabaseComposer with the ZUL page (database.zul) so that the controller can listen to events and control components via API.

Specify the fully-qualified class name at apply attribute, then Keikai will instantiate it automatically when you visit the page. The controller can control the root component, <hlayout>, and all its children components (those inner tags).

xxxxxxxxxx

<hlayout width="100%" vflex="1" apply="io.keikai.tutorial.database.DatabaseComposer">

...

    <spreadsheet ></spreadsheet>

<hlayout>

Range API

For each cell/row/column operation, you need to get a Range object first. It can represent one or more cells, a row, a column, a sheet, or a book. Just like you need to select a cell with your mouse before you take any edit action.

The helper class Ranges supports various methods to create a Range object like:

xxxxxxxxxx

// a book

Ranges.range(spreadsheet.getBook());

// a sheet

Ranges.range(spreadsheet.getSelectedSheet());

// a row

Ranges.range(spreadsheet.getSelectedSheet(), "A1").toRowRange();

// a cell

Ranges.range(spreadsheet.getSelectedSheet(),  3, 3);

// multiple cells

Ranges.range(spreadsheet.getSelectedSheet(), "A1:B4");

Ranges.range(spreadsheet.getSelectedSheet(), 0, 0, 3, 1);

Getting a Range for one cell requires a sheet, row index, and column index as the coordinate, and getting multiple cells requires starting and end row/column index.

With a Range object, you can perform an action like setValue() or getValue().

Populate Data Into Cells

After you query one or more Trade from the database, you can populate it into the target cells with Range setter:

xxxxxxxxxx

//column index

public static int ID = 0;

public static int TYPE = 1;

public static int SALESPERSON = 2;

public static int SALES = 3;

...

private void load(Trade trade, int row) {

    Sheet sheet = ss.getSelectedSheet();

    Ranges.range(sheet, row, ID).setCellValue(trade.getId());

    Ranges.range(sheet, row, TYPE).setCellValue(trade.getType());

    Ranges.range(sheet, row, SALESPERSON).setCellValue(trade.getSalesPerson());

    Ranges.range(sheet, row, SALES).setCellValue(trade.getSale());

}

Listen to Events

There are 2 buttons on the page that we need to listen to their click event and implement related application logic. Specify 2 buttons’ id so that you can easily listen to their events.

xxxxxxxxxx

<button id="save" label="Save to Database" />

<button id="load" label="Load from Database" disabled="true"/>

Annotate a method with @Listen to turn it as an event listener method with CSS selector-like syntax below:

xxxxxxxxxx

@Listen("onClick = #load")

That means you want to listen to onClick event on #load which represents a component whose ID is load. For more syntax, please refer to SelectorComposer Javadoc. Therefore, when a user clicks “Load from Database” button, DatabaseComposer::load() will be invoked.

xxxxxxxxxx

//Load from Database

@Listen("onClick = #load")

public void load(){

    reload();

    ...

}

//Save to Database

@Listen("onClick = #save")

public void save(){

    dataService.save(modifiedTrades);

    ...

}

Then, you can implement related application logic in each listener according to the requirements.

Save Data Into a Table

Before you save a Trade, you need to extract user input from cells with getter. You still need a Range, but you will call better this time, like:

xxxxxxxxxx

private Trade extract(int row ){

    Sheet sheet = ss.getSelectedSheet();

    Trade trade = new Trade(extractInt(Ranges.range(sheet, row, ID)));

    trade.setType(Ranges.range(sheet, row, TYPE).getCellEditText());

    trade.setSalesPerson(Ranges.range(sheet, row, SALESPERSON).getCellEditText());

    trade.setSale(extractInt(Ranges.range(sheet, row, SALES)));

    return trade;

}

private int extractInt(Range cell){

    CellData cellData = cell.getCellData();

    return cellData.getDoubleValue() == null ? 0 : cellData.getDoubleValue().intValue();

}

line 12: Beware - if a cell is blank, CellData::getDoubleValue() returns null.

Persistence Layer Class

To make things easy to understand, I created a class MyDataService to handle queries and updates for the (simulated) database. It can query a collection of Trade objects for us to populate into Keikai and save Trade objects into the database.

In your real application, you can implement your persistence layer classes according to your preference. There’s no limitation here, you can use Hibernate or JDBC or any Java solutions you like.

This wraps up the example application.  

Why Template-Based Approach?

Before finishing the article I would like to talk a bit about why I decided to use this template-based approach.

Maintain Templates by Domain Experts

If you are building a finance system, obviously it’s better to let the finance experts to create their sheets and models instead of asking a software developer to do so. With the template-based approach, domain experts can create/maintain Excel templates by themselves without trying to explain everything to the software developers. Plus, after an Excel file is modified, you can simply replace the template file with the new one without updating your Java code.

Decouple the Display and Data

In this example, since the data is not stored in the file, it's effortless to change both sides: template and data. You can either apply another template or import a different set of trade records according to different contexts.

Easy to Integrate With Other Backend Systems

Because Keikai is controlled by a Java controller class, it’s straightforward to integrate it with any Java-based backend, and there is no limitation for connecting to a database.

Source Code

I hope you enjoyed reading my article. Visit GitHub to get the complete source code mentioned in this article.