DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Best Gantt Chart Libraries for React
  • How To Create a Network Graph Using JavaScript
  • How To Create a Resource Chart in JavaScript
  • JavaScript Temperature Anomaly Chart

Trending

  • How to Convert Between PDF and TIFF in Java
  • SQL Server Index Optimization Strategies: Best Practices with Ola Hallengren’s Scripts
  • Key Considerations in Cross-Model Migration
  • Beyond Microservices: The Emerging Post-Monolith Architecture for 2025

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.

By 
Hawk Chen user avatar
Hawk Chen
DZone Core CORE ·
Updated Apr. 28, 21 · Tutorial
Likes (6)
Comment
Save
Tweet
Share
11.9K Views

Join the DZone community and get the full member experience.

Join For Free

Overview

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.

In this article, I am taking the Keikai spreadsheet, ZK Listbox, and ZK Charts components to demonstrate how you can display formula precedent/dependent relationships in a table or in a chart.

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:

Formula Tracking Demo

Click an item in the list and it will jump to the corresponding cell:

List Selection Demo

Let me explain how I build this application.

Design the UI

Keikai is a spreadsheet component based on the ZK framework, a Java EE UI framework, by which I can build UI by composing various existing components. To start I just need to create a zul file, written in XML format. When a user visits this zul file, ZK will translate this zul into an HTML page with CSS and javascript widgets.

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.

XML
 




xxxxxxxxxx
1


 
1
<spreadsheet id="spreadsheet" src="/WEB-INF/books/demo_sample.xlsx"
2
             showSheetbar="true" showSheetTabContextMenu="true" 
3
             showToolbar="true" showFormulabar="true"
4
             maxVisibleRows="200" maxVisibleColumns="40"
5
             hflex="2" vflex="1"/>



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

XML
 




x





1
<zk ...
2
    xmlns:h="native">
3
...
4
  <h:div style="display: flex; align-items: center">
5
    <h:h3 style="margin: 10px 0; flex:1">Formula Tracking</h:h3>
6
    <button id="trace" label="trace" />
7
  </h:div>
8
  <separator bar="true" height="20px"/>
9
  <h:div>
10
    Trace cell: [<label id="cellAddress" style="font-weight: bold"/>]
11
  </h:div>



Next, put 2 listboxes to show precedents and dependents respectively.

XML
 




xxxxxxxxxx
1
19


 
1
<window id="rangeWin" title="List" vflex="1" border="normal" contentStyle="overflow-y: auto">
2
    Select an item below to focus the cell:
3
    <listbox width="100%" emptyMessage="no data" rows="3" sclass="precedentBox">
4
        <listhead>
5
            <listheader label="Precedents"/>
6
        </listhead>
7
        <template name="model">
8
            <listitem label="${each}"/>
9
        </template>
10
    </listbox>
11
    <listbox width="100%" emptyMessage="no data" rows="3" sclass="dependentBox">
12
        <listhead>
13
            <listheader label="Dependents"/>
14
        </listhead>
15
        <template name="model">
16
            <listitem label="${each}"/>
17
        </template>
18
    </listbox>
19
</window>



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

XML
 




xxxxxxxxxx
1


 
1
<window title="Chart" vflex="1" border="normal">
2
    <charts hflex="1"/>
3
</window>



Finally, arrange them in the horizontal layout:

XML
 




xxxxxxxxxx
1


 
1
<hlayout height="100%" width="100%" apply="io.keikai.devref.usecase.DisplayFormulaTraceComposer">
2
    <spreadsheet .../>
3
        <div>
4
        <!-- 2 listboxes and a chart -->
5
        </div>
6
</hlayout>



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.

MVC Pattern

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 apply attribute:

XML
 




xxxxxxxxxx
1


 
1
<hlayout apply="io.keikai.devref.usecase.DisplayFormulaTraceComposer">



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:

Java
 




xxxxxxxxxx
1


 
1
public class DisplayFormulaTraceComposer extends SelectorComposer<Component> {
2

          
3
    @Wire
4
    private Spreadsheet spreadsheet;
5

          
6
    @Wire("window .precedentBox")
7
    private Listbox precedentBox;



  • Line 3: The default case, wire by component ID as the variable name,spreadsheet. In the zul, there should be <spreadsheet id="spreadsheet"/>.
  • Line 6: Wire a component that is a child of window and has a CSS class precedentBox.

For complete syntax, please refer to JavaDoc.

Initialize Components

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:

Java
 




x
20


 
1
public class DisplayFormulaTraceComposer extends SelectorComposer<Component> {
2

          
3
    ...
4
    @Wire("window charts")
5
    private Charts charts;
6
        ...
7
    private DefaultFromToModel fromToModel = new DefaultFromToModel();
8

          
9
    @Override
10
    public void doAfterCompose(Component comp) throws Exception {
11
        super.doAfterCompose(comp);
12
        ...
13
        charts.setType(Charts.NETWOKRGRAPH);
14
        charts.setModel(fromToModel);
15
        NetworkGraphLayoutAlgorithm algorithm = charts.getPlotOptions().getNetworkGraph().getLayoutAlgorithm();
16
        algorithm.setLinkLength(40);
17
        algorithm.setEnableSimulation(true);
18
        charts.getPlotOptions().getSeries().getDataLabels().setEnabled(true);
19
        charts.getPlotOptions().getSeries().getDataLabels().setLinkFormat(""); //make link text empty
20
    }



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

Java
 




x





1
public class DisplayFormulaTraceComposer extends SelectorComposer<Component> {
2
    @Listen(Events.ON_CLICK+ " = #trace")
3
    public void trace() {
4
      ...
5
    }



