Spreadsheet applications, like the popular Microsoft Excel, are known for their wide range of functions — efficient, predefined formulas that perform calculations on data. When it comes to building web applications, spreadsheets with formulas serve well to do the heavy lifting of data processing. They’re an easy, flexible, and inexpensive solution for most data-processing tasks.

However, implementing such functionality from the ground up could be a huge investment on your part. Luckily, you can make use of readymade solutions — like our own Handsontable.

In this article, you’ll be introduced to the Handsontable data grid component and the HyperFormula calculation engine behind some of its more advanced functionality. Moreover, you’ll learn how to use them to run Excel-like formulas on the web. Additionally, you’ll gain a few tips on how to make the most of Handsontable’s functionality.

What can Handsontable do for you?

As a data grid, Handsontable helps you quickly integrate data-driven spreadsheet interfaces into your web applications. Handsontable’s built-in features make it a good fit for a variety of use cases, including the following:

• Human resource management applications
• Data management systems
• Data modeling applications
• Resource planning software
• Reporting software

One commonality the above tools possess is that they typically work with huge volumes of data, which makes performance a serious concern. Handsontable is fundamentally designed to address that issue; as a result, it is highly performant even with large datasets.

Below is an example of the Handsontable data grid in action.

How Excel-style spreadsheet formulas work in Handsontable

Handsontable uses the optional Formulas plugin, which is based on the HyperFormula engine.

HyperFormula is an open-source spreadsheet and calculation engine library for developing robust data management apps. It was created and is actively maintained by the same team responsible for Handsontable. HyperFormula is able to parse and evaluate Excel formulas.

HyperFormula has more than 380 built-in functions with the same A1 notation-based syntax used by Microsoft Excel and Google Sheets; it also has a rich set of APIs that can be used to write custom functions. As headless software, HyperFormula doesn’t include a user interface, letting programmers use it across different applications.

Using Excel formulas with Handsontable and HyperFormula for calculations

The best way to fully understand the power of Handsontable and HyperFormula is to see it in action. To that end, here are some scenarios where the software shines.

Flexible custom reporting

In organizations that provide B2B products or services, users tend to generate a lot of data; subsequently, product owners want to be able to report on that data, as it contains insights crucial to their business strategies. Custom needs will therefore vary enough that it can be difficult to offer a reasonable one-size-fits-all solution. Until now, the standard practice was to force customers to deal with CSV or Excel reports and leave them to the task of wrangling a large dataset on their own.

Handsontable and HyperFormula are bridging the gap between your product offering and your customers’ needs. They get the benefit of being able to report on whatever data they need without needing to leave your application, and your team gets the benefit of not needing to implement every possible reporting functionality or scenario.

Learn how Handsontable helped Advise MC provide transparent, actionable data for ROI audits  ⟶

Handling financial calculations

Another scenario that would be a good fit for the functionality offered by Handsontable is financial calculations. Historically, spreadsheet applications like Excel have been used for a wide variety of tasks in the financial domain, so it is not a question of whether Handsontable would be capable but what problems it could solve better, faster, or cheaper than a custom-built solution.

Consider something as simple as invoicing. If you’re building an invoicing application, a lot of nuances can go into the implementation. From taxes like GST and VAT to discounts and subtotals, there are a lot of calculations that need to be run. Granted, many of them are not that complex on their own; however, they will compound over time.

If you start out by writing bespoke calculations in code, you may find it challenging to expand to a more robust, dynamic system later. Conversely, if you try to build something all-encompassing right from the beginning, you’re making a significant upfront time investment with no guarantee of return. Alternatively, you can simply adopt a solution like Handsontable and HyperFormula, which gives you a simple, refined, and well-tested framework for crafting your calculations.

Exposing predefined formulas

Consider services like Omni Calculator, which provides users with a large number of purpose-built calculators for specific, well-known formulas. This purpose is another excellent use case for Handsontable and HyperFormula.

If you operate in a domain that relies on a lot of well-known formulas, like various financial domains, Handsontable and HyperFormula give you the relative ease of being able to implement calculations into your application with familiar Excel-like syntax. Couple this with the freedom to use your own UI, and you have the potential to implement whatever calculation-driven workflows suit your business needs.

See it in action

To get a feel for how easy it is to get started with Handsontable, you can follow along with this short tutorial section or see the result on CodeSandBox.

The scenario that’s being implemented here is a simple bounce rate calculator.

The formula for calculating this bounce rate is as follows:

