By one estimate, over 750 million people around the world use Google Sheets or Excel. That’s a huge number of people using spreadsheets for complex calculations and storing, organizing, and analyzing all kinds of numerical data.

Due to the popularity and widespread usage of spreadsheets across various business domains, there is an increasing demand for the functionality and familiarity that spreadsheets bring. This demand is even present in standalone web applications that historically wouldn’t have had this kind of functionality. This desire to bring the spreadsheet experience to existing web applications is one of the driving factors behind libraries like HyperFormula.

HyperFormula is a spreadsheet engine built with TypeScript that provides an API for Excel functions and formulas as well as data processing that can be consumed in a web application. HyperFormula takes care of the heavy lifting when it comes to adding spreadsheet functionality to your application. It has implementations of over 380 of the most popular Excel and Google Sheets functions.

This guide will introduce you to the HyperFormula library. It will show you its most important features and functionality and then demonstrate how they can benefit your workflows. You’ll see an example of how to use HyperFormula to create a simple mortgage calculator application. We will also show you how to create custom functions to implement your own logic.

What is HyperFormula and what does it do?

HyperFormula is an open-source, headless spreadsheet engine. It offers the freedom and flexibility to consume its APIs for parsing and evaluating Excel formulas.

HyperFormula was developed with a number of goals in mind:

• Perform complex calculations quickly on large data sets using Excel-compatible formulas
• Support custom functions, thus allowing you to extend the HyperFormula library as needed
• Perform CRUD operations such as creating multiple sheets, updating cell values, and removing rows or columns within the spreadsheet
• Work on multiple platforms either on the front-end or on the backend
• Be easy to pick up and understand from a DevOps perspective

As a headless spreadsheet calculation engine, HyperFormula does not include any user interface of its own. This means you have greater flexibility in how it can be used. You can use it as a plugin in Handsontable, a front-end library to complement React, Angular, Vue —or whatever your preferred front-end framework is—with a custom-built user interface or even inside a backend application powered by Node.js. HyperFormula is platform-agnostic, allowing you to use it with any combination of backend and front-end technologies.

This makes HyperFormula an appealing choice for both new and existing projects. The cost of entry is significantly reduced compared to libraries that may not be compatible with your preferred UI library.

In the following section, you’ll see a simple example of what you can do with HyperFormula. You’ll create a basic “mortgage calculator” type of application, where the logic and calculations are all handled by HyperFormula.

Creating a mortgage calculator spreadsheet with HyperFormula

Although this demo is not particularly complex, and you can easily implement it without leveraging HyperFormula, its intended purpose is to give you an example of how HyperFormula works. You will also learn how to use it to create and parse spreadsheet formulas on the web.

Because HyperFormula is headless, you can use it with whatever UI makes the most sense for your application. Whether you decide to implement a full spreadsheet-style experience or something as simple as a few inputs and a button, the choice is yours. This example uses a basic table to emulate something reminiscent of a spreadsheet.

This example is based on and inspired by the official HyperFormula demo. You can find the code for this particular example in CodeSandbox.

There are a few notable things happening in the source code. For one, all the data is stored in src/data.js as an array of arrays. This is indicative of the fact that spreadsheets don’t typically have a strict schema. If you think of applications like Excel and Google Sheets, you can enter whatever values you want in whatever cells you want. HyperFormula follows suit in this regard. It doesn’t require you to have a strict schema. Instead, it treats your data as if it were a spreadsheet, regardless of the form your UI takes.

In src/data.js, you can also see the formulas that are defined for this example, like so:

export const tableData = [
[
    "Victor Perez",
    350000,
    45000,
    "=B1-C1",
    30,
    "3.90%",
    "=ROUNDUP(PMT(F1/12;E1*12;-D1); 2)",
    "=(G1*E1*12)+C1",
    "=H1-B1"
  ],
//… truncated for brevity
];

