Excel-Like formulas on HTML forms
Reactive updates using React or LemonadeJS.
Jspreadsheet Team
Published at 08/06/2023
Published at 08/06/2023

Introduction
Effective data management is essential for individuals and organizations in the digital era. Microsoft Excel has long been renowned for its data manipulation capabilities. But what if we could bring Excel-like formulas to HTML forms?This concept revolutionizes web development by empowering users to perform calculations, automate processes, and generate dynamic outputs directly within web forms. This tutorial presents a step-by-step guide on implementing Excel-like formulas in HTML forms using the Jspreadsheet Formula plugin.
The formula results will be rendered and updated automatically through integration with LemonadeJS or React, ensuring real-time updates.
Excel formulas are tools for performing calculations and data manipulation. This tutorial explores integrating Excel-like formulas into HTML forms using the Jspreadsheet Formula plugin. With integration with LemonadeJS or React, developers can achieve automatic rendering and real-time updates of the formula results.
How to install
From NPM
% npm install @jspreadsheet/formula
From CDN
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula@2.0.2/dist/index.min.js"></script>
Tutorial
As mentioned in the introduction, achieving a smooth user experience involves updating the formula result whenever the user modifies an input value. While this can be accomplished using events, in this tutorial, we will leverage the two-way data binding feature to automatically update the formula result based on changes made to the HTML input elements. By implementing two-way data binding and incorporating features like the home tab and absolute cell references, developers can ensure that the formula result remains up to date, providing a seamless and responsive user experience.Working example
The following example demonstrates how to create a dynamic HTML form that calculates the sum and average of selected cells. Start by clicking the cell where you want the result to appear, then type an equal sign in the formula bar. Next, choose the range of cells you want to include in the calculation and use the SUM function to add the values. To calculate the average, use the AVERAGE function. Finally, press Enter to execute the formula and see the result in the selected cell.
Directly in the browser
<html> <script src="https://cdn.jsdelivr.net/npm/lemonadejs@3.2.1/dist/lemonade.js"></script> <script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula@2.0.2/dist/index.min.js"></script> <div id="excel-like-formulas"></div> <script> function App() { let self = this; self.price1 = 0; self.price2 = 0; return `<div class="row"> <div class="form-group p2"> <label>Enter the price 1:</label> <input type="text" data-mask="#.##0,00" :bind="self.price1" /> </div> <div class="form-group p2"> <label>Enter the price 2:</label> <input type="text" data-mask="#.##0,00" :bind="self.price2" /> </div> <span>SUM: {{SUM(parseFloat(self.price1),parseFloat(self.price2))}}</span> <span>- AVERAGE: {{ROUND(AVERAGE(parseFloat(self.price1),parseFloat(self.price2)),2)}}</span> </div>`; } lemonade.render(App, document.getElementById('excel-like-formulas')); </script>
Using Webpack
import lemonade from 'lemonadejs'; import formula from '@jspreadsheet/formula'; function App() { let self = this; self.price1 = 0; self.price2 = 0; return `<div class="row"> <div class="form-group p2"> <label>Enter the price 1:</label> <input type="text" data-mask="#.##0,00" :bind="self.price1" /> </div> <div class="form-group p2"> <label>Enter the price 2:</label> <input type="text" data-mask="#.##0,00" :bind="self.price2" /> </div> <span>SUM: {{SUM(parseFloat(self.price1),parseFloat(self.price2))}}</span> <span>- AVERAGE: {{ROUND(AVERAGE(parseFloat(self.price1),parseFloat(self.price2)),2)}}</span> </div>`; }
React implementation
In React, here is an example of how you can implement automatic updates to re-run the formula whenever the user makes changes.See this React example on codesandbox
import React, { useState } from "react"; import formula from "@jspreadsheet/formula"; export default function App() { const [price1, setPrice1] = useState(0); const [price2, setPrice2] = useState(0); return ( <div> <div className="form-group p2"> <label>Enter the price 1:</label> <input type="text" data-mask="#.##0,00" value={price1} onChange={(e) => setPrice1(e.target.value)} /> </div> <div className="form-group p2"> <label>Enter the price 2:</label> <input type="text" data-mask="#.##0,00" value={price2} onChange={(e) => setPrice2(e.target.value)} /> </div> <span> SUM: {SUM(parseFloat(price1), parseFloat(price2)).toString()}{" "} </span> <span> - AVERAGE: {ROUND(AVERAGE(parseFloat(price1), parseFloat(price2)), 2).toString()} </span> </div> ); }