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