These formulas are subsequently resolved in src/renderer.js by the renderTable function, which is called when the corresponding Run Calculations button in the UI is clicked. This function makes a number of calls to the HyperFormula API and constructs new HTML to set into the table’s DOM node. We included the code below for your convenience:

export function renderTable(calculated = false) {
  const tbodyDOM = document.querySelector(".example tbody");
  const updatedCellClass = ANIMATION_ENABLED ? "updated-cell" : "";
  const { height, width } = hf.getSheetDimensions(sheetId);
  let newTbodyHTML = "";

  for (let row = 0; row < height; row++) {
    for (let col = 0; col < width; col++) {
      const cellAddress = { sheet: sheetId, col, row };

      const cellHasFormula = hf.doesCellHaveFormula(cellAddress);
      const showFormula = calculated || !cellHasFormula;
      let cellValue = "";

      if (!hf.isCellEmpty(cellAddress) && showFormula) {
        cellValue = formatCellValue(cellAddress);
      } else {
        cellValue = hf.getCellFormula(cellAddress);
      }

      newTbodyHTML += `<td class="${
        cellHasFormula ? updatedCellClass : ""
      }"><span>
      ${cellValue}
      </span></td>`;
    }

    newTbodyHTML += "</tr>";
  }

  tbodyDOM.innerHTML = newTbodyHTML;
}

In this code, you can see a number of calls to the HyperFormula API, such as hf.doesCellHaveFormula(cellAddress), hf.getCellValue(cellAddress), and hf.getCellFormula(cellAddress). They speak to one of the compelling benefits of HyperFormula. It provides you with all the pieces to build advanced functionality into your applications without imposing opinions about the specifics of those implementations.

Custom functions in HyperFormula

A custom function in HyperFormula is equivalent to a user-defined function (UDF) in Microsoft Excel. When you’re dealing with not particularly common requirements like advanced numerical calculations, string manipulations, or complex lookups and expressions, there will be times when you won’t find the specific formula among the standard HyperFormula functions.

As an example, consider a scenario where you’re selling office supplies, and you’re using HyperFormula to assist with invoicing your customers. Suppose you have a policy that awards a discount when a customer purchases more than a hundred units of a given item.

While you could implement this using built-in formulas, it might be worth doing it with a custom function instead. The rules surrounding your discount policy might undergo changes over time, and you’d want the formula to be responsive to those changes.

Instead of updating your formula everywhere, you could just update the custom function’s definition.

Take a look at the application below, which shows a spreadsheet calculation with a custom function.

You can see the code for this example on CodeSandbox, with the most relevant part included below:

export class Discount extends FunctionPlugin {
  discount(ast, state) {
    return this.runFunction(
      ast.args,
      state,
      this.metadata("DISCOUNT"),
      function (quantity, price) {

        let discount = 0;

        // check for undefined value
        if (quantity == null || price == null) {
          return new CellError(ErrorType.VALUE);
        }

        if (quantity >= 100) {
          discount = parseFloat(quantity * price * 0.1).toFixed(2);
        }

        return discount;
      }
    );
  }
}
// Static property with the custom functions definitions
Discount.implementedFunctions = {
  DISCOUNT: {
    method: "discount",
    parameters: [{ argumentType: "NUMBER" }, { argumentType: "NUMBER" }]
  }
};

In the above snippet, you can see the definition of the custom function and the underlying logic that it will run when invoked by HyperFormula.

Part of HyperFormula’s value proposition is its excellent extensibility and flexibility. The ability to use whatever UI you want and implement custom functions as needed means that HyperFormula can grow alongside your application rather than constraining it.

Conclusion

In this article, you’ve seen the possibilities that HyperFormula brings to the table, helping you understand how the engine works. Specifically, you saw what HyperFormula is and what it does. This includes a practical demo of a mortgage calculator, as well as how to add custom function features.

If you need spreadsheet-like functionality in your application, be sure to give HyperFormula a try.