Bounce rate = (number of one-page visits / number of visits) * 100

If you’d like to follow along, you can do so with either local files on your computer or the starter project in Vanilla in CodeSandBox. This section will assume you’re using CodeSandBox.

Assume that you have data in the following multidimensional array format:

[
  ["150", "643"],
  ["172", "474"],
  ["188", "371"],
  ["162", "731"],
  ["133", "682"],
  ["87", "553"],
  ["26", "592"],
  ["110", "461"],
  ["50", "346"],
  ["160", "423"]
]

Here, the first column is the number of one-page visits, while the second column is the total number of website visits. With this scenario in mind, you can begin. Create a new starter project in CodeSandBox if you haven’t already. This starter has a simple HTML file and a corresponding JavaScript file premade. Change the content of the index.html file to the following:

<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="UTF-8" />
		<meta http-equiv="X-UA-Compatible" content="IE=edge" />
		<meta name="viewport" content="width=device-width, initial-scale=1.0" />
		<title>Bounce Rate Calculator</title>

		<script src="./src/index.js"></script>
	</head>
	<body>
		<h1 style="text-align: center; font-family: 'Segoe UI', sans-serif;">
			Bounce Rate Calculator
		</h1>
		<div id="demo-data-grid"></div>
	</body>
</html>

Replace the content of package.json file with the following code:

{
  "name": "bounce-rate-calculator",
  "version": "1.0.0",
  "description": "",
  "main": "index.html",
  "scripts": {
  "start": "parcel index.html --open",
  "build": "parcel build index.html"
},
  "dependencies": {
  "handsontable": "12.4.0",
  "hyperformula": "2.5.0",
  "parcel-bundler": "^1.6.1"
},
  "devDependencies": {
  "@babel/core": "7.2.0",
  "typescript": "4.4.4"
},
  "resolutions": {
  "@babel/preset-env": "7.13.8"
},
  "keywords": []
}

Next, modify the existing index.js file to have the following content:

import Handsontable from "handsontable";
import { HyperFormula } from "hyperformula";
import { registerAllModules } from "handsontable/registry";
import "handsontable/dist/handsontable.full.min.css";

// register Handsontable's modules
registerAllModules();

// third column contain bounce rate excel expression
const data = [
  ["150", "643", "=(A1/B1)*100"],
  ["172", "474", "=(A2/B2)*100"],
  ["188", "371", "=(A3/B3)*100"],
  ["162", "731", "=(A4/B4)*100"],
  ["133", "682", "=(A5/B5)*100"],
  ["87", "553", "=(A6/B6)*100"],
  ["26", "592", "=(A7/B7)*100"],
  ["110", "461", "=(A8/B8)*100"],
  ["50", "346", "=(A9/B9)*100"],
  ["160", "423", "=(A10/B10)*100"],
  ["Average", "Average", "Average"],
  ["=AVERAGE(A1:A10)", "=AVERAGE(B1:B10)", "=AVERAGE(C1:C10)"]
];

const container = document.getElementById("demo-data-grid");
// new instance of handsontable
new Handsontable(container, {
  // add the data
  data: data,
  formulas: {
    // use HyperFormula as the calculation engine
    engine: HyperFormula
  },
  // add left serial number
  rowHeaders: true,
  // column names
  colHeaders: [
  "Number of one-page visits",
  "Number of website visits",
  "Bounce rate"
  ],
  // make the last 2 bottom row fixed
  fixedRowsBottom: 2,
  stretchH: "all",
  height: 500,
  licenseKey: "non-commercial-and-evaluation"
});

Here, you can see your data from above but now with a third column. This new column contains Excel-like syntax that will be used to run the bounce rate calculation. There are also additional rows at the bottom that will calculate the average of each given column.

In your preview window, you should now see something like this:

With a trivial amount of code, you’ve been able to create a simple application with dynamic calculation logic built in, courtesy of Handsontable.

Conclusion

Handsontable reduces the complex process of writing calculation logic to the simple process of writing Excel expressions that get resolved through the HyperFormula engine.

In this article, you were introduced to Handsontable and its use cases. You also got to know more about HyperFormula, the calculation engine used by Handsontable’s Formulas plugin to parse Excel-like expressions.

Finally, you saw a number of real-world use cases for this kind of functionality that will hopefully inspire you and give you ideas on how you can leverage Handsontable and HyperFormula in your own applications. If you’d like to use either of our products for a commercial application, don’t wait — contact our Sales team today.