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 src at <spreadsheet/>. The zul is an XML-formatted UI language supported by ZK framework. formHelp.zul.

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.

https://i.imgur.com/nQLlHnX.jpeg

Then I use the <include> component to include that help HTML into <drawer>.

  • 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:

  • SelectorComposer helps me to get a reference of helpDrawer which is instantiated by the ZK framework. That’s why I declare a variable of Drawer without instantiating its object.
  • @Listen can register the method as an event listener for Events.ON_CELL_CLICK of the spreadsheet. So when a user clicks a cell, ZK will invoke this method.
  • RangeHelper.getTargetRange(e) returns a Range that 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:

  1. Click a cell to vote for a hotel.
  2. Click a hotel to show more information and also vote for the hotel.

Here is a short clip for the features:

Short Clip of Features

Step 1: Load Pre-designed Excel File

Firstly, I create a table of hotels and voting checkboxes in an Excel file like:

Pre-designed Excel FileLike 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

Add ON_CELL_CLICK event listener:

To check the cell clicking easily, I create 4 named Range: Vote1 ~ Vote4 for 4 voting areas, so that I can easily determine whether a user clicking is inside these 4 ranges or not.

Vote Ranges

  • 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

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 helpDrawer
  • 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

Vote This Hotel Button

After the 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 onVote event on the drawer
  • Line 3~4: convert user-clicked cell into the cell in Vote1 to update the checkmark and vote count.

Summary

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.

Source Code

You can see the complete source code in Keikai developer reference repository.



Source link

Write A Comment