  • Line 2: It registers a listener for ON_CLICK event on the component whose ID is trace. Please read Wire Event Listeners for details.

Get Precedents/Dependents

To know what precedent and dependent are, please read the Microsoft Office doc. Keikai provides methods to get them:

Java
 




xxxxxxxxxx
1


 
1
Set<Range> precedents = Ranges.range(spreadsheet.getSelectedSheet(), spreadsheet.getSelection()).getDirectPrecedents();
2
Set<Range> dependents = Ranges.range(spreadsheet.getSelectedSheet(), spreadsheet.getSelection()).getDirectDependents();



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:

Listbox MVC Architecture

To show precedents/dependents in a Listbox, I put them into a ListModelList and pass it to Listbox.setModel(). Then ListModelList will automatically notify a Listbox to render items in a browser when I add remove Range.

Java
 




xxxxxxxxxx
1
22


 
1
private ListModelList<Range> precedentList = new ListModelList<>();
2
private ListModelList<Range> dependentList = new ListModelList<>();
3
...
4
@Override
5
public void doAfterCompose(Component comp) throws Exception {
6
    super.doAfterCompose(comp);
7
    precedentBox.setModel(precedentList);
8
    dependentBox.setModel(dependentList);
9
...
10
}
11

          
12
@Listen(Events.ON_CLICK + " = #trace")
13
public void trace() {
14
    ...
15
    precedentList.clear();
16
    dependentList.clear();
17
    Set<Range> precedents = Ranges.range(spreadsheet.getSelectedSheet(), spreadsheet.getSelection()).getDirectPrecedents();
18
    Set<Range> dependents = Ranges.range(spreadsheet.getSelectedSheet(), spreadsheet.getSelection()).getDirectDependents();
19
    precedentList.addAll(precedents);
20
    dependentList.addAll(dependents);
21
    ...
22
}



  • Line 7~8: I can assign an empty ListModelList to Listbox first 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.

Java
 




xxxxxxxxxx
1
19


 
1
private void addDependent(int maxLevel) {
2
    List<Point> points = traceCells(Ranges.range(spreadsheet.getSelectedSheet(), spreadsheet.getSelection())
3
            , maxLevel, DisplayFormulaTraceComposer::getDirectDependents);
4

          
5
    String selectedCellRef = spreadsheet.getCellFocus().asString();
6
    charts.getSeries().setName(selectedCellRef);
7
    charts.getSeries().setColor(DEPENDENT_COLOR);
8
    points.stream().forEach(p -> { charts.getSeries().addPoint(p); });
9
}
10

          
11
private void addPrecedent(int maxLevel) {
12
    List<Point> points = traceCells(Ranges.range(spreadsheet.getSelectedSheet(), spreadsheet.getSelection())
13
            , maxLevel, DisplayFormulaTraceComposer::getDirectPrecedents);
14

          
15
    String selectedCellRef = spreadsheet.getCellFocus().asString();
16
    charts.getSeries(1).setName(selectedCellRef);
17
    charts.getSeries(1).setColor(PRECEDENT_COLOR);
18
    points.stream().forEach(p -> { charts.getSeries(1).addPoint(p); });
19
}



  • Line 6, 16: getSeries() and 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.

Chart Example 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.

XML
 




xxxxxxxxxx
1


 
1
<window id="rangeWin" title="List" vflex="1" border="normal" contentStyle="overflow-y: auto">
2
    ...
3
    <listbox width="100%" emptyMessage="no data" rows="3" sclass="precedentBox">
4
        ...
5
        <template name="model">
6
            <listitem label="${each}" forward="rangeWin.onFocusCell"/>
7
        </template>
8
    </listbox>



  • Line 6: forward onClick to its ancestor Window whose ID is rangeWinwith a custom event name onFocusCell(see Doc).

Focus a Cell

Java
 




xxxxxxxxxx
1


 
1
@Listen("onFocusCell = #rangeWin")
2
public void focus(ForwardEvent e){
3
    Range range = (Range)((Listitem)e.getOrigin().getTarget()).getValue();
4
    if (!isInCurrentSheet(range)){
5
        spreadsheet.setSelectedSheet(range.getSheet().getSheetName());
6
    }
7
    spreadsheet.focusTo(range.getRow(), range.getColumn());
8
}



  • Line 1: Listen to the custom event onFocusCell specified in a zul (see the previous section).
  • Line 7: focusTo() will scroll a sheet and focus on the specified cell.

Conclusion

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.

Chart

Opinions expressed by DZone contributors are their own.

Related

  • Best Gantt Chart Libraries for React
  • How To Create a Network Graph Using JavaScript
  • How To Create a Resource Chart in JavaScript
  • JavaScript Temperature Anomaly Chart

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!