Build a Web Application Based on Your Excel Files
Build a Web Application Based on Your Excel Files
In this article, build a web application based on your Excel files.
Join the DZone community and get the full member experience.Join For Free
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
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) —
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:
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.
The controller for Keikai is a Java class that extends ZK
SelectorComposer, and it interacts with the database via
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).
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:
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.
Range object, you can perform an action like
Populate Data Into Cells
After you query one or more
Trade from the database, you can populate it into the target cells with
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.
Annotate a method with @Listen to turn it as an event listener method with CSS selector-like syntax below:
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.
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:
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.
I hope you enjoyed reading my article. Visit GitHub to get the complete source code mentioned in this article.
Opinions expressed by DZone contributors are their own.