3 Steps to Give Your Spreadsheet a Modern Touch
Spreadsheets are no doubt the most popular business application – they are heavily used in all fields, favored by users from finance experts in Wall Street to top scientists in NASA. Spreadsheets allow users to calculate, organize and store data, and most important of all, analyze data without having to program.
Nowadays spreadsheets extend their reach more – they no longer sit inside a user’s Excel desktop application, they are now widely used online, either being embedded inside applications or run on an online platform like google sheets. But when a spreadsheet goes online, can we do more than just duplicating Excel’s functionality into the browser?
More Than a Spreadsheet
While spreadsheets are great for displaying tabular data, presenting forms or charts, it is not always great to display a large block of text or display images and other elements that have varied dimensions. This is where we can leverage today’s fancy and easy-to-use web UI components to work with an online spreadsheet and upgrade spreadsheet users’ experience.
Here I am taking Keikai spreadsheet and a ZK drawer component as an example demonstrating how this can be done in a few steps. In this example, we will add a slide-in/out Help panel to our spreadsheet where we can display large blocks of Help text in a much more easy-to-read format.
Let me show you how to build this application.
Step 1: Load the Excel File
I can load my
helpTemplate.xlsx in a zul by specifying
<spreadsheet/>. The zul is an XML-formatted UI language supported by ZK framework.
Other attributes of the tag determine spreadsheet looking; please refer to Keikai Developer Reference.
Step 2: Include Additional UI Components
Then I will create the help page and show it in a sliding drawer.
There are 5 blocks of numbers in the form above. Hence, I create an HTML page and put help descriptions into 5 different color boxes. Each box has a corresponding help box with the same background and title. So people who read the help page can easily identify which help text matches their needs for the current block.
Then I use the
<include> component to include that help HTML into
visible="false"means it’s hidden by default, so you won’t see the drawer at first. But users can click the ‘Help’ cell to show the help.
Note, this is just a simple example; you can definitely put any other HTML content (images, videos…) to the help page based on your own context.
Step 3: Implement Application Logic with Event Listeners
To show the help panel when a user clicks the Help button (made by a cell with borders), I need to add an event listener for the cell click event in a controller. Keikai supports the MVC pattern, so it’s better to implement my application logic in a controller with a better OO design. The controller looks like this:
SelectorComposerhelps me to get a reference of
helpDrawerwhich is instantiated by the ZK framework. That’s why I declare a variable of
Drawerwithout instantiating its object.
@Listencan register the method as an event listener for
Events.ON_CELL_CLICKof the spreadsheet. So when a user clicks a cell, ZK will invoke this method.
Rangethat represents the clicked cell. Then I can check the cell’s value by
cell.getCellValue().toString()to know whether a user clicks the ‘Help’ cell or not.
helpDrawer.open()makes the drawer slide in.
With these 3 steps, you can combine the best of both worlds: the classic spreadsheet and the modern web.
Advanced Example: Turn an Excel File into a Hotel Voting App
The example above shows how you can integrate a static “help” with your sheet. Now let me take it further and demonstrate a more complicated example where the additional UI component (panel) interacts with the spreadsheet.
This example is a hotel voting application for all employees. For an employee, he can:
- Click a cell to vote for a hotel.
- Click a hotel to show more information and also vote for the hotel.
Here is a short clip for the features:
Step 1: Load Pre-designed Excel File
Firstly, I create a table of hotels and voting checkboxes in an Excel file like:
Like step 1 in my previous example, load the designed Excel file into Keikai with a zul.
Step 2: Add Additional UI Components
Similar to the previous step 2, I still add a
drawer to show the ‘More Info’ panel on the right-hand side. Since I need to show different details for different hotels, I don’t include a fixed page this time. I will inject a different zul page for each clicked hotel.
Step 3: Implement Application Logic with Event Listeners
Keikai supports an event-driven programming model, I should implement application logic in event listeners.
Click to Vote a Hotel
ON_CELL_CLICK event listener:
To check the cell clicking easily, I create 4 named Range:
Vote4 for 4 voting areas, so that I can easily determine whether a user clicking is inside these 4 ranges or not.
- Line 4:
Ranges.rangeByName(), get a range of cells by a name. With this approach, the Java code can resist cell position change which is better than using cell reference.
Then, I need to update the checkmark position and the corresponding vote count.
- Line 8-9: I can change the checkmark’s position by setting the cell value with ‘√’.
- Line 17:
CellOperationUtil.applyFontColor()can change the text color in cells.
Show More Info When Clicking on a Hotel
When a user clicks a cell on Day 1 (
C6:F9), I will call
helperDrawer.open() to show more information. But each hotel has different details, I create each zul for each hotel like:
Silver Oyster Resort.zul Silver Mountain Resort.zul Ivory Baron Hotel.zul Crown Lodge Resort & Spa.zul
Then I put a template injection component inside the
drawer, so that I can dynamically switch the page inside the template.
- Line 1,6: create a template inject component and put it into
- Line 10: get hotel name from cell value
- Line 11~12: switch to the corresponding zul page and enforce
apply()to recreate the new zul again
‘Vote This Hotel’ Button
drawer opens, it shows more information of a hotel and a “Vote This Hotel” button and users can click the button to vote for the said hotel. Because the page is dynamically created for each cell clicking, I can’t add a listener to the button. I need to forward its
onClick event to
drawer (see Event Forwarding).
Then listen to my custom forwarding event
onVote to update the vote count:
- Line 1: Listen to
onVoteevent on the
- Line 3~4: convert user-clicked cell into the cell in
Vote1to update the checkmark and vote count.
Spreadsheets are brought online for easier sharing and collaboration. But we can do more! This example uses Keikai Spreadsheet and ZK components to demonstrate how you can leverage rich UI components to upgrade a user’s spreadsheet experience while preserving its benefits.
You can see the complete source code in Keikai developer reference repository.
Credit: Source link