Embed a Spreadsheet Into Your Web App
A developer gives a tutorial on how to embed a Keikai spreadsheet into your web application that allows users to interact with and download the spreadsheet.
Join the DZone community and get the full member experience.Join For Free
Having an Embedded Spreadsheet Is Handy
Spreadsheets are used everywhere. There's no doubt that a spreadsheet application like Excel is one of the most popular business applications in the world. It is so easy that anyone can use it, yet it is so powerful that it enables experts in different fields to “code” their business logic in the simplest way possible.
A standalone spreadsheet is great for personal use, but it's hard to share. That is why Google Sheets was born. With Google Sheets, it becomes much easier to share and collaborate. But people want more — they want to have the sheet directly on their web pages; whether it is a web application, a wiki page, or a company website.
An easy way, like what Google Sheets offers, is to use an iframe to embed a spreadsheet into your web page. With a single iframe tag, you can easily display a spreadsheet on your web page. This is convenient if you wish to just “display” a sheet – but if your web page needs to interact with the spreadsheet, then iframe becomes a barrier you need to cross.
Here, I’d like to introduce an example embedding a Keikai spreadsheet into any web page and interact with your page, no matter on which platform your web application is based, e.g. Node.js, Python, or PHP.
Assume you have an existing web application managing the inventory of your products, and now you are adding a new feature called "Stock Search." Users can input some criteria to search for in-stock goods from a database, and the application will show the result in a spreadsheet. Then users can edit the list and process it as needed. For example, a user can search for low-stock goods and edit and export the search result as an XLSX or PDF file and send it to the vendor to place an order. Also when the user clicks on a specific good, it will show the vendor's contact information on the same row.
In this article, I will focus on "embedding" a Keikai spreadsheet in an external app, and "the interaction" between the spreadsheet and the external page. Hence, I will assume the Keikai application is already built for "embedding" and "interaction." The external web page can be served by any non-Java server, e.g. a Node.js server.
For those who are interested in learning to build a Keikai application, please read the following articles:
The image below shows the relationship between the two applications and how the process of embedding Keikai works. Under this architecture, you still need to implement spreadsheet-related application logic in Java instead of other programming languages.
The process is:
- A browser requests an HTML page of the stock search app.
- The HTML page requests a zul file of the Keikai-based app.
I designed the page with the Bootstrap grid system and divided the page into rows.
Only the spreadsheet is embedded with Keikai at
<div id="embed">, so it's empty. Other HTML elements are already created on this page.
How to Embed
keikai-app is just an example – you need to replace it with your Keikai application context root.
zEmbedded API so that I can use it to embed a zul file.
Because the stock search application is normally in a different domain from the Keikai-based application, you need to set the HTTP-header to allow Keikai resources to be loaded by a cross-origin request.
You need to set at least the following CORS headers:
You can reference
CorsHeaderFilter to set these headers.
The API to Embed
Then, call the ZK embedding API,
zEmbedded.load(), to embed zul into the HTML page like:
http://keikai-app/useCase/stock-search.zul, is the same URL as the one you visit with a browser.
The first parameter,
embed, is the anchor element ID that Keikai will render itself in. It's just a
<div id="embed"> element in my example.
Communication Between a Web Page and Keikai
After embedding Keikai, you can see the file rendered in the HTML page, and you can edit and scroll. But it still lacks communication between the HTML page and Keikai itself. I will explain how to do this by implementing "search product" and "show vendor contact information."
There is a Java controller applied on Keikai at the server-side. If you check
search-stock.zul, you will see a class name at
The controller can receive the events from the client-side, query products from a database, and contain event listeners with application logic like exporting and accessing cell values.
Fire Events to the Server
fire() to fire events to the Keikai Java controller.
Export XLSX and PDF
I have defined the event name,
onExportExcel, in the Java controller. So, what I now have to do is to fire the event at the client-side to invoke the event listener of the Java controller:
The ZK framework contains jQuery by default, so you can use
$('#exprtExcel').on() to register an
onclick listener on the button
<button id="exprtExcel" class="btn btn-primary">Export to Excel</button>.
In the server-side controller, I implement exporting with Keikai's
Exporter in a few lines.
Exporting a PDF is quite similar, please check the source code on GitHub.
ZK Widget Selector
zk.Widget.$(mySelector) is a function that can get a ZK widget reference by a selector syntax. The ID
spreadsheet is the value specified in the "id" attribute of
Search Products With Criteria
When a user clicks the "Search" button, this application can show products in the sheet based on the criteria.
Firing an Event at Client-side
You can follow a similar pattern to invoke the
onSearch event listener, but this time I need to pass search criteria as the second parameter.
Extract user input as a JSON object:
Receive the Parameter
event.getData(), I can get a JSON object sent from the client-side and convert it to
- Line 1: The annotation to register an
onSearchevent listener on the component
- Line 4: The
productServicejust represents a business layer class – it can return a list of products upon given criteria. You can imagine that class as any of your business classes, just to perform a business operation.
Populate the Result
To populate the product list into the sheet on the browser, you need to call an important API
Range. There are two basic steps:
- Create a
Rangeobject that represents one or multiple cells, e.g.
Rangemethods to get or set data on that range, e.g.
After invoking the event listener, Keikai will communicate with the client-side widgets to render cell values on the sheet. I don't need to take care of communication and rendering details.
For more APIs, please refer to the Keikai Developer Reference Guide.
Show a Vendor Contact
When a user clicks on a cell, it will show the vendor contact information at the bottom of the page from the server-side. This demonstrates how a sheet interacts with a page (triggered inside a sheet).
The Java method,
In this example, I assume the server has the vendor data. Therefore, I register an
Events.ON_CELL_CLICK listener that queries a vendor by its name. Then, every click on a cell invokes this method:
- Line 4: I locate the cell of the vendor name based on the row the user clicked and find its fourth column cell.
- Line 5: Get the cell value as a string.
Render the Vendor Contact
At the client-side, I simply just render the vendor contact on the page with jQuery:
To wrap up, I have demonstrated how you can embed a spreadsheet into any web page, and how the spreadsheet can interact with the web page in both directions. I hope this helps you bring sme spreadsheet power into your web applications.
Get the Source
The GitHub repository contains the runnable code so that you can try it by yourself.
Opinions expressed by DZone contributors are their own.