Expand Your App with Cell Dependencies
For up-to-date Handsontable documentation, see handsontable.com/docs.
Data entered into the spreadsheet may often contain some cell dependencies. For example, you might want to make one cell’s value dependent on another. You may have asked yourself thousands of times in many cases how easy it is to create relationships between cells in Handsontable. I’ll do my best to explain it to you.
When do cells depend on each other?
You can say so whenever the value of one or more cells influences the value or attribute of another cell or cells.
When using Handsontable you have multiple possibilities. You can decide between the date, time, autocomplete and many more when choosing the type of cell. The process of creating dependencies is pretty much the same for each one. Assuming we have two cells dependent on one another:
- For cell 1 we listen for a change using a hook called
- For cell 2, which depends on cell 1, we apply a method called
setDataAtCell. We do so whenever we wish to change its value. By using
setCellMetawe change its metadata (its appearance for instance).
The possibility of connecting cells in an easy way and defining their mutual relationships is a thing that makes the business world your oyster. Just use it! If you own an online business, you can decide which products are available in your store and which are not. Imagine, this is just the tip of the iceberg.
Let’s start our journey through cell dependencies with a simple checkbox example. It accepts only two states and, in my case below, the checkbox is selected by default. Whenever it gets deselected, the whole row changes its background color and becomes read-only.
In the example above, we added a class called
highlightCell through the
getCellMeta method to change the background color of all cells in the row, besides the checkbox column. The read-only property was also added with the
In another demo, we are going to change the value inside the cell. Selecting the checkbox erases the content of cells under the
Year of production and
Colorcolumns for this particular row. See how it works below:
In the example above, to erase the cell values after calling a hook called
beforeChange we use the
In the presented cases we have learned how to use checkbox states to change cell values or their attributes.
When talking about data selected from a list of available options, we mainly refer to the dropdown cell. In the case below, by changing the car production year we determine the vehicle owner. See the example code here:
The above is possible thanks to listening
beforeChange and through the implementation of methods such as
setDataAtRowProp. Don’t forget to pass a specific value, like
internal as the
source argument in order to avoid an infinite loop in the
The next demonstration is an example of how to set a background color for the cars produced in 2016. We used a dropdown cell to present this concept.
Choose the year
2016 from the dropdown to make the whole row green. Want to see how this works? Jump in:
Once again, we used
setCellData in order to add the
highlightCell class, which makes the cells green.
You can use the exact same methods to change cell attributes. It doesn’t matter whether you pick select, dropdown or autocomplete type.
The case below shows you how to change the format of a time display. Either from 12-hour to 24-hour or the other way round. See the example here:
We call a hook named
beforeRenderer to transfer the time format to the
getCellMeta method, which is then used in a render of a time-type column.
If you wish to change your clock from a 12 to a 24-hour one, you just need a few lines of code. You are just one step away from making an international clock displaying the current time in all the countries in the world! Sounds exciting, doesn’t it?
This time let’s imagine we belong to a world of quantum physics and we want to present the results of particle size measurements. According to how accurate your results ought to be, you can set your own decimal place. In the example below, when typing a digit in the
Fraction digits column we can specify the accuracy of the result displayed in the
Value column. See how it’s done here:
The above was possible thanks to a custom function called
makeFormat. It takes a value from the
Fraction digits column as an argument and precisely sets the decimal place in the
In case you want to change data in a dropdown cell, use the example below. Theoretically speaking, it is viable only through accessing instance settings. Thankfully we know how to obviate that rule.
Enter the value in any cell of the
Colors column and watch how they appear in the dropdown list, beside other options, in that column. It’s pretty simple, as shown below:
In the above example we enter the values as text and treat the comma as a value separator. Next, taking the entered values as a data source, we make a list of choices for the dropdown cell. In practice, we extend the renderer called
AutocompleteRenderer by the possibility of getting the list of entries to the dropdown cell from its neighbour.
Text or numeric cell types don’t have to present only static values. They can be dependent on one another, but are also able to affect the fields of a different type such as dropdown.
What distinguishes a regular spreadsheet from your application is the power of customization. Only you decide how far you adjust Handsontable to the needs of your users and business case. The examples that we delivered will surely help you take a look at your spreadsheet in a way you might have never imagined.