How I Visualize My Spreadsheet Formula Dependencies in a Chart
When bringing Excel files online we can do more. This article shares how I leverage common web components to visualize formula dependencies in a custom way.
Join the DZone community and get the full member experience.Join For Free
The formula is the heart of an Excel file. And of course, we all want to do correct calculations and deliver accurate results. When there is something wrong, we want to trace back to those referenced cells to find the root cause. Excel natively has a built-in convenient formula dependent/precedent trace feature, it highlights the dependent/precedent cells and displays arrows to indicate the relationships. This helps users to trace back and find any error formulas easily.
Now, when bringing the spreadsheet online, we can do more. We can display the relationships in a custom way that is most useful according to the application context, or bring them to a different service or application for doing the validation programmatically.
What to Build
My goal is to list the precedent and dependent cells of a target cell and show them in 2 different forms: a list and a network graph. Here's a short demo:
Click an item in the list and it will jump to the corresponding cell:
Let me explain how I build this application.
Design the UI
First, I put Keikai on the page. Each tag represents a component and its attributes determine its functions and appearance. Please check Developer Reference for details.
- Line 1: extracted from displayTrace.zul.
Then add a block to show the cell to trace its formula. ZK allows you to mix HTML tags with ZK tags by declaring a namespace
xmlns:h="native". For a UI that doesn't need to change dynamically, I can just use HTML to save memory.
Next, put 2 listboxes to show precedents and dependents respectively.
- Line 1:
<window>is a container to group components in a window-style box. (See demo.)
- Line 3:
<listbox>is used to show a list of items. It accepts a data model and renders the data model with a template. (See demo.)
- Line 7: You can design a template to render each object in the data model.
To show precedents and dependents in a network graph, I need to add a chart component:
Finally, arrange them in the horizontal layout:
ZK provides various layout components, I just go with the one that's most suitable here.
How to Implement Application Logic
After building the UI, I need to add application logic. For example, when clicking the 'trace' button, I wish to show precedents and dependents in listboxes. To do this I need to create a controller.
ZK supports MVC pattern: Model-View-Controller. The zul plays 'View.' It provides a SelectorComposer to play 'Controller.' Hence, I need to create a class that extends
SelectorComposer and associate the zul with the controller with
Control Components by Java
In the ZK MVC pattern, I can control a component by calling its API. When a user visits a zul page, ZK will create each component's Java object on the zul, and the controller can get component references with
@Wire and selector syntax. Therefore, I don't need to instantiate each component like
new Spreadsheet() by myself. Here are some examples:
- Line 3: The default case, wire by component ID as the variable name,
spreadsheet. In the zul, there should be
- Line 6: Wire a component that is a child of
windowand has a CSS class
For complete syntax, please refer to JavaDoc.
After components are created, sometimes we want to initialize them, e.g., configure chart options, then I have to override a lifecycle method
doAfterCompose() and implement my initializing logic:
- Line 10: ZK will call this method after all components are created. (see JavaDoc.)
- Line 13~19: Initialize chart options.
Event-Driven Programming Model
ZK framework supports the event-driven programming model. Each component fires different events when users interact with them e.g. clicking a button or selecting an item. After applying a controller, I can register event listeners in a controller to listen to events triggered by user action and implement application logic in an event listener. To register an event listener method, I have to put
@Listen on a method in a controller like:
- Line 2: It registers a listener for
ON_CLICKevent on the component whose ID is
trace. Please read Wire Event Listeners for details.
To know what precedent and dependent are, please read the Microsoft Office doc. Keikai provides methods to get them:
These 2 methods return a set of
Range , so that I can display them in 2 different forms: a list and a network chart.
Trace in a List
Because Listbox supports model-driven rendering which is also in MVC architecture:
To show precedents/dependents in a Listbox, I put them into a
ListModelList and pass it to
ListModelList will automatically notify a
Listbox to render items in a browser when I add remove Range.
- Line 7~8: I can assign an empty
Listboxfirst and add items later.
- Line 19~20: put precedents and dependents into a
ListModelList, this will automatically notify a Listbox to render them into browsers.
Trace in a Network Graph
A ZK chart also supports model-driven rendering, what I need to do is similar: add data points into a chart, and the chart will render points into a browser.
- Line 6, 16:
getSeries(1)returns 2 series respectively, so I can give them different colors.
I can trace to 2nd level of precedents and dependents:
Here is an example for Input!J15.
Jump to a Precedent/Dependent
When tracing a formula's precedent, it's quite convenient to jump to the precedent cell and keep inspecting the next precedents and repeat until we find where is wrong. The same rule applies to dependents. Therefore, I implement a feature: click an item (cell) in Listbox and jump to the cell.
Forward an Event
Because a Listbox is empty and doesn't have any Listitem when the page is loaded, a controller can't register a listener on a non-existing component. So I have to forward
onClick event to an existed component,
Window and register a listener on it.
- Line 6: forward
onClickto its ancestor Window whose ID is
rangeWinwith a custom event name
Focus a Cell
- Line 1: Listen to the custom event
onFocusCellspecified in a zul (see the previous section).
- Line 7:
focusTo()will scroll a sheet and focus on the specified cell.
Spreadsheets are no doubts one of the most popular business applications in the world. When spreadsheets are brought online, we can do more than just duplicating Excel's functionality. This example combines the modern web UI components with the classic spreadsheet to demonstrate how you can display formula dependencies in a modern and custom way. The complete source code can be found at Github.
Opinions expressed by DZone contributors are their own.